运用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 各分数段人数占总人数的比例 90 11 84 70 79 32 89 80 89 27 63 60 69 19 74 90 示例 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 为需要在数据表中查找的数值 它可以是数值 或数字 文本 或逻辑值 对数字 文本或逻辑值的单元格引用 Lookup array 是可能包含所要查找的数值的连续单元格区域 Match type 为数字 1 0 或 1 它说明 Excel 如何在 lookup array 中查找 lookup value 如果 match type 为 1 函数 MATCH 查找小于或等于 lookup value 的最大数值 如果 match type 为 0 函数 MATCH 查找等于 lookup value 的第 一个数值 如果 match type 为 1 函数 MATCH 查找大于或等于 lookup value 的最小数值 示例 7 用 INDEX 和 MATCH 函数计算成绩等级 INDEX H 2 H 11 MATCH 1 C2 F 2 F 11 C2104 COUNTA C 2 C 46 利用十字柄往右 自动填充 把大于某值的分数改一下 因为总分不一样 上面的例子是总分 130 分的 假 设总分 100 分的 把 104 改成 80 即可 这是优秀率 同理在单元格 N5 中输入 COUNTIF C 2 C 46 78 COUNTA C 2 C 46 即可算出合格率 在格式 单元格格式 数值分类中 选择 百分比 即出 符号 统计前三名 在单元格 N6 中输入 LARGE C 2 C 46 1 或 MAX C 2 C 46 1 往右自动填充 得出各科成绩最高分 LARGE C 2 C 46 2 可得各科第二名成绩 LARGE C 2 C 46 3 可得各科第三名成绩 统计后三名 在单元格 N6 中输入 SMALL C 2 C 46 1 或 MIN C 2 C 46 1 往右自动填充 得出各科成绩最低分 SMALL C 2 C 46 2 可得各科倒数第二名成绩 SMALL C 2 C 46 3 可得各科倒数第三名成绩 第四 利用 if 函数 对科目成绩进行等级划分 在单元格 K2 中输入 IF D2 78 B C A 使用 if 函数进行判断 数学成绩达到优秀的 等级划分为 A 在合格与优秀之间的 等级为 B 不合格的 等级为 C 二 利用二 利用 excelexcel 统计函数进行数据分析统计函数进行数据分析 对于一组数据 只获得一些常规的总分 排名 优秀率和合格率还远远不够 对于数 据之间的分布规律还没表达出来 这时可以使用 Excel 提供的统计函数来实现 例如 AVERAGE 平均值 STDEV 样本标准差 VAR 样本方差 KURT 峰度系数 SKEW 偏度系 数 MEDIAN 中位数 即在一组数据中居于中间的数 MODE 众数 即在一组数据中出现 频率最高的数值 等 第一 计算样本方差 VAR C2 C46 利用十字柄往右自动填充即可完成各个科目的样本方差计算 第二 计算标准差 STDEV C2 C46 利用十字柄往右自动填充即可完成各个科目的标准差计算 样本中各数据与样本平均数的差的平方的平均数叫做样本方差 样本方差的算术平方 根叫做样本标准差 样本方差和样本标准差都是衡量一个样本波动大小的量 样本方差或样本标准差越大 样本数据的波动就越大 结果分析 我们看到数学和英语的样本方差和标准差较大 数学科目的标准差为 26 84 英语的标准差更是达到了 33 137 说明对于数学和英语 学生的掌握程度参差不 齐 差别较大 第三 计算峰度系数 KURT C2 C46 利用十字柄往右自动填充即可完成各个科目的峰度系数计算 峰度系数 Kurtosis 用来度量数据在中心聚集程度 在正态分布情况下 峰度系数 值是 0 正的峰度系数说明观察量更集中 有比正态分布更长的尾部 负的峰度系数说明 观测量不那么集中 有比正态分布更短的尾部 类似于矩形的均匀分布 结果分析 从上述表中数据可以看书语文和作文科目数值较大 8 5291 和 22 25 说 明大部分学生掌握较好 成绩均比平均分高 第四 计算偏度系数 SKEW C2 C46 利用十字柄往右自动填充即可完成各个科目的峰度系数计算 偏度系数 Skewness 用来度量分布是否对称 正态分布左右是对称的 偏度系数为 0 较大的正值表明该分布具有右侧较长尾部 较大的负值表明有左侧较长尾部 结果分析 语文和作文科目呈现为较小的负数 说明较多分布在左侧 三 利用三 利用 excelexcel 柱形图直观分析柱形图直观分析 第一 首先得先找个能统计某区域内数据的频率分布函数 这里我们用 FREQUENCY data array bins array 函数 data array 表示用来计算频率的区域 bins array 表示为前面数组进行分割一列数值 各个科目数据分数段分隔点为 max 值 max 值 均值 2 均值 min 值 均值 2 min 值 在单元格 R33 中输入 FREQUENCY C 2 C 46 R26 V26 按下 Ctrl Shift Enter 组合键确认 往右自动填 充 即完成语文科目各个分数段的人数统计 第二 插入图表 选择图表向导中的柱形图 数据区域选择为上述统计好的人数 即 分布 1 区域 完成柱形图 此方法可以较为直观地提供各个分数段人数的分布情况 这与上述峰度系数和偏度系 数的计算是相辅相成的 各个科目的柱形图如下 语文科目的数据分布图 数学科目的数据分布图 英语科目的数据分布图 政治科目的数据分布图 历史科目的数据分布图 作文科目的数据分布图 四 分析与评价四 分析与评价 统计规律表明 学生的智力水平 包括学习能力 实际动手能力等呈正态分布 因而 正常的考试成绩分布应基本服从正态分布 柱形图中形状即为 中间高 两边低 从上述几个数据和柱形图分析来看 对于语文和作文 峰度系数为较大的正数 柱形 图上绝大部分分布均在左侧 学生掌握得较好 政治和历史在分布上也较为积极 数学和 英语除了标准差较大 柱形图上分布也不均匀 学生掌握程度相差太大 个性化时代使得学校不再是用 克隆 的方法 批量生产标准件 的地方 必须看到 学生之间的差异是客观存在的 只有根据学生的实际情况 因材施教 才能切实提高学校 教学质量 第一 对语文 作文 政治和历史科目 开展 提优补差 活动 利用前面各科成绩 等级的 ABC 分类 可以直观的看出学生各科的平衡 根据 水桶理论 针对性重点是 辅导 和家长沟通也很直接 对 C 类 学生采取正面引导和心理沟通 多表扬 多鼓励 帮助他们尽早树立自信心 同时对 B 类 学生要时时加以鞭策 居安思危 第二 对数学 英语科目 可根据 ABC 等级开展 走班教育 所谓 走班教学 就是根据大纲和教材要求 针对不同类型的学生 设计不同层次的 教学目标 提出不同层次的学习要求 给予不同层次的教学与辅导 进行不同层次的检测 以达到他们各自的最佳状态 对不同的层次 作为教师 对哪些知识点必须详尽讲解 让 学生尽快掌握 必须做到心中有数 结语 excel 作为一个很好的工具 让我们更容易地对数字进行分析 在数字中发现 问题 从而有的放矢的探究教学中 班