ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¤£©w°Ï°ì¬q¸¨ªº®æ¦¡¤Æ»yªk¡C

[µo°Ý] ¤£©w°Ï°ì¬q¸¨ªº®æ¦¡¤Æ»yªk¡C

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-12-25 07:35 ½s¿è

Àµ½Ð¦U¦ì¤j¤jÀ°¦£½s¼g1­Ó¯à¨Ì"¤p­p"©M"Á`­p"ªº¼ÐÃD¦Û°Ê§P§O¬q¸¨°Ï°ì½d³ò§@®æ¦¡³]©wªºµ{¦¡ÀÉ¡A
¥H§Q¯à¥H1´Ú¦¡ªºµ{¦¡½X§Y¥i§¹¦¨°õ¦æ¦UºØ±Æ§Ç¬q¸¨ªº®æ¦¡»Ý¨D¡C
ÁÂÁÂ !

»Ý¨D¡J
A_«ü©w°Ï°ì¼Ð¥Ü²Ê®Ø½u©M©³¦â
1_½Ð±N°¸¼Æ¬q¸¨(¥HBÄæ¬q¸¨°_ºâ)¥ÑA3¨ìÁ`­p¦C(¦ì§}¤£©w)¦U¼Ð¥Ü5¸¹²Ê®Ø½u©M34¸¹©³¦â¡C
2_½Ð±N¹ï¨¤°Ï°ìªº¤p­p¦C¬q¸¨¼Ð¥Ü36¸¹©³¦â¡C

B_«ü©w°Ï°ìªº«e3¤j­È¼Ð¥Ü©³¦â
1_½Ð±N¦U"¤p­p"¦C©M"Á`­p"¦Cªº¦U°Ï°ì¬q¸¨¤§«e3¤j­È(¥i­«½Æ)¦U¼Ð¥Ü38,4,8¸¹©³¦â¡C

¸Ô¦p½d¨ÒÀÉ¡J ®æ¦¡¤Æªº»yªk.rar (13.4 KB)
¥¼©R¦W.png
2020-12-25 07:29

¦^´_ 1# ziv976688


¦³¤Wµù¸Ñ¡A¤£¹LÀ³¸ÓÁÙ¬O¤£¤Ó¦nÀ´

°õ¦æ§¹VBA ¥i¥H«ö "Ctrl + G" ¬d¬Ý®æ¦¡¤Æ±ø¥óªº³]©w¤º®e

Åý§A¤è«K§Ö³t¬d¬Ý¡A¤£¥Î¤@­Ó­ÓÂI¶i¥h¡A¥H«á§A¤]¦n¦Û¤v­×§ï

Àɮצp¤U¡A¸Õ¸Õ¬Ý~


