小e為大家準(zhǔn)備了40 excel函數(shù)大戰(zhàn)

直接關(guān)注[秋葉excel]、[頭條]回答！

大家好，我是綠水零。

如果在工作過(guò)程中需要規(guī)范地收集具有固定選項(xiàng)的信息類(lèi)，建議使用“數(shù)據(jù)驗(yàn)證”創(chuàng)建下拉列表。

下拉列表不僅可以指定單元格輸入內(nèi)容，而且很快。請(qǐng)看下圖。不快！

然而，當(dāng)我使用下拉列表收集員工的戶(hù)籍信息時(shí)，竟然被推翻了！戶(hù)籍名單太長(zhǎng)，慘遭討厭！

所以扔掉我的表格后，同事們開(kāi)始填寫(xiě)“花紋”表格。其中光西城就可以這樣填滿(mǎn)。

廣西的同胞們是想惹我生氣嗎？還是笑我？

當(dāng)然，你們知道自己姓的正式名稱(chēng)的人不多。只知道廣西。

既然你們只記得關(guān)鍵詞，我就做一個(gè)有關(guān)鍵詞搜索的下拉列表！如下圖所示。

感覺(jué)高檔嗎~

你想知道怎么做嗎？跟著我的腳步走，可以分三個(gè)階段制作搜索式下拉菜單！

從關(guān)鍵字創(chuàng)建輔助列

在列a中創(chuàng)建完整的州列表。

要?jiǎng)?chuàng)建按關(guān)鍵字篩選的輔助列：

在單元格B2中填充以下公式，并使用[CTRL SHIFT ENTER]鍵結(jié)束公式，然后向下填充：

公式：

=iferror (index ($ a $2: $ a $35)，small(iferror(match(if(find)(cell(' contents))

上面的公式這么長(zhǎng)，是不是嚇到你了？

公式很難長(zhǎng)，但直接應(yīng)用就可以了。

應(yīng)用方法很簡(jiǎn)單。

因?yàn)楣街械乃膫€(gè)藍(lán)色部分完全相同，所以$A$2:$A$35是具有完整州列表的單元格。

所以，把藍(lán)色部分直接換成你要做的列表區(qū)域就可以了！

如果簡(jiǎn)單地應(yīng)用公式，就不能滿(mǎn)足你的求知欲。

而是想知道這個(gè)公式，是怎么得出篩選列表的！

FOLLOW ME！

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

下面要開(kāi)始高能套娃了，準(zhǔn)備好了嗎？

好的，我知道你們準(zhǔn)備好了！接著往下看吧！

公式：

=IFERROR(INDEX([完整列表區(qū)域],SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),[完整列表區(qū)域])>0,[完整列表區(qū)域],""),[完整列表區(qū)域],0),""),ROW(A1))),"")

公式解析：

理解很長(zhǎng)很長(zhǎng)的嵌套公式，最好是把它拆分出來(lái)逐步理解。

? CELL 函數(shù)

公式：

=CELL("contents")

使用這個(gè)公式可以獲取最后編輯的單元格內(nèi)容，就是我們要搜索的動(dòng)態(tài)關(guān)鍵詞。

為了更好理解，這里先不使用 CELL 函數(shù)，直接以搜索包含關(guān)鍵詞"北"為例，我們把公式拆分出來(lái)看看。

輔助列 1：

公式：

B3=FIND("北",A3,1)

目標(biāo)：判斷是否含有關(guān)鍵詞。

解析：FIND 函數(shù)的作用，是從 A3 單元格「河北省」的第 1 個(gè)字開(kāi)始查找字符串"北"字，找到后就返回「北」字的位置。

「河北省」的第 2 個(gè)字符是"北"所以 B3 單元格顯示 2，如果找不到關(guān)鍵詞則返回#VALUE!。

輔助列 2：

公式：

C3=IF(B3>0,A3,"")

目標(biāo)：將 FIND 的結(jié)果數(shù)字轉(zhuǎn)換為省份名稱(chēng)。

解析：IF 函數(shù)的作用是，判斷條件 B3 單元格 2 是否大于零。

如果是，顯示 A3「河北省」；如果不是，顯示空值「」。

到這里，我們其實(shí)已經(jīng)得到了含有關(guān)鍵詞「北」的省份列表（輔助列 2）。

但是，它不能直接作為下拉菜單的列表，因?yàn)檫€包含了很多#VALUE!。

所以，接下來(lái)我們要去掉錯(cuò)誤值，并且給含有關(guān)鍵詞「北」的省份列表重新排序。

輔助列 3：

公式：

D3=MATCH(C3,$A$3:$A$19,0)

目標(biāo)：根據(jù)省份名稱(chēng)，找到該原始列表的相對(duì)位置。

解析：MATCH 的作用是返回 C3 單元格「河北省」，在數(shù)組 A3 到 A19 單元格（即原始列表）中的相對(duì)位置，匹配方式是 0（即精確匹配）。

因?yàn)楹颖笔≡谑窃剂斜?A3:A19 的第一個(gè)值，所以結(jié)果為 1。

