Excel 怎么把多列表头逆透视成一列
按月份横向摊开的宽表没法直接做透视和图表,逆透视其他列能一步压成属性加数值两列。
按月份、门店横向摊开的宽表看着清楚,做数据透视、画图或者导入系统时却很别扭。Power Query 里的「逆透视其他列」能把多列标题一步压成一列属性加一列数值,而且整套步骤可以反复刷新复用。
入口在哪
- 数据选项卡 > 来自表格/区域,进入 Power Query 编辑器。
- 编辑器里右键列标题,菜单中有「逆透视列」和「逆透视其他列」两项。
- 版本较旧的 Excel 可能没有这个按钮,WPS 表格的功能位置也不一样,在菜单里搜「逆透视」最快。
具体步骤
- 选中数据区域任一单元格,数据 > 来自表格/区域,弹窗里勾选「表包含标题」。
- 在编辑器里按住 Ctrl 点选要保留的维度列,比如门店、产品、负责人。
- 右键这些列 > 逆透视其他列,剩下所有列都会折叠成「属性」和「值」两列。
- 双击列标题改名,属性改成月份、值改成销量,后面看数据不容易混。
- 改类型:点值列标题左侧的图标,把文本改成小数或整数,日期列同理处理。
- 主页 > 关闭并上载,结果落到新工作表,原表保持不动。
几个坑
- 逆透视出来的值列默认是文本类型,直接求和会得到 0 或者报错,必须手动改类型,这是最常见的翻车点。
- 原表里的合计行、小计行逆透视后会多出「合计」这个属性值,先在上一步筛掉,否则统计结果会翻倍。
- 源区域如果是固定的 A1:F100,新增的月份列不会被带进来;把源改成超级表或者整列引用才能自动扩展。
- 动手前先另存一份副本。查询步骤虽然能逐步删掉回退,但一旦上载把原表覆盖了就很麻烦。
怎么验证
先核对行数:结果行数应该等于原表行数乘以被逆透视的列数。再随手挑一个格子,回到原表找到同一月份、同一门店的数值比对,两边对得上才算通过。最后在结果表上拉一个简单的透视或折线图,能正常出数说明类型改对了。这份查询以后要交给别人用,就在编辑器里把步骤名改成一句看得懂的话,别留着默认的查询 1。
常见问题
逆透视和转置有什么区别
转置是行列整体对调,表还是宽表,只是横竖换了方向;逆透视是把多列折叠成两列,行数会成倍增加,得到的是标准一维表。做透视和图表要用后者。
源表新增了月份列,结果会自动更新吗
点数据 > 全部刷新即可,前提是源区域覆盖了新列。如果源是固定区域,先把区域改成包含新列的超级表,或者在编辑器里直接改源那一步的范围。
没有 Power Query 的版本怎么办
可以用多重合并计算区域的数据透视表向导,或者复制粘贴后手工整理。各家功能命名不同,核心动作都是把多列折叠成两列。
一句话总结:逆透视就是宽表变长表,顺序是先选维度列、再逆透视其他列、最后改值列类型。