®æ¦¡¤Æªº»yªk-1226.rar (21.28 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 2# n7822123

­×§ï¤@¤U¨¾§bµ{¦¡(¦³§ä¨ì½d³ò¤~°õ¦æ)¡AÁקK¸õ¿ù»~

®æ¦¡¤Æªº»yªk-1226.rar (21.65 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 3# n7822123
n¤j : ¦­¦w !
´ú¸Õ¦¨¥\~¤Ó´Î¤F ! ²×©ó§¹¦¨¾A¥Î¥þ³¡¤£¦PÄæ¦ì°Ï°ì¬q¸¨®æ¦¡Àɮתº»Ý¨D¡C
¤×¨ä¶Qµ{¦¡½X¦³¥[¤Wµù¸Ñ¡AÅý§Ú§ó®e©ö¤F¸Ñµ{¦¡½Xªº·N¸q¡A¦³§Q©ó¤é«á¸U¤@¦³»Ý¨DÅܧó¡A¥i¦Û¦æ­×§ï~
ÁÂÁ±zªº¼ö¤ßÀ°¦£©M«ü¾É~·P®¦³á

TOP

¦^´_ 4# ziv976688


¼gªº¤ñ¸û¥^¦£¡A¦³¦a¤è¨S§ï¨ì¡A½Ð­×§ï¥H¤Uµ{¦¡

¤½¦¡1 = "=(" & ²Ä¤@®æ & "=MAX(" & ¤½¦¡½d³ò & "))*(B11>=0)"

§ï¬°

¤½¦¡1 = "=(" & ²Ä¤@®æ & "=MAX(" & ¤½¦¡½d³ò & "))*(" & ²Ä¤@®æ & ">=0)"

³o¼Ë¤~§¹¥þ²Å¦X§A­ì¥»ªº½d¨Ò¤½¦¡
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

Àµ½Ð¦U¦ì¤j¤jÀ°¦£½s¼g1­Ó¯à¨Ì"¤p­p"©M"Á`­p"ªº¼ÐÃD¦Û°Ê§P§O¬q¸¨°Ï°ì½d³ò§@®æ¦¡³]©wªºµ{¦¡ÀÉ¡A
¥H§Q¯à¥H1´Ú ...
ziv976688 µoªí©ó 2020-12-25 07:31

´£¨Ñ¥t¥~¤@­Ó¨¤«×ªº¼gªk :
  1. Sub SetRng()
  2.   Dim vColor()
  3.   Dim lCol&(), lCols&, lRow&(), lRows&, lL1&, lL2&, lL3&
  4.   
  5.   ReDim lCol(5) ' ¤­¦æ¥u¦³5­Ó
  6.   vColor = Array(0, 38, 4, 8)
  7.   ' ----- ¨ú±o©Ò¦³ÃöÁäÄ渹»P¦C¸¹ -----
  8.   lCol(1) = 2 ' ¨ú±o¤­¦æ¤å¦r©Ò¦bÄ渹
  9.   For lL1 = 2 To 5
  10.     lCol(lL1) = Cells(1, lCol(lL1 - 1) + 1).End(xlToRight).Column
  11.   Next
  12.   lCols = Cells(2, Columns.Count).End(xlToLeft).Column ' ¦b²Ä2¦C§ä¸ê®Æ¥½Äæ
  13.   
  14.   lL1 = 3 ' ¨ú±o¤p­p»PÁ`­p©Ò¦b¦C¸¹
  15.   ReDim lRow(0) ' °}¦Cªì©l¤Æ
  16.   Do While Cells(lL1, 1) <> "Á`­p"
  17.     Do While Cells(lL1, 1) <> "¤p­p" And Cells(lL1, 1) <> "Á`­p"
  18.       lL1 = lL1 + 1
  19.     Loop
  20.     If Cells(lL1, 1) <> "Á`­p" Then ' ¤p­p©Ò¦b¦C¸¹
  21.       ReDim Preserve lRow(UBound(lRow) + 1)
  22.       lRow(UBound(lRow)) = lL1
  23.       lL1 = lL1 + 1
  24.     Else
  25.       lRows = lL1 ' Á`­p©Ò¦b¦C¸¹
  26.     End If
  27.   Loop
  28.   
  29.   ' ----- ²M°£®æ¦¡³]©w»P®Ø½u -----
  30.   With Range(Cells(3, 2), Cells(lRows, lCols)) ' ¸ê®Æ½d³ò
  31.     .FormatConditions.Delete ' ²M°£©Ò¦³®æ¦¡³W«h
  32.     For lL1 = 1 To 10 ' ²M°£®Ø½u³]©w
  33.       .Borders(lL1).LineStyle = 0
  34.     Next
  35.     .Interior.Pattern = xlNone ' ²M°£©³¦â
  36.   End With

  37.     ' ----- ¶}©l®æ¦¡³]©w -----
  38.   For lL1 = 2 To UBound(lCol) Step 2 ' ³]©w°¸¼Æ¬q¸¨
  39.     With Range(Cells(3, lCol(lL1)), Cells(lRows, lCol(lL1 + 1) - 1))
  40.       .Interior.ColorIndex = 34 ' ³]©w©³¦â
  41.       For lL2 = 7 To 10 ' ³]©w¥~®Ø½u
  42.         With .Borders(lL2)
  43.           .LineStyle = 1
  44.           .ColorIndex = 5
  45.           .Weight = 4
  46.         End With
  47.       Next
  48.     End With
  49.   Next
  50.   
  51.   For lL1 = 1 To 4 ' ³]©w¹ï¨¤°Ï°ì©³¦â
  52.     Range(Cells(lRow(lL1), lCol(lL1)), Cells(lRow(lL1), lCol(lL1 + 1) - 1)) _
  53.          .Interior.ColorIndex = 36
  54.   Next
  55.   Range(Cells(lRow(5), lCol(5)), Cells(lRow(5), lCols)).Interior.ColorIndex = 36
  56.   
  57.   ReDim Preserve lCol(UBound(lCol) + 1) ' ÁY´îµ{¦¡½X,³o¸Ì°µ­Ó¨ú¥©°Ê§@,°t¦X°j°é§@·~
  58.   lCol(UBound(lCol)) = lCols
  59.   
  60.   For lL1 = 1 To 5 ' ³]©w®æ¦¡¤Æ¤½¦¡
  61.     For lL2 = 1 To UBound(lRow)
  62.       For lL3 = 1 To 3
  63.         With Range(Cells(lRow(lL2), lCol(lL1)), Cells(lRow(lL2), lCol(lL1 + 1) - 1))
  64.           .FormatConditions.Add Type:=xlExpression, Formula1:= _
  65.           "=" & Cells(lRow(lL2), lCol(lL1)).Address(0, 0) & "=LARGE(" & .Offset(0).Address & "," & lL3 & ")"
  66.           .FormatConditions(.FormatConditions.Count).SetFirstPriority
  67.           With .FormatConditions(1).Interior
  68.             .PatternColorIndex = xlAutomatic
  69.             .ColorIndex = vColor(lL3)
  70.             .TintAndShade = 0
  71.           End With
  72.           .FormatConditions(1).StopIfTrue = True
  73.         End With
  74.       Next
  75.     Next
  76.   Next
  77. End Sub
½Æ»s¥N½X
®æ¦¡¤Æªº»yªk-a.zip (29.78 KB)

TOP

¦^´_ 5# n7822123
n¤j :
¦³´ú¸Õ¥Í¨vªº³W®æ®æ¦¡¡A3¼Óªºµª®×¤]¬O¥¿½Tªº
ÁÂÁ±z­@¤ßªº¦A§@§óºë·Çªº­×¥¿¡C
¤w¨Ì·Ó¶Q¨£­×§ï¦n¤F~·P¿E¤£ºÉ
¥¼©R¦W.png
2020-12-26 15:46

1226-®æ¦¡¤Æªº»yªk.rar (43.93 KB)

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-12-26 16:43 ½s¿è

¦^´_ 6# luhpro
I ¤j : ±z¦n !
·PÁ±zªºÀ°¦£©M«ü¾É
´ú¸Õµ²ªG³ø§i :
¤j­ì«h"¦Û°Ê¤À¬q"¬OOKªº¡F
¥u¬O¦³¨Ç"«Dº¸¦]¯À"¼vÅT¨ì¥¿½Tªºµª®×~
1_«e3¤jªº©³¦â¼Ð¥ÜµLªkÅã¥Ü~¸õ¥X¿ù»~438(ª«¥ó¤£¤ä´©¦¹ÄݩʩΤèªk)¡C
¤£¦n·N«ä¡A¥i¯à¬O¦]¨ü¨ì§ÚªºExcel¬°2003ª©©Ò­P¡C
2_¤À¹j¬q¸¨¥u­­©ó5­Ó¡C
¤£¦n·N«ä¡A¦]¬°§Úªº¤å¦r»¡©ú¥u¥H¤@¥y"¦Û°Ê§P§O¬q¸¨°Ï°ì½d³ò"±a¹L¡A¨Ã¨S¦³¯S§Oµù©ú¦³«Ü¦hºØªº¬q¸¨°Ï°ì¡A¥H­PÅý±z»~·|¥u¦³5¬qªº¤@ºØ°Ï°ì½d³ò~©|½Ð±z¨£½Ì

PS¡G¡@
§Ú¬O¦³¶R¢±¢¯¢°¢µª©ªº¢Ó£A¢ë¢í¢ô¡A¦ý±N¢±¢¯¢¯¢²ª©§ï¦¨¢±¢¯¢°¢µª©¡A±`·|²£¥Í°»¿ù©Î¬Æ¦ÜµLªk¶}±ÒÀɮסF¡@
¦]¬°¸g¦~²Ö¿nªºÀɮ׬Ʀh¡AµLªk¤@¤@­×§ï¡A¥u¦n¥H¢±¢¯¢¯¢²ª©Äò¥Î ¡A½Ð±z¨£½Ì¡AÁÂÁ±z¡I

¥¼©R¦W.png
2020-12-26 16:25

®æ¦¡¤Æªº»yªk-l¤j.rar (33.6 KB)

TOP

¦^´_ 7# ziv976688


    ¦pªG§Aªº¸ê®Æ®æ¦¡ÅܤƦA¤j¤@ÂI¡A²Ä¤@®æ´N¤£¤@©w¬OB11¤F¡A­×¥¿«áµ{¦¡¼u©Ê·|¦n¤@ÂI
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 9# n7822123
n¤j :±z¦n!.
«¢~±z»~¸Ñ§Úªº·N«ä¤F~
§Ú¬O»¡ : ¦pªG±z¤£´£¡A§Ú®Ú¥»¤£ª¾¹D3¼Óªº¶Q¸Ñ©|¦³¥iºë¶iªº¦a¤è
¦A¦¸ÁÂÁ±zªº¼ö¤ß«ü¾É©MÀ°¦£

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD