VLOOKUP函數(shù)在制作電子表格中的應(yīng)用

徐保華，尹利勇，郭 建

（大連海洋大學(xué)，遼寧 大連 116023）

1 前言

Excel的查詢函數(shù)有許多，如 LOOKUP、MATCH、VLOOKUP、HLOOKUP等。其中，VLOOKUP函數(shù)的用途最廣。它的用途可以分為兩類，一類是在制作一個(gè)大型數(shù)據(jù)庫(kù)的查詢系統(tǒng)時(shí)，可以不用編程，僅使用VLOOKUP函數(shù)就能完成。此時(shí)，其可以迅速地在大型數(shù)據(jù)庫(kù)中定位，并提取某條記錄到查詢窗口中。另外一類用途是在制作數(shù)據(jù)庫(kù)時(shí)，其可以根據(jù)查詢條件自動(dòng)創(chuàng)建新的字段。

2 VLOOKUP函數(shù)的查找功能與實(shí)例

VLOOKUP（lookup＿value，table＿array，col＿index＿num， range＿lookup）函數(shù)的參數(shù)有四個(gè)，將參數(shù)翻譯后就是VLOOKUP（查找值，查找區(qū)域，查找區(qū)域的列號(hào)，0或1）。其中，“查找值”可以是數(shù)值也可以是字符，“查找區(qū)域”可以是同一工作表中的某個(gè)區(qū)域，也可以是不同工作表的區(qū)域，“查找區(qū)域”必須按“查找值”進(jìn)行升序排序。“查找區(qū)域列號(hào)”為純數(shù)值，“查找區(qū)域”的首列為1、次列為2，以此類推。最后一個(gè)參數(shù)可以省略，默認(rèn)為1（或TRUE），表示模糊查詢，定位在最接近查找值的兩個(gè)數(shù)中排序低的那一個(gè)。取0（或FALSE）時(shí)為精確查詢，若不能精確查詢到，結(jié)果返回為錯(cuò)誤標(biāo)識(shí)符＃N／A。VLOOKUP函數(shù)最后的結(jié)果是與“查找值”對(duì)應(yīng)的那一行上第“列號(hào)”上的數(shù)據(jù)值。

如果查找區(qū)域的數(shù)據(jù)字段是水平分布的，就需要使用HLOOKUP （lookup＿value，table＿array，row＿index＿num，range＿lookup）函數(shù)進(jìn)行查找，參數(shù)的意義與VLOOKUP類同，只是此時(shí)要用行來(lái)代替VLOOKUP中的列。兩個(gè)函數(shù)的第一個(gè)字母分別是英文的“垂直”（vertical）和“水平”（Horizontal）的第一個(gè)字母。

圖1 職工信息查詢系統(tǒng)

圖1是一個(gè)職工信息的查詢系統(tǒng)，該系統(tǒng)與數(shù)據(jù)庫(kù) （職工庫(kù)）在不同的工作表中，且“職工庫(kù)”已經(jīng)按“職工編號(hào)”排序。使用時(shí)，只要在D2中輸入“職工編號(hào)”，將自動(dòng)地顯示該名職工的所有信息，也可以是部分信息。

“姓名”D3 單元格中的函數(shù)為：VLOOKUP（D2，職工庫(kù)！ A2：F160，2，F(xiàn)ALSE）。其中，D2 是在查詢表中輸入“職工編號(hào)”的單元格，“職工編號(hào)”是查詢時(shí)的關(guān)鍵字。第二個(gè)參數(shù)“職工庫(kù)！A2：F160”是位于“職工庫(kù)”中的查詢區(qū)域，可見(jiàn)共有159名職工。第三個(gè)參數(shù)是“姓名”在查找區(qū)域中所在的列號(hào)（為2，即B列）。第四個(gè)參數(shù)為FALSE，表示精確查詢。

“性別”D4 單元格中的函數(shù)為：VLOOKUP（D2，職工庫(kù)！ A2：F160，3，F(xiàn)ALSE），各參數(shù)的含義同上。

