Excel中如何将行内多列关联数据转置为纵向列存储格式

针对同主键（零件号）对应横向多列值、需要转为主键重复纵向排列值的需求，按你使用的Excel版本选对应方案即可：

新版Excel（365/2021及以上，支持动态数组） #

直接在目标工作表的起始单元格输入以下公式，自动溢出全部结果，无需手动下拉填充：

=LET(
    源数据, Sheet1!$A$2:$F$1000, // 替换为实际原数据范围，首列为零件号，后续列为横向存储的对应值
    零件号列, CHOOSECOLS(源数据, 1),
    数值列范围, DROP(源数据, , 1),
    单零件对应值数量, COLUMNS(数值列范围),
    HSTACK(
        TOCOL(IF(SEQUENCE(, 单零件对应值数量), 零件号列)),
        TOCOL(数值列范围)
    )
)

如果需要跳过原数据里的空值，给两个TOCOL函数加第二参数1即可，写法为TOCOL(数据范围,1)。

全版本兼容公式（适配旧版Excel，匹配你现有INDEX+SMALL的公式逻辑） #

你之前写的列转行公式用COLUMN()做横向递推计数，行转列只需要把递推逻辑改成纵向用ROW()计数即可，假设每个零件号固定对应5条横向数据、原数据从Sheet1第2行开始（第1行为表头）：

• 目标表A列（重复零件号列）A2单元格输入公式，向下拖动填充：
  =INDEX(Sheet1!$A:$A, INT((ROW(A1)-1)/5) + 2)
• 目标表B列（纵向对应值列）B2单元格输入公式，向下拖动填充：
  =INDEX(Sheet1!$B:$F, INT((ROW(A1)-1)/5) + 2, MOD(ROW(A1)-1,5) + 1)
  公式里的5对应单零件号关联的横向数据条数，+2对应原数据起始行号，$B:$F对应原数据横向存储值的列范围，可根据实际表格结构调整。

无公式批量处理方案（适合万行以上大数据量） #

用Power Query操作一次即可永久同步，不需要写公式：

• 选中原数据区域，点击「数据」选项卡→「从表格/区域」，将数据加载到Power Query编辑器
• 选中零件号所在列，点击「转换」选项卡→「逆透视列」→「逆透视其他列」
• 删除自动生成的「属性」列，将值列修改为需要的列名
• 点击「关闭并上载」即可直接生成目标格式表，后续原数据更新后，右键结果表选择「刷新」就能自动同步最新结果。

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