實用Excel技巧分享：製作高效率的搜尋式下拉式選單

在先前的文章《實用Excel技巧分享：製作動態的甘特圖》中，我們學習了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。

相關學習推薦：excel教學

以上是實用Excel技巧分享：製作高效率的搜尋式下拉式選單的詳細內容。更多資訊請關注PHP中文網其他相關文章！