如何在 Excel 中粘贴转置并保持公式引用？

在使用 Excel 时，转置功能常用于将数据的方向从列切换为行，或反之亦然。然而，当这些数据包含公式时，就会出现一个常见的挑战——Excel 默认会调整单元格引用以匹配新的方向。如下图所示，这种自动调整经常破坏预期的计算，尤其是在复杂或链接的数据集中。了解如何在转置数据的同时保留原始公式引用，对于处理财务模型、工程计算或链接仪表板的人来说至关重要，因为保持公式完整性非常重要。本文将解释实现这一结果的几种实用方法，分析它们的最佳和最差使用场景，并提供故障排除建议以便更顺畅地操作。

使用 F4 键将公式引用转换为绝对引用并转置数据

使用查找和替换功能转置并保留引用

使用 Kutools for Excel 转置并保留引用

VBA 代码 - 在保留公式引用（相对或绝对）的同时转置单元格

Excel 公式 - 使用 INDIRECT 或地址构造手动重新创建转置公式

使用 F4 键将公式引用转换为绝对引用并转置数据

1. 选择公式单元格

单击包含要调整公式的单元格。

2. 打开公式栏

单击公式栏，将光标置于公式内部。

3. 转换为绝对引用

在公式栏中选择整个公式，然后按 F4 键。

这将在相对引用、绝对引用和混合引用之间切换引用格式。

对公式中的所有单元格引用重复此操作，直到它们全部变为绝对引用。

4. 复制数据

选择要复制的数据区域并按 Ctrl + C。

5. 粘贴为转置数据

右键单击目标单元格，然后选择“选择性粘贴 → 转置”。

💡 提示：
绝对引用确保公式始终引用相同的单元格，即使在复制或移动后也是如此。转置数据会将行切换为列或将列切换为行，非常适合重新组织数据布局。

使用查找和替换功能转置并保留引用

为了在转置一系列单元格的同时保留 Excel 中的原始公式引用，您可以使用“查找和替换”功能将公式暂时转换为文本，重新定位它们，然后将其还原为公式。这种方法适用于中小型数据集，在没有安装其他插件或不想使用 VBA 的情况下非常有用。

1. 首先，选择包含要转置公式的单元格范围。按 Ctrl + H 打开“查找和替换”对话框。

2. 在 查找和替换 对话框中，输入 = 在 查找内容 字段中，然后输入 #= 在 替换为 字段中。此步骤通过替换等号将实时公式转换为纯文本。这样做可以防止 Excel 在复制和转置过程中转换公式单元格引用。

3. 单击 全部替换。将弹出一个对话框，显示替换的数量。单击 确定 然后 关闭 退出对话框。

4. 选中已转换为文本的单元格，然后按 Ctrl + C 进行复制。移动到您希望粘贴的位置，右键单击，然后从上下文菜单中选择 选择性粘贴 > 转置 进行转置和粘贴。请注意，如果您有大型数据集或您的公式使用的是易失性函数，则可能需要仔细检查粘贴后的结果。

5. 粘贴完成后，再次按 Ctrl + H 打开 查找和替换 对话框。现在，反转原始替换：在 #= 查找内容 框中输入 并在 = 替换为 框中输入 这将把文本转换回功能性公式。

6. 单击 全部替换，然后单击 确定 > 关闭 完成。您的公式现在已转置并保留了与原始范围相同的引用。

这种手动方法最适合小型数据集。对于更复杂的范围或处理混合引用样式时，请仔细检查结果以确保公式按预期重新计算。如果存在命名范围或外部引用，则在转置后可能需要对其进行审查。

使用 Kutools for Excel 转置并保留引用

如果您经常需要转置包含公式的数据，Kutools for Excel 提供了一种简化的解决方案。其“转换单元格引用”工具允许您在转置之前快速将所有公式引用转换为绝对引用。这确保了在转置后原始引用保持不变，从而最大限度地减少手动干预和公式损坏的风险。

安装 Kutools for Excel 后，按照以下步骤操作：

1. 选择包含要转置公式的单元格，然后单击 Kutools > 更多 （在“公式”组中）> 转换单元格引用。这将打开引用转换对话框。

