表格必备技巧: VLOOKUP函数常用套路大全
2018-01-01 来源:互联网 |
本站每天都会更新最新的内容,请持续关注我们,建议收藏我们网站

函数的语法为:

VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)

第一参数是要在表格或区域的第一列中查询的值。

第二参数是需要查询的单元格区域,这个区域中的首列必须要包含查询值,否则公式将返回错误值。如果查询区域中包含多个符合条件的查询值,VLOOKUP函数只能返回第一个查找到的结果。

第三参数用于指定返回查询区域中第几列的值,该参数如果超出待查询区域的总列数,VLOOKUP函数将返回错误值#REF!,如果小于1返回错误值#VALUE!。

第四参数决定函数的查找方式,如果为0或FASLE,用精确匹配方式,而且支持无序查找;如果为TRUE或被省略,则使用近似匹配方式,同时要求查询区域的首列按升序排序。

1、常规查询

如图,需要从B~E的数据表中,根据H3单元格的姓名查询对应的职务。

公式为:

=VLOOKUP(H3,C:E,3,0)


提示:

VLOOKUP函数第三参数中的列号,不能理解为工作表中实际的列号,而是指定要返回查询区域中第几列的值。

2、带通配符的查询

如图,需要从B~E的数据表中,根据H3单元格的姓氏,查询对应的姓名和部门。

公式为:

=VLOOKUP($H3&"*",$C:$E,COLUMN(A1),0)


提示:

通配符“*”表示任意多个字符,VLOOKUP函数第一参数使用$H3&"*",即在C列中查询以H2单元格内容开头的内容,并返回对应列的信息。

3、近似查询

如图,需要根据H~I的对照表,判断D列成绩对应的评议结果。

公式为:

=VLOOKUP(D2,H:I,2)


提示:

VLOOKUP函数第四参数被省略,在近似匹配模式下返回查询值的精确匹配值或近似匹配值。如果找不到精确匹配值,则返回小于查询值的最大值。

使用近似匹配时,查询区域的首列必须按升序排序,否则无法得到正确的结果。

4、逆向查询

如图,需要从B~E的数据表中,根据H3单元格的部门,查询对应的姓名。

公式为:

=VLOOKUP(H3,CHOOSE({1,2},D2:D11,C2:C11),2,0)


提示:

VLOOKUP函数的查询值要求必须位于查询区域中的首列,如果被查找值不在数据表的首列时,需要先将目标数据进行特殊的转换。

CHOOSE函数第一参数使用常量数组{1,2},将查询值所在的D2:D11和返回值所在的C2:C11整合成一个新的两列多行的内存数组。

生成的内存数组符合VLOOKUP函数的查询值必须处于数据区域中首列的要求。VLOOKUP函数以职务做查询条件,在内存数组中查询并返回对应的姓名信息,从而实现了逆向查询的目的。

5、多条件查询

如图,需要从B~E的数据表中,根据H3单元格的部门和I3单元格的职务,查询对应的姓名。

J3单元格公式为:

=VLOOKUP(H3&I3,IF({1,0},D2:D11&E2:E11,C2:C11),2,)


提示:

使用连接符“&”将部门和职务合并成新的字符串,以此作为VLOOKUP函数的查询条件。

IF部分,先将D列的部门和E列的职务进行连接,再使用IF({1,0}的方式,构造出部门职务在前、姓名在后的内存数组。

VLOOKUP函数在IF函数构造出的内存数组首列中查询部门职务字符串的位置,返回对应的姓名。

数组公式,不要忘了按组合键。

6、一对多查询

如图,需要从B~E的数据表中,根据H3单元格的职务,查询对应的多个姓名。


首先在A2单元格输入以下公式,向下复制:

=(E2=$H$3)+A1


然后在I3单元格输入以下公式,向下复制:

=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")


提示:

C列的职务每重复出现一次,A列的序号增加1。

VLOOKUP函数使用1至N的递增序列作为查询值,使用A:C列作为查询区域,以精确匹配的方式返回与之相对应的B列的姓名。注意查找区域必须由辅助列A列开始。

最后将辅助列字体设置为白色或进行隐藏即可。

7、按指定次数重复数据

如图,需要根据C列指定的次数,重复显示B列的内容。


首先在A2单元格输入以下公式,向下复制:

=A1+C2


然后在E2单元格输入以下公式,向下复制:

=IFERROR(VLOOKUP(ROW(A1),A:B,2,0),E3)&""


今天的分享就是这些,祝小伙伴们一天好心情!更多表格技巧,请关注:EXCELers

作者:祝洪忠

小编推荐

【教程】2017康复治疗师视频资料
教育培训康复治疗专业大专以上学历。现为卫生部和人力资源部颁发的卫生专业技术“康复医学治疗技术”资格证书。工作经验熟悉人体解剖学知识,了解各器官功能位。熟练掌握各种康复技术,应用现代康复仪器,并能明确仪
【教程】【炸裂】5个极度烧脑又极其实用的函数公式
今天和大家分享的几条函数公式,一个比一个烧脑,但又非常实用。因为烧脑,所以计算原理和过程我们就不解释了。因为实用,所以建议大家收藏,用到的时候可以直接拿去套用。1,文本格式的时间转换。烧脑指数★如下图
【教程】表格必备技巧: VLOOKUP函数常用套路大全
函数的语法为:VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)第一参数是要在表格或区域的第一列中查询的值。第二参数是需要查询的单元格区域,这个区域中的首列必须要包含查询值,否则公
【书籍】北京人艺话剧整理 28部
1.窝头会馆:
【书籍】教师必备期末工作清单!每一位老师都需要!
一、提前拟好复习计划给孩子上好复习课,让孩子达到温故知新的效果,是我们期末工作中最重要的事情。但一学年的知识如何总结?如何帮孩子查缺补漏?就需要咱们老师提前做好复习计划了。1、分析教材:帮助学生把课本
【书籍】怎样写好论文?一位大学教授、审稿专家的写作经验
 作者:陆际联,现任北京理工大学博士生导师,机械工程与自动化学院院长,机器人研究中心主任给全国十五、六种学报、杂志审稿占用了我业余生活的大部分时间,每年的审稿量少说有100篇。近年来,觉得稿件质量大不
