• 欢迎访问人家博客,WordPress信息,WordPress教程,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站,欢迎加入人家博客 QQ群
  • Git主题现已支持滚动公告栏功能,兼容其他浏览器,看到的就是咯,在后台最新消息那里用li标签添加即可。
  • 最新版Git主题已支持说说碎语功能,可像添加文章一样直接添加说说,新建说说页面即可,最后重新保存固定连接,演示地址
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏人家博客吧

常用Excel查询套路,你用过几个?

经验分享 admin 2年前 (2018-01-07) 85次浏览 0个评论

大众情人

如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的年龄。

经典套路:

=VLOOKUP(G2,B1:E6,4,0)

常用Excel查询套路,你用过几个?


万能查询

如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的工号。

=LOOKUP(1,0/(G2=B2:B6),A2:A6)

常用Excel查询套路,你用过几个?

套路指南:

=LOOKUP(1,0/(条件区域=指定条件),要返回的区域)


最佳组合

用MATCH函数来定位查询值的位置,再用INDEX函数返回指定区域中指定位置的内容,二者结合,可以实现上下左右全方位的查询。

如下图所示,根据姓名查询部门和职务。

F3单元格公式为:

=INDEX(A:A,MATCH($E3,$C:$C,))

常用Excel查询套路,你用过几个?


隔壁老王

VLOOKUP函数和MATCH函数结合,常用于不确定列数的数据查询。

如下图所示,要根据B13单元格的姓名,在数据表中查询对应的项目。

C13单元格公式为:

=VLOOKUP(B13,A1:G9,MATCH(C12,1:1,),0)

常用Excel查询套路,你用过几个?

如果数据表的列数非常多,在使用VLOOKUP函数时,还需要掰手指头算算查询的项目在数据表中是第几列,真是麻烦的很。


现在好了,先用MATCH函数来查询项目所在是第几列,然后VLOOKUP函数就根据MATCH函数提供的情报,返回对应列的内容。


多P套路

一对多的查询在日常工作中经常遇到,以下图为例,要提取出财务部的所有人员。

F2单元格输入以下公式后,按Ctrl+Shift+回车,然后向下拖动复制即可。

=INDEX(C:C,SMALL((B$2:B$10<>E$2)/1%+ROW($2:$10),ROW(B1)))&””

常用Excel查询套路,你用过几个?


公式的意思是:

如果B$2:B$10单元格区域中的部门不等于E$2单元格中指定的部门,就除以1%,得到100和0组成的内存数组。

再加上行号ROW($2:$10),如果等于E2单元格姓名,就是0+对应行号,否则就是100+对应行号。

然后使用SMALL函数从小到大依次提取出行号,INDEX函数根据提取出的行号,返回A列对应的内容。

提示:这个公式相对复杂一些,如果暂时看不懂,可以先收藏备用。


图文制作:祝洪忠



易学宝微视频教程,1290个Office技巧精粹,每个技巧都与实际工作密切相关。轻松学习技巧,练就职场达人,淘宝搜索关键字:ExcelHome易学宝

本公众号主页回复关键字 大礼包 ,获取50集精彩微视频教程。

始发于微信公众号:Excel之家ExcelHome


极客公园 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:常用Excel查询套路,你用过几个?
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址