hello大家好，我是城乡经济网小晟来为大家解答以上问题，excel表格制作下拉菜单方法，Excel下拉菜单制作的小技巧）很多人还不知道，现在让我们一起来看看吧！
在工作中，常常需要对多行多列的数据设置下拉菜单，但在使用数据验证，设置下拉菜单时，序列来源是不能执行引用多行多列区域的，否则即会出现下图提示框，应该如何进行设置呢？
第一步：定义名称框
(资料图片)
鼠标依次点击公式—定义的名称—定义名称，在打开的窗口中，输入名称“城市”，再将引用位置设为“A2:A9”，之后使用鼠标点击确定。
第二步：设置下拉菜单
之后再选中区域，鼠标点击数据—数据验证，将验证条件设为“序列”，将序列来源设为“=城市”，鼠标点击确定即可。
第三步：修改定义名称
鼠标依次点击公式—定义的名称—名称管理器，在打开的窗口，将引用位置改为多行多列数据，如“A2:D9”，鼠标点击确定，即可完成多行多列下拉菜单的制作。
如果下拉列表太长在下拉选择的时候也是很遭嫌弃的。那我们就做个带关键词搜索的下拉列表吧！就像下图这样的：
有没有觉得很高级？想知道怎么做的吗？紧跟我的步伐，三步就能做出搜索式下拉菜单！
❶ 在 A 列填写完整的省份列表；
❷ 创建根据关键词筛选的辅助列：
将下列公式填入 B2 单元格，使用【CTRL SHIFT ENTER】组合键结束公式，向下填充。
公式：
=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))),"")
上面的公式这么长，是不是把你吓到了？公式虽然很长很难，但直接套用即可。
套用方法很简单：
因为公式中 4 处标蓝的部分是完全一样的：$A$2:$A$35，就是完整的省份列表所在单元格。
所以，直接把标蓝的部分换成你要做的列表区域就可以了！
如果简单的套用公式，并不能满足你的求知欲。而是想知道这个公式，是怎么得出筛选列表的！
公式：
=IFERROR(INDEX([完整列表区域],SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),[完整列表区域])>0,[完整列表区域],""),[完整列表区域],0),""),ROW(A1))),"")
公式解析：
理解很长很长的嵌套公式，最好是把它拆分出来逐步理解。
❶ CELL 函数
公式：=CELL("contents")
使用这个公式可以获取最后编辑的单元格内容，就是我们要搜索的动态关键词。
为了更好理解，这里先不使用 CELL 函数，直接以搜索包含关键词"北"为例，我们把公式拆分出来看看。
辅助列 1：
公式：B3=FIND("北",A3,1)
目标：判断是否含有关键词。
上述今天和大家分享的Excel下拉菜单的制作小技巧，非常实用哦！
解析：FIND 函数的作用，是从 A3 单元格「河北省」的第 1 个字开始查找字符串"北"字，找到后就返回「北」字的位置。
「河北省」的第 2 个字符是"北"所以 B3 单元格显示 2，如果找不到关键词则返回#VALUE!。
辅助列 2：
公式：C3=IF(B3>0,A3,"")
目标：将 FIND 的结果数字转换为省份名称。
解析：IF 函数的作用是，判断条件 B3 单元格 2 是否大于零。
如果是，显示 A3「河北省」；如果不是，显示空值「」。
到这里，我们其实已经得到了含有关键词「北」的省份列表（辅助列 2）。
但是，它不能直接作为下拉菜单的列表，因为还包含了很多#VALUE!。
所以，接下来我们要去掉错误值，并且给含有关键词「北」的省份列表重新排序。
辅助列 3：
公式：D3=MATCH(C3,$A$3:$A$19,0)
目标：根据省份名称，找到该原始列表的相对位置。
解析：MATCH 的作用是返回 C3 单元格「河北省」，在数组 A3 到 A19 单元格（即原始列表）中的相对位置，匹配方式是 0（即精确匹配）。
因为河北省在是原始列表 A3:A19 的第一个值，所以结果为 1。
辅助列 4：
公式：E3=IFERROR(D3,1048765)
目的：去除错误值。
解析：因为辅助列 5 使用 SMALL 函数进行排顺序，但是该函数不支持错误值。
所以，这一步先使用 IFEERROR 函数将错误值替换为空值""。
辅助列 5：
公式：F3=SMALL($E$3:$E$19,ROW(A1))
目的：对列表进行排序，使有关键词的省份排
解析：ROW(A1)函数的作用是获取单元格的行号，结果是 1，这里的作用是构建一个随行号递增的数列 1、2、3……
SMALL 函数的作用是返回数组 E3 至 E19 单元格（辅助列 4）中第 1 小的值，结果是 1。
辅助列 6：
公式：G3=NDEX($A$3:$A$19,F3)
目的：根据相对行号找到对应省份。
解析：INDEX 函数的作用是在数组 A3 至 A19 单元格（原始列表）中找到第 1（F3 单元格）个单元格的内容，结果是河北省。
这一步也会有很多错误值（#NUM!），同样可以使用 IFERROR 将其替换为空值。
到辅助列 6 位置，我们已经获得了含关键词的省份列表。
如果想要使用一列搞定的话，就是把套（函）娃（数）给组（嵌）装（套）起来！
嵌套要将公式稍作改动，改成数组公式，这里就不展开啦~
❶ 点击【公式】选项卡-【名称管理器】-新建名称。
❷ 新建名称，名称区输入「省份列表」，引用位置输入公式：
=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)是获取辅助列非空单元格的个数。尽管 B5 单元格的公式结果是空值「」，但是仍然属于非空单元格。
② COUNTIF(Sheet1!$B$2:$B$35,"")是获取辅助列空值「」的个数。
③ 使用 OFFSET 函数构建一个动态的列表区域。以 B2 单元格为起点，向下偏移 0 个单元格，向下偏移 0 个单元格，长度为②-①（即关键词的匹配数），宽度是 1。
这里关键词是「北」，匹配数是 3，所以整个公式得到的结果就是 B2 到 B4 这个区域。
根据关键词，区域会动态变化。
❶ 选中需要设置下拉列表的单元格，点击【数据】选项卡-【数据验证】-「数据验证」；
❷ 在验证条件对话框的允许中选择「序列」，来源填写「=省份列表」；
❸ 点击「出错警告」选项卡，取消勾选「输入无效数据时显示出错警告(S)」。
完成!
搜索式下拉列表和多级下拉列表一样，本质都是利用辅助列，创建动态的下拉选项。
需要注意的是，因为设置下拉列表时取消了出错警告，所以数据验证无法限制填写内容。
本文使用的公式虽然很长，但好处是对 Excel 的版本没有太高要求，Office2007 以上的版本和 WPS 都可以使用。
PS：如果是 WPS2019 及以上的版本，则自带「搜索式下拉菜单」~
本文就为大家讲解到这里，希望对大家有所帮助。
标签：
