如何用ARRAYFORMULA和TRANSPOSE转换Google Sheets订单产品表格

嘿，这个需求我之前帮不少做库存管理的朋友解决过，用ARRAYFORMULA配合TRANSPOSE完全能一键搞定，不用手动拆分或拖拽公式。咱们直接上解决方案，再拆解细节：

核心公式（适配你的数据结构） #

假设你的原数据从A1单元格开始，A列是订单编号，从B列起每3列对应一组「sku/name/qty」，直接在空白单元格（比如A20）输入以下公式：

=ARRAYFORMULA(
  LET(
    订单列, A2:A,
    数据范围, B2:ZZ,
    每组列数, 3,
    原数据行数, ROWS(订单列),
    每组数量, COLUMNS(数据范围)/每组列数,
    重复订单号, FLATTEN(REPT(订单列&"|", 每组数量)),
    扁平化数据, FLATTEN(TRANSPOSE(SPLIT(FLATTEN(QUERY(TRANSPOSE(数据范围),,9^9)), "|"))),
    最终订单列, IF(SEQUENCE(原数据行数*每组数量,1,0,1) MOD 每组数量 = 0, LEFT(重复订单号, LEN(重复订单号)-1), ""),
    结果表, HSTACK(最终订单列, INDEX(扁平化数据, SEQUENCE(原数据行数*每组数量, 每组列数))),
    FILTER(结果表, INDEX(结果表,0,2)<>"")
  )
)

公式逻辑拆解 #

我把公式拆成了几个变量，方便你理解和调整：

• 订单列：提取所有订单编号的列（A2:A，跳过表头）
• 数据范围：包含所有sku、name、qty的区域（从B2开始到最后一列ZZ，确保覆盖所有可能的产品组）
• 每组列数：固定为3，因为每个产品对应sku/name/qty三列
• 重复订单号：把每个订单编号重复N次（N是每个订单最多的产品组数），用|做分隔符，后续用来匹配行
• 扁平化数据：通过TRANSPOSE转置每组数据，再合并拆分，把所有sku/name/qty按顺序排成一列，方便后续重组
• 最终订单列：判断当前行是否是某个订单的第一行，是的话显示订单编号，否则留空，实现你要的合并单元格效果
• 结果表：把处理后的订单列和扁平化的sku/name/qty列合并，最后用FILTER去掉空的产品行（比如有些订单没有15个产品，自动过滤掉多余的空行）

调整提示 #

• 如果你的表头不是从A1开始，或者每组列数不是3，直接修改订单列、数据范围、每组列数这三个变量即可
• 公式是动态的，原数据新增订单或产品组时，结果会自动更新，不用手动调整

效果示例 #

原宽格式数据：

转换后结果：

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