【Excel函数实例】INDEX、MATCH实现数据自动查找与呈现.doc

【excel函数实例】index、match实现数据自动查找与呈现这两个函数都属于查找与引用函数，index函数的作用主要是取指定单元格的值；match函数的作用用于查找；这两个函数经常成对使用，配合起来就能实现数据的自动查找与呈现。我们经常看到这样的情况，考试完成后，我们去查询成绩，工作人员询问你的考号是多少，然后工作人员在计算机里输入考号，你的成绩就自动呈现出来了。今天，我们就是要用excel的index和match函数来实现类似的功能，同时也让大家能理解index与match函数的用法。一、建立如下表格。要完成今天我的实例，首先要准备好一个成绩表，然后设计成下图的表样。二、创建取姓名的公式。根据上面的表格，我们第一步不用考虑太多，只考虑当输入考号时，能在右边的成绩数据中找到此考号，并取出此考号相对应的姓名即可。1、查找考号。由上表可知，我们输入的考号在b8单元格，要查找的考号范围在c2到c563之间。（注：我的表格中最大考号为22013562，即有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输入考号22013003，公式match（b8，$c$2:$c$563，0）的执行结果是多少呢？答案是3。为什么呢？因为它返回22013003在c2到c563中的相对位置，对照上表，从c2单元格的22013001算起，它刚好处在第3的位置上，所以返回值为3。2、取出该考号的姓名。还是以考号22013003为例，用match函数只能查到了他的相对位置，却不能取出姓名来，怎么办呢？取值就要用index函数来实现了。index的作用取指定区域里行列交叉处单元格的值。index的格式：index（数据区域，行，列）特别提醒：这个行与列，不是excel整个表格的行与列，而是指定区域内的行与列。数据区域：可能有人会说，当然也应该是c2到c563；不对了，因为我们要返回姓名，c2到c563只有考号，没有姓名，因此要扩大区域。用c2到d563就行了。但为了几个公式统一，我用c2到i563区域。这样没有问题，因为姓名包含在其中。行：22013003在第几行呢，答案c2到c563区域里的第3行。列：姓名在区域里的第几列呢？一看就是第2列，对吧。得到公式：index（c2:i563，3，2），这个公式就可以取22013003的姓名。但考号是不停变化的，但不管考号如何变，姓名在第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）取总分的公式：index（$c$2: $i$563，match（$b$8，$c$2:$c$563，0），7）下图是应用上全部公式的效果：是不是大功告成了呢？不是的。四、完善公式。现在看起来，我们需要的结果都出现了，的确是完成了。只要输入正确的考号都会出现要查找的结果了。