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

[µo°Ý] «ü©w¦C¤§«e3¦W¦U¼Ð¥ÜÃC¦â¤Î«ü©wÄæ¼Ð¥Ü©³¦âªº»yªk¡C

[µo°Ý] «ü©w¦C¤§«e3¦W¦U¼Ð¥ÜÃC¦â¤Î«ü©wÄæ¼Ð¥Ü©³¦âªº»yªk¡C

¥»©«³Ì«á¥Ñ papaya ©ó 2019-3-24 22:23 ½s¿è

¼Ð¥ÜÃC¦â.rar (8.35 KB)

EX_1.png
2019-3-24 22:15

EX_2.png
2019-3-24 22:15

»Ý¨D_1¡J
A1¡JA&¦³¼Æ¦rªº³Ì«á¤@¦C¼Ð¥Ü²LºñÂÅ©³¦â
G1¡JK&¦³¼Æ¦rªº³Ì«á¤@¦C¼Ð¥Ü²LºñÂÅ©³¦â
Q1¡JU&¦³¼Æ¦rªº³Ì«á¤@¦C¼Ð¥Ü²LºñÂÅ©³¦â
AA1¡JAE&¦³¼Æ¦rªº³Ì«á¤@¦C¼Ð¥Ü²LºñÂÅ©³¦â
AK1¡JAO&¦³¼Æ¦rªº³Ì«á¤@¦C¼Ð¥Ü²LºñÂÅ©³¦â
AU1¡JAX&¦³¼Æ¦rªº³Ì«á¤@¦C¼Ð¥Ü²LºñÂÅ©³¦â

»Ý¨D_2-1¡J
±N­Ë¼Æ²Ä7¦C(§YAÄæ=ªÅªº¦C)ªºB¡JAX~
³Ì¤j¼Æ¼Ð¥Ü²L¯»ÂÅ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤j¼Æ¼Ð¥Ü«Gºñ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤j¼Æ¼Ð¥Ü²L¾í©³¦â(¥i¦³1­Ó¥H¤W)¡F
³Ì¤p¼Æ¼Ð¥Ü¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤p¼Æ¼Ð¥Ü¯»¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤p¼Æ¼Ð¥Ü²`¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F

»Ý¨D_2-2¡J
±N­Ë¼Æ²Ä5¦C(§YAÄæ=¾÷ªº¦C)ªºB¡JAX~
³Ì¤j¼Æ¼Ð¥Ü²L¯»ÂÅ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤j¼Æ¼Ð¥Ü«Gºñ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤j¼Æ¼Ð¥Ü²L¾í©³¦â(¥i¦³1­Ó¥H¤W)¡F
³Ì¤p¼Æ¼Ð¥Ü¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤p¼Æ¼Ð¥Ü¯»¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤p¼Æ¼Ð¥Ü²`¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F

¥H¤W»Ý¨D¡A¸Û½Ð¦U¦ì¤j¤jÀ°¦£©M½ç±Ð!
ÁÂÁ¦U¦ì!

¦^´_ 1# papaya
¼Ð¥ÜÃC¦â.rar (28.43 KB)

EX 1.png
2019-3-28 07:06

EX 2.png
2019-3-28 07:07

­«·s¾ã²z~
»Ý¨D_1¡J
±N­Ë¼Æ²Ä7¦C(§YAÄæ=ªÅªº¦C)ªºB¡JAX~
³Ì¤j¼Æ¼Ð¥Ü²L¯»ÂÅ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤j¼Æ¼Ð¥Ü«Gºñ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤j¼Æ¼Ð¥Ü²L¾í©³¦â(¥i¦³1­Ó¥H¤W)¡F
³Ì¤p¼Æ¼Ð¥Ü¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤p¼Æ¼Ð¥Ü¯»¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤p¼Æ¼Ð¥Ü²`¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F

»Ý¨D_2¡J

±N­Ë¼Æ²Ä5¦C(§YAÄæ=¾÷ªº¦C)ªºB¡JAX~
³Ì¤j¼Æ¼Ð¥Ü²L¯»ÂÅ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤j¼Æ¼Ð¥Ü«Gºñ©³¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤j¼Æ¼Ð¥Ü²L¾í©³¦â(¥i¦³1­Ó¥H¤W)¡F
³Ì¤p¼Æ¼Ð¥Ü¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤G¤p¼Æ¼Ð¥Ü¯»¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F
²Ä¤T¤p¼Æ¼Ð¥Ü²`¬õ¦â²ÊÅ骺¦rÅéÃC¦â(¥i¦³1­Ó¥H¤W)¡F

½Ð¸Ô¨£¡J¼Ð¥ÜÃC¦â(EX 1) & (EX 2)

¥H¤W»Ý¨D¡A¸Û½Ð¦U¦ì¤j¤jÀ°¦£©M½ç±Ð!
ÁÂÁ¦U¦ì!

