Google Sheets合并重复唯一值：按ID聚合列数据

需求概述 #

针对表格中的每个唯一ID，对每列执行以下操作：提取该ID对应行中的非空唯一值，用~符号连接；若仅存在一个唯一非空值，则直接显示该值。

示例数据 #

输入表格 #

预期输出 #

注：原预期输出中ID=B的Occurence显示为1~1应为笔误，按输入数据逻辑正确结果应为1~2

解决方案：数组公式实现 #

假设原始数据位于A1:H7（包含表头），在空白单元格（如J1）输入以下数组公式，可一次性生成完整的合并结果：

=LET(
  unique_ids, UNIQUE(A2:A7),
  headers, A1:H1,
  results, BYROW(unique_ids, LAMBDA(id,
    HSTACK(
      id,
      BYCOL(B1:H1, LAMBDA(col,
        LET(
          col_vals, FILTER(INDIRECT(ADDRESS(2,COLUMN(col))&":"&ADDRESS(7,COLUMN(col))), A2:A7=id),
          unique_non_empty, FILTER(UNIQUE(col_vals), UNIQUE(col_vals)<>""),
          IFERROR(TEXTJOIN("~", TRUE, unique_non_empty), "")
        )
      ))
    )
  )),
  VSTACK(headers, results)
)

公式拆解 #

1. LET函数：定义中间变量，简化公式结构，提升可读性
2. unique_ids：提取A列中所有唯一的ID值
3. BYROW遍历ID：针对每个唯一ID，处理其对应的所有数据行
4. BYCOL遍历列：对每个数据列（除ID列），执行以下操作：
   • FILTER：筛选出当前ID对应的该列所有值
   • 二次FILTER+UNIQUE：去除空值和重复值
   • TEXTJOIN：用~连接所有非空唯一值，无有效值则返回空字符串
5. VSTACK：将表头和结果行组合成完整表格

手动验证逻辑（以ID=A为例） #

• Occurence列：筛选值为{1,2,3}，去重后直接连接为1~2~3
• Field3列：筛选值为{b,c,c}，去重后为{b,c}，连接为b~c
• Name列：所有值均为john，去重后直接显示john

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