Excel教程:Excel最核心的2个查询套路!解决80%查询问题

来源:互联网 作者:

编按:哈喽,大家好!说到查询,我们第一反应就是VLOOKUP、LOOKUP、INDEX等查询函数。它们的基础用法,我们都知道,但是仅仅用它们的基础用法,远远没办法满足我们的需求。所以今天我们来说点实在的,在查询界最核心的两个查询套路!学会了它们,大多数的查询问题都能迎刃而解,本篇教程以查询员工最后一次调薪金额为例,小伙伴们赶紧来看看吧!

下表中记录了每一个员工多次薪资调整信息,现在需要根据姓名来查找最终的薪资状况。(VLOOKUP查找数据的时候,默认是从上往下查找,找到第一个符合条件的数据后,就不会再继续往下查找)


如上表通过函数公式VLOOKUP(K2,A:E,5,0)查找秦英邦对应薪资调整记录,返回的是第一次调整后的薪资。现在需求返回员工最终的薪资,也就是表中的E6单元格。

(本文的数据源是默认的按姓名和时间顺序排列的,所以只需要找出每个姓名的最后一次记录,如果大家在实际工作中记录的表格是乱序的,可以先用“排序”功能按姓名和时间排序,再进行下方的操作)


解决方案①


根据表中数据可以看出员工薪资调整是按顺序,有章可循。我们可以通过INDEX结合数组公式完成查找。

通过数组公式

INDEX(E:E,SMALL(IF(A:A=K2,ROW(A:A),65536),COUNTIF(A:A,K2)))

即可返回员工最后一次调整后的薪资。(输入公式后,按shift+ctrl+enter结束

函数公式解析:

INDEX函数通过行数来返回对应单元格的数值,INDEX(E:E,6)就表示返回E列中第6行的数值。所以上方的公式中,第二参数使用了SMALL函数来得到同一个人最后一次工资调整的行号。

1、第一个参数E:E就是表示返回E列的数值。

2、第二个参数SMALL(IF(A:A=K2,ROW(A:A),65536),COUNTIF(A:A,K2))返回数组中最大的行号。

ROW(A:A)用于返回A列的行号。

IF(A:A=K2,ROW(A:A),65536)通过IF判断A列中的姓名是否等于K2单元格,如果等于则返回对应的行号,否则返回65536(无实意,表示无限大)。

COUNTIF(A:A,K2)部分是统计K2单元格中姓名在A列数据中出现的次数。

SMALL(IF(A:A=K2,ROW(A:A),65536),COUNTIF(A:A,K2))=SMALL({2、3、4、5、6},5),返回数组中第5小的值,也就是最大值行号6。

INDEX-SMALL-IF-ROW其实是一个万金油公式,在查询方面的很多难题都可以用到它,想了解更多这个公式用法的小伙伴,可以戳这里,查看往期教程《熬夜加班发际线后移?谁让你不会Excel万金油公式!【Excel教程】


解决方案②


通过函数公式LOOKUP(1,0/(K2=A:A),E:E)即可返回员工对应的最终薪资。

函数公式解析:

(1)用K2单元格依次与A列中的数据匹配,如果相同则返回TURE,如果不同则返回FALSE。在运算过程中TRUE为1,FALSE为0。

以秦英邦为例,在M2单元格输入=$K$2=A2,鼠标放置在M2单元格右下角,向下拖动填充至M19单元格。K2单元格依次与A列中所有姓名进行匹配,相同则返回TRUE,不相同则返回FALSE,如下图所示:

然后再用0除以M列中的结果,由于四则运算中TRUE表示1,FALSE表示0,所以就会得到0或者#DIV/0! 两种结果。

(2)根据LOOKUP查询规则,如果第1参数的数值大于第2参数中的最大值,那么就定位到第2参数最后一个数值所在行(错误值不参入),然后返回该行中第3参数(E列)的值。

其实LOOKUP(1,0/(条件)......结构也是一个十分经典的查询结构,关于这个结构我们在《VLOOKUP&LOOKUP双雄战(四):在横向和逆向查询上的血拼!》文章中的“ROUND 06 逆向查询”有详细介绍。

今天的课程就到这里,如果大家还有更简单的方法,欢迎留言分享!

资讯来源说明:本文章来自网络收集,如侵犯了你的权益,请联系:puerppt#163.com进行删除。

PPT模板

  • 现代古风通用PPT
  • 故宫国风演示文档PPT
  • 古风水墨简约模板PPT
  • 古风中国风环保主题PPT
  • 古风工作总结汇报模板PPT
  • 古风古典雅致通用模板PPT
  • 扁平插画手绘中小学生安全教育日PPT
  • 小学交通安全教育课件PPT
  • 小学交通安全教育课件PPT
  • 可爱卡通幼儿教学课件模板PPT

Excel模板

  • 单据粘贴单11
  • 出差申报单11
  • 出差申报单(2)1
  • 付款申请单11
  • 付款申请单(3)1
  • 入库单1
  • 领料单
  • 销售明细单1
  • 销售明细单(3)
  • 付款申请单(2)1

Word模板

  • 面试招聘
  • 社会招聘面试审批流程
  • 电话营销销售人员的招聘面试流程
  • 求职招聘登记表及面试记录表
  • 校园招聘面试结果评价表
  • 校园招聘求职者面试报告示范模板
  • 招聘面试评分记录表
  • 招聘面试评估表
  • 招聘面试评价表
  • 招聘面试记录表
优页文档

优页文档(www.youyedoc.com)是一家专注于分享高质量的PPT模板、Excel表格、Word模板的下载网站,1000+各行业优质设计师每日更新200+优质办公文档模板,满足各行业办公需求。海量office文档制作教程,致力于打造国内最大最权威的办公文档下载一站式服务平台

Copyright © 2021-2021 www.youyedoc.com. All Rights Reserved.   粤ICP备2021116258号

本站所有文档资源来源于互联网或作者上传,仅供学习研究使用,版权归作者所有,请勿用于商业用途,如果用于商业用途请联系作者,如果因为您将本站资源用于其他用途而引起的纠纷,本站不负任何责任。

如果本站内容无意中侵犯了您的版权,请联系youyedoc,我们会及时处理。