KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中剔除最低分后，计算其余数值的平均值或总和？

作者Xiaoyang修改日期

在 Excel 中处理学生成绩时，您可能需要先剔除最低分（甚至最低的 n 个分数），再对剩余成绩计算平均值或总和，以得出最终成绩。这种做法在教育场景中十分常见——例如允许学生舍弃表现最差的一次成绩，以应对突发状况或确保评分公平。然而，手动操作不仅耗时费力，尤其在面对大型数据集或频繁调整计算规则时更显繁琐。值得庆幸的是，Excel 提供了多种灵活高效的解决方案，无论是使用简洁公式，还是借助 VBA 实现批量自动化处理，都能轻松满足您的需求。

使用公式舍弃最低分并计算平均值或总和

VBA 代码——自动舍弃最低分或最低 n 个分数并计算总和或平均值

蓝色右箭头气泡使用公式舍弃最低分并计算平均值或总和

如果您希望从数据行或列表中排除最低值或最低的 n 个值，再对剩余数值进行计算（如求平均值或总和），Excel 的内置公式提供了一种高效实用的解决方案。当您处理中等规模的数据行，或倾向于使用公式以确保计算过程透明、便于后续调整时，这种方法尤为适用。

以下分别详述了求和与求平均值的方法，涵盖具体操作步骤及灵活应用的实用技巧。

对数值求和但舍弃最低值或最低 N 个数值：

若要在每行或列表中计算总和并排除最低值，请使用以下方法：

1. 选择一个空白单元格用于显示第一行的求和结果（例如，若您的数据位于 B2:H2，则选择 I2 单元格），然后输入以下公式：

=SUM(B2:H2)-SMALL(B2:H2,1)

2. 按 Enter 确认，然后向下拖动填充柄，即可将此公式应用到其他行，对每行中的所有数值求和并自动排除最低值。

参考截图如下：

使用公式对数字求和但排除最小值

注意事项与技巧：

  • 要排除最低的两个、三个或更多数值，您只需在公式中减去额外的 SMALL 结果即可轻松实现。例如：
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)
  • 在这些公式中，B2:H2 是您要汇总的区域，数字 1、2、3 等用于指定需排除的最小数值个数。请根据您希望舍弃的最低成绩数量，调整 n 值。
  • 请注意，不要将 n 设置为大于或等于总数值数量，否则将导致错误或非预期结果。
  • 这些公式按行独立运行。如果您的数据是按列而非按行排列，请相应调整区域范围。
  • 如果您的数据集中包含最低数值的重复项，SMALL(B2:H2,1) 每次引用仅会舍弃一个实例。若要舍弃多个重复项，请按上述方式重复使用 SMALL 函数，并依次递增 k 值。

对数值求平均但舍弃最低值或最低 N 个数值：

若要在计算平均值时忽略最低值或最低的 n 个值，可使用以下公式。该方法在评分体系中尤为实用，例如计算平均分时排除异常低分。

1. 选择一个单元格用于显示平均值结果（例如，若您的成绩位于 B2:H2，则选择 J2 单元格），并输入以下公式：

=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)

2. 按 Enter 后，根据需要向下拖动公式，即可自动计算其他行的平均值，且每次均会排除该行范围内的最低分。

使用公式对数字求平均但排除最小值

注意事项与重要指导：

  • 若要在舍弃多个最低分的同时计算平均值，请通过减去额外的 SMALL 项并相应减少除数来扩展公式：
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)
  • 同样，B2:H2 是要计算平均值的区域，而 n 表示有多少个最低值将被排除在计算之外。
  • 如果尝试减去的数值数量超过区域中的实际数值数量，公式将返回 #NUM!错误，表明用于平均的数值不足。请务必确保 n 小于数值总数。
  • 建议在排除最低值前，仔细确认这些值对您的计算并非关键或必需，以免影响最终结果。
  • 对于超大型数据集，或需动态剔除最低 n 个值的情形，请考虑采用自动化方案或数组公式解决方案。
kutools for excel AI 的截图

借助 KUTOOLS AI 解锁 Excel 的神奇功能

  • 智能执行：只需输入简单命令，即可执行单元格操作、分析数据并创建图表。
  • 自定义公式：生成量身定制的公式，助您优化工作流程！
  • VBA 编码：轻松编写并运行 VBA 代码。
  • 公式解析：轻松掌握复杂公式，一目了然！
  • 文本翻译：轻松打破电子表格中的语言障碍！
