使用字典匯總數(shù)據(jù)

hercules028 2022-06-22 發(fā)布于四川

展開全文

excelperfect

標(biāo)簽：VBA，Dictionary對象

以一個簡單的數(shù)據(jù)集為例，通過唯一的標(biāo)識符對其進行匯總。如果我們有一個水果店，想按售出的商品匯總銷售額。如下圖1所示。

圖1

水果的匯總?cè)缦聢D2所示，使用字典生成這個簡單的匯總。這是展示的第一種方法：根據(jù)唯一條件生成一個求和，而這里唯一的部分是水果的名稱。

圖2

代碼如下：

Sub SumIt()

Dim ar

Dim i As Long

Dim arr As Variant

Dim n As Long

ar = [a1].CurrentRegion.Value

With CreateObject('Scripting.Dictionary')

For i = 1 To UBound(ar, 1)

.Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 6)

arr = Array(.keys, .items)

n = .Count

End With

[T4].CurrentRegion.ClearContents

[T4].Resize(n, 2).Value = Application.Transpose(arr)

End Sub

上述代碼有效的原因是字典將只包含唯一的項。水果是唯一的，所以每個項目都會依次評估和匯總。

.Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 6)

上面一行末尾的6表示示例數(shù)據(jù)集中的第6列（總和），可以將6更改為與數(shù)據(jù)相關(guān)的列。

此外，當(dāng)前項目值將與列表中的下一個相似值相加。循環(huán)完成后，字典的全部內(nèi)容將賦給變量arr。

arr = Array(.keys, .items)

現(xiàn)在需要做的就是定義一個空間來放置數(shù)組（arr）的內(nèi)容。

[T4].Resize(n, 2).Value = Application.Transpose(arr)

其中，n是數(shù)組的長度，2是寬度（兩列的水果和我們對其放置的值）。

第二種方法是，生成匯總但包含表中的每一個唯一行，如下圖3所示。

圖3

上圖3中每個項目有更多詳細信息，可以看到匯總中包含了更多的細節(jié)。這次在列O和列P中進行匯總。

代碼如下：

Sub SumMultiple()

Dim ar As Variant

Dim i As Long

Dim j As Long

Dim n As Long

Dim str As String

n = 1

ar = Cells(10, 1).CurrentRegion.Value

With CreateObject('Scripting.Dictionary')

For i = 2 To UBound(ar, 1)

str = ar(i, 1)

If Not .Exists(str) Then

n = n + 1

For j = 1 To UBound(ar, 2)

ar(n, j) = ar(i, j)

.Item(str) = n

Else

For j = 5 To UBound(ar, 2)

ar(.Item(str), j) = ar(.Item(str), j) + ar(i, j)

End If

End With

[K4].CurrentRegion.ClearContents

[K4].Resize(n, UBound(ar, 2)).Value = ar

End Sub

代碼的關(guān)鍵是：

For j = 5 To UBound(ar, 2)

ar(.Item(str), j) = ar(.Item(str), j) + ar(i, j)

要求和的列從第5列（E）開始，并轉(zhuǎn)到需要求和的列的末尾（在本例中是）6?，F(xiàn)在，如果數(shù)據(jù)集更大，比如說有10列求和，它將依次對這些列求和，上面的循環(huán)從第5列開始，對所有類似的項求和，然后在第14列結(jié)束。這非常有效，而且很容易適應(yīng)需要。你也會驚訝地發(fā)現(xiàn)，即使擴展到數(shù)千行的數(shù)據(jù)集，它的運行速度也很快。

注：本文學(xué)習(xí)整理自thesmallman.com，有興趣的朋友可以到該網(wǎng)站下載示例工作簿，也可以到知識星球App完美Excel社群下載示例工作簿。