Excel求区域内最小3个非空值的动态平均值技术问询

核心函数（兼容所有Excel版本） #

使用以下公式即可实现需求：

=AVERAGE(SMALL(A1:I1,ROW(INDIRECT("1:"&MIN(COUNTA(A1:I1),3)))))

公式拆解 #

• COUNTA(A1:I1)：统计目标区域内的非空单元格数量
• MIN(COUNTA(A1:I1),3)：动态确定要取的最小值数量——数据不足3个时取实际非空数，数据≥3个时固定取3个
• ROW(INDIRECT("1:"&MIN(...)))：生成对应数量的位次序列（例如非空数为2时生成{1,2}，非空数为5时生成{1,2,3}）
• SMALL(A1:I1, 序列)：提取对应位次的最小值
• AVERAGE(...)：对提取出的数值计算平均值

示例验证 #

1. 示例1：仅2个非空值
   区域A1:I1中仅A1=40、B1=42，其余为空：

   • COUNTA(A1:I1)返回2，MIN(2,3)得到2
   • 生成序列{1,2}，SMALL提取40、42
   • 平均值为(40+42)/2=41，符合预期

2. 示例2：5个非空值
   区域A1:E1为40、39、43、45、48，其余为空：

   • COUNTA(A1:I1)返回5，MIN(5,3)得到3
   • 生成序列{1,2,3}，SMALL提取39、40、43
   • 平均值为(39+40+43)/3≈40.7，符合预期

简化写法（Excel 365/2021及以上版本） #

如果使用支持动态数组的Excel版本，可使用更直观的公式：

=AVERAGE(TAKE(SORT(FILTER(A1:I1,A1:I1<>"")),,MIN(COUNTA(A1:I1),3)))

• FILTER(A1:I1,A1:I1<>"")：筛选出所有非空成绩
• SORT(...)：对筛选结果升序排序
• TAKE(..., ,MIN(...))：取排序后的前N个值（N为实际非空数与3的较小值）
• AVERAGE(...)：计算平均值

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