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

问题描述

我创建了一个谷歌表格.单个谷歌工作表中有多个工作表(标签).

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,virtual!B5:B)

1. 计算新请求"总数

=(COUNTIF(kkweb!D5:D,"New Requests")) + (COUNTIF(virtual!D5:D,"New Requests"))

注意:我上面的公式运行良好，但我将有 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,virtual!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.

有没有什么简单的方法可以在谷歌表格中进行计算?

我们也许可以使用名称配置创建额外的工作表并在那里添加工作表名称 &执行一个简单的公式来完成上述计算

注意:每张工作表都会有不同的名称，而不是工作表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 Requestskkweb 表.我还有另一列 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

所以基本上我想检查John"有多少New Requests

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

至于 John/Katrina 问题，您可以使用 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 Sheets 公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！