VLOOKUP°ÝÃD

074063

¤U¸ü (58.91 KB)

2015-7-13 21:34

½Ð°Ý¦U¦ì¤j¤j¦p¦ó°Ñ·Ó¸ê®ÆA1:F7, ¦bB11¿é¤J²£«~¦W,
A13:B17±a¥X¹ïÀ³¸ê®Æ, ­YªÅ¥Õ«h¸õ¹L¤£±a¥X(¦p«~¦W-C)

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,±N¤Gºûµ²ªG°}¦C¦b¦r¨å´£¨ú/½s¿è/©ñ¦^...¹F¨ì·Q­nªº®ÄªG,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:

¤U¸ü (68.03 KB)

2024-3-1 09:36

°õ¦æµ²ªG:

¤U¸ü (94.13 KB)

2024-3-1 09:37

Option Explicit
Sub TEST()
Dim Brr, Crr(1 To 200, 1 To 2), A, Z, i&, j%, R&, c%, T$, xR As Range
'¡ô«Å§iÅܼÆ:&¬Oªø¾ã¼Æ,%¬Oµu¾ã¼Æ,¨S¦³«ü©w¬O³q¥Î«¬ÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([IV1].End(xlToLeft), [A65536].End(xlUp))
'¡ô¥OBrrÅܼƬO ±a¤J°Ï°ìÀx¦s®æ­Èªº¤Gºû°}¦C
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!¥Oi±q2 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Brr(i, 1)): A = Z(T): R = Z(T & "/r")
   '¡ô¥OTÅܼƬOi°j°é¦C1ÄæBrr°}¦C­È:¥OAÅܼƬO ¥HÅܼƬdZ¦r¨å¦^¶Çªºitem­È
   '¥ORÅܼƬO TÅܼƳs±µ"/r"¦r¦ê²Õ¦¨ªº·s¦r¦ê¬°key,¬dZ¦r¨å¦^¶Çªºitem­È
   If Not IsArray(A) Then A = Crr: R = 1: A(R, 1) = Brr(1, 1): A(R, 2) = Brr(i, 1)
   '¡ô¦pªGAÅܼƤ£¬O¤Gºû°}¦C!´N¥OAÅܼÆÅܬ°¦PCrrªº¤Gºû°}¦C:¥ORÅܼÆ=1:¥ORÅܼƦC1ÄæA°}¦C­È¬O 1¦C1ÄæBrr°}¦C­È
   '¥ORÅܼƦC2ÄæA°}¦C­È¬O i°j°é¦C1ÄæBrr°}¦C­È
   For j = 2 To UBound(Brr, 2)
   '¡ô³]¶¶°j°é!¥Oj±q2 ¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹
      If Brr(i, j) = "" Then GoTo j01
      '¡ô¦pªGi°j°é¦Cj°j°éÄæBrr°}¦C­È¬O ªÅ¦r¤¸!´N¸õ¨ì¼Ð¥Üj01¦ì¸mÄ~Äò°õ¦æ
      R = R + 1
      '¡ô¥ORÅܼƲ֥[1
      A(R, 1) = Brr(1, j)
      '¡ô¥ORÅܼƦC1ÄæA°}¦C­È¬O 1¦Cj°j°éÄæBrr°}¦C­È
      A(R, 2) = Brr(i, j)
      '¡ô¥ORÅܼƦC2ÄæA°}¦C­È¬O i°j°é¦Cj°j°éÄæBrr°}¦C­È
j01: Next
   Z(T) = A: Z(T & "/r") = R
   '¡ô¥Okey¬O TÅܼÆ,ªºitem­È¥H AÅܼƩñ¦^Z¦r¨å¤¤
Next
Set xR = [A11]
'¡ô¥OxRÅܼƬO ª«¥ó A11 Àx¦s®æ
For Each A In Z.KEYS
'¡ô³]³v¶µ°j°é!¥OAÅܼƬO Z¦r¨å¸Ìªºkey
   If Not IsArray(Z(A)) Then GoTo A01
   '¡ô¦pªG¥HAÅܼƬdZ¦r¨å±oitem¤£¬O°}¦C!´N¸õ¨ì¼Ð¥Ü A01¦ì¸mÄ~Äò°õ¦æ
   xR.Resize(Z(A & "/r"), 2) = Z(A)
   '¡ô¥O°Ï°ìÀx¦s®æ¥H ¤Gºû°}¦C­È¼g¤J
   Set xR = xR(1, 4)
   '¡ô¥OxRÅܼÆÅܬ°¦V¥k²¾°Ê¦Û¨­®æºâ°_ªº²Ä4ÄæÀx¦s®æ
A01: Next
End Sub

hcm19522

http://blog.xuite.net/hcm19522/twblog/353362071

074063

¦^´_ 11# JBY


    ·PÁ¤j¤jªº¸Ñµª¡I¤p§ÌÁٻݮø¤Æ¤@·|:)

JBY

½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&""
=""
=ªÅ¥Õ

JBY

¦^´_  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.

074063

¦^´_ 8# JBY


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

¡@A¡GZªí¥Ü¤°»ò¡H¡@50©M1000®t§O¦b¨º¡H

JBY

......½Ð°Ý¬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)))&""}

074063

¦^´_ 6# JBY


    ·PÁÂJBY¤j¤j¸Ñµª :)
¡@½Ð°Ý¬O§_¥i¥H¤£§[±Ð¾É¤½¦¡»yªk,  °²¦p¸ê®Æ¦b¥t¤@¤u§@ªí¡@list¡IA:W ,  ¤½¦¡­n¦p¦ó­×§ï

JBY

¦^´_ 5# 074063

¤U¸ü (76.69 KB)

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))),";;;@")}