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

前言

VBA开发者经常讨论的一个话题就是：字典和数组哪个效率更高？使得很多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("Scripting.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 If

与此类似，下面代码将汇总交期信息。注意更新字典对象的数组元素时，只能全部重新赋值，而不能使用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

后记

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