首页 > Excel专区 > Excel教程 >

10个示例让你的VLOOKUP函数应用从入门到精通(上)

Excel教程 2022-01-06 21:10:27

VLOOKUP函数是众多的Excel用户最喜欢和最常用的函数之一,因此介绍VLOOKUP函数使用技巧的文章也特别多。我们学习了VLOOKUP函数的语法及应用,在Excel公式与函数之美前面的系列文章中,我们又详细探讨了VLOOKUP函数的4个参数。

熟练掌握VLOOKUP函数的使用,是Excel必备技能之一。下面我们通过10个示例,进一步巩固VLOOKUP函数的使用技能。

概述

VLOOKUP函数最擅长在列中查找相匹配的数据,若找到匹配的数据,则在找到的数据所在行的右边从指定的列中获取数据。

示例1:查找郭靖的数学成绩

如图1所示,在最左边的列中是学生的姓名,在列B至列E中是不同科目的成绩。

图1

现在,我需要从上面的数据中找到郭靖的数学成绩。公式为:

=VLOOKUP(“郭靖“,$A$3:$E$10,2,0)

公式有4个参数:

“郭靖”——要查找的值。

$A$3:$E$10——查找的单元格区域。注意,Excel在最左列搜索要查找的值,本例中在A3:A10中查找姓名郭靖。

2——一旦找到了郭靖,将定位到区域的第2列,返回郭靖所在行相同行的值。数值2指定从区域中的第2列查找成绩。

0——告诉VLOOKUP函数仅查找完全匹配的值。

以上面的示例来演示VLOOKUP函数是如何工作的。

首先,在区域的最左列查找郭靖,从顶部到底部查找并发现在单元格A7中存储着这个值。

图2

一旦找到该值,就会到右边第2列,获取其中的值。

图3

可以使用相同结构的公式来获取任意学生任一科目的成绩。

例如,查找杨康的化学成绩,公式为:

=VLOOKUP(“杨康“,$A$3:$E$10,4,0)

图4

在上面的示例中,查找值(学生姓名)在公式中是包含在引号中的,也可以使用包含查找值的单元格引用。使用单元格引用可以创建动态公式。

例如,如果在某单元格中放置要查找的学生姓名,使用公式来查找该学生的数学成绩,那么当修改学生姓名时,查找的结果将自动更新。

图5

如果在最左边的列中没有找到查找值,那么返回错误值#N/A。

示例2:双向查找

在示例1中,列数值采用了“硬编码”,使用2作为列索引值,因此公式总是返回数学成绩。

如果想要查找值和列索引值都是动态的,如下图6所示,修改学生姓名或者科目时,VLOOKUP函数获取相应的成绩。

图6

要创建双向查找公式,需要使列也是动态的。这样,当用户修改科目时,公式自动获取正确的列,例如数学是第2列,物理是第3列。

此时,需要使用MATCH函数作为列参数,公式为:

=VLOOKUP(A14,$A$3:$E$10,MATCH(B13,$A$2:$E$2,0),0)

公式中使用MATCH(B13,$A$2:$E$2,0)作为列的数值。MATCH函数接受科目作为查找值(单元格B13),返回该值在A2:E2中的位置。因此,如果查找数学,则返回2。

示例3:使用下拉列表作为查找值

在上面的示例中,我们手工输入数据,耗时且易出错,特别是有许多查找值时。

一种好的方法是创建查找值列表,然后只需从列表中选择即可。

图7

在单元格B14中的公式仍然为:

=VLOOKUP(A14,$A$3:$E$10,MATCH(B13,$A$2:$E$2,0),0)

查找值在下拉列表中,这些下拉列表是使用Excel的数据有效性功能创建的。选择单元格A14,单击“数据——数据有效性”,在“数据有效性”对话框中设置为“序列”,来源选择单元格区域A3:A10。同样的方法设置单元格B13的下拉列表。

示例4:三向查找

在示例2中,使用了一个包含不同学科学生成绩的查找表,是一个使用两个变量(学生姓名和学科名称)双向查找学生成绩的示例。

现在,假设一年中,学生有三种不同的测试:单元测试、期中测试和期末测试。那么,三向查找就是从指定测试中获取学生指定科目的成绩。如下图8所示。

图8

在图8的示例中,VLOOKUP函数可以查找三个不同的表(单元测试、期中测试和期末测试),返回其中某学生的某学科的成绩。

在单元格H4中的公式为:

=VLOOKUP(G4,CHOOSE(IF(H2=”单元测试“,1,IF(H2=”期中测试“,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

公式使用CHOOSE函数来确定要引用的表。公式中的CHOOSE函数为:

CHOOSE(IF(H2=”单元测试“,1,IF(H2=”期中测试“,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

第1个参数是IF(H2=”单元测试“,1,IF(H2=”期中测试“,2,3)),检查单元格H2中的值,返回要选择各类测试表所对应的数值。如果是“单元测试”,则返回1,CHOOSE函数返回单元格区域$A$3:$E$7;如果是“期中测试”,则返回2,否则返回3,分别对应着单元格区域$A$11:$E$15$A$19:$E$23

示例5:获取位于列表最后的值

可以创建VLOOKUP公式来获取位于列表最后一个位置的数字值。

在Excel中可以使用的最大的正数是9.99999999999999E+307,这意味着在VLOOKUP函数中最大的查找数也是这个数。几乎不会涉及到如此大的一个数的计算,但可以使用来获取列表中最后一个数字。

如图9所示,在单元格区域A1:A14中有一组数,想要获取列表中最后一个数,即1514。

图9

公式为:

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,1,TRUE)

注意到,公式使用了近似匹配,并且列表也没有排序。

下面是使用了近似匹配的VLOOKUP函数的工作原理。VLOOKUP函数从顶到底搜索最左侧的列:

如果发现一个精确匹配的值,则返回该值。

如果发现一个高于查找值的值,则返回该值所在单元格上方单元格中的值。

如果查找值大于列表中所有的值,则返回最后一个值。

由于9.99999999999999E+307是Excel中可以使用的最大数,将该数用作查找值时,从列表中返回最后一个数字。

同样的原理也可以用于返回列表中最后一个文本项。如图10所示。

图10

公式为:

=VLOOKUP(“zzz”,$A$1:$A$8,1,TRUE)

Excel查找所有的名字,由于zzz比任何文本都大,因此返回列表中最后一个文本项。


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.