在excel中查询和引用函数的使用方法

在excel中查询和引用函数的使用方法

1、 LOOKUP函数与MATCH函数
LOOKUP函数可以返回向量（单行区域或单列区域）或数组中的数值.此系列函数用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值.当比较值位于数据表的首行,并且要查找下面给定行中的数据时,使用函数 HLOOKUP.当比较值位于要进行数据查找的左边一列时,使用函数 VLOOKUP.
如果需要找出匹配元素的位置而不是匹配元素本身,则应该使用函数 MATCH 而不是函数 LOOKUP.MATCH函数用来返回在指定方式下与指定数值匹配的数组中元素的相应位置.从以上分析可知,查找函数的功能,一是按搜索条件,返回被搜索区域内数据的一个数据值；二是按搜索条件,返回被搜索区域内某一数据所在的位置值.利用这两大功能,不仅能实现数据的查询,而且也能解决如"定级"之类的实际问题.
2、 LOOKUP用于返回向量（单行区域或单列区域）或数组中的数值.
函数 LOOKUP 有两种语法形式：向量和数组.
（1） 向量形式
函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值,然后返回第二个单行区域或单列区域中相同位置的数值.
其基本语法形式为LOOKUP(lookup_value,lookup_vector,result_vector)
Lookup_value为函数 LOOKUP 在第一个向量中所要查找的数值.Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用.
Lookup_vector为只包含一行或一列的区域.Lookup_vector 的数值可以为文本、数字或逻辑值.
需要注意的是Lookup_vector 的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则,函数 LOOKUP 不能返回正确的结果.文本不区分大小写.
Result_vector 只包含一行或一列的区域,其大小必须与 lookup_vector 相同.
如果函数 LOOKUP 找不到 lookup_value,则查找 lookup_vector 中小于或等于 lookup_value 的最大数值.
如果 lookup_value 小于 lookup_vector 中的最小值,函数 LOOKUP 返回错误值 #N/A.
示例详见图3
　
图3
（2） 数组形式
函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值,然后返回数组的最后一行或最后一列中相同位置的数值.通常情况下,最好使用函数 HLOOKUP 或函数 VLOOKUP 来替代函数 LOOKUP 的数组形式.函数 LOOKUP 的这种形式主要用于与其他电子表格兼容.关于LOOKUP的数组形式的用法在此不再赘述,感兴趣的可以参看Excel的帮助.
3、 HLOOKUP与VLOOKUP
HLOOKUP用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值.
VLOOKUP用于在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值.
当比较值位于数据表的首行,并且要查找下面给定行中的数据时,请使用函数 HLOOKUP.
当比较值位于要进行数据查找的左边一列时,请使用函数 VLOOKUP.
语法形式为：
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
其中,Lookup_value表示要查找的值,它必须位于自定义查找区域的最左列.Lookup_value 可以为数值、引用或文字串.
Table_array查找的区域,用于查找数据的区域,上面的查找值必须位于这个区域的最左列.可以使用对区域或区域名称的引用.
Row_index_num为 table_array 中待返回的匹配值的行序号.Row_index_num 为 1 时,返回 table_array 第一行的数值,row_index_num 为 2 时,返回 table_array 第二行的数值,以此类推.
Col_index_num为相对列号.最左列为1,其右边一列为2,依此类推.
Range_lookup为一逻辑值,指明函数 HLOOKUP 查找时是精确匹配,还是近似匹配.
下面详细介绍一下VLOOKUP函数的应用.
简言之,VLOOKUP函数可以根据搜索区域内最左列的值,去查找区域内其它列的数据,并返回该列的数据,对于字母来说,搜索时不分大小写.所以,函数VLOOKUP的查找可以达到两种目的：一是精确的查找.二是近似的查找.下面分别说明.
（1） 精确查找--根据区域最左列的值,对其它列的数据进行精确的查找
示例：创建工资表与工资条
首先建立员工工资表
　
图4
然后,根据工资表创建各个员工的工资条,此工资条为应用Vlookup函数建立.以员工Sandy（编号A001）的工资条创建为例说明.
第一步,拷贝标题栏
第二步,在编号处（A21）写入A001
第三步,在姓名（B21）创建公式
=VLOOKUP($A21,$A$3:$H$12,2,FALSE)
语法解释：在$A$3:$H$12范围内（即工资表中）精确找出与A21单元格相符的行,并将该行中第二列的内容计入单元格中.
第四步,以此类推,在随后的单元格中写入相应的公式.
　
图5
（2） 近似的查找--根据定义区域最左列的值,对其它列数据进行不精确值的查找
示例：按照项目总额不同提取相应比例的奖金
第一步,建立一个项目总额与奖金比例的对照表,如图6所示.项目总额的数字均为大于情况.即项目总额在0~5000元时,奖金比例为1%,以此类推.
　
图6
第二步 假定某项目的项目总额为13000元,在B11格中输入公式
=VLOOKUP(A11,$A$4:$B$8,2,TRUE)
即可求得具体的奖金比例为5%,如图7.
　
图7
4、 MATCH函数
MATCH函数有两方面的功能,两种操作都返回一个位置值.
一是确定区域中的一个值在一列中的准确位置,这种精确的查询与列表是否排序无关.
二是确定一个给定值位于已排序列表中的位置,这不需要准确的匹配.
语法结构为：MATCH(lookup_value,lookup_array,match_type)　
lookup_value为要搜索的值.
lookup_array：要查找的区域(必须是一行或一列).
match_type：匹配形式,有0、1和－1三种选择："0"表示一个准确的搜索."1"表示搜索小于或等于查换值的最大值,查找区域必须为升序排列."－1"表示搜索大于或等于查找值的最小值,查找区域必须降序排开.以上的搜索,如果没有匹配值,则返回＃N/A.