TOP

¦^´_ 2# papaya
²¤Æ°ÝÃD¡J
½Ð«ü¥¿¤U¦Cµ{¦¡½X~
'±NB¡JAX­Ë¼Æªº²Ä7¦C¤§³Ì¤j¼Æ¼Ð¥Ü²L¯»ÂÅ©³¦â~
For Each A In Cells([B65536].End(xlUp).Row - 6, 2).Resize(1, 49)
If Application.Max(Cells([B65536].End(xlUp).Row - 6, 2).Resize(1, 49)) Then A.Interior.ColorIndex = 8
Next

§e²{ªº¬O¿ù»~ªº¼Ð¥Ü½d³ò(±NB¡JAX­Ë¼Æªº²Ä7¦C¥þ¼Ð¥Ü²L¯»ÂÅ©³¦â)
¿ù»~ªº¼Ð¥Ü.png
2019-3-29 05:50


¥¿½Tªº¼Ð¥Ü¬O¡J±N³Ì¤j¼Æ(=13)¼Ð¥Ü²L¯»ÂÅ©³¦â
¥¿½Tªº¼Ð¥Ü.png
2019-3-29 05:52


½Ð°Ý:¥¿½Tªºµ{¦¡½X­n¦p¦ó½s¼g?
ÁÂÁ¦U¦ì!

TOP

(¥ý)B1:AX23®æ¦¡¤Æ=(MOD(INT(COLUMN(E1)/5),2)=0)*1

B17:AX17³Ì¤j¼Æ=LARGE($B17:$AX17,1)=B17

²Ä¤G ¤T¤j¼Æ :¤½¦¡ 1§ï 2 ,3

³Ì¤p¼Æ ¤½¦¡ LARGE§ïSMALL

19¦C¦P17¦C
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 4# hcm19522
hcm19522¤j¤j:±z¦n!
¨ç¼Æ¸Ñ§Ú¤w·|¸Ñ~http://forum.twbts.com/thread-20731-1-1.html~
¦ý¬O¨C­Ó²Î­pªí·|¦]´Á¼Æªº¼W¥[¦Ó¦C¼Æ¤£©T©w¡A©Ò¥H¨ç¼Æ¸Ñ¤£¾A¥Î¡C

ª¦¤å¦h¤é~¥u¦³¦b"ª¾ÃÑ+"§ä¨ì¤@¬q¾A¥Îªº»yªk¡A¦ý¦]¦~¤ë¹L¤[¡A¨ä½d¨Ò¤wµLªk¤U¸ü¡A©Ò¥HA­Èªº³]©wÁÙ¨S¦³·d²M·¡^^"
³Ì«áÁÙ¬O«D±`·PÁ±zªº¸Ñµª^^

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2019-3-30 16:36 ½s¿è

¦^´_ 5# papaya


    ("ªÅ"¦æ ³Ì¤j)B77:AX???®æ¦¡¤Æ=(LARGE(OFFSET($B$1,MATCH("ªÅ",$A:$A,)-1,,,20),1)=OFFSET($A$1,MATCH("ªÅ",$A:$A,)-1,COLUMN(A1)))*(ROW(A1)=MATCH("ªÅ",$A:$A,)-1)
¦¸¤j «Ý¬ã¨s
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 6# hcm19522
½Ð°Ý:
("ªÅ"¦æ ³Ì¤j)B77:AX???®æ¦¡¤Æ=(LARGE(OFFSET($B$1,MATCH("ªÅ",$A:$A,)-1,,,20),1)=OFFSET($A$1,MATCH("ªÅ",$A:$A,)-1,COLUMN(A1)))*(ROW(A1)=MATCH("ªÅ",$A:$A,)-1)
  °²³]¬O¦b$B17:$AX17
20¬O¥Nªí¤°»ò·N«ä?ÁÂÁ±z!

TOP

https://blog.xuite.net/hcm19522/twblog
¥ý¤F¸Ñ¦¹ÅÞ¿èhttps://blog.xuite.net/hcm19522/twblog/587219554
OFFSET($B$1,MATCH("ªÅ",$A:$A,)-1,,,20)-->¾ã¦æ¸ê®Æ
N(OFFSET($A$1,MATCH("ªÅ",$A:$A,)-1,COLUMN(A1)))-->¸Ó¦æ©¹¥k³v¤@¸ê®Æ
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 7# papaya


    ¸ê®Æ½d³ò49-->20§ï49
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 9# hcm19522
´ú¸ÕÀÉ:
EX 1 & 2.rar (15.11 KB)

¤£ª¾§Úªº¾Þ§@¦³¨S¦³¿ù»~?
®æ¦¡³]©w¤½¦¡¦bB2:AX2~´ú¸Õ¨S¦³¦¨¥\^^"
½Ð«ü¥¿!ÁÂÁ±z!

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD