求助：Excel非连续单元格求平均值并忽略0值的正确公式

示例表格 #

需求说明 #

需要在单元格C6中计算分散单元格（A1、A3、B6）的平均值，要求忽略值为0的单元格。示例中A3值为0，仅计算A1（10）和B6（5），结果为(10+5)/2=7.5。实际场景中A3的值可能为0或其他非0数值，非0时需纳入计算。

遇到的问题 #

这些单元格并非连续区域，直接使用=AVERAGEIF函数无法处理；尝试的公式返回结果错误：

=SUM(A1+A3+B6)/INDEX(FREQUENCY((A1+A3+B6);0);2)

该公式返回15（仅求和未正确取平均），而非预期的7.5。

可行解决方案 #

方案1：SUMPRODUCT+COUNTIF组合公式

=SUMPRODUCT((A1,A3,B6)*(A1,A3,B6<>0))/COUNTIF((A1,A3,B6),"<>0")

• 逻辑：SUMPRODUCT计算所有非0值的总和，COUNTIF统计非0值的数量，两者相除得到平均值。
• 适用所有Excel版本，无需数组输入。

方案2：AVERAGEIF数组公式

=AVERAGEIF((A1,A3,B6),"<>0")

• 逻辑：将分散单元格用括号组成数组区域，AVERAGEIF会自动忽略符合条件（<>0）的值并计算平均。
• 注意：Excel 2019及更早版本需按Ctrl+Shift+Enter作为数组公式输入；Excel 365/2021及以后版本直接回车即可。

方案3：SUM+IF数组公式

=SUM(IF((A1,A3,B6)<>0,(A1,A3,B6),0))/SUM(IF((A1,A3,B6)<>0,1,0))

• 逻辑：通过IF函数筛选出非0值，分别计算总和与数量后求商。
• 注意：需按Ctrl+Shift+Enter作为数组公式输入（Excel 365/2021可直接回车）。

修正原公式

原公式错误原因是未将分散单元格作为数组传入，修正后即可正常使用：

=SUM((A1,A3,B6)*(A1,A3,B6<>0))/INDEX(FREQUENCY((A1,A3,B6),0),2)

• 逻辑：FREQUENCY((A1,A3,B6),0)统计数组中小于等于0和大于0的数量，INDEX(...,2)取大于0的数量，再用非0值总和除以该数量得到平均。

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