如何结合SUBTOTAL与IF计算筛选后指定条件的Error列标准差

核心逻辑 #

要实现「筛选Group后，仅计算Woehler=3的Error列标准差」，不能直接用SUMPRODUCT搭配SUBTOTAL的标准差代码（106/107）——因为这类标准差函数需要对连续区域计算，而非单个单元格的判断结果。正确思路是：先标记出筛选可见且Woehler=3的行，再对对应Error值计算标准差。

具体公式（适配不同Excel版本） #

假设数据行范围是第2行到第100行（可根据实际数据调整），Woehler列是D列，Error列是E列：

1. 样本标准差（对应SUBTOTAL的107） #

=STDEV.S(IF((SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$100)-ROW($A$2),0))=1)*($D$2:$D$100=3),$E$2:$E$100,""))

• Excel 2019及更早版本：输入后按Ctrl+Shift+Enter作为数组公式执行
• Excel 365/2021及以后：直接回车即可

2. 总体标准差（对应SUBTOTAL的106） #

=STDEV.P(IF((SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$100)-ROW($A$2),0))=1)*($D$2:$D$100=3),$E$2:$E$100,""))

执行要求同上。

公式拆解 #

• SUBTOTAL(103, OFFSET(...))：通过OFFSET生成每行的单个单元格引用，SUBTOTAL(103)（忽略隐藏行的COUNTA）判断该行是否在筛选后可见——可见返回1，隐藏返回0。
• ($D$2:$D$100=3)：筛选出Woehler值为3的行。
• 两个条件相乘得到逻辑数组，仅同时满足「可见」和「Woehler=3」的行，其Error值会被纳入计算，不符合条件的返回空值（会被标准差函数自动忽略）。
• STDEV.S/STDEV.P分别对应样本/总体标准差，替代SUBTOTAL的标准差功能，同时实现条件筛选。

简化方案（仅Excel 365/2021及以后） #

用FILTER函数直接筛选目标值，写法更直观：

=STDEV.S(FILTER($E$2:$E$100,(SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$100)-ROW($A$2),0))=1)*($D$2:$D$100=3)))

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