EXCEL中的下拉菜单（三）

1、【简 介】

前面我们可以看到，通过下拉菜单，我们可以不用输入数据，直接点击右侧的小三角符号就可以选择数据。but，但是，然而，however，如果下拉菜单引用的源数据太长、有几十行的话，我们点下拉菜单时看到的也是长长的一串，这在录入数据时不仅没有起到减少时间的作用，还会拉跨工作效率。

如果可以实现这样的功能：我们在单元格里只是输入几个关键字，这时点击右侧小三角就会自动出现所有包含该关键字的选项，那该有多方便啊！

只是越有效的功能，实现起来往往越复杂，这第五回的教程对于刚入门的新手来说有很大困难，如果有一定函数基础的话，那么学习起来就会很快的。

这里主要借用了【定义名称】、【offset函数】、【counta函数】、【countif函数】、【cell函数】、【find函数】、【isnumber函数】、【if条件判断函数】、【row函数】、【small函数】、【index函数】、【数组公式】、【数据验证】共计13个主要概念。

看起来密密麻麻一大串，其实搜一下，简单掌握每个函数的基本用法就可以了。你会发现，每个函数的功能都比较简单，但是组合在一起，就能够发挥出非常棒的效果。

2、【方 法】

① 定义源数据名称。

这一步所要做的，我们刚刚在第二回的下拉菜单自动更新那里说过了。我们先选中B列，即部门源数据所在的那一列，点击【公式】-【定义名称】，名称默认为“部门“，引用位置输入【=OFFSET(信游的侠!$B$2,,,COUNTA(信游的侠!$B:$B)-1,)】，点击确定。这样一个叫做”部门“的名称就被我们建立好了。

3、② 设立辅助列函数

另建一个新的工作表，A1输入【辅助列】（或其他任何名字，这个可以自动默认为你辅助列的名称），之后在A2单元格中输入：

 【=INDEX(信游的侠!B:B,SMALL(IF(ISNUMBER(FIND(CELL("contents"),部门)),ROW(部门),4^8),ROW(A1)))&""】

这是一个数组公式，输入完后【CTRL+Shift+Enter】三键一起按下去，就可以得到第一个满足条件的值，然后选中A2单元格，鼠标移到右下角，出现黑色小十字时，就直接按住鼠标左键，然后一直往下拖，拖到足够长的单元格为止。

这是为了防止出现你在源数据部门下面新增数据时，辅助列函数的行不够用的情况。

4、建立辅助列是个关键步骤，我们详细说下这个数组公式。不想看详解的话，可以忽略解释，直接跳到第③步骤~~

5、首先是运算的第一层函数【CELL(“contents”)】，这个函数能抓取到你在任意位置选中或输入的单元格内容。Cell函数的返回值是一段字符。你在下拉菜单区域输入模糊关键词时，函数就可以通过该 函数抓取关键词，从而知道应该找什么了。

6、第二层函数【FIND(CELL("contents"),部门)】，意为根据第一层抓取的关键词，在“部门”这个区域里面去查找。从这里开始，就是一个数组公式。

我们根据上图举个小小的例子。先定义C2：C7为一个叫“示例”的名称，在F1输入【=FIND("无",示例)】，然后【CTRL+SHIFT+ENTER】三键一起按。

这表示找有“无”这个关键字在“示例”区域中的位置，如果找到，返回值就是数字，如果没找到，返回值就是错误的了。我们用【F9】模拟运算的功能，看看find函数是怎么计算的：

7、先看函数编辑栏：这是我们F1单元格所输入的函数，表面看起来风平浪静。

我们选中“部门”，然后按【F9】模拟运算，哇，人群中突然冒出几个大光头！啊呸，突然冒出一大堆东西出来，这一大堆就是个数组，等下函数就要在这里面一个一个地去找“无”这个关键字有没有了。

