在之前的文章《實(shí)用Excel技巧分享：制作動(dòng)態(tài)的甘特圖》中，我們學(xué)習(xí)了Excel制作動(dòng)態(tài)甘特圖的方法。今天我們來(lái)聊聊Excel數(shù)據(jù)下拉菜單，介紹一下制作高效搜索式下拉菜單的方法，快來(lái)看看！

手機(jī)如何做表格：點(diǎn)擊查看

工作中大家經(jīng)常會(huì)用Excel數(shù)據(jù)驗(yàn)證制作下拉菜單，規(guī)范數(shù)據(jù)輸入、節(jié)省數(shù)據(jù)輸入時(shí)間。但是當(dāng)下拉菜單的數(shù)據(jù)選項(xiàng)很多的時(shí)候，就會(huì)出現(xiàn)數(shù)據(jù)難找的困擾。比如下圖，下拉菜單中的數(shù)據(jù)選項(xiàng)太多，通過(guò)拖動(dòng)旁邊的滾動(dòng)條來(lái)“找出”需要的數(shù)據(jù)項(xiàng)很費(fèi)時(shí)，直接降低了我們的工作效率。

那有沒(méi)有辦法解決這種選項(xiàng)多數(shù)據(jù)難找的問(wèn)題呢？

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

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

下圖是我們本次教程的數(shù)據(jù)源。注意，必須對(duì)數(shù)據(jù)源按關(guān)鍵字排序，升序降序都可以。

選擇E2:E6單元格區(qū)域點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡，單擊【數(shù)據(jù)驗(yàn)證】，在彈出的“數(shù)據(jù)驗(yàn)證”對(duì)話框的“設(shè)置”選卡中設(shè)置驗(yàn)證條件為“序列”。

在來(lái)源中輸入公式：

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

公式說(shuō)明：

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

OFFSET（參照系，行偏移量，列偏移量，新引用區(qū)域的行數(shù)，新引用區(qū)域的列數(shù)）

• 1.第一參數(shù)引用了A1單元格作為參照系。

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

• 3.第三參數(shù)為0，因?yàn)槲覀兊臄?shù)據(jù)源只有A列一列，所以列偏移量為0，表示不偏移。簡(jiǎn)單來(lái)說(shuō)，就是offset函數(shù)以A1單元格為參照，不橫向偏移，只向下偏移。

• 4.第四參數(shù) COUNTIF($A$2:$A$17,E2&"*")統(tǒng)計(jì)A2-A17區(qū)域內(nèi)滿足條件E2&"*"，也就是包含E2單元格內(nèi)的關(guān)鍵詞的單元格出現(xiàn)的次數(shù)，也就是最終在數(shù)據(jù)驗(yàn)證下拉菜單中一共會(huì)出現(xiàn)幾行。

• 5.第五參數(shù)為新引用區(qū)域的列數(shù)，因?yàn)橹挥蠥列一列，所以為1。

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

再回到教程當(dāng)中。我們?cè)凇皵?shù)據(jù)驗(yàn)證”對(duì)話框“來(lái)源”中輸入公式后點(diǎn)擊“確定”。但是當(dāng)我們?cè)贓2單元格輸入關(guān)鍵字“碎花”后，會(huì)立馬彈出警告框，這是為什么呢？

原因是我們輸入關(guān)鍵字“碎花”后，由公式得到的下拉菜單中沒(méi)有只含“碎花”兩個(gè)字的選項(xiàng)，所以會(huì)報(bào)錯(cuò)。

如下圖所示，我們需要再次選擇E2:E6單元格區(qū)域點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡【數(shù)據(jù)驗(yàn)證】按鈕進(jìn)入到“數(shù)據(jù)驗(yàn)證”對(duì)話框中，在“出錯(cuò)警告”選卡中取消勾選【輸入無(wú)效數(shù)據(jù)時(shí)顯示出錯(cuò)警告】選項(xiàng)，然后點(diǎn)擊“確定”即可。

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

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

搜索式下拉菜單可以成倍提高數(shù)據(jù)錄入效率，尤其是下拉菜單選項(xiàng)很多的時(shí)候特別高效。同學(xué)，趕緊打開(kāi)你的excel去操作一下吧。

相關(guān)學(xué)習(xí)推薦：excel教程