欢迎来到新兴区工厂招聘网!
您当前的位置:首页 > 职场资讯 > 职业向导

受够加班煎熬,我整理出函数之王VLOOKUP使用神技!

来源:工厂招聘网 时间:2018-01-07 作者:工厂招聘网 浏览量:


和公司HR的小姐姐聊天,她说大概有10份简历,有9份都会写着“精通办公软件",但是实际工作中却发现,做个图表还要老半天,只会复制、黏贴,更别说常见的VLOOKUP,SUM,CountIF,IF函数,真的非常心塞。
VLOOKUP函数让大家又爱又恨,其实它功能强大满足各种查询需要,我们用它查工资、查住址……无所不能,但是能用好并不简单。稻壳儿整理出VLOOKUP函数常见用法,真正提高你的办公效率。
- 01 -
了解函数语法

公式:
=VLOOKUP(查找值,数据表,列序数,[匹配条件])
解析:
公式的含义=VLOOKUP(找什么,在哪找,第几列,精确找还是大概找一找)
第4个参数[匹配条件]:
为0时代表精确查找(必须完全一致)
为1时代表模糊查找(缺省也默认模糊查找,如果找不到精确匹配值,则返回小于“查找值”的最大数值)
- 02 -
精确查找
如查找某学号学生的成绩,学号固定唯一,这属于精确查找:
▲利用函数公式,可以快速、精准查询学生成绩
公式:
=VLOOKUP(E2,A2:C10,3,0)
解析:
查找值为E2单元格的值,查找范围为A2:C10,返回地3列(C列),查找方式为0精确查找。
- 03 -
模糊查找
模糊查找在很多时候用处也很大,比如给某个数值自动划分优良差等级:
公式:
=VLOOKUP(C2,$G$2:$H$4,2,1)
解析:
查找范围为$G$2:$H$4(注意加上$符号即可将范围锁定,下拉拖拽公式时不会发生改变)
利用模糊查找0,即得到了对于各个分数段等级(注意数据区域要按从小到大排列,因返回值为小于“查找值”的最大数值)
- 04 -
字符模糊查找
查找下图名称包含AA的产品的价格:
▲第4个产品的字符名称包含AA
公式:
=VLOOKUP(´*´&D2&´*´,A2:B7,2,0)
解析:
通配符“*”来表示任意文本,把*放在字符的两边,即´*´ & 字符 & ´*´代表包含AA
- 05 -
反向查找
即通过查找右边列查找返回左边列,一般VLOOKUP从左向右查找,那么反过来怎么做呢?
▲查找姓名2对应的学号
公式:
=VLOOKUP(E2,IF({1,0},B2:B10,A2:A10),2,0)
解析:
IF({1,0},B2:B10,A2:A10)是实现反向查找的关键,相当于将数组A列和B列互换,这里1和0不是实际意义上的数字,而是1相关于TRUE,0相当于FALSE;
当为1时,它会返回IF的第二个参数(B列),为0时返回第二个参数(A列)。
- 06 -
多条件查找
查找下图中华东地区B产品销售额(需要满足地区为华东,产品为B)
公式:
{=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0)}
解析:
这里利用数组(地区和产品分类)求得均满足条件的销售额,E2&F2连接在一起作为整体要查找值,A2:A10&B2:B10对应连接在一起作为被查找部分,再利用IF({1,0}把C列合并。
这样数组被重新配置成一个新的结构,相当于一个新表,下面就可以利用VLOOKUP的基本查找功能了
公式两边的大括号 {公式} ,不是手输,是鼠标放在编辑栏同时按Ctrl Shift Enter得到的,俗称三建结束或组合键结束。
- 07 -
一对多查询
VLOOKUP能否一对多查询?即符合某查询条件的多个查询结果均返回。比如经典组合数组函数INDEX SMALL IF。
但是数组函数有个缺点是对于内存耗用较大,经常使用表格的朋友都遇到过内存耗用过多软件、电脑罢工的情况,而VLOOKUP函数可以不使用数组运算,大大减少内存消耗。
▲如上图,求供货商A的所有供货产品名称,
- 7.1 -
公式:
=IF(B2=$E$2,CountIF($B$2:$B2,$E$2),"")
解析:
先在A列增加辅助列,对于符合条件的按1、2、3……在A列自动编号,查询时只要查询编号就可以了。
在辅助列中,对于不是供货商A的显示为空,是供货商A的依据$B$2:$B2区域的值计数。这就实现了供货商A在辅助列按1、2、3……编号了。这里注意($)符号的运用。
- 7.2 -
公式:
=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")
解析:
ROW(A1)的值等于1,ROW(A2)的值等于2,利用VLOOKUP查找ROW(A*),返回第3列C列,就返回了所有、满足条件的值。IFERROR函数的作用是对于查询不到的返回空,屏蔽错误值。
- 08 -
同时查询多列
VLOOKUP函数第3个参数表示返回的值在第几列,我们经常将第3个参数修改为数字2、3、4……实现,如果项目很多修改起来很不方便。我们可以通过COLUMN函数,一次输入公式查询所有按顺序的返回结果:
公式:
=VLOOKUP($G2,$A:$E,COLUMN(B2),0)
解析:
通过COLUMN函数产生列号从而得到2、3、4……,替代参数3,无论多少有个项目都可以实现一个公式查询所有返回结果。这里也注意($)符号的运用。
- 09 -
提取
分享到:

Copyright C 2018 All Rights Reserved 版权所有 工厂招聘网 陕ICP备14009204号-5

地址:陕西省西安市工厂招聘网总部 电话(Tel):17778022153 EMAIL:admin@gczp.cc

用微信扫一扫