从Excel多工作表提取B27:B36范围数据到汇总列并统计各值出现次数

我在同一个工作簿中有71个工作表，工作表编号为“1”到“71”。我需要将所有工作表B27:B36范围内的值汇总生成一个主列表，并统计每个特定值的出现次数。

• 示例工作表“1”内容如下：
  
• 示例工作表“2”内容如下：
  
  我目前自行编写了如下公式：

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&TabList&"'!B27:B36"),"5.1.1 (Policies for information security)",INDIRECT("'"&TabList&"'!B27:B36"),"5.1.1 (Policies for information security)"))

但该方案的问题在于需要编写大量独立的公式才能完成需求，操作繁琐，请问是否有更高效的实现方法？

根据你使用的Excel版本，可选择以下任意一种高效方案：

方案1：Excel 365/2021 动态数组方案（推荐） #

无需手动维护工作表列表、无需下拉公式，输入后自动生成完整的汇总统计结果：

=LET(
  all_data, TOCOL(INDIRECT("'"&SEQUENCE(71)&"'!B27:B36"),1),
  unique_val, UNIQUE(all_data),
  count_num, COUNTIF(all_data, unique_val),
  HSTACK({"统计值","出现次数"}, unique_val, count_num)
)

公式说明：

• SEQUENCE(71)自动生成1到71的工作表名序列，不需要提前创建TabList
• TOCOL将所有工作表对应区域的值合并为单列，自动忽略空值
• 最终结果会自动向下向右溢出，完整展示所有唯一值和对应出现次数

方案2：Excel 2019及更低版本兼容方案 #

1. 先在汇总表的A1:A71列输入1到71的数字，对应所有工作表的名称
2. 提取唯一值：在C2单元格输入以下数组公式，输入完成后按Ctrl+Shift+Enter三键结束，下拉直到出现#N/A错误为止：

=INDEX(INDIRECT("'"&$A$1:$A$71&"'!B27:B36"),MATCH(0,COUNTIF($C$1:C1,INDIRECT("'"&$A$1:$A$71&"'!B27:B36")),0))

3. 统计次数：在D2单元格输入以下公式，下拉和C列的唯一值对齐即可：

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$1:$A$71&"'!B27:B36"),C2))

方案3：VBA一键生成方案 #

1. 按Alt+F11打开VBA编辑器，右键点击当前工作簿→插入→模块
2. 粘贴以下代码后按F5运行：

Sub 汇总多表统计()
    Dim ws As Worksheet, cell As Range, dic As Object, i As Integer
    Set dic = CreateObject("Scripting.Dictionary")
    '遍历所有编号工作表
    For i = 1 To 71
        Set ws = Sheets(CStr(i))
        For Each cell In ws.Range("B27:B36")
            If cell.Value <> "" Then
                dic(cell.Value) = dic(cell.Value) + 1
            End If
        Next
    Next
    '输出结果到当前工作表A列开始位置
    Cells(1, 1) = "统计值"
    Cells(1, 2) = "出现次数"
    Range("A2").Resize(dic.Count, 1) = Application.Transpose(dic.Keys)
    Range("B2").Resize(dic.Count, 1) = Application.Transpose(dic.Items)
End Sub

内容的提问来源于stack exchange，提问作者mak47