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

VLOOKUP°ÝÃD

½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

¦^´_ 11# JBY


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

TOP

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

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-3-1 10:17 ½s¿è

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


°õ¦æµ²ªG:


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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¦n¨Æ­n´£±o°_¡A¬O«D­n©ñ±o¤U¡A¦¨´N§O¤H§Y¬O¦¨´N¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD