如何在Google Sheets中将带后缀计数的ID列按前缀分组转横向行？

需求说明 #

我有一列带计数后缀的ID数据，需要按ID前缀分组、排序后转换为横向行，列数与对应后缀数量匹配。尝试过数据透视表、Hlookup、筛选数组等方法未解决，优先采用精确ID匹配方案，也可接受基于固定字符数（8位）的方案。

现有数据 #

期望效果 #

解决方案 #

1. 精确ID匹配方案（推荐） #

利用Google Sheets的GROUPBY、LET等函数实现动态分组与转置，无需依赖固定字符长度：

=LET(
  原始ID列, A2:A15,
  提取前缀, LEFT(原始ID列, FIND("-", 原始ID列)-1),
  分组排序, GROUPBY(提取前缀, 原始ID列, LAMBDA(x, SORT(x)), 1),
  BYROW(分组排序, LAMBDA(行数据, TEXTSPLIT(TEXTJOIN("|", TRUE, 行数据), "|")))
)

说明：

• LET：定义变量简化公式结构，原始ID列替换为你的实际数据范围
• FIND("-", 原始ID列)-1：精确提取-符号前的前缀部分
• GROUPBY：按前缀分组，LAMBDA(x, SORT(x))确保同组ID按后缀数字排序
• TEXTJOIN+TEXTSPLIT：将分组后的ID列表转为横向列

2. 固定字符数方案（兼容旧版） #

若前缀长度固定为8位，可直接用LEFT提取前缀，公式更简洁：

=LET(
  原始ID列, A2:A15,
  提取前缀, LEFT(原始ID列, 8),
  分组排序, GROUPBY(提取前缀, 原始ID列, LAMBDA(x, SORT(x)), 1),
  BYROW(分组排序, LAMBDA(行数据, TEXTSPLIT(TEXTJOIN("|", TRUE, 行数据), "|")))
)

3. 兼容无GROUPBY函数的旧版方案 #

如果你的Google Sheets版本不支持GROUPBY，可分两步实现：

1. 提取唯一前缀：在空白列输入公式获取所有唯一前缀

   =UNIQUE(LEFT(A2:A15, FIND("-", A2:A15)-1))
   

2. 横向转置每组ID：在对应前缀右侧输入公式，下拉填充

   =TRANSPOSE(FILTER(A2:A15, LEFT(A2:A15, FIND("-", A2:A15)-1)=B2))
   

   （注：B2为唯一前缀所在单元格，根据实际位置调整）

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