¨âµ§¸ê®Æ¶¡±µ¤ñ¹ï(¨Ï¥Îvlookup)
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- OSX10.8
- ³nÅ骩¥»
- excel2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-14
- ³Ì«áµn¿ý
- 2018-3-16
|
¨âµ§¸ê®Æ¶¡±µ¤ñ¹ï(¨Ï¥Î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
|
|
|
|
|
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- OSX10.8
- ³nÅ骩¥»
- excel2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-14
- ³Ì«áµn¿ý
- 2018-3-16
|
Map.zip (12.2 KB)
¤£¦n·N«ä¡A³o¬O§ÚªºÀÉ®×
§Ú¥u·Qª¾¹D¬O¦³®Ú¥»¤Wªº¿ù»~¡AÁÙ¬O¤@¨Ç¨ä¥L¤ñ¸û¤pªº¦a¤è©Ò³y¦¨ªº¿ù»~
ÁÂÁ¤j®aÀ°¦£ |
|
hi
|
|
|
|
|
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- OSX10.8
- ³nÅ骩¥»
- excel2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-14
- ³Ì«áµn¿ý
- 2018-3-16
|
¹ï¤£°_¡A§Úªºªí¹F¯à¤O¦³ÂI®t
§Ún¤ñ¹ïsheet1¸òsheet2ªº¸ê®Æ¡A¥i¬On¤ñ¹ïªº¶µ¥Ø¨Ã«D§¹¥þ¬Û¦P¡A
©Ò¥H§Ú±Nsheet2ªº¸ê®Æ¥Hsheet3¬°¹ï·Óªí¡A¦A¸òsheet1ªº¸ê®Æ¶i¦æ¤ñ¹ï¡A
³Ì«á¤ñ¹ï¥X¨Ó§âsheet2ªº¸ê®Æ¶ñ¨ìsheet1
¥i¯à§Úªºµ{¦¡½X¦³ÂI°ÝÃD¡A¤~Åý±z¬Ý¤£À´
ÁÂÁª©¥DÀ°¦£ |
|
hi
|
|
|
|
|
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- OSX10.8
- ³nÅ骩¥»
- excel2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-14
- ³Ì«áµn¿ý
- 2018-3-16
|
§Ú§ä¥X¿ù»~¤F¡Aì¨Ó¬Ovlookup function ¤ñ¹ï¥X²{error©Ò¥H¾ãÓµ{¦¡¤~¥X²{°ÝÃD
·PÁª©¥D |
|
hi
|
|
|
|
|
- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 246
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2024-11-12
|
¦^´_ 6# 01300607
¤£¬O«Ü²M·¡§Aªº¤ñ¹ï¼Ò¦¡
¸Õ¸Õ¬Ýµ²ªG¹ï¤£¹ï- Sub ex()
- Dim A As Range, d As Object, d1 As Object
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- With Sheet3 '¼g¤J¹ï·Óªí
- For Each A In .Range(.[A2], .[A2].End(xlDown))
- d(A.Value) = A.Offset(, 1)
- Next
- End With
- With Sheet2 '¥H¹ï·Ó¦WºÙ¦s¤J¼ÆÈ
- For Each A In .Range(.[B2], .[B2].End(xlDown))
- d1(d(A.Value)) = Array(A.Offset(, 4), A.Offset(, 5))
- Next
- End With
- With Sheet1 '±N¹ï·Ó¼Æȼg¤J
- For Each A In .Range(.[G2], .[G2].End(xlDown))
- A.Offset(, 8).Resize(, 2) = d1(d(A.Value))
- Next
- End With
- End Sub
½Æ»s¥N½X |
|
¾Ç®üµL²P_¤£®¢¤U°Ý
|
|
|
|
|
- ©«¤l
- 13
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 18
- ÂI¦W
- 0
- §@·~¨t²Î
- OSX10.8
- ³nÅ骩¥»
- excel2010
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-3-14
- ³Ì«áµn¿ý
- 2018-3-16
|
ªO¥Dªº¤è¦¡§Ú¤§«e³£¨S·Q¹L¡A¨Ì·ÓªO¥Dªº¤è¦¡¤]¯à¹F¨ì§Únªº®ÄªG¡A¦Ó¥B¤£¥Î¨Ï¥ÎVlookup function
¥H¤U¬O§Úµy·Lקï¹Lªº¡A°õ¦æ«á¤]¯à¹F¨ì§Ú·Qnªº¥Øªº- Sub ex()
- Dim A As Range, d As Object, d1 As Object
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- With Worksheets(3)
- For Each A In .Range(.[A2], .[A2].End(xlDown))
- d(A.Value) = A.Offset(, 1)
- Next
- End With
- With Worksheets(2)
- For Each A In .Range(.[B2], .[B2].End(xlDown))
- d1(d(A.Value) & d(A.Offset(, 2).Value)) = Array(A.Offset(, 4), A.Offset(, 5))
- Next
- End With
- With Worksheets(1)
- For Each A In .Range(.[G2], .[G2].End(xlDown))
- A.Offset(, 8).Resize(, 2) = d1(A.Value & A.Offset(, 7).Value)
- Next
- End With
- End Sub
½Æ»s¥N½X ·PÁªO¥DÀ°¦£¡A¤S¾Ç¨ì¤F¡AÁÂÁ |
|
hi
|
|
|
|
|