首页 > Excel专区 > Excel教程 >

MATCH函数+INDEX函数组合

Excel教程 2022-01-06 21:10:43

在Excel中,MATCH函数和INDEX函数是一对非常经典的组合,我们经常能够在Excel公式中看到他俩的“身影”。MATCH函数返回查找值在单元格区域或者数组中的位置,INDEX函数返回这个位置的数据。下面,让我们看看MATCH函数和INDEX函数组合使用的一些例子,从中体会这对组合的强大威力。

查找满足多个条件的数据

如下图1所示的工作表,数据区域为B3:D16,求单元格G2中指定班级和单元格G3中指定姓名的学生成绩?在单元格G4中使用数组公式:

=INDEX(D3:D16,MATCH(G2&G3,B3:B16&C3:C16,0))

其中,MATCH(G2&G3,B3:B16&C3:C16,0)查找到指定班级的学生在数据区域中的位置,作为INDEX函数的参数来提取值。

图1

总是获取列表中的最后一个数据

如下图2所示的工作表,求列表区域B3:D16中最后一位同学的成绩?在单元格H4中的公式:

=INDEX($D$3:$D$16,MATCH(9.9E+307,$D$3:$D$16))

其中,MATCH(9.9E+307,$D$3:$D$16)总是获取D3:D16中最后一个数据所在的位置。

图2

创建动态区域

动态区域就是当该区域中增加或删除数据时,引用的区域会自动调整。

仍以图2所示的工作表为例,将上例中的公式作为单元格引用的第二个元素:

$B$3:INDEX($D$3:$D$16,MATCH(9.9E+307,$D$3:$D$16))

因为INDEX函数在引用的开始单元格和冒号之后,因此不再获取该区域中的最后一个单元格值,而是获取区域中最后一个数据单元格的地址,从而与开始单元格组成单元格区域。

为了演示效果,我们将上面的引用定义为名称:DynamicData,如下图3所示,当增加数据后,引用区域会自动扩展。

图3

创建更加强大的动态区域

下面中的例子,使用INDEX函数、MATCH函数和COUNTA函数结合的公式,根据指定的列名创建动态区域,很特别的是,各列的行数不需要相同。

如下图4所示,当工作表Sheet4中单元格A1内容为“水果”时,动态命名区域为工作表Sheet3中的水果列;当工作表Sheet4中单元格A1内容为“家用电器”时,动态命名区域为工作表Sheet3中的家用电器列,依此类推。

图4

首先,工作表Sheet3中创建一个动态命名区域:Datas

公式为:

=Sheet3!$A$2:INDEX(Sheet3!$1:$50,50,COUNTA(Sheet3!$1:$1))

如果工作表Sheet3如下图5所示,则创建的动态区域为从列A开始的3列以及从第1行开始的50行的区域。

图5

然后,选中工作表Sheet4的单元格B2,如图6所示,打开“新建名称”对话框,创建动态名称:DynamicList

公式为:

=INDEX(Datas,1,MATCH(Sheet4!A1,Sheet3!$1:$1,0)):INDEX(Datas,COUNTA(INDEX(Datas,,MATCH(Sheet4!A1,Sheet3!$1:$1,0))),MATCH(Sheet4!A1,Sheet3!$1:$1,0))

图6

注意:由于要想引用当前单元格左侧的单元格,因此在定义名称时,一定要选择工作表Sheet4的单元格B1。

上面的公式比较复杂,以冒号为界,分为两个部分。

第一部分:INDEX(Datas,1,MATCH(Sheet4!A1,Sheet3!$1:$1,0))

在工作表Sheet3中找到工作表Sheet4单元格A1中的数据所在的单元格,作为起始单元格。

在第二部分中,公式:COUNTA(INDEX(Datas,,MATCH(Sheet4!A1,Sheet3!$1:$1,0)))

找到工作表Sheet4单元格A1中的数据在工作表Sheet3中的列并统计该列非空单元格数量,作为外层INDEX函数的参数。整个第二部分的INDEX公式找到相应列的最后一个数据单元格,作为结束单元格。

结语

使用MATCH函数与INDEX函数的组合,让我们突破VLOOKUP函数的局限,创建常用的获取数据的公式。如果更深入的发掘MATCH函数与INDEX函数的能力,可以创建更加强大的获取数据区域的公式,使其发挥得淋漓尽致。


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.