返回目录:excel表格制作
大家好,我是婶婶,希望接下来的分享能够对大家有些许帮助,也希望大家多多支持鼓励,收藏、分享、评论多多益善啦,如果对胃口记得关注哦!
之前咱们一起学习了Excel教程--如何快速学习掌握VLOOKUP函数(入门篇)、Excel教程——如何快速学习掌握VLOOKUP函数(初级篇)以及Excel教程——如何快速学习掌握VLOOKUP函数(进阶篇),今天咱们一起学习———《Excel教程——如何快速学习掌握VLOOKUP函数(高阶篇)》;为了保证大家能够高效地学习今天的内容,建议大家仔细阅读之前的文章,特别是入门篇!
今天我们将一起学习三个知识点:
VLOOKUP的反向查找;
VLOOKUP函数的多条件查找;
VLOOKUP函数的批量查找。
一、VLOOKUP的反向查找。
一般情况下,VLOOKUP函数只能从左向右查找。但如果需要从右向右查找,则需要把区域进行“乾坤大挪移”,把列的位置用数组互换一下。
例1:现已知每一个身高对应一个人,要求根据如下图所示表中的身高反查姓名,具体如下:
公式:=VLOOKUP(E2,IF({1,0},C2:C9,B2:B9),2,0);
解析:
1)这里正常情况下VLOOKUP是无法实现从右至左的查找,所以用IF数组函数把A/B两列换位后,再按正常从左至右查找。
2)IF({1,0},B:B,A:A)是一个数组函数,在EXCEL函数中使用数组函数,返回的结果也会是一个数组。大括号中的1和0分另代表逻辑值TRUE 和 FALSE,也就是IF函数的判断条件,当为1时,它会返回IF的第一个参数(B列),为0时返回第二个参数(A列),在本例中IF函数返回的结果显示在EXCEL为:{"邓超",1.81;"李晨",2.81;"陈赫",3.81;"郑凯",4.81;"鹿晗",5.81;"王祖蓝",6.81;"杨颖",7.81;"迪丽热巴",8.81};从而实现VLOOKUP的反向查找。
二、VLOOKUP函数的多条件查找
vlookup 本身是无法实现多条件查找的,为了实现多条件查找,我们可以采用变通的方法,将多条件合并为单一条件,从来实现“曲线救国” 的目的。
例2:要求根据姓名和体重查找对应身高。
输入公式=VLOOKUP(E2&F2,IF({1,0},C2:C9&B2:B9,A2:A9),2,0),具体操作:
解析:
1)、F1&F2 把两个条件连接起来,对VLOOKUP来说,它就可以看做是一个整体,也就是单一条件进行查找;
2)、B:B&A:A,把对应的姓名和部门两个条件相连接,作为一个待查找的整体区域;
3)、IF({1,0},B:B&A:A,C:C), 用IF({1,0}把连接后的两列与C列数据合并成一个两列的内存数组:
{"邓超50",1.81;"李晨51",2.81;"陈赫52",3.81;"郑凯53",4.81;"鹿晗54",5.81;"王祖蓝55",6.81;"杨颖56",7.81;"迪丽热巴57",8.81};
4)、完成了查找值及查找范围的重构后,接下来就是VLOOKUP的基本查找功能了,因为公式是以数组的方式计算,所以必须以数组形式输入,即按ctrl+shift后按ENTER输入;如果只按enter输入,就会出现错误结果(在下拉的时候,见动图)。
三、VLOOKUP函数的批量查找
VLOOKUP一般情况下只能一次查找一个值,那么相同条件下有多个对应值该怎么查找,怎么一次性多显示出来呢?
例3要求把如图表中所有陈赫获得跑男MVP时的节目点击率全列出来,输入公式
=VLOOKUP(F$2&ROW(B1),IF({1,0},$B$2:$B$11&COUNTIF(INDIRECT("b2:b"&ROW($2:$11)),F$2),$C$2:$C$11),2,0) ;在公式栏中 按ctrl+shift后按ENTER输入,并在单元格中下接即可。
解析:
1)、F$1&ROW(B1), 公式向下后会变为F$1连接1,2,3,即“陈赫1”,“陈赫2”,“陈赫3”;
2)、使用COUNTIF 函数对区域B2:B8内与F1值相同的数值进行计数,同时再与$B$2:$B$8相连,就可以生成“陈赫1”,“陈赫2”,“陈赫3”的一个区域;
3)、用IF({1,0}把编号后的B列和C列重构成一个两列数组;
4)、使用VLOOKUP的正常功能进行查找。
怎么样,大家理解了么,如果有问题,可以在评论里交流或者私信我哦!