COUNTIF语句：范围跨所有表格+单元格引用作为标准 [英] COUNTIF Statements: Range Across All Sheets + Cell Reference as Criterion
问题描述
我只搜索了一切，但没有任何结果。基本上，我需要一个在所有表单中查找相同范围的公式。
我的当前公式如下所示：
= COUNTIF（Aug_15！$ G：$ G，Shaun）+ countif（July_15！$ G：$ G，Shaun）+ countif（June_15！$ G：$ GShaun）+ countif（May_15！$ G：$ G，Shaun）+ COUNTIF（Apr_15！$ G：$ G，Shaun）+ COUNTIF（Mar_15！$ G：$ G，Shaun ）
我遇到的问题是，一个月过去后，会创建一个月的新工作表。所以这大大降低了自动化程度，因为你必须每个月都要编辑公式，我基本上都在寻找一些可以在所有表格中搜索G：G的东西。
>所以在我想象中的世界里，它看起来像这样： = COUNTIF（ALLSHEETS！$ G：$ G， Shaun）
2）单元格引用作为标准
我试图让标准从另一个单元格中寻找某些东西，例如，我会用单元 L3 。但它不起作用！它从字面上搜索两个字符 L 和 3 ！
有没有办法让这个标准成为另一个单元的值？
很多感谢，
Shaun。
正如Akshin Jalilov注意到的，您需要一个脚本来实现那。前一段时间，我碰巧为该场景写了一个自定义函数。
/ **
*计算多个工作表中范围内的单元格。
*
* @param {A1：B23}范围要监视的范围（A1Notation）。
* @param {valueToCount} countItem字符串或单元格引用
* @param {Sheet1，Sheet2} excluded [可选] - 保存被排除表单名称的字符串（以逗号分隔的列表）;
* @return {number}项目出现在范围内的次数。
* @customfunction
* /
函数COUNTALLSHEETS（range，countItem，excluded）{
try {
var count = 0，
ex =（不包括）？修剪（excluded.split（））：false;
SpreadsheetApp.getActive（）
.getSheets（）
.forEach（function（s）{
if（ex&& ex.indexOf（s.getName（）） === -1 ||！ex）{
s.getRange（range）
.getValues（）
.reduce（function（a，b）{
return a。 concat（b）;
}）
.forEach（function（v）{
if（v === countItem）count + = 1;
}）;
};
}）;
返回计数;
} catch（e）{
throw e.message;
函数Trim（v）{
return v.toString（）。replace（/ ^ \ s\s * /， ）
.replace（/ \s\s * $ /，）;
}
您可以在电子表格中使用自定义函数，如下所示：
= COUNTALLSHEETS（B2：B10，Shaun）
或当'Shaun'在C2中时b
= COUNTALLSHEETS（B2： B3，C2）
有一个可选参数，允许您使用逗号分隔的工作表你想从计数中排除的名字。如果你想计算所有的床单，请不要使用这个参数。
看看它是否适合你？
1) Range Across All Sheets:
I've googled everything but nothing. Basically, I need a formula that looks for the same range across all sheets.
My current formula looks like this:
=COUNTIF(Aug_15!$G:$G, "Shaun")+countif(July_15!$G:$G, "Shaun)+countif(June_15!$G:$G, "Shaun")+countif(May_15!$G:$G, "Shaun")+COUNTIF(Apr_15!$G:$G, "Shaun")+COUNTIF(Mar_15!$G:$G, "Shaun")
The issue I have is, as a month passes, a new sheet for the month is created. So this lowers the automation dramatically as you have to edit the formula every month. I'm basically looking for something that will search G:G across all sheets for that criteria.
So in my imaginary world, it would look something like this:
=COUNTIF(ALLSHEETS!$G:$G, "Shaun")
2) Cell Reference as Criterion
I'm trying to make the criteria look for something from another cell. For example, I'd replace "Shaun" with the cell L3. But it doesn't work! It searches for literally the two characters L and 3!
Is there anyway to make the criteria a value from another cell?
Many Thanks,
Shaun.
As Akshin Jalilov noticed, you will need a script to achieve that. I happen to have written a custom function for that scenario some time ago.
/**
* Counts the cells within the range on multiple sheets.
*
* @param {"A1:B23"} range The range to monitor (A1Notation).
* @param {"valueToCount"} countItem Either a string or a cell reference
* @param {"Sheet1, Sheet2"} excluded [Optional] - String that holds the names of the sheets that are excluded (comma-separated list);
* @return {number} The number of times the item appears in the range(s).
* @customfunction
*/
function COUNTALLSHEETS(range, countItem, excluded) {
try {
var count = 0,
ex = (excluded) ? Trim(excluded.split()) : false;
SpreadsheetApp.getActive()
.getSheets()
.forEach(function (s) {
if (ex && ex.indexOf(s.getName()) === -1 || !ex) {
s.getRange(range)
.getValues()
.reduce(function (a, b) {
return a.concat(b);
})
.forEach(function (v) {
if (v === countItem) count += 1;
});
};
});
return count;
} catch (e) {
throw e.message;
}
}
function Trim(v) {
return v.toString().replace(/^\s\s*/, "")
.replace(/\s\s*$/, "");
}
You can use the custom function in your spreadsheet like this:
=COUNTALLSHEETS("B2:B10", "Shaun")
or when 'Shaun' is in C2
=COUNTALLSHEETS("B2:B3", C2)
There is an optional parameter allowing you to provide a string with comma-separated sheet names you wish to exclude from the count. Don't use this paramater if you want to count ALL sheets.
See if that works for you ?
这篇关于COUNTIF语句：范围跨所有表格+单元格引用作为标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
