微軟 excel 教學
Excel 自動帶入資料完整教學：VLOOKUP、XLOOKUP、INDEX-MATCH 一次學會【2026 更新】
文章目錄
Excel 自動帶入資料是什麼？一次搞懂 4 大方法
Excel 自動帶入資料，是指透過函數公式或內建功能，讓 Excel 根據條件自動從另一個表格、工作表或資料範圍中擷取對應資料，省去手動複製貼上的重複動作。常見方法包括 VLOOKUP、XLOOKUP、INDEX-MATCH 組合函數，以及快速填入（Flash Fill）功能。
根據 Microsoft 官方文件（截至 2026 年），Excel 365 與 Excel 2021 以上版本已全面支援 XLOOKUP 函數，大幅簡化了跨工作表自動帶入資料的操作流程。
| 方法 | 適用情境 | 難度 | 版本需求 |
|---|---|---|---|
| VLOOKUP | 單欄查找、資料在查找欄右側 | 初級 | 所有版本 |
| XLOOKUP | 雙向查找、多條件、取代 VLOOKUP | 中級 | Excel 365 / 2021+ |
| INDEX + MATCH | 靈活查找、資料在查找欄左側 | 中級 | 所有版本 |
| 快速填入（Flash Fill） | 文字拆分合併、簡單模式填充 | 初級 | Excel 2013+ |
VLOOKUP 自動帶入資料：最經典的查找函數
VLOOKUP（Vertical Lookup，垂直查找）是 Excel 中最廣泛使用的自動帶入資料函數，能根據一個查找值，從指定資料範圍中找到對應欄位的資料並回傳。據 Microsoft 統計，VLOOKUP 長年位居 Excel 使用頻率前 5 名的函數。
VLOOKUP 的語法結構如下：
=VLOOKUP(查找值, 資料範圍, 欄位索引, [精確/模糊比對])
- 查找值：您要用來搜尋的關鍵值（例如員工編號、學號）
- 資料範圍：包含查找值與目標資料的儲存格範圍
- 欄位索引：目標資料在範圍中的第幾欄（從 1 開始計算）
- 精確/模糊比對：填入 FALSE 為精確比對（建議預設使用），TRUE 為模糊比對
VLOOKUP 實戰範例：員工薪資查詢
假設工作表 1 有一份員工清單（A 欄為員工編號、B 欄為姓名、C 欄為部門、D 欄為薪資），您在工作表 2 的 A2 輸入員工編號，希望自動帶入姓名：
=VLOOKUP(A2, 工作表1!A:D, 2, FALSE)
這個公式會在工作表 1 的 A 欄中搜尋 A2 的值，找到後回傳同一列第 2 欄（姓名）的資料。將公式往右複製並調整欄位索引為 3、4，即可同時帶入部門與薪資。
實戰經驗：使用 VLOOKUP 時，最常踩的坑是「資料範圍沒有鎖定」。當您把公式往下拖曳複製時，如果沒有用 $ 符號鎖定範圍（例如
工作表1!$A:$D），範圍會跟著位移，導致查找結果錯誤。我在處理超過 500 筆員工資料時就遇過這個問題，排查了 30 分鐘才發現是範圍跑掉。建議一開始就養成鎖定範圍的習慣。
XLOOKUP 自動帶入資料：新一代查找函數完整教學
XLOOKUP 是 Microsoft 於 2019 年推出的新一代查找函數，能解決 VLOOKUP 的 3 大限制：只能向右查找、需要指定欄位索引、無法處理查找值不存在的情況。截至 2026 年，XLOOKUP 已成為 Excel 365 和 Excel 2021 以上版本的標準函數。
XLOOKUP 的語法結構：
=XLOOKUP(查找值, 查找範圍, 回傳範圍, [找不到時的值], [比對模式], [搜尋模式])
| 比較項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 查找方向 | 僅能向右查找 | 左右雙向皆可 |
| 欄位指定方式 | 數字索引（第幾欄） | 直接指定回傳範圍 |
| 預設比對模式 | 模糊比對（易出錯） | 精確比對（更安全） |
| 找不到值的處理 | 回傳 #N/A 錯誤 | 可自訂提示文字 |
| 多欄回傳 | 需逐欄寫公式 | 一個公式回傳多欄 |
| 版本支援 | 所有 Excel 版本 | Excel 365 / 2021+ |
XLOOKUP 實戰範例：跨工作表帶入學生資料
假設工作表 1 有學生名冊（A 欄學號、B 欄班級、C 欄座號、D 欄姓名、E 欄性別），您在工作表 2 的 C2 輸入學號，希望自動帶入班級到性別的所有欄位：
=XLOOKUP(C2, 工作表1!A:A, 工作表1!B:E, "查無此學號")
這個公式會一次回傳班級、座號、姓名、性別 4 個欄位的資料。如果學號不存在，會顯示「查無此學號」而非 #N/A 錯誤，大幅提升報表的可讀性。
INDEX + MATCH 組合：最靈活的自動帶入資料方法
INDEX + MATCH 組合函數是 Excel 進階使用者最推薦的查找方式，它不受查找方向限制，且在處理大量資料時效能優於 VLOOKUP。根據多位 Excel MVP（Most Valuable Professional，微軟最有價值專家）的建議，當資料量超過 10,000 列時，INDEX + MATCH 的運算速度比 VLOOKUP 快約 20% 至 30%。
語法結構：
=INDEX(回傳範圍, MATCH(查找值, 查找範圍, 0))
- INDEX 函數：從指定範圍中，根據列號回傳對應的值
- MATCH 函數：在指定範圍中搜尋查找值，回傳其所在的列號（第 3 個參數填 0 代表精確比對）
INDEX + MATCH 實戰範例：庫存查詢系統
假設 A 欄為產品名稱、B 欄為產品編號、C 欄為庫存數量。您想用產品名稱查詢庫存（查找欄在目標欄的右邊，VLOOKUP 無法做到）：
=INDEX(C:C, MATCH("滑鼠", A:A, 0))
MATCH 先在 A 欄找到「滑鼠」的列號，再由 INDEX 從 C 欄取出該列的庫存數量。這種組合不限查找方向，是專業使用者的首選方案。
快速填入（Flash Fill）：免公式的自動帶入技巧
快速填入（Flash Fill）是 Excel 2013 版本以後內建的智慧功能，能自動偵測您輸入的資料模式，並一鍵將模式套用到整欄，完全不需要寫任何公式。這項功能特別適合文字拆分、合併、格式轉換等場景。
啟用快速填入的方法：
- 在目標欄的第一個儲存格中，手動輸入您期望的結果
- 按下 Enter 後，移至下一個儲存格開始輸入，Excel 會自動顯示灰色預覽
- 按下 Enter 接受預覽，或前往「資料」>「快速填入」，或直接按 Ctrl + E
如果快速填入未自動觸發，請確認功能已開啟：前往「檔案」>「選項」>「進階」>「編輯選項」> 勾選「自動快速填入」。
快速填入常見應用場景
| 場景 | 原始資料（A 欄） | 期望結果（B 欄） | 操作方式 |
|---|---|---|---|
| 姓名拆分 | 王小明 | 王（取姓氏） | B1 輸入「王」，Ctrl+E |
| 合併欄位 | A 欄：王、B 欄：小明 | 王小明 | C1 輸入「王小明」，Ctrl+E |
| 格式轉換 | 2026-03-29 | 2026/03/29 | B1 輸入「2026/03/29」，Ctrl+E |
| 擷取數字 | 訂單編號A-1234 | 1234 | B1 輸入「1234」，Ctrl+E |
自動帶入資料的 5 個常見錯誤與解決方法
即使熟悉 VLOOKUP 和 XLOOKUP，在實際操作中仍容易遇到查找失敗或結果錯誤的情況。以下整理 5 個最常見的錯誤及對應解決方案：
- #N/A 錯誤（查找值不存在）：查找值在資料範圍中找不到。解決方法：使用 XLOOKUP 的第 4 個參數設定預設值，或用
=IFERROR(VLOOKUP(...), "查無資料")包裹公式。 - 資料前後有隱藏空格：肉眼看起來相同的文字，因為前後有空格導致比對失敗。解決方法：用
TRIM()函數清除多餘空格，例如=VLOOKUP(TRIM(A2), ...)。 - 資料範圍未鎖定（$ 符號）：公式往下拖曳複製時，資料範圍跟著位移。解決方法：在範圍前加上 $ 符號，例如
$A$1:$D$100。 - 數字與文字格式不一致：查找欄是文字格式的「001」，但查找值是數字 1，導致比對失敗。解決方法：統一格式，或用
TEXT()/VALUE()函數轉換。 - VLOOKUP 欄位索引超出範圍：指定的欄位索引數字大於資料範圍的總欄數。解決方法：確認資料範圍是否正確，或改用 XLOOKUP 直接指定回傳範圍。
Excel 自動帶入資料實戰操作指引
以下是一套完整的操作流程，幫助您從零開始建立自動帶入資料的工作表，適用於員工管理、學生名冊、庫存查詢等常見場景。
- 建立資料來源表：在工作表 1 中整理完整的資料清單（例如員工編號、姓名、部門、薪資），確保第一列有欄位標題，且查找鍵值（如員工編號）沒有重複值。
- 選擇適合的函數：如果您使用 Excel 365 或 2021 以上版本，優先使用 XLOOKUP；舊版本則使用 VLOOKUP（資料在右側）或 INDEX + MATCH（資料在左側或需要更高效能）。
- 撰寫查找公式：在工作表 2 的目標儲存格中輸入公式。以 XLOOKUP 為例：
=XLOOKUP(A2, 工作表1!$A:$A, 工作表1!$B:$B, "查無資料")，記得用 $ 鎖定資料範圍。 - 驗證公式結果：手動核對前 3 至 5 筆資料是否正確，特別注意是否有 #N/A 錯誤或空白結果。
- 複製公式到整欄：確認公式正確後，選取公式儲存格，雙擊右下角填滿控點（或按 Ctrl + D），將公式複製到所有需要的列。
- 加入錯誤處理：用 IFERROR 或 XLOOKUP 的預設值參數，將 #N/A 錯誤替換為「查無資料」或空白，讓報表更整潔。
Excel 自動帶入資料適合用哪個函數？
如果您使用 Excel 365 或 2021 以上版本，建議優先使用 XLOOKUP，語法更直覺、功能更完整。若使用舊版 Excel，VLOOKUP 是最普遍的選擇；當查找值在目標資料的右側時，則需要使用 INDEX + MATCH 組合。
VLOOKUP 和 XLOOKUP 哪個比較好？
XLOOKUP 在功能上全面優於 VLOOKUP：支援雙向查找、預設精確比對、可自訂錯誤提示、一個公式回傳多欄。唯一限制是需要 Excel 365 或 2021 以上版本。如果您的檔案需要與使用舊版 Excel 的同事共用，建議仍使用 VLOOKUP 以確保相容性。
為什麼 VLOOKUP 回傳 #N/A 錯誤？
最常見的 3 個原因：(1) 查找值在資料範圍中確實不存在；(2) 查找值與資料的格式不一致（例如文字格式的「001」vs 數字 1）；(3) 查找值或資料中有隱藏的前後空格。建議使用 TRIM() 清除空格，並用 IFERROR() 包裹公式處理例外情況。
Excel 快速填入（Flash Fill）如何開啟？
前往「檔案」>「選項」>「進階」>「編輯選項」，勾選「自動快速填入」即可。開啟後，在目標欄輸入第一筆期望結果，按 Enter 後 Excel 會自動顯示灰色預覽；或者直接按 Ctrl + E 手動觸發快速填入。此功能需要 Excel 2013 以上版本。
如何從另一個工作表自動帶入資料？
使用 VLOOKUP 或 XLOOKUP 時，在資料範圍中加上工作表名稱即可。例如 =VLOOKUP(A2, 工作表1!$A:$D, 2, FALSE)，其中「工作表1!」代表資料來源在「工作表1」這個工作表中。如果工作表名稱含有空格，需用單引號包裹，例如 '員工清單'!$A:$D。
INDEX + MATCH 和 VLOOKUP 有什麼差別？
INDEX + MATCH 的 3 大優勢：(1) 不限查找方向，可以向左查找；(2) 資料量大時運算速度更快（超過 10,000 列時約快 20%-30%）；(3) 插入或刪除欄位時公式不會出錯。缺點是語法較複雜，需要巢狀兩個函數。
自動帶入資料時如何避免格式不一致的問題？
確保查找值與資料來源的格式一致是關鍵。數字格式與文字格式混用是最常見的錯誤。可使用 VALUE() 將文字轉為數字，或 TEXT() 將數字轉為文字。另外，用 TRIM() 清除多餘空格、CLEAN() 清除不可見字元，都能有效避免比對失敗。