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

[µo°Ý] ¸Uµ§¸ê®Æ§Ö³t¤ñ¹ï³B²z

[µo°Ý] ¸Uµ§¸ê®Æ§Ö³t¤ñ¹ï³B²z

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-10-9 23:11 ½s¿è

¤u§@ªí¤@
¬ù¦³¨â¸Uµ§¸ê®Æ

¤u§@ªí¤G
¬ù¦³250µ§¸ê®Æ


·Q±q¤u§@ªí¤@¥u¯d¤U»P¤u§@ªí2²Å¦XªºªÑ²¼¥N¸¹
¤£²Å¦Xªºrows¥þ³¡§R°£±¼

³Ì«á¯d¤Uªº¸ê®Æ·|¥u³Ñ¤U¸ò¤u§@ªí2¤@¼Ë(¬ù250µ§)
¦Ó¤£¬O¨â¸Uµ§¸ê®Æ...

½Ð°Ý¤j¤j­Ì
³o¼Ë±¡ªp
À³¸Ó±o«ç»ò³B²z·|¤ñ¸û§Ö³t
ÁٽЦU¦ì¤j¤j«ü¾É§Þ¥©
ÁÂÁÂ!

¯¬¦U¦ì¤¤¬î´r§Ö!


ªþ¤W excelÀÉ
test1.rar (795.97 KB)
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-5-25 14:57 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß±N¤W¤@©«3­Ó°}¦C´î¬°2­Ó°}¦C,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST_3()
Dim Arr, Brr, Y, R&, i&, j&, ST
'¡ô«Å§iÅܼÆ
ST = Timer
'¡ô¥OSTÅܼƬO ·í¤U®É¶¡
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([¤u§@ªí2!A1], [¤u§@ªí2!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥Hªí2ªºAÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Brr): Y(Brr(i, 1) & "") = i: Next
'¡ô³]¶¶°j°é!¥O¥HªÑ²¼¥N¸¹·íkey,item¬Oi°j°é¼Æ(¦C¸¹),¯Ç¤JY¦r¨å¤¤
Arr = Range([¤u§@ªí1!H1], [¤u§@ªí1!A65536].End(3))
'¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥Hªí1ªºA~HÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!
   If Y(Arr(i, 2) & "") = "" Then GoTo i01
   '¡ô¦pªG¥HªÑ²¼¥N¸¹¬dY¦r¨å±oitem­È¬OªÅªº!´N¸õ¨ìi01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
   R = R + 1
   '¡ô¥ORÅܼƲ֥[1 (µ²ªG­È©ñ¸mªº¦C¸¹)
   For j = 1 To 8: Arr(R, j) = Arr(i, j): Next
   '¡ô³]¶¶°j°é!±NArr°}¦C­È©¹¤WÁÃ,Âл\±¼­ì°}¦C­È
   Y(Arr(i, 2) & "") = ""
   '¡ô¥O¥HªÑ²¼¥N¸¹ªºkey¹ïÀ³ªºitem§ï¬°ªÅªº
i01: Next
With Sheets("¤u§@ªí1")
   .UsedRange.Clear
   '¡ô¥O²M°£Â¸ê®Æ
   .[A1].Resize(R, 8) = Arr
   '¡ô¥OArr°}¦C­È¼g¤JÀx¦s®æ¸Ì,¶W¹L³oÀx¦s®æ½d³òªº°}¦C­È©¿²¤
End With
Set Y = Nothing: Erase Arr, Brr
'¡ô¥OÄÀ©ñÅܼÆ
MsgBox Format(Timer - ST, "0.0’")
'¡ô¥O¸õ¥X´£¥Üµ¡,Åã¥Ü¦¹·í¤U®É¶¡-STÅܼƫáÂà¤Æ¬°¦³1¦ì¤p¼Æªº"?.?¬í"¦r¦ê
End Sub

==============================================================
¥H¤U¬O¾Ç²ß±N¤W¤@Code ±N2­Ó°}¦C´î¬°1­Ó°}¦C,¾Ç²ß¤è®×¦p¤U


