设置Excel表格一键统计学生成绩

统计学生成绩是每位教师在日常教学中必不可少的一件事，它是教师了解和掌握学生学习情况的一个窗口，也是学校对一个班级教学质量评估的重要数据来源和依据之一。但对学生成绩的统计却是一件繁杂、耗时的事。

为了节省对学生成绩的统计时间，做到事半功倍，笔者想了一个一劳永逸的办法，就是使用Excel一键统计学生成绩。

为此，我设计了两张表格。第一张表(表1)中的“忠(1)”到“游枫”的8个工作表分别是对单个班学生考试成绩进行统计(大家可以根据自己的需要设置工作表的数量)，此表既可以作为期末考试成绩统计表，也可以作为日常测试成绩统计表。

表1　　

第二张表(表2)中的“总表”是学校对某个年级学生考试成绩进行综合统计，此表既可对中心校本部同一年级同一学科学生考试成绩进行统计(如，A5:Q8)，也可以对中心校所管辖的所有同一年级同一学科学生考试成绩进行统计(如A9:Q10)。

表2

以上两张统计表看似空白表格，其实表中包含了一些Excel的函数和自定义公式，当我们在第一张工作表中所有的“成绩”这一列录入学生考试成绩后，一切就OK了。那如何实现“一键统计”呢?下面一起来做吧!

设计统计表

打开Excel，建立第一个工作表，首先将Sheet1、Sheet2工作表重命名为自己定义的名称，如“总表”和“忠(1)”，然后单击“忠(1)”工作表标签，打开“忠(1)”工作表，根据各自具体情况设计成绩统计表(特别提示：只需设计A1:V3单元格区域的内容，其余单元格无需设计)，如“标题”、“序号”、“姓名”、“成绩”……全部设计的内容如第一张表(表1)。

巧设统计函数和公式

要实现统计自动化，就要给统计表设置统计函数或公式，这是至关重要的一步。单击统计表中的“忠(1)”工作表标签，打开“忠(1)”成绩统计表(如：忠信中心小学2009-2010学年第一学期期末考试成绩统计表)，对要统计项目名称的下一行各单元格设置函数或公式见下表。

美化表格 体现人性

为了美化工作表，张扬个性，体现人性化，可以为工作表作以下设置：

1.录入错误警告

单击菜单栏中的“数据”→“数据有效性”，打开“数据有效性”面板，单击“设置”，在“有效性条件”中设置如下：“允许(A )”设置为：小数 ，数据设置：介于，最小值：0，最大值：100。

单击“出错警告”，勾选“输入无效数据时显示出错警告(S)”，“样式(I)：”设为“停止”，“出错信息(T)：”设为“朋友，你录入错误了!请点击‘取消’重新录入!”，最后单击“确定”按钮。若录入的数据超出设置范围，系统会自动报警。

2.重复录入提示

为防止重复录入学生的姓名，我们将为学生姓名所在的列进行一些提示性的设置，如：首先选中B4:B63单元格，单击菜单中的“格式”→“条件格式”， 打开“条件格式”对话框，在“条件1(1)”中选择“公式”，在右边输入框中输入条件求和函数“=COUNTIF($B$2:$B4，$B4)>1”， 在“格式(F )…”中设置一种背景颜色和字体格式，最后单击“确定”，再“确定”即可。

3.隐藏数据“0”

有时表格中有许多的数值为0，这样使得表格显得模糊不清，看上去十分的不爽，那么如何隐藏表格中的0数值呢?方法是：单击打开“忠(1)”工作表，选取N4:U4单元格区域，单击菜单栏中的“格式”→“单元格格式” →“数字” →“分类(C)”→“自定义” →“类型”，在“类型”中输入“0;-0;;@”(不包含引号，“@”表示半角符号)，最后单击“确定”按钮。表格中想不显示的“0”就被隐藏了。若要显示“0”，则只需要单击菜单栏中的“格式” →“单元格格式” →“数字” →“分类(C)”→“常规” →“确定”，即可将被隐藏的“0”显示出来了。

复制工作表 建立模板

通过对第一张工作表(即“忠(1)”)的多项设置后，现在我们开始复制其他班级或学校的学生考试成绩统计的工作表，建立一个工作簿：右键单击工作表标签中的“忠(1)”工作表，选择“插入”→“工作表”→“确定”，然后按F4键，每按一次F4键，将重新插入一张新工作表，然后重命名其他工作表。

单击“忠(1)”工作表，单击列号与行号相交的空白处，全选“忠(1)”工作表，单击“忠(2)”工作表标签，打开“忠(2)”工作表，右键单击A1单元格，选择“粘贴”，完成“忠(2)”与“忠(1)”工作表的一切设置工作，如此这般操作，继续完成“忠(3)”至“游枫”工作表的克隆工作。

建立“总表”工作表，总表就是对所有学校和班级进行汇总统计。单击“忠(1)”工作表标签，打开“忠(1)”工作表，选取F4:V4单元格区域，右键单击该选定的单元格区域，点击“复制”按钮，单击“总表”工作表标签，打开“总表”工作表，右键单击B5单元格，点击“粘贴”按钮，单击R5单元格右下角的粘贴图标，单击选择“粘贴选项”→“链接单元格”单选项。并以此类推，将“忠(2)”至“游枫”工作表的统计项目复制粘贴到“总表”中。

“总表”中的“总计”栏一行，也相应地设置好统计的公式或函数，至此，所有公式和函数设置完成。

最后进行工作表标签设置，右键单击“总表”工作表标签，左键点击“选定全部工作表(s)”，再次右键单击“总表”工作表标签，左键点击“工作表标签颜色(T)…”设置自己喜爱的颜色即可，体现个性。

一键统计，一劳永逸

通过以上的一番设置后，将全部工作表保存起来，文件类型可以为“模板(*.xlt)”。以后你只要改动两个地方就可以一劳永逸了：一是工作表标签;二是优生人数、优生率函数中优生成绩标准(如：小学一、二年级优生标准为90分)，我们只要将“=COUNTIF(C4:C63，">=80")”中的“80”改为“90”即可，由此举一反三。