ROW函数返回行号,COLUMN函数返回列号。
两个函数的返回值都是单元格在Excel表格中的
绝对行号和
绝对列号。如果Excel表格的数据从A1单元格开始,那么相对行(列)号和绝对行(列)号一致,否则是不一致的。
1、MATCH函数返回符合特定顺序的项在数组中的相对位置。
2、INDEX函数在给定的单元格中返回行列交叉处(常常利用MATCH函数确定)的值或引用。
1、MATCH(要查找的值, 含有值的单元格区域, 查找值的类型)
MATCH函数公式总共包含三个参数,前两个参数比较好理解,第三个参数查找值的类型需要说明一下。
查找值的类型为“1或省略、0、-1”三种:
如果为1或省略,MATCH函数查找小于或等于第一个参数要查找的值的最大值,并且第二个参数含有值的单元格区域必须以升序排列(例如,...-2, -1, 0, 1, ...)。
如果为
0,MATCH函数精确匹配,完全等于第二个参数含有值的单元格区域的第一个值,并且第二个参数含有值的单元格区域可以按任何顺序排列。
如果为-1,与1或省略相反,查找最小值并且降序排列。
INDEX函数公式也包含三个参数,第一个参数就是选定的查找区域,第二个参数行号和第三个参数列号就是指在第一个参数查找区域中的相对行号和相对列号,函数值返回(或引用)行和列交叉处的单元格数据。
*注:由于INDEX函数中的后两个参数需要用到查找区域中的行号和列号——即用到查找区域中的相对位置,所以INDEX函数常常与MATCH函数嵌套使用,下面将举例说明。
例中的Excel为某网站的网络课程相关数据,见下图
下面,我们通过完成几个简单的任务来加深理解如何使用MATCH函数和INDEX函数
1、确定云计算/大数据报名人数的相对位置和排名
先实现相对位置,使用MATCH函数,在第三个参数设置为0,进行精确匹配
要确定在整个表里的相对位置,在选择区域的时候必须选择行标签
报名人数。
接下来如果想看云计算/大数据报名人数在所有课程里面排名第几位,只需要把MATCH凹函数第三个参数改为-1,并且将报名人数按照降序排列0。此外,由于是查找排名,因此选择区域只选择数据,不要选择行标签报名人数。
云计算/大数据报名人数从高到低排名第二,在降序排列之后,排名和相对位置都改变了。
进一步,还可以用将所有的报名人数按照序列全部放置到单元格B22内,如下图
可以快速确定不同学科报名人数的排名,通过以上步骤完成了一个
最简单的查找器,见下图 在上面的公式中,第二参数显示为
表1[报名人数],那表1到底是怎么一回事呢?
选定区域之后,点击Ctrl+t可以快速生成表格,并且同时生成非常便于操作的表格功能,比如升序、降序、按颜色排序、数字筛选等,如下图:
2、通过课程名称快速确定依据报名人数(或其他某个行标签)的排名、相对位置和具体数据
第一部分只是制作一个最简单的查找器,但使用起来还是相对比较麻烦。如果想通过课程名称快速的确定报名人数(或其他某个行标签)的排名和具体数据应该怎么实现呢?
首先利用将所有课程名称放置到单元格A25内
同样的方法,把所有
行标签放置到
单元格A26内 以课程云计算/大数据为例,按照第一部分用MATCH函数查找排名(数据按照报名人数降序排列,这里MATCH函数第三个参数选择0,精确匹配)。
再查找报名人数在行标签中的相对位置(这里忽略行标签的排名),精确匹配,选择的单元格区域见下图