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

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

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

¦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)

¥»©«³Ì«á¥Ñ p212 ©ó 2019-1-29 10:01 ½s¿è

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

¥X®u¤é´Á_¥[»²§UÄæ.zip (9.94 KB)

TOP

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

TOP

¦^´_ 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

TOP

¦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,),))

TOP

¦^´_ 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)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD