VLOOKUP函数在制作电子表格中的应用

2015-12-27徐保华尹利勇

中国管理信息化 2015年23期

徐保华，尹利勇，郭建

（大连海洋大学，辽宁大连 116023）

1 前言

Excel的查询函数有许多，如 LOOKUP、MATCH、VLOOKUP、HLOOKUP等。其中，VLOOKUP函数的用途最广。它的用途可以分为两类，一类是在制作一个大型数据库的查询系统时，可以不用编程，仅使用VLOOKUP函数就能完成。此时，其可以迅速地在大型数据库中定位，并提取某条记录到查询窗口中。另外一类用途是在制作数据库时，其可以根据查询条件自动创建新的字段。

2 VLOOKUP函数的查找功能与实例

VLOOKUP（lookup＿value，table＿array，col＿index＿num， range＿lookup）函数的参数有四个，将参数翻译后就是VLOOKUP（查找值，查找区域，查找区域的列号，0或1）。其中，“查找值”可以是数值也可以是字符，“查找区域”可以是同一工作表中的某个区域，也可以是不同工作表的区域，“查找区域”必须按“查找值”进行升序排序。“查找区域列号”为纯数值，“查找区域”的首列为1、次列为2，以此类推。最后一个参数可以省略，默认为1（或TRUE），表示模糊查询，定位在最接近查找值的两个数中排序低的那一个。取0（或FALSE）时为精确查询，若不能精确查询到，结果返回为错误标识符＃N／A。VLOOKUP函数最后的结果是与“查找值”对应的那一行上第“列号”上的数据值。

如果查找区域的数据字段是水平分布的，就需要使用HLOOKUP （lookup＿value，table＿array，row＿index＿num，range＿lookup）函数进行查找，参数的意义与VLOOKUP类同，只是此时要用行来代替VLOOKUP中的列。两个函数的第一个字母分别是英文的“垂直”（vertical）和“水平”（Horizontal）的第一个字母。

图1 职工信息查询系统

图1是一个职工信息的查询系统，该系统与数据库（职工库）在不同的工作表中，且“职工库”已经按“职工编号”排序。使用时，只要在D2中输入“职工编号”，将自动地显示该名职工的所有信息，也可以是部分信息。

“姓名”D3 单元格中的函数为：VLOOKUP（D2，职工库！ A2：F160，2，FALSE）。其中，D2 是在查询表中输入“职工编号”的单元格，“职工编号”是查询时的关键字。第二个参数“职工库！A2：F160”是位于“职工库”中的查询区域，可见共有159名职工。第三个参数是“姓名”在查找区域中所在的列号（为2，即B列）。第四个参数为FALSE，表示精确查询。

“性别”D4 单元格中的函数为：VLOOKUP（D2，职工库！ A2：F160，3，FALSE），各参数的含义同上。

3 VLOOKUP函数和HLOOKUP函数的转换功能与实例

除了可以不用编程制作大型数据库查询系统外，更多的应用是利用VLOOKUP或HLOOKUP函数新建数据库字段，下面介绍VLOOKUP函数的另一大类应用。

可以利用IF函数进行学生成绩的五分制和百分制之间的转换。其实，利用VLOOKUP函数或HLOOKUP函数，也可以快捷地实现两种分制之间的转换。其中，HLOOKUP函数与VLOOKUP函数不同之处在于：“查找区域”的字段是沿水平方向的，所以查找方向为水平方向。

图2 用IF函数将五分制成绩转换为百分制成绩

图2所示为用IF函数将五分制的“体育”课成绩转换为百分制。公式为：

IF（E2＝＂优＂，95，IF（E2＝＂良＂，85，IF（E2＝＂中＂，75，IF（E2＝＂及格＂，65，55）））），若使用 VLOOKUP 或 HLOOKUP 函数进行转换，公式不需要嵌套，就会变得很简单。

首先，使用HLOOKUP函数进行转换。此时，还需要添加一个字段呈水平方向排列的“查找区域”（C2：G3），该区域给出了两个分制的对应关系，如图3所示，并按该区域的第1行（五分制）排序完成。注意，汉字是按拼音字母排序的。

图3 用HLOOKUP函数作五分制转百分制

新建一个“体育（百分制）”字段，在G7单元格中输入函数HLOOKUP（F7，SCS2：SGS3，2），其值返回“查找区域”C2：G3 的第2行（百分制）。再向下填充拷贝，即可完成“体育（五分制）”字段中所有数据向百分制的转换。

应用过程中，VLOOKUP和HLOOKUP函数中“查找区域”的位置和数据分布方向没有特别的要求与规定，视方便而定。图4所示为前面例子的逆向转换，即将百分制转换为五分制时的公式和工作表中数据的布局。

图4 用VLOOKUP函数将百分制转换为五分制

由图4可见，纵向的“查找区域”为I8：J12，并已经按该区域的第1列“百分制”排序完成。将F7中的函数VLOOKUP（G7，SIS 8：SJS12，2）向下“填充拷贝”即可完成“体育”（百分制）字段中所有数据向五分制的转换。由于在函数中省略了查询精度参数，则为模糊查找，当得分在60～69时，均取60与70中的低序值60，其他分数类推。