巧用Excel实现学生成绩分学期打印

摘 要：Excel具有很强的数据管理和分析功能，要想充分发挥其数据管理和分析功能，就得按规定的数据格式组织数据，但这种便于数据管理和分析的数据格式往往不能满足我们的打印需求。本文以贵州电子信息职业技术学院学生成绩卡打印为例，介绍如何通过Excel 函数来实现特殊报表的打印。

关键词：excel；数组公式；特殊报表；成绩打印

学生成绩卡是职业院校学生求职和学籍档案中的必备材料之一，而且成绩卡的打印不像毕业证书打印那样一次性批量完成，成绩卡的打印既有批量打印，也有零散打印。如果用传统的邮件合并功能来实现，需要将学生的基本信息和所有学科的成绩合并成一行放在数据源表中，工作量相当大，而且每个班级的学科往往不同，一个班级要做一个模板，不利于成绩数据的统一管理和查询。下面将详细介绍通过Excel函数、条件格式和宏命令来实现学生成绩卡的打印方法。

1 数据准备

（1）添加“学生基本信息”工作表，将成绩打印所需的学生基本信息整理成如图1所示格式，该表可根据需要任意排序，各列的列名必须与图1所示完全一致。

（2）添加“成绩表”工作表，将学生成绩表整理成如图2所示的格式，该表必须以学号学期列为主要关键字进行排序，各列的顺序必须与图2所示完全一致。

图1

图2

2 成绩卡制作

（1）添加“成绩卡”工作表，设计成绩卡打印样式如图3所示（为了节省空间，此处隐藏了部分行），其中每个学生的具体信息保持为空。在U10单元格中输入数字1备用。

（2）将A1：Q27单元格区域设置为打印区域。

（3）引用学生基本信息表中的学号，在L2单元格中输入公式：=INDIRECT（"学生基本信息！r"&U10+1&"c"&MATCH（"学号"，学生基本信息！A1：F1，0），0）。

（4）引用其他基本信息的方法和引用学号的方法相同，只需把公式中的学号改为要引用的列名即可。

（5）引用当前学生第1学期的成绩信息，选择A5：E15单元格区域，在编辑栏中输入：=OFFSET（成绩表！$B$1，MATCH（$L$2&"1"，成绩表！$A$1：$A$793，0）-1，0，SUM（（成绩表！$A$2：$A$793=$L$2&"1"）*1），5），按组合键。公式中双引号包含的1表示引用第1学期成绩，$A$1：$A$793表示成绩表中有数据的区域。

（6）选择相应的单元格区域，重复（5）的操作引用其他学期的成绩信息，注意要将公式中双引号包含的1改为对应的学期。

3 修饰成绩卡

由于每学期所修课程数不相同，当成绩卡模板中的行数多于学期课程数时，多于的行就会显示#N/A错误；当学期课程数只有1门时，所有行都会显示相同结果；如图4所示。为了避免这种情况，可以通过条件格式来修饰表格，操作方法如下：

（1）选择A5：E15单元格区域，单击功能区中的【开始】-〉【样式】-〉【条件格式】-〉【新建规则】打开【新建规则格式】对话框，选择【使用公式确定要设置格式的单元格】，在【为符合此公式的值设置格式】框中输入：=ISNA（$A5），再单击【格式】按钮打开【设置单元格格式】对话框，将字体颜色设置为白色，单击【确定】按钮关闭所有对话框。

（2）重复（1）的操作，将公式改为：=$A5=$A4完成对第1学期成绩的修饰。

（3）重复（1）、（2）的操作，依次完成对其他学期成绩的修饰。操作过程中要注意单元格区域的选择和公式的变化。

4 浏览成绩卡

（1）单击功能区中的【开发工具】-〉【控件】-〉【插入】，在表单控件工具箱中单击【数值调节钮】，在“成绩卡”工作表的U11单元格处拖动鼠标画一个数值调节钮。

（2）右击数值调节钮在弹出的菜单中单击【设置控件格式】命令。在【控制】选项卡的【单元格链接】引用框中输入U10，再单击【确定】按钮。

（3）单击数值调节钮的增加和减少按钮即可顺序浏览成绩卡。

5 连续打印成绩卡

（1）单击功能区中的【开发工具】-〉【控件】-〉【插入】，在表单控件工具箱中单击【按钮】，在“成绩卡”工作表的空白处拖动鼠标画一个按钮。

（2）在打开的【指定宏】对话框中单击【新建】按钮。在【代码】窗口中输入如下代码。

Sub 按钮1_Click（）

x=InputBox（"请输入起始序号："）

y=InputBox（"请输入终止序号："）

For i=x To y Step 1

Range（"u10"）=i

Sheets（"成绩卡"）.PrintOut

Next i

End Sub

（3）右击按钮可以将按钮文字改为“连续打印”。

（4）单击【连续打印】按钮，根据提示输入打印起止序号即可批量打印成绩卡。

6 零散打印成绩卡

（1）在“学生基本信息”工作表中添加一个【打印当前学生成绩】按钮，在【代码】窗口中输入如下代码。

Sub 学生基本信息_按钮1_Click（）

Sheets（"成绩卡"）.Range（"u10"） = ActiveCell.Row - 1

Sheets（"成绩卡"）.PrintOut

End Sub

（2）利用查找功能找到要打印成绩的学生学号或姓名，单击【打印当前学生成绩】按钮即可打印当前选中学生的成绩卡。

用这种方法实现数据的管理和打印，可以在基础表（这里指学生基本信息表和学生成绩表）中管理和維护数据，相关的修改会自动反应在打印报表中。在产生新的数据时，直接将新数据添加到基础表的后面或者覆盖基础表中原来的数据就可以得到新的打印报表。从而在Excel中实现了数据管理和报表打印的分离，不但可以制作各种复杂的特殊报表，还可以大幅度提高工作效率。

作者简介：陈开华（1983，11-），男，贵州省桐梓县，本科，办公自动化