改善计数，CountIF Google表格公式 [英] Improving Count, CountIF Google Sheets formulas

问题描述

我创建了一个Google表格.单个Google工作表内有多个工作表(Tab).

I have created a google sheets. There are multiple sheets (Tabs) inside single google sheet.

我有一张叫 Dashboard

还有其他工作表，例如 kkweb ， virtual .当前除仪表板外还有2张其他工作表.所有2张纸都具有相同的列&结构，即使新的工作表也将具有相同的列&结构.

There are other sheets like kkweb, virtual. Currently there are 2 other sheets except dashboard. All the 2 sheets has same columns & structure, Even new sheets will have same columns & structure.

我想在 dashboard 工作表上执行一些计算.

I want to perform some calculations on dashboard sheet.

1. 计算任务总数

= COUNT(kkweb！B5:B，虚拟！B5:B)

1. 计算新请求"总数

=(COUNTIF(kkweb！D5:D，新请求"))+(COUNTIF(虚拟！D5:D，新请求"))

注意:我的上述公式运行正常，但是我将需要20张纸而不是2张纸.所以我的计数公式将像

Note: My above formulas are working perfectly, But I'm going to have like 20+ sheets instead of 2. So my count formula will be like

= COUNT(kkweb！B5:B，虚拟！B5:B，Sheet3！B5:B，Sheet4！B5:B，Sheet5！B5:B，Sheet6！B5:B，Sheet7！B5:B)等

每当有新表格出现时，对我所有的公式来说都将是很长的时间.

It will be very long for all of my formulas, every time a new sheet comes.

在Google表格中可以通过任何简单的方法进行计算吗?

我们也许可以使用名称配置创建额外的工作表，并在其中添加工作表名称&执行简单的公式以进行上述计算

注意:每个工作表都有不同的名称，而不是工作表1，工作表2，sheet3.

Note: Each sheet will have a different name, not sheet1, sheet2, sheet3.

第二，如何根据所有工作表中的其他列计算值.

Secondly, How can i calculate value based on other column from all sheets.

例如: =(COUNTIF(kkweb！D5:D，"New Requests"))此公式用于计算D5:D中值为 New Requests 的位置kkweb工作表.我还有另一列C5:C，其中包含名称"John"，"Katrina"等

For example: =(COUNTIF(kkweb!D5:D,"New Requests")) This formulae is counting where value is New Requests from D5:D in kkweb sheet. I have another column C5:C which contains names "John", "Katrina" etc

所以基本上我想检查约翰"有多少个新请求

So basically I want to check how many count of New Requests are for "John"

推荐答案

有几种解决方法.这取决于你想走多远.您喜欢哪条路线.内部没有用于检测工作表名称的公式，因此您需要一个脚本.例如，有几个:

there are several ways how to solve this. it depends on how far you want to go, eg. which route you prefer. internally there is no formula to detect sheet names so for that you will need a script. there are several for example:

function SNAME(option) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()
  var thisSheet = sheet.getName(); 
  if(option === 0){                  // ACTIVE SHEET NAME =SNAME(0)
    return thisSheet;
  }else if(option === 1){            // ALL SHEET NAMES =SNAME(1)
    var sheetList = [];
    ss.getSheets().forEach(function(val){
       sheetList.push(val.getName())
    });
    return sheetList;
  }else if(option === 2){            // SPREADSHEET NAME =SNAME(2)
    return ss.getName();    
  }else{
    return "#N/A";                   // ERROR MESSAGE
  };
};

然后您可以在其上构建并创建例如字符串生成器

then you can build upon it and create for example a string generator

=ARRAYFORMULA(TEXTJOIN(",", 1, SNAME(1)&"!D5:D"))

甚至可以复制粘贴的完整公式:

or even full formula ready to be copy-pasted:

=ARRAYFORMULA("=COUNT('"&TEXTJOIN(",'", 1, SNAME(1)&"'!B5:B")&")")

当然，如果您精通JS并且可以通过一些精美的onOpen/onEdit或时间触发器运行它，则可以通过脚本对其进行完全管理

of course, it can be fully managed via script if you are skilled in JS and have it run with some fancy onOpen/onEdit or time trigger

对于约翰/卡特里娜飓风问题，您可以使用 COUNTIFS 代替 COUNTIF .在那里，您可以定义多个条件来获得所需的计数.例如:

as for the John/Katrina issue you can use COUNTIFS instead of COUNTIF. there you can define multiple criteria to get your desired count. for example:

=COUNTIFS(kkweb!D5:D, "New Requests", kkweb!E5:E, "John")

这篇关于改善计数，CountIF Google表格公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！