Excel平均分计算必知，为何去掉最高分？

Excel计算平均分时为什么要去掉最高分？专业解读与操作指南

在Excel中处理成绩、评分或比赛数据时，去掉最高分（有时也去掉最低分）再计算平均分，核心目的是为了消除极端值对整体平均水平的扭曲影响，从而得到更能代表“典型”或“普遍”水平的平均值。 这背后的原理和应用场景非常值得深入了解。

平均值（算术平均数）的局限性

Excel中最常用的AVERAGE函数计算的是算术平均数，其公式为：
平均值 = (所有数据之和) / (数据个数)

• 优点： 计算简单,意义直观。
• 缺点： 对极端值（极大值或极小值）非常敏感。 一个异常高或异常低的分数会显著地将平均值拉向它所在的方向。

极端值（最高分/最低分）带来的问题

想象以下场景：

1. 评委打分： 在歌唱比赛、体操比赛等评委打分制中，某个评委可能因为个人偏好（特别喜欢或特别不喜欢）、评分标准理解偏差、甚至操作失误,给出了一个远高于或远低于其他评委的分数。
2. 学生成绩： 某个学生可能因特殊情况（如考试时严重失误、身体不适，或题目理解完全错误）得到了一个极低分；或者因作弊、题目泄露等原因得到了一个异常高分。
3. 用户评分/满意度调查： 可能存在恶意刷高分或刷低分的情况。

如果直接计算包含这些极端值的算术平均数：

• 一个异常高的分数（最高分）会显著拉高整体平均值。
• 一个异常低的分数（最低分）会显著拉低整体平均值。

结果： 这个计算出来的“平均分”不能真实、公平地反映大多数评委的共识、大多数学生的实际水平或大多数用户的真实感受，它被少数极端数据“污染”了。

去掉最高/低分：截尾均值的优势

为了解决算术平均数对极端值敏感的问题，统计学中引入了截尾均值（Trimmed Mean）的概念。

• 原理： 在排序后的数据序列两端，对称地去掉一定比例（例如5%、10%）的最高值和最低值,然后用剩下的中间数据计算算术平均数。
• 在平均分计算中的应用：
  • 去掉最高分： 主要消除异常高分的干扰,防止其不公正地拉高平均分。
  • 去掉最低分： 主要消除异常低分的干扰,防止其不公正地拉低平均分。
  • 同时去掉最高和最低分： 这是最常见的做法（尤其在评委打分中），目的是同时消除两个方向的极端值影响，使结果更加稳健（Robust）,更能反映中间群体的集中趋势。
• 优势：
  • 抗干扰性强： 对数据中的少数异常值不敏感。
  • 更公平、更具代表性： 结果更能反映“大多数”数据的中心位置,减少了偶然因素或个别偏差对整体评价的影响。
  • 广泛认可： 在体育赛事评分、学术评审、市场调研、用户评价分析等领域被广泛采用,作为更公平的评价标准。

如何在Excel中实现“去掉最高最低分计算平均分”

假设你的分数数据在A2:A11区域（共10个分数）,目标是去掉1个最高分和1个最低分后计算剩下8个分数的平均值。

方法1：组合SUM、LARGE、SMALL函数 (最常用且灵活)

=(SUM(A2:A11) - LARGE(A2:A11, 1) - SMALL(A2:A11, 1)) / (COUNT(A2:A11) - 2)

• SUM(A2:A11)： 计算所有10个分数的总和。
• LARGE(A2:A11, 1)： 找出第1大的分数（即最高分）。
• SMALL(A2:A11, 1)： 找出第1小的分数（即最低分）。
• SUM(...) - LARGE(...) - SMALL(...)： 从总分中减去最高分和最低分,得到剩下8个分数的总和。
• COUNT(A2:A11) - 2： 计算有效分数的个数（10 – 2 = 8）。
• 总和 / 个数： 计算截尾平均值。

方法2：组合TRIMMEAN函数 (专为截尾均值设计)

=TRIMMEAN(A2:A11, 2/COUNT(A2:A11))

