Excel如何设置单元格取值上限并计算符合指定区间条件的数据平均值

实现方法 #

方案1：全Excel版本兼容（SUMPRODUCT方案，无需数组回车） #

假设你的数据存储在A2:A5区域，指定的上限值存储在C1单元格，直接输入以下公式即可：

=SUMPRODUCT(IF(A2:A5<0,0,IF(A2:A5>C1,C1,A2:A5)))/COUNTA(A2:A5)

逻辑说明：

• 内层IF先对每个数值做区间截断：小于0的按0计算，大于上限的按上限计算，区间内的取原值
• SUMPRODUCT对处理后的所有数值求和
• COUNTA统计数据区域的非空单元格总数，求和结果除以总数得到平均值

方案2：Excel 365/2021及以上版本（更简洁的LAMBDA方案） #

同样适配上述数据和上限位置，公式如下：

=AVERAGE(BYROW(A2:A5,LAMBDA(x,MIN(MAX(x,0),C1))))

逻辑说明：

• BYROW遍历数据区域的每个值，用MAX(x,0)过滤小于0的数值，再用MIN(结果, 上限)过滤大于上限的数值
• 直接用AVERAGE对截断后的所有数值求平均

示例验证 #

以你给出的测试数据为例：

• 上限设为2时，公式返回结果为2，符合预期
• 上限设为3时，公式返回结果为2.75，符合预期

如果你需要调整上限，直接修改上限单元格的数值即可，不需要改动公式。

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