如何用VBA统计包含指定关键词的工作表数量

Excel 解决方案 #

方法1：VBA宏（直接统计+列出工作表） #

按Alt + F11打开VBA编辑器，插入新模块，粘贴以下代码：

Sub CountArchivedSheets()
    Dim ws As Worksheet
    Dim count As Integer
    Dim sheetList As String
    
    count = 0
    sheetList = "包含ARCHIVED的工作表：" & vbCrLf
    
    For Each ws In ThisWorkbook.Worksheets
        ' 不区分大小写匹配，要区分的话把vbTextCompare改成vbBinaryCompare
        If InStr(1, ws.Name, "ARCHIVED", vbTextCompare) > 0 Then
            count = count + 1
            sheetList = sheetList & "- " & ws.Name & vbCrLf
        End If
    Next ws
    
    MsgBox sheetList & vbCrLf & "总数：" & count, vbInformation, "统计结果"
End Sub

运行宏后，会弹出对话框显示所有符合条件的工作表名称和总数。

方法2：公式+定义名称（仅统计数量） #

不想用VBA的话，用定义名称实现：

1. 按Ctrl + F3打开名称管理器，新建名称（比如ArchivedSheetsCount）
2. 引用位置输入：

=SUMPRODUCT(--(ISNUMBER(SEARCH("ARCHIVED",GET.WORKBOOK(1)))))

3. 在任意单元格输入=ArchivedSheetsCount即可得到总数。注意需要启用宏，部分Excel版本可能要允许加载旧函数。

Google Sheets 解决方案 #

方法1：自定义函数（统计+列表） #

点击「扩展」>「Apps 脚本」打开脚本编辑器，粘贴以下代码：

function countArchivedSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let count = 0;
  let sheetList = ["包含ARCHIVED的工作表："];
  
  sheets.forEach(sheet => {
    // 不区分大小写的话改成 /ARCHIVED/i.test(sheet.getName())
    if (sheet.getName().includes("ARCHIVED")) {
      count++;
      sheetList.push("- " + sheet.getName());
    }
  });
  
  sheetList.push("\n总数：" + count);
  SpreadsheetApp.getUi().alert(sheetList.join("\n"));
}

保存后回到表格，插入绘图作为按钮，把按钮链接到这个函数，点击就能弹出统计结果。

方法2：公式统计数量 #

在单元格输入以下公式直接获取总数：

=COUNTA(QUERY(GET_SHEETS(),"where Col1 contains 'ARCHIVED'"))

要区分大小写的话，把contains替换成matches 'ARCHIVED'即可。

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