外链论坛

 找回密码
 立即注册
搜索
查看: 11|回复: 1

Excel中的VLOOKUP函数8大经典用法大全,必定要保藏哦

[复制链接]

3094

主题

2万

回帖

9996万

积分

论坛元老

Rank: 8Rank: 8

积分
99968785
发表于 2024-10-1 14:10:28 | 显示全部楼层 |阅读模式

点击下面名片,关注咱们,从此更加多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个满足的要求,返回对应的结果。

回复

使用道具 举报

3074

主题

2万

回帖

9910万

积分

论坛元老

Rank: 8Rank: 8

积分
99108805
发表于 2024-10-24 17:18:08 | 显示全部楼层
你的见解真是独到,让我受益匪浅。
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

站点统计|Archiver|手机版|小黑屋|外链论坛 ( 非经营性网站 )|网站地图

GMT+8, 2024-11-9 03:37 , Processed in 0.073396 second(s), 19 queries .

Powered by Discuz! X3.4

Copyright © 2001-2023, Tencent Cloud.