2. 在 转换单元格引用 对话框中，选择 至绝对 选项并单击 确定。此步骤确保所选公式中的所有单元格引用都设置为绝对引用（带有 $ 符号）。因此，在转置单元格时引用不会发生偏移。

3. 现在，再次选择单元格并按 Ctrl + C 进行复制。在目标粘贴位置，右键单击并从上下文菜单中选择 转置 来自 选择性粘贴 子菜单。这将转置您的数据并保留正确的公式引用。

提示：如果您希望快速转换表格结构（例如将行更改为列以适应大型数据表），Kutools for Excel 还提供了“转换表格维数”功能。该工具特别适合快速重构大型表格，同时不会丢失数据连接或格式。该工具可免费试用有限天数；点击此处下载以体验所有功能。

Kutools 解决方案在您经常执行此类任务时最为有效，尤其是处理大量数据或包含多个公式的复杂电子表格时。作为预防措施，始终检查转置后是否需要绝对引用；根据需要，可以使用相同的功能将引用转换回相对引用。如果原始公式混合了相对引用和绝对引用，请在转换和转置操作后检查它们的准确性。

VBA 代码 - 在保留公式引用（相对或绝对）的同时转置单元格

在高级场景中，编写 VBA 宏可以自动化转置公式的过程，同时保留原始引用类型——无论是相对引用、绝对引用还是混合引用。该解决方案非常适合熟悉宏的用户，尤其适用于大型数据范围或频繁执行此操作的情况。VBA 提供灵活性，能够容纳复杂的引用模式，并直接处理各种公式结构。

1. 首先，如果 Excel 中尚未显示“开发工具”选项卡，请启用它。转到“开发工具 > Visual Basic”以打开 VBA 编辑器。

2. 在 VBA 编辑器中，单击“插入 > 模块”以打开新模块窗口，然后将以下 VBA 代码复制并粘贴到此窗口中：

Sub TransposeFormulasPreserveReferences()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destRange As Range
    Dim numRows As Long, numCols As Long
    Dim i As Long, j As Long
    Dim tempArray As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set sourceRange = Application.InputBox("Select the range you want to transpose", xTitleId, Selection.Address, Type:=8)
    
    If sourceRange Is Nothing Then Exit Sub
    
    numRows = sourceRange.Rows.Count
    numCols = sourceRange.Columns.Count
    
    Set destRange = Application.InputBox("Select the upper-left cell for the transposed output", xTitleId, , Type:=8)
    
    If destRange Is Nothing Then Exit Sub
    
    tempArray = sourceRange.Formula ' Store original formulas
    
    ' Transpose formulas, cell by cell
    For i = 1 To numRows
        For j = 1 To numCols
            destRange.Offset(j - 1, i - 1).Formula = tempArray(i, j)
        Next j
    Next i
End Sub

3. 要运行代码，请单击 按钮，或按 F5。按照提示操作：选择要转置的源数据（包括公式）以及输出的起始单元格。宏将复制并转置所有公式，保留与原始位置相同的引用。如果您的公式使用相对引用，请注意它们的上下文可能会发生变化（导致值可能与原始值不匹配），但公式文本本身不会被调整，从而保留引用类型。

这种方法特别适用于大型数据集、重复操作或需要精细控制的情况。如果发生错误（例如未正确选择足够大小的目标区域），请重新运行宏并仔细检查范围选择。

总之，Excel 提供了几种在转置数据的同时保留原始公式引用的方法，包括手动查找和替换、高级工具如 Kutools、VBA 自动化以及使用 INDIRECT 或 ADDRESS 的基于公式的方法。在选择方法时，请考虑您的数据大小、公式复杂性和对自动化与手动控制的需求。始终仔细检查结果——特别是相对引用——以确保计算保持正确，并在执行任何批量更改或运行宏之前保存工作簿的备份副本。如果遇到“引用”错误或意外值，请验证引用是否未超出正确范围，或者绝对/相对混合引用是否未错误偏移。如有疑问，请先在小样本上尝试您的方法，以建立对该过程的信心。