输入关键字智能创建下拉菜单

马震安

客服人员每天要接到许多业务咨询信息，需要对客户名称、咨询问题等逐一进行记录，在Excel工作表中已经存放了一些客户全称的情况下，如果想在记录时输入客户的部分名称后，能够在形成的下拉菜单中显示出包含该部分名称的所有客户的全称供选择，同时还要支持下拉菜单动态更新（即当在存有客户全称表中添加新客户名称时，下拉菜单也随之更新）（图1），具有这样功能的工作表如何才能实现？下面是Excel 2016中的具体实现方法。

1. 实现下拉菜单动态扩展

在Excel工作表中，一般情况下制作下拉菜单需要选定相应单元格后，点击“数据”选项卡中的“数据验证”，在弹出窗口的“设置”选项卡中，“验证条件→允许”处选择“序列”，来源处选择相应单元格内容或输入所要显示的列表条目（列表条目用英文状态下的逗号隔开），这样制作的下拉菜单，当在所选单元格下增加新内容时，下拉列表并不会增加，这在实际使用中很不理想。要想使下拉菜单根据新增内容进行动态扩展，可以使用函数配合数据验证。

首先，点击“公式”选项卡中的“定义名称”，在弹出窗口的名称处输入“客户全称”，在引用位置处输入“=offset（客户名称！$A$2，，，counta（客户名称！$A：$A）-1）”；然后，再选定相应单元格（如D2：D27），点击“数据验证”，在“验证条件→允许”处选择“序列”，将光标点到来源处，按F3键，在弹出窗口选择“客户全称”。这样，当在A列增加或删除客户名称时，下拉列表也跟著动态更新（图2）。

2. 智能获取当前输入内容

一般情况下，在记录客户名称时，不输入客户全称，这就需要工作表能够及时捕捉到输入的内容是什么。接下来说明工作表是如何智能获取当前输入内容的。在工作表的F1单元格输入“=CELL（"contents"）”，回车后会出现警告提示，直接确定，F1单元格的值就会变成0。这时，在任意单元输入内容，F1单元格就会捕获到输入的内容（图4）。

3. 动态筛选提取数据

新建一个名称为“客户列表”的工作表，点击“公式”选项卡中的“名称管理器”，在弹出的窗口中点击“新建”，在新弹出窗口的名称处输入“客户列表”，引用位置处输入“=offset（客户列表！$A$2，，，countif（客户全称，"*"&cell（"contents"）&"*"））”；选定“客户列表”这个工作表的A2单元格，在公式编辑栏输入“=INDEX（客户名称！A：A，SMALL（IF（ISNUMBE R（FIND（CELL（"contents"），客户全称）），ROW（客户全称） ， 4 ^ 8） ， R O W （ A1））） &" "”，同时按下Crtl+Shift+Enter，当弹出警告提示时，直接确定即可；将数组公式填充到A80（可根据客户名称中的记录估算筛选结果进行填充），这样就能将根据输入关键字在“客户名称”工作表中筛选出的客户全称添加到“客户列表”的A2单元格及以下单元格中（图5）。

函数解释：

⑴ “=offset（客户列表！$A$2，，，countif（客户全称，"*"&cell（"contents"）&"*"））”，countif根据输入的内容在客户全称中统计符合包含输入内容的记录个数，offset是从客户列表中筛选出符合条件的记录；

⑵ “=INDEX（客户名称！A：A，SMALL（IF（ ISNUMBER（FIND（CELL（"contents"），客户全称）），ROW（客户全称），4^8），ROW（A1）））&""”，FIND是根据输入内容在客户全称中进行查找，如果找到就返回相应的位置，找不到就返回一个错误，为了兼容这个错误需要用ISNUMBER，即如果找到就返回TRUE，找不到就返回FALSE；ROW就是如果找到就返回到该记录相应的行；SMALL能够逐一提取找到的记录。

最后，在“来电记录”工作表中，选中“客户全称”这列，点击“数据”选项卡中的“数据验证”，在弹出窗口的“设置”选项卡中，“验证条件→允许”处选择“序列”，将光标点到来源处，按下F3键，在弹出的粘贴名称窗口中选择“客户列表”；在“出错警告”窗口中，去掉“输入无效数据时显示出错警告”前面的勾选（图6）。如此，就完成了整个智能化表格的制作。