Excel 怎么把表格的行和列互换
从系统导出的明细一行一条记录、字段全堆在列上,横过来才好用。转置有三种做法,选错会让结果不再随原表变动。
把一张表横过来,常见于两种场景:从系统导出的明细是一行一条记录、字段全堆在列上,需要改成一行一个字段;或者别人给的横向排班表要改成纵向。手工重录最费时间,表格软件里有三种现成做法,选哪种取决于你做完之后还要不要跟着原表更新。
入口在哪
三个入口都在常用位置,不用装插件。
- 选择性粘贴:选中区域复制,在开始选项卡的粘贴下拉里找选择性粘贴,勾选转置。一次性操作,最快。
- TRANSPOSE 函数:在空白区域输入公式引用原区域,输出的行列方向和原区域相反。适合希望结果随原表变动的情况。
- Power Query:在数据选项卡里把表格导进编辑器,用转换里的转置。适合几百列、需要反复做的大表。
三个入口在不同版本里的名称略有差别,找不到就在功能区顶部的搜索框里输转置两个字。
具体步骤
- 先算尺寸。原区域是 3 行 8 列,转置后就是 8 行 3 列,落点周围必须有这么多空单元格,否则会提示无法粘贴,或者直接盖掉旁边的数据。
- 选择性粘贴法:选中原区域复制,点目标区左上角单元格,右键选择性粘贴,勾选转置,确定。拿到的是不带公式的静态值。
- 公式法:选中和结果尺寸一致的区域,输入 TRANSPOSE 并引用原区域;较新的 Microsoft 365 版本回车后自动溢出,老版本要按 Ctrl 加 Shift 加 Enter 作为数组公式录入。
- Power Query 法:选中原表任一单元格导入表格,进入编辑器点转换里的转置,再把第一行用作标题,最后关闭并上载。
- 粘完补格式:行高列宽不会跟着走,日期和数字可能变成一串序号,需要重新设一次单元格格式。
几个坑
- 转置不带走公式的相对关系。原来引用左边单元格的公式,转置后仍指向原位,算出来的值往往是错的。要保留计算逻辑,就转置后重写,或者用公式法让引用跟着走。
- 原区域有合并单元格时会提示区域大小不一致。先取消合并、把内容补全,再转置。
- 选择性粘贴只搬值,原表改了结果不变。做报表时容易拿着旧数据汇报,这一点要记住。
- 数组公式的输出区域不能只改其中一格,删除也要整块选中再删,否则会提示不能更改数组的某一部分。
- 列数很多时,粘到新工作表更安全。原表右侧或下方藏着的辅助列、批注不会被提示,粘过去可能刚好被盖住。
怎么验证
挑三个已知单元格对一下:原表第一行第二列的值,转置后应落在第一列第二行。再核对总行数和总列数是否互换,最后把两边的数值分别求和,总数应当相等。用公式法的还要改一个原表数据,看结果是否同步变化;不变说明拿到的是值而不是公式。
常见问题
转置后数字变成日期或者一串井号
列宽不够就显示井号,拉宽即可。数字被识别成日期,是因为原表存的是日期序列值,粘贴后格式丢了,重新设成数值或常规格式就能恢复。
能不能让结果自动跟着原表更新
用 TRANSPOSE 函数可以,原表一变结果就变,缺点是原区域尺寸改变时要重新选一遍。原表会不断加行加列,更适合用 Power Query,刷新一次就同步。
转置和行列互换是一回事吗
是一回事,只是叫法不同。表格软件里这个功能通常叫转置,指把第 m 行第 n 列的值放到第 n 行第 m 列,方阵转置两次会回到原样。
一句话总结:一次性的表用选择性粘贴加转置,要跟着原表更新就用 TRANSPOSE,反复处理的大表交给 Power Query,动手前先确认落点周围是空的。