玩转Excel函数之旅（五）

刘宗青
2000年 第45期 

　　前几期介绍了在表格中如何利用函数进行有关计算，本期将介绍查找函数，利用它对含有大量数据的工作表进行访问，即实现对数据进行有关的查找和应用，当我们把外部的数据库与Excel联系使用时，用查找函数对数据进行查找是非常重要的。
#1　　VLOOKUP函数
　　这个函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数VLOOKUP的查找可以达到两种目的：一是精确的查找。二是近似的查找。下面分别说明。
　　函数的语法：前面学用了不少函数，都利用插入函数，在“粘贴函数”对话框中进行有关参数的设置。其实这种设置，就是函数的语法结构了，如本文要讲的函数VLOOKUP，其语法结构为：
　　VLOOKUP（lookup_value，table_array，col_index_num，range_lookup），其中：
　　lookup_value：表示要查找的值，它必须位于自定义查找区域的最左列；
　　table_array：查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列；
　　col_index_num：相对列号，最左列为1，其右边一列为2，依此类推；
　　range_lookup：这是一个逻辑量，当为“TRUE”时，按近似值查找，这时查找值必须为升序排列；如果为“FALSE”时，按精确值查找，查找值不必排序。
#1　　一、根据区域最左列的值，对其它列的数据进行精确的查找
　　（一）利用函数VLOOKUP，根据学生的姓名，查找他的其它信息
　　如^45050201a^1，这是我们前面作的例子，假设需要根据姓名进行其它信息的查询。
　　1．定义查找区域。因为是按姓名查找，所以姓名所在的列应该是所定义区域的最左列，先按照前面介绍的定义单元格区域的方法，把“B2～J11”命名为“按姓名查找”。
　　2．利用函数VLOOKUP，按姓名进行有关信息的查找。如：
　　（1）查找“邓菊”的总分：=VLOOKUP（“邓菊”，按姓名查找，7，FALSE）。（返回“395”）
　　（2）查找“张平”的数学分数：=VLOOKUP（“张平”，按姓名查找，3，FALSE）。（返回“80”）
　　（3）查找“黄环”的名次：=VLOOKUP（“黄环”，按姓名查找，9，FALSE）。（返回“10”）
　　以上按姓名进行有关项目的查找，其中的“按姓名查找”是刚才定义的查找区域，“7”、“3”、“9”是需查找的信息所在的自定义区域内的相对列号。
　　操作技巧：既可以在选取的单元格中直接输入函数及参数，也可以在“粘贴函数”对话框中进行设置。
　　（二）利用函数VLOOKUP，根据仓库名称，查找仓库的容量和存放物资
　　如^45050201b^2，是某仓库的部分情况，同样可以根据“仓库号”查找相关数据，首先把区域“A2～C11”定义为“按仓库号查找”。如下是一些精确查找的例子：
　　1．查找X3仓库中物资的名称（结果为“钢筋”）：=VLOOKUP（“X3”，按仓库号查找，3，FALSE）。
　　2．查找X9仓库的容量（结果为“452”）：=VLOOKUP（“X9”，按仓库号查找，2，FALSE）。
#1　　二、根据定义区域最左列的值，对其它列数据进行不精确值的查找
　　当“range_lookup”为“TRUE”时，函数按近似值查找，并返回小于查找值的最接近值。^45050201c^3是一个个人所得税税率表，先把区域“A3～B11”定义为“查找个人所得税税率”，注意区域最左列的数据是按升序进行排列的。下面是用函数VLOOKUP，查找一些个人收入对应的税率：
　　1．如查找个人收入为958元的税率（查找结果为5％）：=VLOOKUP（950，查找个人所得税税率，2，TRUE）（因为比950小的是500，而500对应的税率是5％，所以结果是5％）。
　　2．如查找个人收入35000元的税率（查找结果为25％）：=VLOOKUP（35000，查找个人所得税税率，2，TRUE）（因为比35000元小的是20000元，而20000对应的税率是25％，所以结果是25％）。
　　3．如查找个人收入为1000000元的税率（查找结果是45％）：=VLOOKUP（1000000，查找个人所得税税率，2，TRUE）。
#1　　MATCH函数
　　MATCH函数有两方面的功能：一是确定区域中的一个值在一列中的准确位置，这种精确的查询与列表是否排序无关。二是确定一个给定值位于已排序列表中的位置，这不需要准确的匹配；两种操作都返回一个位置值。
　　语法结构为：
　　MATCH（lookup_value，lookup_array，match_type）
　　lookup_value:要搜索的值。
　　lookup_array：要查找的区域（必须是一行或一列）。
　　match_type：匹配形式，有0、1和－1三种选择：“0”表示一个准确的搜索。“1”表示搜索小于或等于查换值的最大值，查找区域必须为升序排列。“－1”表示搜索大于或等于查找值的最小值，查找区域必须降序排开。以上的搜索，如果没有匹配值，则返回＃N/A。
#1　　一、搜索一个准确匹配的例子
　　1．如图2，查找仓库号为“X8”在区域（A2～A11）中的位置（结果是8）：=MATCH（“X8”，A2:A11，0）。
　　2．查找“钢筋”在区域（C2～C11）中的位置（结果是3）：=MATCH（"钢筋"，C2:C11，0）。
#1　　二、搜索一个大于或等于查找值的最小值的位置
　　如^45050201d^4，是一个系统内各部门的综合得分表，如果需要按照得分进行评级：得分大于90而且小于或等于100定为1级；把大于80而且小于或等于90定为2级；依次类推分别定为3级、4级；小于或等于59分的定为5级。利用函数，根据得分搜索分级标准的位置值，就可以实现上述的要求。
　　1．首先命名单元格区域名称：把区域A3～A7命名为“分级标准”；把C3～C12命名为“得分”。
　　2．插入函数MATCH，根据各部门的得分评定级别：选取D3插入函数后，在粘贴函数对话框中，在“lookup_value”项中输入“得分”、在“lookup_array”项中输入“分级标准”、在“match_type”项中输入“－1”，确定后得出“部门1”为2级，其它各部门的级别按前面介绍过的拖动复制就可以了，如^45050201e^5。
　　上述过程利用了函数MACTH在查找时返回的位置值，而定出各部门的级别。比如得分是“85”，则查找比它大的分级标准是“90”，而“90”在分级标准区域（A3～A7）中位置为“2”，所以可以定出这个得分为“2”级。再如得分为“60”，查找出比它大的分极标准是“70”，而“70”在分级标准区域（A3～A7）中位置为“4”，所以可以定出这个得分为“4”级，再如得分是“23”，则查找比它大的分级标准是“59”，而“59”在分级标准区域（A3～A7）中位置为“5”，所以定出为“5”级等等。
　　当“match_type”为“1”时，查找区域内的数据必须按升序排列，要搜索的值，则返回查找区域内比它小的最大值的位置。^45050201f^6是上述相同的得分，当“match_type”为“1”时的结果。出现“＃N/A”，说明在“分级标准中没有找到匹配值。
　　总结：查找函数的功能，一是按搜索条件，返回被搜索区域内数据的一个数据值；二是按搜索条件，返回被搜索区域内某一数据所在的位置值。利用这两大功能，不仅能实现数据的查询，而且也能解决如“定级”之类的实际问题。

 