跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

在 Excel 中转置数据的 5 种方法（逐步教程）

Author Amanda Li Last modified

转置数据意味着通过将数组的行转换为列或反之来改变数组的方向。在本教程中，我们将与您分享五种不同的方法来切换数组的方向并实现所需的结果。

视频：在 Excel 中转置数据

使用选择性粘贴将列转换为行

注意：如果您的数据在 Excel 表格中，“转置粘贴”选项将不可用。您应该先通过右键单击表格，然后从上下文菜单中选择“表格” > “转换为区域”来将表格转换为区域。

步骤 1：复制要转置的区域

选择要切换行和列的单元格区域，然后按“Ctrl” + “C”复制该区域。

Select and copy the range of cells where you want to switch rows and columns

步骤 2：选择转置粘贴选项

右键单击目标区域的第一个单元格，然后从右键菜单中点击“转置”图标（在“粘贴选项”下）。

Right-click on the first cell of the destination range, and click the Transpose icon from the right-clicking menu

结果

瞧！该区域瞬间被转置了！

The range is transposed

注意：转置后的数据是静态的，并且独立于原始数据集。如果您对原始数据进行任何更改，这些更改不会反映在转置的数据中。要将转置的单元格链接到原始单元格，请转到下一节。

(AD) 使用 Kutools 轻松转置表格维度

在 Excel 中将交叉表（二维表）转换为平面列表（一维列表）或反之总是需要耗费大量时间和精力。然而，安装了 Kutools for Excel 后，其“转置表格维度”工具将帮助您快速轻松地完成转换。

Kutools for Excel's Transpose Table Dimensions tool

“Kutools for Excel” - 包含超过 300 个实用功能，让您的工作更加轻松。立即获取 30 天全功能免费试用！

转置并将数据链接到源

要动态切换某个区域的方向（将列转换为行，反之亦然），并将切换后的结果连接到原始数据集，您可以使用以下任何一种方法：

使用 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))

结果

行被转换为列，列被转换为行。

Table is transposed by using the TRANSPOSE function

注意：如果您使用的是 Excel 365 或 Excel 2021，按下“Enter”键后，结果会自动溢出到所需的行数和列数。在应用公式之前，请确保溢出范围为空；否则会返回 #SPILL 错误。
使用 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)))

The formula is entered in the top-left-most cell of the destination range

注意事项：
  • 您应将 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：将公式复制到右侧和下方的单元格

选择公式单元格，拖动其填充柄（单元格右下角的小绿色方块）向下然后向右扩展到所需的行数和列数。

结果

列和行立即切换。

The columns and rows are switched right away

注意：数据的原始格式未保存在转置范围内，如有必要，您可以手动设置单元格格式。
使用选择性粘贴和查找替换将列转换为行

使用选择性粘贴方法结合查找和替换功能，可以多几个步骤让您在转置数据的同时将源单元格链接到转置后的单元格。

步骤 1：复制要转置的区域

选择要转置的单元格区域，然后按“Ctrl” + “C”复制该区域。

Select and copy the range of cells to transpose

步骤 2：应用粘贴链接选项

  1. 右键单击空白单元格，然后点击“选择性粘贴”。

    Right-click on a blank cell, and then click Paste Special

  2. 点击“粘贴链接”按钮。

    Click on Paste Link

您将获得如下所示的结果：

The links to the copied cells are pasted

 步骤 3：从粘贴链接结果中查找并替换等号（=）

  1. 选择结果区域（A6:C9）并按“Ctrl” + “H”，然后在“查找和替换”对话框中将“=”替换为“@EO”（或选定范围内不存在的任何字符）。

    Replace = with @EO in the Find and Replace dialog

  2. 点击“全部替换”，然后关闭对话框。以下是数据的显示效果。

    Replacement result

步骤 4：转置替换后的粘贴链接结果

选择区域（A6:C9）并按“Ctrl” + “C”复制它。右键单击空白单元格（这里我选择了 A11），然后从粘贴选项中选择“转置”图标以粘贴转置结果。

Right-click on a blank cell (here I selected A11) and select the Transpose icon from Paste Options

步骤 5：恢复等号（=）以将转置结果链接到原始数据

  1. 保持转置结果数据 A11:D13 选中状态，然后按“Ctrl” + “H”，将“@EO”替换为“=”（与“步骤 3”相反）。

    Replacement result

  2. 点击“全部替换”，然后关闭对话框。

结果

数据已转置并链接到原始单元格。

注意：数据的原始格式已丢失；您可以手动恢复。请随时在过程结束后删除 A6:C9 区域。
使用 Power Query 转置并将数据链接到源

Power Query 是一个强大的数据自动化工具，可让您轻松在 Excel 中转置数据。您可以按照以下步骤完成任务：

步骤 1：选择要转置的区域并打开 Power Query 编辑器

选择要转置的数据区域。然后，在“数据”选项卡的“获取和转换数据”组中，点击“从表格/区域”。

From Table/Range button on the ribbon

注意事项：
  • 如果所选数据区域不在表格中，将弹出“创建表格”对话框；点击“确定”为其创建表格。
  • 如果您使用的是 Excel 2013 或 2010 并且在“数据”选项卡上找不到“从表格/区域”，则需要从“Microsoft Power Query for Excel 页面”下载并安装它。安装后，转到“Power Query”选项卡，点击“Excel 数据”组中的“从表格”。

步骤 2：使用 Power Query 将列转换为行

  1. 转到“转换”选项卡。在“使用第一行作为标题”下拉菜单中，选择“将标题作为第一行”。

    Go to the Transform tab. In the Use First Row as Headers drop-down menu, select Use Headers as First Row

  2. 点击“转置”。

    Click on Transpose

步骤 3：将转置后的数据保存到工作表

在“文件”选项卡上，点击“关闭并加载”以关闭“Power Editor”窗口并在新工作表中加载转置后的数据。

Click Close & Load

结果

转置后的数据被转换为新创建的工作表中的表格。

The transposed data is converted to a table in a newly created worksheet

注意事项：
  • 如上所示，第一行生成了额外的列标题。要将标题下方的第一行提升为列标题，请选择数据内的单元格并点击“查询” > “编辑”。然后，选择“转换” > “使用第一行作为标题”。最后，选择“主页” > “关闭并加载”。
  • 如果对原始数据集进行了任何更改，您可以通过点击“查询和连接”窗格中表格旁边的“刷新”图标，或点击“查询”选项卡上的“刷新”按钮，将上述转置数据更新为这些更改。

(AD) 使用 Kutools 快速转换区域

Kutools for Excel 中的“转换区域”工具可以帮助您轻松转换（转换）垂直列到多列或多行，反之亦然，或将行转换为多行或反之亦然。

Kutools for Excel's Transform Range tool

“Kutools for Excel” - 包含超过 300 个实用功能，让您的工作更加轻松。立即获取 30 天全功能免费试用！