删除Excel行引发#REF!引用错误的解决方法咨询

原公式=IF(A1<>A2,1-SUM(B1),SUM(B1))在删除行后，因引用的单元格被移除导致出现#REF!错误，以下是几种可行的修正方案：

• 方法一：用INDIRECT函数动态锁定行号引用
  将B2单元格的公式修改为：

  =IF(INDIRECT("A"&ROW()-1)<>A2,1-SUM(INDIRECT("B"&ROW()-1)),SUM(INDIRECT("B"&ROW()-1)))
  

  利用ROW()-1获取当前行的上一行行号，通过INDIRECT函数将行号转换为单元格引用。删除行后，ROW()会自动更新对应行号，避免出现无效引用。

• 方法二：用OFFSET函数实现动态偏移
  另一种写法是通过OFFSET函数基于当前单元格向上偏移一行来引用数据：

  =IF(OFFSET(A2,-1,0)<>A2,1-SUM(OFFSET(B2,-1,0)),SUM(OFFSET(B2,-1,0)))
  

  OFFSET(B2,-1,0)表示从B2单元格向上偏移1行的位置，行删除后偏移逻辑会自动调整，不会产生#REF!错误。

• 方法三：使用结构化表格（List Object）
  选中数据区域按Ctrl+T将其转换为结构化表格，假设表格名为Table1，B列公式可改为结构化引用：

  =IF(Table1[@[A列]]<>Table1[#Above],1-SUM(Table1[#Above]),SUM(Table1[#Above]))
  

  结构化引用会自动适配行的增删操作，完全避免#REF!问题，同时还能享受表格自带的格式维护、筛选等功能。

注意事项 #

• INDIRECT和OFFSET属于易失性函数，数据量较大时可能影响Excel运算性能，此时优先选择结构化表格方案。
• 修改公式后可通过删除中间行测试，验证公式是否能正常计算。

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