过期罐头电脑论坛-win7、win10雨人系统下载

过期罐头电脑论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 191|回复: 0
收起左侧

[办公设计] 如何用EXCEL制作表格下拉菜单

[复制链接]
发表于 2017-10-11 15:38:01 | 显示全部楼层 |阅读模式
发贴请勿包含网站外链(直接下载地址和网盘除外),屡犯者将封禁ID、网址永久屏蔽,www.gqgtpc.com感谢支持!
如何用EXCEL制作表格下拉菜单


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

1. 实现下拉菜单动态扩展
在Excel工作表中,一般情况下制作下拉菜单需要选定相应单元格后,点击“数据”选项卡中的“数据验证”,在弹出窗口的“设置”选项卡中,“验证条件→允许”处选择“序列”,来源处选择相应单元格内容或输入所要显示的列表条目(列表条目用英文状态下的逗号隔开),这样制作的下拉菜单,当在所选单元格下增加新内容时,下拉列表并不会增加,这在实际使用中很不理想。要想使下拉菜单根据新增内容进行动态扩展,可以使用函数配合数据验证。
首先,点击“公式”选项卡中的“定义名称”,在弹出窗口的名称处输入“客户全称”,在引用位置入输入“=offset(客户名称!$A$2,,,counta(客户名称!$A:$A)-1)”;然后,再选定相应单元格(如D2:D27),点击“数据验证”,在“验证条件→允许”处选择“序列”,将光标点到来源处,按F3键,在弹出窗口选择“客户全称”。这样,当在A列增加或删除客户名称时,下拉列表也跟着动态更新(图2)。
1507617847920.jpg

小提示:
还有一种实现下拉菜单动态扩展的方法:首先,选择客户名称数据,点击“插入”选项卡中的“表格”,确定;再次选择客户名称数据,点击“公式”选项卡“定义的名称”中的“根据所选内容创建”,在弹出窗口只勾选“首行”,确定;同样,选定相应单元格(如D2:D27),点击“数据验证”,在“验证条件→允许”处选择“序列”,将光标点到来源处,按F3键,在弹出窗口选择“客户名称”。这样,当在A列增加或删除客户名称时,下拉列表也跟着动态更新(图3)。
1507617847921.jpg

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

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

函数解释:
⑴ “=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)。如此,就完成了整个智能化表格的制作。
1507617848599.jpg           

楼主热帖
[Windows7] Win7系统补丁包的正确安装内存蓝屏及较稳妥优
[Windows7] Win7系统任务栏右键功能系统减负及媒体中心功
[电脑组装维修] 如何调节键盘的重复延迟和重复速度
[Windows10] Win7依然是主流win10份额8%增速减缓
[Windows7] Win7系统下也许你不知道shell的用法及显卡硬
[Windows10] 淘宝上的win10激活码不要买小心上当
[系统应用经验技巧] 微软神作Windows 98在6月25日已发布
[Windows10] Win10升级时因为磁盘空间不够失败的解决方法
[Windows10] AUTOCAD在win10中运行时出现闪退现象的解决
[电脑组装维修] 至强E3-1231配合GTX970独显超强游戏DIY配

您需要登录后才可以回帖 登录 | 注册

本版积分规则

win7 64位旗舰版下载|win10系统下载|win7旗舰版|联系我们|网站地图|过期罐头电脑论坛

GMT+8, 2017-11-23 00:11 , Processed in 0.116993 second(s), 23 queries .

官方免责声明:本站内容来自网友和互联网.若侵犯到您的版权.请致信联系,我们将第一时间删除相关内容!

Powered by Discuz!

过期罐头电脑论坛

win7旗舰版_windows10

快速回复 返回顶部 返回列表