跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

如何在Excel中排除列中的单元格进行求和？

Author Siluvia Last modified

在Excel中处理数据时，您可能会经常需要计算某个区域的总和，同时有意跳过某些单元格——例如，这些单元格包含异常值、错误、无关数据或您希望排除在汇总之外的特定值。如下图所示，假设您有一列数字，但希望对除了A3和A4单元格之外的所有单元格求和。本文将全面展示几种实用方法，帮助您在Excel中求和时排除一个或多个指定单元格，确保您在财务分析、报表或库存管理等需要选择性计算的任务中获得准确的结果。

a screenshot of the original data range and the cells you want to exclude from a sum

使用公式排除列中的单元格进行求和

通过在SUM公式中使用简单的算术运算，您可以直接在计算中排除不需要的单元格。此方法适用于需要快速计算且排除项较少的情况。请按照以下步骤操作：

1. 选择一个空白单元格以显示求和结果，并在编辑栏中输入以下公式，然后按Enter键以计算总和并排除特定单元格。例如：

=SUM(A2:A7)-SUM(A3:A4)

a screenshot of using formula to exclude the cell A3 and A4 from sum

解释与提示：

  • SUM(A2:A7) 计算整个区域，而 SUM(A3:A4) 减去排除单元格的值。当要排除的单元格是连续的时候，这种方法效果最佳。
  • 如果排除的单元格是非连续的，可以轻松混合和减去多个排除单元格。例如，要从范围内排除A3和A6，调整公式如下：

=SUM(A2:A7)-A3-A6

a screenshot of using formula to exclude discontinuous cells A3 and A6 from a sum

  • 如果排除的单元格分散或数量较多，手动列出每个排除的单元格会使公式变得更长且难以管理。
  • 注意单元格引用：如果您的数据或范围发生变化，请相应更新公式以避免错误。

VBA代码 – 通过编程方式求和范围，跳过/排除指定单元格

对于需要排除大量单元格或需要频繁重复该过程的场景，使用VBA宏提供了灵活性和自动化。通过VBA，您可以对指定范围求和，并通过编程方式定义排除任意数量的单元格，无论是连续的还是不连续的。此方法适用于熟悉VBA环境并希望简化更复杂排除逻辑的用户。

注意事项：VBA宏可以修改您的工作簿。在运行新代码之前，请始终保存您的工作。必须启用宏才能运行上述内容。

1. 转到开发工具 > Visual Basic 打开VBA编辑器。在项目窗口中，右键单击您的工作簿，选择插入 > 模块，并将以下代码粘贴到模块中：

Sub SumWithExclusions()
    Dim sumRange As Range
    Dim excludeCells As Range
    Dim cell As Range
    Dim result As Double
    Dim xTitleId
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set sumRange = Application.InputBox("Select the range to sum", xTitleId, Type:=8)
    Set excludeCells = Application.InputBox("Select cells to exclude (use Ctrl+Click to select multiple)", xTitleId, Type:=8)
    
    result = 0
    If Not sumRange Is Nothing Then
        For Each cell In sumRange
            If Not Application.Intersect(cell, excludeCells) Is Nothing Then
                ' Skip excluded cells
            Else
                result = result + cell.Value
            End If
        Next
        
        MsgBox "The sum excluding specified cells is: " & result, vbInformation
    Else
        MsgBox "No range selected.", vbExclamation
    End If
End Sub

2. 点击 Run button 在VBA窗口中运行或按 F5 执行宏。将弹出一个对话框，提示您选择要求和的完整范围，然后选择要排除的单元格（按住Ctrl选择多个）。宏将在消息框中显示结果。

  • 如果您误选了单元格，重新运行宏即可。该过程具有交互性和灵活性。
  • 宏最适合用于日常任务或基于复杂条件的排除。

Excel公式 – 使用SUMIF或SUMIFS函数仅包括不符合排除条件的值

对于更高级或基于逻辑的排除，您可以使用SUMIF或SUMIFS函数。当您的排除基于值、条件或有一份要避免的值列表时，这些公式非常有效。

示例 – 基于特定值排除

1. 如果您想对A2:A7求和但排除值“16”，请在目标单元格中输入以下公式（例如，在B1单元格中）：

=SUMIF(A2:A7,"<>16")

此公式会对A2:A7范围内的所有值求和，排除等于16的值。

2. 输入公式后，按Enter键。您可以根据需要复制或调整范围/单元格引用。

示例 – 排除与单元格值匹配的所有单元格

假设C1单元格包含您希望从总和中排除的值：

=SUMIF(A2:A7,"<>"&A3)
注意：此公式对A2:A7中所有不等于C1值的值求和。如果A2:A7中有多个单元格包含与C1相同的值，它们都将被排除在总和之外。

根据需要更新C1，公式将动态排除所有匹配的值。

  • 对于多个排除条件或更复杂的规则，可以考虑结合辅助列或数组使用SUMIFS。然而，当排除基于特定且一致的标准时，SUMIF/SUMIFS效果最佳，而不是任意的单元格位置。
  • 如果您的范围包含文本或空白单元格，SUMIF会自动忽略它们；请确保这是预期的行为。

Excel公式 – 使用FILTER函数（新版Excel）在求和前过滤掉排除的单元格

如果您使用的是Microsoft 365版Excel或Excel 2021及更高版本，FILTER函数可以在应用SUM之前动态灵活地排除单元格。这对于大型数据集或变化的排除条件尤其有用。

示例 – 排除特定值（例如16和13）

1. 在目标单元格（例如B1）中输入以下公式：

=SUM(FILTER(A2:A7,(A2:A7<>16)*(A2:A7<>13)))

这将对A2:A7中的所有值求和，排除等于16和13的值。FILTER函数创建一个仅包含不等于这些值的单元格的数组，然后SUM将其加起来。

2. 按Enter键。如果排除项或源数据发生变化，计算将动态更新。

  • 要根据列表动态排除值（例如，排除列表在C2:C4中）：
=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))

此公式排除A2:A7中与C2:C4中任何值匹配的任何值。只需在C列中更新排除列表，公式结果将自动更新。

  • 对于使用最新Excel版本并寻求动态、可扩展排除逻辑的用户，推荐使用基于FILTER的方法。
  • 如果您收到#CALC!错误，请检查是否在所有排除后范围内至少剩下一个值；否则，FILTER将返回错误。

总之，Excel提供了几种实用的解决方案，可以在求和时排除特定单元格或值。简单的公式适合快速、少量的排除，而SUMIF/SUMIFS和FILTER支持更灵活、基于条件的场景。当排除项众多、多样或需要自动化时，VBA是理想选择。更改源数据时，请务必仔细检查单元格引用和公式调整。如果遇到错误，请验证范围或排除列表，并尝试重新应用公式或重新运行宏。

相关文章：

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件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中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