大家好，我是繼續(xù)挖掘 Excel 各種技巧的小爽~

在工作中，我們經(jīng)常需要在 Excel 中填寫一些固定選項(xiàng)的數(shù)據(jù)。

對(duì)于「懂點(diǎn) Excel」的小伙伴來說，一般會(huì)選擇用【數(shù)據(jù)驗(yàn)證】的功能制作下拉列表。

不過一旦數(shù)據(jù)選項(xiàng)過多，用下拉列表選擇還是會(huì)顯得比較麻煩，手還很累。

這個(gè)時(shí)候，我們就急需用到搜索式的下拉列表，就像下圖這樣，用關(guān)鍵字進(jìn)行搜索：

之前我們有寫過一篇文章是用傳統(tǒng)做法做的搜索式下拉列表，不過那時(shí)嚇怕了一堆人（文章見文末~）。

吶，你看~

今天，我們就來介紹一下，用 Filter 函數(shù)如何去解決這類問題~

PS：Filter 函數(shù)需要 Office 365 版本，WPS2019 及以上的版本，則自帶「搜索式下拉菜單」

傳統(tǒng)方法需要 N 步，對(duì)吧？用 Filter 函數(shù)，我們只需要兩步，一起來看看吧！

創(chuàng)建搜索數(shù)據(jù)對(duì)應(yīng)的列表

這里我們來看一個(gè)案例，需要搜索有關(guān)「廣東」的所有數(shù)據(jù)。

只要在 E3 單元格中輸入公式：

• =FILTER($A$2:$A$401,ISNUMBER(FIND(C3,$A$2:$A$401)))
  

吶，關(guān)于「廣東」的數(shù)據(jù)，一下子就出來啦~

我們可以嘗試在 C3 單元格中輸入其他的省份。輸入搜索內(nèi)容，對(duì)應(yīng)的列表就一下子出來了～

公式看起來好復(fù)雜啊，不怕！接下來我們就來一步步拆解它。

（著急看下一步的同學(xué)，也可以直接滑到 02。）

公式：

• =FILTER($A$2:$A$401,ISNUMBER(FIND(C3,$A$2:$A$401)))

?? 公式解析：

在看函數(shù)公式前，我們先來看看公式涉及函數(shù)的基本語法~

???Find 函數(shù)基本語法：

Find 函數(shù)是用來返回一個(gè)字符串在另一個(gè)字符串中出現(xiàn)的起始位置（區(qū)分大小寫）；若找不到則返回錯(cuò)誤值。

• =FIND(find_text,within_text,[start_num])
  

• =FIND（查找值，在哪里找，[從第幾個(gè)字符開始查找]）

Isnumber 函數(shù)是用來判斷：引用的參數(shù)或指定單元格中的值是否為數(shù)字。是的話返回 True，否則返回 False。

???Filter 函數(shù)基本語法：

Filter 函數(shù)是一個(gè)篩選函數(shù)，可以將數(shù)組中條件為 True 的結(jié)果篩選出來。

• =FILTER(array,include,[if_empty])=FILTER(篩選區(qū)域,篩選條件,[是否忽略空值])
  

???公式套路：

• =FILTER(搜索區(qū)域,ISNUMBER(FIND(搜索內(nèi)容,搜索區(qū)域)))

PS：由于 Filter 函數(shù)不支持使用通配符，所以我們用 Find 和 Isnumber 函數(shù)來彌補(bǔ)這一特點(diǎn)。

比如說，我們要搜索 Excel 對(duì)應(yīng)的數(shù)據(jù)。

? 我們先用 Find 函數(shù)去查找搜索區(qū)域中有關(guān) Excel 的位置，如果沒有找到返回錯(cuò)誤值；

? 接著用 Isnumber 函數(shù)判斷是否是數(shù)值，是的話（也就是找得到）返回 True，否的話（也就是錯(cuò)誤值）返回 False；

? 最后利用 Filter 函數(shù)將篩選條件為 True 的篩選區(qū)域返回過來。

回到案例，我們直接套用公式，就可以搞定！

• =FILTER($A$2:$A$401,ISNUMBER(FIND(C3,$A$2:$A$401)))
  

設(shè)置數(shù)據(jù)驗(yàn)證

搜索列表已經(jīng)出來了，下一步就是要設(shè)置數(shù)據(jù)驗(yàn)證啦~

???具體操作：

? 選中 C3 單元格，選擇【數(shù)據(jù)】選項(xiàng)卡-點(diǎn)擊「數(shù)據(jù)驗(yàn)證」；

? 彈出數(shù)據(jù)驗(yàn)證對(duì)話框，「允許」選擇序列；「來源」選擇：$E$3#；

? 出錯(cuò)警告：取消勾選「輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告」，點(diǎn)擊【確定】。

???動(dòng)圖如下：

最后的效果~

看到這里或許有小伙伴疑惑了。

? 在數(shù)據(jù)驗(yàn)證，序列來源中，$E$3 單元格中后面為什么要加個(gè)#號(hào)，它是用來干嘛的？

? 為什么出錯(cuò)警告中，要取消勾選「輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告」？

下面我們就來一一解答～

Q1：$E$3 中后面的#是干什么的？

A：#符號(hào)稱為溢出的范圍運(yùn)算符，它是引用整個(gè)數(shù)組范圍的表示方式。

如下圖，我們直接等于索引整個(gè)區(qū)域，單元格中自動(dòng)會(huì)變成 E3#。

$E$3#可以自動(dòng)擴(kuò)選該單元格的數(shù)組區(qū)域，所以我們可以直接用在數(shù)據(jù)驗(yàn)證中的序列中。

Q2：取消勾選【輸入無效數(shù)據(jù)時(shí)顯示出錯(cuò)警告】。

A：由于我們輸入的數(shù)據(jù)跟序列中不一樣，所以為了防止出現(xiàn)錯(cuò)誤提醒，所以需要取消勾選。

不同單元格搜索式查找

前面我們只是針對(duì)一個(gè)單元格進(jìn)行搜索式查找，那如果是對(duì)于不同單元格呢，我們還需要一個(gè)個(gè)進(jìn)行設(shè)置嘛？

其實(shí)這個(gè)時(shí)候，我們只需要把原本搜索的單元格，改成 Cell("contents")就可以！

Cell 函數(shù)可以用來返回有關(guān)單元格的格式、位置或內(nèi)容的信息。

所以不難理解，Cell("contents")這一部分的作用，就是把當(dāng)前輸入的單元格直接作為 Find 函數(shù)的搜索值，也就是第一參數(shù)。

最終，我們?cè)?E3 單元格輸入如下公式：

• =FILTER($A$2:$A$401,ISNUMBER(FIND(CELL("contents"),$A$2:$A$401)))

???效果如下：

總結(jié)一下

本文介紹了用 Filter 函數(shù)制作搜索式下拉列表的做法：

? 通過?Filter，Isnumber 和 Find 函數(shù)進(jìn)行模糊搜索；

? #符號(hào)是溢出的范圍運(yùn)算符，單元格#可以自動(dòng)擴(kuò)選數(shù)組區(qū)域；

? Cell("contents")可以獲取當(dāng)前單元格的內(nèi)容。

文中的技巧還可以用在哪些地方？大家還想要知道哪些 Excel 小妙招？

歡迎在后臺(tái)與我留言，我們繼續(xù)來聊個(gè)兩毛錢的~也歡迎動(dòng)動(dòng)手點(diǎn)點(diǎn)贊~

最后，為了幫助職場(chǎng)的 Excel 困難戶提升工作效率，用數(shù)據(jù)分析技能、圖表思維提升競(jìng)爭(zhēng)力，咱們秋葉團(tuán)隊(duì)推出了《3天Excel集訓(xùn)營》課程！

大神帶你學(xué)習(xí)表格飛速排版、數(shù)據(jù)高效整理、圖表美化設(shè)計(jì)……??！和志同道合的小伙伴一起交流進(jìn)步~

秋葉《3 天 Excel 集訓(xùn)營》

課程原價(jià)?99?元?

但只要你是秋葉 Excel 的讀者

就能限時(shí)?1 元秒殺?。?/strong>

僅需 3 天

你就可能成為 Excel 高手！

趕緊掃碼搶課吧！！

優(yōu)惠名額有限，先到先得！

現(xiàn)在掃碼報(bào)名

還能免費(fèi)領(lǐng)《35 個(gè)函數(shù)使用手冊(cè)》！

??

*廣告