8、这时选中所有的find函数，然后按【F9】模拟运算，我们可以看到运算结果是【{#VALUE!;2;#VALUE!;#VALUE!;#VALUE!;#VALUE!}】

注意，“示例”名称中总共有6个单元格，包含“无”这个关键字的恰好就在第二个“良无限事业部”里，所以那一排数组中第二个位子返回的是数值，而不是错误值#value. 

这时再看“无”这个字在单元格中左数第二个字符位置，所以返回值是2.

这就是find函数的绝妙用法。

9、第三层函数：

【ISNUMBER(FIND(CELL("contents"),部门))】，是用isnumber函数判断find算出来的是数字还是错误值。

如果在某一行中找到了要找的关键字，那么就会返回所在行的字符位置，这个位置就是一个数字，那么isnumber函数会判断为真，返回值为TRUE ；

如果find在某一行没找到关键字，那返回就是错误值#VALUE，这时isnumber函数就判断为假，返回值为FALSE。

10、第四层函数：

【IF(ISNUMBER(FIND(CELL("contents"),部门)),ROW(部门),4^8)】，这里是一个IF判断函数。通过前面isnumber函数的计算，返回值是TRUE和FALSE，那么对IF函数判断来说就简单了，是TRUE就返回值为ROW(部门)，是FALSE就返回4^8，即4的8次方(这个数值的计算结果是65536，这是03版本中单元格行数的最大值，写这个数字就是为了照顾老版本的OFFICE。新版的EXCEL可以轻松兼容，拥有104万行单元格)。

在IF眼里，看到的是例如这样的函数：【IF（{TRUE;FALSE;FALSE;TRUE},ROW(部门),4^8）】,那么IF计算出的结果就简单了：{1;65536;65536,4}，ROW函数是找有关键字的单元格是在哪一行。

11、第五层函数：

【SMALL(IF(ISNUMBER(FIND(CELL("contents"),部门)),ROW(部门),4^8),ROW(A1))】，

是利用SMALL函数给前面计算出的结果排个顺序，并把结果都展示在前面几行，这也方便了我们下拉菜单时，为什么符合所输入的关键字的选项总能出现在最前头，而不是间隔好多空行才出现包含关键字的情况。

我们以第四层举的例子来继续推演：

加入IF函数返回值是{1;65536;65536,4}，ROW(A1)此时返回值是1，此时在SMALL函数眼中，是这样子计算的：”SMALL({1;65536;65536,4},1)”，之后包含SMALL函数的单元格用公式刷往下面单元格刷下去，那个ROW(A1)就会变为ROW(A2)、ROW(A3)…，返回值就会成1,2,3……，意为计算SMALL函数中最小、倒数第二小、倒数第三小、倒数第N 小的数

我们给排排序，就是1,4,65536,65536，这样就可以把符合关键字的选项排到前头，不符合条件的直接给你排序到最后面去。

12、第六层函数：

【INDEX(信游的侠!B:B,SMALL(IF(ISNUMBER(FIND(CELL("contents"),部门)),ROW(部门),4^8),ROW(A1)))】，根据SMALL排好顺序的数字，直接匹配出数字所在行的内容。那么排在最后面的65536行，肯定返回的是空白值了，但是在单元格里，返回的却是0.

上面六层全部写完后，在公式后边在加上【&""】，这样子可以把返回的0值直接变成空白。最后按住【CTRL+SHIFT+ENTER】，运行该数组公式，然后用公式刷直接拉下去，把底下的单元格刷一刷。

这样第二步设立辅助列就全部完成啦！这是建立模糊查询下拉菜单最关键的步骤！

13、③ 定义辅助列名称建立下拉菜单

建立好辅助列之后，我们就可以给辅助列新定义一个名称，然后建立下拉菜单啦~

14、先选中辅助列A列，【公式】-【定义名称】，在引用位置那里输入：

【=OFFSET(辅助列!$A$2,,,COUNTIF(部门,"*"&CELL("contents")&"*"),)】。

“*“可以代表任意字段，&是连接符。这里COUNTIF函数就是计算在”部门“这个名称区域里，统计有多少个符合关键字。这样OFFSET函数就可以正确显示出应该显示的行数。

15、之后选中准备建立下拉菜单的M2：M11区域，点击【数据】-【数据验证】，验证条件选择“序列“，来源输入【=辅助列】或直接按【F3】选择”辅助列“这个名称。

在【出错警告】这里，不要勾选”输入无效数据时显示下列出错警告“这一项，之后点击确定。这一步是为了保证在下拉菜单中输入关键字的合法性。

16、【总 结】

经过以上的步骤，一个支持模糊查询的下拉菜单就做好啦！第二步建立辅助列那里，套用了一个非常经典的一对多查询公式组合【INDEX+SMALL+IF+ROW】，以后再写EXCEL教程时，肯定还会讲一下这个万金油公式。

【难度值：1500】

1、到了这里，所有（一）、（二）、（三）三篇文章就全部结束啦~~

我们再重头回顾一下整篇教程的学习历程，绘制一个简单的思维脑图：

每一个标注黄色部分的，都代表新开始学习的主要知识点。数一数的话，可以发现，为了拔出这一颗“花生“，为了实现想要的下拉菜单功能，我们竟然用到了至少17个新的知识点！ 这就是”以点破面“的学习方法。