如何在 Excel 中向下或向右自動填滿時，讓參照自動轉置？

作者Sun修改日期2025-07-10

當您在 Excel 中使用公式與自動填滿功能時，預設行為如下：水平向右拖曳填滿控點時，欄位參照會遞增；垂直向下拖曳時，列參照則會遞增。然而，有時您可能需要轉置這些參照——例如，參照一個垂直範圍卻要水平填滿，或反過來操作。這類技巧在重新調整資料版面配置，或建立需以轉置方式交叉參照資料的動態報表時尤為實用。本文將介紹多種實現轉置自動填滿的實用方法，包括運用公式、VBA 巨集，以及 Kutools 等便利工具。請參閱下方插圖，了解典型的轉置自動填滿情境：
向右自動填滿時轉置參照

填滿向下或向右時轉置參照

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

填滿向下或向右時轉置參照

若要在跨列或跨欄填滿時轉置參照，請善用 Excel 中的 TRANSPOSE 函數！當您需要將直欄資料轉為橫列（或反之），並希望公式在自動填滿時自動調整參照方向，此方法絕對是您的最佳選擇。

選取您要套用轉置參照的範圍。在編輯欄中輸入下列公式：=TRANSPOSE(Sheet 2!$B$1:B12)，然後同時按下 Ctrl + Shift + Enter（適用於 Microsoft 365／Excel 2021 之前的版本）。在支援動態陣列的新版本中，只需按下 Enter 即可！詳情請參閱下方截圖：
套用公式以在向右填滿時轉置參照

在此公式中，B1:B12 代表您要將垂直範圍轉置為水平排列的資料區域。您可以根據實際來源區域，調整工作表名稱與對應的儲存格範圍。
提示：為避免公式錯誤，請務必確認轉置後的目的區域大小相符（例如：若來源為 1 欄 12 列，則目的區域應為 1 列 12 欄）。

提示。若您需要頻繁將範圍快速轉換為單一欄或列（或反之），或因範圍過大導致公式難以管理，不妨使用 Kutools for Excel 的「轉換區域」功能，高效完成此作業！此工具介面簡潔、操作彈性，能有效降低公式錯誤或參照不符的風險。該功能可免費完整試用 60 天。立即下載，免費試用！

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)，當您向右移動時自動遞增，從而在 B1:F1 中依序參照 A1、A2、……、A5.

如何使用 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 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

最佳 Office 生產力工具

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