Excel VBA 使用字典对象实现数据统计-CSDN博客

本文链接：https://blog.csdn.net/zheng15ddi/article/details/147725784

在日常办公中，我们经常需要对Excel中的数据进行分类汇总统计。今天我将分享一个实用的VBA解决方案，利用字典对象实现对车间数据的多维度统计，包括人数统计、工资总额和平均工资计算。这种方法不仅高效，而且代码结构清晰，易于理解和修改。

功能概述

本VBA程序实现了以下功能：

统计各车间出现次数（人数）
计算各车间工资总额
计算各车间平均工资
自动格式化输出结果

完整代码

Private Sub CommandButton1_Click()
    On Error Resume Next ' 如果出现错误,不中断代码,而是继续正常运行
    Range("f2:i" & Rows.Count).Clear ' 清除f至i列第二行以下的所有单元格格式和内容
    
    ' 声明变量
    Dim dic As Object, dic1 As Object, dic2 As Object
    Dim lastrow As Long, i As Long
    
    ' 创建三个字典对象
    Set dic = CreateObject("scripting.dictionary")
    Set dic1 = CreateObject("scripting.dictionary")
    Set dic2 = CreateObject("scripting.dictionary")
    
    ' 获取A列最后一行有数据行的行号
    lastrow = Range("a" & Rows.Count).End(xlUp).Row
    
    ' 遍历第二行至第lastrow行,分别设置dic、dic1、dic2的关键字-条目对
    For i = 2 To lastrow
        Dim key As String
        key = CStr(Cells(i, 1).Value) ' 获取A列的值作为字典键
        
        ' 统计每个车间出现的次数
        If dic.Exists(key) Then
            dic(key) = dic(key) + 1
        Else
            dic.Add key, 1
        End If
        
        ' 统计每个车间的总工资
        If dic1.Exists(key) Then
            dic1(key) = dic1(key) + Cells(i, 4).Value
        Else
            dic1.Add key, Cells(i, 4).Value
        End If
        
        ' 计算每个车间的平均工资
        dic2(key) = dic1(key) / dic(key)
    Next
    
    ' 将结果输出到工作表
    Range("f2").Resize(dic.Count, 1) = WorksheetFunction.Transpose(dic.keys) ' 获取字典的关键字(车间名称)
    Range("g2").Resize(dic.Count, 1) = WorksheetFunction.Transpose(dic.items) ' 各车间出现次数(人数)
    Range("h2").Resize(dic2.Count, 1) = WorksheetFunction.Transpose(dic2.items) ' 各车间的平均工资
    Range("i2").Resize(dic1.Count, 1) = WorksheetFunction.Transpose(dic1.items) ' 各车间的总工资
    
    ' 设置表头
    Range("f1:i1") = Array("车间名称", "人数", "平均工资", "总工资")
    
    ' 格式化结果区域
    With Range("f1").CurrentRegion
        .Borders.LineStyle = xlContinuous ' 设置边框
        .HorizontalAlignment = xlCenter ' 字体水平居中
        .VerticalAlignment = xlCenter ' 字体垂直居中
        .Font.Bold = True ' 设置表头加粗
    End With
    
    ' 释放字典对象
    Set dic = Nothing
    Set dic1 = Nothing
    Set dic2 = Nothing
End Sub

代码解析

1. 初始化与清理

On Error Resume Next
Range("f2:i" & Rows.Count).Clear

On Error Resume Next：错误处理语句，防止程序因意外错误中断
Range.Clear：清除F-I列原有数据，为输出新结果做准备

2. 字典对象创建

Dim dic As Object, dic1 As Object, dic2 As Object
Set dic = CreateObject("scripting.dictionary")
Set dic1 = CreateObject("scripting.dictionary")
Set dic2 = CreateObject("scripting.dictionary")

创建三个字典对象：

dic：存储各车间人数
dic1：存储各车间工资总额
dic2：存储各车间平均工资

3. 数据遍历与统计

For i = 2 To lastrow
    key = CStr(Cells(i, 1).Value)
    
    ' 统计人数
    If dic.Exists(key) Then
        dic(key) = dic(key) + 1
    Else
        dic.Add key, 1
    End If
    
    ' 统计工资总额
    If dic1.Exists(key) Then
        dic1(key) = dic1(key) + Cells(i, 4).Value
    Else
        dic1.Add key, Cells(i, 4).Value
    End If
    
    ' 计算平均工资
    dic2(key) = dic1(key) / dic(key)
Next

通过一次遍历完成三项统计，效率极高。

4. 结果输出与格式化

' 输出结果
Range("f2").Resize(dic.Count, 1) = WorksheetFunction.Transpose(dic.keys)
Range("g2").Resize(dic.Count, 1) = WorksheetFunction.Transpose(dic.items)
Range("h2").Resize(dic2.Count, 1) = WorksheetFunction.Transpose(dic2.items)
Range("i2").Resize(dic1.Count, 1) = WorksheetFunction.Transpose(dic1.items)

' 设置表头
Range("f1:i1") = Array("车间名称", "人数", "平均工资", "总工资")

' 格式化
With Range("f1").CurrentRegion
    .Borders.LineStyle = xlContinuous
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Font.Bold = True
End With