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

VLOOKUP°ÝÃD

¦^´_ 5# 074063
Reply59.jpg
2015-7-15 16:48

1. B11, ÅçÃÒ \ ²M³æ \ ¿é¤J : =$A$2:$A$6

2. A13, ¿é¤J°}¦C¤½¦¡«á, ¦V¤U½Æ»s¦ÜA17 :

{=TEXT(OFFSET(A$1,,SMALL(IF(T(OFFSET(A$1,MATCH($B$11,$A$2:$A$6,0),COLUMN(A:E)))<>"",COLUMN(A:E),1000),ROWS(A$1:A1))),";;;@")}

3. B13, ¿é¤J°}¦C¤½¦¡«á, ¦V¤U½Æ»s¦ÜB17 :

{=TEXT(OFFSET(A$1,MATCH($B$11,$A$2:$A$6,0),SMALL(IF(T(OFFSET(A$1,MATCH($B$11,$A$2:$A$6,0),COLUMN(A:E)))<>"",COLUMN(A:E),1000),ROWS(A$1:A1))),";;;@")}

TOP

¥»©«³Ì«á¥Ñ JBY ©ó 2015-7-15 21:29 ½s¿è
......½Ð°Ý¬O§_¥i¥H¤£§[±Ð¾É¤½¦¡»yªk,  °²¦p¸ê®Æ¦b¥t¤@¤u§@ªí¡@list¡IA:W ,  ¤½¦¡­n¦p¦ó­×§ï.....

1. ¸ê®Æ¦b¥t¤@¤u§@ªí¡@list¡IA:W ,  ¤½¦¡

2. A13, ¿é¤J°}¦C¤½¦¡«á, ¦V¤U½Æ»s¦ÜA34 :

{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A1)))&""}

3. B13, ¿é¤J°}¦C¤½¦¡«á, ¦V¤U½Æ»s¦ÜB34 :

{=OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A1)))&""}

TOP

¦^´_  JBY


    COLUMN(A:Z),50  ½Ð°Ý³o­Ó¬O¤°»ò

¡@A¡GZªí¥Ü¤°»ò¡H¡@50©M1000®t§O¦b¨º¡H
074063 µoªí©ó 2015-7-15 21:38


1. §Aªº¸ê®Æ¦b¥t¤@¤u§@ªí¡@list¡IA1:W6 , ¸ê®Æ¦@­p 22 Äæ¡C

2. ¤½¦¡ , ¥Î COLUMN(A:Z) ={1,2,3,4,¡K¡K..24,25,26}, ¦@­p 26 Äæ,  ¶W¥X¸ê®ÆÄ檺3 Äæ, §@¬°°£¿ù¥Î,

·íµM§A¥i¥H¥Î COLUMN(A:AX) ={1,2,3,4,¡K¡K..48,49,50} ¦@­p 50 Äæ,  ¶W¥Xªº28 Äæ, §@¬°°£¿ù¥Î¡K¡K¡K.

TOP

½d¨Ò  :

1. ¦pªG A1:W6, ³£¦³¸ê®Æ¡C

2. A13, ¿é¤J°}¦C¤½¦¡«á, ¦V¤U½Æ»s¦ÜA38 :
{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A1)))&""}

3. A34 ¤½¦¡ :
{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A22)))&""}
=OFFSET(list!A$1,,SMALL({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,50,50,50,50},22))&""
=OFFSET(list!A$1,,22)&""
ªð¦^ W1 ³Ì«á²£«~, «~¦W

4. A35 ¤½¦¡ :
{=OFFSET(list!A$1,,SMALL(IF(T(OFFSET(list!A$1,MATCH($B$11,list!$A$2:$A$6,0),COLUMN(A:Z)))<>"",COLUMN(A:Z),50),ROWS(A$1:A23)))&""}
=OFFSET(list!A$1,,SMALL({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,50,50,50,50},23))&""
=OFFSET(list!A$1,,50)&""
ªð¦^ A$1 «á ²Ä50Äæ (AY1) ¸ê®Æ, AY1 À³¸Ó¬OªÅ¥Õªº¡C
=0&""
=""
=ªÅ¥Õ

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD