Excel 统计多张工作表中出现的人名

发表于 分类于 阅读次数： 本文字数： 753 阅读时长 ≈ 3 分钟

假设有这样一个需求，一个 excel 表中含有多张工作表，每张工作表的 B 列都包含很多人名，如何统计出人名总共出现的次数。

可以通过 VBA 宏来实现，遍历所有工作表，统计每个人名在所有工作表的 B 列中出现的总次数，并将结果列在一个新创建的 “统计表” 中。

  1. 打开 Excel，按下 Alt + F11 打开 VBA 编辑器。

  2. 在 VBA 编辑器中，右键点击 “VBAProject”，选择 “插入”->“模块”。

  3. 将如下代码粘贴进去。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Sub CountNameOccurrences()
    Dim wsSummary As Worksheet ' 定义一个工作表对象，用于存储统计结果
    Dim ws As Worksheet ' 定义一个工作表对象，用于遍历所有工作表
    Dim lastRow As Long ' 定义一个长整型变量，用于存储当前工作表中 B 列的最后一行行号
    Dim name As Range ' 定义一个范围对象，用于存储当前正在处理的单元格
    Dim nameList As Object ' 定义一个对象变量，用于存储字典对象
    Dim nameKey As Variant ' 定义一个变体变量，用于遍历字典中的键
    Dim i As Integer ' 定义一个整型变量，用于循环计数

    ' 创建一个统计表
    Set wsSummary = ThisWorkbook.Sheets.Add ' 在当前工作簿中添加一个新的工作表
    wsSummary.Name = "统计表" ' 将新工作表命名为“统计表”

    ' 初始化一个字典来存储每个人名及其出现次数
    Set nameList = CreateObject("Scripting.Dictionary") ' 创建一个字典对象

    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Sheets ' 遍历当前工作簿中的所有工作表
        If ws.Name <> wsSummary.Name Then ' 如果当前工作表不是“统计表”
            ' 找到当前工作表中 B 列的最后一行行号
            lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row ' 找到 B 列的最后一行行号
            ' 遍历当前工作表中 B 列的每一行
            For i = 1 To lastRow ' 从第 1 行到最后一行
                Set name = ws.Cells(i, 2) ' 获取当前行的 B 列单元格
                ' 检查人名是否已经存在于字典中
                If nameList.exists(name.Value) Then ' 如果字典中已经存在该人名
                    ' 如果存在，增加出现次数
                    nameList(name.Value) = nameList(name.Value) + 1 ' 增加该人名的出现次数
                Else
                    ' 如果不存在，初始化出现次数为 1
                    nameList(name.Value) = 1 ' 初始化该人名的出现次数为 1
                End If
            Next i ' 继续下一行
        End If ' 结束当前工作表的检查
    Next ws ' 继续下一个工作表

    ' 将统计结果写入统计表
    i = 1 ' 初始化行号计数器
    For Each nameKey In nameList.keys ' 遍历字典中的所有键（人名）
        wsSummary.Cells(i, 1).Value = nameKey ' 在统计表的第 i 行第 1 列写入人名
        wsSummary.Cells(i, 2).Value = nameList(nameKey) ' 在统计表的第 i 行第 2 列写入该人名的出现次数
        i = i + 1 ' 行号计数器加 1
    Next nameKey ' 继续下一个键（人名）
End Sub ' 结束子程序

  1. 关闭 VBA 编辑器，返回 Excel。

  2. 按下 Alt + F8，选择 “CountNameOccurrences” 宏并运行。