如何在AVERAGEIF()函数中限制取值上限并排除0值计算平均

嘿，你这个需求可以通过嵌套几个基础函数来完美解决，既排除0值，又把超100%的数值限制在100%后再算平均，具体公式和逻辑如下：

推荐公式 #

=AVERAGE(IF(J2:J214<>0, MIN(J2:J214, 1), ""))

公式逻辑拆解 #

• 第一步：筛选非0值：J2:J214<>0 作为IF的判断条件，先把所有0值的单元格排除在外，对应位置返回空值（""），AVERAGE函数会自动忽略空值。
• 第二步：限制数值上限：对每个非0的单元格，用MIN(J2:J214, 1)取原数值和1（也就是100%）中的较小值——这样超过100%的数值会被替换成100%，正常的百分比数值则保持不变。
• 第三步：计算平均值：AVERAGE函数对经过前两步处理后的有效数值计算平均值。

版本注意事项 #

• 如果你用的是 Excel 365/2021及以后版本：直接输入公式后按回车即可生效（这些版本支持动态数组，无需额外操作）。
• 如果你用的是 Excel 2019及更早的旧版本：输入完公式后，需要按住 Ctrl+Shift+Enter 组合键触发数组计算，公式会自动被大括号包裹（不要手动输入大括号）。

举个简单例子验证：假设J列有0%、120%、80%三个数值，经过公式处理后，参与计算的是100%和80%，最终平均值为90%，完全符合你的需求。

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