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

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

大家好，我是綠水零。

工作中，需要規(guī)范收集有固定選項(xiàng)的一類信息時(shí)，肯定首選用【數(shù)據(jù)驗(yàn)證】制作下拉列表。

下拉列表不僅可以規(guī)范單元格錄入的內(nèi)容，而且還快捷?？聪聢D，是不是很快！

可是，我在用下拉列表收集員工戶籍信息的時(shí)候，居然翻車了！！！ 因?yàn)閼艏斜硖L(zhǎng)，而慘遭嫌棄?。?/span>

于是，拋棄了我的表格之后，同事們就開始「花樣」填表。 其中，就一個(gè)廣西省，都可以填成這樣。

廣西的同胞們是想氣死我呢？還是笑死我？

敢情你們知道自己省份全稱的人不多呀，只知道是廣西。

既然你們只記得關(guān)鍵詞，那我就做個(gè)帶關(guān)鍵詞搜索的下拉列表吧！就像下圖這樣的：

有沒有覺得很高級(jí)~

想知道怎么做的嗎？緊跟我的步伐，三步就能做出搜索式下拉菜單！

根據(jù)關(guān)鍵詞創(chuàng)建輔助列

? 在 A 列填寫完整的省份列表；

? 創(chuàng)建根據(jù)關(guān)鍵詞篩選的輔助列：

將下列公式填入 B2 單元格，使用【CTRL+SHIFT+ENTER】組合鍵結(jié)束公式，向下填充。

公式：

=IFERROR(INDEX($A$2:$A$35,SMALL(IFERROR(MATCH(IF(FIND(CELL('contents'),$A$2:$A$35)>0,$A$2:$A$35,''),$A$2:$A$35,0),''),ROW(A1))),'')

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

公式雖然很長(zhǎng)很難，但直接套用即可。

套用方法很簡(jiǎn)單：

因?yàn)楣街?4 處標(biāo)藍(lán)的部分是完全一樣的：$A$2:$A$35，就是完整的省份列表所在單元格。

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

如果簡(jiǎn)單地套用公式，并不能滿足你的求知欲。

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

FOLLOW ME！

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

下面要開始高能套娃了，準(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è)字開始查找字符串'北'字，找到后就返回「北」字的位置。

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

輔助列 2：

公式：

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

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

解析：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ù)省份名稱，找到該原始列表的相對(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)鍵詞的省份列表。

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

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

定義輔助列名稱

? 點(diǎn)擊【公式】選項(xià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ì)話框的允許中選擇「序列」，來(lái)源填寫「=省份列表」；

? 點(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ú)法限制填寫內(nèi)容。

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

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