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

徐保华，尹利勇，郭 建

（大连海洋大学，辽宁 大连 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，其他分数类推。

4 结语

VLOOKUP函数或HLOOKUP函数以其灵活多变的查询功能和简捷的应用格式在实际工作中应用广泛，除了可以快速查询大型数据库中的信息外，在制作数据库的过程中更展示出他们的强大功能。