3 VLOOKUP函數(shù)和HLOOKUP函數(shù)的轉(zhuǎn)換功能與實(shí)例

除了可以不用編程制作大型數(shù)據(jù)庫(kù)查詢系統(tǒng)外，更多的應(yīng)用是利用VLOOKUP或HLOOKUP函數(shù)新建數(shù)據(jù)庫(kù)字段，下面介紹VLOOKUP函數(shù)的另一大類應(yīng)用。

可以利用IF函數(shù)進(jìn)行學(xué)生成績(jī)的五分制和百分制之間的轉(zhuǎn)換。其實(shí)，利用VLOOKUP函數(shù)或HLOOKUP函數(shù)，也可以快捷地實(shí)現(xiàn)兩種分制之間的轉(zhuǎn)換。其中，HLOOKUP函數(shù)與VLOOKUP函數(shù)不同之處在于：“查找區(qū)域”的字段是沿水平方向的，所以查找方向?yàn)樗椒较颉?/p>

圖2 用IF函數(shù)將五分制成績(jī)轉(zhuǎn)換為百分制成績(jī)

圖2所示為用IF函數(shù)將五分制的“體育”課成績(jī)轉(zhuǎn)換為百分制。公式為：

IF（E2＝＂優(yōu)＂，95，IF（E2＝＂良＂，85，IF（E2＝＂中＂，75，IF（E2＝＂及格＂，65，55）））），若使用 VLOOKUP 或 HLOOKUP 函數(shù)進(jìn)行轉(zhuǎn)換，公式不需要嵌套，就會(huì)變得很簡(jiǎn)單。

首先，使用HLOOKUP函數(shù)進(jìn)行轉(zhuǎn)換。此時(shí)，還需要添加一個(gè)字段呈水平方向排列的“查找區(qū)域”（C2：G3），該區(qū)域給出了兩個(gè)分制的對(duì)應(yīng)關(guān)系，如圖3所示，并按該區(qū)域的第1行（五分制）排序完成。注意，漢字是按拼音字母排序的。

圖3 用HLOOKUP函數(shù)作五分制轉(zhuǎn)百分制

新建一個(gè)“體育（百分制）”字段，在G7單元格中輸入函數(shù)HLOOKUP（F7，SCS2：SGS3，2），其值返回“查找區(qū)域”C2：G3 的第2行（百分制）。再向下填充拷貝，即可完成“體育（五分制）”字段中所有數(shù)據(jù)向百分制的轉(zhuǎn)換。

應(yīng)用過(guò)程中，VLOOKUP和HLOOKUP函數(shù)中“查找區(qū)域”的位置和數(shù)據(jù)分布方向沒(méi)有特別的要求與規(guī)定，視方便而定。圖4所示為前面例子的逆向轉(zhuǎn)換，即將百分制轉(zhuǎn)換為五分制時(shí)的公式和工作表中數(shù)據(jù)的布局。

圖4 用VLOOKUP函數(shù)將百分制轉(zhuǎn)換為五分制

由圖4可見(jiàn)，縱向的“查找區(qū)域”為I8：J12，并已經(jīng)按該區(qū)域的第1列“百分制”排序完成。將F7中的函數(shù)VLOOKUP（G7，SIS 8：SJS12，2）向下“填充拷貝”即可完成“體育”（百分制）字段中所有數(shù)據(jù)向五分制的轉(zhuǎn)換。由于在函數(shù)中省略了查詢精度參數(shù)，則為模糊查找，當(dāng)?shù)梅衷?0～69時(shí)，均取60與70中的低序值60，其他分?jǐn)?shù)類推。

4 結(jié)語(yǔ)

VLOOKUP函數(shù)或HLOOKUP函數(shù)以其靈活多變的查詢功能和簡(jiǎn)捷的應(yīng)用格式在實(shí)際工作中應(yīng)用廣泛，除了可以快速查詢大型數(shù)據(jù)庫(kù)中的信息外，在制作數(shù)據(jù)庫(kù)的過(guò)程中更展示出他們的強(qiáng)大功能。