如何在Excel中向下/向右自動填充時轉置引用？

Author Sun Last modified 2025-08-23

在Excel中使用公式和自動填充時，預設行為是當你向右拖動填滿控制點時列引用會遞增，而當你垂直向下拖動時行引用會遞增。然而，有時候你可能需要轉置引用——例如，引用一個垂直範圍但水平填充，或者反之亦然。此操作在重新組織數據佈局或構建需要以轉置方式交叉引用數據的動態報表時特別有用。本文將探討實現這種轉置自動填充的各種實用方法，包括公式、VBA宏以及像Kutools這樣的便捷工具。請參見下圖了解典型的轉置自動填充場景：
transpose reference while auto fill right

在向下或向右填充時轉置引用

Excel公式 – 使用INDEX或OFFSET手動構建轉置引用

在向下或向右填充時轉置引用

在跨行或列填充時轉置引用，可以利用Excel中的TRANSPOSE函數。這種方法適用於當你想將排列在一列中的數據轉換成一行（或反之），公式會在自動填充時自動調整引用的方向。

選擇要應用轉置引用的範圍。在公式欄中輸入以下公式： =TRANSPOSE(Sheet2!$B$1:B12) 然後按下 Ctrl + Shift + Enter 同時按下（針對Microsoft 365 / Excel 2021之前的版本）。在支援動態陣列的新版本中，只需按下 Enter 即可。參見下面的截圖：
apply a formula to transpose reference while fill right

在此公式中， B1:B12 代表你想從垂直範圍轉置為水平排列的範圍。你可以調整工作表名稱和具體範圍以適應你的實際數據源。
提示： 為避免公式錯誤，確保轉置數據的目標範圍大小合適（例如，如果來源是一列12行，那麼目標範圍應該是一行12列）。

提示：如果你想快速將範圍轉換為單列或單行（或反之）多次，或者範圍很大且公式變得難以處理，你可以使用Kutools for Excel的轉換範圍工具高效完成這項操作。該工具提供了一個簡單的界面和靈活性來進行範圍轉換，最大程度減少公式錯誤或不匹配的風險。該功能可免費試用60天。請下載並免費試用。

convert a range to a single column or vice versa by kutools

Excel公式 – 使用INDEX或OFFSET手動構建轉置引用

有時候使用如TRANSPOSE這樣的陣列公式不是首選，或者你需要更多地控制引用的轉置方式，特別是在處理非連續數據或在舊版Excel中工作時。使用INDEX和OFFSET函數，你可以直接引用轉置數據並使用標準的填滿操作，無需按Ctrl + Shift + Enter。

適用場景：當你希望每個單元格有一一對應的轉置引用時首選，例如，垂直引用A1到水平引用B1（或反之）。對於避免陣列條目或在複雜工作表中進行動態引用很有用。

優勢： 不需要陣列輸入，公式簡單易於拖放填充。
潛在缺點： 初始公式的設置可能稍微複雜；密切注意如何調整行和列索引。

如何使用INDEX進行轉置引用：

1. 假設你的數據在A1:A5（垂直），並且你想將其轉置填滿至B1:F1（水平）。選擇單元格B1並輸入以下公式：

=INDEX($A$1:$A$5,COLUMN(A1))

2. 按Enter鍵，然後將公式向右拖動至單元格F1。該公式使用COLUMN(A1)在向右移動時遞增，因此引用A1, A2, ..., A5跨B1:F1。

如何使用OFFSET進行轉置引用：

1. 假設你的數據在B1:F1（水平），並且你想將其垂直向下填滿至G1:G5。在單元格G1中輸入：

=OFFSET($B$1,0,ROW(A1)-1)

2. 按Enter鍵，然後將公式向下拖動至G5。隨著向下填充，ROW(A1)-1將從0開始遞增，移動OFFSET函數的列索引以獲取B1, C1, D1等。

根據需要調整公式範圍或起始位置以匹配你的數據佈局。在複製或移動公式時，務必驗證正確的引用，以避免引用不匹配。

故障排除：如果你得到#REF!錯誤，請仔細檢查引用範圍，確保公式不會嘗試引用超出目標數據集的範圍。此外，對於大型數據集，這些公式可能會增加工作簿的計算負載。

總之，無論你選擇使用公式、VBA還是Excel外掛程式，都取決於你的數據結構、執行此操作的頻率以及你對高級Excel功能的熟悉程度。如果某種方法不適合你的需求，請嘗試上述其他方法。如果持續遇到公式或引用問題，請仔細檢查公式中的鎖定單元格引用（使用$符號），並根據需要進行調整以便在轉置時保持正確的方向性。

使用 Kutools AI 解鎖 Excel 的魔法

智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
自訂公式：生成量身定制的公式，簡化您的工作流程。
VBA 編碼：輕鬆編寫和實現 VBA 代碼。
公式解釋：輕鬆理解複雜的公式。
文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多