OFFSET、MATCH函数实例自动寻找并填写的成绩.doc

【Excel技巧】OFFSET、MATCH函数实例：自动寻找并填写的成绩对于这两个函数，很多人比较陌生，这两个函数很多时候是成对使用的。今天我就举个例子，帮助大家理解这对函数。本文以实例讲解为主，只作必要解释，希望大家能有所获。今天举个例，我经常进行学生的机读卡的时候，经常遇到这种情况，由于学生机读卡填涂的原因，每次读的5000多张机读卡中，有200多张不能被正常读取，而造成了这部分学生的成绩没有。所以导出的机读卡成绩中，这些考号根本没有。将机读卡的成绩填到成绩册时，就遇到麻烦了，每次要去寻找是哪个考号没有，眼都看花了。今天就是要解决这个问题，让电脑自动寻找成绩，并填入成绩册中。下面开始动手讲解这一实现的实例：一、建立如下表格。（为方便大家，只有几个数据，重在讲方法）大家观察表格，有几个考号是没有的，而成绩册里的考号是都有的。二、建立成绩册（为方便大家观看，这里都建在一个表里）这里只需要考号的成绩，就可以了，一切从简，请理解，理解了成绩的产生后，其它的数据都可以自动寻找的。表格如下所示：三、填入自动寻找成绩的公式。、用MATCH实现自动寻找。要自动寻找数据，就必须要选择寻找的方式，这里用考号来寻找，找到考号相同的就可以填入数据了。寻找的数据是考号(E2单元格)，查找的范围是在机读卡考号里找(即A2到A7单元格区域里)。使用的函数是MATCH函数。于是寻找的公式就产生了。MATCH(E2,A2:$A$7,0)这个公式的意义：在A2到A7单元格中寻找与E2单元格相同的数据，找到以后，会返回一个值，这个值是该数据在A2到A7中的相对的位置。特别说明一下，后面的0，其意思是表示必须找到等于E2的。还可以为-1或1，为1时代表找大于等于E2的，为-1代表找小于等于E2的。这个公式的返回值为1，为什么呢？能想通吗？为了能应用方便，公式必须改成以下模样，将原始数据区域变成静态引用，固定下来。MATCH(E2, $A$:$A$7,0)、用OFFSET函数实现自动取成绩。在找到与E2考号的在机读成绩中的位置后，就可以用OFFSET来定位，取成绩了。OFFSET要取成绩，首先要找一个单元格作为固定不变的坐标原点。这里当然是找A1单元格最为合适。以201101001的成绩为例，该考号从A1下移一个行，成绩呢？再右移三个就行，对吧。用OFFSET就产生如下公式：公式的意思：OFFSET(固定的参考单元格, 下移位置，右移位置)产生的公式：OFFSET(A1, 1 , 3)这个公式的结果取得数据98，理解起来应该没有问题吧？参数1就是201101001的位置，这个位置是怎么产生的呢？对了，就是用MATCH函数来实现的对吧？右移3就是成绩，这个永远都是右移3，因为我们查找的是第一列，在第一列的基础上右移3就是成绩。将1用MATCH公式代换。A1是固定不变的，应该静态引用。因此完整公式如下：=OFFSET($A$1,MATCH(E2,$A$2:$A$7,0),3)鼠标拖动应用该公式，其应用效果如下图：观察上图，大家很容易发现一个问题，那就是没有考号的成绩会出现错误，如果你不想看到这种情况，可以对公式进行一个IF判