第5章 Excel综合应用——成绩表统计及分析
Excel成绩统计及常用统计方法
统计和分析学生成绩1、按学生成绩排列名次Excel中提供了一个用于统计名次的RANK( )函数，RANK( )函数可统计出一个单元格中的数值，在一个单元格区域所有数值中的排名（排序）结果。
RANK( )函数的用法：RANK( x1,x2,x3) 。
其中x1为要排名次的单元格，x2为排名次的单元格区域（即排列名次的范围），x3用于指定升序或降序排名次。
用RANK( )函数可以极为方便地按学生成绩计算名次，例如对图5-13中的学生成绩表要按总分计算出每个学生的名次，并将名次结果分别填入J2:J101区域的各个单元格中。
首先，按前面介绍的方法，利用SUM( )函数在I2:I101中计算出全部学生的总分；单击单元格J1，在其中输入“总分名次”，单击单元格J2，在其中输入公式“=RANK (I2,I$2:I$101)”后按【Enter】键即可统计出表中第1个学生的名次。
由于在对每个学生统计名次时，其范围始终应该是在区域I2:I101，所以公式的RANK( )函数中使用了“$”符号，用于标识固定不变的单元格区域，即“绝对引用”，以便下一步处理中利用复制公式的方法计算其他学生的名次。
其次，单击选定单元格J2，然后拖动其“填充柄”标记至单元格J101，即可将公式复制到下面的各个单元格，计算出其余学生的名次。
2、按分数段统计人数分布利用Excel中的频度函数FREQUENCY( )，可以较方便地统计分布于各个分数段的学生人数，统计出的分布数据对于分析试题难度是否适当是十分有用的。
设计较合理的试题，其相应的成绩应该呈正态分布。
FREQUENCY( )函数可以分别计算出一个单元格区域中的数据在各个不同数据段分布的数目，因此计算结果将是一组数值（即数组），这一组数值将分别填入工作表中同一列的若干个单元格中。
FREQUENCY( )函数的用法：FREQUENCY( x1,x2)。
其中的参数x1可用于指定要进行频度统计的单元格区域，x2指定要统计的各个数据段的分段点的数值。
excel表格学生成绩的统计
一、介绍Excel表格学生成绩的统计重要性学生成绩的统计是教育工作中非常重要的一环。
通过对学生成绩的统计，学校和教师可以全面、客观地了解学生的学习情况，及时发现问题并采取相应的措施加以改进。
而Excel表格作为一种强大的数据处理工具，可以帮助教师们快速、准确地进行学生成绩的统计分析，为教学工作提供有力的支持。
二、学生成绩的Excel表格统计步骤1. 收集学生成绩数据教师需要收集学生的考试成绩数据，包括考试科目、每位学生的得分等信息。
这些数据可以通过试卷、考试记录册等途径获取。
2. 创建Excel表格在收集到学生成绩数据后，教师需要利用Excel软件创建一个新的工作表格，将学生成绩数据输入到表格中。
在创建Excel表格时，应该注意合理的表格结构，例如每一列代表一个不同的考试科目，每一行代表一个学生。
3. 数据的录入与整理在Excel表格中录入学生成绩数据后，教师需要对数据进行整理，包括检查数据的准确性和完整性，并进行必要的修正。
另外，还需要添加一些必要的数据信息，例如学生尊称、班级、考试日期等。
4. 进行统计分析完成学生成绩数据的整理后，Excel软件提供了丰富的统计分析功能，例如可以计算每个学生的总分、平均分，以及不同科目的平均分、最高分、最低分等。
通过这些统计分析，教师可以直观地了解学生的学习情况，找出学习中存在的问题和特点。
5. 制作数据可视化图表除了进行数值上的统计分析外，Excel还提供了丰富的图表工具，可以帮助教师将学生成绩数据呈现出直观的可视化图表，例如折线图、柱状图等。
利用图表工具，教师可以更加清晰直观地看到学生的学习成绩情况，并据此进行针对性的教学工作。
6. 结果输出根据统计分析和图表呈现的结果，教师可以输出相应的学生成绩报告，以便及时向学校管理部门和学生家长进行反馈。
这些学生成绩报告也是学生学习情况管理和教学评估的重要依据。
三、Excel表格学生成绩统计的优势1. 准确性通过Excel表格进行学生成绩统计，可以提高统计过程的准确性。
利用Excel进行学生成绩统计分析
利用Excel进行学生成绩统计分析在学校的教学过程中，对学生成绩的处理是必不可少的，为了体现考试成绩的公正公平性，更好地为教学服务,提高教师的工作积极性,我们需要对学生的考试成绩进行认真的分析，这就要求我们算出与之相关的一些数值：像每一个同学的总分及班名次、级名次，各科分数的平均分，各科的优秀率及及格率等等，如果用Excel来处理这些数据则非常简单，下面就我在实际工作中的一点儿经验，简单谈一谈用Excel处理学生成绩。
处理学生成绩时所分析的项目包括参考班级，参考人数、平均分，及格人数、及格率、优秀人数、优秀率，最高分、最低分以及各分数段所占的人数：600分以上人数、500～599 分人数、400～499 人数、300～399 人数、300 分以下人数。
这个统计比较复杂，且工作量大，如果用人工计算，不但效率低，且出错机率大，而用EXCEL函数可以很好解决这个问题。
我们可采用以下方法：新建空白Excel工作簿，将自动建立的Sheet1、She et2、Sheet3工作表重命名为成绩表、分析表、分数段统计表。
其中各科考试成绩表用于输入各科成绩。
(一)设计各科成绩表在各科考试成绩表第一行中输入分析名称，如“** 中学2007～2008 学年度第一学期高一月考成绩表”，在第二行从A1单元格开始顺序输入记录字段名：考号、姓名、班级、语文、数学、英语、物理、化学等。
假设全级学生有674 人，10个班,所以从第3行到第676 行用来输入所有学生各科的成绩。
如图1所示.图11.求总分：主要用SUM函数，其语法格式为SUM(Ref)，此处Ref为参与计算的单元格区域。
例如：SUM(B2:E2)是表示求B2、C2、D2、E2四个单元格内数字的和。
另外还用到SUMIF函数，语法格式为SUMIF(Range,Criteria,Sum_ran ge)，其功能是根据指定条件对若干单元格求和，参数Range表示引用，用于条件判断的单元格区域。
Excel综合应用—成绩统计分析表
或复制工作表”对话框，如图所示； ④ 在打开的“移动或复制工作表”对话框中，选择“工作簿”列表
中的工作簿，可以将所选工作表移动、复制到已打开的其他工作簿 中；选择“下列选定工作表之前”列表中的表名，可以确定表的新 位置； ⑤ 选择“建立副本”为复制操作，取消“建立副本”为移动操作； ⑥ 按“确定”按钮，完成操作。
1.3 工作表格式化
1．自定义格式
1．自定义格式
（1）设置数字格式 （2）设置对齐格式 （3）设置字体 （4）设置边框线 （5）设置列宽、行高
设置数字格式和对齐格式示例：
格式对话框
对齐方式对话框
自动换行 缩小字体填充
水平居中与合 并单元格
对齐格式示例
设置字体格式示例
字体对话框
设置边框线示例：
1．设置打印区域
ቤተ መጻሕፍቲ ባይዱ
设置打印区域的方法
方法1：先选定打印区域所在的工作表，选定 需要打印的区域，然后选定“文件”|“打印区 域”|“设置打印区域”项，Excel 2003就会把 选定的区域作为打印的区域。
方法2：首先选定工作表，选择需要打印的区 域，打开“文件”菜单，单击菜单中的“打印 ”命令，就会弹出一个的打印设置对话框。
内部加表格线
清除所有框线
预览效果 画对角线
所选区域加 外框线
选择边框线的线 型和粗细
清除或添加 左边线
边框对话框
选择边框线 的颜色
设置行高、列宽
1．用鼠标拖动设置行高、列宽 将鼠标移至行（列）标号格线处，光标为双向箭头状
，这时拖动标号格线可改变行高（列宽）； 若调整多行或多列的高度或宽度，可以先选定需更改
第5章 Excel综合应用——成绩表统计与分析
2017/1/23
第8章 Excel综合应用1—成绩表统计与分 析
8
条件统计函数COUNTIFS的功能
• COUNTIFS函数的功能是： 将条件应用于跨多个区域的单元格，并计算符 合所有条件的次数。 • 语法格式： COUNTIFS (Criteria_range1, Criteria1, [Criteria_range2, Criteria2]…)
8.2.1 用统计函数与公式制作“成绩统计表”
跨工作表的单元格引用 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF与COUNTIFS的使用 公式计算 单元格数据的清除与删除 逻辑判断函数IF的使用 条件格式
8.2.2 用IF函数与条件格式制作“各科等级表”
8.2.3用图表向导制作成绩统计图
2017/1/23 第8章 Excel综合应用1—成绩表统计与分 析 7
条件统计函数COUNTIF的使用
• 在“成绩统计表”的相应单元格中，用COUNTIF函数统计 “各科成绩表”中4门课程的“缺考”人数、 “90-100”以 及“小于60”分数段的人数。
– 在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的 函数为 =COUNTIF(各科成绩表!D2:D38,"缺考")。 – 用相同方法可以统计出“90-100”以及“小于60”分数段的人数。
2017/1/23 第8章 Excel综合应用1—成绩表统计与分 析 21
思考题（二）
• 对单元格数据的清除和删除操作有何区别 ？ • IF函数有几个参数，每个参数的意义是什么 ？ • 如何设置条件格式？
2017/1/23
第8章 Excel综合应用1—成绩表统计与分 析
22
问题分析
利用Excel进行学生成绩统计分析
利用Excel进行学生成绩统计分析案例背景期末考试后，除了要统计各个分数段人数分布情况外可能还要统计年级和各班的及格人数与及格率，优秀人数与优秀率，以及各班里前、后若干名（如年级前20名和后20名）的人数和比例等。
本案例以某中学出一年级4个教学班100名学生的期末考试的3科成绩和总分为样本，统计全年级4个班级学生的期末考试及格人数与及格率，优秀人数与优秀率。
1创建原始数据表Step 1创建工作簿、重名工作表创建工作簿“综合统计表.xls”，然后将工作表重命名为“统计表”，删除多余工作表。
Step2输入原始成绩①在单元格A49输入表格标题：“高一年级期末考试成绩统计表”，选择你好单元格区域A49:G49，单击“合并及居中”按钮，字体、字型、字号设置为黑体加粗16号。
②在单元格区域A50：G50输入字段名：“班级”，“学号”，“姓名”，“语文”，“数学“，“英语”和“总分“。
③在单元格区域A51：F150输入全年级4个班100名学生的“语文”，“数学“，“英语”成绩④在单元格G51输入公式：“=SUM(D51:F51) ”，然后按键确认，计算出第一个学生的“总分”。
⑤选中单元格G51，双击单元格G51右下角的填充柄即可完成其余学生“总分”的统计工作。
⑥选中单元格区域A50：G150，按图2样式设置边框，添加底纹，文本居中显示。
2统计及格人数与及格率Step 1创建统计表框架在单元格区域A1:J9按图3样式输入“高一年级各班及格人数与及格率统计表”框架信息。
Step2输入统计分数线①选中单元格区域B3:D3，输入“60”，按组合键确认。
②在单元格E3中输入“180”。
Step3统计及格人数①在单元格B4输入如下公式，然后按组合键确认。
“=SUM(ISNUMBER(D$51:D$150)*(D$51:D$150>=B3))”②在单元格B5输入如下公式，按组合键确认。
“=SUM((($A$51:$A$150=1))*(D$51:D$150>=B$3))”③在单元格区域B6：B8分别输入如下公式，按组合键确认。
EXCEL在学生成绩统计分析中的综合应用
…
～
ｗ
～
～
…
～
＾ ＾
—
Ｍ
＾ ＾
ｕ
＾ ｕ
ｕ＾
＿＾ ＾ ＾
＿ Ｉ
Ｍ
—
—
＾
、
—
ｗ
沓獬
黜
—、 — — — … —
—— —— ｆ
渫砖崔鞯
ｌ
洼樟 样 太擎 整 供舷置 嚣擎 姆 计蒹 料带 聋 威ｊ 撤攀 蟪阿 体育 蟪麓 差 塞 堆耕 地藕 基础 论 粪语 辱论 纯 窭薄 蛭耕 蜜
一
学生成绩一般以百分之为主 ， 没有达到 ６ Ｏ分的成绩 为不及 格， 这些成绩 就要 特殊对待 （ 如通知其补考 、 重修等 ） 为了使 成 ， 绩 表一 目了然 ， 便于统计 ， 就需要把这些成绩 区别于其它成绩显
、
成绩表 的建 立与整 理
如 小于 ６ Ｏ分或没有成绩的就显示为红色 的。为了达到 在 Ｅ Ｃ Ｌ工作表 中建立如 图 １的工作表 。 １ 已经输入 示 ， 图 １ ＸＥ 图 是
解析 ： 此公式分为两部 分分 子部分和分母部分 ， 分子为 “ ∑
取得学分 ，根据此的要求 ，在统计 １ 号学生总学分 的单元格 内 （ 课程 的成绩 ×课程 的学分 数 ） Ｕ Ｒ Ｄ Ｃ （Ｃ ：Ｐ ， ｃ ＝Ｓ ＭＰ Ｏ Ｕ Ｔ ￥ ４ ￥ ４ ￥ （ Ｖ 如 ４单元 格 ） 键入 条件 求和公 式“ ｓ Ｍ Ｒ Ｄ Ｃ （ （Ｃ ： ￥ ：Ｐ ３ ” ＝ Ｕ Ｐ ０ ｕ Ｔ 一 ￥ ４ ３ ￥ ￥ ） ，含义为学生 已修课程与其课程学分成绩的积的和 ，
Ｅ ＣＥ Ｘ Ｌ在 学 生成 绩 统计 分析 中的综 合应 用
巧用EXCEL公式进行成绩统计与分析
巧用EXCEL公式进行成绩统计与分析成绩的统计与分析是学校教学中的一个重要环节。
通过对学生每次考试的成绩的合理分析，可以及时了解学生在学习过程中出现的问题和老师在教学中存在的不足，进而及时改正。
很多老师在教学管理中或多或少的使用EXCEL进行成绩的统计与分析，然而由于对EXCEL 使用不够深入，不能正确合理的使用公式，往往也要花费很大的精力。
其实只要熟练地掌握以下几个公式，就可以大大地提高工作的效率。
下面就以我在实践中得出的一点经验为例，讲解一下学生成绩的统计与分析一般过程，以期与大家交流与分享：一、成绩的录入1、单科成绩录入。
现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争，考前全年级统一进行考场和座号的安排，每个考场里分布几个甚至十几个班的学生，考完试卷按考场装订阅卷，所以成绩录入时应按考场和考号顺序录入。
这样增加了透明度，公平合理。
如表1：2、成绩汇总。
把各个备课组送交上的成绩表，通过按学号排序，全年级按班以及班里学号的顺序将成绩汇总成一张总表，如表2：二、成绩分析1、排名统计。
很多学校都是把每次考试成绩来做为每个科任老师教学能力的重要指标，统计每个科任老师所教科目在全年级各名次段中占有的人数比例是必不可少的内容。
用“=RANK(D28,$D$28:$D$32)”求出每个学生的各科成绩在全年级的排名，注意“$”符号在该函数中的作用，是不能省略的。
然后统计每个科目在每个名次段的人数。
假设在D列是一个班的语文成绩的排名我们可以采用EXCEL公式“=COUNTIF(成绩表!D28:D32,"<=200") “来完成计算该科任老师所教科目在全年级前200名的学生人数。
如表3：表32、利用图表分析每个班的每个科目的情况。
根据该班某科的分数段分布可清晰得出是否发展正常。
利用公式“=COUNTIF(成绩表!C28:C32,">130")-COUNTIF(成绩表!C28:C32,">=140")”可以轻松获得成绩在130~140的人数，如表4。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
8.3 案例总结 8.4 课后练习
2018/11/12
第8章 Excel综合应用1—成绩表统计与分 析
3
8.1成绩表统计案例分析
• 任务表” 及“成绩统计图。
• 解决方案
2018/11/12
第8章 Excel综合应用1—成绩表统计与分 析
=COUNTIF(各科成绩表!D$2:D$38,">=60")/COUNT(各科 成绩表!D$2:D$38) 或：=1-B13/B6
2018/11/12 第8章 Excel综合应用1—成绩表统计与分 析 11
思考题（一）
如何引用同一工作簿中不同工作表的数据？ 引用单元格的数据与复制粘贴数据有何区别？ 统计函数COUNT与COUNTA的区别是什么？ 条件统计函数COUNTIF和COUNTIFS的应用场合 是分别什么？ • 在“各科成绩表”工作表中分别统计出男、女 生人数应该用什么函数？试写出函数的表达式 。 • • • •
2018/11/12 第8章 Excel综合应用1—成绩表统计与分 析 7
条件统计函数COUNTIF的使用
• 在“成绩统计表”的相应单元格中，用COUNTIF函数统计 “各科成绩表”中4门课程的“缺考”人数、 “90-100”以 及“小于60”分数段的人数。
– 在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的 函数为 =COUNTIF(各科成绩表!D2:D38,"缺考")。 – 用相同方法可以统计出“90-100”以及“小于60”分数段的人数。
2018/11/12 第8章 Excel综合应用1—成绩表统计与分 析 12
单元格数据的删除与清除
• 复制“各科成绩表”工作表，并将复制后的工作表 更名为“各科等级表”。在“各科等级表”中，清 除4门课程列中的分数内容；清除“总分”列的所 有属性；删除分数统计所在单元格区域。
– 按Delete键，只清除选定单元格的内容，而保留了单元 格的格式。 – 在“开始”选项卡的“编辑”组中单击“清除”按钮 ，在弹出的列表中选择“全部清除”选项。此时单元 格中的内容和格式全部被清除。 – 在“开始”选项卡的“单元格”组中单击“删除”按 钮 ，这时单元格区域的内容会由其他内容替代。
计算机应用基础
学习目标
熟练掌握统计函数COUNT、COUNTIF及 COUNTIFS的使用方法 熟练掌握逻辑判断函数IF的使用方法 掌握条件格式的设置方法 熟练使用图表的创建、修改及格式化
2018/11/12
第8章 Excel综合应用1—成绩表统计与分 析
2
目录
8.1 成绩表统计案例分析 8.2 实现方法
2018/11/12
第8章 Excel综合应用1—成绩表统计与分 析
6
条件统计函数COUNTIF的功能
• COUNTIF函数的功能：
统计指定区域内满足给定条件的单元格数目。
• 语法格式：
COUNTIF(range,criteria) – 其中：Range 指定单元格区域，Criteria表示指 定的条件表达式。 – 条件表达式的形式可以为数字、表达式或文本 。例如，条件可以表示为 60、"60"、">=90" 或 "缺考"等。
2018/11/12 第8章 Excel综合应用1—成绩表统计与分 析 10
公式计算
• 在“成绩统计表”中，计算各门课程的优 秀率和及格率 。
– 计算优秀率 ：
=COUNTIF(各科成绩表!D$2:D$38,">=90")/COUNT(各科 成绩表!D$2:D$38) 或：=B9/B6
– 计算及格率 ：
2018/11/12
第8章 Excel综合应用1—成绩表统计与分 析
8
条件统计函数COUNTIFS的功能
• COUNTIFS函数的功能是： 将条件应用于跨多个区域的单元格，并计算符 合所有条件的次数。 • 语法格式： COUNTIFS (Criteria_range1, Criteria1, [Criteria_range2, Criteria2]…)
4
跨工作表的单元格引用
• 打开“统计表（素材）.xlsx”文件，选择“成绩统计表”工 作表。 • 从“各科成绩表”中，将四门课程的“班级平均分”、“ 班级最高分”和“班级最低分”的数据引用到“成绩统计 表”中的相应单元格中 。
2018/11/12
第8章 Excel综合应用1—成绩表统计与分 析
5
统计函数COUNT及COUNTA的使用
– 其中： – Criteria_range1 必需。在其中计算关联条件的第一个区 域， – Criteria1必需。条件表达式； – Criteria_range2, Criteria2, ...可选。附加的区域及其关联 条件。最多允许 127 个区域/条件对。
第8章 Excel综合应用1—成绩表统计与分 析 9
8.2.1 用统计函数与公式制作“成绩统计表”
跨工作表的单元格引用 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF与COUNTIFS的使用 公式计算 单元格数据的清除与删除 逻辑判断函数IF的使用 条件格式
8.2.2 用IF函数与条件格式制作“各科等级表”
8.2.3用图表向导制作成绩统计图
2018/11/12
条件统计函数COUNTIFS的使用
• 在“成绩统计表”的相应单元格中，用 COUNTIFS函数统计“各科成绩表”中4门课 程的“80-89”、“70-79”以及“60-69”分数 段的人数。
– 在打开的“函数参数”对话框中填写上两组单 元格范围和条件，如下图所示，单击“确定” 按钮。 – 用相同方法可以统计出“70-79”以及“60-69”分 数段的人数。
• 将“各科成绩表”中各门课程的“参考人数”和“应考人 数”的统计结果放置到“成绩统计表”中的相应单元格中 。
– 在“开始”选项卡的“编辑”组中单击“自动求和”按钮 旁边的 下拉箭头，在弹出的列表中选择“计数”选项。可计算出“参考 人数”。 – 在“公式”选项卡的“函数库”组中单击“其他函数”按钮 ，在 下拉列表中选择“统计”选项，在弹出的下拉列表框中选择 “COUNTA”。可计算出“应考人数”。