点击下面名片,关注咱们,从此更加多Excel秘籍分享给您!
Excel秘籍大全
全网极具影响力excel精英培训、Excel杨公众号、Excel之家excelhome、Excel不加班,excel广场,秋叶Excel,为你供给Excel免费在线培训,Excel模板、教程,Excel函数公式大全,excel全套自学教程
534篇原创内容
公众号
Excel秘籍大全,前言
在Excel中的公式非常多,要评选哪个是最应该把握的,相信必定绕不外VLOOKUP函数。
说到VLOOKUP函数,只要是运用Excel的,相信对它必定不陌生,它的运用好处说多少都不为过,今天就重点给大众分享下VLOOKUP函数的有些常用的技巧。
首要咱们先看下VLOOKUP函数的语法结构:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value:查询值table_array:查询的区域col_index_num:返回数据在查询区域的第几列range_lookup:匹配模式(0为精确查询,1为模糊查询)
Excel秘籍大全,正文起始
1
普通查询
按照姓名查找其姓名对应的销量,F2公式:
=VLOOKUP(E2,$B$1C$15,2,0)
E2:查询的内容,B1:C15为查询的区域(重视需按F4锁定),返回的列数为2,0表率的精确查询。
2
屏蔽错误值错误值查询
=VLOOKUP(D2,A:B,2,0)
VLOOKUP函数倘若查询不到对应值会表示错误值#N/A,这个看起来很不美观。此时能够在外面加个容错函数IFERROR,倘若是2013版本那就更好,能够用IFNA函数,这个是专门处理#N/A这种错误值。
=IFERROR(VLOOKUP(D2,A:B,2,0),"")=IFNA(VLOOKUP(D2,A:B,2,0),"")
函数语法:
=IFERROR(表达式,错误值要表示的结果)
说白了便是将错误值表示成你想要的结果,不是错误值就返回原来的值。IFNA函数的功效亦是同样,只是IFERROR函数是针对所有错误值,而IFNA函数只针对#N/A。
3
逆向查询
VLOOKUP函数是咱们在查找数据时运用频率最高的一种函数,它本身是不支持逆向查找的,需要协同IF函数达到逆向查找的目的。如图1所示,按照薪资查找对应的员工姓名,其公式为:=VLOOKUP(H2,IF({1,0},F1:F8,A1:A8),2,0)。
图1
IF函数搭配数组{1,0},当{1,0}为1时,IF函数返回第二参数F1:F8,当{1,0}为0时,IF函数返回第三参数A1:A8,这般就从空间上构建了F1:F8和A1:A8构成的顺向数据区域,IF函数的功效便是将本来逆向排布的数据区域进行顺向排布。
4
按次序返回多列值内容
例如如下,咱们想按照工号,分别输出姓名和销量,要怎样快速实现呢?
咱们能够在F2输入公式,而后向下向右填充就可
=VLOOKUP($E2,$A$1C$15,COLUMN(B1),0)
此公式的关键在于$E2的混合引用(列绝对引用,行相对引用),以及COLUMN函数返回的需要的列数。
5
多要求查找
倘若不想或不可改变数据源的格式,或想向同事展示一下自己的技能,就不可运用第1种办法。那就只能用第二种办法, Vlookup+if.
思路便是: 手工创建一个复合要求,将多个要求变成一个要求。手工创建一个复合数据列,将月份和姓名合并成一个数据列;过程1. 手工创建复合要求。
和第1种办法同样。在H2输入=I2&J2 。
过程2. 手工创建复合数据列。
咱们能够利用if函数创建复合数据列。公式为IF({1,0},B:B&C:C,F:F) B:B&C:C。是将月份列和姓名列合并成一列。F:F。是查找结果列If({1,0})。是将B:B&C:C和F:F和成两列数据。第1列是复合数据列B:B&C:C。第二列是查找结果列F:F过程3. 将以上手工创建的复合数据IF({1,0},B:B&C:C,F:F),代入Vlookup. 在K2输入=VLOOKUP(H2,IF({1,0},B:B&C:C,F:F),2,0)并将光标移到公式编辑栏,按Ctrl+Shift+Enter键。另一,过程1亦能够省略。公式直接改为 在K2输入=VLOOKUP(I2&J2,IF({1,0},B:B&C:C,F:F),2,0) ,并将光标移到公式编辑栏,按Ctrl+Shift+Enter键。6
一对多查找
经过一个公式怎么快速引用所有的结果值,首要要思考解题的思路。
有了创建辅助列解答的经验,其实咱们能够借鉴countif函数获取一个独一值列表。
但countif函数输出的是一个结果,怎样使一个结果转换成一个列表,则是问题的关键。
这里作者利用row函数来获取一个数字列表,而后结合indirect函数的单元格引用,来创建一个单元格列表,再利用countif函数计数时,将会得到一个数组结果。
到了这一步,其实已然能够获取不重复的独一值列表。
但语言描述太笼统,下面直接上公式:
=VLOOKUP($D$5&COLUMN(A1),IF({1,0},$B$2B$29&COUNTIF(INDIRECT("b2:b"&ROW($229)),$D$5),$C$2C$29),2,0)
这个公式组合了if数组、countif、indirect和row函数,如上所讲,countif函数得到一个计数的数组结果时,而后直接与货号列进行合并,直接得到了与创建辅助列的结果相同的查找列表。
而后利用if数组在vlookup函数中的应用,进行查找列和返回列的设置。从这一步来看,其实之前讲解的vlookup+if函数的两种用法,亦是这个案例中的一个解题要点!
最后给vlookup的第1参数查询值连接上column函数,进行横向的动态引用。
7
Vlookup+Match函数
公式如下:
=VLOOKUP($H2,$A$2F$17,MATCH(I$1,$A$1F$1,0),0)
这个函数亦是非常简单的一个搭配,倘若无看懂,能够瞧瞧视频中的仔细解析!
8
Vlookup+通配符查询
举个例子,左边是员工工资表数据,此刻需要按照姓名的简叫作来快速匹配工资
倘若咱们直接运用VLOOKUP公式是查询不出结果的,由于查询值,和查询列数据不同样
这个时候,咱们需要用到通配符*号,暗示任意多个字符,咱们用"*"&E2&"*"来查询,暗示悟空前面和后面都能够连接任意的字符,都能进行匹配出来
因此运用公式:
=VLOOKUP("*"&E2&"*",A:C,3,0)
需要重视的是,倘若咱们的原数据中有多个满足的要求,那只会返回第1个结果,例如咱们原始数据中有假悟空和孙悟空两个姓名,由于咱们用的通配符,因此会找到第1个满足的要求,返回对应的结果。
|