VBA中字典的每一个key对应一个唯一的item,只要指定key的值,就可以马上返回其对应的item,利用字典这一特性可以实现快速的分类汇总。分类汇总可以分以下四种情况:
1、单条件单列汇总,如求商品A的总销量。
思路:
通过字典中key的唯一性进行汇总,即
字典d(key)=d(key)+销量
若key不存在,字典中增加新的关键字记录;
右key已存在,在原有item基础上加上新的销量。
2、单条件多列汇总,如求商品A的总销量和总金额。
思路:
每个要汇总的列做一个字典,如上列,声明两个字典
字典d1 key:=商品,item:=销量
字典d2 key:=商品,item:=金额
然后参照第1种情况单条件单列进行汇总
3、多条件单列汇总,如求业务员张三商品A的总销量。
思路:
将多条件连接成新的字符串作为关键字生成字典
将“业务员” & “商品名称”形成新的字符串作为key关键字生成字典
字典d key:=业务员 & 商品名称,item:=销量
然后参照第1种情况单条件单列进行汇总
4、多条件多列汇总。如求业务员张三商品A的总销量与总金额。
思路:
首先参照第3种情况多条件单列汇总的思路,将多条件连接成新的字符串作为关键字,这样就将第4种情况多条件多列汇总转化成了第2种情况单条件多列汇总。
字典d1 key:=业务员 & 商品名称,item:=销量
字典d2 key:=业务员 & 商品名称,item:=金额
然后参照第种情况单条件单列进行汇总
以下为实例说明
数据源表如下:
日期 | 商品名称 | 销量 | 单价 | 金额 | 业务员 |
5月1日 | A | 10 | 2 | 20 | 张三 |
5月1日 | B | 8 | 3.5 | 28 | 李四 |
5月1日 | A | 6 | 2 | 12 | 王五 |
5月2日 | C | 8 | 4 | 32 | 张三 |
5月2日 | C | 11 | 4 | 44 | 王五 |
5月2日 | A | 9 | 2 | 18 | 张三 |
5月3日 | B | 20 | 3.5 | 70 | 李四 |
5月3日 | A | 2 | 2 | 4 | 张三 |
5月3日 | C | 9 | 4 | 36 | 张三 |
一、、单条件单列汇总
如求5月1日~5月3日,商品A的总销量
VBA代码
Sub 单条件单列汇总()
Dim d As New Dictionary
Dim arr
Dim n As Integer, i As Integer
arr = Range("A2:F10")
n = UBound(arr, 1)
For i = 1 To n Step 1
d(arr(i, 2)) = d(arr(i, 2)) + arr(i, 3)
Next i
MsgBox "商品A总销量:" & d("A")
End Sub
二、单条件多列汇总
如求5月1日~5月3日,商品A的总销量和总金额
VBA代码
Sub 单条件多列汇总()
Dim d1 As New Dictionary, d2 As New Dictionary
Dim arr
Dim n As Integer, i As Integer
arr = Range("A2:F10")
n = UBound(arr, 1)
For i = 1 To n Step 1
d1(arr(i, 2)) = d1(arr(i, 2)) + arr(i, 3)
d2(arr(i, 2)) = d2(arr(i, 2)) + arr(i, 5)
Next i
MsgBox "商品A的总销量:" & d1("A") & Chr(10) & "商品A的总金额:" & d2("A")
End Sub
三、多条件单列汇总 (将相关条件连接成新的字符串,作为key)
如求5月1日~5月3日,业务员张三商品A的总销量
VBA代码
Sub 多条件单列汇总()
Dim d As New Dictionary
Dim arr
Dim i As Integer, n As Integer
n = Range("A1").CurrentRegion.Rows.Count - 1
ReDim arr(1 To n, 1 To 2)
For i = 1 To n Step 1
arr(i, 1) = "业务员" & Cells(i + 1, 6) & "商品" & Cells(i + 1, 2)
arr(i, 2) = Cells(i + 1, 3)
d(arr(i, 1)) = d(arr(i, 1)) + arr(i, 2)
Next i
MsgBox "业务员张三商品A总销量:" & d("业务员张三商品A")
End Sub
四、多条件多列汇总
如求5月1日~5月3日,业务员张三商品A的总销量与总金额
VBA代码
Sub 多条件多列汇总()
Dim d1 As New Dictionary, d2 As New Dictionary
Dim i As Integer, n As Integer
Dim arr
n = Range("A1").CurrentRegion.Rows.Count - 1
ReDim arr(1 To n, 1 To 3)
For i = 1 To n Step 1
arr(i, 1) = "业务员" & Cells(i + 1, 6) & "商品" & Cells(i + 1, 2)
arr(i, 2) = Cells(i + 1, 3)
arr(i, 3) = Cells(i + 1, 5)
d1(arr(i, 1)) = d1(arr(i, 1)) + arr(i, 2)
d2(arr(i, 1)) = d2(arr(i, 1)) + arr(i, 3)
Next i
MsgBox "业务员张三商品A总销量:" & d1("业务员张三商品A") & Chr(10) & "业务员张三商品A总金额:" & d2("业务员张三商品A")
End Sub