你以为会了VLOOKUP就很牛逼?5个查询查找公式 一个比一个厉害 图文

2020年5月12日10:35:56 评论 79 views

工作中,大家使用VLOOKUP函数查询数据是很常见的事,但有时候,一个VLOOKUP函数可能无法解决更多的问题,这时候学习下其他函数组合也很必要!

下图中,如果通过VLOOKUP函数查找E3:E5单元格姓名对应的销售额,公式应该怎么写?

F3公式:=VLOOKUP(E3,$B$3:$C$10,2,0)

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

公式解析:

=VLOOKUP(找什么,从哪里找,找到了返回什么,精确查找还是模糊查找

E3:表示要查找的内容。

$B$3:$C$10:表示以查找内容为首列的查找区域。

2:表示我们要返回的结果在查找区域中属于第2列。

0:表示精确查找。

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

可以代替VLOOKUP函数解决这道题的函数有哪些?

一、LOOKUP函数。

=LOOKUP(查找值,查找范围,返回值范围)

F3公式:=LOOKUP(1,0/($B$3:$B$10=E3),$C$3:$C$10)

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

公式解析:

$B$3:$B$10=E3:判断B3:B10单元格区域中的内容是否跟E3单元格内容相等,若相等,返回TRUE,否则,返回FALSE,此时返回一个TRUE和FALSE的数组:{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

0/($B$3:$B$10=E3):用0/TRUE,0/FALSE会返回一个0和错误值的数组:{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

整个公式的意思是:用LOOKUP函数查找数字1在{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}这个数组中的位置,但始终查不到,于是返回最后一个0值的位置,这时返回相对应的C3:C10单元格区域中对应的值。

二、INDEX+MATCH函数。

F3公式:=INDEX($C$3:$C$10,MATCH(E3,$B$3:$B$10,0))

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

公式解析:

=MATCH(查找对象,查找范围,查找方式)

=INDEX(查找区域,返回所在行号,返回所在列号)

MATCH(E3,$B$3:$B$10,0):精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:5。

$C$3:$C$10:是INDEX函数的查找区域。

整个公式的意思就是:在C3:C10查找区域内,返回第5行对应单元格的内容。

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

三、OFFSET+MATCH函数。

F3公式:=OFFSET($B$2,MATCH(E3,$B$3:$B$10,0),1)

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

公式解析:精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:5。

=OFFSET(基准位置,向下或上偏移几行,向右或左偏移几列,引用区域的高度,引用区域的宽度)

MATCH(E3,$B$3:$B$10,0):精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:5。

$B$2:是OFFSET函数的基准位置。

整个公式的意思是:以B2单元格为基准,向下偏移5行,向右偏移一行,最后两个参数省略,默认一个单元格的高度。即C7单元格的位置。

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

四、INDIRECT+MATCH函数。

F3公式:=INDIRECT("C"&MATCH(E3,$B$2:$B$10,0)+1)

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

公式解析;

=INDIRECT(引用区域,引用格式)

MATCH(E3,$B$2:$B$10,0):精确查找E3单元格内容在查找范围B3:B10中的位置,返回的结果为:6。

MATCH(E3,$B$2:$B$10,0)+1:加1是因为单元格的内容是从第2行开始写的,而行号从1开始,加1才能得到正确的结果,这里返回的结果为:7。

=INDIRECT("C"&MATCH(E3,$B$2:$B$10,0)+1):使用&文本连接符将C与MATCH函数返回的位置连接起来,相当于公式=INDIRECT("C7"),也就是引用C7单元格的内容,所以结果为:195。

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

五、HLOOKUP+TRANSPOSE函数。

=HLOOKUP(查找值,查找区域,返回第几行的数据,精确查找还是近似查找)

=TRANSPOSE(需要进行转置的数组或工作表上的单元格区域)

F3公式:{=HLOOKUP(E3,TRANSPOSE($B$3:$C$10),2,0)}

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

公式解析:

TRANSPOSE($B$3:$C$10):将纵向的单元格区域B3:C10转成横向的单元格数据。使用该函数的原因是因为HLOOKUP函数只能横向查找。

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

{=HLOOKUP(E3,TRANSPOSE($B$3:$C$10),2,0)}:

E3:查找值。

TRANSPOSE($B$3:$C$10):查找区域。

2:返回值在查找区域的第2行。

0:精确查找。

你以为会了VLOOKUP就很牛逼?学好这几个查询组合更加重要

注意:该公式中的双大花括号并不是手动输入的,而是公式写完之后按组合键“Ctrl+Shift+Enter”自动生成的。

如有需要本次教程的Excel练习文件,私信发送“017”即可免费获取!

以上就是本期的教程,有任何不懂之处可以私信找我或评论区留言哦~觉得文章不错转发或者点个赞吧!

本文已取的原作者授权 禁止转载 原文链接为https://www.toutiao.com/item/6739345845763719683/

  • 微信扫码免费学习
  • 免费学习48小时
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin

发表评论

您必须才能发表评论!