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

[µo°Ý] ³Ì§Ö³tªº¤ñ¹ï¸ê®Æ¤è¦¡?

[µo°Ý] ³Ì§Ö³tªº¤ñ¹ï¸ê®Æ¤è¦¡?

¥»©«³Ì«á¥Ñ PKKO ©ó 2015-9-4 16:34 ½s¿è

¤p§Ìªº¤ñ¹ï¸ê®Æ¥HAÄæ¦ì¦³¨â¸Uµ§¸ê®ÆµM«á¤ñ¹ïBÄæ¦ìªº¨â¸Uµ§¸ê®Æ,§@¬°½d¨Ò¸ê®Æ

¥H«e¤@ª½¥H¬°¨Ï¥Î°}¦C°µ¤ñ¹ï¸ê®Æ,¤@©w¬O³Ì§Öªº,³o¬O¤U¤èªºµ{¦¡½X
  1. Sub test_B()
  2. t1 = Hour(Now()) * 3600 + Minute(Now()) * 60 + Second(Now())

  3. Rng = [a1].Resize(20000, 1).Value
  4. rng2 = [b1].Resize(20000, 1).Value
  5. For j = 1 To 20000
  6.     For i = 1 To 10000
  7.         If Rng(i, 1) = rng2(j, 1) Then
  8.             '§ä¨ì¤ñ¹ï¸ê®Æ´N¸õ¶}
  9.             Exit For
  10.         End If
  11.     Next
  12. Next
  13. T2 = Hour(Now()) * 3600 + Minute(Now()) * 60 + Second(Now())
  14. MsgBox "¦@¯Ó®É " & T2 - t1 & " ¬í"

  15. End Sub
½Æ»s¥N½X
«á¨Ó¤~µo²{­ì¨Ó,¤U­±ªº¤è¦¡§ó§Ö,¦ý»Ý­nª`·Nªº­«ÂI¬Omatchªº¤ñ¹ï°}¦C,¤£¥i¯uªº©ñ¸m°}¦C,³t«×·|¤ñ¤W¤èºC,¥B¦³65536¦Cªº­­¨î,¦ý¨Ï¥Î½d³ò°µ¤ñ¹ï,·|¤ñ¤W¤è§Ö450 %....
  1. Sub test_A()
  2. t1 = Hour(Now()) * 3600 + Minute(Now()) * 60 + Second(Now())

  3. rng2 = [b1].Resize(20000, 1).Value
  4. For i = 1 To 20000
  5.     Application.Match(rng2(i, 1), Sheets("test").Range("A:A"), 0)
  6. Next
  7. T2 = Hour(Now()) * 3600 + Minute(Now()) * 60 + Second(Now())
  8. MsgBox "¦@¯Ó®É " & T2 - t1 & " ¬í"
  9. End Sub
½Æ»s¥N½X
¤p§Ì¤£¤~,·Q½Ð°Ý¦U¦ì°ª¤â¤j¤j,ÁÙ¦³§ó§Öªº¤ñ¹ï¤è¦¡¶Ü?
PKKO

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½úªº¤è®×,¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

°õ¦æµ²ªG:
20230509_1.jpg
2023-5-9 14:47



Sub TEST_Vlookup()
Dim TM, Arr, Brr, xRow&, xD, i&
'¡ô«Å§iÅܼÆ
TM = Timer
[B:B].Clear: [J1] = ""
'¡ô¥Oµ²ªGÄæ¸ê®Æ²M°£
xRow = 20000
'¡ô¥O³B²z¦C¼Æ¬O2¸U¦C
Arr = [A1].Resize(xRow)
'¡ô¥OArrÅܼƬO¤Gºû°}¦C,¥HAÄæÀx¦s®æ­È±a¤J
Brr = [C1:D1].Resize(xRow)
'¡ô¥OBrrÅܼƬO¤Gºû°}¦C,¥HC.DÄæÀx¦s®æ­È±a¤J
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxDÅܼƬO¦r¨å
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
    xD(Brr(i, 1)) = Brr(i, 2)
    '¡ô¥O¥HBrr°}¦C²Ä¤@Äæ­È·íkey,item¬OBrr°}¦C²Ä¤GÄæ­È,¯Ç¤JxD¦r¨å¸Ì
Next
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é
    Arr(i, 1) = xD(Arr(i, 1))
    '¡ô¥O¥HArr°}¦C­È¬dxD¦r¨å,±N¦^¶Ç­È¨ú¥N­ì¨Óªº°}¦C­È,
    '­Y¬d¤£¨ì·|¦^¶ÇªÅ¦r¤¸¨ú¥N­ì¨Óªº°}¦C­È

Next
[B1].Resize(xRow) = Arr
'¡ô¥OArr°}¦C­È±a¤JBÄæ
[J1] = Timer - TM
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 10# PKKO


¤ñ¹ï¤å¦rÁÙ¦³ Instr ¤èªk¡A
­n¬Ý¸ê®Æµ²ºc¤Î»Ý¨D¡A¤~¯à¨M©w¨Ï¥Î¦óºØ¤è¦¡¡A
½Ð´£¨Ñ¬ÛÃöÀɮ׸ê®Æ¡A»¡©ú»Ý¨D¤Î¼ÒÀÀµ²ªG¡A

¥t¡A¦¹°ÝÃD¤w»P¦¹ÃD¥»·N¤£¦P¡A«Øij¥t¦æµo©«¸û§´¾A¡A¤]¥iÅý§ó¦h¤H¦@¦P°Ñ»P°Q½×¡I
¡@

TOP

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


    ÁÂÁª©¥D¤j¤j,±zªºµ{¦¡½X¯uªº«Ü¤£¿ù,¤p§Ì¥i§_¦A¦Vª©¤j½Ð±Ð¤@¤U

¶Ç²Îªº¤è¦¡for i ¨ì for j ªº¤è¦¡ §Ú¥i¥H¨Ï¥Î like ¨Ó §ä¥X¬ÛÃö¤å¦r
ex:
m=0
rng=[a1].currentregion
for i = 1 to ubound(rng)
     for j = 1 to ubound(rng,2)
          if rng(i,j) like "*" & "AA" & "*" then
               m=m+1
          endif
     next
next

¨º¥Î¤j¤j±zªº°}¦C+¦r¨åªº¤è¦¡©O?­n¦p¦ó¿ì¨ì?
¦]¬°¤j¤j±zªº¤è¦¡«Ü§Ö³t,¦ý¥Ø«e¥d¦b³oÃäÁٽФj¤j«ü¾É¤@¤U!
PKKO

TOP

¦^´_ 6# PKKO


¡eÂù­«¤ñ¹ï¡f¨ú¥X¹ïÀ³­È¡G
  1. Sub TEST_Vlookup()
  2. Dim TM, Arr, Brr, Crr, Xrr, xRow&, xD, i&
  3. TM = Timer:¡@ [B:B,E:E].Clear:¡@ [M1] = ""
  4. xRow = 20000
  5. Arr = [A1].Resize(xRow)
  6. Brr = [C1:D1].Resize(xRow)
  7. Crr = [F1:G1].Resize(xRow)
  8. ¡@
  9. Set xD = CreateObject("Scripting.Dictionary")
  10. For i = 1 To UBound(Crr)
  11. ¡@xD(Crr(i, 1)) = Crr(i, 2)
  12. Next
  13. ¡@
  14. Xrr = [E1].Resize(xRow) ¡@'(X1)
  15. For i = 1 To UBound(Brr)
  16. ¡@¡@If xD.Exists(Brr(i, 2)) Then
  17. ¡@¡@¡@¡@xD(Brr(i, 1)) = xD(Brr(i, 2))
  18. ¡@¡@¡@¡@Xrr(i, 1) = xD(Brr(i, 2)) ¡@'(X2)
  19. ¡@¡@End If
  20. Next
  21. [E1].Resize(xRow) = Xrr¡@ '(X3)
  22. ¡@
  23. Xrr = [B1].Resize(xRow)
  24. For i = 1 To UBound(Arr)
  25. ¡@¡@If xD.Exists(Arr(i, 1)) Then Xrr(i, 1) = xD(Arr(i, 1))
  26. Next
  27. [B1].Resize(xRow) = Xrr
  28. ¡@
  29. [M1] = Timer - TM
  30. End Sub
½Æ»s¥N½X
(X1)(X2)(X3)³o¤T¦æ¥Î¨Ó¶ñ¤J¢ÓÄæ°µÀˬd¥Î¡A¥i¥H§R°£¡G
Test20150904v2.rar (689.08 KB)
¡@
EXCEL¤ÎVBA¡A¥u¬O´¡ªá«D±M·~¡A¤j·§¼g¼g¡A°Ñ¦Ò§Y¥i¡ã¡ã
¡@

TOP

¦^´_  PKKO


¤§«e§A¦Û¤v¤£¬O¨q¹L¤@¬qµ{¦¡½X
§A¦Û¤v¸Ì­±´N¥Î¦r¨å¦b·j´M
bobomi µoªí©ó 2015-9-4 23:06


¦³®@!
  1. '«Å§iª«¥ó
  2. Dim D As Object
  3. Set D = CreateObject("SCRIPTING.DICTIONARY")  '¦r¨åª«¥ó
  4. '§P©w¬O§_¦s¦b©óª«¥ó¤§¤º
  5. If not D.Exists(cstr(E)) Then
  6.         D.Add cstr(E), I
  7. END IF
  8. Ar=d.keys
  9. '²¾°£ª«¥ó¤ºªº©Ò¦³ª«¥ó
  10.    'D.RemoveAll
½Æ»s¥N½X
¦ý§Ú¤£¾å±o­ì¨Ó¦r¨åª«¥óÁÙ¥i¥H¦³¨º¼Ëªº¥Îªk,§Ú¥u·|³oºØ¦Ó¤w
¦ý¥L¨ººØ¤è¦¡·Pı¤ñ§Ú³oºØ¦n¥Î,´N³s§Ú¤W­zªºµ{¦¡½X,À³¸Ó¤]¥i¥H§ï¬°¥Lªº¤è¦¡(ÁÙ¨S´ú¸Õ¹L³t«×®t²§)
¦]¬°¤£¥Î¶]¤ñ¹ïªº°j°é,¥u­n¶]¤@¦¸¥»¨­,·Pı³t«×§Ö«Ü¦h
PKKO

TOP

¥»©«³Ì«á¥Ñ bobomi ©ó 2015-9-4 23:07 ½s¿è

¦^´_ 6# PKKO


¤§«e§A¦Û¤v¤£¬O¨q¹L¤@¬qµ{¦¡½X
§A¦Û¤v¸Ì­±´N¥Î¦r¨å¦b·j´M

TOP

¢Ñ¡D¢ÒÄ欰³Q¤ñ¹ï¸ê®Æ¡A
®Ú¾Ú¢ÏÄæ¼Æ¾Ú¡A¨ú¥X»P¢ÑÄæ¬Û¦P­È©Ò¹ïÀ³¢ÒÄæ¼Æ­È¡A¨Ã¶ñ¦Ü¢ÐÄæ¡]»PVLOOKUP¬Û¦P¡^¡G¡@ ...
­ã´£³¡ªL µoªí©ó 2015-9-4 19:50



¤j¤j¼F®`!

±zªºµ{¦¡½X¤p§Ì©Ó±µ¤F

³o¬O§Ú¬Ý¹L³Ì§Öªº¤è¦¡¤F

§Ú¥Î¶Ç²Îªº¤ñ¹ï¤è¦¡­n35¬í(¤j¬ù),Vlookupªº¤è¦¡­n5¬í

±zªº¤è¦¡¥u»Ý­n0.1x¬í,³t«×¤W§Ö¤F±Nªñ50­¿....¤Ó«Â¤F

¤p§Ì·Q½Ð±Ð¤@¤U,±zÁ|ªº¨Ò¤l¬OA Äæ¤ñ¹ï C  ,µM«á¨ú¥XD

­Y±NÃD¥Ø§ïA ¤ñ¹ïC ,µM«á±o¨ìD,¦A¥ÎD¥h¤ñ¹ïFµM«á¨ú¥XG,³Ì«á¤~±NG ¿é¤J¨ìB Äæ¦ìªº¸Ü
¤U¤è¬O§Ú¥Î±zªº¤è¦¡¼g¥X¨Óªº,¤£¾å±o¦pªG¬O¤j¤j±z¥»¤H¨Ó¼¶¼gªº¸Ü,¤]¬O·|¥Î¬Û¦Pªº¤è¦¡¶Ü?
ÁÙ¬O·|¥Î¤£¦Pªº¼gªk?(§ó«K§Qor§ó²M·¡or§ó§Ö)
³Â·Ð¤j¤j¤F,¤j¤j±zªº³o¬qµ{¦¡½X,¾_¾Ù¨ì¤p§Ì¤F,ªþÀɪº³¡¤À,·|¦Aªá®É¶¡¬Ý,¦³°ÝÃD¦A¦V¤j¤j±z½Ð±Ð®@!
  1. Sub TEST_Vlookup()
  2. '¢Ñ¡D¢ÒÄ欰³Q¤ñ¹ï¸ê®Æ¡A
  3. '®Ú¾ÚAÄæ¼Æ¾Ú , ¨ú¥X»PCÄæ¬Û¦P­È©Ò¹ïÀ³DÄæ¼Æ­È, ¨Ã¶ñ¦ÜBÄæ(»PVLOOKUP¬Û¦P):

  4. Dim TM, Arr, Brr, xRow&, xD, i&
  5. TM = Timer
  6. [B:B].Clear: [J1] = ""
  7. xRow = 20000
  8. Arr = [A1].Resize(xRow)
  9. Brr = [C1:D1].Resize(xRow)
  10. Crr = [f1:g1].Resize(xRow)
  11. Set xD = CreateObject("Scripting.Dictionary")
  12. 'CªºD
  13. For i = 1 To UBound(Brr)
  14.     xD(Brr(i, 1)) = Brr(i, 2)
  15. Next
  16. '¤ñ¹ïµ²ªG±o¨ìD
  17. For i = 1 To UBound(Arr)
  18.     Arr(i, 1) = xD(Arr(i, 1))
  19. Next
  20. '¦h¥[¤Fpart1-----------------------(FªºG)
  21. For i = 1 To UBound(Arr)
  22.     xD(Crr(i, 1)) = Crr(i, 2)
  23. Next
  24. '¦h¥[¤Fpart2-----------------------(¤ñ¹ïµ²ªG±o¨ìG)
  25. For i = 1 To UBound(Arr)
  26.     Arr(i, 1) = xD(Arr(i, 1))
  27. Next


  28. [B1].Resize(xRow) = Arr
  29. [J1] = Timer - TM
  30. End Sub
½Æ»s¥N½X
PKKO

TOP

«K¥Î¦r¨å¨Ó¥N´ÀVlookup, «Øij®M¥Îlcase or ucase, ¦]¦r¨å·|¤À¤j¤p¼g¡A¦r¨å+°}¦C¡AªÖ©w¬O§Q®`ªº²Õ¦X¡C

TOP

¢Ñ¡D¢ÒÄ欰³Q¤ñ¹ï¸ê®Æ¡A
®Ú¾Ú¢ÏÄæ¼Æ¾Ú¡A¨ú¥X»P¢ÑÄæ¬Û¦P­È©Ò¹ïÀ³¢ÒÄæ¼Æ­È¡A¨Ã¶ñ¦Ü¢ÐÄæ¡]»PVLOOKUP¬Û¦P¡^¡G
  1. Sub TEST_Vlookup()
  2. Dim TM, Arr, Brr, xRow&, xD, i&
  3. TM = Timer
  4. [B:B].Clear: [J1] = ""
  5. xRow = 20000
  6. Arr = [A1].Resize(xRow)
  7. Brr = [C1:D1].Resize(xRow)
  8. Set xD = CreateObject("Scripting.Dictionary")
  9. For i = 1 To UBound(Brr)
  10. ¡@¡@xD(Brr(i, 1)) = Brr(i, 2)
  11. Next
  12. For i = 1 To UBound(Arr)
  13. ¡@¡@Arr(i, 1) = xD(Arr(i, 1))
  14. Next
  15. [B1].Resize(xRow) = Arr
  16. [J1] = Timer - TM
  17. End Sub
½Æ»s¥N½X
¡@
ªþÀÉÁÙ¦³¡eÃþ¡fCOUNTIF¤ÎSUMIF¥Îªk¡A³£¬O¦r¨åÀÉ»P°}¦Cªº¹B¥Î¡]¢±¸Uµ§¡A¢°¬í¤º§¹¦¨¡A¥i¯à¶Ü¡H¡H¡H¡^¡A
«Ü´¶³qªºVBA¡A½Ð¦Û¦æ°Ñ°u¡A®¤¤£¥t§@»¡©ú¡G
Test20150904.rar (374.9 KB)
¡@¡@

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD