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

[µo°Ý] vlookup³t«×ºC¡A¨Ï¥Îvba¨ú¥Nªºµ{¦¡½X

[µo°Ý] vlookup³t«×ºC¡A¨Ï¥Îvba¨ú¥Nªºµ{¦¡½X

·í¹J¨ì¸ê®Æ¶q¤Q´X¸Uµ§ªº±¡ªp¤U¡A¨Ï¥Îvlookup¨ç¼Æ³t«×·|«ÜºC
¸ß°Ýgoogle¤j®v¦³³o»ò¤@¬qµ{¦¡½X
¦ý¬O¸ÕµÛ®M¡A·|¥X²{·¸¦ìªº¿ù»~¡A½Ð°Ý¬O§_¯àÀ°¦£­×§ïµ{¦¡½X¡AÁÂÁÂ!

5.jpg
2018-6-15 14:49

ss.jpg
2018-6-15 14:47

¨ú¥NVlookup.tar (318 KB)

try
dim i as Variant, r as Variant

TOP

¦^´_ 2# ikboy

ikboy §A¦n:

½Ð°Ý¬Oª½±µ¥[¤J³o¥yµ{¦¡½X¶Ü?

TOP

§ó§ï dim i %, r% ¬° dim i as Variant, r as Variant

TOP

¦^´_ 1# chiang0320

¸Õ¸Õ¬Ý
  1. Option Explicit
  2. 'Option Explicit ¬° ¦b¼Ò²Õ¼h¦¸¤¤±j­¢¨C­Ó¦b¼Ò²ÕùتºÅܼƳ£¥²¶·©ú½Tªº«Å§i¡C
  3. '³o¬O½s¼gµ{¦¡©ö©ó°»¿ùªº¦n²ßºD
  4. Sub Ex()
  5.     Dim d As Object, E As Range, Ar(), T As Date
  6.     T = Time
  7.     Debug.Print "µ{¦¡¶}©l®É¶¡ : " & T   '«ü¥O->À˵ø->§Y®É¹Bºâµøµ¡ :  ¬d¬Ýµ{¦¡°_©l®É¶¡
  8.     'Dim i%= i As Integer
  9.     'Integer ¸ê®Æ«¬ºA Integer ÅܼƫY¥H½d³ò¬° -32,768 ¨ì 32,767 ¤§ 16 ¦ì¤¸ (2 ­Ó¦ì¤¸²Õ) ¼Æ¦rªº§Î¦¡Àx¦s¡CInteger ªº«¬ºA«Å§i¦r¤¸¬O¦Ê¤À¤ñ²Å¸¹(%
  10.     '********** ¤£·|·¸¦ì  ***********
  11.     Dim i As Long  '= i&
  12.     'Long ¸ê®Æ«¬ºA
  13.     'Long (ªø¾ã¼Æ)ÅܼƫY¥H½d³ò±q -2,147,483,648 ¨ì 2,147,483,647 ¤§ 32 ¦ì¤¸ (4 ­Ó¦ì¤¸²Õ) ¦³¸¹¼Æ¦r§Î¦¡Àx¦s¡CLong ªº«¬ºA«Å§i¦r¤¸¬° &¡C '

  14.     Set d = CreateObject("scripting.dictionary")  '¦r¨åª«¥ó
  15.     With Sheets("p10")
  16.         For Each E In .Range(.[a1], .[a1].End(xlDown))
  17.             d(E.Value) = Array(E.Offset(, 2), E.Offset(, 3))
  18.             'e.Value > ¦r¨åª«¥óªºÃöÁä¦r(key) ¾É¤J Array(e.Offset(, 2), e.Offset(, 3))
  19.         Next
  20.     End With
  21.     With Sheets("q72").Range(Sheets("q72").[B2], Sheets("q72").[B2].End(xlDown)).Resize(, 4)
  22.         Ar = .Value
  23.         For i = 1 To UBound(Ar)
  24.             If d.exists(Ar(i, 1)) Then
  25.             'Exists ¤èªk ¦pªG¦b Dictionary ª«¥ó¤¤«ü©wªºÃöÁä¦r¦s¦b¡A¶Ç¦^ True¡A­Y¤£¦s¦b¡A¶Ç¦^ False¡C
  26.                 Ar(i, 3) = d(Ar(i, 1))(0)
  27.                 Ar(i, 4) = d(Ar(i, 1))(1)
  28.             Else
  29.                 Ar(i, 3) = "µL¸ê®Æ"
  30.                 Ar(i, 4) = "µL¸ê®Æ"
  31.             End If
  32.         Next
  33.         .Value = Ar
  34.     End With
  35.     Debug.Print "µ{¦¡µ²§ô®É¶¡ : " & Time, Application.Text(Time - T, "¦@­p[S]¬í")
  36.     '«ü¥O->À˵ø->§Y®É¹Bºâµøµ¡ :  ¬d¬Ýµ{¦¡¹B¦æ³t«×
  37. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE

®M¥Î¤F§Aªºµ{¦¡½X, ¦ý¬O³t«×º¡ºCªº, ¤£ª¾¹D°ÝÃD¥X¦b¨º¨½?
¥i§_½ÐG¤jÀ°§Ú¬Ý¬Ý..
ÁÂÁÂ!!

Dictionary.rar (850.66 KB)

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-4 03:54 ½s¿è

¦^´_ 6# Qin


¦r¨åª«¥óªºKey ¦pªG¿é¤Jªº¬O "¦r¦ê"¡A·|¥[§Ö³t«×
³o©Û·Ç¤j¤w¸g¥Î¹L¤£¤Ö¦¸¡A¦hª¦¤å´Nª¾¹D¤F
¬Ýªº¦³ÂIµh­W¡AÀ°§AÁY±Æ¤F

Option Explicit
Sub Ex()
Dim d As Object, E As Range, Ar(), T As Date
T = Time
Debug.Print "³Ìʽ¿ªÊ¼Ê±¼ä : " & T
Dim i As Long
Set d = CreateObject("scripting.dictionary")
With Sheets("Data")
  For Each E In .Range(.[a1], .[a1].End(xlDown))
    d(E.Value & "") = Array(E.Offset(, 1), E.Offset(, 2), E.Offset(, 3))
  Next
End With
   
With Sheets("Search").Range(Sheets("Search").[a2], Sheets("Search").[a2].End(xlDown)).Resize(, 4)
  Ar = .Value
  For i = 1 To UBound(Ar)
     If d.exists(Ar(i, 1)) Then
      Ar(i, 2) = d(Ar(i, 1) & "")(0)
      Ar(i, 3) = d(Ar(i, 1) & "")(1)
      Ar(i, 4) = d(Ar(i, 1) & "")(2)
    Else
      Ar(i, 2) = "No Data"
      Ar(i, 3) = "No Data"
      Ar(i, 4) = "No Data"
    End If
  Next
  .Value = Ar
End With

Debug.Print "³Ìʽ½áÊøʱ¼ä : " & Time, Application.Text(Time - T, "¹²¼Æ[S]Ãë")
End Sub

Dictionary.rar (834.78 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

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

next

³o¬q§ï¦¨Array·|¦A¥[§Ö³t«×~~

TOP

Sub Ex_01()
Dim xD, Arr, Brr, i&, j%, R&, Tm
Tm = Time
Set xD = CreateObject("scripting.dictionary")
Arr = Range([Data!D1], [Data!A1].Cells(Rows.Count, 1).End(3))
For i = 2 To UBound(Arr): xD(Arr(i, 1) & "") = i: Next
   
Brr = Range([Search!D1], [Search!A1].Cells(Rows.Count, 1).End(3))
For i = 2 To UBound(Brr)
    R = Val(xD(Brr(i, 1) & ""))
    For j = 1 To 3
        Brr(i - 1, j) = "No Data"
        If R > 0 Then Brr(i - 1, j) = Arr(R, j + 1)
    Next j
Next i

[Search!B2:D2].Resize(UBound(Brr) - 1) = Brr
MsgBox Time - Tm
End Sub

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2018-11-5 01:32 ½s¿è

¦^´_ 9# ­ã´£³¡ªL


­ì¨Ó§âÀx¦s®æ¸ê®Æ©ñ¨ì¼Æ²Õ°}¦C«á¡A¦A¸Ë¨ì¦r¨å¸Ì­±
·|¤ñª½±µ®³Àx¦s®æªº­È©ñ¤J¦r¨å¸Ì­±¨Óªº§Ö!!
³o¦³ÂI¹H¤Ïª½Ä±...........

¥H¤U¬O§Ú®³·Ç¤jªºµ{¦¡½X°µ¤@¨Ç­×§ï¨Ó°µ¤ñ¸û
©úÅãtest_1 ¤ñ¸û§Ö

Sub test_1()
Dim T1, d, R%, Arr
T1 = Timer
Set d = CreateObject("scripting.dictionary")
Arr = Range([data!A1], [data!D1].End(4))
For R = 2 To UBound(Arr)
  d(Arr(R, 1) & "") = R
Next R
MsgBox "¦@¯Ó®É" & Round(Timer - T1, 2) & "¬í"
End Sub

Sub test_2()
Dim T1, d, R%
T1 = Timer
Set d = CreateObject("scripting.dictionary")
Sheets("data").Activate
For R = 2 To [A1].End(4).Row
  d(Cells(R, 1) & "") = R
Next R
MsgBox "¦@¯Ó®É" & Round(Timer - T1, 2) & "¬í"
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD