Excel里折腾半天的“逆透视”,Power Query点两下就搞定!80%的人还不知道

开篇：一个让90% Excel用户抓狂的场景

你是不是经常遇到这样的情况：

从系统导出的数据，或者同事发来的报表，长得像下面这张表——

看起来挺整齐对吧？但当你想要做数据分析、做图表、做透视表时，你会痛苦地发现：这种“二维交叉表”根本没法直接用！

你想要的是下面这种一维表（也叫“规范数据表”）：

这个把“列标题变成一列数据”的操作，在数据分析里有一个专业名字——逆透视。

一、在Excel里做逆透视，有多痛苦？

如果你是Excel老手，可能会想到这些方法：

方法1：手动复制粘贴 ❌

如果只有3个人、4个季度，手动复制12次还能忍。但如果有100个产品、12个月的数据，那就是1200次复制粘贴……手会断。

方法2：用“数据透视表”的多重合并计算 ✔️但步骤繁琐

• 按 Alt + D + P 调出老式透视表向导

• 选择“多重合并计算数据区域”

• 添加每个区域（注意不能有行重复）

• 生成透视表后，再双击右下角单元格才能得到明细

• 最后还要手动修改列名、删除多余列

至少10步以上，而且一般人根本不知道那个隐藏的快捷键。

方法3：用公式 + 定位条件 ✔️技术流但容易出错

用 OFFSET、INDEX 配合 INT、MOD 函数构建索引，再拖拽公式……对普通用户来说，堪比写代码。

结论：在传统Excel里，逆透视是公认的“入门劝退”操作。

二、Power Query：逆透视只需2步

下面请出今天的主角——Power Query（Excel 2016及以上版本自带，在“数据”选项卡里叫“获取和转换数据”）。

实际案例操作步骤

假设我们有以下原始数据（选中任意单元格）：

Step 1：将数据加载到Power Query

• 选中数据区域（Ctrl + A）

• 点击 数据 选项卡 → 从表格/区域

• 在弹出的“创建表”对话框中确认包含标题行 → 确定

此时会打开Power Query编辑器窗口。

Step 2：执行逆透视

• 选中你不想被逆透视的列（本例中就是“姓名”列）

• 右键 → 逆透视其他列

一秒钟，结果就出来了！

Step 3：修改列名并上载回Excel

• 双击列头，将“属性”改为“季度”，“值”改为“销售额”

• 点击 主页 → 关闭并上载

大功告成！整个过程不到20秒。

三、为什么Power Query这么强？

最关键的是：Power Query会自动记录你的所有步骤。下个月你拿到新的季度数据，直接替换原表，然后右键 → 刷新，最新的逆透视结果就自动生成了。

这就是自动化的魅力。

四、进阶提示（让你更像高手）

1. 逆透视其他列 vs 逆透视列

• 逆透视其他列：选定列不动，其他列都被转换

• 逆透视列：手动勾选要转换的列（适合只转部分列）

2. 如果不想保留“属性”列中的原列名可以在逆透视之前，把一季度~四季度选中，转换 选项卡 → 替换值，将“季度”替换为空，这样属性列里只有“1、2、3、4”更干净。

3. 一次性完成多个报表的合并如果你是文件夹里有12个月的销售表（都是这种二维表），Power Query可以批量读取、批量逆透视、再追加成一个总表。这在Excel里几乎无法想象。

写在最后

逆透视只是Power Query能力的冰山一角。它还能帮你：

• 一键合并几十个工作表

• 智能拆分不规范的数据（比如“张三（销售部）”拆成姓名和部门）

• 自动处理各种格式混乱的报表

• 连接数据库、网页、文件夹……

很多你在Excel里做得想摔鼠标的任务，在Power Query里只是轻轻一点。

明天预告：《合并12个月工资表，同事加班2小时，我点了3下鼠标》——用Power Query搞定多表合并。

如果你觉得这篇文章有用，点个“在看”，转发给那个还在手动复制粘贴的同事吧！

关注我，每天一篇Excel实战技巧，让你工作少加班，早点下班。