在VBA中,字典是一个很重要的概念,有点你我们生活中用到的字典,(你可以通过pinyin或部首,查找到某个字),VBA中的字典,可以根据一个关键字(key)查询一个值(item),且关键字(key)是唯一的,不能重复,但是值(item)可以重复。利用字典这一特性唯一关键字---值的特性,可以快速进行分类汇总。
上一篇,讲了VBA中运用字典进行分类汇总的四种情况
1)单条件单列汇总
2)单条件多列汇总
3)多条件单列汇总
4)多条件多列汇总
但是前面这四种都是针对单一对象(如商品A或业务员张三商品A)进行的汇总,如果要做一个多个对象的汇总表,该如何做呢。比如,已知原始表见下方
表1:原始表:
产品 | 月份 | 费用 |
A | 1月 | 10 |
B | 1月 | 20 |
C | 1月 | 1000 |
A | 1月 | 10 |
B | 1月 | 200 |
C | 2月 | 309 |
D | 2月 | 24 |
E | 2月 | 34 |
A | 2月 | 33 |
B | 2月 | 12 |
C | 2月 | 3000 |
D | 3月 | 20 |
A | 3月 | 50000 |
C | 3月 | 2 |
E | 3月 | 34 |
A | 3月 | 800 |
D | 3月 | 30 |
A | 4月 | 10 |
B | 4月 | 20 |
C | 4月 | 1000 |
A | 4月 | 10 |
B | 5月 | 200 |
C | 5月 | 309 |
D | 5月 | 24 |
E | 5月 | 34 |
A | 5月 | 33 |
B | 5月 | 12 |
C | 6月 | 3000 |
D | 6月 | 20 |
A | 6月 | 50000 |
C | 6月 | 2 |
E | 6月 | 34 |
A | 6月 | 800 |
D | 6月 | 30 |
要求,根据上表(表1)数据做成如下格式的表2:分类汇总表
产品 | 1月 | 2月 | 3月 | 4月 | 5月 | 6月 | 合计 |
A |
|
|
|
|
|
|
|
B |
|
|
|
|
|
|
|
C |
|
|
|
|
|
|
|
D |
|
|
|
|
|
|
|
E |
|
|
|
|
|
|
|
分析思路:
表1原始表显然属于一维表,表2分类汇总表属于二维表。由表2可知需要汇总数据的关键字(key)为“产品“+”月份“ 或 “产品”+“合计”,这样的话,我们只需要在字典中将关键字(key)设置为“产品”+“月份” 或 “产品”+“合计”,如“A” & “-” & “1月”,“A” & “-” & “合计”。然后根据表2分类汇总表中的列标和行标确定关键字(key),如“A” & “-‘ & ”1月“,相对坐标为(2,2),将字典中key对应的值item,填入相应的位置(相对坐标(2,2)即可。
VBA代码如下:
Sub tt()
Dim arr, arr1
Dim d As New Dictionary
Dim n As Integer, i As Integer, j As Integer, st As String
Dim rg As Range
Dim r As Integer, c As Integer
n = Range("A65536").End(xlUp).Row
arr = Range(Cells(2, 1), Cells(n, 3))
For i = 1 To n - 1 Step 1
st = arr(i, 1) & "-" & arr(i, 2)
d(st) = d(st) + arr(i, 3)
st = arr(i, 1) & "-" & "合计"
d(st) = d(st) + arr(i, 3)
Next i
Set rg = Range("F1")
r = rg.CurrentRegion.Rows.Count
c = rg.CurrentRegion.Columns.Count
For i = 2 To r Step 1
For j = 2 To c Step 1
st = rg.Offset(i - 1, 0) & "-" & rg.Offset(0, j - 1)
rg.Offset(i - 1, j - 1) = d(st)
Next j
Next i
End Sub