excel公式计算数据去掉最高分和最低分怎么操作

       在日常的数据处理工作中，我们常常会遇到需要计算一组数据的平均值，但又希望排除掉可能存在的极端值，比如最高分和最低分，以获得更客观、更具代表性的中心趋势指标。无论是教师统计学生成绩去掉最高分和最低分，还是评委在打分后需要计算选手的最终平均分，亦或是数据分析师在处理市场调研数据时希望排除异常值，这个需求都非常普遍。因此，熟练掌握在Excel中实现这一操作的方法，对于提升工作效率和数据准确性有着直接的意义。

       很多初次接触这个问题的朋友可能会觉得，无非就是把数据加起来，减去最大值和最小值，再除以个数减二。这个思路完全正确，但在Excel中如何用公式优雅且高效地实现，却有很多细节和不同的方法值得探讨。不同的数据场景、不同的计算要求，可能需要选择不同的函数组合。接下来，我将为你系统性地梳理几种主流且实用的方法，从最简单的函数组合到更智能的动态数组公式，并结合实际示例，让你彻底搞懂“excel公式计算数据去掉最高分和最低分怎么操作”这个问题的所有门道。

理解需求：为何要去掉最高分和最低分？

       在深入探讨操作方法之前，我们有必要先理解这个操作背后的逻辑。去掉最高分和最低分，在统计学上可以看作是一种简单的“截尾平均”处理。其目的是减少异常值，或称离群值，对平均数造成的扭曲影响。例如，在竞技比赛中，个别评委可能因为个人偏好给出极高或极低的分数，这些分数并不能真实反映选手的普遍水平。通过剔除它们，得到的平均数更能代表“大众观点”或“普遍表现”，使得评估结果更加公平和稳健。理解了这一点，我们在应用公式时就能更清楚地知道自己在做什么，以及结果代表了什么意义。

方法一：使用TRIMMEAN函数——最直接的解决方案

       Excel专门为计算截尾平均数准备了一个内置函数，那就是TRIMMEAN。这个函数的设计初衷就是排除数据集中头部和尾部一定百分比的数据点后，再计算剩余数据的平均值。它的语法非常简单：TRIMMEAN(数组, 百分比)。其中，“数组”就是你的数据区域，比如A2:A10；“百分比”是指定要从数据集的头部和尾部共同排除的数据点比例。

       举个例子，如果你有10个数据，想去掉一个最高分和一个最低分（即总共去掉2个数据），那么需要排除的比例就是 2/10 = 0.2。所以公式可以写为：=TRIMMEAN(A2:A11, 0.2)。这个公式会自动找出区域中的最大值和最小值（如果比例指定正确，就是各去掉一个），将它们排除在计算之外，然后对剩下的8个数字求平均。它非常智能，即使数据没有经过排序，也能准确工作。

       使用TRIMMEAN函数的优点是公式简洁，意图明确。但需要注意，它排除的是“比例”而非“固定个数”。如果你的数据个数不是10的整数倍，或者你想固定去掉头尾各一个，而不管总共有多少个数据时，计算百分比就需要小心。例如，有7个数据，想去掉一个最高和一个最低，比例应为 2/7。

方法二：基础函数组合法——理解计算本质

       如果你希望更清晰地掌控计算过程的每一个环节，或者使用的Excel版本较旧，那么使用SUM、MAX、MIN、COUNT这几个基础函数的组合是一个绝佳的选择。这个方法的思路完全遵循我们最开始的数学逻辑：总和减去极值，再除以数量减二。

       假设你的数据存放在B2:B9这个区域，那么公式可以写为：=(SUM(B2:B9) - MAX(B2:B9) - MIN(B2:B9)) / (COUNT(B2:B9)-2)。让我们拆解一下：SUM(B2:B9)计算所有数据的总和；MAX(B2:B9)找出其中的最大值；MIN(B2:B9)找出其中的最小值。分子部分就是去掉极值后的总和。分母部分，COUNT(B2:B9)统计区域内数字的个数，减去2（即去掉的两个分数），得到有效数据的个数。最后两者相除，即得到所求的平均值。

       这种方法的优点是非常直观，任何看到公式的人都能立刻理解其计算逻辑。它不依赖于特定的函数，兼容性极强。同时，它也便于我们进行变形，例如，如果我们想去掉两个最高分和两个最低分，只需要将公式稍作修改为减去两个最大值和两个最小值之和，分母相应地减去4即可，虽然找“第二大值”需要用到LARGE函数，但思路是延续的。

