数值工具之Excel篇(2)
本篇着重介绍查找与引用函数。
查找与引用函数的3个代表是Vlookup，lookup和Hlookup。V是vertical的简写，H是horizontal的简写，分别代表在垂直方向和水平方向查找。
1. Vlookup
1.1. 函数说明：
在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值.当比较值位于数据表首列时,可以使用函数VLOOKUP代替函数HLOOKUP.
1.2. 使用格式：
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
! lookup_value是查找内容，为需要在数组第一列中查找的数值.Lookup_value可以为数值、引用或文本字符串。
! table_array是查找的区域，为需要在其中查找数据的数据表.可以使用对区域或区域名称的引用,例如数据库或列表。
! col_index_num是从查找区域首列开始到需要返回的内容所列的相对列数，Col_index_num为1时,返回table_array第一列中的数值；col_index_num为2,返回table_array第二列中的数值,以此类推。
! range_lookup是个可选参数，指定是近似匹配还是精确匹配查找方式。
(上图是帮助文档中的函数说明)
1.3. 注意要点：
1.Table_array的第一列中的数值可以为文本、数字或逻辑值.
2.文本不区分大小写.
3. 如果range_lookup为TRUE,则table_array的第一列中的数值必须按升序排列：…、-2、-1、0、1、2、…、-Z、FALSE、TRUE；否则,函数VLOOKUP不能返回正确的数值.如果range_lookup为FALSE,table_array不必进行排序.
4.如果函数VLOOKUP找不到lookup_value,且range_lookup为TRUE,则使用小于等于lookup_value的最大值.
5.如果lookup_value小于table_array第一列中的最小数值,函数VLOOKUP返回错误值#N/A.
6.如果函数VLOOKUP找不到lookup_value且range_lookup为FALSE,函数VLOOKUP返回错误值#N/A.
7.若有多个符合条件的情况:vlookup返回的是第一个满足条件的值,lookup返回的是最后一个满足条件的值.
1.4. 实例演示：
1.4.1. 常规用法
下图显示了如何使用 =VLOOKUP("康",B2:D5,2,FALSE) 设置您的工作表以返回“霓”。
1.4.2. 派生用法：
上一篇我们说过，一个函数的返回值也可以作为另一个函数的参数。仍以上图的数据为例，我知道名字“月英”，希望反查得到她的姓氏。仍然用vlookup函数，在不用辅助列，不操作调换BC两列位置的话。可以这样写公式：=VLOOKUP("月英",IF({1,0},C:C,B:B),2,)，获得姓氏“任”。
采用上一篇介绍的F9大法，可知，If函数返回了一个数组，{"名字","姓氏";"月英","任";"珺","米";"霓","康";"德威","彭"}。本篇暂不深入讲解数组公式，只简单的埋个伏笔。If函数大家平时用的也较多，但都是返回一个具体值，而这里却是返回了一个数组。原因在于if的第一个参数“{1,0}”，是以数组形式输入的。相应的，if函数的返回值也进行了自适应，于是返回了一个数组。
另外，注意到range_lookup的参数我们什么都没填，但，这是一种便捷写法。请注意=VLOOKUP("月英",IF({1,0},C:C,B:B),2,) 和=VLOOKUP("月英",IF({1,0},C:C,B:B),2)的区别。
2. HLookup
2.1. 函数说明：
在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值.当比较值位于数据表的首行,并且要查找下面给定行中的数据时,请使用函数 HLOOKUP.当比较值位于要查找的数据左边的一列时,请使用函数 VLOOKUP.
2.2. 使用格式：
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Lookup_value 为需要在数据表第一行中进行查找的数值.Lookup_value可以为数值、引用或文本字符串.
Table_array 为需要在其中查找数据的数据表.可以使用对区域或区域名称的引用.Table_array的第一行的数值可以为文本、数字或逻辑值.
Row_index_num 为table_array中待返回的匹配值的行序号.Row_index_num为1时,返回table_array第一行的数值,row_index_num为2时,返回table_array第二行的数值,以此类推.如果row_index_num小于1,函数HLOOKUP返回错误值#VALUE!；如果row_index_num大于table-array的行数,函数HLOOKUP返回错误值#REF!.
Range_lookup 为一逻辑值,指明函数HLOOKUP查找时是精确匹配,还是近似匹配.如果为TRUE或省略,则返回近似匹配值.也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值.如果range_value为FALSE,函数HLOOKUP将查找精确匹配值,如果找不到,则返回错误值#N/A!.
2.3. 注意要点：
1.如果range_lookup为TRUE,则table_array的第一行的数值必须按升序排列:……-2、-1、0、1、2、……、A-Z、FALSE、TRUE；否则,函数HLOOKUP将不能给出正确的数值.如果range_lookup为FALSE,则table_array不必进行排序.
2.文本不区分大小写.
3.如果函数HLOOKUP小于table_array第一行中的最小数值,函数HLOOKUP返回错误值#N/A!.
4.如果函数HLOOKUP找不到lookup_value,且range_lookup为TRUE,则使用小于lookup_value的最大值.
2.4. 实例演示：
2.4.1. 常规用法：
下图显示了如何使用 =HLOOKUP(F2,B1:D2,2,0) 设置您的工作表以返回“张三”。
2.4.2. 派生用法：
和vlookup类似，也是使用了if函数，但是{1,0}变成了{1;0}
3. Lookup
3.1. 函数说明：
(向量形式)(数组形式)搜索单行、单列、区域、查找对应值
3.2. 使用格式：
向量形式LOOKUP(lookup_value,lookup_vector,result_vector)
数组形式LOOKUP(lookup_value,array)
Lookup_value 为函数LOOKUP在第一个向量中所要查找的数值.Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用
Lookup_vector 为只包含一行或一列的区域.Lookup_vector的数值可以为文本、数字或逻辑值
Result_vector 只包含一行或一列的区域,其大小必须与lookup_vector相同.
3.3. 注意要点：
要点：向量形式
1.向量为只包含一行或一列的区域.函数LOOKUP的向量形式是在单行区域或单列区域(向量)中查找数值,然后返回第二个单行区域或单列区域中相同位置的数值.如果需要指定包含待查找数值的区域,则可以使用函数LOOKUP的这种形式.函数LOOKUP的另一种形式为自动在第一列或第一行中查找数值.
2.函数LOOKUP的数组形式是在数组的第一行或第一列中查找指定数值,然后返回最后一行或最后一列中相同位置处的数值.如果需要查找的数值在数组的第一行或第一列,就可以使用函数LOOKUP的这种形式.当需要指定列或行的位置时,可以使用函数LOOKUP的其他形式.
3.Lookup_vector的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则,函数LOOKUP不能返回正确的结果.文本不区分大小写.
4.如果lookup_value小于lookup_vector中的最小值,函数LOOKUP返回错误值#N/A.
5.如果函数LOOKUP找不到lookup_value,则查找lookup_vector中小于或等于lookup_value的最大数值.
要点：数组形式
1.如果函数LOOKUP找不到lookup_value,则使用数组中小于或等于lookup_value的最大数值.
2.如果lookup_value小于第一行或第一列(取决于数组的维数)的最小值,函数LOOKUP返回错误值#N/A.
3.函数LOOKUP的数组形式与函数HLOOKUP和函数VLOOKUP非常相似.不同之处在于函数HLOOKUP在第一行查找lookup_value,函数VLOOKUP在第一列查找,而函数LOOKUP则按照数组的维数查找.
4.如果数组为正方形,或者所包含的区域高度大,宽度小(即行数多于列数),函数LOOKUP在第一列查找lookup_value.
5.函数HLOOKUP和函数VLOOKUP允许按行或按列索引,而函数LOOKUP总是选择行或列的最后一个数值.
6.数组中的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则,函数LOOKUP不能返回正确的结果.文本不区分大小写.
3.4. 实例演示：
注：
若有多个符合条件的情况:vlookup返回的是第一个满足条件的值,lookup返回的是最后一个满足条件的值.
4. 其它：
4.1. match+index
match函数用于查找某个值在向量中的位置。
使用格式MATCH(lookup_value,lookup_array,match_type)
Lookup_value 为需要在数据表中查找的数值.为需要在Look_array中查找的数值.可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用.
Lookup_array 可能包含所要查找的数值的连续单元格区域.Lookup_array应为数组或数组引用.
Match_type 为数字-1、0或1.Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value：
1.如果match_type为1,函数MATCH查找小于或等于lookup_value的最大数值.Lookup_array必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE.
2.如果match_type为0,函数MATCH查找等于lookup_value的第一个数值.Lookup_array可以按任何顺序排列.
3.如果match_type为-1,函数MATCH查找大于或等于lookup_value的最小数值.Lookup_array必须按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...,等等.
4.如果省略match_type,则假设为1.
以上公式是使用match+index，替代vlookup，获得“贾淑君”的性别，第7个位置的“女”。
注：
Index函数还支持INDEX(array,row_num,column_num)格式的用法，也可以结合match函数使用。
4.2. offset+match
使用和match+index组合差不多，但offset可以返回数组。可以粗犷的认为offset+index是比match+index大的。
4.3. dget
这个就比较冷门了，使用限制也多，。
4.4. 其它的其它
还能举出至少3种以上的例子来，但是大同小异的，思路都是根据查找条件，即查找的键值和查找的字段值，先获得键值的行数和字段值所在的列数，然后据此获得最终返回值。
如：=INDIRECT(ADDRESS(1,1)) 的组合可以返回单元格A1的值，把address的两个参数分别用match查找到的键值行和字段值列来替换，就可以得到想要的结果。
题外，据说lookup家族，在查找时使用的是二分法，所以在未按要求升序排列查找键值，并Range_lookup设置为true或不设置时，会返回查找次数最少的最符合条件的值。于是就各种错乱，有兴趣的同学可以自行尝试。