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

VBA´M§ä­«½Æ

¥»©«³Ì«á¥Ñ jackyq ©ó 2018-9-28 08:40 ½s¿è

¦^´_ 10# ¤d·u´M

§A¬Ý¤£À´¬O¦]¬°¦h¤F2¬qÂŦr¸õªO , ¸õªO¤º¤S¦s¦bµÛ U ¦b -1, ¦C¦ì¸m ¤G­È¤§¶¡¨Ó¦^¥æ´À¤Á´«

§A¥i¥H§â¸õªO²¾°£ , µ¥®Ä¦p¤U

    Sub test_01a()
Dim Arr, xD, i&, T$, U&, TM
TM = Timer
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([C2], Cells(Rows.Count, 3).End(3))
For i = 1 To UBound(Arr)
    T = Arr(i, 1):  U = xD(T):  Arr(i, 1) = ""
    If U > 0 Then Arr(i, 1) = "­«ÂÐ" ': xD(T) = -1: U = -1
    'If U < 0 Then Arr(i, 1) = "­«ÂÐ"
    If U = 0 Then xD(T) = i
Next i
[B2].Resize(UBound(Arr)) = Arr
'MsgBox Timer - TM
End Sub

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-9-28 10:04 ½s¿è

¦^´_ 10# ¤d·u´M

U=xD(T) ¥ý¨ú¥X¦r¨åªºITEM­È, ­YT­ÈÁÙ¥¼¥á¶i¦r¨å, U ­È¬°0(©Î"")

U=0  ªí¥Ü©|¥¼±½¹LªºKEY, ¥ýµ¹­È i «O¯d«á¥Î (¬Û·í¤_¦C¸¹)
U>0  ªí¥Ü¬O²Ä2¦¸±½¨ìªº, ¨äITEM­È¬O¤W¤@¦¸«O¯dªº"¦C¸¹", ¶¶¶Õ±N¤W¤@¦¸ªº¦ì¸m¼Ð¥Ü"­«ÂÐ", ¦A±N ITEM §ï¦¨ -1
U<0  ªí¥Ü¤w±½¹L2¦¸¥H¤W, ª½±µ¼Ð¥Ü"­«ÂÐ"

TOP

¦^´_  ¤d·u´M

U=xD(T) ¥ý¨ú¥X¦r¨åªºITEM­È, ­YT­ÈÁÙ¥¼¥á¶i¦r¨å, U ­È¬°0(©Î"")

U=0  ªí¥Ü©|¥¼±½¹Lªº ...
­ã´£³¡ªL µoªí©ó 2018-9-28 10:01

ÁÂÁ·Ǥjªº»¡©ú,²©ú§ã­n,§Ú´¿¸Õ¹L,²Ä1¦¸¥X²{U­È³£¬O0 ,·Q¤£³q¬°¦ó,ÁÙ¦bÆp¤û¨¤¦y·Q»¡0¨ì©³¬OKEY­ÈÁÙ¬OITEM­È,­ì¨Ó¨âªÌ³£¤£¬O,¬O®Ú¥»ÁÙ¨S¼g¤J,¤@»yĵ¿ô¹Ú¤¤¤H!

TOP

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


    ´M§ä¦P¤@Äæ­«Âиê®Æ       
       
(¥uÅã¥Ü¦h­Ó­«ÂФ¤ªº¨ä¤¤ªº1­Ó)

Rept.rar (54.61 KB)

TOP

¦^´_ 14# Qin

¥u¼Ð¥Ü¦³­«ÂЪº[³Ì«á¤@­Ó]

Sub test_03()
Dim Arr, xD, i&, T$, U&, TM
TM = Timer
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([C2], Cells(Rows.Count, 3).End(3))
For i = UBound(Arr) To 1 Step -1
    T = Arr(i, 1):  U = xD(T):  Arr(i, 1) = ""
    If U > 0 Then Arr(U, 1) = "Rept": xD(T) = -1
    If U = 0 Then xD(T) = i
Next i
[B2].Resize(UBound(Arr)) = Arr
MsgBox Timer - TM
End Sub

TOP

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

³o¬O­ì¥»·Q­nªº·j´Mµ²ªG, ¦ý¦Û¤vµo¿ùªº°ÝÃD...
µL½×¦p¦ó, ÁÂÁ­ã¤j..

Rept                code
                40000001
Rept                40000001
                40000002
Rept                40000002
                40000003
                40000004
Rept                40000004
                40000005
Rept                40000005
                40000006
Rept                40000006
Rept                40000006
Rept                40000006

Sub test_04()
Dim Arr, xD, i&, T$, U&, TM   
TM = Timer
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([C2], Cells(Rows.Count, 3).End(3))
For i = UBound(Arr) To 1 Step -1
    T = Arr(i, 1):  U = xD(T):  Arr(i, 1) = ""
    If U > 0 Then Arr(U, 1) = "Rept": xD(T) = -1: U = -0
    If U = 0 Then xD(T) = i
Next i
[B2].Resize(UBound(Arr)) = Arr
MsgBox Timer - TM
End Sub

TOP

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


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

°õ¦æ«e:
20230516_1.jpg
2023-5-16 14:45


°õ¦æµ²ªG:
20230516_2.jpg
2023-5-16 14:45



Option Explicit
Sub test_01()
Dim Arr, xD, i&, T$, U&, TM
'¡ô«Å§iÅܼÆ
TM = Timer
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxDÅܼƬO ¦r¨å
Arr = Range([C2], Cells(Rows.Count, 3).End(3))
'¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é
    T = Arr(i, 1):  U = xD(T):  Arr(i, 1) = ""
    '¡ô¥OTÅܼƬO Arr°}¦C­È(ÃöÁä¦r),
    '¥OUÅܼƬO ÃöÁä¦r¬dxD¦r¨åªºitem­È
    '¥O²M°£±¼Arr°}¦C¸Ìªº­È

    If U > 0 Then Arr(U, 1) = "­«ÂÐ": xD(T) = -1: U = -1
    '¡ô¦pªGUÅܼƤj©ó0,¥Nªí¦¹ÃöÁä¦r¦bArr°}¦Ci¦C­ÈÁÙ¤£¬O "­«ÂÐ",
    '¤]´N¬Oªì¦¸Àˬd¨ì­«ÂÐ!´N¥OArr°}¦CUÅܼƦC­È¬O "­«ÂÐ"¦r¦ê,
    '¥O¦¹ÃöÁä¦rªºitem§ï¬°-1,¥OUÅܼƬO -1

    If U < 0 Then Arr(i, 1) = "­«ÂÐ"
    '¡ô¦pªGUÅܼƤp©ó0,¥NªíArr°}¦C³o°j°é¦C­È¬O­«½Æªº,
    '´N¥OArr°}¦Ci°j°é¦C­È¬O "­«ÂÐ"¦r¦ê

    If U = 0 Then xD(T) = i
    '¡ô¦pªGUÅܼƬO 0,¥Nªí¦¹ÃöÁä¦r¬O²Ä1¦¸¥X²{,
    '¥u¥H¦¹ÃöÁä¦r·íkey,item¬Oi°j°é¼Æ­n¯Ç¤JxD¦r¨å¸Ì´N¦n

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

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD