在 Excel 中转置数据的 5 种方法(分步教程)
转置数据意味着通过将行转换为列来改变数组的方向,反之亦然。 在本教程中,我们将与您分享五种不同的方法来切换数组的方向并获得所需的结果。
视频:在 Excel 中转置数据
使用选择性粘贴将列切换为行
第 1 步:复制要转置的范围
选择要切换行和列的单元格范围,然后按 按Ctrl + C 复制范围。
第 2 步:选择粘贴转置选项
右键单击目标区域的第一个单元格,然后单击 颠倒 图标 (下 粘贴选项)。
结果
瞧! 范围立即转移!
(AD)使用 Kutools 轻松转置表格尺寸
在 Excel 中将交叉表(二维表)转换为平面列表(一维列表)或反之亦然总是需要花费大量时间和精力。但是,安装了 Kutools for Excel 后, 转置台尺寸 工具将帮助您快速轻松地进行转换。
Kutools for Excel - 包括 300 多个方便的功能,使您的工作更加轻松。 立即获得 30 天的全功能免费试用!
转置数据并将其链接到源
要动态切换范围的方向(将列切换为行,反之亦然)并将切换后的结果连接到原始数据集,您可以使用以下任何一种方法:
- 转换功能 (易于使用,但结果不可编辑)
- INDIRECT、ADDRESS、COLUMN 和 ROW 函数 (大公式但允许更改结果)
- 选择性粘贴和查找替换 (复杂但容易理解)
- Power Query (很容易将结果格式化为表格)
使用 TRANSPOSE 函数将行更改为列
将行转换为列,反之亦然 移调 功能,请执行以下操作。
步骤 1:选择与原始单元格组相同数量但方向相反的空白单元格
提示: 如果您使用的是 Excel 365 或 Excel 2021,请跳过此步骤。
假设您的原始表格在范围内 A1:C4,这意味着表格有 4 行和 3 列。 因此,转置后的表格将有 3 行和 4 列。 这意味着您应该选择 3 行和 4 列的空白单元格。
第 2 步:输入 TRANSPOSE 公式
在公式栏中输入下面的公式,然后按 Ctrl + Shift + Enter 得到结果。
提示: 媒体 输入 如果您是 Excel 365 或 Excel 2021 用户。
=TRANSPOSE(A1:C4)
结果
行变成列,列又变成行。
使用 INDIRECT、ADDRESS、COLUMN 和 ROW 函数旋转数据
虽然上面的公式很容易理解和使用,但它的缺点是你不能编辑或删除旋转表格中的任何单元格。 所以,我将介绍一个使用的公式 间接, 地址, COLUMN 和 行 功能。 假设您的原始表格在范围内 A1:C4, 要执行列到行的转换并保持旋转数据与源数据集的连接,请按照以下步骤操作。
第一步:输入公式
在目标区域最左上角的单元格中输入以下公式(A6 在我们的例子中),然后按 输入:
=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 步:应用粘贴链接选项
-
右键单击空白单元格,然后单击 选择性粘贴.
-
点击 粘贴链接.
您将得到如下所示的结果:
-
选择结果范围(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 编者
选择要转置的数据范围。 然后,在 时间 标签,在 获取和转换数据 组,点击 从表/范围.
- 如果选定的数据范围不在表中,则 创建表 将弹出对话框; 点击 OK 为它创建一个表。
- 如果您使用的是 Excel 2013 或 2010,并且找不到 从表/范围 在 时间 选项卡,您需要从 微软 Power Query 对于 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列中有较长的数据,现在,您想将A列中的每5行转置为多列,例如将A1:A5转换为C6:G6,将A6:A10转换为C7:G7,依此类推显示以下截图。 如果不在Excel中重复复制和粘贴,如何处理此任务?
- 如何将列和行转置/转换为单列?
- 当您使用Excel工作表时,有时会遇到此问题:如何将一系列数据转换或转置为单个列? (请参见以下屏幕截图:)现在,我为您介绍三个快速技巧来解决此问题。
- 如何将列和行转置/转换为单行?
- 如何将多行和多列连接成一个长行? 也许,这对您来说似乎很容易,因为您可以将它们一个接一个地复制并手动将它们连接成一行。 但是,如果有数百行和数百列,这可能既费时又乏味。 在这里,我将讨论一些快速解决它的技巧。
最佳办公生产力工具
🤖 | Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数... |
热门特色: 查找、突出显示或识别重复项 | 删除空白行 | 合并列或单元格而不丢失数据 | 不使用公式进行四舍五入 ... | |
超级查询: 多条件VLookup | 多值VLookup | 跨多个工作表的 VLookup | 模糊查询 .... | |
高级下拉列表: 快速创建下拉列表 | 依赖下拉列表 | 多选下拉列表 .... | |
列管理器: 添加特定数量的列 | 移动列 | 切换隐藏列的可见性状态 | 比较范围和列 ... | |
特色功能: 网格焦点 | 设计图 | 大方程式酒吧 | 工作簿和工作表管理器 | 资源库 (自动文本) | 日期选择器 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊过滤器 (过滤粗体/斜体/删除线...)... | |
前 15 个工具集: 12 文本 工具 (添加文本, 删除字符,...) | 50+ 图表 类型 (甘特图,...) | 40+ 实用 公式 (根据生日计算年龄,...) | 19 插入 工具 (插入二维码, 从路径插入图片,...) | 12 转化 工具 (小写金额转大写, 货币兑换,...) | 7 合并与拆分 工具 (高级组合行, 分裂细胞,...) | ... 和更多 |
使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松
- 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
- 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
- 每天将您的工作效率提高50%,并减少数百次鼠标单击!