VLOOKUP在工作中的应用

张志华

北京北大方正软件技术学院 河北廊坊 065001

1 问题的提出

在实际工作中，经常会遇到在原表中搜索相关数据的现象；而人事也会根据员工的工龄、职务、学历计算员工的基本工资(企业会给出工龄、职务、学历参照标准)、个人所得税等，企业员工的纳税基数要遵循国家给出的标准。这些工作具有工作量大的特点，既容易出错又枯燥乏味，有什么解决办法呢？在EXCEL中利用“VLOOKUP函数”功能可以巧妙、轻松、快速地加以解决。

从所遇到的实际问题出发，采用“提出问题→找出解决方案→解决问题”的案例教学法，创设师生共同参与的学习环境，将知识讲解和技能训练有机结合，融“教、学、练”于一体，使学生有效地学习。

2 VLOOKUP的功能

VLOOKUP是一个查找函数，查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列的值。它的基本语法为：VLOOKUP(查找什么，到哪个区域查找，返回区域第几列的值，精确查找还是模糊查找)，下面我们通过实例说明各参数的含义。如图1所示，要求根据表二中的姓名，查找计算机应用成绩。

图1 VLOOKUP函数参数说明

公式：B10=VLOOKUP(A10,$B$3:$E$6,3,FALAE)，参数说明如下：

(1)查找什么：是指根据什么查找，可以是指定内容或单元格引用。本例中查找李四即A10单元格。

(2)到哪个区域中找：即到指定的区域中查找，也可以把指定区域先定义一个名称，然后引用这个名称，那么这个区域定义多大合适呢？表二中要返回的值是计算机应用成绩，所以定义的区域必须包括计算机应用成绩这一列，本例中的区域是指：$B$3:$E$6，之所以是使用绝对单元格的引用，是指无论查谁都在这个范围内查找，注意的一点是：参数1即“查找什么”一定要位于该区域的第1列，即查找姓名，所以姓名列必须在查找区域的第1列。也就是$B$3:$E$6，而不是$A$3:$E$6(因为查找的“姓名”不在$A$3:$E$6区域的第1列)[1]。

(3)返回区域中第几列的值，这是VLOOKUP第3个参数，它是一个整数值，它是怎么得来的呢？它是“返回值”在第二个参数给定的区域中的列数，也是我们要找的最终值。本例中我们要返回的是“计算机应用成绩”，它是第二个参数到哪个区域中查找(即$B$3:$E$6)的第3列。这里一定要注意，列数不是在工作表中的列数(不是第4列)，而是在查找区域的第几列。

(4)精确查找还是模糊查找，这是查找的关键，精确即完全一样，模糊即包含的意思。第四个参数如果指定0或FALSE就表示精确查找，而值为TRUE或省略时则表示先精确查找，如果找不到再进行模糊查找。值得注意的一点是：这个参数的应用是和以往函数的参数不一样的地方。

3 VLOOKUP的应用

下面我们通过几个案例说明VLOOKUP函数在日常生活中的应用。

3.1 VLOOKUP精确查询

学生假期去饮料公司打工，经理每天交给他一个当天销售记录表，要求他计算出各饮料的销售额及利润，以便做出决策。所给的饮料价格表有42条记录，当天销售记录表1般在1 300条左右，图2[2]是部分截图，学生起初想通过查找、拷贝及粘贴完成，但因工作量大，易烦躁，易出错而无法进行下去，其实用VLOOKUP函数，很容易解决。

图2 VLOOKUP的精确查询

现在要想计算销售额和毛利润，最主要的是找出饮料的单位、进价及售价。在D22单元格中，插入VLOOKUP函数，如图2所示，在指定区域中查找鲜橙多的单位，计算出来之后，不想按部就班地计算进价及售价，想利用EXCEL的向右填充功能，结果发现进价和售价是错误提示：#N/A，仔细分析一下错误的原因，原来是根据什么查找的对象发生了变化(要查找的是B22即鲜橙多，但在进价公式中却变成了C22数量、售价公式中变成D22单位)，返回的第几列值无变化(始终是第2列)，为此修改如下：D22=VLOOKUP〔$B22,$B$3:$E$17,COLUMN(B1),FALSE〕，其中COLUMN(B1)是返回第B列值的函数即2，这样我们就可以向右填充，找出鲜橙多饮料的进价及售价，然后再向下填充，找出所有饮料的单位、进价及售价。然后利用销售额＝数量*售价；毛利润＝(售价-进价)*数量完成运算。

3.2 VLOOKUP模糊查询

如图3所示，要求根据销售额提成比例表，完成180名员工提成额的计算，不同于图2的是，销售额提成比例表是按等级给出的即分段的形式，而我们要查找的销售额是具体数值，而这个数值有可能多数不在销售额提成比例表中出现，如：要查7 576，在所选区域中找不到7 576数值，但我们知道这个数位于5 000到20 000之间，因此借助VLOOKUP函数的模糊查找，即第4个参数设为true或省略，返回比查找数值小的最大值，也就是比7 576小的数值中，有0，500，2 000，5 000，最大值为5 000，所对应的提成比例为20%，即0.2。值得注意的一点是：所选区域第1列的值必须是按升序排序的，否则无法返回正确的值。

图3 VLOOKUP的精确查询

3.3 VLOOKUP嵌套查询

在人事薪酬的计算中，基本工资构成包括职务工资、工龄工资、学历工资等，那么如何根据所给的员工基本信息表，职务工资表，计算出员工的职务工资呢？如图4所示，由图可知，我们无法根据员工编号直接找到员工的职务工资，但我们可以根据员工编号查找到员工的职务，在职务工资表中，再根据员工的职务，查找到员工的职务工资。也就是我们应用VLOOKUP函数的嵌套功能。首先，我们根据员工编号，在指定区域$A$3:$D$10中查找，返回指定区域第4列的值即职务，然后以职务为查找目标，再在指定区域$A$14:$B$23查找，返回职务工资所在区域的第2列的值即职务工资。

图4 VLOOKUP的嵌套查询

4 结束语

VLOOKUP是一个查找函数，查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列的值。使用此函数注意以下几点：

(1)要查找的对象(参数1)一定要定义在查找数据区域(参数2)的第1列。

(2)如果为FALSE或0，VLOOKUP将只寻找精确匹配值。在此情况下，查找所在区域第一列的值不需要排序。如果查找区域第一列中有两个或多个值与查找目标匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。

(3)如果为TRUE或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于查找对象的最大数值。查找区域第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。

[1]兰色幻想[EB/OL].http://www.excelpx.com/HOME/show.aspx?cid=10&id=31325.

[2]许晞.计算机应用基础[M].北京:高等教育出版社,2007.