Excel制作可以根据关键字实时更新的下拉列表 方便快速录入数据

在Excel表格中通过数据验证制作下拉列表，方便快速录入数据是一个非常实用的功能。但是当下拉列表的内容很多时，制作出来的下拉列表会很长。如果要录入下方的信息还需要拖动滚动条，并不能起到快速录入信息的效果。这篇文章为读者朋友分享一个可以根据输入的关键字自动更新只包含关键字的下拉列表。

一.实例要求：

在下图中负责人下方的单元格区域需要制作一个人员名单的下拉列表，但是人员名单很长。

我们希望在单元格中输入人员的姓氏，就可以制作出一个所有该姓氏人员构成的下拉列表。例如，在单元格中输入“孙”，名字中就包含“孙”的人员就会自动更新制作一个下拉列表。

二.动态效果演示：

先给读者朋友上一张动态演示图，感受一下这个自动更新的下拉列表的强大。

三.制作过程：

1.通过定义名称制作一个可以更新的人员的名单。

（1）Ctrl+F3打开名称管理器→名称命名为：人员名单；范围选择工作簿；引用范围输入公式：=INDIRECT("信息表！$A2:$A$" & COUNTA(信息表！$A:$A))→确定

（2）通过定义名称制作的下拉列表可以在A列最后加入人员姓名后自动更新，如果直接引用单元格区域在加入人员名单之后需要修改公式。这里起到主要功能的是Counta函数。

（3）当然也可以直接在函数中引用上方的公式，但是那样公式会看起来非常长、不易理解。

2.在B列制作一个辅助列，用于列出包含关键字的所有人员的姓名。

（1）在B1单元格输入公式=INDEX($A:$A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),员工名单)),ROW(员工名单),10000),ROW(A1))) & ""

（2）公式解析：

①CELL("contents")返回的当前活动单元的内容。例如在D2（任意单元格都可以）单元格输入“赵”，那么返回的就是“赵”。

②ISNUMBER(FIND(CELL("contents"),员工名单))的功能是判断每一个人员姓名中是否包含关键字；包含返回True，否则返回False。最终构成一个由逻辑值构成的数组。

③IF(ISNUMBER(FIND(CELL("contents"),员工名单)),ROW(员工名单),10000)根据②由逻辑值构成的数组判断返回的内容。如果条件为true返回对应人员所在单元格的行，否则返回10000（也可以设置成其他较大的数）。

④接着用Small函数和row分别提取③构成的数组包含关键字人名所在的行。Small和row函数的搭配可以快速提取数组中第N个(row（）返回的行)最小值，不包含关键字的人员名单对应的数值已经设置为较大数10000。这是small函数一对多查找的典型用法。

⑤最后用INDEX函数提取④构成包含关键字的人员名单的单元格所在行数构成的数组对应位置的人员姓名。

（3）为了美观，可以将辅助列的字体颜色设置为白色，起到一个“隐藏”的效果。

3.使用公式制作根据辅助列内容实时更新的下拉列表。

（1）选择要设置下拉列表的单元格区域→数据→数据验证→设置→允许里选择序列→输入公式=INDIRECT("信息表！$B$1:$B$" & COUNTIF(员工名单，"*" & CELL("contents") & "*"))。

①COUNTIF(员工名单，"*" & CELL("contents") & "*")统计的是所有的员工名单里包含关键字的员工名单的个数。“*”是通配符，使用其与CELL("contents") 前后连接可匹配所有包含CELL("contents")的单元格。

②其他解释参考步骤2的公式解析。

四.完整操作动态过程演示：

总结，这个可以自动更新的下拉列表主要用到的还是small+if+index+row这个万金油组合函数，在配合CELL("contents")这个可以返回当前激活单元格内容的函数就构造了这个可以实时更新的下拉列表。