搜索式下拉菜单，制作详解

中年颇善道术，晚年回到南山边陲。当你快乐的时候，单干吧，你知道你的成功是徒劳的。到没有水的地方，坐下来看云起。偶尔拜访林叟，有说有笑良久。——【唐】王维《辞别中南》

我想大家一定对下拉菜单很熟悉了。

当我们需要快速输入数据内容或者防止数据录入错误或者规范输入格式时，我们经常会使用【数据验证】来创建一个下拉菜单来实现这一点，例如下面的数据中，如果我们想要根据名字输入一个人的性别，我们可以使用下拉框的方法。

如果我们涉及到的下拉选项的内容非常多，达到几十个，那么我们使用下拉选择就会非常困难。

有什么办法可以改善一下吗。

听到这里你可能会有疑问，什么是搜索下拉菜单？

当我们使用搜索引擎搜索某个内容时，下面会出现一部分类似的内容供你选择，搜索式的下拉菜单也能达到同样的效果。

先给大家看一下演示图：

让我们开始今天的Excel知识交流吧。

脚步：

1首先，我们需要对数据源进行排序，可以按升序或降序排列。

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

3在序列下方的[源]字段中，输入以下公式：

偏移($A$1,匹配(D2'*',$A$2:$A$20,0),0,COUNTIF($A$2:$A$20,D2'*'),1)

公式解释：

这个公式的关键是OFFSET函数的应用。首先，我们看一下OFFSET函数的定义。

OFFSET（起始位置、下移或上移几行、右移或左移几行、参考区域高度、参考区域宽度）

我们来看一个案例：

例如我们在E2中输入公式：

偏移量(A1,3,3,1,1)

起始位置为A1，下移3行，右移3行，参考区域高度为1，参考区域宽度为1，

可以看到得到的值为9。

又例如，将E2公式更改为，然后拖动公式

偏移量(A1:B2,3,2,2,2)

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

注意：输入公式后，由于引用的是数组，所以必须按CtrlShiftEnter才能完成。

现在我们再看一下上面的公式：

偏移($A$1,匹配(D2'*',$A$2:$A$20,0),0,COUNTIF($A$2:$A$20,D2'*'),1)

1A1：代表起始位置2MATCH(D2'*',$A$2:$A$20,0)：确定向下移动多少行MATCH函数是D2'*查找函数。'isin$A$2：$A$17in0中的哪一行表示精确搜索。搜索值与“*”即通配符一起使用，可以显示所有包含D2关键字的内容。请注意，MATCH函数返回位置3和0的第一次出现：这意味着移动0行。向右。即列不移动4COUNTIF($A$2:$A$20,D2'*')：表示包含关键字D2的内容引用区域的高度，即下拉菜单中显示的行数。51：代表引用的宽度因为我们只有1列，所以是数字1。

例如D2是“格力”，我们对上面的公式进行分解：

MATCH函数返回的值为5

COUNTIF函数返回4

最终公式变为：

偏移量(A1,5,0,4,1)

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

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

为什么这个？

主要原因是我们设置的下拉选项中没有单独存在TCL选项，导致下拉异常，此时我们需要更改【数据检查】-【错误警告】中数据检查中的内容]检查一下。

当我们输入要选择的内容时，就已经是正确的了。

最后，对于销售内容，我们使用VLOOKUP函数进行查找，就完成了。

公式E2：

IF错误(搜索(D2,$A$2:$B$20,2,0),'')

VLOOKUP实现查找D2的内容，查找范围A2:B20，并返回Column2,0实现精确搜索。

然后使用IFERROR函数在找不到内容时返回空值。

这样就完成了完整的查询下拉列表集。该技术主要使用OFFSET函数、MATCH函数、COUNTIF函数以及通配符“*”的使用。如果你不知道怎么做，那就赶快在实践中尝试一下吧。

如果您觉得文章对您有帮助，希望您喜欢并分享~，谢谢

本文由RainbowExcel原创，欢迎关注，帮助您一起了解更多！