如何在Google Sheets中逆透视含多列组的数据？

针对含固定列+多组子项列的表格转换需求，这里提供一个基于Google Sheets原生函数的公式方案，完全规避自定义函数、SPLIT、QUERY的缺陷，兼容IMPORTRANGE和发布场景，且保留子项标签。

核心公式 #

=LET(
  data, A2:H,
  fixedCols, 2,
  groupSize, 3,
  numRows, ROWS(data),
  numGroups, (COLUMNS(data)-fixedCols)/groupSize,
  totalOutputRows, numRows*numGroups,
  MAKEARRAY(totalOutputRows, fixedCols+groupSize,
    LAMBDA(row, col,
      LET(
        originalRow, CEILING(row/numGroups, 1),
        groupNum, MOD(row-1, numGroups)+1,
        IF(col<=fixedCols,
          INDEX(data, originalRow, col),
          INDEX(data, originalRow, fixedCols + (groupNum-1)*groupSize + (col-fixedCols))
        )
      )
    )
  )
)

配置说明 #

只需修改前3个变量即可适配你的数据结构：

• data：原始数据的范围（不含标题行），比如你的数据在A2:J就改成对应值
• fixedCols：固定列的数量，这里是2（对应Receipt date、Amount）
• groupSize：每组子项的列数，这里是3（对应Child、Shirt size、Age一组）

工作原理 #

1. LET定义变量，简化公式结构并方便配置
2. 自动计算子项组数：(总列数-固定列数)/每组列数
3. MAKEARRAY生成堆叠后的数组，行数为原始行数×子项组数，列数为固定列数+每组列数
4. 通过CEILING和MOD映射输出行到原始数据行和子项组号
5. INDEX直接提取原始单元格值，完整保留日期、货币等特殊格式

示例效果 #

原始数据 #

转换后输出 #

进阶优化：过滤空白子项 #

如果原始数据存在未填写的子项组，可在公式外层套FILTER过滤空白行（以Child列不为空为条件）：

=FILTER(
  LET(
    data, A2:H,
    fixedCols, 2,
    groupSize, 3,
    numRows, ROWS(data),
    numGroups, (COLUMNS(data)-fixedCols)/groupSize,
    totalOutputRows, numRows*numGroups,
    MAKEARRAY(totalOutputRows, fixedCols+groupSize,
      LAMBDA(row, col,
        LET(
          originalRow, CEILING(row/numGroups, 1),
          groupNum, MOD(row-1, numGroups)+1,
          IF(col<=fixedCols,
            INDEX(data, originalRow, col),
            INDEX(data, originalRow, fixedCols + (groupNum-1)*groupSize + (col-fixedCols))
          )
        )
      )
    ),
  INDEX(MAKEARRAY(...),,fixedCols+1)<>""
)

注：将MAKEARRAY(...)部分替换为原公式中的对应代码即可。

方案优势 #

• 全原生函数，无自定义函数，完美兼容IMPORTRANGE和发布的表格
• 直接引用原始单元格，完全保留日期、货币、百分比等特殊格式，无数据错乱
• 配置简单，仅需修改3个变量即可适配不同列数结构
• 自动计算子项组数，无需手动指定

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