使用数据有效性制作动态可搜索的下拉菜单

在Excel中制作下拉菜单需要使用数据验证/数据有效性功能。当在单元格中输入关键字时，如何使下拉菜单中只出现包含关键字的项目呢？这就需要在设置数据验证时，引用的数据来源必须也是动态、可变化的。

本文接下来将介绍通过超级表、定义动态名称、函数等结合使用构造动态变化的数据源，并以此作为数据验证的引用来源，制作可搜索的下拉菜单。

1

效果展示

制作的可搜索下拉菜单效果如下图所示。在单元格中输入关键字后，下拉菜单的项目也会随之变化，只出现包含关键字的项目。

本文示例使用的数据如下图所示：

2

操作步骤

1、按【Ctrl+T】快捷键将“菜品”表格转为超级表。当增加新的菜品时，“点菜”工作表中的下拉菜单会自动更新菜品列表。

2、当在C2单元格输入关键字，使用公式从A列提取符合条件的菜品至E列。

公式构造过程如下：

在G:J列构造4列辅助列，“辅助列4”返回最终期望得到的结果。

（1）辅助列1的G2单元格公式：=ISNUMBER(SEARCH($C$2,A2))，该公式用于确认A2:A13中的菜品是否含有关键字。

（2）辅助列2的H2单元格公式：=IF(G2,ROW($A2)-1,"")，该公式返回含有关键字的菜品在A2:A13中的第几行。例如A5单元格含有关键字，其在A2:A13中的位置为第4行。

（3）辅助列3的I2单元格公式：=SMALL($H$2:$H$13,ROW($A1))，该公式用于将含有关键字的菜品所在行号排列在一起。

（4）辅助列4的J3单元格公式：=IFERROR(INDEX($A$2:$A$13,I2),"")，该公式用于从菜品清单中提取符合条件的菜品。

以上是提取包含关键字菜品的分步公式。

可以将上述公式组合为一个公式：

=IFERROR(INDEX($A$2:$A$13,SMALL(IF(ISNUMBER(SEARCH($C$2,$A$2:$A$13)),ROW($A$2:$A$13)-1,""),ROWS($E$2:E2))),"")，该公式为数组公式，按Ctrl+Shift+Enter完成公式输入。

3、为符合条件的菜品定义名称。当搜索的关键字变化时，符合条件的菜品数量也会变化，因此使用公式定义动态变化的名称。

单击【公式】-【定义名称】，打开【新建名称】对话框。

（1）【名称】框中输入定义的名称“符合条件的菜品”；

（2）引用位置输入公式：

=OFFSET(菜品!$E$2,0,0,COUNTIF(菜品!$E$2:$E$13,"?*"))

该公式返回“菜品”工作表中E2:E13单元格中的非空单元格区域。

4、设置数据有效性。选中“点菜”工作表需要设置下拉菜单的B2:B8单元格，单击【数据】-【数据验证】，打开数据验证对话框。

（1）【允许】选择序列；

（2）【来源】输入公式“=符合条件的菜品”；

（3）【出错警告】中取消勾选“输入无效数据时显示出错警告”，该项必须取消勾选。如果不取消勾选，输入关键字后会提示出错，无法出现包含关键字的下拉菜单。

5、在“菜品”工作表的C2单元格输入公式：=CELL("contents")

在C2单元格输入公式，按Enter键后，会出现如下提示，单击“确定”。

CELL函数可以返回单元格的指定信息，其语法为CELL(info_type,reference)。公式“=CELL("contents")”，“contents”指返回单元格的值，参数“reference”省略，表示返回最后更改的单元格的值。

如果只需要为一个单元格设置可搜索的下拉菜单，则无需使用CELL函数，只要将C2单元格值设置为等于设置下拉菜单的单元格值即可。