并肩作战的好兄弟实现快速汇总：字典 + 数组

前言

VBA开发者经常讨论的一个话题就是：字典和数组哪个效率更高？使得很多VBA初学者懵懂的认为，这两者非此即彼，水火不容。其实很多应用场景中，它们是并肩作战的好兄弟。

实例需求

已有生产排程数据如下所示。

需要整理为如下格式。

数据合并的要点有三个：

• 料号和机种号为统计的关键字段
• 未交数量求和
• 交期汇总，包含时间和数量

示例代码

Sub LoadData()Dim aData, aRes(), lst, iRow, iCol, iKeyCol, DicConst COLUMNS_QTY = 21With Sheets("交货排程")iKeyCol = 5lst = .Cells(.Rows.Count, iKeyCol).End(xlUp).RowaData = .Cells(2, 1).Resize(lst, COLUMNS_QTY).ValueEnd WithSet Dic = CreateObject("Scripting.Dictionary")For iRow = 3 To UBound(aData, 1)skey = aData(iRow, 5) & "|" & aData(iRow, 6)If Len(skey) > 1 ThenIf Dic.exists(skey) ThenDic(skey) = Array(Val(aData(iRow, 9)) + Dic(skey)(0), Dic(skey)(1))ElseDic(skey) = Array(Val(aData(iRow, 9)), "")End IfFor iCol = 11 To UBound(aData, 2)If Val(aData(iRow, iCol)) > 0 Thensdate = 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 IfNext iColEnd IfNext iRowReDim aRes(1 To Dic.Count, 1 To 4)n = 1For Each d In Dic.keysakey = 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 + 1Next' 结果数组aRes回写到工作表中With Sheets("结果").Range("2:10000").ClearContents.Cells(2, 1).Resize(Dic.Count, 4).Value = aRes.Range("a1").CurrentRegion.Borders.LineStyle = xlContinuousEnd WithSet 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) ThenDic(skey) = Array(Val(aData(iRow, 9)) + Dic(skey)(0), Dic(skey)(1))
ElseDic(skey) = Array(Val(aData(iRow, 9)), "")
End If

与此类似，下面代码将汇总交期信息。注意更新字典对象的数组元素时，只能全部重新赋值，而不能使用Dic(skey)(0)="xxx"的形式。

For iCol = 11 To UBound(aData, 2)If Val(aData(iRow, iCol)) > 0 Thensdate = 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.keysakey = 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 + 1Next

后记

对于这个实例，只用数组可以实现吗？肯定可以实现，而且在数据量不大的情况下，效率差距也不大。不用保存数组的字典对象，而使用字典对象嵌套也可以实现。条条大路通罗马，希望大家能够灵活运用字典和数组组合。