如何在 Excel 中粘贴转置数据的同时保留公式引用？
在使用 Excel 时，转置功能常用于将数据从列转换为行，或反之。然而，当这些数据包含公式时，通常会遇到一个常见问题：Excel 默认会自动调整单元格引用以适应新的方向。如下方截图所示，这种自动调整往往会破坏预期的计算结果，尤其在处理复杂或高度关联的数据集时更为明显。掌握如何在粘贴转置数据的同时保留原始公式引用，对财务模型、工程计算或关联仪表板等场景中的用户至关重要——因为在这些应用中，公式的完整性直接关系到结果的准确性。本文将介绍几种实用方法来实现这一目标，说明每种方法的最佳与最差适用场景，并提供故障排除建议，助您操作更顺畅高效。
VBA 代码 —— 转置单元格同时保留公式引用（相对或绝对）
Excel 公式 —— 使用 INDIRECT 或地址构造手动重建转置后的公式
使用 F4 键将转换单元格引用转换为绝对引用并转置数据
1. 选择包含公式的单元格
单击包含需要调整公式的单元格。
2. 打开编辑栏
单击编辑栏，将光标定位到公式内部。
3. 转换为绝对引用
选中公式中的全部内容，然后按下 F4 键。
这将在相对引用、绝对引用与混合引用之间切换。
对公式中的所有单元格引用重复此操作，直到全部转换为绝对引用。
4. 复制数据
选择要复制的数据区域，然后按下 Ctrl+C。
5. 以转置形式粘贴数据
右键单击目标单元格，然后选择“选择性粘贴”→“转置”。
提示：
绝对引用可确保公式始终引用同一单元格，即使复制或移动后也毫不改变。转置数据则能将行与列互换——是重新组织数据布局的绝佳利器！
使用查找和替换函数转置并保留引用
要在 Excel 中转置单元格区域并保留原始公式引用，您可以使用查找和替换功能，先将公式临时转换为文本，重新定位后再还原为公式。此方法适用于中小型数据集，在未安装额外插件或不愿使用 VBA 时尤为实用。
1. 首先，选择包含要转置公式的单元格区域，按 Ctrl + H 即可打开查找和替换对话框。
2. 在查找和替换对话框中，在“查找内容”字段中输入=，在“替换为”字段中输入#=
3. 单击全部替换，系统将弹出对话框，显示已执行的替换次数。单击确定，再单击关闭即可退出对话框。
4. 保持已转换为文本的单元格仍处于选中状态，按 Ctrl + C 复制。移动到目标粘贴位置，右键单击，在上下文菜单中选择选择性粘贴> 转置，即可完成转置粘贴。请注意，若数据集较大或公式中包含易失性函数，建议仔细核对粘贴后的结果。
5. 粘贴完成后，再次按下 Ctrl + H，即可打开查找和替换对话框。现在，请反向执行之前的替换操作：在“查找内容”框中输入#=，在“替换为”框中输入=，即可将文本重新转换为可运行的公式。
6. 单击全部替换，再单击确定或关闭即可完成操作。您的公式现已成功转置，并保留了原始区域中的引用。
此手动方法最适合小型数据集。对于更复杂的区域或混合引用样式的公式，请务必仔细检查结果，确保公式按预期重新计算。如果公式中包含命名区域或外部引用，转置后可能需要对其进行复查。
使用 Kutools for Excel 转置并保留引用
Kutools for Excel 的转换单元格引用工具为您提供高效解决方案——若您经常需要转置包含公式的数据，可在转置前快速将所有公式引用转换为绝对引用，确保原始引用在转置后保持不变，最大限度降低手动干预与公式损坏的风险。
安装 Kutools for Excel 后，请按以下步骤操作：
1. 选择包含要转置公式的单元格，然后点击 Kutools > 更多（位于“公式”组中）> 转换单元格引用，即可打开引用转换对话框。
2. 在转换单元格引用对话框中，选择转换为绝对引用选项，然后单击确定。此操作将确保所选公式中的所有单元格引用均设置为带 $ 符号的绝对引用，从而在转置单元格时避免引用偏移。
3. 现在，再次选中这些单元格，按下 Ctrl + C 进行复制。在目标位置右键单击，从上下文菜单的转置子菜单中选择选择性粘贴，即可转置您的数据并保留正确的公式引用。
如果您经常执行此类任务，尤其是处理包含大量公式或结构更复杂的大型电子表格时，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 Sub3. 要运行代码，请单击
按钮，或按 F5. 按照提示操作：选择您希望转置的源数据（包括公式）以及输出结果的起始单元格。该宏将复制并转置所有公式，保持引用与原始位置完全一致。若公式使用相对引用，请注意其上下文可能发生变化（导致结果值与原始值不一致），但公式文本本身不会调整，从而保留原有的引用类型。
此方法特别适用于大型数据集、重复性操作或需要精细控制的场景。如果发生错误（例如未正确选择足够大小的目标区域），请重新运行宏，并仔细检查所选区域。
总之，Excel 提供了多种在保留原始公式引用的同时转置数据的方法，包括手动查找与替换、Kutools 等扩展工具、VBA 自动化，以及基于 INDIRECT 或 ADDRESS 函数的公式方案。选择方法时，请综合考虑您的数据量、公式复杂度，以及对自动化与手动控制的偏好。务必仔细核对结果——尤其是涉及相对引用时——以确保计算准确无误，并在执行任何批量操作或运行宏之前，先保存工作簿的备份副本。若遇到“#REF！”错误或数值异常，请检查引用是否超出了有效范围，或绝对/相对混合引用是否发生偏移。如有疑问，建议先在小样本数据上试用该方法，以增强对整个流程的信心。
最佳办公效率工具
| 🤖 | KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行 | 生成代码| 创建自定义公式 | 数据分析及生成图表| 调用 Kutools Functions…… |
| 热门功能：查找、高亮或标记重复项 | 删除空白行 | 合并列或单元格且不丢失数据 | 不使用公式的四舍五入…… | |
| 高级 LOOKUP：多条件 VLookup | 多值 VLookup | 跨多工作表 VLookup | 模糊查找…… | |
| 高级下拉列表：快速创建下拉列表 | 级联下拉列表 | 多选下拉列表…… | |
| 列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列…… | |
| 特色功能：网格聚焦 | 设计视图 |增强编辑栏 | 工作簿和表管理器 | 资源库（自动文本）| 日期提取 | 汇总工作表 | 加密/解密单元格 | 按列表发送邮件 | 超级筛选 | 特殊筛选（筛选粗体单元格/斜体/删除线……） ...... | |
| 精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）| 50+ 图表 类型（甘特图，……）| 40+ 实用公式（基于生日计算年龄，……）| 19 插入工具（插入二维码，从路径插入图片，……）| 12 转换工具（小写金额转大写，汇率转换，……）| 7 合并和拆分工具（高级合并行，分割单元格，……）|……更多 |
使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……
Office Tab 为 Office 带来标签式界面，让您的工作更轻松
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
- 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
- 将您的工作效率提升 50%，每天减少数百次鼠标点击！
所有 Kutools 插件，一个安装程序
Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。
- 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
- 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
- 协同效果更佳— 在多个 Office 应用中实现高效协同
- 30 天全功能试用— 无需注册，无需信用卡
- 超值之选— 比单独购买插件更省钱