在 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 函數
【延伸學習】