借助 AI 驱动的工具提升您的 Excel 能力。立即下载，体验前所未有的高效！

蓝色右箭头气泡 VBA 代码——自动舍弃最低分或最低 n 个分数并计算总和或平均值

对于包含大型或频繁变动的数据集，或需要在多行中自动剔除最低 n 个分数并计算总和或平均值的情形，使用 VBA 可显著简化重复性操作。借助 VBA 宏，您只需指定数据区域及需排除的最低分数数量，代码即可高效地一次性处理所有选定行。

这种自动化方法特别适合需要管理多个班级成绩表的教师，或任何希望减少手动输入公式及由此引发的潜在错误的用户。以下解决方案让您轻松调整需排除的数据数量及目标函数（求和或求平均值）。

开始前，请务必保存您的工作簿，因为运行宏的操作无法撤销。

1. 单击开发工具 > Visual Basic。在 Microsoft Visual Basic for Applications 窗口中，单击插入 > 模块，然后输入以下代码：

Sub DropLowestNandCalculate()
    Dim WorkRng As Range
    Dim OutputRng As Range
    Dim n As Integer
    Dim FuncType As String
    Dim i As Integer, j As Integer, k As Integer
    Dim Arr() As Variant, TempArr() As Double
    Dim RowSum As Double
    Dim RowCount As Integer
    Dim MinIdx() As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
    
    Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
    
    n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
    
    FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
    
    For i = 1 To WorkRng.Rows.Count
        Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
        RowCount = UBound(Arr)
        
        ReDim TempArr(1 To RowCount)
        For j = 1 To RowCount
            TempArr(j) = Arr(j)
        Next j
        
        ' Mark n lowest values as used by setting to very high number
        For k = 1 To n
            Dim MinVal As Double, MinPos As Integer
            MinVal = Application.WorksheetFunction.Min(TempArr)
            
            For j = 1 To RowCount
                If TempArr(j) = MinVal Then
                    TempArr(j) = 1E+308
                    Exit For
                End If
            Next j
        Next k
        
        RowSum = 0
        Dim ValidCount As Integer
        ValidCount = 0
        
        For j = 1 To RowCount
            If TempArr(j) <> 1E+308 Then
                RowSum = RowSum + Arr(j)
                ValidCount = ValidCount + 1
            End If
        Next j
        
        If UCase(FuncType) = "AVG" Then
            If ValidCount = 0 Then
                OutputRng.Cells(i, 1).Value = "N/A"
            Else
                OutputRng.Cells(i, 1).Value = RowSum / ValidCount
            End If
        Else
            OutputRng.Cells(i, 1).Value = RowSum
        End If
    Next i
End Sub

2. 添加代码后，单击运行按钮按钮或按 F5 键即可执行。

3. 请按照屏幕上出现的提示进行操作：

  • 请选择您要处理的成绩区域（确保每位学生的成绩均位于同一行中）。
  • 请选择列表放置区域左上角的单元格名称（输出将根据行数自动向下填充）。
  • 输入要舍弃的最低成绩数量（例如，）1 表示每行仅排除一个最低分）。
  • 输入 SUM 即可获取总分（不含已舍弃的成绩），或输入 AVG 即可获取重新计算的平均分（不含已舍弃的成绩）。

宏将逐行处理指定成绩区域，并将所选的总和或平均值结果填入您指定的列表放置区域。若某行所有成绩均被舍弃，结果将标记为 N/A，以避免错误。

  • 请确保输入区域与您的数据结构一致（每行对应一名学生的成绩）。
  • 非数值单元格（如空白或文本）将被默认忽略。
  • 此 VBA 代码可显著加快全班重复性成绩计算的速度，并支持灵活调整需舍弃的成绩数量。
  • 如果您经常执行此类操作，不妨将此宏分配给工作表上的按钮，以便快速访问。

如果遇到输出错误或异常问题，请仔细检查区域是否已准确指定，并确认“n”未大于或等于每行中可用成绩的总数。

对于类似的自动化需求（例如同时剔除最高分和最低分，或处理列而非行），只需对 VBA 代码逻辑稍作调整即可。

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