Excel Tables自动填充需求：主管专属审批表与主表双向同步

方法1：VBA宏实现实时双向同步 #

这是适配大量条目场景的高灵活性方案，能实现实时同步。

前置准备 #

在主表新增唯一ID列（用=ROW()-ROW(主表[#Headers])生成自增ID），所有专属表保留该列作为条目匹配的唯一标识。

主表同步到专属表 #

在主表的工作表代码中添加Worksheet_Change事件，主表新增/修改条目时自动同步到对应主管的专属表：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim mainTable As ListObject
    Dim targetTable As ListObject
    Dim ws As Worksheet
    Dim supervisorName As String
    Dim idValue As String
    Dim targetRow As ListRow
    
    Set mainTable = Me.ListObjects("主表")
    If Intersect(Target, mainTable.DataBodyRange) Is Nothing Then Exit Sub
    
    ' 获取当前变更行的主管名称与ID
    supervisorName = mainTable.ListColumns("主管").DataBodyRange(Target.Row - mainTable.HeaderRowRange.Row).Value
    idValue = mainTable.ListColumns("唯一ID").DataBodyRange(Target.Row - mainTable.HeaderRowRange.Row).Value
    
    ' 定位对应主管工作表
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(supervisorName)
    On Error GoTo 0
    If ws Is Nothing Then Exit Sub
    
    Set targetTable = ws.ListObjects(supervisorName & "表")
    If targetTable Is Nothing Then Exit Sub
    
    ' 检查专属表是否已有该条目
    On Error Resume Next
    Set targetRow = targetTable.ListRows(idValue)
    On Error GoTo 0
    
    If targetRow Is Nothing Then
        ' 新增条目
        Set targetRow = targetTable.ListRows.Add
        targetRow.Range.Value = mainTable.ListRows(Target.Row - mainTable.HeaderRowRange.Row).Range.Value
    Else
        ' 更新已有条目
        targetRow.Range.Value = mainTable.ListRows(Target.Row - mainTable.HeaderRowRange.Row).Range.Value
    End If
End Sub

专属表同步回主表 #

为每个主管的专属表添加Worksheet_Change事件，修改后同步回主表：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetTable As ListObject
    Dim mainTable As ListObject
    Dim idValue As String
    Dim mainRow As ListRow
    
    Set targetTable = Me.ListObjects(Me.Name & "表")
    If Intersect(Target, targetTable.DataBodyRange) Is Nothing Then Exit Sub
    
    ' 获取当前变更行的ID
    idValue = targetTable.ListColumns("唯一ID").DataBodyRange(Target.Row - targetTable.HeaderRowRange.Row).Value
    
    Set mainTable = ThisWorkbook.Worksheets("主表").ListObjects("主表")
    ' 定位主表对应条目
    On Error Resume Next
    Set mainRow = mainTable.ListRows(idValue)
    On Error GoTo 0
    
    If Not mainRow Is Nothing Then
        ' 同步变更内容到主表
        mainRow.Range.Value = targetTable.ListRows(Target.Row - targetTable.HeaderRowRange.Row).Range.Value
    End If
End Sub

注意事项 #

• 工作簿需保存为.xlsm格式（启用宏）
• 首次打开需启用宏
• 可隐藏ID列避免误修改

方法2：动态数组函数（Excel 365/2021）+ 辅助宏 #

适合不想写复杂宏的场景，利用动态数组自动同步主表到专属表，再用简单宏处理反向同步。

主表到专属表（自动同步） #

在主管专属表的A1单元格输入主管名称（如John Doe），在数据区域第一行输入动态数组公式：

=FILTER(主表[#All],主表[主管]=A1,"无对应条目")

主表新增/修改时，专属表会实时刷新对应条目。

专属表到主表（宏同步） #

因动态数组生成区域无法直接修改，可添加“同步到主表”按钮，绑定以下宏：

Sub SyncToMainTable()
    Dim targetTable As ListObject
    Dim mainTable As ListObject
    Dim idValue As String
    Dim mainRow As ListRow
    Dim i As Integer
    
    Set targetTable = ActiveSheet.ListObjects(1)
    Set mainTable = ThisWorkbook.Worksheets("主表").ListObjects("主表")
    
    For i = 1 To targetTable.ListRows.Count
        idValue = targetTable.ListColumns("唯一ID").DataBodyRange(i).Value
        On Error Resume Next
        Set mainRow = mainTable.ListRows(idValue)
        On Error GoTo 0
        
        If Not mainRow Is Nothing Then
            mainRow.Range.Value = targetTable.ListRows(i).Range.Value
        End If
    Next i
    MsgBox "同步完成"
End Sub

注意事项 #

• 仅支持Excel 365/2021及以上版本
• 修改专属表前需将动态数组结果复制粘贴为值，再执行同步

方法3：高级筛选 + 定时宏（轻量方案） #

适合对实时同步要求不高的场景，操作简单。

主表到专属表 #

1. 为每个主管表设置高级筛选：
   • 在主管表空白区域设置筛选条件（如主管: John Doe）
   • 点击数据→高级，选择主表为列表区域，条件区域选刚才设置的内容，勾选“将筛选结果复制到其他位置”，复制到位置选主管表表头下方
2. 添加定时刷新宏，比如每10分钟刷新一次：

Sub RefreshAllFilters()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "主表" Then
            ws.Range("A1").CurrentRegion.Clear
            ' 重新执行高级筛选（需根据你的筛选设置调整条件区域路径）
            ThisWorkbook.Worksheets("主表").Range("主表[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=ws.Range("D1:D2"), CopyToRange:=ws.Range("A2"), Unique:=False
        End If
    Next ws
End Sub

用Application.OnTime设置定时执行该宏即可。

专属表到主表 #

依赖唯一ID，绑定方法2中的SyncToMainTable宏实现同步。

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