下拉式選單是EXCEL中一個超級方便的功能，可以簡化工作上重複輸入資料的動作，並且讓資料數據更加統一，但是如果今天選項太多了那麼下拉式選單就就必須花很多時間去尋找。
就像下圖一樣，下拉式選單有100個姓名，那麼就要花很多時間去找，根本考驗視力
資料太多，下拉式選單很不實用
那如果真的需要下拉式選單，可是選項又這麼多該怎麼辦呢?其實可以用函數設定成，輸入第一個字，下拉式選單就自動會顯示符合第一個字所有的資料，這樣資料很多的時候也不用找半天了，你的眼睛也會感謝你哦😏
輸入第一個字，自動列出符合的內容
方法一、手動排序法
- 先將下拉式選單的資料來源排序(由A到Z或Z到A都可以)
- 選取要設定下拉式選單的範圍
- 資料-->資料驗證-->清單
- 輸入=OFFSET($A$1,MATCH($D1&"*",A:A,0)-1,,COUNTIF(A:A,$D1&"*"),)
函數說明:
- [公式1]-->MATCH($D1&"*",A:A,0) : 找到D1儲存格，輸入姓氏是在A欄第幾個儲存格
- [公式2]-->COUNTIF(A:A,$D1&"*") : 計算A欄有多少儲存格，是D1輸入的姓氏
- OFFSET($A$1,[公式1]-1,,[公式2],) : 從A1儲存格開始往下位移[公式1]-1個儲存格，並且包含[公式2]的儲存格數量
設定好之後要記得到資料驗證視窗中的錯誤提醒，把輸入的資料不正確時警訊打勾取消，這樣才能夠正常使用哦
方法二:函數排序法(全版本通用)
假設A欄是原始的資料清單，使用輔助欄將原始的資料清單依照第一個字進行排序，這樣才能順利製作模糊查詢下拉式選單
- 輔助欄B1=CODE(A1)+ROW()*0.00001
- 輔助欄C1=RANK.EQ(B1,$B$1:$B$100,1)
- 輔助欄D1=INDEX(A:A,MATCH(ROW(A1),C:C,0))
- 資料驗證=OFFSET($D$1,MATCH($F1&"*",D:D,0)-1,,COUNTIF(D:D,$F1&"*"),)
函數說明
- B1=CODE(A1)+ROW()0.00001 : 利用CODE函數取出每個儲存格第一個字的代碼，加上列號*0.00001(一個極小值)，使所以數據不重複
- C1=RANK.EQ(B1,$B$1:$B$100,1) : 利用RANK.EQ函數將B欄抓出來的代碼進行數字進行名次的排序
- D1=INDEX(A:A,MATCH(ROW(A1),C:C,0)) : 利用MATCH配合ROW函數將C欄的排序小到大的位置找出來，在使用INDEX配合MATCH找出來的位置將姓氏的第一個字相同的排序再一起
- 資料驗證的公式同方法一
與方法一一樣，設定好之後要記得到資料驗證視窗中的錯誤提醒，把輸入的資料不正確時警訊打勾取消，這樣才能夠正常使用哦
方法三:函數排序法(365版)
如果你的版本比較新，有SORT函數，那麼恭喜你輔助欄只需要短短的一欄就可以了🤩
- 輔助欄B1=SORT(A:A,1,1)
- 資料驗證=OFFSET($B$1,MATCH($D1&"*",B:B,0)-1,,COUNTIF(B:B,$D1&"*"),)
函數說明
- B1=SORT(A:A,1,1) : 利用SORT函數直接將A欄的原始資料進行排序，SORT函數在365版本的EXCEL才可以使用
- 資料驗證的公式同方法一
365也是一樣的，設定好之後要記得到資料驗證視窗中的錯誤提醒，把輸入的資料不正確時警訊打勾取消，這樣才能夠正常使用哦
看文字圖片還不夠清楚的話，來看一下影片吧，看教學影片之前可以先下載練習檔，學中做、做中學效果更好哦。
覺得分享有幫助到你在工作上提升效率📈
可以追蹤我的方格子或請我喝杯咖啡☕
也可以訂閱EXCEL設計新思維👓
可以看到更多增加效率的好方法唷~
職場上有相關問題，加入我的line社群討論唷~
追蹤我的IG
追蹤我的粉絲團
追蹤我的Dcard
