作为班主任，笔者用Excel 2007记录了班内学生在几次大型考试中的考试成绩，每次考试成绩分别放在不同的工作表中，表格格式如图1所示。为了更好地掌握学生的学习状况，我希望能够很快地查阅到每一位学生在这几次考试中各学科及名次的变化情况。由于各工作表中数据的排序情况并不相同，因此，如果在每一个工作表中逐个查找某个学生的成绩的话，就显得非常麻烦，而且也不直观。所以我要建立一个新的工作表，将每位学生每次考试的成绩那么用到新工作表中，做到一目了然。
图1(点击看大图)
一、快速建立表格
先选中A4:A11单元格，将它们合并成一个单元格。再将B4:B11单元格也合并成一个单元格。然后选中这两个合并后的单元格，拖动填充句柄向下至B379单元格（本班有47名学生），再输入考号和姓名。
在C4:C11单元格中分别输入各学科的名字，然后选中C4:C11单元格，向下拖动填充句柄至C379单元格，就可以快速填充好学科了，表格设计如图2所示。
图2(点击看大图)
二、期末考试各科成绩的引用
引用学生各科成绩，当然应该使用VLOOKUP函数。不过，这里有两个问题需要解决：
问题一：如果我们在D4单元格输入公式“=VLOOKUP(B4,期末!$C$3:$S$115,2,FALSE)”，回车后自然可以得到第一名同学的期末考试的语文成绩（该成绩在“期末”工作表的D列）。但是，如果我们拖动填充句柄向下复制公式时，那么就会出现问题了： D4:D11单元格中第一个参数都应该是“B4”，但自动填充公式时此参数会发生变化，需要固定为B4，而到下一同学时，要固定为B12，依此类推。
这个问题后来通过设置辅助列，再配合求余数函数终于解决了。实现方法如下：
将D列作为辅助列。点击D4单元格，输入公式“=$B&(INT((ROW()+4)/8)*8-4)”，回车后得到结果“$B4”，然后拖动填充句柄至D379单元格，这样，就可以实现固定查找参数的目的了，如图3所示。
图3(点击看大图)
问题二：VLOOKUP函数的第三个参数也需要配置。各学科成绩在原始成绩表中的位置是不一样的，按照图1所示表格，查找语文成绩时为应该使用参数“2”，数学、英语、理论、实践、文化、专业、总分各学科的此参数应该分别为“3”、“4”、“5”、“6”、“7”、“9”、“11”。所以，直接拖动填充句柄肯定不行。8个需要引用的成绩只能使用8个不同的公式来实现。此问题的解决方法是使用IF函数嵌套，再配合使用求余数函数作为判断条件。好在Excel 2007允许我们使用64个IF函数嵌套了。另外每个同学的成绩占用8行单元格，所以成绩所在行数除以8所得余数为0～7这几个不同的数字，正好可以作为判断的条件。
为了使我们的公式变得稍微简单一些，我们可以为求余数的公式部分命名。点击功能区“公式”选项卡“定义的名称”功能组中“定义名称”按钮，在弹出的对话框的“名称”输入框中输入名称“yushu”，在“引用位置”处输入公式“=MOD(ROW(),8)”，如图4所示。以后，我们在公式中使用“yushu”，就相当于直接使用公式“=MOD(ROW(),8)”了。
图4
现在将鼠标定位于E4单元格，输入公式“=IF(yushu=4,VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,2,FALSE),IF(yushu=5,VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,3,FALSE),IF(yushu=6,VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,4,FALSE),IF(yushu=7,VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,5,FALSE),IF(yushu=0,VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,6,FALSE),IF(yushu=1,VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,7,FALSE),IF(yushu=2,VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,9,FALSE),VLOOKUP(INDIRECT($D4),期末!$C$3:$S$115,11,FALSE))))))))”，
回车，就可以得到第一名学生期末考试的语文科成绩，如图5所示。向下拖动填充句柄直至E379单元格，每位同学的期末考试全部成绩就填充完成了。很不错吧？
图5(点击看大图)
三、其它成绩及名次的引用
选中E4单元格，拖动填充句柄向右复制公式至F4单元格。选中F4单元格后，在编辑栏将公式中各VLOOKUP函数第三个参数“2”、“3”、“4”、“5”、“6”、“7”、“9”、“11”分别替换为“13”、“14”、“15”、“16”、“17”、“8”、“10”、“12”（这些数字还是根据图1所示表格中各学科在指定查找范围中列数决定的）。拖动F4单元格的填充句柄向下复制公式至F379。到此为止期末考试成绩的引用已经完成了。
如果要得到“第1次”工作表的成绩，那么需要选中E4:F379单元格区域，将其复制到G4:H379单元格区域。再选中G4:H379单元格区域，按下“Ctrl+F”快捷键，点击弹出对话框的“替换”选项卡，在“查找内容”输入框中输入“期末!”，在“替换为”输入框中输入“第1次!”，如图6所示。点击“全部替换”按钮就行了。
图6
其它各次考试的成绩引用可照此法办理。至此，可谓大功告成。