方法三：应对重复极值的场景

       前面两种方法在处理常规数据时表现良好，但在实际工作中，我们可能会遇到一个特殊情况：数据中最高分或最低分不止一个。例如，三个评委都打了满分，或者两个评委都打了最低分。这时，我们的需求可能产生分歧：是去掉所有重复的最高分和最低分，还是只去掉一个实例？

       如果采用TRIMMEAN函数，它是按比例排除，如果最高分有多个相同的值，它们可能都位于数据集的头部，因此可能会被全部排除掉，这取决于你设定的百分比是否足够大。这有时符合需求，有时则不符合。

       如果采用基础函数组合法，公式=SUM-MAX-MIN/(COUNT-2)只会各去掉一个最大值和一个最小值，即使有重复，也只会减掉一个。如果你希望去掉所有重复的极值，公式就需要变得更复杂。一种思路是使用SUMIF函数进行条件求和。例如，求和不包括等于最大值和最小值的所有项：=SUMIFS(B2:B9, B2:B9, “<>”&MAX(B2:B9), B2:B9, “<>”&MIN(B2:B9))。然后再除以满足条件的数字个数，这可以用COUNTIFS函数来实现。这种组合提供了最大的灵活性，可以精确控制要去除哪些数据。

方法四：使用LARGE和SMALL函数进行扩展

       当需求升级，比如需要去掉两个最高分和两个最低分时，LARGE和SMALL函数就派上了大用场。LARGE(数组, k)可以返回数组中第k大的值，SMALL(数组, k)则返回第k小的值。要去掉前两大值，我们可以用SUM减去LARGE(区域,1)和LARGE(区域,2)；同理，减去SMALL(区域,1)和SMALL(区域,2)。

       最终的公式形态为：=(SUM(B2:B10) - LARGE(B2:B10,1) - LARGE(B2:B10,2) - SMALL(B2:B10,1) - SMALL(B2:B10,2)) / (COUNT(B2:B10)-4)。这个公式清晰地表达了去掉两个最高和两个最低分的意图。你可以通过增加或减少LARGE/SMALL函数的项数，来灵活控制要去掉的极值数量，非常适合在评审规则明确要求去掉多个极值的场景下使用。

方法五：动态数组函数的现代解法

       如果你使用的是微软365或2021版Excel，那么恭喜你，可以享受到动态数组函数带来的强大与便捷。FILTER函数可以让我们以声明式的方法轻松过滤掉不需要的数据。思路是先创建一个条件，筛选出所有既不是最大值也不是最小值的项。

       假设数据在C2:C10，我们可以这样写：=LET(data, C2:C10, filteredData, FILTER(data, (data <> MAX(data)) (data <> MIN(data))), AVERAGE(filteredData))。这个公式使用了LET函数来提高可读性和计算效率。它首先将数据区域定义为“data”，然后使用FILTER函数，筛选条件是：数据不等于最大值，并且（用乘号表示“且”）数据不等于最小值。筛选出的结果被赋给“filteredData”，最后对这个结果求平均值。

       这种方法的优势在于思维逻辑非常直接——“先排除，后平均”。公式清晰地反映了我们的操作步骤，而且利用FILTER函数，我们可以轻松扩展条件，例如排除大于某个阈值或小于某个阈值的所有值，而不仅仅是最大最小值。

方法六：处理非连续数据区域

       有时，我们需要计算的数据并不是连续排列在一列或一行中，而是分散在工作表的各个位置。例如，评分可能分布在不同的列对应不同的项目。这时，我们可以通过联合引用将多个区域组合成一个虚拟的数组供函数计算。

       对于SUM、MAX、MIN等函数，它们本身支持多区域引用，例如=SUM((A2:A5, C2:C5, E2:E5))。但需要注意的是，在旧版本中，这种直接联合引用可能在部分函数中无法正常工作。一个更通用的方法是使用CHOOSE函数来构建数组。例如，对于基础函数组合法，公式可以写为：=(SUM(CHOOSE(1,2,3, A2:A5, C2:C5, E2:E5)) - MAX(CHOOSE(...)) - MIN(CHOOSE(...))) / (COUNT(CHOOSE(...))-2)。在动态数组版本中，则可以使用VSTACK函数将多个区域垂直堆叠成一个数组，然后再进行处理。

方法七：将公式封装为自定义名称

       如果你需要频繁地在同一个工作簿的不同地方进行此类计算，每次都输入一长串公式既麻烦又容易出错。这时，你可以考虑使用“定义名称”功能，将一个复杂的公式定义为一个简短的名称，比如“TrimmedAvg”。

       操作步骤是：点击“公式”选项卡下的“定义名称”，在弹出的对话框中，名称输入“TrimmedAvg”（或任何你喜欢的名字），引用位置输入你的核心公式，例如= (SUM(Sheet1!$B$2:$B$9) - MAX(Sheet1!$B$2:$B$9) - MIN(Sheet1!$B$2:$B$9)) / (COUNT(Sheet1!$B$2:$B$9)-2)。定义完成后，你在任何单元格中输入=TrimmedAvg，就可以直接得到计算结果。这大大提升了公式的复用性和工作表的整洁度。

