【Excel VBA】字典+数组实现快速汇总
数据合并的要点有三个:
料号和机种号为统计的关键字段 未交数量求和 交期汇总，包含时间和数量示例代码Sub LoadData()
Dim aData, aRes(), lst, iRow, iCol, iKeyCol, Dic
Const COLUMNS_QTY = 21
With Sheets("交货排程")
iKeyCol = 5
lst = .Cells(.Rows.Count, iKeyCol).End(xlUp).Row
aData = .Cells(2, 1).Resize(lst, COLUMNS_QTY).Value
End With
Set Dic = CreateObject("ing.Dictionary")
For iRow = 3 To UBound(aData, 1)
skey = aData(iRow, 5) & "|" & aData(iRow, 6)
If Len(skey) > 1 Then
If Dic.exists(skey) Then
Dic(skey) = Array(Val(aData(iRow, 9)) + Dic(skey)(0), Dic(skey)(1))
Else
Dic(skey) = Array(Val(aData(iRow, 9)), "")
End If
For iCol = 11 To UBound(aData, 2)
If Val(aData(iRow, iCol)) > 0 Then
sdate = VBA.Replace(Format(aData(2, iCol), "m-d"), "-", "/")
Debug.Print Dic(skey)(1) & "," & sdate & "*" & aData(iRow, iCol)
Dic(skey) = Array(Dic(skey)(0), Dic(skey)(1) & "," & sdate & "*" & aData(iRow, iCol))
End If
Next iCol
End If
Next iRow
ReDim aRes(1 To Dic.Count, 1 To 4)
n= 1
For Each d In Dic.keys
akey = Split(d, "|")
aRes(n, 1) = akey(0)
aRes(n, 2) = akey(1)
aRes(n, 3) = Dic(d)(0)
aRes(n, 4) = Mid(Dic(d)(1), 2)
n= n + 1
Next
'结果数组aRes回写到工作表中
With Sheets("结果")
.Range("2:10000").ClearContents
.Cells(2, 1).Resize(Dic.Count, 4).Value = aRes
.Range("a1").CurrentRegion.Borders.LineStyle = xlContinuous
End With
Set Dic = Nothing
End Sub
代码解析
对于需要进行排重统计的应用，使用数组就可以实现，对于每个数据行都需要循环对比数组的全部元素，然而字典对象元素具有唯一性，因此对于排重统计有着独到的优势。
对于每个关键字段组合，需要保存的信息有:两个关键字段，数量和相应的日期，此时数组就可以方便保存多个数据。因此使用字典和数组组合的双剑合璧，功力无敌!
将工作表数据加载到数组的代码，不需要多讲。
aData = .Cells(2, 1).Resize(lst, COLUMNS_QTY).Value
将关键字段进行组合，确定字典的键值。
skey = aData(iRow, 5) & "|" & aData(iRow, 6
下面代码将一个 1 x 2的数组保存在字典对象中，如果skey是一个新的键值，那么将未交数量aData(iRow, 9)做为数组第一个元素，第二元素暂时留空。
如果skey是一个在字典中已经存在的键值，那么Val(aData(iRow, 9)) + Dic(skey)(0)将未交数量进行累加，并保留已有的交期信息Val(aData(iRow, 9)) + Dic(skey)(0)。
If Dic.exists(skey) Then
Dic(skey) = Array(Val(aData(iRow, 9)) + Dic(skey)(0), Dic(skey)(1))
Else
Dic(skey) = Array(Val(aData(iRow, 9)), "")
End I
与此类似，下面代码将汇总交期信息。注意更新字段对象的数组元素时，只能全部重新赋值，而不能使用Dic(skey)(0)="xxx"的形式。
For iCol = 11 To UBound(aData, 2)
If Val(aData(iRow, iCol)) > 0 Then
sdate = VBA.Replace(Format(aData(2, iCol), "m-d"), "-", "/")
Debug.Print Dic(skey)(1) & "," & sdate & "*" & aData(iRow, iCol)
Dic(skey) = Array(Dic(skey)(0), Dic(skey)(1) & "," & sdate & "*" & aData(iRow, iCol))
End If
Next iCol
对于字典中保存的数组元素，无法直接回写到工作表中，因此需要先构建回写的二维数组。Mid(Dic(d)(1), 2)用于去掉交期信息中的第一个逗号。
For Each d In Dic.keys
akey = Split(d, "|")
aRes(n, 1) = akey(0)
aRes(n, 2) = akey(1)
aRes(n, 3) = Dic(d)(0)
aRes(n, 4) = Mid(Dic(d)(1), 2)
n= n + 1
Next
后记
对于这个实例，只用数组可以实现吗?肯定可以实现，而且在数据量不大的情况下，效率差距也不大。不是只用保存数组的字典对象，而使用字典对象嵌套也可以实现。条条大路通罗马，希望大家能够灵活运用字典和数组组合。
图文作者:taller
ExcelHome论坛版主