使用数据有效性制作动态可搜索的下拉菜单
liebian365 2024-11-21 17:37 19 浏览 0 评论
在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单元格值设置为等于设置下拉菜单的单元格值即可。
相关推荐
- zookeeper的Leader选举源码解析（zookeeper选取机制）
-
作者：京东物流梁吉超zookeeper是一个分布式服务框架，主要解决分布式应用中常见的多种数据问题，例如集群管理，状态同步等。为解决这些问题zookeeper需要Leader选举进行保障数据的强一致...
- 接待外国人英文口语（接待外国人英文口语翻译）
-
接待外国人英文口语询问访客身份： MayIhaveyourname,please? 请问您贵姓？ Whatcompanyareyoufrom? 您是哪个公司的？ Could...
- 一文深入理解AP架构Nacos注册原理
-
Nacos简介Nacos是一款阿里巴巴开源用于管理分布式微服务的中间件，能够帮助开发人员快速实现动态服务发现、服务配置、服务元数据及流量管理等。这篇文章主要剖析一下Nacos作为注册中心时其服务注册与...
- Android面试宝典之终极大招（android 面试宝典）
-
以下内容来自兆隆IT云学院就业部，根据多年成功就业服务经验，以及职业素养课程部分内容，归纳总结：18.请描述一下Intent和IntentFilter。Android中通过Intent...
- 除了Crontab，Swoole Timer也可以实现定时任务的
-
一般的定时器是怎么实现的呢？我总结如下：1.使用Crontab工具，写一个shell脚本，在脚本中调用PHP文件，然后定期执行该脚本；2.ignore_user_abort()和set_time_li...
- Spark源码阅读:DataFrame.collect 作业提交流程思维导图
-
本文分为两个部分：作业提交流程思维导图关键函数列表作业提交流程思维导图collect后Job的提交流程点击「链接」查看DataFrame.collect触发的作业提交流程思维导图。关键函数列表Data...
- Arduino通过串口透传ESP 13板与java程序交互
-
ESP13---是一个无线板子，配置通过热点通信Arduino通过串口透传ESP13板与java程序交互这个程序最基本的想法是用java把Arduino抽象出来，忙活了好几天，虽然没有达到最后的...
- Arduino与两个或多个Arduino板之间的通信
-
问题您希望让两个或多个Arduino板一起工作。您可能希望增加I/O能力或执行比单个板上能够实现的更多处理。您可以使用I2C在板间传递数据，以便它们可以共享工作负载。解决方案本示例中的两段代码展示了如...
- Android开发者必知的5个开源库（安卓开源库）
-
过去的时间里，Android开发逐步走向成熟，一个个与Android相关的开发工具也层出不穷。不过，在面对各种新鲜事物时，不要忘了那些我们每天使用的大量开源库。在这里，向大家介绍的就是，在这个任劳任怨...
- Android 开发中文引导-应用小部件
-
应用小部件是可以嵌入其它应用（例如主屏幕）并收到定期更新的微型应用视图。这些视图在用户界面中被叫做小部件，并可以用应用小部件提供者发布。可以容纳其他应用部件的应用组件叫做应用部件的宿主（1）。下面的截...
- Android | 如何在设备启动完成后打开应用
-
读完这篇文章大概需要1分钟最近,在做一个应用(暂且称之为MyApp),里面需要有provisioning(配置)的部分,也就是在应用启动前有个配置的过程,由好几个Activity组成,一个...
- 2021款欧版标致3008 1.5T柴油版:更为舒适的家用SUV
-
自文艺复兴时期及以后以来闻名的法国人的外向性为现代汽车文化注入了另一块石头，但如今已被大量技术所装饰。这标致3008出生时是畅销书。这是您在看到新船体时就知道的情况之一，那是在2016年，当年几乎...
- 媒库文选估计波士顿动力公司机器狗大军的拉力
-
EstimatethePullingForceofBostonDynamics'Robo-DogArmy估计波士顿动力公司机器狗大军的拉力RhettAllain雷特·阿兰When...
- Three.js建模基础（threejs3d）
-
在Three.js中，一个可见的物体是由几何体和材料构成的。在这个教程中，我们将学习如何从头开始创建新的网格几何体，研究Three.js为处理几何对象和材质所提供的相关支持。1、索引面集/Indexe...
- 如何用2 KB代码实现3D赛车游戏?2kPlus Jam大赛了解一下
-
选自frankforce作者：Frank机器之心编译参与：王子嘉、GeekAI控制复杂度一直是软件开发的核心问题之一，一代代的计算机从业者纷纷贡献着自己的智慧，试图降低程序的计算复杂度。然而，将一款...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- wireshark怎么抓包 (75)
- qt sleep (64)
- cs1.6指令代码大全 (55)
- factory-method (60)
- sqlite3_bind_blob (52)
- hibernate update (63)
- c++ base64 (70)
- nc 命令 (52)
- wm_close (51)
- epollin (51)
- sqlca.sqlcode (57)
- lua ipairs (60)
- tv_usec (64)
- 命令行进入文件夹 (53)
- postgresql array (57)
- statfs函数 (57)
- .project文件 (54)
- lua require (56)
- for_each (67)
- c#工厂模式 (57)
- wxsqlite3 (66)
- dmesg -c (58)
- fopen参数 (53)
- tar -zxvf -c (55)
- 速递查询 (52)