第二个下拉列表的值根据第一个变化_输入的关键字创建智能的下拉菜单

Excel表格屋学堂

在Excel单元格中输入特定范围的内容时，一般会利用数据有效性生成下拉列表的方式进行，但因下拉单内容太多，通过数据有效性设置的下拉单选择数据时非常难找，如果能够通过模糊查询，最快找到所需要的数据，即在下拉单内输入任意一个字，就能显示包含这个字的下拉单列表，然后通过鼠标选择即可完成，将非常方便。

请看下图效果

操作步骤

第1步、我们要保证A列姓名是经过筛选的，将同样的姓氏排列在一起，可以从A2开始框选A列的姓名，并点击【数据】-【排序】功能

第2步、选中E2单元格，点击【数据】-【数据有效性】

第3步、选择【序列】

第4步、再序列内输入公式：

=OFFSET($A$1,MATCH(E2&"*",$A:$A,0)-1,,COUNTIF($A:$A,E2&"*"),)

公式的大致意思是，通过match函数在A列找到第一个模糊输入的关键词，用countif函数找出此关键词有几个，最后用offset函数从A1开始向下偏移match找到的起始位置，框选countif找到的个数。

PS：以上三个函数若使用不熟练，可加入表格屋学堂课程精确学习，A2套餐为函数基础的敲门砖，有兴趣可看本篇文章结尾介绍。

最后点击确认，完成。如果需要同步其它单元格，直接复制即可。

以上方法针对相同姓名排列在一块，如果不在一块，就需要利用辅助列、CELL函数来完成。

操作步骤

第1步、在名单所在工作表中插入一个辅助列，在辅助列的第一个单元格中输入 =INDEX(A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),A$2:A$13)),ROW($2:$13),4^8),ROW(A1)))&""

然后，同时按下Ctrl、Shift、Enter，使得输入的内容变成数组公式。此时会弹出一个循环提示，不用管，直接点击确认。

第2步、然后向下填充（填充多少取决于同姓氏有多少人，可以尽量多点）

小提示：

公式中CELL("contents")省略了第二参数，直接获得最后更改单元格的值；FIND(CELL("contents"),B$2:B$13)是查询CELL("contents")的结果是否在B2:B13单元格区域存在，如果存在则返回一个位置数值，如果不存在则返回错误值；IF(ISNUMBER(FIND(CELL("contents"),B$2:B$13)),ROW($2:$13),4^8)中，ISNUMBER函数判断FIND函数的结果是否为数值，如果为数值，则返回相关值所对应的行号，如果不是数值，则返回值4^8；SMALL函数对IF函数的结果进行从小到大取数，随着公式的向下填充，依次提取第1、2、3、4、5……N个最小值，依次得到包含最后更改单元格值的单元格的行号；INDEX函数根据SMALL函数返回的索引值，得出结果。

接下来，选取需要设置下拉菜单的单元格区域，比如D2:D13；

第3步、选中E2单元格，选择【数据】选项卡，点击【数据有效性】-【数据有效性】”，在弹出窗口“设置”选项卡中选择【序列】，输入$D$2:$D$13

第4步、选择【输入信息】选项卡，将提示的勾选去掉

第5步、再选择【出错警告】选项卡，将提示的勾选去掉

最后点击确定，完成。

辅助列可以隐藏，最后看效果

有有兴趣学习的朋友可以加WX liuou5201314