Excel 如何将列转换为行

您将信息或数据存储在各种行和列中。跟踪这些信息可能非常困难。在错误输入数据之后，最常见的错误之一是转置列和行。有些人在Excel电子表格中将主要字段水平排列，而其他人则更喜欢垂直排列数据。有时，这些偏好会导致您想要转置Excel数据的情况。

使用Excel的话，将条目复制/粘贴到其他位置或重来都是错误的，因为这样会将所有条目粘贴到同一行或同一列。在“特殊粘贴”菜单中有一种非常简单的修复方法，可以让您在不重来的情况下将列转换为行。

如果您的工作表中有需要旋转以将其排列为行的列数据，请使用 转置 功能。使用此功能，您可以快速地将数据从列转换为行，反之亦然。

在Excel中转置数据是许多用户熟悉的任务。您经常创建一个复杂的表格，然后意识到最好将其旋转以便进行更好的数据分析或图表展示。

微软必须早就预见到了这个问题，因为他们提供了几种方法来交换列和行。这些解决方案适用于所有版本的Excel 2016、2013、2010、2007以及更早的版本。

使用粘贴在Excel中将行转换为列

假设您有一个类似下面所示的数据集。国家名称以列的方式组织，但国家列表太长了，所以我们最好将列改为行，以适应屏幕：

要将行切换为列，您需要按照以下步骤进行操作：

步骤1： 选择您的表格的原始数据。

要快速选择整个表格，即电子表格中的所有带有数据的单元格，按下Ctrl + Home，然后按下Ctrl + Shift + End。

步骤2： 通过右键单击选择或选择 复制 选项卡或按下Ctrl + C复制所选单元格。

步骤 3： 选择目标范围的第一个单元格。

请务必选择一个不包含原始数据范围的单元格，以防止复制区域和粘贴区域重叠。

例如，如果您目前有9列和5行，转换后的表格将有5列和9行。

步骤4： 右键单击目标单元格，然后从上下文菜单中选择 粘贴特殊 。

步骤5： 然后在 转置 复选框上打勾。

步骤6： 点击 确定 按钮。您将获得表格的转置结果。

注意。如果您的源数据包含公式，请根据需要调整或锁定到特定单元格，正确使用相对和绝对引用。

正如您所看到的，粘贴特殊功能可让您执行行转列或列转行的转换。该方法还会复制您的原始数据格式，这是它更有优势的另一个论点。

然而，这种方法有两个缺点，阻止它被称为在Excel中转置数据的完美解决方案：

1. 它不适用于旋转完全功能的Excel表。如果您复制整个表格，然后打开 粘贴特殊 对话框，您会发现 转置 选项被禁用。在这种情况下，您需要先复制没有列标题的表格或将其转换为一个范围。
2. 在 粘贴特殊 选项中， 转置 不会将新表格与原始数据链接起来，所以它只适用于单次转换。每当源数据发生变化，您需要重复这个过程并旋转表格。

如何转置表格并与原始数据链接

让我们看看您如何使用熟悉的 粘贴特殊 技巧将行转换为列，但将结果表格连接到原始数据集。

这种方法的最好之处在于，每当您更改源表格的数据时，翻转的表格都会反映出这些更改并相应更新。按照以下步骤转置表格并将其链接到原始数据。

步骤1： 复制要转换为列或要转换为行的列。

步骤2： 在同一张或另一张工作表中选择一个空单元格。

步骤3： 打开 粘贴特殊 对话框，如上所述，然后单击左下角的 粘贴链接 。

你将获得以下类似的结果：

步骤4： 选择新表格，并打开Excel的查找和替换对话框，或按Ctrl + H直接进入 替换 选项卡。

步骤5： 用“xxx”或任何其他在您的真实数据中不存在的字符替换所有“=”字符。

这将使您的表格变成下面截图所示的样子。现在您需要按照以下两个步骤进行操作。

步骤6： 复制带有“xxx”值的表格，然后使用 特殊粘贴 按钮和 转置 来将列翻转为行。

步骤7： 最后，再次打开 查找和替换 对话框，以撤销更改，即将所有的”xxx”替换为”=”，将链接恢复到原始单元格。

这种方法有点长，但是优雅。这种方法的唯一缺点是在过程中原始格式会丢失，您需要手动恢复它。

如何使用公式将列转换为行

在Excel中动态将列转换为行的一种简单方法是使用TRANSPOSE或INDEX/ADDRESS公式。这些公式在保持与原始数据的连接的同时还有不同的工作方式。

1. 使用TRANSPOSE函数将行转换为列

TRANSPOSE函数是专门为在Excel中转置数据而设计的。该函数具有以下语法。

=TRANSPOSE(array)

在这个例子中，我们将转换相同的按销售额列出国家的表格。

您需要按照以下步骤使用TRANSPOSE函数来转换表格情境。

步骤1： 计算原始表格中的行数和列数，并选择相同数量的空白单元格，但是在另一个方向或工作表中。

我们的示例表格有8列和5行，包括表头。由于TRANSPOSE函数会将列变为行，我们选择一个5列和8行的范围。

步骤2： 在选择的空白单元格中，写入以下公式。

=TRANSPOSE(A1:H5)

步骤3： 由于我们的公式需要应用于多个单元格，按Ctrl + Shift + Enter将其转换为数组公式。

您将获得将列转换为行，行转换为列的表格，如下所示。

转置函数的优点

使用转置函数的主要好处是旋转后的表格保留了源表格的连接。每当您更改源数据时，转置表格也会相应更改。

转置函数的缺点

• 转换后的表格不会保留原始表格的格式。
• 如果原始表格中有空单元格，则转置后的单元格将包含0。要解决此问题，使用转置函数和IF函数的组合。
• 您无法编辑旋转后的表格中的任何单元格，因为它非常依赖于源数据。如果您尝试更改某些单元格的值，将出现“您无法更改数组的一部分”错误。
• 该函数很好且易于使用，但在许多情况下可能缺乏灵活性，因此可能不是最佳方法。

  2. 使用INDIRECT和ADDRESS函数将列转换为行

以下示例将使用两个函数的组合。这有点棘手。假设您有5列5行的数据，请按照以下步骤使用INDIRECT/ADDRESS函数转换表格。

步骤1： 在目标范围的最左侧单元格中输入下面的公式，然后按Enter键。

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

步骤2： 通过拖动所选单元格右下角的小黑十字，将公式向右和向下复制到所需的行和列。

所有列在新创建的表中变为行，而行则变为列。

如果你的数据不是从第一行和第一列开始，你就需要使用稍微复杂一些的公式：

=INDIRECT(ADDRESS(COLUMN(A1) - COLUMN(A1) + ROW(A1), ROW(A1) - ROW(A1) + COLUMN(A1)))

A1是源表格中左上角的单元格。此外，请记住使用绝对和相对单元格引用。

然而，与原始数据相比，转置后的单元格在颜色上看起来非常平淡。要恢复原始格式，只需按照以下三个简单步骤进行操作：

步骤1： 复制原始表格。

步骤2： 选择转置后的表格。

步骤3： 右键单击转置后的表格，选择 粘贴选项 ，然后点击 格式 按钮。

公式的工作原理

现在你知道如何使用INDIRECT/ADDRESS函数的组合，你可能想要了解这个公式实际上是做什么的。

• 如其名所示，INDIRECT函数用于间接引用一个单元格。但INDIRECT的真正威力在于它可以将任何字符串转换为引用，包括使用其他函数和其他单元格值构建的字符串。这正是我们要做的。
• 我们在公式中使用了3个更多的函数，如ADDRESS、COLUMN和ROW。
• ADDRESS函数通过您分别指定的行号和列号来获取单元格地址。
• 在我们的公式中，我们按相反顺序提供坐标，这正是它的奥秘所在！换句话说，公式的这一部分ADDRESS(COLUMN(A1), ROW(A1))将行换成列，即将列号变为行号，然后将行号变为列号。
• 最后，INDIRECT函数输出旋转后的数据。

INDIRECT/ADDRESS函数的优点

• 此公式为将行转换为列提供了更灵活的方法。
• 它允许对转置后的表格进行任何更改，因为您使用的是常规公式，而不是数组公式。

INDIRECT/ADDRESS函数的缺点

原始数据的格式丢失。但同时，您可以快速恢复原始格式。

在Excel中使用VBA宏转置数据

要在Excel中自动化将列转换为行，可以使用以下宏：

Sub TransposeColumnsRows()
    Dim SourceRange As Range
    Dim DestRange As Range

    Set SourceRange = Application.InputBox(Prompt:="Please select the range to transpose", Title:="Transpose Rows to Columns", Type:=8)
    Set DestRange = Application.InputBox(Prompt:="Select the upper left cell of the destination range", Title:="Transpose Rows to Columns", Type:=8)

    SourceRange.Copy
    DestRange.Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Application.CutCopyMode = False

End Sub

注意：使用VBA进行转置的限制是65536个元素。如果您的数组超过此限制，多余的数据将被静默丢弃。

如何使用宏将列转换为行

在您的工作簿中插入宏后，请按照以下步骤旋转您的表情景。

步骤1： 打开目标工作表，按下Alt + F8键。

步骤2： 选择 TransposeColumnsRows 宏，然后点击 运行 按钮。

步骤3： 选择要转置的范围，然后点击 确定 按钮。

步骤4： 选择目标范围的左上角单元格。

步骤5： 点击 确定 按钮。