center">图1 |
center">图2 |
一、VLOOKUP函数
我们可以在“综合”class="tags" href="/tags/GongZuo.html" title=工作>工作表的C3单元格输入公式“=VLOOKUP($B3,第1次!$B$1:$I$92,7,FALSE)”c;回车后就可以将第一位同学第一次考试的总分汇总过来了。
把C3单元格公式复制到D3单元格c;并将公式中第三个参数“7”改成“8”c;回车后c;就可以得到该同学第一次考试名次。
选中C3:D3这两个单元格c;向下拖动填充句柄到最后就可以得到全部同学的总分及名次了。是不是很简单呀?如图3所示。
VLOOKUP函数的用法是这样的:VLOOKUP(参数1,参数2,参数3,参数4)。“参数1”是“要查找谁?”本例中B3单元格c;那就是要查找B3单元格中显示的人名。“参数2”是“在哪里查找?”本例中“第1次!$B$1:$I$92”就是告诉Excel在“第1次”class="tags" href="/tags/GongZuo.html" title=工作>工作表的B1:I92单元格区域进行查找。“参数3”是“找第几列的数据?”本例中的“7”就是指从“第1次”class="tags" href="/tags/GongZuo.html" title=工作>工作表的B列开始起c;第7列的数据c;即H列。本例中“参数4”即“FALSE”是指查询方式为只查询精确匹配值。
该公式先在“第1次”class="tags" href="/tags/GongZuo.html" title=工作>工作表的B!:I92单元格区域的第一列(即B1:B92单元格区域)查找B3单元格数据c;找到后c;返回该数据所在行从B列起第7列(H列)的数据。所以c;将参数3改成“8”以后c;则可以返回I列的数据。
由此可以看出c;使用VLOOKUP函数时c;参数1的数据必须在参数2区域的第一列中。否则是不可以查找的。
二、INDEX函数
某些情况下c;VLOOKUP函数可能会无用武之地c;如图4所示。“综合”class="tags" href="/tags/GongZuo.html" title=工作>工作表中c;姓名列放到了A列c;而B列要求返回该同学所在的班级。但我们看前面的class="tags" href="/tags/GongZuo.html" title=工作>工作表就知道了c;“班级”列是位于“姓名”列前面的。所以c;此时我们不可能使用VLOOKUP函数来查找该同学的班级。而INDEX函数就正可以一试身手。
center">图4 |
在B3单元格输入公式“=INDEX(第1次!$A$1:$I$92,MATCH(A3,第1次!$B$1:$B$92,0),1)”c;回车并向下复制公式就可以了c;如图5所示。
center">图5 |
这里用到了两个函数c;INDEX和MATCH。先说说这个MATCH(A3,第1次!$B$1:$B$92,0)。它的意思是在“第1次”class="tags" href="/tags/GongZuo.html" title=工作>工作表的B1:B92单元格区域中查找A3单元格数据c;然后返回该数据在B1:B92单元格区域中的行数。以本例而言c;该公式返回的结果是“2”。这一点c;只要看一下图1所示class="tags" href="/tags/GongZuo.html" title=工作>工作表就清楚了。至于该公式中最后一个参数“0”是要求精确匹配c;而且使用该参数时c;B1:B92单元格区域不需要排序。
再来看这个INDEX函数。它的用法是INDEX(参数1,参数2,参数3)。参数1是要查找的区域。参数2是行数c;参数3是列数。比如公式INDEX(A2:C6,2,3)的意思就是要返回在A2:C6这个区域中第二行第三列的数据c;即C3单元格的数据。所以c;本例中公式的意思就是返回“第1次”class="tags" href="/tags/GongZuo.html" title=工作>工作表A1:I92单元格区域中第二行第一列的数据c;呵呵c;那不正是该同学所在的班级嘛!
与VLOOKUP函数相比较c;INDEX函数的“限制”要少一些。
三、OFFSET函数
其实c;我们还可以使用OFFSET函数完成这个任务的。比如我们要查A3单元格同学在第二次考试中的总分。那么只需要点击E3单元格c;并输入公式“=OFFSET(第2次!$A$1,MATCH($A3,第2次!$B$1:$B$92,0)-1,7,1,1)”c;回车后并向下复制公式c;就可以得到所需要的数据了。如图6所示。
center">图6 |
OFFSET函数是以指定的引用为参照系c;通过给定偏移量得到新的引用c;比如公式“=OFFSET(C3,2,3,1,1)”c;它的意思就是返回以C3单元格为基准c;向下偏移2个单元格c;向右偏移3个单元格处的单元格数据c;即F5单元格的数据c;数一下就清楚了。
所以本例公式中先由“MATCH($A3,第2次!$B$1:$B$92,0)”得到“第2次”class="tags" href="/tags/GongZuo.html" title=工作>工作表B1:B92单元格中与A3单元格相匹配的数据所在行数(本例结果为2)c;再减去1正好是它相对于“第2次”class="tags" href="/tags/GongZuo.html" title=工作>工作表A1单元格的向下偏移的行数c;而后面的参数“7”c;则是该同学的总分成绩所在单元格相对于A1单元格的向右偏移量。因此c;本例中在E3单元格的公式其实就是“=OFFSET(第2次!$A$1,1,7,1,1)”c;其意思就是返回“第2次”class="tags" href="/tags/GongZuo.html" title=工作>工作表中相对于A1单元格下移一行右移7行处单元格数据。
好了c;这三个查找引用函数的用法应该算说清楚了吧?只要我们认真研究它们的用法c;相信会有很多妙用的。