Excel 下拉式選單自動帶出資料，是透過「資料驗證」建立下拉選單，再搭配 VLOOKUP、INDEX MATCH 或 XLOOKUP 等查找公式，讓你選擇 A 就自動帶出 B 的實用技巧。 本文完整教學從資料表設計、選單建立、三種公式應用到連動選單設定，涵蓋 Excel 2016 至 Microsoft 365 所有版本。

為什麼要用下拉式選單自動帶出資料？

以下三種情境是這項技巧最常見的應用場景：

• 產品查詢：業務開報價單時，要從上百個品項中找出正確的價格和規格——手動查找不但慢，還容易抄錯一個零
• 員工資料：人資要填寫員工異動表，每次都要翻另一張表找部門代碼和分機號碼——來回切換工作表浪費大量時間
• 客戶代號：客服接到電話，需要從客戶代號查出聯絡人和地址——如果輸入錯誤的代號，後續出貨就會寄錯地方

簡單來說，下拉式選單負責「讓使用者從清單中選擇」，查找公式負責「根據選擇的值去資料表中找到對應資料」。兩者搭配起來，就能實現「選擇 A 會自動帶出 B」的效果。

本文適用版本說明：

如果你不確定自己的版本，最簡單的方式是在任意儲存格輸入 =XLOOKUP(，如果出現函數提示就代表支援；如果顯示錯誤，就使用 VLOOKUP 或 INDEX MATCH 即可。

Step 1：建立資料來源表（正確設計是成功的一半）

資料來源表的結構決定了後續公式能不能正常運作。很多人公式寫對了卻得到錯誤結果，問題往往出在資料表設計。

資料表設計四原則

1. 每列一筆資料：一個產品、一位員工、一個客戶各佔一列，不要把多筆資料擠在同一列
2. 每欄一個屬性：名稱、價格、庫存、規格各自獨立一欄，不要在同一欄混合不同類型的資訊
3. 欄位名稱唯一且無空格：「產品名稱」和「產品 名稱」（中間有空格）在公式眼中是不同的東西
4. 獨立工作表存放：將資料來源放在 Sheet1（或命名為「資料庫」），操作介面放在 Sheet2，避免誤刪資料

以下是一個正確的產品資訊表範例：

建議將這個範圍轉換為 Excel 表格（選取資料後按 Ctrl+T），轉換後的 Table 有兩大優勢：新增資料時範圍自動擴展，以及可以用結構化參照（如 Table1[產品名稱]）取代傳統的儲存格範圍。

三種會讓公式失效的常見錯誤

錯誤一：資料表有合併儲存格。 VLOOKUP 和 XLOOKUP 無法正確讀取合併儲存格的值，會導致部分查詢回傳空白或 #N/A。解法：全選資料範圍 → 常用 → 合併與置中（取消合併）→ 用填滿功能補上空白儲存格的值。

錯誤二：欄位名稱前後有隱藏空格。 肉眼看不出來，但公式會判定為不同值。解法：對欄位名稱使用 =TRIM() 函數清除多餘空格。

錯誤三：資料與標題在同一列。 如果你的資料從 A1 開始（A1 是第一筆資料而非標題），後續設定命名範圍和 Table 時會出問題。永遠讓第一列是標題列。

Step 2：建立下拉式選單（資料驗證設定）

有了正確的資料來源表，接下來用資料驗證功能建立下拉式選單。

基本設定步驟

步驟一：選取目標儲存格。 切換到操作介面的工作表（例如 Sheet2），點選你要放下拉選單的儲存格（例如 A2）。

步驟二：開啟資料驗證。 點選功能區的「資料」索引標籤 → 找到「資料工具」群組 → 點選「資料驗證」。

步驟三：設定允許條件。 在「設定」索引標籤中，「允許」欄位選擇「清單」（部分版本顯示為「序列」）。

步驟四：指定來源範圍。 在「來源」欄位中輸入資料範圍。如果資料在 Sheet1 的 A2 到 A6，輸入 =Sheet1!$A$2:$A$6。如果已轉換為 Table，輸入 =Table1[產品名稱]。

步驟五：確認完成。 按下「確定」，點選該儲存格時就會出現下拉箭頭，點擊即可從清單中選擇。

💡 小技巧：如果要對多個儲存格設定同一個下拉選單（例如 A2 到 A20），先選取整個範圍再執行資料驗證，一次就能完成設定。

動態範圍設定（資料新增時選單自動擴展）

如果你的資料表會持續新增品項，靜態範圍（如 $A$2:$A$6）會導致新增的選項不出現在下拉選單中。

方法一：轉換為 Excel Table（推薦）

這是最簡單的方式。選取資料來源表的任意儲存格 → 按 Ctrl+T → 確認範圍正確 → 按確定。之後在資料驗證的來源欄位輸入：

=Table1[產品名稱]

當你在 Table 最後一列下方輸入新資料時，Table 會自動擴展，下拉選單也會同步更新。

方法二：命名範圍搭配 OFFSET（進階）

前往「公式」索引標籤 → 點選「名稱管理員」→ 新增 → 名稱輸入「產品清單」→ 參照到輸入：

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

然後在資料驗證的來源欄位輸入 =產品清單。

如何選擇？ 資料筆數固定不變 → 用靜態範圍最簡單。資料會持續新增 → 用 Table 方法最省事。需要跨活頁簿參照 → 用命名範圍。

防止輸入錯誤與允許自行輸入的設定

在資料驗證對話框中，切換到「錯誤提醒」索引標籤，這裡有三種模式：

如果你的需求是「Excel 下拉式選單自行輸入數字」——例如數量欄位有常用的 10、50、100 可選，但也允許輸入其他數字——就將錯誤提醒設為「警告」或「資訊」模式。

Step 3：用公式自動帶出對應資料

下拉選單建好後，接下來是最關鍵的一步：用查找公式讓 Excel 根據選單的值，自動從資料來源表中帶出對應的欄位資料。

VLOOKUP 函數（Excel 2016 以上通用）

VLOOKUP 是最經典的查找函數，語法如下：

=VLOOKUP(查詢值, 資料表範圍, 欄號, 精確比對)

實際範例： 假設 Sheet2 的 A2 是下拉選單（選擇產品名稱），要在 B2 帶出單價：

=VLOOKUP(A2, Sheet1!$A$2:$E$6, 2, FALSE)

• A2：查詢值（下拉選單選到的產品名稱）
• Sheet1!$A$2:$E$6：資料表範圍（用絕對參照 $ 鎖定，方便複製公式）
• 2：要帶出第 2 欄的值（單價）
• FALSE：精確比對（務必設為 FALSE，否則可能回傳錯誤結果）

帶出多欄資料： 如果要同時帶出單價、庫存、規格、供應商，只需要改變欄號：

一定要加 IFERROR。 當 A2 還沒選擇任何值（空白）時，VLOOKUP 會顯示 #N/A 錯誤，整張表看起來一片紅色錯誤訊息。用 IFERROR 包起來就能避免：

=IFERROR(VLOOKUP(A2, Sheet1!$A$2:$E$6, 2, FALSE), "")

第二個參數 "" 代表找不到時顯示空白，你也可以改成 "請選擇產品" 之類的提示文字。

VLOOKUP 的限制： 查詢值必須在資料表範圍的第一欄。如果你的資料表是「單價→產品名稱→庫存」的順序，想用單價查產品名稱，VLOOKUP 就做不到——這時候需要 INDEX MATCH。

INDEX MATCH 組合（支援左側查找，版本通用）

INDEX MATCH 是兩個函數的組合，比 VLOOKUP 更靈活：

=INDEX(要帶出的欄位範圍, MATCH(查詢值, 查詢欄位範圍, 0))

• MATCH 負責找出查詢值在查詢欄位中的「位置」（第幾列）
• INDEX 負責根據這個位置，從目標欄位中取出對應的值

範例： 同樣是 A2 選產品名稱、B2 帶出單價：

=INDEX(Sheet1!$B$2:$B$6, MATCH(A2, Sheet1!$A$2:$A$6, 0))

INDEX MATCH vs VLOOKUP 差異對比：

左側查找範例： 假設資料表的欄位順序是「規格→產品名稱→單價」，你想用產品名稱（第二欄）查出規格（第一欄）：

=INDEX(Sheet1!$A$2:$A$6, MATCH(A2, Sheet1!$B$2:$B$6, 0))

VLOOKUP 做不到這件事，因為查詢值不在第一欄。

XLOOKUP（Microsoft 365 / Excel 2021 以上）

XLOOKUP 是微軟推出的新一代查找函數，語法更直覺，一個函數就能取代 VLOOKUP 和 INDEX MATCH 的大部分功能：

=XLOOKUP(查詢值, 查詢範圍, 帶出範圍, 找不到時顯示)

單欄帶出範例：

=XLOOKUP(A2, Sheet1!$A$2:$A$6, Sheet1!$B$2:$B$6, "查無資料")

不需要記欄號、不需要指定精確比對、找不到時的預設值直接寫在第四個參數——比 VLOOKUP 加 IFERROR 的寫法簡潔很多。

一次帶出多欄（陣列語法）： 這是 XLOOKUP 最強大的功能。只要將「帶出範圍」設為多欄，就能一條公式帶出所有欄位：

=XLOOKUP(A2, Sheet1!$A$2:$A$6, Sheet1!$B$2:$E$6, "查無資料")

在 B2 輸入這條公式後，單價、庫存、規格、供應商會自動溢出到 C2、D2、E2——不需要寫四條公式。

如何確認你的 Excel 是否支援 XLOOKUP？ 在任意空白儲存格輸入 =XLOOKUP(，如果出現函數提示和參數說明，就代表支援。如果顯示 #NAME? 錯誤，代表你的版本不支援，請改用 VLOOKUP 或 INDEX MATCH。

實務案例：業務報價單設定方式

以下是一個典型的業務報價單設定流程，適用於產品品項多、需要頻繁開立報價單的情境：

設定方式：

1. Sheet1（資料庫）存放所有產品資訊，轉換為 Table（命名為「產品資料」）
2. Sheet2（報價單）的 A 欄設定下拉選單，來源為 =產品資料[產品名稱]
3. B2 輸入 =IFERROR(XLOOKUP(A2, 產品資料[產品名稱], 產品資料[單價]), "")（或用 VLOOKUP 版本）
4. 將 B2 的公式向右複製到 C2、D2、E2，分別帶出庫存、規格、最低訂購量
5. F2 加入數量輸入欄，G2 用 Excel 公式 =IF(B2="","",B2*F2) 計算小計

設定完成後，業務只需要從下拉選單選產品、輸入數量，報價單就自動完成，不需要手動查找產品資料。

Step 4：建立連動下拉選單（選 A 自動更新 B 的選項）

當你的資料有層級關係時——例如「類別→產品」、「部門→員工」、「地區→縣市」——就需要連動下拉選單：第一層選了「飲料」，第二層只顯示飲料類的產品，而不是所有產品。

兩層連動選單設定

任務一：設計資料來源結構

在 Sheet1 建立以下格式的資料：

A 欄是所有類別的清單，B、C、D 欄分別列出各類別下的產品。

任務二：為每個類別建立命名範圍

前往「公式」索引標籤 → 點選「名稱管理員」→ 新增：

• 名稱：飲料，參照到：=Sheet1!$B$2:$B$4
• 名稱：食品，參照到：=Sheet1!$C$2:$C$4
• 名稱：日用品，參照到：=Sheet1!$D$2:$D$4

⚠️ 關鍵注意事項：命名範圍的名稱必須與 A 欄的類別名稱完全一致。 如果 A 欄寫「飲料」，命名範圍也必須叫「飲料」，不能有空格、不能有全形半形差異。

任務三：設定第一層下拉選單

在 Sheet2 的 A2 設定資料驗證：

• 允許：清單
• 來源：=Sheet1!$A$2:$A$4（類別清單）

任務四：理解 INDIRECT 函數的邏輯

INDIRECT 函數的作用是「把文字轉換成儲存格參照」。當 A2 選了「飲料」，INDIRECT(A2) 就等於 INDIRECT("飲料")，Excel 會去找名稱為「飲料」的命名範圍，回傳該範圍的內容。

任務五：設定第二層下拉選單

在 Sheet2 的 B2 設定資料驗證：

• 允許：清單
• 來源：=INDIRECT(A2)

現在當 A2 選「飲料」時，B2 的下拉選單只會顯示可樂、果汁、礦泉水；選「食品」時，只會顯示餅乾、麵包、泡麵。

常見錯誤排查： 如果第二層選單顯示空白或錯誤，90% 的原因是命名範圍名稱與第一層選單的值不一致。檢查方式：到「名稱管理員」確認每個命名範圍的名稱，再對照第一層選單的來源值，確保完全相同。

三層連動延伸（地區→縣市→鄉鎮）

三層連動的邏輯與兩層完全相同，只是多加一層 INDIRECT：

1. 第一層：地區（北部、中部、南部）
2. 第二層：=INDIRECT(A2) → 根據地區顯示縣市
3. 第三層：=INDIRECT(B2) → 根據縣市顯示鄉鎮

命名範圍命名規則： 每個縣市都需要一個命名範圍，名稱就是縣市名稱。例如命名範圍「台北市」對應的範圍是台北市下的所有行政區。如果名稱包含特殊字元（如「台北市」的「市」），通常不會有問題，但如果包含空格或符號，建議改用底線替代。

連動選單搭配自動帶出資料： 連動選單也可以搭配 VLOOKUP 或 XLOOKUP 使用。例如在「部門→員工」的連動選單中，第一層選部門、第二層選員工後，再用查找公式自動帶出該員工的編號、職稱、分機等資訊，減少手動填寫的欄位數量。

實務應用：下拉選單搭配篩選與條件格式

下拉選單不只能帶出單筆資料，還能搭配其他功能做更進階的應用。

下拉選單 + FILTER 函數（動態篩選多筆資料）

如果你需要的不是「查一筆」而是「篩選出所有符合條件的資料」——例如選擇「飲料」類別後，列出所有飲料產品——可以使用 FILTER 函數（需要 Microsoft 365 或 Excel 2021）：

=FILTER(Sheet1!A2:E100, Sheet1!F2:F100=A2, "無符合資料")

這條公式會根據 A2 下拉選單選擇的類別，從資料表中篩選出所有該類別的產品，並動態顯示在指定範圍。比傳統的自動篩選（AutoFilter）更靈活，因為結果會即時更新，不需要手動操作篩選按鈕。

這就是「Excel 下拉式選單篩選」的核心做法。

下拉選單 + 條件式格式設定（自動標色提醒）

搭配條件式格式設定，可以讓帶出的資料自動標色。例如：

• 庫存數量低於 10 → 儲存格背景變紅色
• 庫存數量介於 10-30 → 儲存格背景變黃色
• 庫存數量高於 30 → 儲存格背景變綠色

設定方式：選取庫存欄位 → 常用 → 條件式格式設定 → 新增規則 → 選擇「使用公式來決定要格式化哪些儲存格」→ 輸入 =C2<10 → 設定紅色背景。

這樣業務在選擇產品時，一眼就能看出庫存是否充足，不需要額外判斷。搭配 Excel IF 函數，還能在庫存不足時自動顯示「需補貨」的提示文字。

當 Excel 不夠用時：團隊協作的下一步

Excel 的下拉選單搭配查找公式，對個人使用來說已經非常強大。但當以下情況出現時，代表你可能需要更專業的工具：

• 多人同時編輯衝突：兩個業務同時開啟同一份報價單，存檔時互相覆蓋對方的修改
• 需要權限控管：不希望業務看到成本欄位，但 Excel 的工作表保護很容易被破解
• 需要自動通知：庫存低於安全水位時，希望自動發通知給採購——Excel 做不到
• 版本管理困難：多人協作時難以追蹤誰改了什麼，容易出現檔案版本混亂的問題

如果你的團隊正面臨這些問題，以下兩個工具可以考慮：

monday.com 的自訂欄位功能可以建立類似下拉選單帶出資料的邏輯，並且內建即時協作、自動化通知和權限控管。操作方式是透過視覺化介面設定欄位關聯，不需要撰寫公式。

ClickUp 也提供類似的自訂欄位和關聯功能，特別適合技術導向的團隊。它的免費方案功能就很完整，如果你的團隊習慣用更細緻的任務管理方式，值得試試。

怎麼選？

• 5 人以下、需求單純 → 繼續用 Excel 就好，本文教的技巧已經夠用
• 5-15 人跨部門協作 → monday.com 是我們團隊的首選，免費方案不需要信用卡
• 技術團隊、需要高度客製化 → ClickUp 的彈性更大

結論

回顧本文的核心重點：

• 資料表設計是基礎：每列一筆、每欄一屬性、欄位名稱唯一、獨立工作表存放。資料表有問題，後面的公式再正確也沒用
• 三種查找公式各有適用場景：VLOOKUP 最簡單（查詢值在第一欄）、INDEX MATCH 最靈活（支援左側查找）、XLOOKUP 最簡潔（需要 Excel 2021 以上）
• 動態範圍用 Table 最省事：按 Ctrl+T 轉換為 Excel Table，新增資料時下拉選單自動擴展
• 連動選單靠 INDIRECT + 命名範圍：命名範圍名稱必須與第一層選單值完全一致，這是最常見的出錯點
• IFERROR 不能省：沒有它，空白儲存格會顯示一片 #N/A，影響報表的專業度

你的下一步行動： 打開 Excel，建立一個 5 筆資料的產品資訊表，按照 Step 1 到 Step 3 的步驟，實際做一次「選產品名稱→自動帶出價格和庫存」的設定。

如果你的團隊已經開始出現多人協作的需求，不妨試試 monday.com 的免費方案——用自訂欄位建立產品查詢看板，全團隊即時同步，不需要來回傳送檔案。

Excel 下拉式選單自動帶出資料常見問題

下拉選單無法顯示或選項不見怎麼辦？

最常見的原因有三個：(1) 資料驗證的來源範圍輸入錯誤，例如工作表名稱拼錯或範圍不正確；(2) 來源範圍中有空白列，導致選單只顯示到空白列為止；(3) 資料驗證被意外清除——選取儲存格後到「資料→資料驗證」確認設定是否還在。如果下拉式選單設定都正確但箭頭不見，檢查「檔案→選項→進階」中的「顯示儲存格下拉式清單」是否被取消勾選。

公式出現 #N/A 錯誤怎麼處理？

N/A 代表查找公式找不到對應的值。最常見的原因是查詢值前後有多餘的空格——肉眼看不出來，但公式會判定為不同值。解法：在公式中加入 TRIM 函數清除空格，例如 =VLOOKUP(TRIM(A2), Sheet1!$A$2:$E$6, 2, FALSE)。另外也要確認大小寫是否一致（VLOOKUP 不區分大小寫，但 MATCH 的精確比對模式會區分）。

公式出現 #REF! 錯誤是什麼意思？

REF! 代表公式參照的儲存格或範圍已經不存在。通常是因為你刪除了資料表中的某些列或欄，導致公式的範圍參照失效。解法：重新檢查公式中的範圍是否正確，必要時重新指定。使用 Excel Table 的結構化參照（如 Table1[產品名稱]）可以大幅降低這個問題的發生機率。

下拉選單選項無法自動擴展怎麼辦？

如果你用靜態範圍（如 $A$2:$A$6）設定來源，新增的資料不會自動出現在選單中。最簡單的解法是將資料來源轉換為 Excel Table（Ctrl+T），然後將資料驗證的來源改為 =Table1[產品名稱]。Table 會自動擴展範圍，下拉選單也會同步更新。

XLOOKUP 顯示「找不到此函數」怎麼辦？

這代表你的 Excel 版本不支援 XLOOKUP。XLOOKUP 僅在 Excel 2021 和 Microsoft 365 中可用。如果你使用 Excel 2016 或 2019，請改用 VLOOKUP 或 INDEX MATCH，功能完全相同，只是語法不同。本文三種公式都有教學，選擇你的版本支援的即可。

Google Sheets 如何做同樣的事？

Google Sheets 的操作邏輯與 Excel 相同，但介面路徑略有不同。資料驗證的路徑是「資料→資料驗證→條件→下拉式選單（從範圍）」。查找公式方面，Google Sheets 支援 VLOOKUP、INDEX MATCH，也支援 XLOOKUP（需確認你的 Google Sheets 版本）。INDIRECT 連動選單的邏輯完全相同，命名範圍在「資料→已命名範圍」中設定。