在 Excel 中转置数据的 5 种方法(逐步教程)
转置数据意味着通过将数组的行转换为列或反之来改变数组的方向。在本教程中,我们将与您分享五种不同的方法来切换数组的方向并实现所需的结果。
视频:在 Excel 中转置数据
使用选择性粘贴将列转换为行
步骤 1:复制要转置的区域
选择要切换行和列的单元格区域,然后按“Ctrl” + “C”复制该区域。
步骤 2:选择转置粘贴选项
右键单击目标区域的第一个单元格,然后从右键菜单中点击“转置”图标(在“粘贴选项”下)。
结果
瞧!该区域瞬间被转置了!
(AD) 使用 Kutools 轻松转置表格维度
在 Excel 中将交叉表(二维表)转换为平面列表(一维列表)或反之总是需要耗费大量时间和精力。然而,安装了 Kutools for Excel 后,其“转置表格维度”工具将帮助您快速轻松地完成转换。
“Kutools for Excel” - 包含超过 300 个实用功能,让您的工作更加轻松。立即获取 30 天全功能免费试用!
转置并将数据链接到源
要动态切换某个区域的方向(将列转换为行,反之亦然),并将切换后的结果连接到原始数据集,您可以使用以下任何一种方法:
- “TRANSPOSE 函数”(易于使用但结果不可编辑)
- “INDIRECT、ADDRESS、COLUMN 和 ROW 函数”(公式较大但允许修改结果)
- “选择性粘贴和查找替换”(复杂但易于理解)
- “Power Query”(简单且结果以表格格式呈现)
使用 TRANSPOSE 函数将行更改为列
要使用“TRANSPOSE”函数将行转换为列,反之亦然,请按照以下步骤操作。
步骤 1:选择与原始单元格集相同数量的空白单元格,但方向相反
提示:如果您使用的是 Excel 365 或 Excel 2021,请跳过此步骤。
假设您的原始表格在 A1:C4 区域内,这意味着表格有 4 行和 3 列。因此,转置后的表格将有 3 行和 4 列。这意味着您应该选择 3 行和 4 列的空白单元格。
步骤 2:输入 TRANSPOSE 公式
在编辑栏中输入以下公式,然后按“Ctrl” + “Shift” + “Enter”获取结果。
提示:如果您是 Excel 365 或 Excel 2021 用户,请按“Enter”。
=TRANSPOSE(A1:C4)
- 您应将 A1:C4 更改为实际要转置的源区域。
- 如果原始区域中有任何空白单元格,“TRANSPOSE”函数会将空白单元格转换为 0(零)。为了在转置时消除零结果并保留空白单元格,您需要利用“IF”函数:
-
=TRANSPOSE(IF(A1:C4="","",A1:C4))
结果
行被转换为列,列被转换为行。
使用 INDIRECT、ADDRESS、COLUMN 和 ROW 函数旋转数据
虽然上述公式非常容易理解和使用,但缺点是您无法编辑或删除旋转表中的任何单元格。因此,我将介绍一个使用“INDIRECT”、“ADDRESS”、“COLUMN”和“ROW”函数的公式。假设您的原始表格在 A1:C4 区域内,要执行列到行的转换并将旋转后的数据连接到源数据集,请按照以下步骤操作。
步骤 1:输入公式
在目标区域左上角的单元格(在我们的情况下是 A6)中输入以下公式,然后按“Enter”:
=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
- 您应将 A1 更改为实际要转置的源区域的左上角单元格,并保持美元符号不变。列字母和行号前的美元符号($)表示绝对引用,这会在您将公式移动或复制到其他单元格时保持列字母和行号不变。
- 如果原始区域中有任何空白单元格,公式会将空白单元格转换为 0(零)。为了在转置时消除零结果并保留空白单元格,您需要利用“IF”函数:
-
=IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1))))
步骤 2:将公式复制到右侧和下方的单元格
选择公式单元格,拖动其填充柄(单元格右下角的小绿色方块)向下然后向右扩展到所需的行数和列数。
结果
列和行立即切换。
使用选择性粘贴和查找替换将列转换为行
使用选择性粘贴方法结合查找和替换功能,可以多几个步骤让您在转置数据的同时将源单元格链接到转置后的单元格。
步骤 1:复制要转置的区域
选择要转置的单元格区域,然后按“Ctrl” + “C”复制该区域。
步骤 2:应用粘贴链接选项
右键单击空白单元格,然后点击“选择性粘贴”。
点击“粘贴链接”按钮。
您将获得如下所示的结果:
步骤 3:从粘贴链接结果中查找并替换等号(=)
选择结果区域(A6:C9)并按“Ctrl” + “H”,然后在“查找和替换”对话框中将“=”替换为“@EO”(或选定范围内不存在的任何字符)。
点击“全部替换”,然后关闭对话框。以下是数据的显示效果。
步骤 4:转置替换后的粘贴链接结果
选择区域(A6:C9)并按“Ctrl” + “C”复制它。右键单击空白单元格(这里我选择了 A11),然后从粘贴选项中选择“转置”图标以粘贴转置结果。
步骤 5:恢复等号(=)以将转置结果链接到原始数据
保持转置结果数据 A11:D13 选中状态,然后按“Ctrl” + “H”,将“@EO”替换为“=”(与“步骤 3”相反)。
点击“全部替换”,然后关闭对话框。
结果
数据已转置并链接到原始单元格。
使用 Power Query 转置并将数据链接到源
Power Query 是一个强大的数据自动化工具,可让您轻松在 Excel 中转置数据。您可以按照以下步骤完成任务:
步骤 1:选择要转置的区域并打开 Power Query 编辑器
选择要转置的数据区域。然后,在“数据”选项卡的“获取和转换数据”组中,点击“从表格/区域”。
- 如果所选数据区域不在表格中,将弹出“创建表格”对话框;点击“确定”为其创建表格。
- 如果您使用的是 Excel 2013 或 2010 并且在“数据”选项卡上找不到“从表格/区域”,则需要从“Microsoft Power Query for Excel 页面”下载并安装它。安装后,转到“Power Query”选项卡,点击“Excel 数据”组中的“从表格”。
步骤 2:使用 Power Query 将列转换为行
转到“转换”选项卡。在“使用第一行作为标题”下拉菜单中,选择“将标题作为第一行”。
点击“转置”。
步骤 3:将转置后的数据保存到工作表
在“文件”选项卡上,点击“关闭并加载”以关闭“Power Editor”窗口并在新工作表中加载转置后的数据。
结果
转置后的数据被转换为新创建的工作表中的表格。
(AD) 使用 Kutools 快速转换区域
Kutools for Excel 中的“转换区域”工具可以帮助您轻松转换(转换)垂直列到多列或多行,反之亦然,或将行转换为多行或反之亦然。
“Kutools for Excel” - 包含超过 300 个实用功能,让您的工作更加轻松。立即获取 30 天全功能免费试用!
相关文章
- 如何在 Excel 中快速转置区域并跳过空白单元格?
- 当我们粘贴一个区域作为转置时,空白单元格也会被粘贴。然而,有时我们只想转置区域并忽略空白单元格,如下图所示。有没有办法在 Excel 中快速转置区域并跳过空白单元格?
- 如何每隔 5 行或 n 行从一列转置到多列?
- 假设您在 A 列中有很长的数据,现在您想每隔 5 行从 A 列转置到多列,例如将 A1:A5 转置到 C6:G6,A6:A10 转置到 C7:G7,依此类推,如下图所示。如何在不反复复制和粘贴的情况下处理这个任务?
- 如何转置/转换列和行到单列?
- 当您使用 Excel 工作表时,有时会遇到这个问题:如何将一系列数据转换或转置为单列?(见以下截图:)现在,我将为您介绍三种快速解决此问题的方法。
- 如何转置/转换列和行到单行?
- 如何将多行和多列合并成一个长行?也许,这对您来说很容易,因为您可以逐个复制它们并将它们手动合并成一行。然而,如果有数百行和列,这可能会非常耗时和繁琐。在这里,我将讨论一些快速解决它的技巧。
最佳 Office 办公效率工具
🤖 | Kutools AI 助手:基于智能执行,彻底革新数据分析 |生成代码|创建自定义公式|分析数据并生成图表|调用 Kutools Functions… |
热门功能:查找、选中项的背景色或标记重复项|删除空行|合并列或单元格且不丢失数据|四舍五入(无公式)... | |
高级 LOOKUP:多条件查找 (VLookup)|多值查找 (VLookup)|多表查找 (VLookup Across Multiple Sheets)|模糊查找 (Fuzzy Lookup)... | |
高级下拉列表:快速创建下拉列表|依赖型下拉列表|多选下拉列表... | |
列管理器:添加指定数量的列 |移动列 |切换隐藏列的可见状态| 比较区域及列... | |
特色功能:网格聚焦|设计视图|增强编辑栏|工作簿 & 工作表管理器|资源库(自动文本)|日期提取|合并数据|加密/解密单元格|按列表发送电子邮件|超级筛选|特殊筛选(筛选粗体/倾斜/删除线等)... | |
热门15 大工具集:12 款文本工具(添加文本、删除特定字符等)|50+ 种图表 类型(甘特图等)|40+ 实用公式(基于生日计算年龄等)|19 款插入工具(插入二维码、按路径插入图片等)|12 种转换工具(小写金额转大写、汇率转换等)|7 款合并与分割工具(高级合并行、分割单元格等)|...更多精彩等你发现 |
用 Kutools for Excel 加速你的 Excel 技能,体验前所未有的高效办公。 Kutools for Excel 提供300 多项高级功能,助您提升效率,节省大量时间。点击此处,获取你最需要的功能...
Office Tab 为 Office 带来标签式界面,让你的工作更加轻松
- 在 Word、Excel、PowerPoint 启用标签式编辑和阅读
- 在同一窗口的新标签中打开和创建多个文档,无需新建窗口。
- 办公效率提升50%,每天帮你减少上百次鼠标点击!