ªð¦^¦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

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

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 : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD