利用Excel函数统计学生个人总分、平均分、排名等

查看文章
让成绩分析智能起来---利用Excel函数统计学生及格人数、及格率、最高分、最低分、各分数段分布等内容
2007-12-08 14:48
本文可以学到
1 利用Excel函数统计学生个人总分、平均分、排名等项目
2 统计总人数、及格人数、及格率、最高分、最低分、各分数段分布等内容
3 模板的制作
4 局域网联机汇总各科、各班级成绩的方法
网络大补贴
EXCEL教学成绩统计系统 V7.5/soft/25163.html
Excel自动生成网页，学生成绩网上查/pcedu/soft/office/excel/0511/725239.html
用分类汇总做成绩统计/course/5_office/excel/excelshl/2007214/22721.html
期中考试结束了，各位教师是否开始忙于成绩的统计和分析？这里汇集了几位老师的实战经验，让我们和原始计算器说再见，借助Excel从繁重的劳动中解脱出来吧。

统计项目Excel来帮忙
运行Excel 2003，在成绩表第一行中输入以下内容：学号、姓名、各考试科目名称、总分、名次，输入学号时，用填充柄自动填充。

在学号下方依次输入“总人数”、“及格人数”、“及格率”、“总分”、“均分”、“最高分”、“最低分”。

各段成绩格式不同
选定所有录入学生成绩的单元格，进入“格式→条件格式”，在“单元格数值”范围中填入“80”与“100”，单击“格式”按钮，将单元格数值设置为自己喜欢的格式：如数值颜色为蓝色、字形为倾斜等，这样，在80到100之间的分数在输入后自动变为蓝色并倾斜。

确定，返回条件格式对话框，单
击“添加”，设置60到79、小于60分等分数段的格式（见图1）。

(1) 个人总分
单击总分下的单元格，在编辑栏中输入=SUM(C2:H2)，在下一个单元格中输入=SUM(C3:H3)，然后选定这两个单元格，将光标移动到两个单元格的右下角，到变成细十字，按住鼠标左键向下拖拉至最后一名学生，以后只要输入学生各科成绩，每位学生的总分即自动求出。

2.个人名次
在Excel中可以用单元格自动排序的方式给成绩排序，但不好处理同分数的名次，利用RANK函数则没有这样的问题。

方法是：总分位于I列，名次位于J列，在第一名学生的名次单元格中输入=RANK(I3,$I$3:$I$59)，该公式的目的是排出I3单元格的数据从I2到I59所有数据中的位次(假设该班共56名学生，学生的姓名、成绩等数据从第3行排到第59行)。

在第二名学生的名次单元格中输入= RANK(I4,$I$3:$I$59)，然后拖动鼠标，选中这两个单元格，利用自动填充的功能，在每个学生的名次单元格中输入RANK函数的公式。

这样只要将学生各科成绩输入到对应的单元格中后，RANK函数即自动求出该学生的名次，同分数的名次则自动排为相同的名次，下一个名次数值自动空出。

3.各科逐一分析
以“语文”为例。

在“总人数”单元格中输入公式=COUNT(C3:C59)，在“及格人数”单元格中输入= COUNTIF(C3:C59,">=72")，在“及格率”单元格中输入=COUNTIF(C3:C59,">=72")/COUNT (C3:C59)，[满分为120分为">=72"，若满分为100分则为">=60"] ，“总分”单元格中输入=SUM(C3:C59)，“平均分”单元格中输入=AVERAGE(C3:C59)，“最高分”输入=MAX(C3:C59)，“最低分”单元格输入=MIN(C3:C59)。

以上公式输入后可利用自动填充功能填充其他各科的公式（见图2）。

4.每科各分数段人数
可用FREQUENCY函数来实现。

在（A67：A74）区域输入39、49、59、69、79、89、99、109（即统计出40分以下 ~120分范围内组距为10的各分数段人数），同时选中c67至c75单元格区域，输入公式:=FREQUENCY(C3:C59,$A$67:$A $74)，按下“Ctrl+Shift+Enter”组合键进行确认，即可求出c67至c75区域中，按A67: A74区域进行分隔的各段数值的出现频率数目。

其他单元格可用自动填充功能实现。

有时要将优秀人数和不及格人数统计出来，在语文优秀人数的单元格中输入公式=COUNTIF(C3:C59, “>=80”)(假设各科的满分为100分)。

及格人数改为>=60，不及格人数改为<=59即可。

这样Excel就自动统计出各分数段人数了。

