数值工具之Excel篇(2)

发表于2015-08-06
评论3 1k浏览

想免费获取内部独家PPT资料库?观看行业大牛直播?点击加入腾讯游戏学院游戏策划行业精英群

361498939



本篇着重介绍查找与引用函数。

              查找与引用函数的3代表VlookuplookupHlookupVvertical的简写Hhorizontal简写分别代表在垂直方向和水平方向查找。

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_num1,返回table_array第一列中的数值;col_index_num2,返回table_array第二列中的数值,以此类推

!       range_lookup是个可选参数,指定是近似匹配还是精确匹配查找方式

(上图是帮助文档中的函数说明)

1.3.                   注意要点:

1.Table_array的第一列中的数值可以为文本、数字或逻辑值.

2.文本不区分大小写.

3. 如果range_lookupTRUE,table_array的第一列中的数值必须按升序排列:-2-1012-ZFALSETRUE;否则,函数VLOOKUP不能返回正确的数值.如果range_lookupFALSE,table_array不必进行排序.

4.如果函数VLOOKUP找不到lookup_value,range_lookupTRUE,则使用小于等于lookup_value的最大值.

5.如果lookup_value小于table_array第一列中的最小数值,函数VLOOKUP返回错误值#N/A.

6.如果函数VLOOKUP找不到lookup_valuerange_lookupFALSE,函数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.         

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_num1,返回table_array第一行的数值,row_index_num2,返回table_array第二行的数值,以此类推.如果row_index_num小于1,函数HLOOKUP返回错误值#VALUE!;如果row_index_num大于table-array的行数,函数HLOOKUP返回错误值#REF!.             

Range_lookup              为一逻辑值,指明函数HLOOKUP查找时是精确匹配,还是近似匹配.如果为TRUE或省略,则返回近似匹配值.也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值.如果range_valueFALSE,函数HLOOKUP将查找精确匹配值,如果找不到,则返回错误值#N/A!.

2.3.                   注意要点:

1.如果range_lookupTRUE,table_array的第一行的数值必须按升序排列:……-2-1012……A-ZFALSETRUE;否则,函数HLOOKUP将不能给出正确的数值.如果range_lookupFALSE,table_array不必进行排序.

2.文本不区分大小写.

3.如果函数HLOOKUP小于table_array第一行中的最小数值,函数HLOOKUP返回错误值#N/A!.

4.如果函数HLOOKUP找不到lookup_value,range_lookupTRUE,则使用小于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.         

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-1012...A-ZFALSETRUE;否则,函数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-1012...A-ZFALSETRUE;否则,函数LOOKUP不能返回正确的结果.文本不区分大小写.             

3.4.                   实例演示

注:

              若有多个符合条件的情况:vlookup返回的是第一个满足条件的值,lookup返回的是最后一个满足条件的值.

4.       其它

4.         

4.1.                   match+index

match函数用于查找某个值在向量中的位置

使用格式MATCH(lookup_value,lookup_array,match_type)

Lookup_value              为需要在数据表中查找的数值.为需要在Look_array中查找的数值.可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用.                                                                                                                                           

Lookup_array              可能包含所要查找的数值的连续单元格区域.Lookup_array应为数组或数组引用.                                                                                                                                           

Match_type              为数字-101.Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value

1.如果match_type1,函数MATCH查找小于或等于lookup_value的最大数值.Lookup_array必须按升序排列:...-2-1012...A-ZFALSETRUE.

2.如果match_type0,函数MATCH查找等于lookup_value的第一个数值.Lookup_array可以按任何顺序排列.

3.如果match_type-1,函数MATCH查找大于或等于lookup_value的最小数值.Lookup_array必须按降序排列:TRUEFALSEZ-A...210-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+indexmatch+index的。

4.3.                   dget

这个就比较冷门了使用限制也多,

4.4.                   其它的其它

还能举出至少3以上的例子来,但是大同小异的,思路都是根据查找条件,即查找的键值和查找的字段值获得键值的行数和字段值所在的列数,然后据此获得最终返回值。

=INDIRECT(ADDRESS(1,1)) 组合可以返回单元格A1值,把address两个参数分别用match查找到的键值行和字段值列替换,就可以得到想要的结果。

 

题外,据说lookup家族在查找使用的是二分法,所以在按要求升序排列查找键值,并Range_lookup设置true或不设置时,会返回查找次数最少的最符合条件的值。于是就各种错,有兴趣的同学可以自行尝试。


如社区发表内容存在侵权行为,您可以点击这里查看侵权投诉指引

游戏学院公众号二维码
腾讯游戏学院
微信公众号

提供更专业的游戏知识学习平台