Power Query 逆透视：把二维交叉表一键拆成一维明细，附公式替代方案

为什么你的表需要“逆透视”

从 ERP、CRM 或业务同事手里拿到的 Excel 报表，十有八九长这样：行是员工、商品或门店，列是 1 月、2 月、3 月……这种行列都在承载信息的表格叫二维交叉表（也叫宽表）。人看着直观，机器却不喜欢——你没法直接对它做数据透视，没法画时间趋势图，也没法用 SUMIFS 按月份汇总。

解决办法就是逆透视（Unpivot）：把列标题“降级”成一列数据，把宽表压成长表。手工复制粘贴当然行，但下次数据更新你还得再来一遍。Power Query 的逆透视只要点几下，而且设置一次，以后刷新即可。

示例数据

把下面这张表放在 A1:D5：

目标是变成四列都装信息的一维明细：

操作步骤

第一步：把区域转为表格

选中 A1:D5，按 Ctrl + T，在弹窗里确认勾选“我的表格包含标题”。转为“表格”是必要前提，否则 Power Query 无法自动感知数据范围的变化。

第二步：进入 Power Query

选中表格任意单元格，点击 “数据” → “来自表格/区域”，Excel 会打开 Power Query 编辑器（新版本叫 Power Query 编辑器窗口，界面基本一致）。

第三步：执行逆透视

在编辑器里选中 “姓名” 列（按住 Ctrl 可多选），然后 右键 → “逆透视其他列”。

结果会新增两列：“属性”（原来的列标题）和 “值”（原来的单元格内容）。到这里，二维表已经变成一维表了。

第四步：改名、改类型、上载

1. 双击列标题，把“属性”改成“月份”，“值”改成“销售额”。
2. 选中“销售额”列，在 “转换” → “数据类型” 里设为“整数”或“小数”。这一步很关键，否则后续计算会按文本处理。
3. 点击 “主页” → “关闭并上载”，结果会写入一个新工作表。

以后原始表新增了 4 月、5 月的数据，只要在结果表上右键“刷新”，新列会被自动卷进来。

“逆透视列”和“逆透视其他列”到底选哪个

这是最容易出错的地方，两者的选择逻辑刚好相反：

实用建议：日常报表优先用“逆透视其他列”。因为一旦下个月新增了“4月”列，选“逆透视列”的查询不会自动包含新列，你还得回去改一次；而“逆透视其他列”会自动把新列纳入。

背后的 M 代码

点几下操作，Power Query 其实生成了这样一段 M 语言代码（在“主页” → “高级编辑器”里能看到）：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    逆透视 = Table.UnpivotOtherColumns(源, {"姓名"}, "月份", "销售额"),
    改类型 = Table.TransformColumnTypes(逆透视, {{"销售额", type number}})
in
    改类型

其中 Table.UnpivotOtherColumns(表, 保留列, 新属性列名, 新值列名) 就是核心函数。理解它之后，你会发现整个流程只有一行在干活。

四个常见的坑

1. 表头有合并单元格或两级表头。 Power Query 只认第一行做标题，遇到“2024年”横跨三个月的双层表头会直接报错。建议先在原表里把合并单元格拆开、补全空白，再进 Power Query。

2. 总计行、小计行混在里面。 逆透视后这些行会变成一条条“总计”记录，污染数据。务必在逆透视之前筛选掉，顺序反了会多出一堆麻烦。

3. 月份排序错乱。 “属性”列是文本，按文本排序会出现 1月、10月、11月、2月 这种顺序。可以在 M 里加一步把月份转成日期，或增加一列辅助数字再排序。

4. 空单元格变成 null。 原始表里没数据的格子，逆透视后会变成 null。如果不需要这些行，右键该列取消勾选 null 即可（注意：这会把“确实为零”和“没填”区分开来，多数场景下这正是你想要的）。

不想装 Power Query？一条公式也能做

如果你的 Excel 是 Microsoft 365 版本，用动态数组函数同样能一步拆完（假设姓名在 A2:A5，月份标题在 B1:D1，数据在 B2:D5）：

=LET(
  姓名, A2:A5,
  月份, B1:D1,
  数据, B2:D5,
  HSTACK(
    TOCOL(IF(SEQUENCE(,COLUMNS(数据)), 姓名)),
    TOCOL(IF(SEQUENCE(ROWS(数据)), 月份)),
    TOCOL(数据)
  )
)

思路是：用 IF 配合 SEQUENCE 把姓名和月份分别“广播”成与数据区同尺寸的矩阵，再用 TOCOL 按行拉直，最后 HSTACK 横向拼接。公式法胜在轻量、随源数据自动重算；缺点是数据量大时计算变慢，且列数变化时需要手动调整引用范围。

小结

• 二维交叉表 → 一维明细，Power Query 的“逆透视其他列”是最省心的选择，一次设置、长期复用。
• 记住三个顺序：先清理（去合并、去总计）→ 再逆透视 → 最后改数据类型。
• 逆透视后的一维表，才是数据透视表、SUMIFS、折线图的理想输入。
• 适用场景：月度/季度销售报表、多门店指标表、问卷“题目×受访者”矩阵、任何“列名本身是数据”的表格。