Google Sheets多条件筛选计算平均百分比公式报错解决

问题背景 #

需基于样本数据按多规则筛选后计算平均百分比，此前组合使用AVERAGE、AVERAGEIF、FILTER、AVERAGEIFS函数编写公式均返回错误，其中使用AVERAGEIFS时触发Array arguments to AVERAGEIFS are of different size报错。

计算规则 #

• 得分存储位置：Data工作表N列
• 结果输出位置：Calculation工作表E列
• 筛选规则：
  • 排除code/ID以0开头的条目，匹配逻辑：Data!A:A&"", "^0.+"
  • 筛选日期与Calculation工作表B3单元格一致的条目，匹配逻辑：Data!C:C=$B3
  • 筛选名称与Calculation工作表A3单元格一致的条目，匹配逻辑：Data!B:B=$A3
• 预期输出：筛选完成后直接返回平均百分比，例：3条有效得分分别为100%、0%、100%时，结果为66.7%

错误原因 #

此前编写的AVERAGEIFS公式不符合函数语法规范：AVERAGEIFS要求条件参数严格按照「条件范围, 匹配条件」成对传入，不支持在条件范围参数位直接写等式判断、正则匹配逻辑，因此触发数组尺寸不匹配错误。
错误公式示例：

=AVERAGEIFS(Data!N:N,Data!B:B=$A3,Data!C:C=$B3,Data!A:A&"", "^0.+")

正确公式（Google Sheets环境适用） #

在Calculation工作表E3单元格输入以下公式，下拉即可批量计算对应行结果：

=AVERAGE(FILTER(Data!N:N,Data!B:B=$A3,Data!C:C=$B3,NOT(REGEXMATCH(Data!A:A&"","^0.+")),Data!N:N<>""))

逻辑说明 #

• 内层用FILTER完成全量条件筛选：
  1. 匹配B列名称与当前行A3值一致的条目
  2. 匹配C列日期与当前行B3值一致的条目
  3. 用REGEXMATCH判断A列ID转文本后是否以0开头，NOT取反排除这类不符合要求的条目
  4. 增加N列非空判断，避免空值干扰计算结果
• 筛选得到全部符合规则的得分后，外层套AVERAGE直接计算平均值，将结果单元格设置为百分比格式、保留1位小数即可得到符合预期的输出。

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