方法八：结合排序功能进行可视化验证

       对于重要的数据计算，尤其是当公式结果影响到关键决策时，进行人工验证是一个好习惯。Excel的排序功能可以在这里帮上忙。你可以将原始数据复制一列，然后对这列数据进行升序或降序排序。

       排序后，最高分和最低分会清晰地出现在两端。此时，你可以手动选中中间部分的数据，观察Excel状态栏上显示的“平均值”，与你公式计算的结果进行比对。这种方法虽然不能作为日常计算流程，但却是理解和验证公式逻辑的绝佳途径，也能帮助你在数据出现异常时快速定位问题。

方法九：处理包含文本或空值的数据区域

       现实中的数据往往并不完美，你的数据区域里可能混杂着文本说明、错误值或是空单元格。如果直接对这样的区域使用SUM、MAX等函数，可能会导致公式返回错误。

       一个稳健的做法是使用AGGREGATE函数。AGGREGATE函数功能强大，它可以在计算时忽略错误值、隐藏行、嵌套 subtotal等。例如，你可以使用AGGREGATE(1, 6, 数据区域)来求平均值，并忽略错误值。但要去掉极值，我们需要更巧妙的组合。我们可以用AGGREGATE(4, 6, 区域)求最大值（忽略错误），用AGGREGATE(5, 6, 区域)求最小值，然后用SUMIFS或SUMPRODUCT配合条件来求和及计数。虽然公式稍显复杂，但它能确保在数据不“干净”的情况下，公式依然能够稳定运行，返回一个有意义的结果。

方法十：在数据透视表中实现去极值平均

       如果你习惯使用数据透视表来汇总和分析数据，同样可以实现去掉最高分和最低分后求平均。数据透视表默认的“平均值”汇总方式是无法直接实现这一点的，但我们可以通过添加计算字段来“曲线救国”。

       首先，在数据透视表分析选项卡中，找到“字段、项目和集”，选择“计算字段”。在弹出的对话框中，你可以新建一个字段，其公式可以引用原始数据字段，并运用我们前面介绍过的函数组合逻辑。不过，需要注意的是，数据透视表中的计算字段公式在引用字段时，其上下文是总计级别的，因此直接使用MAX、MIN函数可能无法按分组正确计算。更可靠的方法是在原始数据源旁边，先使用公式计算出每一行数据是否属于该分组的极值（可能需要用到SUBTOTAL或AGGREGATE函数在分组内计算），然后将这个标志字段添加到数据透视表中，通过筛选或作为计算条件来排除极值。

方法十一：使用VBA编写自定义函数

       对于有编程基础的用户，如果上述所有公式方法都觉得不够用或不够灵活，那么使用VBA（Visual Basic for Applications）编写一个用户自定义函数是终极解决方案。你可以创建一个名为`CustomTrimMean`的函数，它不仅可以接受数据区域作为参数，还可以接受要去掉的最高分和最低分的个数，甚至可以处理重复值、空值、指定取舍规则等。

       编写好后，这个函数就可以像内置函数一样在工作表中使用，例如=CustomTrimMean(A1:A20, 2, 2)表示去掉两个最高分和两个最低分后求平均。这提供了无与伦比的灵活性和可定制性，特别适合在复杂、固定的工作流程中部署，一次开发，多次使用。

方法十二：注意事项与常见误区总结

       最后，我们来总结一下在整个操作过程中需要注意的关键点和常见误区。首先，务必明确你的需求：是严格去掉一个最高分和一个最低分，还是按比例排除？是否需要处理重复的极值？其次，选择公式时，要考虑到数据的“清洁度”和Excel的版本兼容性。TRIMMEAN虽方便，但比例计算要准确；基础组合法直观，但要确保分母（COUNT-2）不为零，即数据个数至少为3个。

       第三，当数据区域可能变动或增减时，建议使用结构化引用（如果数据在表格中）或定义动态名称，避免因区域改变而导致公式引用错误。第四，对于重要的计算结果，尤其是用于汇报或决策的数据，建议保留原始数据并清晰标注所使用的计算方法，以便核查和审阅。

       掌握在Excel中去掉最高分和最低分的计算，看似是一个简单的公式应用，实则涉及到数据处理的严谨思维。从理解统计意义，到根据具体场景选择最合适的工具，再到验证和优化计算过程，每一步都体现了数据分析工作的专业性。希望这篇详尽的指南，能帮助你不仅学会“excel公式计算数据去掉最高分和最低分怎么操作”这个具体技能，更能举一反三，从容应对更多类似的数据清洗与汇总需求。