vba 如何在excel中包含多个工作表的整个工作簿中查找某个值的计数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9089187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find a count of certain value in a whole workbook with multiple sheets in excel?
提问by Yan
I have a workbook with multiple sheets of data.
我有一个包含多张数据的工作簿。
In the first sheet I have a list of values, and I would like to find how many times they appear in the rest of the book. The count would appear in the adjacent cell.
在第一张表中，我有一个值列表，我想知道它们在本书的其余部分中出现了多少次。计数将出现在相邻的单元格中。
Thank you very much in advance!
非常感谢您提前！
回答by brettdj
You can do it with an extra step and some tinkering with your formula
你可以通过一个额外的步骤和一些修改你的公式来做到这一点
Define a range name,
AllSheetas=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
(see pic below)To count all occurrences of "Fred" from A1:B10 of all sheets enter in
=SUM(COUNTIF(INDIRECT("'" & AllSheets&"'!A1:B10"),"fred"))
as an array formula (press Ctrl - Shift - Enter together)
定义一个范围名称，
AllSheet如=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
（见下图）要计算所有工作表的 A1:B10 中“Fred”的所有出现次数，请
=SUM(COUNTIF(INDIRECT("'" & AllSheets&"'!A1:B10"),"fred"))
作为数组公式输入（同时按 Ctrl - Shift - Enter）
the "'" & AllSheets&"'!portion ensures that sheets with spaces etc that produce names like 'My Sheet' will work
该"'" & AllSheets&"'!部分确保带有空格等的工作表可以产生诸如“我的工作表”之类的名称
