O365 Excel中按ID跨表查找数据并聚合至单个单元格的方法

问题根源 #

VLOOKUP仅能返回第一个匹配值，无法处理子表中同一RefId对应多条Component的场景，因此需要改用支持多值聚合的函数组合。

解决方案 #

基于O365的动态数组功能，使用TEXTJOIN+FILTER组合实现多表匹配聚合，同时兼容主表Components列已有内容的追加需求。

1. 逐个引用子表（适合子表数量少的场景）

假设主表SheetA中：

• Id列在A列
• Components列在B列

子表SheetB/SheetC中：

• RefId列在A列
• Component列在C列

在SheetA的B2单元格（对应A2的Id）输入以下公式，下拉填充即可：

=TEXTJOIN(",", TRUE, B2, 
  IFERROR(TEXTJOIN(",", TRUE, FILTER(SheetB!$C:$C, SheetB!$A:$A=A2)), ""),
  IFERROR(TEXTJOIN(",", TRUE, FILTER(SheetC!$C:$C, SheetC!$A:$A=A2)), "")
)

• TEXTJOIN(",", TRUE, ...)：用逗号拼接非空值
• FILTER(SheetX!$C:$C, SheetX!$A:$A=A2)：筛选子表中RefId等于当前主表Id的所有Component
• IFERROR(..., "")：处理无匹配结果的情况，避免返回错误值
• 第一个参数B2：保留主表原有Components内容，实现追加效果

2. 批量指定工作表列表（适合子表数量多的场景）

使用LET+LAMBDA简化多表引用，无需逐个写子表名称：

=LET(
  sheets, {"SheetB", "SheetC"},  // 替换为你的子表名称列表
  getComp, LAMBDA(sheet, 
    IFERROR(TEXTJOIN(",", TRUE, FILTER(INDIRECT(sheet&"!$C:$C"), INDIRECT(sheet&"!$A:$A")=A2)), "")
  ),
  combined, TEXTJOIN(",", TRUE, INDEX(getComp(sheets), )),
  TEXTJOIN(",", TRUE, B2, combined)
)

• sheets：定义需要聚合的子表名称数组
• getComp：自定义函数，批量处理每个子表的匹配与聚合
• INDIRECT(sheet&"!$C:$C")：动态引用指定工作表的列范围

示例验证 #

• 主表A1行Components初始为xxxx：公式会追加SheetB匹配的B1Comp，最终结果为xxxx,B1Comp
• 主表A2行无初始内容：聚合SheetB的B2Comp和SheetC的C1Comp、C2Comp，最终结果为B2Comp,C1Comp,C2Comp
• 主表A3行无初始内容：仅取SheetB匹配的B3Comp，结果为B3Comp

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