[µo°Ý] ½Ð±N¤å¦rªº¤½¦¡«¬Åã¥Ü­È§ï¬°¼Æ­È«¬ªº¤½¦¡Åã¥Ü­È¡C

ziv976688

¤U¸ü (105.55 KB)

2020-12-3 06:24

B6=¨ú²Ä4¦Cªº10­Ó°Ï¬q(5Äæ*9+4Äæ*1)¤§¦U³Ì¤j¼Æ(¦³­«½Æ®É¡A¥þ³¡¦C¥X)
¤½¦¡
=INDEX(4:4,SMALL(IF(SUBTOTAL(4,OFFSET($A4,,ROW(1:10)*5-4,,5))-N(OFFSET($A4,,ROW(1:10)*5-5+COLUMN($A:$E)))=0,ROW(1:10)*5-4+COLUMN($A:$E),99),COLUMN(A1)))&""   °}¦C

½Ð±NB6ªº¤å¦r«¬¤½¦¡Åã¥Ü­È§ï¬°¼Æ­È«¬¤½¦¡Åã¥Ü­È¡A¥H§QB7ªº¤½¦¡¯à¦³®Ä¹Bºâ¡C
ÁÂÁ¦U¦ì¡I

´ú¸ÕÀÉ : ¤å¦r«¬¤½¦¡Åã¥Ü­È§ï¬°¼Æ­È«¬¤½¦¡Åã¥Ü­È.rar (4.29 KB)

¤U¸ü¦¸¼Æ: 1

2020-12-3 06:24

ziv976688

§Ú¬O¦³±NB6­ì¤½¦¡§ï¬°
=IF(INDEX(4:4,SMALL(IF(SUBTOTAL(4,OFFSET($A4,,ROW(1:10)*5-4,,5))-N(OFFSET($A4,,ROW(1:10)*5-5+COLUMN($A:$E)))=0,ROW(1:10)*5-4+COLUMN($A:$E),99),COLUMN(A1)))<>"",INDEX(4:4,SMALL(IF(SUBTOTAL(4,OFFSET($A4,,ROW(1:10)*5-4,,5))-N(OFFSET($A4,,ROW(1:10)*5-5+COLUMN($A:$E)))=0,ROW(1:10)*5-4+COLUMN($A:$E),99),COLUMN(A1))),"")
°}¦C
¦ýı±o¤½¦¡«Ü¤¾ªø^^"
¤£ª¾¤W­z¤½¦¡¯à§_¦A²¤Æ¶Ü?
ÁÂÁ¦U¦ì !

hcm19522

(¤è¦¡ 1 ) B7 $B4:$AX4=B6§ï$B4:$AX4-B6=0
(¤è¦¡ 2 ) B6¤p§ï{=IFERROR(INDEX(4:4,SMALL(IF(SUBTOTAL(4,OFFSET($A4,,ROW(1:10)*5-4,,5))-N(OFFSET($A4,,ROW(1:10)*5-5+COLUMN($A:$E)))=0,ROW(1:10)*5-4+COLUMN($A:$E)),COLUMN(A1))),"")
(¤è¦¡ 3 )(»²§U) B5:AX5=MAX(OFFSET($B4,,INT(COLUMN(E1)/5)*5-5,,5))
B6:AX6{=INDEX(4:4,SMALL(IF($B4:$AY4=$B5:$AY5,COLUMN($B:$AY),99),COLUMN(A1)))&""
­YµL¶·²Ä6¦æ=INDEX(1:1,SMALL(IF($B4:$AY4=$B5:$AY5,COLUMN($B:$AY),99),COLUMN(A1)))&""
µL¶·»²§U µL¶·6¦æ{=INDEX(1:1,SMALL(IF(SUBTOTAL(4,OFFSET($B4,,INT((COLUMN($A:$AW)-1)/5)*5,1,5))=$B4:$AX4,COLUMN($B:$AX),99),COLUMN(A1)))&""

https://blog.xuite.net/hcm19522/twblog/589492626

ziv976688

¦^´_ 3# hcm19522
h¤j:
ÁÙ¯àµL¶·²Ä6¦C¡A§Y¥iª½¨ú²Ä1¦Cªº¹ïÀ³¸¹½X~¯u¬O¥O§Ú¨ØªA~¤Ó¯«¤F
¸U¤À·PÁ±z½ç±Ðªº¦h­Óºë¸Ñ~¨ü¯q¨}¦h~·P®¦