Option Explicit
Sub TEST_4()
Dim Brr, Y, R&, i&, j&, ST
'¡ô«Å§iÅܼÆ
ST = Timer
'¡ô¥OSTÅܼƬO ·í¤U®É¶¡
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([¤u§@ªí2!A1], [¤u§@ªí2!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥Hªí2ªºAÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Brr): Y(Brr(i, 1) & "") = i: Next
'¡ô³]¶¶°j°é!¥O¥HªÑ²¼¥N¸¹·íkey,item¬Oi°j°é¼Æ(¦C¸¹),¯Ç¤JY¦r¨å¤¤
Brr = Range([¤u§@ªí1!H1], [¤u§@ªí1!A65536].End(3))
'¡ô¥OBrr°}¦C´«¸Ëªí1ªºA~HÄæÀx¦s®æ­È
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!
   If Y(Brr(i, 2) & "") = "" Then GoTo i01
   '¡ô¦pªG¥HªÑ²¼¥N¸¹¬dY¦r¨å±oitem­È¬OªÅªº!´N¸õ¨ìi01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
   R = R + 1
   '¡ô¥ORÅܼƲ֥[1 (µ²ªG­È©ñ¸mªº¦C¸¹)
   For j = 1 To 8: Brr(R, j) = Brr(i, j): Next
   '¡ô³]¶¶°j°é!±NBrr°}¦C­È©¹¤WÁÃ,Âл\±¼­ì°}¦C­È
   Y(Brr(i, 2) & "") = ""
   '¡ô¥O¥HªÑ²¼¥N¸¹ªºkey¹ïÀ³ªºitem§ï¬°ªÅªº
i01: Next
With Sheets("¤u§@ªí1")
   .UsedRange.Clear
   '¡ô¥O²M°£Â¸ê®Æ
   .[A1].Resize(R, 8) = Brr
   '¡ô¥OBrr°}¦C­È¼g¤JÀx¦s®æ¸Ì,¶W¹L³oÀx¦s®æ½d³òªº°}¦C­È©¿²¤
End With
Set Y = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
MsgBox Format(Timer - ST, "0.0’")
'¡ô¥O¸õ¥X´£¥Üµ¡,Åã¥Ü¦¹·í¤U®É¶¡-STÅܼƫáÂà¤Æ¬°¦³1¦ì¤p¼Æªº"?.?¬í"¦r¦ê
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST_2()
Dim Arr, Brr, Crr, Y, R&, i&, j&, ST
'¡ô«Å§iÅܼÆ
ST = Timer
'¡ô¥OSTÅܼƬO ·í¤U®É¶¡
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([¤u§@ªí2!A1], [¤u§@ªí2!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥Hªí2ªºAÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Brr): Y(Brr(i, 1) & "") = i: Next
'¡ô³]¶¶°j°é!¥O¥HªÑ²¼¥N¸¹·íkey,item¬Oi°j°é¼Æ(¦C¸¹),¯Ç¤JY¦r¨å¤¤
Arr = Range([¤u§@ªí1!H1], [¤u§@ªí1!A65536].End(3))
'¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥Hªí1ªºA~HÄæÀx¦s®æ­È±a¤J°}¦C¤¤
ReDim Crr(1 To UBound(Arr), 1 To 8)
'¡ô«Å§iCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PArr°}¦C,¾î¦V1~8
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!
   If Y(Arr(i, 2) & "") = "" Then GoTo i01
   '¡ô¦pªG¥HªÑ²¼¥N¸¹¬dY¦r¨å±oitem­È¬OªÅªº!´N¸õ¨ìi01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
   R = R + 1
   '¡ô¥ORÅܼƲ֥[1 (µ²ªG­È©ñ¸mªº¦C¸¹)
   For j = 1 To 8: Crr(R, j) = Arr(i, j): Next
   '¡ô³]¶¶°j°é!±NArr°}¦C­ÈÁèìCrr°}¦C¸Ì
   Y(Arr(i, 2) & "") = ""
   '¡ô¥O¥HªÑ²¼¥N¸¹ªºkey¹ïÀ³ªºitem§ï¬°ªÅªº
i01: Next
With Sheets("¤u§@ªí1")
   .UsedRange.Clear
   '¡ô¥O²M°£Â¸ê®Æ
   .[A1].Resize(R, 8) = Crr
   '¡ô¥OCrr°}¦C­È¼g¤JÀx¦s®æ¸Ì
End With
Set Y = Nothing: Erase Arr, Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
MsgBox Format(Timer - ST, "0.0’")
'¡ô¥O¸õ¥X´£¥Üµ¡,Åã¥Ü¦¹·í¤U®É¶¡-STÅܼƫáÂà¤Æ¬°¦³1¦ì¤p¼Æªº"?.?¬í"¦r¦ê
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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

Option Explicit
Sub TEST()
Application.ScreenUpdating = False
'¡ô¥O¿Ã¹õµe­±¤£ÀHµ{§ÇÅܤƵ²ªG
Dim Brr, Y, R&, i&, T$, ST, S
'¡ô«Å§iÅܼÆ
ST = Timer
'¡ô¥OSTÅܼƬO ·í¤U®É¶¡
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([¤u§@ªí2!B1], [¤u§@ªí2!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥Hªí2ªºA~BÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Brr): T = Brr(i, 1): Y(T) = 1: Next
'¡ô³]¶¶°j°é!¥O¥HªÑ²¼¥N¸¹·íkey,item¬O1,¯Ç¤JY¦r¨å¤¤
Brr = Range([¤u§@ªí1!B1], [¤u§@ªí1!B65536].End(3))
'¡ô¥OBrr°}¦C´«¸Ëªí1ªºBÄæÀx¦s®æ­È
For i = 1 To UBound(Brr): T = Brr(i, 1): Brr(i, 1) = Y(T): Y(T) = "": Next
'¡ô³]¶¶°j°é!±NBrr¦^°j°é°}¦C­È´«¦¨¬dY¦r¨å±o¨ìªºitem­È,
'¨ÃÅý­«½Ækey¬dY¦r¨åªºitem­ÈÅܦ¨ ªÅ¦r¤¸,¥u¯d¤@µ§­È¬O1

[¤u§@ªí1!I1].Resize(UBound(Brr), 1) = Brr
'¡ô¥Oªí1ªºIÄæ·í»²§UÄæ,¥OBrr°}¦C­È¼g¤JIÄ椤
With Range([¤u§@ªí1!I1], [¤u§@ªí1!A65536].End(3))
   .Sort KEY1:=.Item(9), Order1:=1, Header:=1, Orientation:=1
End With
'¡ô¥O¥HIÄ欰±Æ§Ç°ò·Ç,°µ¦³¼ÐÃD¦CªºÁa¦V¶¶±Æ§Ç
R = [I1].End(xlDown).Row
'¡ô¥ORÅܼƬO±Æ§Ç«á IÄæ³Ì«á¤@­Ó¦³¤º®eªºÀx¦s®æ¦C¸¹
Rows(R + 1 & ":65536").Clear
'¡ô¥OIÄæ¬OªÅ®æªº¦C³q³q²M°£
'¦]¬°¦³±Æ§ÇªºÃö«Y,IÄæ¬OªÅ®æªº¦C³QÀ½¨ì«á¤è¤F
[I:I].Clear
'¡ô¥O³oIÄæ(»²§UÄæ)¥\¦¨¨­°h!°µ²M°£
Set Y = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
S = Format(Timer - ST, "0.0’")
MsgBox Format(Timer - ST, "0.0’")
'¡ô¥O¸õ¥X´£¥Üµ¡,Åã¥Ü¦¹·í¤U®É¶¡-STÅܼƫáÂà¤Æ¬°¦³1¦ì¤p¼Æªº"?.?¬í"¦r¦ê
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST() '¡ô
Application.ScreenUpdating = False
Dim Brr, Y, R&, i&, T$, ST
ST = Timer
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([¤u§@ªí2!B1], [¤u§@ªí2!A65536].End(3))
For i = 1 To UBound(Brr): T = Brr(i, 1): Y(T) = 1: Next
Brr = Range([¤u§@ªí1!B1], [¤u§@ªí1!B65536].End(3))
For i = 1 To UBound(Brr): T = Brr(i, 1): Brr(i, 1) = Y(T): Y(T) = "": Next
[¤u§@ªí1!I1].Resize(UBound(Brr), 1) = Brr
With Range([¤u§@ªí1!I1], [¤u§@ªí1!A65536].End(3))
   .Sort KEY1:=.Item(9), Order1:=1, Header:=1, Orientation:=1
End With
R = [I1].End(xlDown).Row
Rows(R + 1 & ":65536").Clear
[I:I].Clear
Set Y = Nothing: Erase Brr
MsgBox Format(Timer - ST, "0.0’")
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 10# mhl9mhl9


TO
mhl9mhl9

ÁÂÁ¤j¤j¸Ñ»¡µ{¦¡½XªºÅ޿趶§Ç
§Ú¦A¦n¦n¬ã¨s¬Ý¬Ý!
ÁÂÁÂ!!¥H«á½Ð¦h¦h«ü±Ð!!
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

¦^´_ 1# bioleon69
sheet1ªº20000µ§¸ê®Æ¸Ë¶id,sheet2ªº500µ§¸ê®Æ¸Ë¶idd,¤Z¬Od¨½¦³ddªº½s¸¹ªº¸ê®Æ¸Ë¶iddd,³Ì«á§R°£sheet1ªºÂ¸ê®Æ¶K¤Wddd,´N¬O©Ò»Ýµ²ªG.
¥Ñ©ó¦r¨å°õ¦æ±o«Ü§Ö,¤W­zÅÞ¿è²M·¡,¦pªG¸ê®Æ·½¦³ÅܤƩ岪G­n¨D¦³ÅܤÆ,µ{¦¡½X«Ü®e©ö­×§ï.©Ò¥HÃþ¦ü½ÒÃD§Ú³£³ßÅw¥Î¦r¨å³B²z.
lmh

TOP

¥»©«³Ì«á¥Ñ bioleon69 ©ó 2017-10-11 21:21 ½s¿è

¦^´_ 8# Hsieh

ÁÂÁÂ
ziv976688
Kubi
mhl9mhl9
Hsieh
ªº«ü¾É¤èªk

ziv976688
ÁÂÁ±zªº«ü¾É,§Ú¤F¸Ñ·N«ä¤F
¦]¬°§Ú¥D­n¬O·Q§â¤u§@ªí1ªº¼Æ¾Ú¯d¤U»Ý­n¬Ý±o¦Ó¤w
¨â¸Uµ§¥e¤F¤Ó¦h®e¶q..
¥Ø«e¬O¦b¥[°j°é§PÂ_§âNAªº¥þ³¡§R°£«á
¤u§@ªí2¸ê®Æ½Æ»s°_¨Ó
¦A¸õ¨ì¤u§@ªí1,§â¨â¸Uµ§§R°£
µM«á¦A±N¤ñ¹ï«áªºVALUE­È¶K¨ì¤u§@ªí1
³o¬O¥Ø«e¤p§Ìªº·ÓµÛ±zªº»¡©úºtÅܪº¸Ñ¨M¤èªk ·PÁ·PÁÂ


KUBI
·PÁ´£¿ô,§Úª¾¹D­ì¦]¬O¤°»ò¤F «¢
¤u§@ªí2ªº¼Æ¾Ú¤@­Ó¤ë¤~·|¤½¥¬¤@¦¸
¤u§@ªí1ªº¬O¨C©P,¤¤¶¡¹j¤F¤T©P¨S§ó·s
¸ê®Æ¦³²§°Ê

mhl9mhl9
·PÁ±zªº«ü¾É
¤£¹L¤p§Ìªºµ{«×¦ü¥GÁÙ¤£¤Ó°÷
¦AºCºC§l¦¬¤¤...
«¢ µ{¦¡½Xµy·L°ª²`¤FÂI©Ô!!
¤£¹L³o­Ó¦r¨åªº¥\¯à³t«×­Ë¬O®¼§Ö!!
(PS,¤£¬O§@·~©Ô!..¤p§Ì¦Û¤v¨C©P°lÂܪѲ¼¥Îªº..¦Û¥Î..¦Û¥Î)

Hsieh
·PÁª©¤j...³o°ÊºA¹Ï¤ù¯u°÷¤ûB
¥Ø«e·ÓµÛ¿ý»s«á(µ{¦¡½X¥u¦³¨â¦æ!)
¦Û¦æ¿ý»s«á>§R°£¤u§@ªí1ªº2¸Uµ§¸ê®Æ
¦A±N¶i¶¥¿z¿ï«áªº¸ê®Æ¶K¨ì¤u§@ªí1

¥Ø«e¨â­Ó¤ñ¹ï¤èªk¤J¤â ·PÁ·PÁ¦U¦ì¤j¤j!!
VBA ±q0¶}©l
¥ý±q¾Ç·|¬ÝªºÀ´¶}©l
¥ý±q·|¦³°ò¥»­×§ï¯à¤O¶}©l
¤@¨B¤@¨B¾Ç²ß¤¤

TOP

¦^´_ 1# bioleon69

¶i¶¥¿z¿ï

    play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

test1.rar (784.03 KB)
sheets(1)ªº«ö¶s¥i¥H§¹¦¨§Aªº§@·~
lmh

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD