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

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

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

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

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

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

吶，你看~

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

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

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

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

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

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

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

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

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

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

公式：

?? 公式解析：

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

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

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

• =FIND(find_text,within_text,[start_num]

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

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

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

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

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

???公式套路：

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

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

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

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

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

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

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

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

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

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

???具體操作：

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

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

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

???動圖如下：

最后的效果~

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

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

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

下面我們就來一一解答～

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

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

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

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

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

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

不同單元格搜索式查找

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

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

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

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

最終，我們在 E3 單元格輸入如下公式：

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

???效果如下：

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

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

? #符號是溢出的范圍運算符，單元格#可以自動擴選數(shù)組區(qū)域；

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

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

歡迎在后臺與我留言，我們繼續(xù)來聊個兩毛錢的~也歡迎動動手點點贊~

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

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

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

課程原價?99?元?

但只要你是秋葉 Excel 的讀者

就能限時?1 元秒殺?。?/strong>

僅需 3 天

你就可能成為 Excel 高手！

趕緊掃碼搶課吧??！

*廣告