【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）下图是应用上全部公式的效果：是不是大功告成了呢？不是的。四、完善公式。现在看起来，我们需要的结果都出现了，的确是完成了。只要输入正确的考号都会出现要查找的结果了。但大家想一想，如果输入错误的考号，或不输入的呢。就会出现下图的效果了。怎么全部出现错误？考号错了，当然会出错了。好的设计者应该让界面更友好，这样才为大家所接受。你能处理这种情况吗？试一试吧，我处理后的效果。