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

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

¦^´_ 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

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


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

°õ¦æµ²ªG:



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

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD