如何用VBA/Power Query在Excel中实现表交叉连接并自动追加数据？

我有两个Excel工作表：

1. 项目表：持续新增项目，以Project ID作为唯一标识
2. 任务表：包含所有项目都需执行的约110项固定任务

需要实现：当项目表新增项目后，自动将所有未处理过的新项目与任务表的每一条任务配对，生成一行记录追加到主表末尾。支持定时刷新或按钮/触发器触发自动化执行。

表格示例 #

项目表

任务表

目标主表（最终效果）

已尝试方案及问题 #

• SQL：逻辑简单，但依赖Power Automate，而文件存储在SharePoint，所需许可证不符合现有权限，无法使用
• Power Query：因两张表无公共列，未找到正确生成扁平表的方法
• Power Automate：同样遇到SharePoint相关许可证问题
• Access：尚未实操

方法1：Excel VBA脚本（支持按钮/新增触发） #

无需额外工具，直接在Excel中编写脚本，可手动按钮触发，或设置工作表变更触发器自动执行。

操作步骤： #

1. 打开Excel文件，按Alt+F11打开VBA编辑器
2. 插入新模块，粘贴以下代码（根据实际表名修改工作表名称）：

Sub AppendNewProjectsToMaster()
    Dim wsProject As Worksheet, wsTask As Worksheet, wsMaster As Worksheet
    Dim lastRowProj As Long, lastRowTask As Long, lastRowMaster As Long
    Dim projID As String, i As Long, j As Long
    Dim isNew As Boolean
    
    ' 定义工作表，替换为你的实际表名
    Set wsProject = ThisWorkbook.Worksheets("项目表")
    Set wsTask = ThisWorkbook.Worksheets("任务表")
    Set wsMaster = ThisWorkbook.Worksheets("主表")
    
    ' 获取各表最后一行数据的行号
    lastRowProj = wsProject.Cells(wsProject.Rows.Count, "A").End(xlUp).Row
    lastRowTask = wsTask.Cells(wsTask.Rows.Count, "A").End(xlUp).Row
    lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历项目表所有项目（跳过表头行）
    For i = 2 To lastRowProj
        projID = wsProject.Cells(i, "A").Value
        isNew = True
        
        ' 检查主表是否已存在该项目的记录
        For j = 2 To lastRowMaster
            If wsMaster.Cells(j, "A").Value = projID Then
                isNew = False
                Exit For
            End If
        Next j
        
        ' 若为新项目，与所有任务配对后追加到主表
        If isNew Then
            For j = 2 To lastRowTask
                lastRowMaster = lastRowMaster + 1
                wsMaster.Cells(lastRowMaster, "A").Value = projID
                wsMaster.Cells(lastRowMaster, "B").Value = wsTask.Cells(j, "A").Value
                wsMaster.Cells(lastRowMaster, "C").Value = wsTask.Cells(j, "B").Value
            Next j
        End If
    Next i
    
    MsgBox "新项目已成功追加到主表！", vbInformation
End Sub

3. 返回Excel界面，在「开发工具」选项卡插入按钮，关联上述宏，点击即可执行
4. （可选）设置自动触发：在项目表的工作表模块中添加以下代码，当A列（Project ID列）新增内容时自动执行：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅当Project ID列有变更时触发
    If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
        AppendNewProjectsToMaster
    End If
End Sub

方法2：Power Query交叉连接（无需额外许可证） #

Power Query支持交叉连接（笛卡尔积），可实现项目与任务的全配对，再筛选未处理的新项目，解决无公共列的问题：

操作步骤： #

1. 分别将项目表、任务表、主表导入Power Query：点击「数据」选项卡 → 「自表格/区域」，关闭加载到，保留三个查询
2. 新建空白查询，打开「高级编辑器」，替换为以下M代码（修改查询名称为你的实际表名）：

let
    // 获取项目表数据
    项目表 = Excel.CurrentWorkbook(){[Name="项目表"]}[Content],
    // 获取任务表数据
    任务表 = Excel.CurrentWorkbook(){[Name="任务表"]}[Content],
    // 获取主表已存在的Project ID，避免重复处理
    主表已存在ID = Excel.CurrentWorkbook(){[Name="主表"]}[Content][Project ID],
    // 筛选项目表中未在主表出现的新项目
    新项目 = Table.SelectRows(项目表, each not List.Contains(主表已存在ID, [Project ID])),
    // 交叉连接新项目和任务表
    交叉连接 = Table.AddColumn(新项目, "任务信息", each 任务表),
    展开任务信息 = Table.ExpandTableColumn(交叉连接, "任务信息", {"Checkpoint", "Task"}, {"Checkpoint", "Task"}),
    // 保留需要的列
    整理列 = Table.SelectColumns(展开任务信息, {"Project ID", "Checkpoint", "Task"})
in
    整理列

3. 将查询结果追加到主表：点击「加载到」→ 「仅创建连接」，右键该连接 → 「追加到主表」
4. 设置定时刷新：点击「数据」选项卡 → 「全部刷新」→ 「连接属性」，设置刷新频率即可

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