Excel 2019表格插入行时A列公式自动填充错误求助

方法1：替换为不依赖相邻行引用的公式 #

原公式依赖上一行相对引用，插入行时Excel的引用调整逻辑出错。改用基于区域统计的公式，彻底避免引用错位：

• 选中A5单元格（表格第一行数据的下一行），输入公式：
  =SUMPRODUCT(1/COUNTIF($B$4:[$@B],$B$4:[$@B]))
• 选中A5，双击填充柄将公式向下覆盖所有现有数据行
• 后续插入行时，表格会自动填充该公式，完全符合需求：B列内容重复时A列编号相同，内容不同时编号递增

方法2：用VBA自动修正插入行后的公式 #

如果必须保留原有的相邻行判断逻辑，可通过工作表事件自动修正引用：

1. 右键工作表标签，选择「查看代码」打开VBA编辑器
2. 粘贴以下代码：

   Private Sub Worksheet_Change(ByVal Target As Range)
       Dim tbl As ListObject
       Dim affectedRow As Long
       
       Set tbl = Me.ListObjects(1) ' 假设表格是工作表中第一个表格，可根据实际名称修改
       
       ' 检测是否插入了行且影响到表格区域
       If Target.Rows.Count > 0 And Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then
           affectedRow = Target.Row + 1 ' 插入行的下一行
           
           ' 解除工作表保护（需替换为你的保护密码，无密码则省略Password参数）
           Me.Unprotect Password:="your_password"
           
           ' 修正A列公式
           If affectedRow <= tbl.DataBodyRange.Row + tbl.DataBodyRange.Rows.Count - 1 Then
               Me.Cells(affectedRow, "A").Formula = "=IF(B" & affectedRow & "=B" & affectedRow - 1 & ",A" & affectedRow - 1 & ",A" & affectedRow - 1 & "+1)"
           End If
           
           ' 重新保护工作表，保留允许插入行的权限
           Me.Protect Password:="your_password", AllowInsertingRows:=True, UserInterfaceOnly:=True
       End If
   End Sub
   

3. 修改代码中的保护密码（如果有），保存工作簿为「启用宏的工作簿（.xlsm）」
4. 后续插入行时，代码会自动修正下一行的A列公式

方法3：调整表格公式为整列应用 #

将A列公式改为整列统一逻辑，利用表格的结构化引用确保插入行时公式正确：

• 选中A列整个数据区域（从A4到最后一行）
• 输入公式：
  =IF(ROW([@A])=ROW(Table1[#Data]),1,IF([@B]=INDEX([B],ROW([@B])-ROW(Table1[#Data])),INDEX([A],ROW([@A])-ROW(Table1[#Data])),INDEX([A],ROW([@A])-ROW(Table1[#Data]))+1))
  （注意将Table1替换为你的实际表格名称）
• 按Ctrl+Enter批量应用公式
• 此方法下，插入行时表格会自动填充正确的公式，无需手动调整

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