求可批量计算的Array Formula：忽略两门最低分后算成绩平均值

Google Sheets 实现方案 #

在H2单元格输入以下数组公式，按回车即可自动批量处理所有学生：

=ARRAYFORMULA(IF(B2:B="", "", BYROW(B2:G, LAMBDA(row, (SUM(row) - SUM(SMALL(row, {1,2})))/4))))

Excel 365/2021 实现方案 #

Excel支持动态数组，直接在H2输入公式回车，结果会自动溢出到所有对应行：

=IF(B2:B="","",BYROW(B2:G,LAMBDA(x,(SUM(x)-SUM(SMALL(x,{1,2})))/4)))

公式逻辑解析 #

• 空行过滤：IF(B2:B="", "", ...) 跳过无学生信息的空行，避免生成无效结果
• 逐行处理：BYROW 函数遍历每行的6次测试成绩，确保每个学生的计算独立进行
• 核心计算：
  1. SUM(row) 计算当前行6次测试的总分
  2. SUM(SMALL(row, {1,2})) 提取并累加当前行的两个最低分
  3. 用总分减去两个最低分的和，再除以4得到剩余4个有效分数的平均值

使用注意事项 #

• 适配实际列范围：将公式中的B2:G替换为你表格中存放六次测试成绩的真实列区间
• 自动适配班级规模：公式会根据学生姓名列（示例中为B列）的非空行自动扩展计算范围，无需手动下拉
• 旧版Excel兼容：若使用Excel 2019及更早版本，需输入公式后按Ctrl+Shift+Enter触发数组计算，公式调整为：

  =IF(B2:B="","",(SUM(B2:G,1)-SMALL(B2:G,1)-SMALL(B2:G,2))/4)
  

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