不知道诸位有没有在地里拔花生的经历，抓住花生的根茎往上一提，那根茎下的花生就带着一大块泥土和碎根须从地里钻劲忧商偌了出来。学习也有类似的情况。有时候学习一样东西，除了正儿八经地从头开始一步一步去学习的方法外，还有一种以点破面的快速学习方法：先在脑中构建一个大体的比较完整的大知识框架，然后抓着某一难点深钻下去，当遇到障碍时，回过头补充其他知识，然后再继续抓着这一点往下钻。这是一种另类的学习方法，对一个人的学习能力有一定的要求：在学习过程中头脑一定保持清醒，能理清其中的各个知识点的联系，以一点来贯通该事物的其他知识面。最忌讳东一下西一下地去学，捡了芝麻丢了西瓜，如果脑中不能时时刻刻有一个完整清晰的知识框架，那么以点破面的学习方法就是个灾难。所以在此尝试做一个EXCEL中的下拉菜单学习教程，来实践一下以点破面的学习方法。这些教程都是工作中的根据实际要求而来的，有很强的应用性。而且在深入学习下拉菜单的几个应用方法时，还能够串起一个函数公式的知识树。下拉菜单是那根茎下的花生，其他函数知识则是连带起来的泥土。下拉菜单只是EXCEL中的一种很实用的小功能，路径在【数据】-【数据验证】里面，office2010及之前的版本叫【数据有效性】，office2013和2016的版本叫【数据验证】。只是名字变了，前后版本的功能区别不大，对学习该教程没影响。不过建议最好在电脑上观看，因为里面的动图在电脑上可以看得更清楚些。这个教程主要有以下三个主要部分：（一） 1，下拉菜单是个啥 2，让下拉菜单自动更新数据（二） 3，二级联动下拉菜单 4，多级联动下拉菜单（三） 5，下拉菜单的模糊查询好了，接下来就是EXCEL下拉菜单的学习时间啦！全文六千余字，请耐心坚持学习下去哦~
工具/原料
Office Excel 2016/2013/2010
5、下拉菜单的模糊查询
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个新的知识点！ 这就是”以点破面“的学习方法。
