INDEX、MATCH实现数据自动查找与呈现.docVIP

PAGE 1 - 【Excel函数实例】INDEX、MATCH实现数据自动查找与呈现 这两个函数都属于查找与引用函数，INDEX函数的作用主要是取指定单元格的值；MATCH函数的作用用于查找；这两个函数经常成对使用，配合起来就能实现数据的自动查找与呈现。 我们经常看到这样的情况，考试完成后，我们去查询成绩，工作人员询问你的考号是多少，然后工作人员在计算机里输入考号，你的成绩就自动呈现出来了。 今天，我们就是要用Excel的INDEX和MATCH函数来实现类似的功能，同时也让大家能理解INDEX与MATCH函数的用法。 一、建立如下表格。 要完成今天我的实例，首先要准备好一个成绩表，然后设计成下图的表样。 二、创建取姓名的公式。 根据上面的表格，我们第一步不用考虑太多，只考虑当输入考号时，能在右边的成绩数据中找到此考号，并取出此考号相对应的姓名即可。 1、查找考号。 由上表可知，我们输入的考号在B8单元格，要查找的考号范围在C2到C563之间。（注：我的表格中最大考号即有562位学生） 而MATCH函数正好是查找函数，它能查找指定区域里的值，并得到该值在此区域的相对位置。其格式为： MATCH(要查找的数据，查找的区域，参数) MATCH有三个参数：-1、0、1。其意思不一样：-1是查找小于或等于指定数据的单元格；0是查找等于指定数据值的单元格；1是查找大于或等于指定数据的单元格。 因此，本例中，要查找的数据就是考号（即B8），查找的范围是C2到C563（即C2:C563），必须查找学号完全一样的，参数应该是0。 产生的查找公式为：MATCH（B8，C2:C563，0）。 这个公式应该能理解吧！由于学生成绩的区域，与考号输入的单元格是固定不可变的，因此将数据区域改为静态引用。 完善后的公式：MATCH（$B$8，$C$2:$C$563，0） 特别说明：在本例中，也可以不静态引用。 如果B8输入考公式MATCH（B8，$C$2:$C$563，0）的执行结果是多少呢？答案是3。 为什么呢？因为它返C2到C563中的相对位置，对照上表，从C2单元格起，它刚好处在第3的位置上，所以返回值为3。 2、取出该考号的姓名。 还是以考例，用Match函数只能查到了他的相对位置，却不能取出姓名来，怎么办呢？ 取值就要用INDEX函数来实现了。INDEX的作用取指定区域里行列交叉处单元格的值。 INDEX的格式：INDEX（数据区域，行，列） 特别提醒：这个行与列，不是Excel整个表格的行与列，而是指定区域内的行与列。 数据区域：可能有人会说，当然也应该是C2到C563；不对了，因为我们要返回姓名，C2到C563只有考号，没有姓名，因此要扩大区域。用C2到D563就行了。但为了几个公式统一，我用C2到I563区域。这样没有问题，因为姓名包含在其中。 行第几行呢，答案C2到C563区域里的第3行。 列：姓名在区域里的第几列呢？一看就是第2列，对吧。 得到公式：INDEX（C2:I563，3，2），这个公式就可以姓名。 但考号是不停变化的，但不管考号如何变，姓名在第2列不会变，只会变的是在第几行。第几行怎么确定呢？ 大家没有忘记怎么查找考号的吧？就是Match函数来确定是第几行。是不是就搞定了呢？ 最终取姓名的公式就得出来了，将第3行代换为公式： INDEX（C2:I563，MATCH（$B$8，$C$2:$C$563，0），2） 也变成静态引用吧，完善后的取姓名的公式如下： INDEX（$C$2: $I$563，MATCH（$B$8，$C$2:$C$563，0），2） 不知大家是否明白了呢？下面我们在B9单元格输入上面这个公式，然后任意输入一个考号，验证一下是否会取出正确的姓名呢？ 效果如下图所示： 由上图可以看出，我们已经能根据输入的考号取出该考号的学生姓名了。第二步完成了。 三、完成其它公式。 其它公式在取姓名的公式稍加变化即可。由于考号所在的行不变，只是列变化。因此取第3列就是班级，取第4列就语文成绩，取第5列就是数学成绩，以此类推。 取班级的公式： INDEX（$C$2: $I$563，MATCH（$B$8，$C$2:$C$563，0），3） 取语文成绩的公式： INDEX（$C$2: $I$563，MATCH（$B$8，$C$2:$C$563，0），4） 取数学成绩的公式： INDEX（$C$2: $I$563，MATCH（$B$8，$C$2:$C$563，0），5） 取英语成绩的公式： INDEX（$C$2: $I$563，MATCH（$B$8，$C$2:$C$563，0），6） 取总分的公式