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

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）撤消保护：若要编辑工作表，需要先撤消工作表保护。方法是：单击“工具/保护/撤消工作表保护”，输入事先设置好的保护密码 xuelix