Excel中如何为需求匹配项目并返回无间隙拼接列表？

问题背景 #

现有两个工作表：

• 工作表1（匹配矩阵）：行是需求，列是项目，匹配项标记为x，结构如下：

  PJ1  PJ2  PJ3  ...
  Req 1     x    x     x
  Req 2     x          x    
  Req 3     x    x 
  ...
  

• 工作表2（需求列表）：包含顺序不同的需求列表，需要在需求旁的列返回对应标x的项目，以无间隙空格分隔的横向形式呈现，示例：

  Req 1: PJ1 PJ2 PJ3
  Req 2: PJ1 PJ3
  Req 3: PJ1 PJ2
  

实现方法 #

方法1：Excel函数法（分版本） #

假设工作表1名为「匹配矩阵」，A列为需求名称，B~N列为项目名称；工作表2名为「需求列表」，A列为待匹配需求，B列为结果输出列。

适用于Excel 365/2021（支持动态数组函数）

在工作表2的B2单元格输入以下公式，下拉填充即可：

=TEXTJOIN(" ", TRUE, FILTER('匹配矩阵'!$B$1:$N$1, '匹配矩阵'!$B2:$N2="x"))

• FILTER函数：筛选出当前需求对应列中标记为x的项目名称
• TEXTJOIN函数：用空格将筛选出的项目名称连接，TRUE参数用于忽略空值

适用于旧版Excel（无动态数组支持）

在工作表2的B2单元格输入以下数组公式，按Ctrl+Shift+Enter确认后下拉填充：

=TRIM(LEFT(SUBSTITUTE(TEXTJOIN(" ", TRUE, IF('匹配矩阵'!$B2:$N2="x", '匹配矩阵'!$B$1:$N$1&" ", "")), REPT(" ", 99), REPT(" ", 99)), 99*COUNTA('匹配矩阵'!$B2:$N2)))

通过嵌套函数实现筛选+连接逻辑，TRIM用于清除首尾多余空格。

方法2：Power Query法（适合大量数据） #

当数据量较大时，用Power Query批量处理更高效：

1. 打开「匹配矩阵」工作表，选中数据区域，点击「数据」→「从表格/区域」，进入Power Query编辑器
2. 选中需求列（A列），点击「转换」→「逆透视列」→「逆透视其他列」，生成「属性」（项目名）和「值」两列
3. 筛选「值」列，仅保留值为x的行，删除「值」列
4. 选中需求列，点击「转换」→「分组依据」：
   • 分组列：需求
   • 新列名：匹配项目
   • 操作：连接
   • 分隔符：空格
   • 列：属性
5. 关闭并上载处理后的数据到新工作表，在「需求列表」中用XLOOKUP匹配结果：

=XLOOKUP(A2, '分组结果'!$A$2:$A$100, '分组结果'!$B$2:$B$100, "无匹配")

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