• A2:A11： 包含分数的数据区域。
• 2/COUNT(A2:A11)： 指定要截去的数据点比例。
  • COUNT(A2:A11) 计算总数据点个数（10）。
  • 2/10 = 0.2 或 20%，表示总共要截去20%的数据（即10个数据的20%是2个数据）。
  • TRIMMEAN 会自动在数据排序后，对称地去掉两端各10%的数据（即去掉最高的1个和最低的1个），然后用剩下的中间80%的数据（8个）计算平均值。
• 优点： 函数目的明确，公式简洁。缺点： 当需要去掉的个数不是严格对称时（比如总数为奇数，或明确要求只去最高不去最低）,不如方法1灵活。

方法3：使用AVERAGE配合IF和数组公式 (较复杂，不推荐新手)

=AVERAGE(IF((A2:A11 <> MAX(A2:A11)) * (A2:A11 <> MIN(A2:A11)), A2:A11))

• 这是一个数组公式，在较新版本的Excel (Office 365, Excel 2021+) 中，按Enter即可，在旧版本中，需要按 Ctrl+Shift+Enter 确认。
• 原理： 创建一个逻辑数组，筛选出不等于最大值且不等于最小值的单元格,然后对这些单元格求平均。
• 注意： 如果最高分或最低分有重复，此方法会去掉所有重复的最高/低分，而方法1和方法2通常只去掉一个最高分和一个最低分（除非特别指定）。

选择建议： 方法1（SUM+LARGE+SMALL） 是最直观、最灵活、兼容性最好的首选方法，尤其当你明确知道要去掉的具体个数（如1个最高1个最低）时。方法2（TRIMMEAN） 在需要按比例截尾时更简洁。

重要注意事项

1. 明确规则： 在应用此方法前，务必清楚规则是只去最高分、只去最低分，还是同时去掉最高和最低分？去掉几个（或比例是多少）？这直接影响公式的选择和编写。
2. 数据重复： 如果最高分或最低分有多个相同的值，上述方法1和方法2通常只去掉其中一个实例（如LARGE(…,1)只返回最大的那个值，即使有多个并列最大，也只去掉一个），如果规则要求去掉所有并列的最高/低分，则需要更复杂的公式（如方法3，或结合RANK等函数）。
3. 数据量： 当数据量很少时（如只有3个分数），去掉最高最低分后只剩1个分数，此时计算“平均”意义不大,需谨慎使用。
4. TRIMMEAN的对称性： TRIMMEAN 总是对称地去掉两端数据，指定去掉20%，它会自动去掉最高端的10%和最低端的10%，无法用它实现“只去掉最高分不去最低分”这种不对称操作。
5. 应用场景： 这种方法并非万能，它主要适用于存在明显、个别的异常值干扰，且这些异常值并非我们想关注的“典型”情况时，如果数据分布本身就很广或者存在多个异常值，截尾均值可能也不是最佳选择，需结合其他统计量（如中位数）分析。

在Excel中计算平均分时选择去掉最高分（和/或最低分），是一种基于统计稳健性考虑的常用策略，它通过排除数据中可能存在的、非典型的极端值（异常高分或异常低分），有效防止这些“离群点”对整体平均水平的过度影响，从而计算出更能代表主体数据集中趋势、更公平、更可靠的“平均”结果，无论是评委打分力求公正，还是分析成绩避免个别极端情况干扰,掌握在Excel中实现截尾平均分的方法都是数据处理中一项有价值的技能。

引用说明：

• 本文中关于算术平均数局限性和截尾均值（Trimmed Mean）的概念与优势，核心原理源自统计学基本理论，可参考经典统计学教材如《统计学》（David Freedman等著）或《Introductory Statistics》（Neil A. Weiss著）中关于集中趋势度量及稳健估计的章节。
• Excel函数（AVERAGE, SUM, LARGE, SMALL, COUNT, TRIMMEAN, MAX, MIN, IF）的功能描述与应用示例，依据Microsoft Office官方支持文档及函数说明 (support.microsoft.com/excel)。
• 评分实践中去除最高/低分的做法，广泛见于国际体育组织（如国际体操联合会FIG、国际滑冰联盟ISU）的竞赛规则以及各类学术、商业评审规程中,体现了对评价公平性与结果稳健性的共同追求。