Google Sheet大表格转置求助：ArrayFormula与Transpose组合失效

针对大数据集的转置需求，推荐以下几个避开ArrayFormula+Transpose局限性、比数据透视表更灵活的高效公式：

方案1：QUERY+FLATTEN+SPLIT组合公式（首选） #

适合将宽表批量转为长表，十万级数据也能保持高效计算：
假设原始数据范围是A1:Z10000，第一行是表头（日期、分类、指标1、指标2...），公式如下：

=QUERY(SPLIT(FLATTEN(A2:A10000&"|"&B2:B10000&"|"&C1:Z1&"|"&C2:Z10000), "|"), "select Col1,Col2,Col3,Col4 where Col4 is not null", 0)

• 逻辑：用FLATTEN把多行多列数据压成单行，SPLIT按分隔符拆分回多列，最后用QUERY过滤空值并整理结构
• 优势：无额外行数限制（不超Google Sheet上限即可），计算速度快，无需手动调整范围

方案2：LAMBDA+MAP递归公式（复杂场景适配） #

如果数据有特殊格式要求，用Lambda函数实现高度定制化转置：

=LET(
  data, A2:Z10000,
  headers, C1:Z1,
  dates, INDEX(data,,1),
  cats, INDEX(data,,2),
  values, INDEX(data,,3):INDEX(data,,COLUMNS(data)),
  result, MAP(dates, cats, LAMBDA(d,c, MAP(headers, INDEX(values,ROW(dates)-ROW(A2)+1,), LAMBDA(h,v, d&"|"&c&"|"&h&"|"&v)))),
  SPLIT(FLATTEN(result), "|")
)

• 逻辑：嵌套MAP遍历每个单元格，拼接统一格式后拆分，支持自定义转置规则
• 优势：适配非标准化数据集，可灵活修改字段拼接逻辑

方案3：SEQUENCE+VLOOKUP内存优化公式（超大数据集） #

若之前用ArrayFormula+Transpose出现内存溢出，用此方案减少计算负载：

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP(
      SEQUENCE(ROWS(A2:A10000)*COLUMNS(C1:Z1)),
      {SEQUENCE(ROWS(A2:A10000)*COLUMNS(C1:Z1)), FLATTEN(A2:A10000), FLATTEN(B2:B10000), FLATTEN(C1:Z1), FLATTEN(C2:Z10000)},
      {2,3,4,5},
      FALSE
    )
  )
)

• 逻辑：用SEQUENCE生成唯一索引关联转置后的数据，避开Transpose的内存瓶颈
• 优势：适合超大规模数据集，降低内存占用

注意事项 #

• 公式中的数据范围需根据实际表格调整，比如A1:Z10000替换为你的数据区域
• 若存在合并单元格，先拆分再转置，避免数据错位
• 优先选择方案1，代码简洁且效率最高

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