Excel还能模仿百度智能提示?教你3步搞定
点击上方SQL数据库开发,关注获取SQL视频教程
SQL专栏
大家在使用搜索引擎时,当你输入某个关键字后,搜索引擎就会智能提示一些相关内容出来供你选择。
其实Excel也可以实现类似的功能,不信你看。
示例解释我们设定的场景是,人力资源经理想查询某个员工的工资。但是员工人数较多,如果一个一个从下拉菜单中选择肯定非常麻烦。于是我们做了一个智能提示功能,只需要输入某个员工的姓即可智能提示以这个姓开头的员工。从智能提示选项中选择员工后,会显示该员工的工资。
那么我们该如何制作呢?
1、准备原始数据
要制作下拉提示,我们需要有一个先准备一份原始数据。如下图:上图是一份员工工资明细表,我们需要对姓名列进行排序(升序或降序均可)。
2、制作查询窗口接下来我们制作一个查询窗口,如下图:注意:务必保证查询的两列的名称与原始数据保持一致,这里A1单元格就与D1单元格的内容一致,B1单元格与J1单元格内容一致。
制作下拉菜单选中A2单元格,依次点【数据】-数据工具下的【数据验证】-允许菜单下选择【序列】,在来源中输入如下公式:=OFFSET($D$1,MATCH(A2&"*",$D$2:$D$18,0),,COUNTIF($D$2:$D$18,A2&"*"))具体如下图:
我们来解释一下上面的公式:OFFSET作用:以指定的引用为参照系,通过给定偏移量得到新的引用。语法:OFFSET(参考系,向下几行,向右几列,引用区域)OFFSET是以某个起点为参考,在指定的区域内向下移动几行,向右移动几行。这里我们使用OFFSET是为了生成动态引用区域。
MATCH作用:返回指定数值在指定数组区域中的位置语法:MATCH(查找对象,指定查找的范围或者数组,查找方式)查找的方式主要有三种:0、1、-1,分别表示精确匹配、升序查找、降序查找模式。这里我们是为了查找A2单元格中的内容,在D2:D18区域的具体位置,A2后面加*是为了实现模糊搜索。
COUNTIF作用:计算指定区域中满足指定条件的单元格个数语法:COUNTIF(条件范围,条件)这里我们是为了统计D2:D18区域与A2一致的个数有多少个,比如以孙开头的姓就有3个。
解释完函数意思,我们还要修改一下刚才的数据验证窗口,点击【出错警告】页,取消勾选【输入无效数据时显示出错警告】。如下图:这个是防止我们在查询时,查询的内容不在引用区域内,会弹出出错警告。
3、匹配具体工资
接下来我们需要通过A2中选定的员工姓名来查询出他的实发工资。这个时候就要用到我们的VLOOKUP函数了。可以在B2单元格中输入如下公式:=IFERROR(VLOOKUP(A2,$D$2:$J$18,7,0),"")VLOOKUP函数我就不进一步解释了,还没学会的小伙伴可以查看历史文章《VLOOKUP函数都不会,如何在职场快人一步?》这里我们使用VLOOKUP函数直接查询出A2在区域D2:J18对应的实发工资即可。这样一个简单智能提示工资查询就完成啦。是不是很简单?赶紧打开Excel跟着制作一下吧。
觉得不错,记得【转发】+【在看】哦,谢谢啦~
——End——
后台回复关键字:资料领取,获取一份精心整理的技术干货
后台回复关键字:进群,带你进入高手如云的交流群。
推荐阅读点击「阅读原文」了解SQL训练营
评论