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