保存为模板文件
在“打印预览”中，按下“设置”，单击“工作表”标签，选中打印风格线，单击“页眉/页脚”标签，单击“自定义”，设置表格标题、页眉、页脚、纸张的大小、页边距，调整字体、字号、单元格的宽度及高度，然后“另存为”为“模板”，取名为“成绩统计”，确定，保存为模板文件。

最后，我们就可以使用已建立的模板来处理学生的成绩。

启动Excel，执行“文件”菜单中的“新建”，在对话框中出现的“常用”标签下找到已建立的“成绩统计”模板，双击，出现了原来的模板文件，输入学生新的成绩总分、各科的平均分、各分数段的人数，最高分、各学生的总分名次即自动统计得出。

最后打印输出，既美观又便于分析。

联机汇总各科成绩
利用Excel提供的共享工作簿命令，在局域网中对Excel文档联机处理，可以快速汇总各科、各班级的成绩。

在局域网中的一台电脑上新建一个Excel文档，命名为“学生综合成绩表”，设置好格式后存入一个专门的“学生成绩”文件夹，右击从快捷菜单中选择共享，弹出图3所示文件夹属性对话框，在共享标签中找到“网络共享和安全”，在“共享名”框中输入“学生成绩”（名称可任意），注意在图中圈中的两个复选框中必须都打上钩，设置好后，录入成绩的教师就能在任何一台联网微机上打开此综合成绩表。

(3)
(4)
光能打开还不行，所有人都能同时编辑，还要打开“学生综合成绩表”的“工具”菜单，选择“共享工作簿”命令，在“编辑”标签中勾选“允许多用户同时编辑”，同时“允许工作簿合并”，这样就可以多个人同时编辑了。

最后，所有录入成绩的教师从各自电脑上打开“网上邻居”，找到学生综合成绩表所在的计算机名称、共享文件夹名称——“学生成绩”（如果设置了密码，要告诉大家），打开学生综合成绩表，按学科、班级录入各自的成绩，并注意随时存盘，存盘时会有如图4所示提示。

若单元格被其他用户更改，在单元格中会有批注提示用户，可防止数据冲突。

使用此法，能充分利用现有电脑及教师资源，提高效率。

本文相关小提示
为防止别人更改数据，可以将成绩输入和成绩统计分别放在两个工作表中，如将成绩输入放在Sheet1中，而将成绩统计放在Sheet2中，并将Sheet2保护起来。

进入“工具→保护→保护工作表”，输入密码即可（此时需将函数公式中加入“Sheet1!”）。

如果只需评等级，设K3为某学生的成绩，要达到“优秀”、“良好”、“合格”、“不合格”四级综合评定的效果，只要输入：=IF (K3>=90,"优秀",IF(K3>=80,"良好",IF(K3>=60,"合格","不合格")))，再把结果用填充柄填充到其他单元格即可。

类别：办公技巧 | | 添加到搜藏 | 分享到i贴吧 | 浏览(4418) | 评论(5)
上一篇：轻松破解Word与Excel的文档密码下一篇：迅雷快车地址加解密工具 0.2_简...
最近读者：
登录后，您就出
现在这里。

aptx0409sam729826879creditmgm原味_庚澈sxab003607wux0210824077213myshmilu 网友评论：
1
真的一无所
知2007-12-08 18:15 | 回复
各位，针对这篇文章我有两个问题：一是保存为模板后，如果学生人数发生变化，再用这个模板还准确吗（因为文章是以57名学生来套用公式的）？二是我一面看文章一面实践，只是在统计“每科各分数段人数”中按照文章的介绍输入公式后，怎么也不行，统计“优秀人数”“及格人数”和“不及格人数”时，按文章提示的公式也不可以。

如果哪位老师有兴趣可以试一下，也可以共同探讨。

2
梅乡帅哥2007-12-09 08:50 | 回复不错不错，收藏了
3 网友:mhye2007-12-2
4 09:16 | 回复
因为那个要用到数组功能，必须要用数组公式的方法输入，也就是很关键的Ctrl+Shift+Enter组合键。

另外就
是要提前设置好分组区间。

4
真的一无所
知2008-07-16 21:12 | 回复
同类文章：/jiachengcheng1/blog/item/d4c72673f8b1b11a8701b0f2.html
5 匿名网友2010-01-07 18:44 | 回复
怎样统计各学科均在60分以上人数，使用什么函数公式？
发表评论：
姓
名：
注册 | 登录
网址
或邮
箱：
选填)
内
插入表情▼闪光字容：
验证
码：
©2010 Baidu。