excel单元格下拉选项怎么设置_使用Excel制作搜索式下拉菜单，让你不再烦恼下拉选项多内容...

中岁颇好道，晚家南山陲。兴来每独往，胜事空自知。行到水穷处，坐看云起时。偶然值林叟，谈笑无还期。 --[唐] 王维《终南别业》

下拉菜单，相信大家一定不陌生。

当我们需要快速输入数据内容，或者防止数据录入错误，或者规范录入的格式时，我们常常使用【数据验证】制作下拉菜单来实现，比如如下数据，我们想根据人员姓名录入其性别，可以采用下拉框的方法来填充。

那如果我们涉及到的下拉选项的内容非常多，多到几十，那我们再进行使用下拉选择的时候，就会很困难。

那有没有办法去改善呢，当然是有的，今天就给大家分享一个操作技巧，制作搜索式下拉菜单。

可能大家听到会有疑问，什么是搜索式下拉菜单？

当我们在使用搜索引擎的时候，搜索部分内容，下面就会出现一部分类似内容，供你选择，而搜索式下拉菜单可以实现相同的效果。

先给大家看下演示图：

​下面开始我们今天的Excel知识分享。

操作步骤：

1、首先我们需要对我们的数据源进行排序，不管是升序还是降序都是可以的

2、选择D2:D5数据区域，点击菜单栏中的【数据】-【数据验证】，在【设置】里的【允许】框，选择【序列】。

3、在序列下的【来源】处，输入如下公式：

=OFFSET($A$1,MATCH(D2&"*",$A$2:$A$20,0),0,COUNTIF($A$2:$A$20,D2&"*"),1)

公式讲解：

这个公式的主要关键在于OFFSET函数的应用，首先我们先看下OFFSET函数的定义。

OFFSET(起始位置，向下或向上移动几行，向右或向左移动几行，引用区域的高度，引用区域的宽度)

拿个案例来讲下：

比如我们在E2输入公式：

=OFFSET(A1,3,3,1,1)

起始位置是A1，向下移动3行，向右移动3行，引用区域的高度1，引用区域的宽度1，

可以看到得到的值为9。

再比如把E2公式改成，然后拖动公式

=OFFSET(A1:B2,3,2,2,2)

起始位置为A1:B2,向下移动3行，向右移动2行，引用区域的高度2，引用区域的宽度2，即得到了C4:D5区域。

注意：公式输入完，因为引用的为数组，必须按Ctrl+Shift+Enter三键结束

这时我们再看上面的公式：

=OFFSET($A$1,MATCH(D2&"*",$A$2:$A$20,0),0,COUNTIF($A$2:$A$20,D2&"*"),1)

1、A1：代表的起始位置

2、MATCH(D2&"*",$A$2:$A$20,0)：

确定要向下移动几行，MATCH函数是查找函数，查找值D2&"*"在$A$2:$A$17处于第几行，0代表精确查找。其中查找值是和"*"，即通配符搭配使用的，可把包含D2关键字的所有内容显示，注意MATCH函数查找返回的是第一次出现的位置

3、0：代表向右移动0行，即列不移动

4、COUNTIF($A$2:$A$20,D2&"*")：

代表引用的区域高度，通过COUNTIF函数计算包含关键字D2的内容有几行，即下拉菜单显示的行内容数。

5、1:代表引用的宽度，因为我们只有1列，所以为数字1.

比如D2为"格力",我们拆解以上公式：

MATCH函数返回的值为5

COUNTIF函数返回的是4

最后公式变成了：

=OFFSET(A1,5,0,4,1)

即把A1单元格向下移动5行，向右移动0列，引用的高度为4，宽度为1，即返回了A6:A9区域，就是我们想要的结果了。

4、点击确定，即设置完成了，但是当我们再输入内容准备搜索的时候，会提示如下错误。

这是为什么呢？

主要是是因为，我们设置的下拉选项里面没有单独存在TCL这个选项，导致下拉异常，此时我们需要把数据验证里面的内容修改下，只需要把【数据验证】-【出错警告】里面的选项勾选掉即可。

当我们再输入内容进行选择的时候，就已经正确了。

最后针对销量内容，我们再使用VLOOKUP函数进行查找即完成了。

E2公式：

=IFERROR(VLOOKUP(D2,$A$2:$B$20,2,0),"")

VLOOKUP实现查找D2的内容，查找区域A2:B20，返回第2列，0实现精确查找。

再使用IFERROR函数，当查找不到内容时，以空值显示。

这样一整套下拉查询菜单就完成制作完成了，这个技巧的主要用到了OFFSET函数、MATCH函数、COUNTIF函数以及结合通配符"*"的使用，你学会了吗？不会的话赶紧去实际操作下吧。

如果觉得文章对你有帮助的话，希望大家帮忙点赞加分享哦~，谢谢

本文由彩虹Excel原创，欢迎关注，带你一起长知识！