时间戳

首页 > 解决方案 > 使用转置公式谷歌表格对多行求和

arrays - 使用转置公式谷歌表格对多行求和

问题描述

我正在尝试构建一个公式来计算日期范围内发生的课程总数。这是我可以使用的公式，但它需要包含数百行（即从 B2 到 B500 左右的“类计数”）。有没有办法把它变成一个数组，所以我不必有一个页面和页面长的公式？

=countifs(transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2))),">="&'All Totals'!$N4,transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2))),"<"&'All Totals'!$N5)+
countifs(transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3))),">="&'All Totals'!$N4,transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3))),"<"&'All Totals'!$N5)+
countifs(transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4))),">="&'All Totals'!$N4,transpose(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4))),"<"&'All Totals'!$N5)
+ ... etc.

“所有数据”列 A 包含班级的日期，列 B 包含班级名称（每个学生重复但只能计算一次）。“类计数”列 B 包含唯一类名称的列表。'All Totals' 单元格 N4 和 N5 包含月份开始检查之间。

目标是当且仅当它落在“所有总计”上的 N4 和 N5 指定的数据范围内时，对类的每次出现计数一次。唯一的问题是，多年来最终会有数百个课程。

我的想法是把它变成一个数组公式并计算整个范围，但我所有的尝试都返回了 0。

我无法分享实际工作表，因为其中包含个人信息，但我在这里创建了一个测试版本： https ://docs.google.com/spreadsheets/d/1Nf0f5Bnuwe0-dnH6zHILGntdv2JDywFOTvmTjteXVLQ/edit?usp=sharing

我要修复的公式位于“导入”选项卡上。

编辑：我意识到这可能没有必要的“转置”方面，所以我将公式编辑了一点，但仍然无法自动汇总所有“类计数”类名称（B 列）。

=countifs(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2)),">="&'All Totals'!$N4,unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B2)),"<"&'All Totals'!$N5)
+countifs(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3)),">="&'All Totals'!$N4,unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B3)),"<"&'All Totals'!$N5)
+countifs(unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4)),">="&'All Totals'!$N4,unique(filter('All Data'!$A$2:$A,'All Data'!$B$2:$B='Class Counts'!$B4)),"<"&'All Totals'!$N5)
+ ... etc

谢谢！

标签： arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

解决方案

尝试：

=QUERY('All Data'!A2:B, 
 "select B,count(B) 
  where A >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and A <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"'
  group by B 
  label count(B)''")

如果您只想要特定的课程，请尝试：

=QUERY('All Data'!A2:B, 
 "select B,count(B) 
  where A >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and A <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"'
    and B matches '"&TEXTJOIN("|", 1, 'Class Counts'!B2:B)&"' 
  group by B 
  label count(B)''")

并只返回它的总和：

=SUM(QUERY('All Data'!A2:B, 
 "select count(B) 
  where A >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and A <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"'
    and B matches '"&TEXTJOIN("|", 1, 'Class Counts'!B2:B)&"' 
  group by B 
  label count(B)''"))

正则表达式括号修复：

=INDEX(SUM(QUERY(UNIQUE('All Data'!A2:B), 
 "select count(Col2)
  where Col1 >= date '"&TEXT('All Totals'!N4, "yyyy-mm-dd")&"' 
    and Col1 <  date '"&TEXT('All Totals'!N5, "yyyy-mm-dd")&"' 
    and Col2 matches '"&
 SUBSTITUTE(SUBSTITUTE(TEXTJOIN("|", 1, 'Class Counts'!B2:B), "(", "\("), ")", "\)")&"' 
  group by Col2
  label count(Col2)''")))

推荐阅读