Excel如何结合SUBTOTAL与条件判断实现多汇总方式动态切换

完全符合你不新增辅助列/表、保留F5控制汇总类型逻辑的要求，分两种场景提供对应方案： #

场景1：需要保留SUBTOTAL忽略隐藏/筛选行的特性 #

如果你的统计需要自动跳过原始数据中手动隐藏、或者筛选排除的行，使用如下公式，直接粘贴到汇总表B2单元格下拉填充即可：

=SUMPRODUCT(SUBTOTAL($F$5,OFFSET(数据源!$B$2,ROW(数据源!$B$2:$B$7)-ROW(数据源!$B$2),0,1,1))*(数据源!$A$2:$A$7=A2))

公式说明： #

• OFFSET(数据源!$B$2,ROW(...)-ROW(...),0,1,1) 会把Sales QTY列的整段区域拆分为单个单元格，供SUBTOTAL分别计算
• 乘积项*(数据源!$A$2:$A$7=A2) 会只匹配当前行对应的产品类型
• SUMPRODUCT会汇总符合条件的SUBTOTAL计算结果，自动适配F5的编码切换：
  • F5=9时：输出对应产品的销量总和
  • F5=1时：输出对应产品的单次销量平均值
  • F5=2时：输出对应产品的交易次数
• 原生支持Office 2016，不需要启用数组公式，不需要修改现有任何基础逻辑

如果你的原始数据会动态新增，建议把原始数据转换为Excel结构化表（选中数据区域按Ctrl+T），公式可以适配自动扩展的范围，不用每次手动修改区域参数。 #

场景2：不需要考虑隐藏/筛选行，仅需按产品类型切换汇总方式 #

如果不需要跳过隐藏行，仅需要实现按产品分类+F5切换统计逻辑，可以用更轻量化的IFS嵌套条件统计函数方案，性能更好：

=IFS(
$F$5=1,AVERAGEIF(数据源!$A:$A,A2,数据源!$B:$B),
$F$5=2,COUNTIF(数据源!$A:$A,A2),
$F$5=9,SUMIF(数据源!$A:$A,A2,数据源!$B:$B)
)

这个方案和你当前临时用嵌套IFS的思路完全匹配，不需要额外调整现有逻辑，直接替换现有公式即可。

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