在 Excel 中，如果要製作下拉式清單為了以選取方式輸入資料，通常透過「資料驗證」工具可以完成。但是如果可選取項目很多，就不是那麼方便了。
如何能製作隨輸入字元列出可選項目的下拉式清單？輸入文字愈多，顯示可選的項目會愈來愈少，這可以大大的縮小選取的時間。
本篇你將會學到：
1. 動態的下拉式清單製作方式
2. 資料驗證的運用
3. MATCH、COUNTIF、OFFSET、XLOOKUP等函數的使用
【設計與解析】
1. 選取儲存格B3:C100，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：姓名、居住地。
2. 找出第1筆姓林的位置
儲存格E4：=MATCH(G4&"*",姓名,0)
在 MATCH 函數中，尋找「G4&"*"」符合者，是利用萬用字元概念，讓 MATCH 函數找出姓林者的第一個位置。
請注意：圖中的姓名已依筆劃順序排序。
3. 找出共有幾筆姓林的資料
儲存格E6：=COUNTIF(姓名,$G$4&"*")
利用 COUNTIF 函數計算「$G$4&"*"」符合者的個數，這也是利用萬用字元概念，讓 COUNTIF 函數計算共有幾個姓林者。
4. 列出所有姓林的清單
儲存格E8：=OFFSET($B$3,E4,0,E6)
利用 OFFSET 函數代入第1筆姓林的位置和共有幾筆姓林的資料，得到一個儲存格範圍。
5. 合成公式並複製備用
=OFFSET($B$3,MATCH(G4&"*",姓名,0),0,COUNTIF(姓名,$G$4&"*"))
6. 定義名稱：清單
在「名稱管理員」中新增：
名稱：清單
參照到：=OFFSET($B$3,MATCH(G4&"*",姓名,0),0,COUNTIF(姓名,$G$4&"*"))
7. 選取儲存格G4，使用「資料／資料工具」的「資料驗證」。
(1) 在「設定」標籤下設定資料驗證準則。
儲存格內允許：清單
來源：=清單，此處的「清單」為前一個步驟定義的名稱。
(2) 在「錯誤提醒」標籤下取消勾選：輸入的資料不正確時顯示警訊。
8. 依姓名找出對應的居住地
儲存格H4：=XLOOKUP(G4,姓名,居住地)
利用 XLOOKUP 函數在姓名欄位中找尋和儲存格G4相同者，傳回其對應的居住地。
【如何使用】
使用時，輸入「林」，即會顯示所有姓林者的清單，而居住地欄位會因尚未比對到完全符合的姓名，所以傳回錯誤訊息。
再進一步輸入第二個字，符合的會減少。
最後可以點選想要的姓名：
練習改挑選不同姓氏和名字：
【參考資料】
XLOOKUP 函數參考微軟提供的說明網頁：XLOOKUP 函數
OFFSET 函數參考微軟提供的說明網頁：OFFSET 函數
【延伸學習】
留言列表