如何在Google Sheets中用公式匹配项目与对应公司并生成目标表格？

核心思路 #

先构建所有公司-项目的对应关系对，再过滤空值并去重，最终生成包含Company和Project两列的结果。

完整公式 #

直接在目标表格的起始单元格（比如A1）输入以下公式，会自动生成两列结果：

=QUERY(
  FLATTEN(
    BYCOL(B2:Q30, LAMBDA(col, 
      HSTACK(
        INDEX(col, 1),
        OFFSET(col, 1, 0, ROWS(col) - 1)
      )
    ))
  ),
  "SELECT Col1, Col2 WHERE Col2 <> '' AND Col1 <> '' GROUP BY Col1, Col2",
  0
)

公式拆解 #

1. BYCOL(B2:Q30, LAMBDA(col, ...))：遍历B到Q的每一列，对每一列执行后续逻辑
2. HSTACK(INDEX(col,1), OFFSET(col,1,0,ROWS(col)-1))：
   • INDEX(col,1)：提取当前列的表头（即公司名称，如Company1、Company2）
   • OFFSET(col,1,0,ROWS(col)-1)：提取当前列表头下方的所有项目行
   • 横向拼接公司名称和对应项目，形成[公司, 项目]的行对
3. FLATTEN(...)：将所有列的[公司, 项目]行对合并为一个二维数组
4. QUERY(...)：
   • 过滤掉空公司名或空项目的行
   • 通过GROUP BY去重，确保每个公司-项目组合只出现一次
   • 输出整理后的两列数据

备选方案（分步实现） #

如果你想保留自己的项目提取公式，可分两步操作：

1. 提取唯一项目：在目标表格的Project列（比如B1开始）输入你的公式：

   =FILTER(UNIQUE(FLATTEN(B3:Q30)), UNIQUE(FLATTEN(B3:Q30))<>"")
   

2. 匹配对应公司：在Company列（A1开始）输入公式，下拉填充：

   =INDEX(B2:Q2, MATCH(B1, B3:Q30, 0))
   

   注意：如果同一个项目出现在多个公司列中，该公式会返回第一个匹配到的公司名称。

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