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

[µo°Ý] ª½¦¡§ï¾î¦¡·j´Mªº¤½¦¡

[µo°Ý] ª½¦¡§ï¾î¦¡·j´Mªº¤½¦¡

  
¨ç¼ÆINDEX+SMALL+IF
·Q±Nª½¦¡ªí®æ·j´M¡A§ï¬°¾î¦¡ªí®æ·j´M
½Ð°Ý¡J
Sheet2! L2ªº¤½¦¡­n«ç»ò½s¼g?
½Ð¦U¦ìÀ°¦£! ÁÂÁÂ!

´ú¸ÕÀÉ :   ª½¦¡§ï¾î¦¡·j´Mªº¤½¦¡.rar (2.75 KB)

¨ç¼ÆINDEX+SMALL+IF
·Q±Nª½¦¡ªí®æ·j´M¡A§ï¬°¾î¦¡ªí®æ·j´M
½Ð°Ý¡J
Sheet2! L2ªº¤½¦¡­n«ç»ò½s¼g?
½Ð¦U ...
papaya µoªí©ó 2019-9-10 02:58



    ¦n¤[¨S¼g¡A´ê¤F¤@¤U

    L2={INDEX($A$2:$J$2,,SMALL(IF($B$4:$J$4=K$2,TRANSPOSE(ROW($2:$10)),1000),ROW(A1)))&""}
   °}¦C¤½¦¡

TOP

¦^´_ 2# KCC

ÁÂÁÂÀ°¦£!µª®×¥¿½T^^
¦A½Ð±Ð
L2={INDEX($A$2:$J$2,,SMALL(IF($B$4:$J$4=K$2,TRANSPOSE(ROW($2:$10)),1000),ROW(A1)))&""}
¬°¤°»ò¬O ,,¡A¦Ó¤£¬O ,
ÁÙ¦³&""¨S¦³§@¥Î¡A#REF!¨S¦³¿ìªkÅã¥ÜªÅ¥Õ¡A¤½¦¡¤¤¦P¼Ëªº&""¦³¤°»ò®t§O©O?
¯à§_·Ð½Ð¸Ñ´b¡CÁÂÁ±z^^

TOP

¦^´_  KCC

ÁÂÁÂÀ°¦£!µª®×¥¿½T^^
¦A½Ð±Ð
L2={INDEX($A$2J$2,,SMALL(IF($B$4J$4=K$2,TRANSPOSE(ROW ...
papaya µoªí©ó 2019-9-11 00:48


°}¦C¤½¦¡
={INDEX($2:$2,,SMALL(IF($B$4:$J$4=K$2,TRANSPOSE(ROW($2:$10)),256),ROW(A1)))&""}

&"" ¬O¥Î¨Ó«ü¨ìªÅ®æ®É¡Aexcel·|Åã¥Ü 0 ¡A¥Î&""Åý¥LÅܦ¨ªÅ¦r¦ê
xls¬°2003(§t)¤w¤Uª©¥»¡AÄæ³Ì¦h´N256¡A©Ò¥H§â1000§ï¦¨ 256¡A¥tindex()ªº²Ä¤@­Ó°Ñ¼Æ½d³òÂX¤j¬°¾ã­Ó 2:2

³oºØ¼gªk«Ü©Ç¡A«Øijºû«ù­ì¨Óª½¦¡¤~¬O¥¿³W¡A­Y­ì©l¸ê®Æ³£¬O¾î¦¡¡A¤£§«³z¹LÂà¸m§ï¦¨ª½¦¡¡A¤~¬O¥¿³~

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2019-9-11 12:49 ½s¿è

¦^´_ 4# KCC
±N1000§ï¦¨256¡A#REF!ÁÙ¬O¨S¦³Åܦ¨ªÅ¥Õ¡F
ÁÙ¦³~¦pªG±N¤½¦¡¤¤ªº&""²¾°£¡Aµª®×¬O¤@¼Ëªº¡A©Ò¥Hı±o&""¨S¦³§@¥Î¡C
®æ¦¡¬O¤½¥qªø´Áªu¥Î¡A§Ú­Ó¤HµLªk¾Õ¦Û§ó§ï^^"

³o¼Ë´N¥i¥H¤F~«D±`·PÁ±zªº¼ö¤ß«ü¾É¡C

TOP

¦^´_  KCC
±N1000§ï¦¨256¡A#REF!ÁÙ¬O¨S¦³Åܦ¨ªÅ¥Õ¡F
ÁÙ¦³~¦pªG±N¤½¦¡¤¤ªº&""²¾°£¡Aµª®×¬O¤@¼Ëªº¡A©Ò¥Hı ...
papaya µoªí©ó 2019-9-11 12:44


index()ªº²Ä¤@­Ó°Ñ¼Æ­n§ï¡A§Ú¦³»¡©ú¡A½Ð¸Ô¾\

TOP

Sheet2
A1:=INDEX('Sheet1(ª½¦¡)'!$A:$E,COLUMN(A$1),ROW(A1))  ¥k©Ô¦ÜJ2, ¤U©Ô5¦æ

K2:=IF('Sheet1(ª½¦¡)'!F2="","",'Sheet1(ª½¦¡)'!F2)  ¥k©Ô¤@®æ/¤U©Ô

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2019-9-12 16:02 ½s¿è

¦^´_ 7# ­ã´£³¡ªL
­ã´£ª©¥D:
±zªº¤½¦¡«Üºë²¡A©öÀ´¹ê¥Î¡A·PÁÂÀ°¦£^^

¥i¯à¬O§Ú¨S¦³»¡²M·¡~
Sheet2¬O¤½¥q²{¦b©Ò¨Ï¥Îªºªí®æ®æ¦¡¡A
¦]¬°§Ú¦bª¦¤å®É¡A§ä¨ìªº¤½¦¡³£¾A¥Î©ó¦pSheet1ª½¦¡ªº®æ¦¡ªí®æ¡A©Ò¥H§Ú¥u¦n¥H¤½¥qªº¾î¦¡®æ¦¡§ï¬°ª½¦¡ªº®æ¦¡ªí®æ¨Óµo°Ý¡A§Æ±æ¯à¥Hª½¦¡®æ¦¡ªº¤½¦¡§ï¬°¾A¥Î¾î¦¡®æ¦¡³æ¯Âªºµo°Ý¡F
¤]´N¬O»¡§Ú»Ý­nªº¬OSheet2!L2ªº¸ÑÃD¤½¦¡(¦]¬°¤½¥q²{¦b¨Ã¨S¦³¦s¦bSheet1ªºªí®æ)¡C
¦pªG±z¤£¤¶·N¡A·Ð½Ð±z«ü±Ð!ÁÂÁ±z!

TOP

L2/°}¦C¤½¦¡:
=LOOKUP("z",IF({1,0},"",SMALL(IF($B$4:$J$4=K$2,$B$2:$J$2),ROW(A1))&""))

TOP

¦^´_ 9# ­ã´£³¡ªL
­ã´£ª©¥D:
·PÁ¦A¦¸¦^ÂСAµª®×¥¿½T¡C

½Ð±Ð±z¤@¤U:¤½¦¡¤¤ªº"z"­^¤å¦r¥À¡A¥Nªí¤°»ò·N¸q?
¦n¹³§ï¬°"a'¡Aµª®×¤]¥¿½T¡A¤½¦¡½s¼gªº«Ü¿W¯S«Ü¶ø§®^^

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD