![]()
最近小伙伴们都在问我VLOOKUP函数的用法,
我也不是个磨蹭的人,
一个函数分10章来讲,
我今天就用一章来给大家讲9个经典的例子(课件下方领取)
1，区分VLOOKUP和HLOOKUP函数
通常情况下,我们使用VLOOKUP或者是HLOOKUP函数取决于表格的格式,准确的来说应该是表头,
![]()
如果你的表头是这样横向排布,
而我们通常会根据具体的产品去进行数量,收入,单价,客户的查找.
就需要用到这样VLOOKUP.
![]()
首先选择你的表格内容区域,左上角编辑一个名称(这也是最直接的名称定义),好处就是,不管你的表格有多大,定义完了之后,直接在VLOOKUP函数里面进行范围的引用,左右拉扯,不需要理会绝对引用或者是相对引用.
大家也可以通过我的图片,看到这个函数,比如找价格
=VLOOKUP(I5,销量统计1,4,0)
文字解析:
VLOOKUP(找产品4,定义名称的范围,相对于范围价格所在的列数,0精确匹配)
同理,我们也可以找到具体客户(如上图.)
如果当你的表格发生转置,或者你的表格格式如下图这样,
![]()
大家不妨与之前的表格进行一下对比,这个时候的表格表头变成了纵向.
所以这样的情况下,我们就应该使用HLOOKUP来进行查找.
![]()
这个公式也没发生什么改变,知识我们VLOOKUP函数中是数的列数,而HLOOKUP函数是数的行数.
![]()
![]()
2，有朋友问,跨工作簿引用,怎么弄,
回答,其实都是一样的,你直接引用就行了.无非就是多了一个步骤,切换工作簿.!!!!
3，怎么算分段奖金.
其实这个也说过了,在我的基础篇.不过也无妨,大家可以多熟悉熟悉.
例子,
![]()
这是一系列需要求的值,
我们有以下的关系:
销售额=50000且=75000且=100000且=125000且=175000且=200000,提成10%
用表格的形式体现出来时这样的:(大家一定要记住这样的形式)
![]()
![]()
我们将奖金提成辅助表格命名为:奖金提成
D5单元格的函数则为:=VLOOKUP(C5,奖金提成,2)
![]()
最后一个值不填写,或者填写1,都视为近似匹配,
而我们这个地方需要用到这样的近似匹配,来找到区间值.
4，成绩表评级
![]()
这是一张成绩单,我们需要根据分数段来进行评级,
成绩>=0且=60且=70且=85且=95,评级A
用列表的方式体现则是:
![]()
其实这个时候,我们也可以使用近似匹配的方式来做,有可能你会想复杂,用IF的多重逻辑来判断,那么会浪费很多时间.
![]()
G5单元格的函数则为:=VLOOKUP(F5,评级,2)
5，团队分组
![]()
可能某一个,你们的团队需要组织一次拔河比赛,需要分成A组和B组.
这个时候,你可以建立一个辅助表格.如下:
![]()
因为这个地方不是数字范围,而是精确的部门名称,所以我们可以使用精确匹配来做
![]()
E6单元格的函数则为:=VLOOKUP(D6,分组,2,0)
6，在另表批量提取所需的数据
![]()
上图时数据源,
在另外一张工作簿中,或许你有一个挑选名单,
![]()
我们需要根据工号,来找到相对应的人员信息,而且是输入一个单元格的函数,然后向下向右拉扯就做完.
这个地方需要注意的是,两张表格的栏位并不是一样的,可能之前你会想到用COULUM()函数去取代第三个参数.
其实我们可以建立一个辅助列就行了,因为如果表格特别庞大,你数数字都得数半天.
![]()
我同样将数据源命名为人员信息,
F7单元格应该为:=VLOOKUP($E7,人员信息,提取!F$5,0)
注意:我们的工号处应该锁列,第三个参数应该锁行
需要解释的是,这个上边的辅助行,实际是对应的我们"人员信息"数据源的相对列数.
然后写完这个函数之后,向右向下拉扯.
结果就出来了,
![]()
7，多个名称有重叠字样,怎么进行精确的查找.
![]()
大家可以看到这张图,例如豆角,就出现了4次,
而我们也都知道VLOOKUP只能找到一个值,那么这样的情况,我们怎么实现通过VLOOKUP找到所有的值呢?
![]()
我们需要建立一个辅助列在旁边.
A5单元格写成:=B5&COUNTIF($B$5:B5,B5)
当这个行数往下拉扯的时候,这个范围会不断的拉长,
那么同样的名词,就会出现若干次,这样我们把名词和数字进行合并,就可以了.
![]()
F7单元格的内容:=IFERROR(VLOOKUP($F$4&E7,$A$5:$C$19,3,0),"")
除去IFERROR的话,
=VLOOKUP($F$4&E7,$A$5:$C$19,3,0)
那意思就是,我们把F4单元格中需要查找的值,和数字进行拼接.
以一种取巧的方式来实现这样的查找.
8，多条件查找
![]()
其实这个例子和上边的例子很相似,问题在于,前者是同样的名词出现,而这个地方则是,我们需要拟定2个条件,
类别IT
国家日本
求满足条件的收入.
我们直接把类别和国家进行名词合并,
然后使用VLOOKUP
G6单元格的输入为:=VLOOKUP(G4&G5,A5:D20,4,0)
同样的在查找值这个地方把名词合并.
9，上述例子的VLOOKUP函数数组解法
{=VLOOKUP(F3&F4,CHOOSE({1,2},A4:A19&B4:B19,C4:C19),2,0)}
范围这个地方变成了
CHOOSE({1,2},A4:A19&B4:B19,C4:C19)
这个地方CHOOSE含义在,我们将范围分段,
第一个范围:类别系列和国家系列,这两个系列的条件必须符合我们选择的条件.
第二个范围:在这段VLOOKUP函数的第三个参数位置,我们选择的2,
C4:C19在CHOOSE函数里的含义是收入.
意味着我们用第一个范围来确定位置,而第二个范围是用来被VLOOKUP函数引用.
视频链接:
复制链接PC端看视频
https://haokan.baidu.com/videoui/page/videoland?pd=bjh&vid=7789272692056200535&fr=bjhauthor&type=video
课件地址:
https://share.weiyun.com/5AabARg
|
|
转播
