第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。
项目七 Excel高级应用——学生成绩统计与分析
项目七 Excel高级应用——学生成绩统计与分析Microsoft Office Excel 2003的主要功能是对输入的数据进行管理。
除了在上一项目中我们所了解的对于数据的输入、格式化操作和美化操作以外，我们还可以利用Excel 2003软件相应功能对输入的数据进行排序、筛选、分类汇总等工作，从而更有效的对于数据进行管理，同时可以根据相关数据制作相应图表，更加直观有效的展示数据信息。
7.1 项目提出学校每学期都要对班级学生的考试成绩进行有效的统计管理，其中包括将各科成绩准确的录入、单科成绩及总成绩的排序、汇总、筛选等功能都要简单的实现，同时还要对相应的一些内容进行图表制作，更加有效的观察整体成绩的分布，作为班长的小张决定使用Excel 2003来完成自己班级学生基本情况表的制作，进而帮助班主任更加方便的了解班级学生学习情况。
7.2 项目分析学生成绩是学籍管理中非常重要的信息。
每个学期结束后，每个班级都要统计本班学生本学期的成绩，一份合理的学生成绩表可以帮助教师了解每个学生及整个班级的学习情况。
例如，10高职21班期末考试已经结束，小张将以本次考试成绩为基础数据制作一份学生成绩表，分析本班学生学习状况。
本项目中我们将利用Excel 2003提供的各种功能完成对学生成绩的表数据的录入、美化、计算、排序、筛选、分类汇总和图表等操作，具体工作通过以下几个任务来完成：任务1：制作学生成绩表。
任务2：完成数据计算。
任务3：制作学生成绩统计图表。
项目最终完成后效果如图7-1所示。
图7-17.3项目实施及相关知识点任务1 制作学生成绩表任务描述：本任务主要完成的工作是制作学生成绩表——10级高职21班学生成绩表.xls。
通过本任务使用户在制作过程中能熟练掌握Excel 2003的输入数据的方法，设置单元格的字体、边框、图案等，并掌握条件格式的设置。
该任务是对已学知识的巩固，任务完成最终效果如图7-2所示。
图7-2为完成本任务，我们需要掌握如下几个知识点：●掌握Excel 2003输入数据的方法；●掌握Excel 2003美化单元格格式的方法；●掌握Excel 2003工作表的操作方法●掌握Excel 2003条件格式的设置知识要点：1. 修改单元格字体格式2．设置单元格底纹3．设置单元格边框以上内容在前面项目中已经讲述，这里不在赘述。
Excel综合应用——学生成绩统计与分析
Excel综合应用——学生成绩统计与分析实验六Excel综合应用——学生成绩统计与分析注意：在做题的过程中请注意随时保存文件。
一、教学目标◆掌握单元格的命名◆掌握冻结窗口的应用◆掌握公式和常用函数的使用◆熟悉数组公式的使用◆掌握图表的创建二、相关知识点◆冻结窗口◆函数的使用RANK函数返回一个数字在数字列表中的排位。
COUNT函数返回指定范围内数字型单元格的个数。
COUNTIF函数统计指定区域内满足给定条件的单元格数目。
◆公式的使用◆图表三、实验步骤打开“练习.xls”，并在其中完成以下操作。
（一）设置单元格格式1.将sheet1重命名为“年级成绩总表”2.将第3行至691行行高设置为19.53.将A3：A691、J3:J691背景颜色设置为“浅绿”4.将B3：B691显示效果设置为“##班”，并居中对齐方法：选中B3：B691单元格，单击鼠标右键，选择【设置单元格格式】，在【数字】标签下，选择“自定义”分类，在“类型（T）”输入“##班”，在【对齐】标签下，水平对齐项选择“居中”5.将E3：J691设为小数位数为1方法：选中E3：J691单元格，单击鼠标右键，选择【设置单元格格式】，在【数字】标签下，选择“数值”分类，在“小数位数”输入“1”6.将第一行和第二行单元格进行冻结窗口设置。
方法：选中单元格A3，单击【窗口】|【冻结窗口】命令（二）输入学号1.在D3中输入学号“0001“，观察其显示结果2.在D3中输入一个单引号（’），再输入0001，观察显示结果3.以序列方式填充D4:D691单元格，并设置D3：D691中内容居中对齐方法：鼠标指针指向单元格D3的右下角，鼠标指针变为“+”，按住鼠标左键向下拖动填充柄，在拖动过程中填充柄的右下角显示填充的数据，拖动到目标单元格D691时释放鼠标即可（三）计算总成绩利用公式或sum函数计算总成绩，总成绩=语文+数学+英语+物理+化学（四）利用Rank函数录入年级排名RANK函数返回一个数字在数字列表中的排位。
EXCEL做考试成绩分析
EXCEL做考试成绩分析自己动手，用EXCEL做一个考试成绩分析应用小软件XXXXXX长期以来，在每次考完试后，不仅要去处理学生的成绩，还要计算和分析教师的成绩。
这是一件非常烦琐和无可奈何的事情。
如果只是对学生的总分、名次、各科的平均分进行统计的话，用EXCEL去完成这项工作是一件非常容易的事情，然而许多学校还要对任课教师进行成绩统计，依据就是该任课教师所教该学科的“三率和”。
“三率和”是指将某一学科的平均分+及格率（及格人数/参加考试的人数*100）+优秀率（优秀人数/参考人数*100）。
实际上我们用EXCEL是可以轻松做到的。
只需将学生的考试成绩按照一定的格式录入，那么EXCEL就可以自动去完成计算三率和的工作。
下面我们通过一个实例来看一下。
比如录入初一年级某次考试成绩：录入结束后，利用EXCEL自带的一些函数，就能方便地把结果统计出来。
如图：在开始研究之前，大家有必要了解一些EXCLE函数方面的知识。
公式和函数是Excel最基本、最重要的应用工具，是Excel的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。
1．数组公式的输入数组公式的输入步骤如下：（1）选定单元格或单元格区域。
如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。
2）输入数组公式。
3）同时按“Crtl+Shift+Enter”组合键，则Excel自动在公式的两边加上大括号{ }。
特别要注意的是，第（3）步相当重要，只有输入公式后同时按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。
否则，如果只按Enter键，则输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示出一个计算结果。
2．条件求和SUMIF函数SUMIF函数的功能是按照指定条件对若干单位格求和，公式为:SUMIF(range,criteria,sum_range)式中range—用于条件判断的单元格区域,只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。
Excel表格在统计学生成绩中的应用
excel函数在学生成绩统计中的应用Excel俗称电子表格，是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函数和数据库，本文仅介绍用Excel2KX进行成绩统计和一些日常统计的函数。
一．考试成绩的统计（一）对班学生成绩的总体评估统计1.假设E4：E53单元格存放某班学生的语文考试成绩，若要计算该科的总分、人平分、与格人数、与格率、优秀人数、优秀率、最高（低）分、分段统计等。
（1）参考人数统计：若要将参考人数放在E54单元格中，只需选定该单元格并插入“计数函数”：“COUNT”并选定要计数的单元格区间E4:E53或（输入）公式：“=COUNT(E4:E53)” 回车即可；（2）学科总分的统计：若要将该科的总分存放在E55单元格中，只需选定该单元格并插入“求和函数”：“SUM”并选定要计数的单元格区间E4:E53或（输入）公式：“= SUM (E4:E53)”回车后即可计算出该科总分(注：在E55单元格只显示计算结果。
如要修改该公式，只需选定该单元格，在编辑栏内即可修改)。
（3）学科平均分的统计：若要将该科人平分存放在E56单元格中，选定该单元格插入平均值函数“AVERAGE”并选定求值区间“E4：E53”或输入公式：“＝AVERAGE(E4：E53)”，回车后即可计算出人平分(注：数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的“0”则参与运算)。
（3）学科与格人数统计：若要将该科的与格人数存放在E57单元格中，选定该单元格插入计算某个区域内满足指定条件的单元格数目函数的条件函数：“COUNTIF”，插入函数的方法是单击“插入”菜单中的“插入函数（F）…”命令，弹出一个“插入函数”对话框，在其中的“选择函数”下的列表框中选定选定“COUNTIF”函数，单击“确定”，弹出一个“函数参数”指定对话框，在其中的“Range”右边矫形框中输入统计的区间E4：E53或（单击矫形框右端的压缩图标，再在工作表中选择统计区间E4：E53，再单击“函数参数”压缩框中右端的扩展图标，返回扩展的“函数参数”对话框中），再单击对话框中的“Criteria”右边的矩形框，并键入与格条件：“>=60”，最后单击“确定”即可；也可以手动编辑输入公式与条件：“＝COUNTIF(E误值均被忽略)。
运用Excel进行学生成绩的统计分析
❑学习，能够使用Excel实行学生成绩的统计分析。
❑统计各分数段的人数及比例❑图示成绩分布（饼图绘制）❑学生总成绩及名次❑成绩等级及积点计算❑试卷质量评价❑示例1：用IF函数计算成绩等级❑>=90—A 80-89—B 70-79—C 60-69—D <60—E❑示例2：用COUNTIF函数统计各分数段的人数及比例❑示例3：用FREQUENCY函数统计人数❑FREQUENCY(data_array,bins_array)❑Data_array 为一数组或对一组数值的引用❑Bins_array 为间隔的数组引用，该间隔用于对data_array 中的数值实行分组。
❑对于返回结果为数组的公式，必须以数组公式的形式输入。
❑数组的使用❑选择区域(H81：H85)❑输入公式“=FREQUENCY(C71：C77,G81：G85)”❑使用组合键“Ctrl+Shift+Enter❑示例4：使用数据分析统计❑工具—加载宏—分析工具库❑工具—数据分析—直方图❑成绩分段统计（P59-62）❑各分数段人数占总人数的比例❑饼图❑注意数据源，切忌勿先算比例❑图的修饰和格式化❑示例5：学生成绩名次计算❑总评成绩计算Chapter0502.xls❑绝对引用=C2*$H$2+D2*$I$2❑混合饮用=C2*H$2+D2*I$2❑区域命名=C2*平时比例+D2*期末比例❑名次计算❑=RANK(E2,$E$2：$E$77)❑示例6：用IF函数计算成绩等级❑=IF(ISNUMBER(C2),IF(C2>=85,"A",IF(C2>=75,"B",IF(C2>=60,"C","D"))),IF( ISBLANK(C2),"缺考","输入有误"))❑ISBLANK值为空白单元格❑ISNUMBER值为数字❑ISTEXT值为文本❑INDEX函数❑INDEX(reference,row_num,column_num,area_num)❑返回引用中指定单元格或单元格区域的引用❑Reference为对一个或多个单元格区域的引用❑Row_num为引用中某行的行序号，函数从该行返回一个引用❑Column_num为引用中某列的列序号，函数从该列返回一个引用❑MATCH( )❑返回在指定方式下与指定数值匹配的数组中元素的相对应位置❑MATCH(lookup_value，lookup_array，match_type)❑Lookup_value为需要在数据表中查找的数值，它能够是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。
Excel应用--学生成绩统计与分析
2. COUNT和COUNTIF函 数的使用
9.2 实现方法
9.2.3 制作成绩统计与分析表
3. 创建图表
9.3 案例小结
在使用公式和函数计算时，要注意如下几点。 ① 公式是对单元格中数据进行计算的等式，输入 公式前应先输入“=”。 ② 函数的引用形式为：函数名（参数1，参数 2，… ），参数间用逗号隔开。如果是单独使用 函数，要在函数名称前输入“=”构成公式。 ③ 复制公式时，公式中使用的单元格引用需要随 着所在位置的不同而变化时，使用单元格的相对 引用；不随所在位置变化的使用单元格的绝对引 用。
9.2.2 制作各班级成绩表
9.2 实现方法
2.IF函数的 使用
9.2.2 制作各班级成绩表
9.2 实现方法
3.宏的使用
9.2.2 制作各班级成绩表
9.2 实现方法
4.创建按钮
9.2.2 制作各班级成绩表
9.2 实现方法
1.OFFSET 函数的使 用
9.2.3 制作成绩统计与分析表
9.2 实现方法
THE END
9.2.1 制作年级成绩总表
9.2 实现方法
2.设置单 元格格 式
9.2.1 制作年级成绩总表
9.2 实现方法
3.冻结窗 口
9.2.1 制作年级成绩总表
9.2 实现方法
4.输入数 据
9.2.1 制作年级成绩总表
9.2 实现方法
5.单元格计 算
9.2.1 制作年级成绩总表
9.2 实现方法
1.重命名单 元格
9.1 学生成绩统计与分析案例分析
9.1.2 解决方案
老师根据学生的考试成绩，制作了“年级成 绩总表”、“各班级成绩表”和“成绩统计与分 析”工作表。
如何利用Excel进行考试成绩分析
如何利用Excel进行考试成绩分析在老师的日常工作中，对学生的成绩进行统计分析管理是一项非常重要也是十分麻烦的工作，每次考试后，主任要求班主任自已统计本班成绩，尽快上报教导处。
流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。
如果能够利用EXCEL强大的数据处理功能，就可以让各位老师迅速完成对学生的成绩的各项分析统计工作。
下面就向各位老师介绍利用EXCEL进行学生成绩管理的方法。
一、录入学生基本信息首先，我们要新建一个“Excel工作表”，并将工作表命名为“xx考试xx班xx科成绩统计表”。
然后，打开该工作表，开始录入学生的班别、座号、姓名、考试科目（语文、数学、英语、政治……可以根据具体的考试科目输入）、考试成绩等信息（如图1）。
如果我们希望把不及格的学科突出显示，最好用红色显示。
于是拖拉选择C2:G□（说明：“□”为班级最后一位学生的行号），然后执行“格式”菜单下“条件格式”命令，弹出“条件格式对话框”。
我们把条件设为小于60分（或72分）的用红色显示，点击“格式”按钮，把颜色设为红色。
再按“确定”按钮。
二、计算学生考试总分、平均分输入完每位学生的各科成绩后，接下来我们就要计算学生的总分和平均分了。
首先，在H2单元格处输入公式"=sum(d2:g2)"，然后拖动填充柄向下填充，便得到了每人的总分。
在I2单元格处输入公式"=average(d2:g2)"，然后拖动填充柄向下填充，便得到了每人的平均分。
（如图2）平均分只需保留一位小数，多了没用。
所以选中第I列，用鼠标右键单击，从弹出的快捷菜单中选"设置单元格格式（F）…"（如图3），在数字标签中选中"数值"，小数位数设置为1位。
（如图4）三、按总分给学生排出名次。
在J2单元格处输入公式"=RANK(J2,J$2:J$□,0)"（说明：“□”为班级最后一位学生的行号），然后拖动填充柄向下填充，即可得到每人在班中的名次（如图5）。
EXCEL考试成绩分析表
EXCEL---考试成绩分析表案例背景某中学高二年级期中考试阅卷，成绩登统之后，需要对整个年级和各个班级的期中考试成绩实行分析比照，要排出每个学生每门课程和总分的年级名次。
还要求统计全年级和每个班级各学科的平均分、最高分、最低分与标准差。
以便对考试情况实行综合分析。
要完成排名次的工作最简单的方法就是通过排序来完成。
前面已经学习了用A VERAGE、MAX、和MIN函数分别求平均分、最高分和最低分，随着学习的深入您会发现要完成一件工作，Excel可能会提供多种技术来实现，我们应该寻求一种更为简捷的方法来解决问题，以提升工作效率。
本节将介绍数据的排序和分类汇总解决上述问题。
关键技术点要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识：查找与替换选择性粘贴数据的排序，分类汇总●函数应用：SUM函数最终效果展示2.4.1创建考试成绩分析表Step1 输入表格字段名和原始数据创建一个新的Excel工作簿，在工作表sheet1中按照“最终效果展示”效果图2输入年级“考试成绩分析表”的表格字段信息和原始数据。
Step2计算总分①将光标移到单元格H2，单击桌面上的常用工具栏“∑“自动求和按钮，因为从单元格A2到单元格G2存放的全是数字，所以Excel默认的求和区域为A2:G2，这时用鼠标重新选择准备求和的区域C2:G2或直接在编辑栏输入C2:G2，按键确认即可完成第一个学生的总分计算。
②将光标移到单元格H2右下角，当光标由原来的空心十字变为黑色实心十字时，双击左键即可完成公式的复制填充，从而计算出其余学生的总分。
Step3隐藏“总分“的公式①单击单元格H2，按组合键，从而快速选中矩形单元格区域H2:H121，②单击常用工具栏按钮”复制“，然后单击菜单“编辑”→”选择性粘贴“，③从弹出的”选择性粘贴“对话框的”粘贴“选项框中选择“数值”，单击“确定“按钮或按键确认即可完成公式隐藏或者说去掉公式的任务。
第5章 数据统计和分析软件Excel 2010课件
第5章 数据统计和分析软件Excel 2010
范例2-数字格式化
1. 设置数字格式
• 使用“单元格格式”对话框设置数字格式 “开始”选项卡-“单元格”组-“格式”选项下的“设置单 元格格式”
第5章 数据统计和分析软件Excel 2010
范例2-数字格 式化
2.条件格式
在Excel中，为用户提供了条件格式，它使用户可以 根据单元格的内容有选择地和自动地应用单元格的格 式，避免人工处理时繁琐、重复的操作。 “开始”选项卡下“样式”组中的“条件格式”
• 使用“设置单元格格式”对话框设置对齐 方式
第5章 数据统计和分析软件Excel 2010
范例2-文本
5.设置列宽和行高
1）设置列宽
• 方法一：使用鼠标在列标处拖动 • 方法二：“开始”选项卡“单元格”组中“格式” 选
项下的→“列宽”
2）设置行高
• 方法一：使用鼠标在行标处拖动 • 方法二：“开始”选项卡“单元格”组中“格式” 选
• 通过Windows开始菜单 选择“开始”→“所有程序”命令， 然后在弹出的菜单中选择“Microsoft Office” →“Microsoft Excel 2010” 命令，即可启动Excel 2010。
• 通过桌面快捷方式
第5章 数据统计和分析软件Excel 2010
Excel2010的工 作界面
6.边框线的设置
• 利用“边框”按钮设置单元格的边框
单击
第5章 数据统计和分析软件Excel 2010
范例2-边框1
6. 边框线的设置
• 利用“设置单元格格式”对话框设置单元 格的边框
第一步：先选样 式和颜色
项下的→“行高”
第5章 数据统计和分析软件Excel 2010
EXCEL 在 成绩分析中的应用
EXCEL 在成绩分析中的应用轮机1101班佟文强0121105200122(一）问题说明如图为某班级期末成绩，利用EXCEL计算该班级的每名同学的总分和平均分，并用图表表示出各科的各同学分数情况，并用语言分析。
（二）问题说明在EXCEL中求取总和与平均值，并作图表分析1 求总分点击H列单元格，在公式中设置自动求和，得出每名同学的各科成绩和。
四门课程成绩在从C4到G16 第一个学生总分函数H4：=SUM （C4：G4），其他学生公式往下拉。
2求平均分,点击G列单元格，在公式中设置自动求平均数，得出每名同学平均分。
四门课程成绩在从C4到G16 第一个学生的平均分函数G4：=SUBTOTAL（1，C4：G4），其他学生往下拉。
计算出总和与平均值后，做出图表，更直观的看出各科情况。
1、选择用于创建图表的数据。
2、在“视图”菜单中，单击“插入”-“图表”，调出“图表向导”。
3、在“标准类型”对话框中，选择所需要的图表类型，就创建一个嵌入式图表。
2、3、（三）技术分析求每个人的总分用sum函数，平均分用average函数，平均分各分数段的人数最好的方法是利用COUNTIF(X，Y)函数。
其中有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;第二个参数Y为统计条件，要加引号。
(四)结论通过图表我们可以了解到的情况数学及格人数为12人，不及格人数为4人。
英语及格人数为13人，不及格人数为3人。
语文及格人数为14人，不及格人数为2人。
计算机及格人数为13人，不及格人数为3人。
（五）收获通过EXCEL可以使问题简单直接，通过对excel的学习，我可以利用其分析一些数据问题，做出相应的图表，是问题更加清晰明了，二次，也掌握了其一些高级应用来解决生活中遇到的一些细微的数据问题，可以排序筛选，统计图，复杂的函数自动套用公式等等。
通过学习，我也能体会到现代时代，软件应用的重要性，丰富了人们的生活，是生活更加简洁方便。
- 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”。可计算出“应考人数”。