Excel应用实践06：进行多条件统计

Sub StatisticsData()
    Dim lngLastRow As Long
    Dim rng As Range
    Dim myDict As Variant
    Dim myKey As Variant
    Dim str() As String
    Dim num As Long
    '获取数据最后一行
    lngLastRow = Range("A" &Rows.Count).End(xlUp).Row
    '将数据区域单元格中的场次|考场编码|试室|试室编码|报考专业数据组合
    '将组合后的数据临时存放在G列
    '每个单元格数据之间用空格分开
    '方便后面拆分
    For Each rng In Range("A2:A" & lngLastRow)
        With rng
            .Offset(0, 6) = .Offset(0, 0) +" " + _
                            .Offset(0, 1) +" " + _
                            .Offset(0, 2) +" " + _
                            .Offset(0, 3) +" " + _
                            .Offset(0, 5)
        End With
    Next rng
    '字典
    Set myDict =CreateObject("scripting.dictionary")
    '遍历列G中的数据并将其放置在字典中
    '字典中键值为不同的数据组合
    '字典中键对应的值为每种数据组合的数量,即专业报考人数
    For Each rng In Range("G2:G"& lngLastRow)
        With myDict
            If Not .exists(rng.Value) Then
                .Item(rng.Value) = 1
            Else
                .Item(rng.Value) =.Item(rng.Value) + 1
            End If
        End With
    Next rng
    '清除临时存放在列G中的数据
    Range("G2:G" &lngLastRow).Clear
    '获取字典键
    myKey = myDict.keys
    '遍历字典键
    For num = 0 To UBound(myKey)
        '拆分字典键中的字符
        '分别对应场次|考场编码|试室|试室编码|报考专业
        str = Split(myKey(num))
        '取出相应的值并放置在相应的单元格
        With Range("I2")
            .Offset(num, 0) = str(0)
            .Offset(num, 1) = str(1)
            .Offset(num, 2) = str(2)
            .Offset(num, 3) = str(3)
            .Offset(num, 4) = str(4)
            .Offset(num, 5) =myDict.Item(myKey(num))
        End With
    Next num
    '获取统计区域的数据最后一行
    lngLastRow = Range("I" &Rows.Count).End(xlUp).Row
    With Range("I1:N" &lngLastRow)
        '对统计区域的数据排序
        .Sort _
        Key1:="场次", Order1:=xlAscending, _
        Key2:="考场编码", Order2:=xlAscending, _
        Key3:="试室编码", Order3:=xlAscending, _
        Header:=xlYes
        '调整列宽
        .Columns.AutoFit
    End With
    Set myDict = Nothing
End Sub