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

火端文库

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

如何运用excel函数统计查询某班各科成绩平均分
如何运用excel函数统计查询某班各科成绩平均分

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

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

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

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

确定,返回条件格式对话框,单

击 “添加”,设置 60 到 79、小于 60 分等分数段的格式(见图 1)。

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

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

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

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

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

www.qikan.com.cnUfmunFfXKcs746WF 3.各科逐一分析 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)。

www.qikan.com.cnUfmunFfXKcs746WF

4.每科各分数段人数 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 区域进行分隔的各段数值的出现频率数目。

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

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

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

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

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

www.qikan.com.cnUfmunFfXKcs746WF 最后,我们就可以使用已建立的模板来处理学生的成绩。

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

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

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

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

www.qikan.com.cnUfmunFfXKcs746WF

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

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

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

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

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

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

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

类别:办公技巧 || 添加到搜藏 | 分享到 i 贴吧 | 浏览(4418) | 评论 (5)

上一篇:轻松破解 Word 与 Excel 的文档密码 最近读者: 登录后, 您就出 现在这里。

aptx0409 网友评论: 1 真的一无所 知下一篇:迅雷快车地址加解密工具 0.2_简...sam729826879 creditmgm原味_庚澈sxab003607wux0210824077213 myshmilu2007-12-08 18:15 | 回复 各位,针对这篇文章我有两个问题:一是保存为模板后,如果学生人数发生变化,再用这个模板还准确吗(因 为文章是以 57 名学生来套用公式的)?二是我一面看文章一面实践,只是在统计“每科各分数段人数”中按照 文章的介绍输入公式后,怎么也不行,统计“优秀人数”“及格人数”和“不及格人数”时,按文章提示的公 式也不可以。

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

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

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

4 真的一无所 知 5 匿名网友 2008-07-16 21:12 | 回复 同类文章:http://hi.baidu.com/jiachengcheng1/blog/item/d4c72673f8b1b11a8701b0f2.html2010-01-07 18:44 | 回复 怎样统计各学科均在 60 分以上人数,使用什么函数公式?发表评论:姓 名: 注册 | 登录网址 或邮 箱: (选填)

内 容:插入表情▼ 闪光字验证 码:请点击后输入四位验证码,字母不区分大小写发表评论©2010 Baidu