Office 16下Excel实现工作表A插入新行自动同步至B表的方法咨询

你目前采用逐行预设B表公式的方式，仅能覆盖预先填好公式的行范围，因此A表插行后新增的行没有对应公式，无法同步；同时原有公式没有对A表空值做判断，向下拖公式时空单元格会被识别为0，导致显示异常。

第一步：先修复0值显示问题 #

把你B表原有公式优化为带空值判断的版本，比如写在B表B3的公式改成：
=IF('WorksheetA'!B3="","",IFERROR('WorksheetA'!B3,""))
如果需要保留你原有的“删行后自动适配行引用”的逻辑，可保留第二参数的引用：
=IF('WorksheetA'!B3="","",IFERROR('WorksheetA'!B3,'WorksheetB'!B4))
优化后再向下拖公式，A表没有数据的行就会显示为空，不会出0。

第二步：用超级表解决插行自动同步问题 #

• 选中A表任意有数据的单元格，按Ctrl+T，确认弹出的对话框中「表包含标题」已勾选，点击确定，将A表转换为超级表。
• 可以在顶部「表设计」选项卡，把超级表的默认名称（比如Table1）改成易识别的名称，比如源表。
• 回到B表，预先向下填充足够多的优化后公式（比如预计后续最多新增50行，就多拖50行的公式）：A表任意位置插行/删行，超级表的范围会自动扩展/收缩，B表已经填好的公式会自动取到对应行的新数据，不需要手动调整。

如果你使用的Office版本支持动态数组功能，不需要预先拖几十上百行公式，直接在B表第一个要同步的单元格（比如B3）输入溢出公式：
=IF(源表[对应列标题]="","",源表[对应列标题])
举个例子，你要同步A表的“姓名”列，超级表“源表”下的列标题是“姓名”，公式就写：
=IF(源表[姓名]="","",源表[姓名])
按回车后，公式会自动溢出所有有数据的行，A表插行/删行时，B表的同步范围会自动更新，完全不需要手动调整。

如果零代码方案需要预先拖公式，不符合你全自动的需求，可以用工作表事件实现插行/删行时自动同步B表的行结构和公式：

• 按Alt+F11打开VBA编辑器，在左侧工程窗口双击你的A表（WorksheetA），打开代码编辑窗口。
• 粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ' 同步B表和A表的行数
    If Me.UsedRange.Rows.Count <> Sheets("WorksheetB").UsedRange.Rows.Count Then
        If Me.UsedRange.Rows.Count > Sheets("WorksheetB").UsedRange.Rows.Count Then
            ' A表行数更多，在B表末尾插入对应数量的行
            Sheets("WorksheetB").Rows(Sheets("WorksheetB").UsedRange.Rows.Count + 1 & ":" & Me.UsedRange.Rows.Count).Insert Shift:=xlDown
        Else
            ' B表行数更多，删除多余行
            Sheets("WorksheetB").Rows(Me.UsedRange.Rows.Count + 1 & ":" & Sheets("WorksheetB").UsedRange.Rows.Count).Delete
        End If
    End If
    ' 批量填充同步公式，做空值判断避免0值
    For i = 3 To Me.UsedRange.Rows.Count
        Sheets("WorksheetB").Cells(i, Target.Column).Formula = "=IF('WorksheetA'!" & Cells(i, Target.Column).Address(False, False) & "="""","""",'WorksheetA'!" & Cells(i, Target.Column).Address(False, False) & ")"
    Next i
    Application.EnableEvents = True
End Sub

• 保存文件时，选择「Excel 启用宏的工作簿(*.xlsm)」格式，后续打开文件时需要启用宏才能生效。
• 该方案下，A表修改内容、插行、删行，B表都会自动同步行位置和数据，不会出现不同步、0值的问题。

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