在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单元格值设置为等于设置下拉菜单的单元格值即可。
点击注册本站用户
声明：本站收集整理各大网赚平台的付费资源，仅提供资源分享，不提供任何的一对一教学指导，不提供任何收益保障，具体请自行分辨测试。
网站上传的百度网盘链接失效，购买网站资源或者开通网站会员有充值问题，可以联系站长，微信：midou7799，其他问题请多看几遍购买的资源教程，就可以解决！