實用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去操作一下吧。

相關學習推薦：

以上就是實用Excel技巧分享：製作高效的搜尋式下拉式選單的詳細內容，更多請關注TW511.COM其它相關文章！