MATCH函数+INDEX函数组合
发布时间:2022-03-28 07:12:46
在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函数的能力,可以创建更加强大的获取数据区域的公式,使其发挥得淋漓尽致。
猜你喜欢
- 在win10纯版本系统中,经常遇到各种问题。 例如,某些用户发现计算机提示他们无法连接到sene服务。 此问题使标准用户无法登录。作为管理员
- Win10系统可以说是最常用的系统之一了,在使用Win10系统的电脑过程中总会遇到一些特别的问题,例如夜间模式使用不了的情况,那么碰到这种情
- 苹果官方在昨天发布了 iOS 14.1 正式版更新,主要是改进和修复问题。值得注意的是,之前在 iOS 14 系统中出现的陌生号码不显示归属
- 1Password是一款功能很强大的密码管理软件,最初只有OS X版本,用户存入1Password的敏感信息会使用一个主密码加密,这意味着除
- Excel中sumif函数的使用方法 Excel中sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。
- 今天小编为大家分享excel画一副简笔画老虎方法,主要是通过excel vba编程来操作,推荐到脚本之家,一起来看看吧本节目的:用excel
- 第1步,打开Word2010文档窗口,切换到“插入”功能区。在“符号”分组中单击“公式”按钮(非“公式”下拉三角按钮),如图1所示。 &nb
- 在日常生活中,我们常常要做出选择,例如,如果明天不下雨,我们就去郊游。在VBA中,也有类似的语句,让我们控制程序的执行方向,例如,如果单元格
- 复合饼图是为了处理较多数据中有一些数据占比例很小,放在饼图中只有极细的扇形,可读性不强,于是将主饼图中某一些小比例的数据合并到一个堆积条形图
- Word文档的页边距怎么调整?在办公软件中,Word文档用的比较多,那页边距的设置也会经常遇到,那怎么设置页边距呢?下面小编就为大家详细介绍
- 当我们重新加载win10操作系统时,我们发现在进入磁盘分区步骤时,系统提示我们无法创建新分区或找到现有分区。在这种情况下,小编认为可以尝试重
- 所谓嵌套函数是指在运算的过程中,将某一函数作为另一函数的参数。在excel2010中输入嵌套函数的方法与输入普通函数的方法相同,但在设置函数
- Win10系统电脑在使用久了以后经常就会遇到各种各样的问题,例如小编这次就遇到了开机的之后显示被拒绝访问的情况,如果遇到这种情况应该怎么办呢
- 对 Apple Music 有兴趣的用户注意了,现在可以在支付宝的“苹果专区”,领取 5 个月或 2 个月的 Apple Musi
- 1、访问世纪互联主页:http://www.21vbluecloud.com/powerbi/pricing/2、点击Power BI Pr
- 如何在Word 2016中设置页边距?Word 2016文档中的页边距在页面的左侧,右侧,顶部和底部创建文本区域。它们在文本和页面边缘之间提
- 今天凌晨一点,苹果终于发布了 iOS 14.7 正式版,版本号是 18G69,这个版本号和上周的 iOS 14.7 RC 的版本号并不一样。
- Word2007如何调整图片位置呢?今天就由小编教大家解决这个问题!希望可以帮到大家!Word2007调整图片位置?1、打开Word2007
- word文档打不开提示"文件包含有宏或需要宏语言"该怎么办?出现这个问题应该是加载项引起的,下面我们就来看看这个问题的解
- 本文主要介绍如何将动画应用于商业图表,如下图1所示。图1制作图表的示例数据如下图2所示的工作表:图2在工作表中放置几个表单控件并设置相应的格