輔助列 4：

公式：

E3=IFERROR(D3,1048765)

目的：去除錯(cuò)誤值。

解析：因?yàn)檩o助列 5 使用 SMALL 函數(shù)進(jìn)行排順序，但是該函數(shù)不支持錯(cuò)誤值。

所以，這一步先使用 IFEERROR 函數(shù)將錯(cuò)誤值替換為空值""。

輔助列 5：

公式：

F3=SMALL($E$3:$E$19,ROW(A1))

目的：對(duì)列表進(jìn)行排序，使有關(guān)鍵詞的省份排在前面。

解析：ROW(A1)函數(shù)的作用是獲取單元格的行號(hào)，結(jié)果是 1，這里的作用是構(gòu)建一個(gè)隨行號(hào)遞增的數(shù)列 1、2、3……

SMALL 函數(shù)的作用是返回?cái)?shù)組 E3 至 E19 單元格（輔助列 4）中第 1 小的值，結(jié)果是 1。

輔助列 6：

公式：

G3=NDEX($A$3:$A$19,F3)

目的：根據(jù)相對(duì)行號(hào)找到對(duì)應(yīng)省份。

解析：INDEX 函數(shù)的作用是在數(shù)組 A3 至 A19 單元格（原始列表）中找到第 1（F3 單元格）個(gè)單元格的內(nèi)容，結(jié)果是河北省。

這一步也會(huì)有很多錯(cuò)誤值（#NUM!），同樣可以使用 IFERROR 將其替換為空值。

到輔助列 6 位置，我們已經(jīng)獲得了含關(guān)鍵詞的省份列表。

如果想要使用一列搞定的話(huà)，就是把套（函）娃（數(shù)）給組（嵌）裝（套）起來(lái)！

嵌套要將公式稍作改動(dòng)，改成數(shù)組公式，這里就不展開(kāi)啦~

定義輔助列名稱(chēng)

? 點(diǎn)擊【公式】選項(xiàng)卡-【名稱(chēng)管理器】-新建名稱(chēng)。

? 新建名稱(chēng)，名稱(chēng)區(qū)輸入「省份列表」，引用位置輸入公式：

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$35)-COUNTIF(Sheet1!$B$2:$B$35,""),1)

公式解析：

Sheet1!$B$2:$B$35 就是輔助列。

? COUNTA(Sheet1!$B$2:$B$35)

是獲取輔助列非空單元格的個(gè)數(shù)。盡管 B5 單元格的公式結(jié)果是空值「」，但是仍然屬于非空單元格。

? COUNTIF(Sheet1!$B$2:$B$35,"")

是獲取輔助列空值「」的個(gè)數(shù)。

? 使用 OFFSET 函數(shù)

構(gòu)建一個(gè)動(dòng)態(tài)的列表區(qū)域。以 B2 單元格為起點(diǎn)，向下偏移 0 個(gè)單元格，向下偏移 0 個(gè)單元格，長(zhǎng)度為②-①（即關(guān)鍵詞的匹配數(shù)），寬度是 1。

這里關(guān)鍵詞是「北」，匹配數(shù)是 3，所以整個(gè)公式得到的結(jié)果就是 B2 到 B4 這個(gè)區(qū)域。

根據(jù)關(guān)鍵詞，區(qū)域會(huì)動(dòng)態(tài)變化。

設(shè)置下拉列表

? 選中需要設(shè)置下拉列表的單元格，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡-【數(shù)據(jù)驗(yàn)證】-「數(shù)據(jù)驗(yàn)證」；

? 在驗(yàn)證條件對(duì)話(huà)框的允許中選擇「序列」，來(lái)源填寫(xiě)「=省份列表」；

? 點(diǎn)擊「出錯(cuò)警告」選項(xiàng)卡，取消勾選「輸入無(wú)效數(shù)據(jù)時(shí)顯示出錯(cuò)警告(S)」。

完成！

總結(jié)一下

? 搜索式下拉列表和多級(jí)下拉列表一樣，本質(zhì)都是利用輔助列，創(chuàng)建動(dòng)態(tài)的下拉選項(xiàng)。

? 需要注意的是，因?yàn)樵O(shè)置下拉列表時(shí)取消了出錯(cuò)警告，所以數(shù)據(jù)驗(yàn)證無(wú)法限制填寫(xiě)內(nèi)容。

? 本文使用的公式雖然很長(zhǎng)，但好處是對(duì) Excel 的版本沒(méi)有太高要求，Office2007 以上的版本和 WPS 都可以使用。

PS：如果是 WPS2019 及以上的版本，則自帶「搜索式下拉菜單」~

? 本文涉及的函數(shù)知識(shí)點(diǎn)相當(dāng)多，有興趣延伸學(xué)習(xí)的同學(xué)可以看看我們往期的文章。覺(jué)得腦殼疼的同學(xué)也可以直接復(fù)制公式。

小E為大家準(zhǔn)備了40+Excel函數(shù)大全

領(lǐng)取直接關(guān)注公棕號(hào)【秋葉Excel】，回復(fù)【頭條】！