Excel新增/删除行时自动更新求和范围的技术方案咨询

方案1：使用Excel结构化表格（推荐，无需宏） #

• 选中原始数据区域任意单元格，按Ctrl+T，勾选「我的表格有标题」（若有表头），点击确定将普通区域转为Excel结构化表格。
• 在results工作表的求和单元格中，使用结构化引用公式，例如统计原始表格「销售额」列总和：

  =SUM(表名[销售额])
  

  （替换「表名」为实际表格名称，「销售额」为对应列的表头名称）
• 效果：新增行时表格自动扩展范围，删除行时公式自动收缩，全程无需手动调整，原生支持动态更新。

方案2：使用动态范围公式（无需转表格） #

如果不想转换为结构化表格，可通过INDEX结合其他函数构建动态求和范围：

针对无空白单元格的列 #

若目标列无空白行（表头除外），使用以下公式：

=SUM(A1:INDEX(A:A,COUNTA(A:A)))

• 原理：COUNTA(A:A)统计A列非空单元格数量，INDEX(A:A, 数量)定位最后一个非空单元格，求和范围自动锁定到A1至最后有数据的行。

针对可能有空白单元格的列 #

若列中间存在空白，用MATCH定位最后一个非空单元格：

=SUM(A1:INDEX(A:A,MATCH("*",A:A,-1)))

• 原理：MATCH("*",A:A,-1)从A列底部向上查找第一个非空单元格的行号，确保求和范围包含所有已填充行，不受中间空白影响。

方案3：使用VBA宏自动更新 #

如需定制化控制，可编写工作表事件宏，在原始数据行变化时自动更新results表公式：

1. 右键点击原始数据所在工作表标签，选择「查看代码」打开VBA编辑器。
2. 粘贴以下代码（根据实际情况修改工作表名、目标单元格和列范围）：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastRow As Long
    Dim wsData As Worksheet
    Dim wsResults As Worksheet
    
    ' 设置工作表对象，替换为你的实际表名
    Set wsData = ThisWorkbook.Worksheets("原始数据")
    Set wsResults = ThisWorkbook.Worksheets("results")
    
    ' 获取数据区域最后一行（假设数据从A列开始）
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    
    ' 更新results表的求和公式，示例：B2统计A列，B3统计B列，B4统计C列
    wsResults.Range("B2").Formula = "=SUM('" & wsData.Name & "'!A1:A" & lastRow & ")"
    wsResults.Range("B3").Formula = "=SUM('" & wsData.Name & "'!B1:B" & lastRow & ")"
    wsResults.Range("B4").Formula = "=SUM('" & wsData.Name & "'!C1:C" & lastRow & ")"
End Sub

3. 关闭VBA编辑器，将文件保存为「启用宏的工作簿（.xlsm）」。

• 效果：每当原始数据工作表新增、删除行或修改单元格时，results表的求和公式会自动更新至最新行范围。

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