[µo°Ý] ¥X®u¤é´Á

shootingstar

¦U¦ì¥ý¶i
°ÝÃD¦pªþ¥ó¡A½Ð°Ý¦p¦ó¦bL2¿é¤J©m¦W«á¡A©óM2¤ÎN2 ¨Ì§Ç±a¥X¹ïÀ³ªº¥X®u¤é´Á¤Î®y¸¹¡AÁÂÁ¡C ¥X®u¤é´Á-1.zip (7.48 KB)

¤U¸ü¦¸¼Æ: 9

2019-1-28 18:16

p212

¦^´_ 1# shootingstar
¥[¤J»²§UÄæ¦ì³B²z¡A½Ð°Ñ¦Ò¡I

hcm19522

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

shootingstar

¦^´_ 3# hcm19522

½Ð°Ý¤½¦¡¦³»~¶Ü¡H§Úª½±µ½Æ»s¬O±a¤£¥X¸ê®Æªº

{=IFERROR(OFFSET($A$1,SMALL(IF(OFFSET($A$2,,MATCH($L$2,$B$1:$I$1,),31)>0,ROW(A$1:A$31)),ROW(A1)),(MATCH($L$2,$1:$1,)-1)*(COLUMN(A1)=2)),"")}

°}¦C=Ctrl+Shift+Enter

­ã´£³¡ªL

¦pªG¤é´Á¤£­«ÂÐ:
M2/°}¦C¤½¦¡:
=IFERROR(SMALL(IF((B$2:I$36<>"")*(B$1:I$1=L$2),A$2:A$36),ROW(A1)),"")

N2/¤@¯ë¤½¦¡:
=IF(M2="","",VLOOKUP(M2,A:I,MATCH(L$2,$1:$1,),))

Hsieh

¦^´_ 4# shootingstar


    M2°}¦C¤½¦¡
=IF(ROW($A1)>COUNTA(OFFSET($A$1,1,MATCH($L$2,$1:$1,0)-1,COUNT($A:$A),1)),"",SMALL(IF(OFFSET($A$1,1,MATCH($L$2,$1:$1,0)-1,COUNT($A:$A),1)<>"",OFFSET($A$1,1,,COUNT($A:$A),1),""),ROW($A1)))

N2¤½¦¡
=IF($M2<>"",OFFSET($A$1,MATCH($M2,$A:$A,0)-1,MATCH($L$2,$1:$1,0)-1),"")
¥X®u¤é´Á.zip (7.81 KB)

¤U¸ü¦¸¼Æ: 3

2019-1-29 17:31