excel纵列变横列怎么操作,Excel 列转行 函数 用法

题图来自Unsplash，基于CC0协议

导读

将Excel中的数据从纵向排列转为横向排列，或者反之，其实是数据转置的问题。这种情况在数据整理和报表制作中非常普遍。掌握相应的方法可以大大提高工作效率。以下是几种常用的操作方法，适用于不同场景和需求。

方法一：使用“粘贴为转置”功能（最常用）

这是最直观、最常用的将列转成行或将行转成列的操作方法。

1. 选择数据源： 首先，选中你想要转置的原始数据区域。例如，你有一列包含多个项目的销售额（列A：项目名称，列B：销售额），你想把每个项目的销售额变成一行，并排显示。
2. 复制数据： 右键单击选中区域的右下角单元格（出现黑色小十字），然后使用快捷键 Ctrl + C 复制，或者直接用鼠标左键双击该单元格后选择“粘贴选项”中的“粘贴为转置”。也可以选中单元格后，直接使用快捷键 Ctrl + C 复制。
3. 选择粘贴位置： 分别按 Ctrl + V 打开粘贴选项窗格。
4. 选择“转置”粘贴： 在粘贴选项列表中，取消选中“自动图文集”旁边的勾选框（通常默认会选中），然后勾选“转置”选项。如果你使用的方法三（复制后），点击“确定”即可。如果你使用的方法二（右键单击副本后），窗口顶部会有“转置”的选项（通常在“样式”标签页下），选择它并点击“确定”。

• 操作步骤详解：
  • 光标定位到你希望放置转置结果的起始单元格。例如，如果要转置一列数据到行上，你希望新行的第一单元格是原数据的第一行（项目名称），那么就点那里。
  • 按 Ctrl + V 应用粘贴，并在选项中选择“转置”。
  • 按 Esc 键取消其他多余选项。
  • 或者，在关键词为“转置”的粘贴选项中直接单击选择。

方法二：使用快捷键 Alt + H, P, S

这是一个更高效的快捷键组合，利用了Excel的粘贴选项面板。

1. 复制数据： 按前述方法复制需要转置的数据区域。
2. 访问粘贴选项面板： 将光标定位于目标单元格。按 Alt + H（打开主页选项面板），默认会显示“P”的快捷键，按 P，此时下方会弹出一个粘贴选项的面板。按 S 切换到“转置”粘贴选项（或直接按下小键盘上的|旁边的箭头按键滚动）。
3. 粘贴： 按 Enter 键将选定的转置粘贴选项应用在目标单元格上。
   • 细分步骤： 按 Alt + H，按 M 打开格式窗口，按 P 滚动至粘贴选项，按 S 选择转置，然后按 V 或 Enter 或 ESC（ESC用于放弃粘贴撤回格式窗口）粘贴。

方法三：使用公式 TRANSPOSE

如果数据会经常变化，或者需要引用转置结果进行计算，使用 TRANSPOSE 函数配合公式进行动态转置可能比复制粘贴更合适。

1. 理解转置：
   • TRANSPOSE 函数用于返回转置的数组，即把一个纵向的区域变为一个横向的区域，或者反之。
2. 单个元素转置：
   • 假设单元格 A1 包含数值 5。你需要在另一个单元格获得 5。
   • 在目标单元格（例如 B1）中输入：=TRANSPOSE(A1)
   • 按 Enter 键。
3. 一维区域转置：
   • 将列转为行： 假设 A1:A5 包含一行五个项目名称。你想将它们转为一行放在 B1 其中，需要选中 B1:E1（横向五个单元格），输入：=TRANSPOSE(A1:A5)，然后按 Ctrl + Shift + Enter(对于早于 Excel 365/2016 版本的Excel是数组公式)，公式会被应用到整个区域内。在 Excel 2016 及更新版本或 Microsoft 365 中，数组公式会自动填入区域，只需按Enter(如果是新增功能，可参照上下文是否启用动态数组)。
   • 将行转为列： 仿照上述方法，将一行数据通过 TRANSPOSE 函数变为一列。
4. 二维区域转置：
   • 将列区域转为行区域： 假设 A1:B5 是一个2行5列的数据区域（A列为姓名，B列为分数）。
   • 选中一个目标区域，这个区域应该是5行1列，例如 D1:D5。
   • 在目标区域的左上角输入公式：=TRANSPOSE(A1:B5)
   • 按 Ctrl + Shift + Enter(或直接按Enter，看Excel版本)。
   • 结果：原来的2行5列数据，现在会显示在5行2列的新区域中。
   • 同理，你可以将一个横向区域（如第1行: 第1行是多列数据）变为一个纵向区域（多行同列）。注意：纵向区域首先按列再按行处理，横向区域首先按行再按列处理。理解“数组”在Excel中的处理方式很重要。

使用函数+INDIRECT进行更灵活的选择

有时候你可能需要从工作表特定的单元格引用数据来构建转置后的结果，这时可以结合 INDIRECT 函数和 TRANSPOSE。但其应用可能相对复杂一些，通常用于构建动态范围或基于选择的数据表。

总结

• 粘贴为转置 是最快、最常用的转换静态数据格式的方法。
• 快捷键 Ctrl + Shift + Enter（旧版数组公式）或直接按 Enter（新的动态数组，功能区为“转置”时）与 TRANSPOSE 函数结合，可以创建响应变化的动态转置结果。
• TRANSPOSE 函数适合数据变化后自动更新转置结构的场景。
• 选择哪种方法取决于你的主要需求是 一次性格式调整 (用方法一或二) 还是 需要公式驱动的动态结果 (用方法三)。

根据你的具体数据和场景，选择合适的方法去实现列和行之间的转换吧。