hello大家好，我是城鄉(xiāng)經(jīng)濟(jì)網(wǎng)小晟來(lái)為大家解答以上問(wèn)題，excel表格制作下拉菜單方法，Excel下拉菜單制作的小技巧）很多人還不知道，現(xiàn)在讓我們一起來(lái)看看吧！
在工作中，常常需要對(duì)多行多列的數(shù)據(jù)設(shè)置下拉菜單，但在使用數(shù)據(jù)驗(yàn)證，設(shè)置下拉菜單時(shí)，序列來(lái)源是不能執(zhí)行引用多行多列區(qū)域的，否則即會(huì)出現(xiàn)下圖提示框，應(yīng)該如何進(jìn)行設(shè)置呢？
第一步：定義名稱框
(資料圖片)
鼠標(biāo)依次點(diǎn)擊公式—定義的名稱—定義名稱，在打開(kāi)的窗口中，輸入名稱“城市”，再將引用位置設(shè)為“A2:A9”，之后使用鼠標(biāo)點(diǎn)擊確定。
第二步：設(shè)置下拉菜單
之后再選中區(qū)域，鼠標(biāo)點(diǎn)擊數(shù)據(jù)—數(shù)據(jù)驗(yàn)證，將驗(yàn)證條件設(shè)為“序列”，將序列來(lái)源設(shè)為“=城市”，鼠標(biāo)點(diǎn)擊確定即可。
第三步：修改定義名稱
鼠標(biāo)依次點(diǎn)擊公式—定義的名稱—名稱管理器，在打開(kāi)的窗口，將引用位置改為多行多列數(shù)據(jù)，如“A2:D9”，鼠標(biāo)點(diǎn)擊確定，即可完成多行多列下拉菜單的制作。
如果下拉列表太長(zhǎng)在下拉選擇的時(shí)候也是很遭嫌棄的。那我們就做個(gè)帶關(guān)鍵詞搜索的下拉列表吧！就像下圖這樣的：
有沒(méi)有覺(jué)得很高級(jí)？想知道怎么做的嗎？緊跟我的步伐，三步就能做出搜索式下拉菜單！
? 在 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è)公式，是怎么得出篩選列表的！
公式：
=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)鍵詞。
上述今天和大家分享的Excel下拉菜單的制作小技巧，非常實(shí)用哦！
解析：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)換為省份名稱。
解析：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ù)組公式，這里就不展開(kāi)啦~
? 點(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è)置下拉列表的單元格，點(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)」。
完成!
搜索式下拉列表和多級(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 的版本沒(méi)有太高要求，Office2007 以上的版本和 WPS 都可以使用。
PS：如果是 WPS2019 及以上的版本，則自帶「搜索式下拉菜單」~
本文就為大家講解到這里，希望對(duì)大家有所幫助。
標(biāo)簽：