【综合】秋招达能管培面经
时间:2017.7首先我想感谢达能给我这次机会让我更清晰的认识自己。我应聘的是达能纽迪西亚市场管培生,传说中是要求很强的领导力和沟通能力,但是到底又多强呢,会会才知道。下面跟大家分享一下悲催的求职经验
【综合】雀巢秋招面经分享
1时间:2017.7网申过后HR打电话进行一轮电面,比较简单;之后进入群面,小组讨论并展示,在这个过程中HR会根据你的表现来决定你是否有机会进入下一轮,其中会用到英语;之后进入一对一面试,由部门的全国
【综合】中信银行管培生的面试经验
时间:2016.12背景:LZ是东莞人,刚刚拿到中信东莞分行的OFFER管理培训生。听说很多东莞的银行都会倾向于招本地人,但感觉中信没有,至少在终面是看到很多都不是东莞人,最终结果暂时还不知道。面试流
【考试】教资笔试中小学模考(第一季)
①15:00,@韩老师Teacher 教资笔试中小学模考(第一季)-科目一综合素质模考大解析 ②19:00,@孙阳爱学习 教资笔试中小学模考(第一季)-科目二:教育知识与能力(中学) ③19:00,@
【考试】注会综合阶段课程(双网校)
第一阶段第一阶段设有会计、审计、财务成本管理、公司战略与风险管理、经济法、税法等6科。这是专业阶段,主要测试考生是否具备注册会计师执业所需的专业知识,是否掌握基本技能和职业道德要求。(可以分科报考,但
【考试】非法本零基础司考弯路/经验帖
【自我请况介绍】本科心理学,大四在读,非法本零基础准备司考,三月份开始备考,首战367。我知道看到这里一定会有小伙伴会说这么低的分还写经验贴,因为最开始没有一起备考的小伙伴也不认识法学院的朋友没有人帮
【公务员考试】公务员备考之申论“加油站”(在线观看)
免费学习:
【公务员考试】公务员零基础学员专享公益课(在线观看)
免费学习:
【公务员考试】资料分析速度上不去怎么办?
一些小伙伴问我,老师,我资料分析也每天在练,但是为什么正确率忽高忽低?速度还是比较慢呢?我觉得问题应该是出在以下几个方面:问题1:资料分析准确率还不错,但是速度慢(4篇,35-40分钟)怎么办?小齐说
【考研】2017北京语言大学英语口译-关于择校 备考过程用书 复试和调剂
之前有关注过我的同学都知道我刚开始的时候虽然知道自己进了北语的复试但是为了求稳妥还是先确认了另一所的待录取。之后我内心真的觉得自己的位置不应该在那儿,为了北语付出了太多我不能就这样放弃,于是我联系了北
【考研】调剂经验 | 考研调剂的几个阶段及所需的准备
声明:本文为@城南考研 原创,未经许可,禁止转载。第一阶段:成绩出来--分数线出来  考研成绩出来后,大家可以根据报考学校以前的分数线、招生人数情况判断自己是否能参加复试,最好是之前认识一些报考学校的
【考研】高等数学同济七版的考研同学必做课后习题完整版
同济六版的教材也可以第一章习题1-1:2,5,6,13,习题1-2:2,3,6,7,8习题1-3:1,2,3,4,7,12习题1-4:1,5,6习题1-5:1,2,3,4,5习题1-6:1:(5),(
【语言】2018年6月六级寒假备考全规划
课程目录1四六级寒假过级学习规划石雷鹏无限次播放2六级听力大救赎Vivian无限次播放3六级阅读满分养成计划宋利辉无限次播放4先人一步,六级写译高分规划袁雨斌无限次播放免费学习:
【语言】2018年6月四级寒假备考全规划
课程目录1四六级寒假过级学习规划石雷鹏无限次播放2四级听力大救赎李尚龙无限次播放3如何拯救你的四级写译?石雷鹏无限次播放4四级阅读满分养成计划尹延无限次播放免费学习:
【语言】标准美音训练及语音思维的建立(在线观看)
免费学习:

赞助会员