这个自动统计表可以自动统计最高分、最低分、总分、平均分、名次等数据信息,还可以根据自定条件以不同的颜色显示分数。自动统计表做好以后还可以保存成模板,以便以后使用。文章末尾提供.xls文件供大家下载参考。
每到学期结束时,教师的一项重要工作就是要统计学生的成绩,在电脑逐步普及的今天,我们就把这项繁杂的工作交给Excel去自动完成吧。
涉及函数:AVERAGE、COUNTIF 、IF、ISERROR、MAX、MIN、RANK、SUM
1、启动Excel,同时选中A1至K1单元格,按“格式”工具条上的“合并及居中”按钮,将其合并成一个单元格,然后输入统计表的标题(参见图1)。
(图片较大,请拉动滚动条观看)
2、根据统计表的格式,将有关列标题及相关内容输入到相应的单元格中(参见图1)。
3、选中J3单元格,输入公式:=SUM(C3:I3),用于计算第一位学生的总分。
4、选中K3单元格,输入公式:=RANK(J3,$J$3:$J$62),计算出第一位学生总分成绩的名次(此处,假定共有60位学生)。
注意:如果将上述公式修改为:=COUNTIF($J$3:$J$62,”>=”&J3),同样可以计算出第一位学生的名次。
25、同时选中J3和K3单元格,将鼠标移至K3单元格右下角的成“细十字”状时(通常称这种状态为“填充柄”状态),按住左键向下拖拉至K62单元格,完成其他学生的总分及名次的统计处理工作。
6、分别选中C63、C64单元格,输入公式:=MAX(C3:C62)和=MIN(C3:C62),用于统计“语文”学科的最高分和最低分。
7、选中C65单元格,输入公式:=IF(ISERROR(AVERAGE(C3:C62)),””,AVERAGE(C3:C62)),用于统计“语文”学科的平均分。
注意:直接用“=AVERAGE(C3:C64)”公式也可以计算平均分,但是,如果成绩表中没有输入成绩时,这一公式将显示出一个错误的值,因此,我们用“ISERROR”函数来排除这一错误。
8、选中C66单元格,输入公式:=SUM(C3:C62),用于统计“语文”学科的总分。
9、同时选中C63至C66单元格,用“填充柄”将上述公式复制到D63至J66单元格中,完成其它学科及总分的最高分、最低分、平均分和总分的统计工作。
至此,一个基本的成绩统计表制作完成(参见xls文件下载),下面我们来进一步处理一下。
10、选中C3单元格,执行“格式、条件格式”命令,打开“条件格式”对话框(如图2),按最左边一个方框右侧的下拉按钮,在随后出现的下拉列表中,选“公式”选项,并在右侧的方框中输入公式:=C3>=AVERAGE(C3:C62),然后按“格式”按钮,打开“单元格格式”对话框,将字体颜色设置为“蓝色”。
再按“添加”按钮,仿照上面的操作,添加一个公式为“=C3
注意:经过这样的设置后,当学生的“语文”成绩大于或等于平均分时,显示蓝色,反之显示红色。
311、再次选中C3单元格,按“格式”工具条上的“格式刷”按钮,然后在D3至J3单元格区域上拖拉一遍,将上述条件格式复制到相应的区域中,完成其他学科及总分的条件格式设置工作。
12、再一次选中C3单元格,打开“条件格式”对话框,将其中的两项公式修改为:=C3>=AVERAGE($C$3:$C$62)和=C3
依照上面的操作,将D3至J3单元格的条件格式中的公式修改为绝对引用(添加“$”符号)。
注意:这样做的目的,是为了将其条件格式,通过“格式刷”复制到下面的各单元格中。
13、同时选中C3至J3单元格,按“格式”工具条上的“格式刷”按钮,然后在C4至J62单元格区域上拖拉一遍,将上述条件格式复制到相应的区域中,完成其他学生各科成绩及总分的条件格式设置工作。
把学生的成绩填入到表格中试试看,效果不错吧。
如果你经常要统计学生的成绩,我们将其保存为模板,方便随时调用。
414、将工作表中的学生成绩等内容删除,执行“文件、保存(另存为)”命令,打开“另存为”对话框(如图4),按“保存类型”右侧的下拉按钮,在随后出现的下拉列表中,选“模板(*.xlt)”选项,然后给定一个名称(如“成绩统计”),按下保存按钮。
15、以后需要统计成绩时,启动Excel,执行“文件、新建”命令,展开“新建工作簿”任务窗格(如图5),点击其中的“本机上的模板”选项,打开“模板”对话框,双击“成绩统计”模板,即可新建一份新的成绩统计表。
16、将学生的成绩填入相应的单元格中,取名保存即可快速完成成绩统计处理工作。