實用Excel技巧分享：制作高效的搜索式下拉菜單（Excel下拉菜單實現搜索功能）

手機如何做表格：點擊查看

工作中大家經常會用Excel數據驗證制作下拉菜單，規范數據輸入、節省數據輸入時間。但是當下拉菜單的數據選項很多的時候，就會出現數據難找的困擾。比如下圖，下拉菜單中的數據選項太多，通過拖動旁邊的滾動條來“找出”需要的數據項很費時，直接降低了我們的工作效率。

那有沒有辦法解決這種選項多數據難找的問題呢？

有，我的方法就是搜索式下拉菜單！

就如同在百度上搜索，輸入關鍵字后會彈出下拉菜單顯示包含關鍵字的搜索題目供選擇。我們要做的效果就是在單元格中輸入關鍵字，然后點擊下拉菜單，菜單中只顯示包含關鍵字的數據，從而提高數據錄入效率。

下圖是我們本次教程的數據源。注意，必須對數據源按關鍵字排序，升序降序都可以。

選擇E2:E6單元格區域點擊【數據】選項卡，單擊【數據驗證】，在彈出的“數據驗證”對話框的“設置”選卡中設置驗證條件為“序列”。

在來源中輸入公式：

=OFFSET($A$1,MATCH(E2&"*",$A$2:$A$17,0),0,COUNTIF($A$2:$A$17,E2&"*"),1)

公式說明：

這里主要使用OFFSET函數返回包含關鍵字的數據。OFFSET函數的作用是根據指定的參照單元格，通過給定的偏移量返回新的引用數據。

OFFSET（參照系，行偏移量，列偏移量，新引用區域的行數，新引用區域的列數）

1.第一參數引用了A1單元格作為參照系。

2.第二參數用MATCH(E2&"*",$A$2:$A$17,0)確定行偏移量。MATCH為查找函數，根據查找值E2&”*”(*號是通配符，代表任意不確定字符)在$A$2:$A$17區域中查找，查找方式為0（精確查找）。當E2單元格輸入關鍵字時，該函數將查找出包含關鍵字的數據在$A$2:$A$17區域中第一次出現的位置。

3.第三參數為0，因為我們的數據源只有A列一列，所以列偏移量為0，表示不偏移。簡單來說，就是offset函數以A1單元格為參照，不橫向偏移，只向下偏移。

4.第四參數 COUNTIF($A$2:$A$17,E2&"*")統計A2-A17區域內滿足條件E2&"*"，也就是包含E2單元格內的關鍵詞的單元格出現的次數，也就是最終在數據驗證下拉菜單中一共會出現幾行。

5.第五參數為新引用區域的列數，因為只有A列一列，所以為1。

如下所示：offset函數以A1為參照系向下查找，通過match函數在A2-A17中找到包含E2關鍵字“碎花”的數據第一次出現的位置是從A2開始的第10行，再通過countif函數找到總共有3行，最終在下拉菜單中返回這3行1列的數據。

再回到教程當中。我們在“數據驗證”對話框“來源”中輸入公式后點擊“確定”。但是當我們在E2單元格輸入關鍵字“碎花”后，會立馬彈出警告框，這是為什么呢？

原因是我們輸入關鍵字“碎花”后，由公式得到的下拉菜單中沒有只含“碎花”兩個字的選項，所以會報錯。

如下圖所示，我們需要再次選擇E2:E6單元格區域點擊【數據】選項卡【數據驗證】按鈕進入到“數據驗證”對話框中，在“出錯警告”選卡中取消勾選【輸入無效數據時顯示出錯警告】選項，然后點擊“確定”即可。

最后在F2單元格輸入公式=IFERROR(VLOOKUP(E2,$A$2:$B$17,2,0),"") 。使用VLOOKUP函數在A2-B17單元格區域中查找E2數值所在位置，并返回對應的第2列（也就是B列）庫存，0代表精確查找。當查找不到返回錯誤值時用IFERROR函數將錯誤值轉為空。

至此，搜索式下拉菜單就制作完成啦！

搜索式下拉菜單可以成倍提高數據錄入效率，尤其是下拉菜單選項很多的時候特別高效。同學，趕緊打開你的excel去操作一下吧。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。