如何在 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 的基於公式的解決方案。選擇方法時，請考慮您的數據大小、公式複雜性和自動化與手動控制的需求。始終雙重檢查結果——特別是相對引用——以確保計算保持正確，並在執行任何批量更改或運行宏之前保存工作簿的備份副本。如果您遇到“引用”錯誤或意外值，請確認沒有引用溢出正確範圍之外，或者絕對/相對混合引用未錯誤移動。如有疑問，請先在小樣本上嘗試您的方法，以建立對過程的信心。