Excel：如何在筛选数据中使用AverageIf并添加多条件？

可行公式 #

针对筛选后的数据，计算≥5且<10的平均值，可使用以下数组公式：

=AVERAGE(IF((SUBTOTAL(2, OFFSET(A20, ROW(A20:A5557)-ROW(A20), 0))=1)*(A20:A5557>=5)*(A20:A5557<10), A20:A5557))

公式说明 #

• SUBTOTAL(2, OFFSET(A20, ROW(A20:A5557)-ROW(A20), 0))=1：通过SUBTOTAL(2)（仅对可见行计数）判断当前行是否为筛选后的可见行，可见行返回1，隐藏行返回0
• (A20:A5557>=5)*(A20:A5557<10)：用乘法逻辑实现多条件“且”判断，两个条件同时满足时结果为1，否则为0
• 外层IF函数将同时满足可见行和数值条件的单元格筛选出来，最终由AVERAGE计算平均值

注意事项 #

• 旧版Excel（非365/2021）需要按Ctrl+Shift+Enter组合键完成数组公式输入；Excel 365/2021直接回车即可生效
• 替换原公式中SUBTOTAL(9)的原因：SUBTOTAL(9)是对可见行求和，可能因数值为0等情况产生误判，用SUBTOTAL(2)计数判断可见行更精准

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