¦P®É¤ñ¹ï¤G­Ó­È

papaya

¤U¸ü (137.87 KB)

2018-4-22 11:46

´ú¸ÕÀÉ ¦P®É¤ñ¹ï¤G­Ó­È.rar (5.79 KB)

¤U¸ü¦¸¼Æ: 16

2018-4-22 11:47

¤å¦r»¡©ú¡J
¥Ø«eK2ªº¤½¦¡¥u«ü©w¤ñ¹ïBÄæ¡JHÄ檺¦U³æ¦C¦³Åã¥Ü$J$1­È¡AµM«á±NK2ªº¤½¦¡¥k²¾¤U©Ô¶ñº¡¡A
    «hKÄæ¡JRÄæ¥iÅã¥Ü¦bBÄæ¡JHÄ榳¤ñ¹ï$J$1­Èªº©¹¤U$J$2¦C¤§AÄæ¡JHÄæ¸ê®Æ¡C
    EX¡JSheet1ªºJ2=1®É¡A«hÅã¥ÜK2¡JR4ªº¸ê®Æ
EX¡JSheet2ªºJ2=2®É¡A«hÅã¥ÜK2¡JR4ªº¸ê®Æ

·Q±NK2¤½¦¡«ü©wBÄæ¡JHÄæ¦U³æ¦C¦³Åã¥Üªº¤ñ¹ï­È§ï¬°·í¦C¦P®É¦³Åã¥Ü$I$1©M$J$1¤G­Ó¼Æ­È¡A
§YK2¤½¦¡§ïÅÜ«á¡A«hSheet1©MSheet2ªºKÄæ¡JRÄæÀ³¥uÅã¥ÜK2¡JR2ªº¸ê®Æ¡C

½Ð±Ð¡J
K2ªº¨ç¼Æ¤½¦¡­n¦p¦ó­«·s¦A½s¼g?
ÁÂÁÂ!

papaya

²¤Æ»¡©ú¡J
¤ñ¹ï¤@­Ó­È($J$1)
K2
=INDEX(A:A,SMALL(IF($B$2:$H$50=$J$1,ROW($B$2:$H$50)+$J$2,4^8),ROW(A1)))&""

¤ñ¹ï¤G­Ó­È($J$1&$I$1)
K2
=INDEX(A:A,SMALL(IF(($B$2:$H$50=$J$1)*($B$2:$H$50=$I$1),ROW($B$2:$H$50)+$J$2,4^8),ROW(A1)))&""

½Ð±Ð¡J
¤ñ¹ï¤G­Ó­È($J$1&$I$1)
K2¥¿½Tªº¨ç¼Æ¤½¦¡­n¦p¦ó­«·s¦A½s¼g?
ÁÂÁÂ!

­ã´£³¡ªL

¦^´_ 2# papaya

¥u­n¦³¤@­Ó¬Û¦P´N¦¨¥ß???
°}¦C¤½¦¡
=INDEX(A:A,SMALL(IF(MMULT(COUNTIF($I$1:$J$1,$B$2:$H$50),{1;1;1;1;1;1;1})>0,ROW($2:$50)+$J$2,4^8),ROW($A1)))&""

¦P®É¨â­Ó¬Û¦P:
=INDEX(A:A,SMALL(IF(MMULT(COUNTIF($I$1:$J$1,$B$2:$H$50),{1;1;1;1;1;1;1})=2,ROW($2:$50)+$J$2,4^8),ROW($A1)))&""

papaya

¦^´_ 3# ­ã´£³¡ªL

·PÁ±zªº¦^ÂЩM¼ö¤ßªº«ü¾É¡C
µª®×¥¿½T¸Û¦p»Ý¨D!·P®¦!