³o­n¦p¦ó³]©w(¤w¸Ñ¨M)

koala2099

¬y¤ô¸¹¤@¼Ë¦ý°Ó«~¥N¸¹°Ï§ON¤ÎL¤@¦C
M¤ÎS¤@¦C

koala2099

¦^´_ 2# Hsieh


·PÁ¶W¯Åª©¥D^^
¦b³o¾Ç¨ì¤F«Ü¦h«Ü·PÁ¤j®aªº¨ó§U
¤u§@¤W¤j®a³£«Ü¦£¨­Ãä¯à½Ð±Ðªº¤H¤£¦h
¥u¯à¥Î³Ì²Â³Ì²³æ¤è¦¡³B²z¸ê®Æ
¦b³o¾Ç¨ì¤F«Ü¦h
¯u¦n ÁÂÁÂ~

Hsieh

¦^´_ 1# koala2099

B2°}¦C¤½¦¡
=IF(SUMPRODUCT((¥N¸¹!$A$2:$A$28=¾ã²z!A2)*((LEFT(¥N¸¹!$B$2:$B$28)="N")+(LEFT(¥N¸¹!$B$2:$B$28)="L"))),INDIRECT("¥N¸¹!B"&MIN(IF((¥N¸¹!$A$2:$A$28=¾ã²z!A2)*((LEFT(¥N¸¹!$B$2:$B$28)="N")+(LEFT(¥N¸¹!$B$2:$B$28)="L")),ROW($B$2:$B$28),""))),"")

G2°}¦C¤½¦¡
=IF(SUMPRODUCT((¥N¸¹!$A$2:$A$28=¾ã²z!F2)*((LEFT(¥N¸¹!$B$2:$B$28)="M")+(LEFT(¥N¸¹!$B$2:$B$28)="S"))),INDIRECT("¥N¸¹!B"&MIN(IF((¥N¸¹!$A$2:$A$28=¾ã²z!F2)*((LEFT(¥N¸¹!$B$2:$B$28)="M")+(LEFT(¥N¸¹!$B$2:$B$28)="S")),ROW($B$2:$B$28),""))),"")