Scripting.DictionaryªºÀ³¥Î

john2006168

³o¨â¤Ñ¦b¬Ý¦r¨åªºÀ³¥Î,¦r¨å¹ï¶Hªº¤èªk¦³6­Ó¡GAdd¤èªk¡BKeys¤èªk¡BItems¤èªk¡BExists¤èªk¡BRemove¤èªk¡BRemoveAll¤èªk¡C
ªþ¥ó¤@­Ótest filr ·Q¬Ý¬Ý¦Ñ®v«ç¼g?©M¥Î¨ì¤W­±ªº­þ´X­Ó¤èªk

¤U¸ü (33.05 KB)

2021-5-16 19:43

samwang

¦^´_ 1# john2006168

½Ð¸Õ¬Ý¬Ý¡AÁÂÁ¡C
Sub test()
Dim Arr, xD, Ar(), T, T2%, i&, M%, N%
Set xD = CreateObject("Scripting.Dictionary")
Sheets("¤u§@ªí1").Range("B1:C1").Copy Sheets("Summary").Range("A1")
Arr = Sheets("¤u§@ªí1").[a1].CurrentRegion
ReDim Ar(1 To UBound(Arr), 1 To 2)
For i = 2 To UBound(Arr)
    T = Arr(i, 2): T2 = Arr(i, 3)
    If xD.Exists(T & "") Then
        M = xD(T & "")
        Ar(M, 2) = Ar(M, 2) + T2
    Else
        N = N + 1: xD(T & "") = N
        Ar(N, 1) = T: Ar(N, 2) = T2
    End If
Next
Sheets("Summary").[A2].Resize(N, 2) = Ar
End Sub

samwang

¦^´_ 1# john2006168


¤£¤@¼Ë¼gªk¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
Sub test2()
Dim Arr, xD, T, T2%, i&
Set xD = CreateObject("Scripting.Dictionary")
Sheets("¤u§@ªí1").Range("B1:C1").Copy Sheets("Summary").Range("A1")
Arr = Sheets("¤u§@ªí1").[a1].CurrentRegion
For i = 2 To UBound(Arr)
    T = Arr(i, 2): T2 = Arr(i, 3)
    xD(T & "") = xD(T & "") + T2
Next
Sheets("Summary").[A2].Resize(xD.Count) = Application.Transpose(xD.keys)
Sheets("Summary").[B2].Resize(xD.Count) = Application.Transpose(xD.items)
End Sub

samwang

§ó·s#3¼Ó¡A©ñ¤F±NªíÀY²Ä¤@¦C¤]¥i¥H¼g¤J¦r¨å§Y¥i¡AÁÂÁ¡C
Sub test3()
Dim Arr, xD, T, T2, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Sheets("¤u§@ªí1").[A1].CurrentRegion
For i = 1 To UBound(Arr)
    T = Arr(i, 2): T2 = Arr(i, 3)
    xD(T & "") = xD(T & "")
    xD(T & "") = xD(T & "") + T3
Next
Sheets("Summary").[A1].Resize(xD.Count) = Application.Transpose(xD.keys)
Sheets("Summary").[B1].Resize(xD.Count) = Application.Transpose(xD.items)
End Sub

john2006168

¦^´_ 3# samwang

ÁÂÁ´£¨Ñ¨âºØ¤èªk,ºCºC¾Ç²ß¤¤.
½Ð°Ý¥i§_À°¦£¸ÑÄÀ³o¥y·N«ä?
    xD(T & "") = xD(T & "") + T2

samwang

¦^´_ 5# john2006168

¤£¦n·N«ä4¼Ó¡AT3¶·§ï¬°T2   
xD(T & "") = xD(T & "") + T2   ' Name¦³­«½Æ®É¡AOder ¼Æ­È²Ö¥[

john2006168

¦^´_ 6# samwang

samwang,½Ð°Ý¤@¤U¦pªG§Ú¥[¦h¤@Äæ©M¦h¤@­Ó±ø¥ó,¬Û¦Pªº"LOCATION"¥[¦b¤@°_,½Ð°Ý³o¼Ë«ç»ò¼g.

samwang

¦^´_ 7# john2006168

½Ð´ú¸Õ¬Ý¬Ý¡A±ø¥ó½ÆÂø®É¡A¥i¥H¨Ï¥Î¦r¨å+¼Æ²Õ°}¦C¤ñ¸û®e©ö¸Ñ¡AÁÂÁ¡C

Sub test()
Dim Arr, xD, T, T2, T3, T4, i&, M%, N%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Sheets("¤u§@ªí1").[A1].CurrentRegion
For i = 1 To UBound(Arr)
    T = Arr(i, 2) & "|" & Arr(i, 4)
    T2 = Arr(i, 2): T3 = Arr(i, 3): T4 = Arr(i, 4)
    If xD.Exists(T & "") Then
        M = xD(T & ""): Arr(M, 2) = Arr(M, 2) + T3
    Else
        N = N + 1: xD(T & "") = N
        Arr(N, 1) = T2: Arr(N, 2) = T3: Arr(N, 3) = T4
    End If
Next
Sheets("Summary").[A1].Resize(N, 3) = Arr
End Sub

john2006168

¦^´_ 8# samwang
¥i¥H¹B¦æ,¦ý¬O¦³¨Ç¦a¤è¤£¬O«Ü²z¸Ñ,¥i§_À°¦£µùÄÀ¤@¤U.

samwang

¦^´_ 9# john2006168

¸ÑÄÀ¼g±o¤£¦n®É¡A½Ð¨£½Ì¡A·PÁ¡C

Sub test()
Dim Arr, xD, T, T2, T3, T4, i&, M%, N%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Sheets("¤u§@ªí1").[A1].CurrentRegion '¸ê®Æ¸Ë¤J¼Æ²Õ
For i = 1 To UBound(Arr)
     T = Arr(i, 2) & "|" & Arr(i, 4)    'ÃöÁä±ø¥ó¦ê³s¤@°_·í§@key
     T2 = Arr(i, 2): T3 = Arr(i, 3): T4 = Arr(i, 4)
     If xD.Exists(T & "") Then          'key¦³µL¦b¦r¨å
         M = xD(T & "")                      '§ä¨ì¦Pkeyªº¦ì¸m
         Arr(M, 2) = Arr(M, 2) + T3  '²Ö¥[
     Else
         N = N + 1: xD(T & "") = N  'key¸Ë¤J¦r¨å¥B½s¬y¤ô¸¹
         Arr(N, 1) = T2: Arr(N, 2) = T3: Arr(N, 3) = T4 '±N²Å¦X±ø¥óªº¸ê®Æ¸Ë¨ìµ²ªGªº¼Æ²Õ
     End If
Next
Sheets("Summary").[A1].Resize(N, 3) = Arr  'µ²ªGÅã¥Ü
End Sub