Excel 怎么把横表转成竖表
表头横着排、一条记录占一行,做透视和图表都别扭。讲清选择性粘贴转置、TRANSPOSE 公式两条路,以及合并单元格为什么会卡住。
横表和竖表的区别不在好看难看,而在能不能被后续处理。很多系统导出的表是字段横着铺、数据竖着排,直接丢进数据透视表会提示字段名无效;反过来手工排成横表好看,后期又没法筛选。转置就是把方向调过来,这一步做对,后面的统计才顺。
入口在哪
按需求分三条路,先想清楚要的是「一次性的结果」还是「跟着原表变的引用」:
- 一次性转换:复制原区域,在目标位置右键,选择性粘贴里勾转置。也可以按快捷键调出选择性粘贴对话框再勾选,不同版本的入口位置略有差别。
- 要跟着原表自动更新:用
TRANSPOSE函数,在公式选项卡的函数库或查找与引用一类里能找到,也可以直接敲函数名。 - 表特别长、还要按月追加:走 Power Query 的逆透视,这类需求另有专门做法,本文只讲前两条。
如果只是想临时看一眼转过来的样子,直接复制粘贴再手动拖也行,但一旦超过十几列,手工挪动出错概率很高。
具体步骤
- 先确认原区域是规整的矩形:没有整行整列的空白穿插,没有大小不一的合并单元格。这一步不做,后面大概率报错。
- 选中原区域复制。注意只选数据本身,不要把外面的说明文字、标题行一起框进去。
- 切到目标工作表,点一个空白区域的左上角单元格,别点在已有数据旁边,转置后的区域横竖互换,很容易压到别的数据上。
- 右键 → 选择性粘贴 → 勾选转置,确定。整块数据的方向就换过来了,原来的第一行变成第一列。
- 公式法:在目标区域左上角输入
=TRANSPOSE(原区域)。支持动态数组的版本直接回车,结果会自动溢出到对应行列;旧版本要先选中与原区域行列数互换的那片区域,再按组合键确认数组公式。 - 转置完检查格式。列宽、行高、边框基本都不跟过来,日期可能显示成一串数字,要重新设置。
- 如果只需要最终数值、不想保留指向原表的引用,再复制一次,选择性粘贴为数值,把公式替换掉。
几个坑
- 目标区域里已经有内容。选择性粘贴会提示是否替换,一旦确认就是覆盖,撤回也只有一步的机会。
- 原表里有大小不一致的合并单元格。转置会直接报错,提示合并单元格大小不同,得先取消合并、补齐内容再转。
- 把公式法当成万能。公式引用的是原区域,原表被删或剪切走,结果就会变成引用错误。
- 转置后忘了含义也跟着换了。原先「一行一个月份」变成「一列一个月份」,做图时行列选错,图就横过来了。
怎么验证
三步就够:一是求和核对,原表整块求和与转置后整块求和必须相等;二是抽查对应关系,原表第一行第三列应在转置结果的第一列第三行;三是把引用错误或空白筛出来看一眼。三步都对,转置就是干净的。
公式法再补一步:改一个原表里的数字,看目标区域是否跟着变。变了说明引用是活的,不变就说明已经粘成数值了。
常见问题
为什么转置后日期全变成一串数字
选择性粘贴默认不搬运格式,单元格格式回落到常规,日期就按序列值显示。选中这些单元格,把格式改回日期即可,数据本身没坏。
为什么提示不能更改合并单元格的一部分
原区域里存在跨行或跨列的合并单元格,且大小不一致,转置时无法一一对应。先取消合并、把空出来的单元格补上值,再重新转置。
转置后改原表,结果没跟着变怎么办
说明当前是粘贴出来的静态值,不是公式。想要联动就用 TRANSPOSE;反过来,看到结果自己会变又不想让它变,就把它粘成数值固定下来。
一句话总结:横竖互换只是方向问题,先确认原区域规整、目标区域空白,转完再核对一次合计,比反复撤回省事。