Google Sheets动态特定计数实现方法求助

核心需求回顾 #

按日期、昼夜维度统计：

1. 各科目非空单元格数量
2. 对应总时长（Subject 1=30min，Subject 3=90min）
3. 支持动态行增减、自动定位科目列、统一修改表名

1. 统一表名配置 #

在单元格（如E1）中存储目标表的名称，所有公式通过INDIRECT调用该单元格，实现一键修改表名：

$E$1 // 输入目标表名，比如"Sheet1"

2. 自动定位科目列 #

用MATCH函数根据表头名称自动获取科目所在列号，避免手动修改列标：

• Subject 1列号（存于H1）：

  =MATCH("Subject 1", INDIRECT("'"&$E$1&"'!1:1"), 0)
  

• Subject 3列号（存于H2）：

  =MATCH("Subject 3", INDIRECT("'"&$E$1&"'!1:1"), 0)
  

3. 按日期+昼夜统计非空数量 #

单科目统计（以Subject 1为例） #

在统计区域的单元格（如I2）中输入公式，自动匹配指定日期（F2）和昼夜（G2）的非空数量：

=SUMPRODUCT(
  (INDIRECT("'"&$E$1&"'!A4:A")=F2)*
  (INDIRECT("'"&$E$1&"'!B4:B")=G2)*
  (INDIRECT("'"&$E$1&"'!"&CHAR(64+H1)&"4:"&CHAR(64+H1))<>"")
)

多科目总数量 #

合并所有科目统计结果：

=SUMPRODUCT(
  (INDIRECT("'"&$E$1&"'!A4:A")=F2)*
  (INDIRECT("'"&$E$1&"'!B4:B")=G2)*
  (INDIRECT("'"&$E$1&"'!"&CHAR(64+H1)&"4:"&CHAR(64+H1))<>"")
) + SUMPRODUCT(
  (INDIRECT("'"&$E$1&"'!A4:A")=F2)*
  (INDIRECT("'"&$E$1&"'!B4:B")=G2)*
  (INDIRECT("'"&$E$1&"'!"&CHAR(64+H2)&"4:"&CHAR(64+H2))<>"")
)

4. 按日期+昼夜统计总时长 #

结合科目时长计算总时长，同样支持动态行和列：

=SUMPRODUCT(
  (INDIRECT("'"&$E$1&"'!A4:A")=F2)*
  (INDIRECT("'"&$E$1&"'!B4:B")=G2)*
  (INDIRECT("'"&$E$1&"'!"&CHAR(64+H1)&"4:"&CHAR(64+H1))<>"")*30
) + SUMPRODUCT(
  (INDIRECT("'"&$E$1&"'!A4:A")=F2)*
  (INDIRECT("'"&$E$1&"'!B4:B")=G2)*
  (INDIRECT("'"&$E$1&"'!"&CHAR(64+H2)&"4:"&CHAR(64+H2))<>"")*90
)

5. 一键生成全量统计报表（QUERY函数） #

用QUERY一次性生成所有日期+昼夜的统计结果，无需逐行计算：

=QUERY(
  INDIRECT("'"&$E$1&"'!A4:"&CHAR(64+COLUMNS(INDIRECT("'"&$E$1&"'!1:1")))&COUNTA(INDIRECT("'"&$E$1&"'!A:A"))),
  "SELECT A, B, COUNT(C)+COUNT(D), COUNT(C)*30+COUNT(D)*90 
   WHERE C IS NOT NULL OR D IS NOT NULL 
   GROUP BY A, B 
   LABEL COUNT(C)+COUNT(D)'总科目数', COUNT(C)*30+COUNT(D)*90'总时长(min)'",
  1
)

注：如果科目列增减，只需修改COUNT(C)+COUNT(D)和COUNT(C)*30+COUNT(D)*90部分，对应新增/删除科目统计项。

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