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

[µo°Ý] §ä­«½Æ¸ê®Æ

[µo°Ý] §ä­«½Æ¸ê®Æ

½Ð°Ý¦p¤U¹Ï
¥x¥_¥«»P°ò¶©¥«³£¦³¤j¦w°Ï¡A­n¦p¦ó±N¦³¬Û¦P°Ï°ìªº¿¤¥«±a¤JD6~D12¤¤


ªþ¥ó¤w¤W¶Ç TEST.zip (12.87 KB)
Kenny

¦^´_ 1# ky2599y

©êºp¡A¾ÇÃÀ¤£ºë¡A¥Ø«e¥u·Q¨ì¥H¤U¿ìªk¡G
¥ý§ïÅܱzªº¸ê®Æ¡A±NA15~B383¨âÄæ¸ê®Æ¹ï´«¡A¤è«K¤§«áªº¤½¦¡¬d¸ß¥Î¡A
¤§«á¦bD6¿é¤J¤½¦¡"=VLOOKUP(C6,$A$16:$B$383,2,FALSE)&"¡B"&VLOOKUP(C6,OFFSET($A$16,MATCH(C6,$A$16:$A$383,0),0,ROWS($A$16:$A$383)-MATCH(C6,$A$16:$A$383,0),2),2,FALSE)"

¤j·§·N¸q¦p¤U¡G
1.«e¥b¬qªº¤½¦¡¥Îvlookup§ä¨ì²Ä¤@µ§²Å¦Xªº¸ê®Æ¡C
2.«á¥b¬q±q«e¥b¬q§ä¨ìªº¸ê®Æ¶}©l¡A"©¹«á¦ì²¾"¨ì³Ì«á¤@µ§¸ê®Æ¡A²Õ¦¨·sªº¬dªí°}¦C¡A¨Ã¦A¤@¦¸¨Ï¥Îvlookup¬d¸ß¡A¥H§ä¨ì²Ä¤Gµ§²Å¦Xªº¸ê®Æ¡C
³o¼Ëªº¤è¦¡¥²¶·°²³]¥u¦³¨âµ§²Å¦Xªº¸ê®Æ¡A­Y¤j©ó¨âµ§´N¤£¾A¥Î¤F¡C

¸É¤Wªþ¥ó¡G
TEST.rar (11.23 KB)

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2015-4-8 10:19 ½s¿è

¦^´_ 1# ky2599y
Åܳq¤è¦¡¡A½Ð°Ñ¦Òªþ¥ó¡I
©w¸q¦WºÙ
¡u¿¤¥«¡v¡A°Ñ·Ó¨ì¿é¤J =§ä­«ÂÐ!$A$2:$A$369
¡u¶mÂí¥«°Ï¡v¡A°Ñ·Ó¨ì¿é¤J =§ä­«ÂÐ!$B$2:$B$369
¡ux¡v¡A°Ñ·Ó¨ì¿é¤J =IF(¶mÂí¥«°Ï=§ä­«ÂÐ!$E2,ROW(¶mÂí¥«°Ï),"")

fig.jpg (109.27 KB)

fig.jpg

TEST_ref.zip (13.2 KB)

TOP

¦^´_ 3# p212


    ÁÂÁ±zªº¨ó§U¡A¦b¤j¦w°Ï¤¤²Ä2­ÓÄæÅã¥Ü¬°¹ü¤Æ¿¤¡A¦ý¹ê»Ú¤WÀ³¬°¥x¤¤¥«¡A§Ú¬Ý¦n¤[·Q¸ÕµÛ­×§ï¦ý«o¦³¦p¤V¤Gª÷­è^^
Kenny

TOP

¦^´_ 2# tku0216


    ÁÂÁ±zªºÀ°¦£!¦ý¥Ñ©ó¬O¦ÒÃD~Äæ¦ì¤£¤è«K§ó°Ê
¦ý¤]µ¹¤F§Ú«Ü¦nªº´£¥Ü¡AÁÂÁÂ~
Kenny

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2015-4-8 12:34 ½s¿è

¦^´_ 4# ky2599y
¦]¬°©w¸q¦WºÙ¤§¡u¿¤¥«¡vªº°Ñ·Ó½d³ò¬° =§ä­«ÂÐ!$A$2:$A$369
¬G3#ªþÀɤ§Àx¦s®æF2À³­×¥¿¬°
=IFERROR(INDEX(¿¤¥«,SMALL(x,COLUMN(A$1))-1),"")
¦V¥k¦V¤U½Æ»s¤½¦¡
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 1# ky2599y


   

¬¡­¶Ã¯1.zip (24.72 KB)

TOP

½Ð°Ý¦p¤U¹Ï
¥x¥_¥«»P°ò¶©¥«³£¦³¤j¦w°Ï¡A­n¦p¦ó±N¦³¬Û¦P°Ï°ìªº¿¤¥«±a¤JD6~D12¤¤......


1] D6, ¿é¤J°}¦C¤½¦¡ (¤@»ô«ö Ctrl + Alt + Enter 3Áä ) :

=INDEX(A$16:A$383,SMALL(IF(B$16:B$383=C6,ROW($1:$368)),1))&IFERROR("¡B"&INDEX(A$16:A$383,SMALL(IF(B$16:B$383=C6,ROW($1:$368)),2)),"")
¦V¤U½Æ»s¤½¦¡


©ÎªÌ


2] D6, ¿é¤J¤@¯ë¤½¦¡ :

=LOOKUP(2,1/(COUNTIF(OFFSET(B$16,,,ROW(A$1:A$368)),C6)*(B$16:B$383=C6)=1),A$16:A$383)&IFERROR("¡B"&LOOKUP(2,1/(COUNTIF(OFFSET(B$16,,,ROW(A$1:A$368)),C6)*(B$16:B$383=C6)=2),A$16:A$383),"")
¦V¤U½Æ»s¤½¦¡

TOP

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD