Vlookup的9种查找方式,灵活运用函数原来这么简单!

挖数网精选
挖数网精选
挖数网精选
352
文章
0
评论
2020-04-2802:05:00 评论 167 2912字
摘要

这两天看了不少网上的教程,也找了一些题库实战,整理一下EXCEL中VLOOKUP函数的常用技巧。首先我们来了解一下绝对引用和相对引用的概念,EXCEL默认横向拉动的时候列变行不变,竖向拉动的时候行变列不变。

这两天看了不少网上的教程,也找了一些题库实战,整理一下EXCEL中VLOOKUP函数的常用技巧。

首先了解一下绝对引用和相对引用的概念,EXCEL默认横向拉动的时候列变行不变,竖向拉动的时候行变列不变。

  • 绝对引用时无论横向还是竖向拉动,行列都不变

  • 相对引用时,$在行前面则横向竖向行动都不变,$在列前面则横向竖向拉动列都不变

一. 基础查找

VLOOKUP(查找目标,查找区域,返回列,0)

下面是一个实例:

在员工信息表中根据员工工号查询员工姓名,我想要在D1:G9的表格区域中查找工号分别为A01048、A05023、A09095的员工姓名,首先查询工号A01048的员工姓名,之后可以直接下拉公式,表格自动填充结果。

注意:此公式查找目标一定要在查找区域的第一列,比如我通过工号查姓名,那么工号必须在查找区域的第一列,即D1:G9;如果是通过姓名查部门,那么查找区域需要是E1:G9,而且查找区域需要用 $ 符号绝对引用,不然下拉公式的时候区域变化可能会查不到数据。

返回列指查询内容所在列,如我想要查找的是姓名,姓名这一列在D1:G9区域的第二列,所以返回列的值是2,以此类推。

最后一个参数0或1表示函数精确查找或模糊查找。精确即完全一样,模糊即包含的意思。参数如果指定值是0或FALSE就表示精确匹配,而值为1或TRUE时则表示模糊匹配,漏掉该参数则默认模糊匹配。

精确匹配是使用历遍法查找,模糊匹配是使用二分法查找。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式01

二. 多列查找 VLOOKUP(查找目标,查找区域,COLUMN(A1/B1/C1...),0)

上例通过工号查询姓名,如果想要通过工号同时查询姓名和部门,则可以使用COLUMNS函数。不使用COLUMNS函数往右拖动返回列的值不变,无法查询其他列。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

不使用函数直接拖动

使用COLUMNS函数,A1、B1、C1...分别代表1、2、3...,向右拖动直接变化返回列,返回列的值递增。注意不要忘了相对引用查找目标,固定列,此例中为$A3。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式02

三. 字符的模糊查找

VLOOKUP("*"&查找目标&"*",查找区域,返回列,0)

如我并不知道具体的工号,只知道工号中包含A05,就可以使用模糊查找。第四个参数0是使用遍历法进行精确查找,因此当从上而下查找到包含A05的工号,即停止查找。举例来说,张丽和夏远的工号都包含A05,但是查找出的姓名是张丽,因为张丽在前面。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式03

四. 数字的区间查找

VLOOKUP(查找目标,查找区域,返回列,1)

由于二分法的原理,引用的数字区域一定要从小到大排序,杂乱的数字是无法准确查找到的。模糊查找的原理是:给一定个数,它会找到和它最接近,但比它小的那个数。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式04

五. 反向查找

VLOOKUP(查找目标,IF({1,0},查找列,区域列),返回列,1)

注意:这里其实不是VLOOKUP可以实现从右至左的查找,而是利用IF函数的数组效应把两列换位重新组合后,再按正常的从左至右查找。

IF({1,0},查找列,区域列)这是本公式中最重要的组成部分。在EXCEL函数中使用数组时,返回的结果也会是一个数组。这里1和0不是实际意义上的数字,而是1相关于TRUE,0相当于FALSE,当为1时,它会返回IF的第二个参数(查找列),为0时返回第二个参数(区域列)。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式05

六. 多条件查找

{=VLOOKUP(查找条件A & 查找条件B,IF({1,0},区域A & 区域B,查找区域),返回列,1)}

根据员工号和姓名两个条件查询部门,输入公式=VLOOKUP(A3&B3,IF({1,0},E1:E9&F1:F9,G1:G9),2,0)

以后按ctrl+shift+enter自动变成数组形式

{=VLOOKUP(A3&B3,IF({1,0},E1:E9&F1:F9,G1:G9),2,0)}

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式06

公式剖析:

  • A3&B3 把两个条件连接在一起。把他们做为一个整体进行查找。

  • E1:E9&F1:F9,和条件连接相对应,把工号和姓名列也连接在一起,作为一个待查找的整体。

  • IF({1,0},E1:E9&F1:F9,G1:G9) 用IF({1,0}把连接后的两列与G列数据合并成一个两列的内存数组。按F9后可以查看的结果。

  • 完成了数组的重构后,接下来就是VLOOKUP的基本查找功能了,另外公式中含有多个数据与多个数据运算,所以必须以数组形式输入,即按ctrl shift后按ENTER结束输入。

七. 批量查找

{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式07

公式剖析:

  • B$9&ROW(A1) 连接序号,公式向下复制时会变成B$9连接1,2,3。

  • 给所有的张一进行编号。要想生成编号,就需要生成一个不断扩充的区域(INDIRECT("b2:b"&ROW($2:$6)),然后在这个逐行扩充的区域内统计"张一"的个数,在连接上$B$2:$B$6后就可以对所有的张一进行编号了。

  • IF({1,0}把编号后的B列和C组重构成一个两列数组。

八. 跨表查找

VLOOKUP(查询目标,查询表!查询区域,返回列,0)

下例跨表查询员工所在部门。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式08

跨工作表的时候,会以!代表工作表的名字。

九. 跨多表查找

VLOOKUP(查询目标,INDIRECT(工作表名 &查询区域),返回列,0)

如汇总查询各个表中同一个人的工资信息,公式为:=VLOOKUP($A3,INDIRECT(B$2&"!A1:B9"),2,0)

查询区域使用INDIRECT函数构造引用区域,INDIRECT()函数的功能:返回由文本字符串所指定的引用,语法格式为 INDIRECT(ref_text,[a1]),ref_text 为对单元格的引用。

indirect函数的引用的两种形式:加引号和不加引号。

  • =INDIRECT("B2")——加引号:文本引用——即引用B2单元格所在的文本(1月工资)。

  • =INDIRECT(B2)——不加引号:地址引用——因为B2的值为1月工资,1月工资是工作表的名字,所以引用工作表的区域。

$A3 和 B$2 代表相对引用。

Vlookup的9种查找方式,灵活运用函数原来这么简单!

公式09

如果不知道查询目标在哪个表里,则使用公式:

IFERROR(VLOOKUP(查询目标,A表!查询区域,返回列,0),

VLOOKUP(查询目标,B表!查询区域,返回列,0),.....

VLOOKUP(查询目标,N表!查询区域,返回列,0))

意思是从A表开始查询,前面的查询不到就到后面的表中查找。如果有更多的表,那就一层层的套用下去,一直到最后一个N表。

可以简化为:

=VLOOKUP(查询目标,INDIRECT(LOOKUP(1,0/COUNTIF

(INDIRECT({"A表";"B表";"...";"N表"}&"!a:a"),查询目标),{"A表";"B表";"...";"N表"})&"!查找区域"),返回列,0)

  • {""}:大括号内是要查找的多个工作表名称,用逗号分隔

  • a:a :本例是姓名在各个表中的A列,如果在B列则为b:b

End.

作者:栗子

来源:豆瓣

本文均已和作者授权,如转载请联系后台删除

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: