Excel函数在成绩统计中的应用

摘要：在学生成绩统计中，计算虽并不复杂，但比较繁琐。期末考试后，多名教师需要处理多门成绩，总工作量相当巨大。采用手工统计，易出错、效率低。该文作者通过实践摸索，巧妙使用Excel函数，制作成绩统计模板，教师只需要正确输入原始数据，自动进行成绩统计。此方法与手工统计相比方便、准确、高效，解决了手工统计中易出错、效率低的问题。

关键词：总评人数；总评成绩；分段统计；班总评分

中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)27-6684-01

1 模板功能

输入或粘贴学生姓名后，根据姓名多少自动统计总人数；输入平时成绩和期末成绩，自动计算总评成绩，不及格分数以粗体突出显示；根据成绩自动判定学生状态：若平时成绩>0且期末成绩=0者为缺考、平时成绩和期末成绩均为0者为退学；根据学生状态统计缺评人数，计算总评人数和班总评分（平均分）。以上所有统计过程全部自动进行，边输入边统计，数据输入完毕，立刻显示统计结果，没有任何延迟。

2 模板制作（下载地址：/source/3519869）

在E6单元格输入公式=IF(B6="","",ROUND(0.3*C6+0.7*D6,0))，向下填充至E42。在K6单元格“选择性粘贴”E3处的公式，并向下填充至K42处，计算出总评成绩。

在F6单元格输入公式=IF(COUNTA(B6:B42,H6:H42)-COUNTIF(F6:F42,"退学")-COUNTIF(L6:L42,"退学")=0,"",COUNTA(B6:B42,H6:H42)-COUNTIF(F6:F42,"退学")-COUNTIF(L6:L42,"退学")-COUNTIF(F6:F42,"=缺考")-COUNTIF(L6:L42,"=缺考"))，向下填充至F42，在L38单元格“选择性粘贴”F6处的公式，并向下填充至L42。

在B44和B46分别输入公式=IF(G45="","",COUNTIF(F6:F42,"=不及格")+COUNTIF(L6:L42,"=不及格"))和=IF(B44="","",B44/G45)，统计0~59的人数的百分比。

在C44和C46处分别输入公式=IF(G45="","",COUNTIF(E6:E42,">=60")+COUNTIF(K6:K42,">=60")-D44-E44)和=IF(C44="","",C44/G45)，统计60~79的人数的百分比。

在D44和D46分别输入公式=IF(G45="","",COUNTIF(E6:E42,">=80")+COUNTIF(K6:K42,">=80")-E44)和=IF(D44="","",D44/G45)，统计80~89的人数的百分比。

在E44和E46分别输入公式=IF(G45="","",COUNTIF(E6:E42,">=90")+COUNTIF(K6:K42,">=90"))和=IF(E44="","",E44/G45)，统计90~100的人数的百分比。

在G45输入公式=IF(COUNTA(B6:B42,H6:H42)-COUNTIF(F6:F42,"退学")-COUNTIF(L6:L42,"退学")=0,"",COUNTA(B6:B42,H6:H42)-COUNTIF(F6:F42,"退学")-COUNTIF(L6:L42,"退学")-COUNTIF(F6:F42,"=缺考")-COUNTIF(L6:L42,"=缺考"))，统计总评人数。

在H45输入公式=IF(G45="","",COUNTIF(F6:F42,"=缺考")+COUNTIF(L6:L42,"=缺考"))，统计缺评人数。

在I45输入公式=IF(G45="","",(SUM(E6:E42)+SUM(K6:K42))/G45)，统计班总评分（指平均分）。

3 保护工作表

1）设置需要保护的区域

①设置保护区域：按Ctrl+A选中整个工作表，再按Ctrl+1打开“单元格格式”设置对话框，选择“保护”选项卡，同时选中“锁定”和“隐藏”后确定。

②设置可编辑区域：按住Ctrl键，通过单击或拖动鼠标，同时选中A1、A2、C3、I3、B6:D42、H6:J42和K45后，按Ctrl+1取消“锁定”和“隐藏”的选择。

2）保护工作表：单击“工具/保护/保护工作表”，选中“保护工作表及锁定的单元格内容”，设置“取消工作表保护时使用的密码”，在“允许此工作表的所有用户进行”列表中，仅选中第二行的“选定未锁定的单元格”，单击“确定”按钮后，“重新输入密码”后再次单击“确定”按钮。

3）保护效果：未保护区域可自由输入，保护区域不能选中和编辑。

4 根据需要进行调整

1）撤消保护：若要编辑工作表，需要先撤消工作表保护。方法是：单击“工具/保护/撤消工作表保护”，输入事先设置好的保护密码 xuelixin，单击“确定”按钮，工作表解除保护，方可自由编辑。

2）根据实际需要，适当改变行高（B5:13，A4:14），可按B5和A4纸输出结果。

3）总评成绩保留整数，若需要保留一位小数，可将“ROUND(0.3*C6+0.7*D6,0”公式中最后的0改为1，其他需要改动的公式做相同的改动。

4）本模板按“平时成绩30%+期末成绩70%”评定总成绩，若需要更改比例，只需将“ROUND(0.3*C6+0.7*D6,0”公式中0.3和0.7改为相应数值即可（注意两数之和必须等于1）。

5）使用max和min函数直接显示最高分和最低分，使用rank函数实现自动排序。

参考文献：

[1] 李翠梅.运用Excel实现学生成绩统计与试卷分析的程序化[J].电脑与信息技术,2011(2).