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

¨âµ§¸ê®Æ¶¡±µ¤ñ¹ï(¨Ï¥Îvlookup)

¨âµ§¸ê®Æ¶¡±µ¤ñ¹ï(¨Ï¥Îvlookup)

§Ú¦³¨âµ§¸ê®Æ­n¤ñ¹ï¡A¥i¬O§Ú­n¤ñ¸ûªºÄæ¦ì¸ê®Æ¨Ã¤£§¹¥þ¬Û¦P¡A¨Ò¦p¡A¦bA¤u§@ªí´ò¤H¶¤¥HLakersªí¥Ü¡A¦ý¦bB¤u§@ªí´ò¤H¶¤¬O¥HLALªí¥Ü
¦]¦¹§Ú·Q§Q¥Îvlookupªº¤è¦¡¶¡±µ¤ñ¸û¸ê®Æ
¥H¤U¬O§ÚªºVBAµ{¦¡½X¡A¥i¬O°õ¦æ¤W«o¦³¿ù»~¡A½Ð°Ý§Ú³o¼Ë¨Ï¥Î¹ï¶Ü?¥i¥H³o¼Ë¥Î¶Ü?¦³¨ä¥L¤èªk¯à¹F¦¨§Úªº¥Øªº¶Ü?ÁÂÁÂ
Sub aa()

Dim mDic As Object
Dim mWk1 As Workbook
Dim mSht1 As Worksheet
Dim mRng As Range
Dim vRng As Range
Dim E As Range

Set mDic = CreateObject("Scripting.Dictionary")
Set mWk1 = Workbooks("Mapping1")
With mWk1
    Set mSht1 = .Worksheets(2)
    With mSht1
        Set mRng = .Range("b2:b" & .[b65536].End(xlUp).Row)
    End With
    Set vRng = .Worksheets(3).Range("a2:b11")
    For Each E In mRng
        If mDic.Exists(E.Value) = False Then
          mDic(Application.WorksheetFunction.vlookup(E.Value, vRng, 2, 0) & Application.WorksheetFunction.vlookup(E.Offset(, 2).Value, vRng, 2, 0)) = E.Offset(, 4).Resize(,2)
        End If
    Next
End With

    With Worksheets(1)

        For Each E In .Range(.[g2], .[g2].End(xlDown))

            E.Offset(, 8).Resize(, 2) = mDic(E.Value & E.Offset(, 7).Value)

        Next

    End With

End Sub
hi

¦^´_ 1# 01300607


    §A¦Û¤v³£»¡°õ¦æ¥X²{¿ù»~¤F¡AÁٰݳo¼Ë¥i¥H¶Ü?³o¤£¬O«Ü¥Ù¬Þ¶Ü?
¤W¶Ç§AªºÀɮ׬ݬÝ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Map.zip (12.2 KB)

¤£¦n·N«ä¡A³o¬O§ÚªºÀÉ®×
§Ú¥u·Qª¾¹D¬O¦³®Ú¥»¤Wªº¿ù»~¡AÁÙ¬O¤@¨Ç¨ä¥L¤ñ¸û¤pªº¦a¤è©Ò³y¦¨ªº¿ù»~
ÁÂÁ¤j®aÀ°¦£
hi

TOP

¦^´_ 3# 01300607
¬Ý¤£À´§A­n¤ñ¹ï¬Æ»ò?
·Ó²z»¡À³¸ÓSheet3¬O¹ï·Óªí¡A¦ý¬Oµ{¦¡½X«o«D¥H¦¹¬°¹ï·Óªí
ÁÙ¬O­n»¡©ú²M·¡§Aªº¹ï·Ó¼Ð·Ç¡A¤~¯àª¾¹D¦p¦ó­×¥¿¡C
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¹ï¤£°_¡A§Úªºªí¹F¯à¤O¦³ÂI®t
§Ú­n¤ñ¹ïsheet1¸òsheet2ªº¸ê®Æ¡A¥i¬O­n¤ñ¹ïªº¶µ¥Ø¨Ã«D§¹¥þ¬Û¦P¡A
©Ò¥H§Ú±Nsheet2ªº¸ê®Æ¥Hsheet3¬°¹ï·Óªí¡A¦A¸òsheet1ªº¸ê®Æ¶i¦æ¤ñ¹ï¡A
³Ì«á¤ñ¹ï¥X¨Ó§âsheet2ªº¸ê®Æ¶ñ¨ìsheet1
¥i¯à§Úªºµ{¦¡½X¦³ÂI°ÝÃD¡A¤~Åý±z¬Ý¤£À´
ÁÂÁª©¥DÀ°¦£
hi

TOP

§Ú§ä¥X¿ù»~¤F¡A­ì¨Ó¬Ovlookup function ¤ñ¹ï¥X²{error©Ò¥H¾ã­Óµ{¦¡¤~¥X²{°ÝÃD
·PÁª©¥D
hi

TOP

¦^´_ 6# 01300607
¤£¬O«Ü²M·¡§Aªº¤ñ¹ï¼Ò¦¡
¸Õ¸Õ¬Ýµ²ªG¹ï¤£¹ï
  1. Sub ex()
  2. Dim A As Range, d As Object, d1 As Object
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")

  5. With Sheet3 '¼g¤J¹ï·Óªí
  6.    For Each A In .Range(.[A2], .[A2].End(xlDown))
  7.    d(A.Value) = A.Offset(, 1)
  8.    Next
  9. End With
  10. With Sheet2  '¥H¹ï·Ó¦WºÙ¦s¤J¼Æ­È
  11.    For Each A In .Range(.[B2], .[B2].End(xlDown))
  12.    d1(d(A.Value)) = Array(A.Offset(, 4), A.Offset(, 5))
  13.    Next
  14. End With
  15. With Sheet1 '±N¹ï·Ó¼Æ­È¼g¤J
  16.    For Each A In .Range(.[G2], .[G2].End(xlDown))
  17.    A.Offset(, 8).Resize(, 2) = d1(d(A.Value))
  18.    Next
  19. End With
  20. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ªO¥Dªº¤è¦¡§Ú¤§«e³£¨S·Q¹L¡A¨Ì·ÓªO¥Dªº¤è¦¡¤]¯à¹F¨ì§Ú­nªº®ÄªG¡A¦Ó¥B¤£¥Î¨Ï¥ÎVlookup function
¥H¤U¬O§Úµy·L­×§ï¹Lªº¡A°õ¦æ«á¤]¯à¹F¨ì§Ú·Q­nªº¥Øªº
  1. Sub ex()
  2. Dim A As Range, d As Object, d1 As Object
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")

  5. With Worksheets(3)
  6.    For Each A In .Range(.[A2], .[A2].End(xlDown))
  7.    d(A.Value) = A.Offset(, 1)
  8.    Next
  9. End With
  10. With Worksheets(2)
  11.    For Each A In .Range(.[B2], .[B2].End(xlDown))
  12.    d1(d(A.Value) & d(A.Offset(, 2).Value)) = Array(A.Offset(, 4), A.Offset(, 5))
  13.    Next
  14. End With
  15. With Worksheets(1)
  16.    For Each A In .Range(.[G2], .[G2].End(xlDown))
  17.    A.Offset(, 8).Resize(, 2) = d1(A.Value & A.Offset(, 7).Value)
  18.    Next
  19. End With
  20. End Sub
½Æ»s¥N½X
·PÁªO¥DÀ°¦£¡A¤S¾Ç¨ì¤F¡AÁÂÁÂ
hi

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD