如何在 Excel 中交换列和行 - 9 种方法

这是一个概述。

此数据集包括员工信息：ID、全名、职称、部门和年薪.

方法 1 – 使用粘贴特殊功能交换列和行

使用选择性粘贴功能。

选择整个数据集 (B4:G8)，然后按 CTRL + C 复制范围。

注意：对于较大的数据集，单击任意单元格并按CTRL + A 选择整个数据集。

右键单击目标单元格 (B10)，然后选择选择性粘贴。

在选择性粘贴对话框中，选中转置并点击确定。

这是输出。

按照相同的步骤反转结果：

方法 2 – 利用查找和替换功能使交换动态化

选择整个数据集并复制它。
按照方法 1 中的步骤打开选择性粘贴对话框。
点击粘贴链接。

它将在选定的输出范围内粘贴数据而不进行格式化。

选择粘贴的范围 (B10:G14) 并转到主页选项卡。
选择查找和替换>>替换。您还可以按 CTRL + H。

在查找和替换对话框中，在查找内容和替换为<中输入=和xx /强>。
单击全部替换。

消息“全部完成。我们进行了 30 次替换。”显示。 xx 替换了“=”。

单击确定。

复制B10:G14并将其粘贴到B16中。
右键单击。
在粘贴选项中选择移调 (T)。

这是输出。

选择粘贴的范围，然后按CTRL + H 打开查找和替换对话框。

在查找内容和替换为中输入xx 和=。
单击全部替换。

这是输出。

这是输出（格式化并隐藏行之后）。

方法 3 – 使用 Power Query 编辑器交换大型数据集中的列和行

使用 Power Query 编辑器。

选择数据集并转到数据>>来自表/范围。

在创建表格对话框中，显示所选的表格范围。

单击确定。

在Power Query 编辑器窗口中，选择表格中的转换>>转置。

该表已转置。

选择使用第一行作为标题。

在首页标签中，点击关闭并加载 >> 关闭并加载到...。

在导入数据对话框中，选择输出目的地。此处，现有工作表和B11 作为单元格引用。
单击确定。

这是输出。

方法四——使用TRANSPOSE函数自动更新数据

使用TRANSPOSE 函数。

在B10中，使用以下公式并按ENTER。

=TRANSPOSE(B4:G8)

由于它是一个数组公式，因此它返回一个包含转置数据但未格式化的数组。

注意：按CTRL + SHIFT + ENTER 如果您使用的是 Excel 365 以外的版本。

这是输出（格式化后）。

注意：此方法也是动态的。

方法 5 – 使用 OFFSET 函数交换列和行

使用OFFSET函数。

输入从0到（总行数-1）或（5 – 1）或4（有5 行）到粘贴数据的区域；输入从0到（总列数-1）或（6 – 1）或5（有6列）的序列号该区域的左侧。

在B11中使用以下公式，然后按ENTER。

=OFFSET($B$4,B$10,$A11)

向下拖动填充手柄将公式复制到其他单元格。

应用格式设置。

选择B10。编辑栏显示 0，但在工作表单元格中不可见。

您可以通过将输出单元格的字体颜色更改为白色来隐藏输出单元格周围的额外数字。

方法 6 – 使用 VLOOKUP 函数

使用VLOOKUP 函数。

手动转置第一列和第一行，并在输出范围的第一行和第一列中输入值。

在C11中，使用以下公式并按ENTER。

=VLOOKUP($B11,TRANSPOSE($C$4:$G$8),COLUMN()-1,FALSE)

这是输出。

方法 7 – 使用 INDEX 函数交换列和行

将INDEX 函数与COLUMN 和ROW 函数结合使用。

在B10中输入以下公式，然后按ENTER。

=INDEX($B$4:$G$8,COLUMN(B2),ROW(B2))

这是输出（格式化后）。

方法 8 – 组合 INDIRECT 和 ADDRESS 函数来交换列和行

组合INDIRECT 和ADDRESS 函数。

在B10中使用以下公式。
向下拖动填充手柄以查看其余单元格中的结果。

=INDIRECT(ADDRESS(COLUMN(C5)-COLUMN($B$4)+ROW($B$4), ROW(C5)-ROW($B$4)+COLUMN($B$4)))

公式细分

COLUMN(C5)：返回C5的列号。
COLUMN($B$4)：返回B4的列号。行和列引用周围的美元符号表示绝对引用（如果将公式复制到其他单元格，则引用不会更改）。
COLUMN(C5)-COLUMN($B$4)：计算参考单元格 (B4) 和目标单元格 (C5) 之间的列数>）。
ROW($B$4)：返回行号B4。
ROW(C5)-ROW($B$4)：计算参考单元格(B4)和目标单元格(C5)之间的行数>）。
COLUMN(C5)-COLUMN($B$4)+ROW($B$4)：将步骤 3 和 4 中计算的列数和行数添加到参考单元格中的行号。
ROW(C5)-ROW($B$4)+COLUMN($B$4)：将第 5 步中计算出的行数和列数添加到参考单元格中的列数。
ADDRESS(COLUMN(C5)-COLUMN($B$4)+ROW($B$4), ROW(C5)-ROW($B$4)+COLUMN($B$4))： 采用行和列值在步骤 6 和 7 中计算，并以文本字符串形式返回单元格地址。
间接（地址（列（C5）-列（$B$4）+行（$B$4），行（C5）-行（$B$4）+列（$B$4）））： 将 ADDRESS 函数返回的文本字符串转换为对目标单元格的引用。 INDIRECT函数引用基于文本字符串的单元格。

方法 9 – 应用 VBA 代码交换 Excel 中大型数据集中的列和行

转到开发人员选项卡，然后单击代码中的Visual Basic。

在Microsoft Visual Basic for Applications窗口中：

选择插入，然后选择模块。
输入VBA代码。

Sub Swap_Columns_Rows()

Dim inputRange As Range
Dim outputCell As Range
Dim outputRange As Range

'Get input range
Set inputRange = Application.InputBox("Select the range to transpose:", Type:=8)

'Get output cell
Set outputCell = Application.InputBox("Select the first cell of the output range:", Type:=8)

'Transpose data and copy formatting
Set outputRange = outputCell.Resize(inputRange.Columns.Count, inputRange.Rows.Count)
inputRange.Copy
outputRange.PasteSpecial Transpose:=True
inputRange.Copy
outputRange.PasteSpecial Paste:=xlPasteFormats, Transpose:=True

End Sub