2017年第11期
99研究与探讨
信息技术与信息化
ＶＬＯＯＫＵＰ函数高级应用探究
刘模 * LIU Mo-qun
摘　要          公式与函数是Excel 的核心内容之一，
其中的VLOOKUP 函数在数据的搜索与筛选方面体现了较强的功能。针对高级查的使用不足，从模糊查、逆向查、多条件查以及查多项值四个方面进行了阐述，提出了将查的数据区域重构成一个数组的方案，可以很好地解决VLOOKUP 函数基本参数使用欠缺的问题，并用实例形象地加以了阐明。
关键词        VLOOKUP 函数；查；高级应用
doi：10.3969/j.issn.1672-9528.2017.11.028
* 常州工学院计算机信息工程学院 江苏常州 213032
1 VLOOKUP 函数简介
目前，在财务管理方面VLOOKUP 函数使用已经非常普遍。在分析该函数的高级应用之前，还是有必要简单介绍一下其基本语法结构。
VLOOKUP(在数据表第一列中要查的值, 查的数据表范围, 返回的值在查范围的第几列, 精确匹配/模糊匹配)
该函数是垂直查，即纵向列查。其功能通俗地讲就是：根据要查的值(第1个参数)，在查范围(第2个参数)的第一列搜索查该值，到该值后就能确定所在行，然后返回查范围相同行上指定列(第3个参数)的单元格的值。在我们的工作中，第4个参数通常都设置为False 或者0，表示精确匹配，精确即完全一样。如果在使用过程中，该参数写为False，则大多数人在用的过程中一般都不会出错。因为该函数现在使用已经很广泛，而且普通的用法大家掌握得都比较好，所以这样的实例在这里就不列举了。但是，第4个参数设置为True 或者1或者省略掉，则返回值常常会出乎人们的预料。2 模糊查2.1对数值的匹配
默认缺省第4个参数时为模糊匹配。模糊查中，对数值的匹配原理是：给定一个要查的数，在查区域内到和它最接近，但不大于它的那个数，最后按要求返回相应值。数值模糊查有两个基本规则：一是查数据范围的第1列必须是数字，二是该数字列必须按照从小到大排好顺序，杂
乱无章的数字顺序是无法准确查的。
如图1所示，它是我国实行的7级超额累进个人所得税税率表。工资个税的计算公式为：应纳税额=应纳税所得额×适用税率－速算扣除数，个税免征额是3500。
例1：要求根据图1中的个人税率表和应税收入，查每个人的适用税率，并计算每个人的应纳税额。
图1
适用税率公式：
C13=VLOOKUP(B13,$A$3:$D$9,3,TRUE) 应纳税额公式：
D13=B13*C13-VLOOKUP(B13,$A$3:$D$9,4)
适用税率公式中在查2000时，因为在税额区间中不到等于2000的精确值，所以VLOOKUP 只选比查值小并且最接近的值1500，最后返回1500所对应的税率10%。应纳税额公式中的“速算扣除数”也是类似的，只不过返回的是查区域的第4列罢了，该公式省略了VLOOKUP 的最后一个参数，等价于把第4个参数设置成了1或TRUE。2.2 对字符的匹配
有的时候只需查单元格内容的部分关键字或者简称，
2017年第11期100
研究与探讨
信息技术与信息化
比如说领用教材时填写的是完整的书名，但为了便捷，查时只要根据书名关键字查询领用人，这就是对字符的匹配问题。
例2：如图2所示的是某校教材领用登记表，要求根据领用教材的部分关键字内容“Basic”查最早的领用人，已知领用日期是按照顺序登记的。
公式：F2 = VLOOKUP(“*”&E2&”*”,$A$2:$C$6,3,-
FALSE)
图2
VLOOKUP 的第一个参数允许使用通配符来表示包含的意思，“*”代表多个任意字符，“？”代表1个任意字符。“&”是文本连接符，”*”&E2&”*”就是”*Basic*”，用这个查值就会自动地查到第一条匹配的记录`。查时在数据区域的A3单元格停下，就是到了跟这个关键字最接近且最早的记录了，返回领用人姓名“张军”。
要注意的是，这里是对字符关键字的模糊查，也就是说不需要知道书的完整名称，只需要知道书名的部分字符就可查，但其VLOOKUP 查方法的实质还是精确匹配，因为第4个参数是FALSE。3 逆向与多条件查3.1逆向查
通常情况下使用VLOOKUP 函数，被查询的值需要位于数据源的首列，也就是按列从左到右进行查，但是如果要反向从右到左逆向查应该如何解决呢？VLOOKUP 函数本身是不能直接实现逆向查的，由于IF 函数的参数支持数组，所以可以利用IF 函数的数组效应，把两列交换位置后再重新组合，最终间接实现从右到左查。
例3：要求根据如图3所示的领用人姓名，在图2的A1:C6教材领用登记表中，逆向查教材的领用日期。
公
式：B9 = VLOOKUP(A9,IF({1,0},$C$2:$C$6,$B$2:
$B$6),2,FALSE)
图3
公式中函数IF({1,0},$C$2:$C$6,$B$2:$B$6)使用了数组，其返回的结果也是一个数组。其中的1表示真，即TRUE；0表示假，即FALSE。当为1时，它会返回IF 函数的第二个参数，即C 列；当为0时，返回IF 函数的第三个参数，
即B 列。由于数组运算后返回数组，所以IF 执行后返回数组为：{"李立奋",43008;"张军",42979;"黎上伟",42984;"钱春利",42993;"蔡晓",42998}，其中的分号“;”表示后面的要换行，即5行2列的数组。通过在编辑栏中选中IF-({1,0},$C$2:$C$6,$B$2:$B$6)，按F9后可以查看到该结果。特别注意的是，这里IF 返回的不是单元格区域，返回的数组中43008相当于日期2017-9-30、42979相当于日期2017-9-1，依此类推…，最后通过VLOOKUP 查到第2列的指定值。3.2 多条件查
VLOOKUP 函数实现多条件查是可以借助于数组来完成的，多个查条件可以用“&”运算符连接在一起，两列数据区域同样也可以连接成一列数据，通过IF 函数重构一个数组，最后组合成一个查的数据
范围。
例4：如图4所示是选修课的开课情况表，要求在E2:F3的区域中，根据课程名称和教师两个条件查选修课的具体上课地点。
G2单元格中的公式为：
{=VLOOKUP(E2&F2, IF({1,0},$A$2:$A$6&$B$2:$B$6,$C
$2:$C$6), 2, FALSE)}
图4
把两个条件通过E2&F2连接在一起，作为一个整体，形成一个组合条件。查区域中的“课程名称”列和“教师”列，通过$A$2:$A$6&$B$2:$B$6也连接在一起，形成数据区域的第1个完整列。IF({1,0},$A$2:$A$6&$B$2:$B$6,$C$2:$C$6)通过IF 函数重构一个数组，把连接后的第1个完整列与C 列合并，形成一个5行2列的数组。即：{"音乐欣赏杨兰宣","艺A305";"网页设计唐鹏","B309机";"音乐欣赏常龙飞","艺媒1";"网页设计吴呼建","B408机";"音乐欣赏杨小仪","艺A309"}，最后通过VLOOKUP 完成查功能。需要注意的是，由于输入的是数组公式，涉及到$A$2:$A$6&$B$2:$B$6运算，所以结束输入时必须要同时按Ctrl+Shift+Enter 三键，公式两边会自动加上{}，若自己键入{}则Excel 会认为输入的
是一个正文标签。另外，为了能将公式直接复制到G3单元格，所以“查数据范围”参数使用的都是绝对地址。4 多项查
通常情况下，VLOOKUP 函数查后返回1个有效值。如
2017年第11期
101研究与探讨
信息技术与信息化
个税查询果对某个要查的项，其有效的返回值有多个该怎么完成呢？由于多项查涉及到几个相关函数，所以下面先简单地介绍一下这几个函数的一般用法。4.1 涉及到的几个函数
ROW()函数：该函数返回引用的行号。例如ROW(A1) ，因为引用所在行的行号是1，所以函数返回值为1。若引用是单元格区域，则公式一般以数组公式的形式输入。例如鼠标选中E1:E3单元格区域，编辑栏中输入数组公式{=ROW(G3:J5)}，则返回值为{3;4;5}；如果不以数组公式的形式输入该公式，则只返回单个结果值3。再如数组公式{=ROW($2:$7)}，其返回值为数组{2;3;4;5;6;7}。
INDIRECT()函数：该函数的功能是返回并显示指定引用的内容。这里只提后面多项查实例中，用到的那部分INDIRECT 函数功能，所以该函数的第2个参数这里采用默认缺省值。例如在如图4的数据中：只需要对单元格B3进行引用，使用公式INDIRECT(“B3”)返回值为“唐鹏”；使用数组公式{=INDIRECT(“B3:B5”)，则返回数组为{"唐鹏";"常龙飞";"吴呼建"}。
COUNTIF()函数：该函数的功能是，计算区域中满足给定条件的单元格的个数。例如在如图4的数据中，公式COUNTIF(A2:A6,A2)的返回值为3。因为在A2:A6单元格区域中，只有3个单元格的值满足A2条件，即：其值为“音乐欣赏”。4.2 实例
例5：如图5所示是各销售点家电数量的销售情况表，要求把表中“电冰箱”的所有“销售数量”全部罗列出来。
表中“电冰箱”出现了三次，要想实现查返回多个值，就必须把这三个“电冰箱”区分开来。可以对查的内容进行编号，第一个出现的电冰箱后面加上数字1成为“电冰箱1”，第二个出现的设定为“电冰箱2”，依此类推。
G2单元格中的公式为：
{=VLOOKUP(F$2&ROW(A1),IF({1,0},$C$2:$C$7&C OUNTIF(INDIRECT(“C2:C”&ROW($2:$7)),F$2),$D$2:$D$7),2,FALSE)}
图5
VLOOKUP 的查目标F$2&ROW(A1)用来连接序号，其值为“电冰箱1”。公式向下复制时，根据相对地址会变成
F$2&ROW(A2)，其值为“电冰箱2”等。
查区域中的电冰箱也要统一进行编写序号，通过”C2:C”&ROW($2:$7)可以逐行扩充区域{“C2:C2”;”C 2:C3”;”C2:C4”;”C2:C5”;”C2:C6”;”C2:C7”}，在这个不断扩充的区域中来统计“电冰箱”的个数并最终生成序号。比如{=INDIRECT(“C2:C3”)}引用区域为C2:C3，其内容为数组{"空调";"电冰箱"}。COUNTIF(INDI-RECT(“C2:C3”),F$2)在扩充的C2:C3区域内，统计出“电冰箱”的个数为1。
$C$2:$C$7连接上统计好的个数，就可以对查区域中的所有“电冰箱”依次进行编号。在编辑栏中选中$C$2:$C$7&COUNTIF(INDIRECT(“C2:C”&ROW($2:$7)),F$2)，按F9键可以看到重新编号后的C 列为：{"空调0";"电冰箱1";"空调1";"电冰箱2";"电视2";"电冰箱3"}。
IF({1,0}…把编号后的产品名称C 列和销售数量D 列重构成一个两列数组，最后用VLOOKUP 就能查到不同序号的电冰箱对应的销售数量了，通过这种方式能够解决查“电冰箱”的多项销售数量值的问题。5 结束语
Excel 软件提供了大量的函数，使得在数据处理方面具有很好的功能性和灵活性。在数据的查与筛选方面，VLOOKUP 属于重要和得力的一种函数。但是VLOOKUP 函数四个参数的设置方法是固定的，要实现高级查就必须利用数组功能并结合一些函数，来重构查内容和查区域。对已有的基本数据进行整合，有助于更好地体现VLOOKUP 函数的特。每个函数都有自己的长处和短处，在表格数据已经确定的前提下，以上的思路和技巧只是提供一种解决方案。其实也可以让数据来适应函数，比如提高数据录入的规范性，以便于函数直接进行处理。不管采用何种方法，最终的目的都是简化难度提高工作效率。
参考文献：
[1] 于双元.全国计算机等级考试二级教程----MS Office 高级应用[M].北京：高等教育出版社，2016.
[2] 冯梅，吴静. Vlookup 函数的应用[J].成都航空职业技术学院学报，2013(4):57-59.
[3] 董亚辉. Excel 表格处理：实例版[M].北京：电子工业出版社，2015.
【作者简介】刘模（1967-），男，江西南昌人，讲师，硕士，研究方向为计算机软件及应用。
（收稿日期：2017-10-23）