EXCEL INDEX和MATCH函数嵌套应用
2016-01-03 15:32:20   来源:   评论:0 点击:

EXCEL INDEX和MATCH函数嵌套应用效果和vloockup能达到相同的查询目的,但是比vloockup使用更加灵活。下边是他们的使用方法一、vloockup的使用方法 Lookup的汉语意思是查找,在Excel中与Lookup相关的函数有三个
EXCEL INDEX和MATCH函数嵌套应用效果和vloockup能达到相同的查询目的,但是比vloockup使用更加灵活。
下边是他们的使用方法

一、vloockup的使用方法
 “Lookup”的汉语意思是“查找”,在Excel中与“Lookup”相关的函数有三个:VLOOKUP、HLOOKUO和LOOKUP。下面介绍VLOOKUP函数的用法。Vlookup函数的作用为在表格的首列查找指定的数据,并返回指定的数据所在行中的指定列处的数据。其标准格式为:

  VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

 

  实例介绍:

 

  1、如利用表b来填写表a。

 

  2、在表a的B2单元格使用VLOOKUP公式:

  输入“=vlookup(”后出现提示,如下。

  (其中第一个输入的是需要查找的值,第二个是被查找的数据表,第三个是我们要找到的结果,第四个写成0即可-精确匹配。

  第一个值:A2 (麻子)

  第二个值:G:J (注意要将G到J四列全部选择,不可以选成G1:J5的固定区域,否则会匹配出错。另外在被查找的表中,一定要将包含查找值得列放到选择区域的第一列)

  第三个值:2 ,即找到值后,返回数据表中第2列的数值,即40。

  第四个值:0 ,进行精确匹配。

 

  3、然后向下拖拽,即可(注意不可以向右拖拽)。

 

  4、填写表a的“数学”、“英语”列,重复上面的过程。



二、INDEX和MATCH函数
第一部分:INDEX和MATCH函数用法介绍

  第一,MATCH函数用法介绍

  MATCH函数也是一个查找函数。MATCH 函数会返回匹配值的位置而不是匹配值本身。在使用时,MATCH函数在众多的数字中只查找第一次出现的,后来出现的它返回的也是第一次出现的位置。

  MATCH函数语法:MATCH(查找值,查找区域,查找模式)

  可以通过下图来认识MATCH函数的用法:

MATCH函数的用法

  =MATCH(41,B2:B5,0),得到结果为4,返回数据区域 B2:B5 中 41 的位置。

  =MATCH(39,B2:B5,1),得到结果为2,由于此处无正确匹配,所以返回数据区域 B2:B5 中(38) 的位置。注:匹配的查找值,MATCH 函数会查找小于或等于(39)的最大值。

  =MATCH(40,B2:B5,-1),得到结果为#N/A,由于数据区域 B2:B5 不是按降序排列,所以返回错误值。

  第二,INDEX函数用法介绍

  INDEX函数的功能就是返回指定单元格区域或数组常量。如果同时使用参数行号和列号,函数INDEX返回 行号和列号交叉处的单元格中的值。

  INDEX函数语法:INDEX(单元格区域,行号,列号)

  可以通过下图来认识INDEX函数的用法:

INDEX函数的用法

  =INDEX(A1:C6,2,3),意思就是返回A1:C6中行号是2 列号是3 ,即第二行与第三列的交叉处,也就是C2单元格的值,为84。

  第二部分:INDEX和MATCH函数应用案例介绍

  下图工作表所示的是一个产品的型号和规格的价格明细表。通过这个表的数据,进行一些对应的查询操作。

INDEX和MATCH函数应用案例介绍

  第一,单击B5单元格下拉按钮,选择型号,然后在B6单元格完成型号所在行号的查询。如下图所示:

INDEX和MATCH函数应用案例介绍

  随意选择一个型号,比如A0110,然后在B6单元格输入公式:=MATCH($B$5,$D$4:$D$12,0),得到结果1。

  公式解释:用MATCH函数查找B5单元格这个型号在D4:D12区域中对应的位置。其中的0参数可以省略不写。MATCH函数中0代表精确查找,1是模糊查找。

  第二,单击B9单元格下拉按钮,选择规格,然后在B10单元格完成规格所在列号的查询。如下图所示:

INDEX和MATCH函数应用案例介绍

  随意选择一个规格,比如101,然后在B10单元格输入公式:=MATCH(B9,E3:G3,0),得到结果1。

  第三,查询B6和B10单元格所对应的价格。

  价格的查询,可以使用index函数完成,输入公式:=INDEX(E4:G12,B6,B10)可以得到结果为78。嵌套上面的match函数,可以将公式改为:=INDEX(E4:G12,MATCH(B5,D4:D12,0),MATCH(B9,E3:G3,0))。大家可以变化C3中的型号来看看结果是否正确。

  通过下面工作表的源数据,利用index函数实现行列汇总查询。

INDEX和MATCH函数应用案例介绍

  INDEX函数的帮助文件知道,如果将 row_num 或 column_num 设置为 0,函数 INDEX 则分别返回整个列或行的数组数值。通过用法说明,来实现上图的行列汇总。

  首先,单击C3单元格下拉按钮,选择数据,比如选择A0111,然后在C4单元格进行C3单元格对应的行号查找,公式为:=MATCH(C3,E2:E10,),得到结果为2,说明A0111在E2:E10区域的第二行。

  然后对行号所对应的价格进行汇总求和。在C5单元格输入公式:=SUM(INDEX(F2:H10,MATCH(C3,E2:E10,),))

  如果选中公式中的INDEX(F2:H10,MATCH(C3,E2:E10,),)部分,按下F9键,执行结果是{80,97,84},可以看到就是对第二行的数据进行求和。

  同样的方法,可以进行列汇总,在C9单元格输入公式:=SUM(INDEX(F2:H10,,MATCH(C7,F1:H1,)))。

  说明:查找行号和查找列号,只是过渡一下,帮助新手朋友加深对match函数的理解和使用,对熟悉的朋友,可以直接在行汇总一步完成公式的输入。

  根据下面的工作表,进行区域汇总求和。

INDEX和MATCH函数应用案例介绍

  首先,分别在开始行号、结束行号、结束列号、结束列号选定需要求和的区域,比如A0110、A0111、201、301。此题可以套用下图的格式进行求和。

INDEX和MATCH函数应用案例介绍

  因此,在C20单元格输入求和公式:=SUM(INDEX(F16:H24,MATCH(C16,E16:E24,),MATCH(C17,E16:E24,)):INDEX(F16:H24,MATCH(C18,F15:H15,),MATCH(C19,F15:H15,)))

 

  5、注意,表b可以位于不同的sheet,或者不同的excel文件中。

相关热词搜索:函数

上一篇:excel 中的sumifs函数时间条件的使用
下一篇:用VBA设置或取消打印区域

分享到: 收藏