如何在Excel中动态生成依赖项概览表并自动匹配关联数据

核心需求实现思路 #

基于Sheet1中动态增长的Table1，在Sheet2生成自动关联依赖项信息的动态表格Table2，无需手动维护公式，随Table1的行增减自动更新。

完整公式方案（支持Excel 365/2021及以上版本） #

在Sheet2的空白单元格（例如A2）输入以下动态数组公式，Excel会自动溢出所有结果：

=LET(
    有依赖行, FILTER(Table1, Table1[Dependency Id]<>"", "无依赖项"),
    当前ID, INDEX(有依赖行,,1),
    当前描述, INDEX(有依赖行,,2),
    依赖ID, INDEX(有依赖行,,3),
    当前完成率, INDEX(有依赖行,,4),
    依赖描述, XLOOKUP(依赖ID, Table1[Id], Table1[Description], "未找到依赖项"),
    依赖完成率, XLOOKUP(依赖ID, Table1[Id], Table1[% of completion], "未找到依赖项"),
    HSTACK(当前ID, 当前描述, 依赖ID, 当前完成率, 依赖描述, 依赖完成率)
)

公式各部分说明 #

• LET：定义变量简化公式结构，提升计算效率与可读性
• 有依赖行：筛选出Table1中所有填写了Dependency Id的行，无符合条件行时返回提示文本
• 当前ID/当前描述/依赖ID/当前完成率：从筛选结果中提取当前项的对应字段
• 依赖描述/依赖完成率：通过XLOOKUP根据依赖ID匹配Table1中对应项的描述与完成率，匹配失败时返回提示
• HSTACK：将所有字段横向拼接，生成最终的关联概览数据

旧版Excel兼容方案（无XLOOKUP支持） #

替换XLOOKUP为INDEX+MATCH组合，公式如下：

=LET(
    有依赖行, FILTER(Table1, Table1[Dependency Id]<>"", "无依赖项"),
    当前ID, INDEX(有依赖行,,1),
    当前描述, INDEX(有依赖行,,2),
    依赖ID, INDEX(有依赖行,,3),
    当前完成率, INDEX(有依赖行,,4),
    依赖描述, IFERROR(INDEX(Table1[Description], MATCH(依赖ID, Table1[Id], 0)), "未找到依赖项"),
    依赖完成率, IFERROR(INDEX(Table1[% of completion], MATCH(依赖ID, Table1[Id], 0)), "未找到依赖项"),
    HSTACK(当前ID, 当前描述, 依赖ID, 当前完成率, 依赖描述, 依赖完成率)
)

动态表格设置 #

公式溢出结果后，选中所有溢出区域，按Ctrl+T创建表格，勾选「我的表有标题」，手动设置表头（例如：当前项ID、当前项描述、依赖项ID、当前项完成率、依赖项描述、依赖项完成率）即可。

关键特性 #

• 自动更新：Table1新增/删除行、修改依赖ID时，Table2会自动同步刷新
• 唯一性提示：若Table1的Id列存在重复值，公式会返回第一个匹配结果；需处理重复ID可调整匹配逻辑

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