如何在 Excel 中貼上轉置後的資料，同時保留公式參照？

作者孫修改日期2025-07-15

在使用 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. 按一下全部取代，再點選確定與關閉，即可完成操作。您的公式已成功轉置，並完整保留原始範圍中的參照。
doc transpose keep reference6

此手動方法最適合小型資料集。若處理較複雜的範圍或混合參照樣式，務必再次檢查結果，確保公式能如預期重新計算。若您使用了命名範圍或外部參照，轉置後可能需要逐一檢視確認。

使用 Kutools for Excel 轉置並保留參照

若您經常需要轉置包含公式的資料，Kutools for Excel 提供更簡便的解決方案！其轉換儲存格參照工具可讓您在轉置前，快速將所有公式參照轉為絕對參照，確保轉置後原始參照維持不變，大幅降低手動調整與公式損壞的風險。

Kutools for Excel 提供超過 300 項進階功能，簡化複雜作業，提升創造力與效率。整合 AI 功能，Kutools 能精準自動化各項任務，讓資料管理輕鬆無負擔。Kutools for Excel 的詳細資訊…… 免費試用……

安裝 Kutools for Excel 後，請依照下列步驟操作：

1. 選取包含您要轉置之公式的儲存格，然後按一下 Kutools > 更多（位於公式群組中）> 轉換儲存格參照，即可開啟「參照轉換」對話方塊。
按一下 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！」錯誤或非預期數值，請檢查參照是否超出有效範圍，或在混合使用絕對與相對參照時是否產生偏移。如有疑慮，建議先於小範圍樣本中測試，以建立對該流程的信心。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多