利用Excel2003函数公式做成绩统计表

利用利用 Excel 2003 函数公式做成绩统计表函数公式做成绩统计表 在在 excel2003 电子表格中怎么才能计算成绩总分和个人电子表格中怎么才能计算成绩总分和个人 的名次的名次 统计项目 Excel2003 来帮忙 现在还很适用喔 现在还很适用喔 运行 Excel2003 在成绩表第一行中输入以下内容 学号 姓名 各考试科目名称 总分 名次 输入学号时 用填充柄自动填充 在学号下方依次输入 总人数 及格 人数 及格率 总分 均分 最高分 最低分 各段成绩格式不同 选定所有录入学生成绩的单元格 进入 格式 条件格式 在 单元格数值 范围 中填入 80 与 100 单击 格式 按钮 将单元格数值设置为自己喜欢的格式 如数 值颜色为蓝色 字形为倾斜等 这样 在 80 到 100 之间的分数在输入后自动变为蓝色并倾 斜 确定 返回条件格式对话框 单击 添加 设置 60 到 79 小于 60 分等分数段的格 式 个人总分 单击总分下的单元格 在编辑栏中输入 SUM C2 H2 在下一个单元格中输入 SUM C3 H3 然后选定这两个单元格 将光标移动到两个单元格的右下角 到变成细十字 按住鼠标左键向下拖拉至最后一名学生 以后只要输入学生各科成绩 每位学生的总分即 自动求出 个人名次 在 Excel 中可以用单元格自动排序的方式给成绩排序 但不好处理同分数的名次 利 用 RANK 函数则没有这样的问题 方法是 总分位于 I 列 名次位于 J 列 在第一名学生的名次单元格中输入 RANK I3 I 3 I 59 该公式的目的是排出 I3 单元格的数据从 I2 到 I59 所有数据中的 位次 假设该班共 56 名学生 学生的姓名 成绩等数据从第 3 行排到第 59 行 在第二名 学生的名次单元格中输入 RANK I4 I 3 I 59 然后拖动鼠标 选中这两个单元格 利 用自动填充的功能 在每个学生的名次单元格中输入 RANK 函数的公式 这样只要将学生各 科成绩输入到对应的单元格中后 RANK 函数即自动求出该学生的名次 同分数的名次则自 动排为相同的名次 下一个名次数值自动空出 各科逐一分析 以 语文 为例 在 总人数 单元格中输入公式 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 以上公式输入后可利用自动填充功能填充其他 各科的公式 每科各分数段人数 可用 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 不及格人数改为 80 IF H2 80 优秀优秀 按回车键 单击 按回车键 单击 J2J2 单元格 鼠标移到 出现十字形时 用填充柄从单元格 鼠标移到 出现十字形时 用填充柄从 J2J2 拖到拖到 J13 J13 优优 秀者即可在有关单元格出现秀者即可在有关单元格出现 优秀优秀 二字 没能评上优秀的 单元格中会出现英文字二字 没能评上优秀的 单元格中会出现英文字 FALSE FALSE 在空白处单击一下 注意公式中的标点符号必须在英文状态下输入 公式中的在空白处单击一下 注意公式中的标点符号必须在英文状态下输入 公式中的 等号等号 不能漏掉 最后请把不能漏掉 最后请把 FALSEFALSE 删除掉 删除掉 求各科及格率方法求各科及格率方法 1 1 先求各科及格人数 先统计语文科及格人数 方法是 在 先求各科及格人数 先统计语文科及格人数 方法是 在 B17B17 单元格输入以下公单元格输入以下公 式式 COUNTIF B2 B13 59 COUNTIF B2 B13 59 按回车键 可得语文科及格人数 在空白处单击一下 单击按回车键 可得语文科及格人数 在空白处单击一下 单击 B17B17 当 当 B17B17 单元格右下角出现十字光标时 用填充柄从单元格右下角出现十字光标时 用填充柄从 B17B17 拖到拖到 F17F17 释放鼠标 在空白 释放鼠标 在空白 处单击一下 就可得到各科及格人数 注意 公式中的标点附号必须在英文状态中输入 处单击一下 就可得到各科及格人数 注意 公式中的标点附号必须在英文状态中输入 注意等号注意等号 不能漏掉 不能漏掉 2 2 按住鼠标左键 从 按住鼠标左键 从 B18B18 拖到拖到 F18 F18 设置设置 B18 F18B18 F18 区域输入百分比的格式 方法是 区域输入百分比的格式 方法是 在在 B18 F18B18 F18 区域单击右键 在弹出的菜单中单击区域单击右键 在弹出的菜单中单击 设置单元格格式设置单元格格式 在弹出的对话框中 在弹出的对话框中 数字数字 选项卡的选项卡的 分类分类 列表中单击列表中单击 百分比百分比 单击 单击 确定确定 在空白处单击一下 在空白处单击一下 经过这样设置 在经过这样设置 在 B18 F18B18 F18 区域的计算结果都会以百分比显示 区域的计算结果都会以百分比显示 3 3 在 在 B18B18 单元格输入以下公式单元格输入以下公式 B17 12 B17 12 注 注 B17B17 为语文科及格人数 为语文科及格人数 1212 为应考人为应考人 数 按回车键 可得语文科及格率 单击数 按回车键 可得语文科及格率 单击 B18B18 单元格 当单元格 当 B18B18 单元格右下角出现十字光单元格右下角出现十字光 标时 按住鼠标左键 从标时 按住鼠标左键 从 B18B18 拖到拖到 F18 F18 释放鼠标 在空白处单击一下 就可得到各科及格释放鼠标 在空白处单击一下 就可得到各科及格 率 率 下面是有答案的效果图表 如果你以图表下面是有答案的效果图表 如果你以图表 1 1 数据来练习的 练习后可对照以下答案 数据来练习的 练习后可对照以下答案 注意 如果不小心操作错误 可单击左上角的注意 如果不小心操作错误 可单击左上角的 快速访问工具栏快速访问工具栏 的的 撤消撤消 按钮 按钮 撤消错误动作 要撤消几次错误动作就单击几次撤消错误动作 要撤消几次错误动作就单击几次 真的好轻松真的好轻松 ExeclExecl XPXP 数据统计全攻略数据统计全攻略 你是否经常需要进行数据统计吗 不知你是如何统计的 反正我是用 Execl 不论你统计 中遇到什么困难 它都会给你很好地解决 不相信 那你是统计方法没掌握好 赶快随我 来吧 学习结束后保证你数据统计好轻松 本文介绍的数据统计是指统计数据的个数或数量 并非指数据的运算 本次以某班级 学生的考试成绩为例进行讲解 举例不一定合适 意在抛砖引玉 一 行号统计法 例如 统计各科成绩中各分数段的人数 即统计 90 100 80 89 70 79 60 69 60 分以下的人数 将光标移动到政治成绩成在列 用鼠标单击工具栏中的 降序 按钮 政治成绩由高 到低排列 如图 1 所示 90 100 分数段中最低分数是 90 分 所在行行号为 20 则此分数 段人数是 20 1 19 在 80 89 分数段所在行的行号在 21 到 37 之间 则 80 89 分数段的人 数是 17 同样方法可统计出其它分数段的人数 图 1 各位请注意了 如果数据不在一列 而是在同一行内 能否迅速进行统计呢 那也难 不住 可以用行列转换法将行内数据转换到列内 然后再进行统计 具体操作是 选中行 中有关数据 单击工具栏中的 复制 按钮 再在目标列内右键单击 选择弹出菜单中的 选择性粘贴 命令 在打开的 选择性粘贴 对话框中选中 转置 前的复选框 单击 确定 退出 则数据都转换到一列内 根据行号统计法进行统计即可 二 选中统计法 上例中在统计 80 89 分数段人数时 也可以这样进行 首先进行排序 然后从最高分 89 分开始选中 向下拖动鼠标 到最低分 81 分结束 在拖动鼠标的过程中 工具栏的左 下角或选中部分的右下角出现一变动的提示条 如图 2 所示 提示条为 17R 1C 意指 选中的部分为 17 行 1 列 则统计得 80 89 分数段的人数为 17 图 2 此方法也适合在同一行内进行 以上两种方法诸位掌握了吗 很容易的 一学就会 需要对大家提示一下的是 以上 两种方法在统计各分数段人数时首先要进行排序 打乱了原来的次序 若不打乱原次序能 否进行统计呢 当然可以了 先给你来一个 筛选法 三 筛选法 依次单击 数据 筛选 自动筛选 则进入自动筛选状态 工作表的第一行都有一个 向下的小箭头 单击 政治 单元格中的下拉箭头 选择 自定义 打开 自定义自动 筛选方式 对话框 如图 3 所示 设置为 小于或等于 99 与 大于或等于 90 单击 确定 按钮退出 则只显示出政治成绩在 90 99 之间的同学 此时显示的行号是每位同 学原来实际所在的行号 不能根据 行号统计法 来统计人数的多少 而且也不能采用 选中统计法 来统计人数 那么如何统计人数呢 如果人数较少 逐个数一下就可以了 若人数较多 先选中所有符合条件的人数 单击工具栏中的 复制 按钮 选择另一个工 作表 单击 粘贴 按钮 则将所有人数复制到一个新的工作表中 此时根据行号就可以 快速统计出人数 依此方法可统计出其它分数段的人数 图 3 利用筛选法也可统计出某科考试中缺考人数 如单击语文成绩所在列的箭头 在下拉 菜单中选择 空白 选项 则只保留了语文成绩为空的学生 此方法在某些数据的统计中 是非常有用的 四 查找法 查找也能进行统计 挺新鲜的 得好好学学 前面介绍的数据统计是在一行或一列中 进行操作 如果要在多列即某一单元格区域内进行统计 那可使用查找的方法 如要统计 各科成绩中 59 分的人数 操作步骤是 先打开 查找 对话框 在查找项中输入 59 单击 查找下一个 按钮一次 可找到一个 59 分 继续查找 就可快速确定成绩是 59 分 的人数 哎 暂停 怎样确定人数 怎么没讲呢 真笨 记下查找到的 59 的次数不就 是最后的统计人数吗 最后有重要提示 Execl2000 在查找时是循环进行的 也就是查找 到文末后从头再来 所以吗 上面介绍的统计方法 乃小技也 若要进行复杂 数据较多的统计 那还得请出统计 函数来 大家注意了 请看本文的压轴戏 函数统计法 五 函数统计法 统计函数较多 主要介绍常用的以下几个 1 COUNT 返回参数的个数 利用函数 COUNT 可以计算数组或单元格区域中数字项的个数 语法 COUNT value1 value2 Value1 value2 是包含或引用各种类型数据的参数 1 30 个 但只有数字类型 的数据才被计数 如统计参加政治考试的学生人数 COUNT B2 B47 等于 46 2 COUNTA 返回参数组中非空值的数目 利用函数 COUNTA 可以计算数组或单元格区域中数据项的 个数 语法 COUNTA value1 value2 Value1 value2 所要计数的值 参数个数为 1 30 个 例如 统计参加政治考试的学生人数 COUNT B2 B47 等于 46 COUNTA 与 COUNTA 的区别 函数 COUNT 在计数时 将把数字 空值 逻辑值 日期或以文字代表的数计算进去 但是错误值或其他无法转化成数字的文字则被忽略 而 COUNTA 的参数值可以是任何类型 它们可以包括空字符 但不包括空白单元格 如图 4 所示 COUNT A1 A8 等于 6 COUNTA A1 A8 等于 7 图 4 3 COUNTIF 计算给定区域内满足特定条件的单元格的数目 语法 COUNTIF range criteria Range 为需要计算其中满足条件的单元格数目的单元格区域 为需要计算其中满足条件的单元格数目的单元格区域 Criteria 为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达 式或文本 例如 条件可以表示为式或文本 例如 条件可以表示为 60 60 60 苹果苹果 如统计政治课考试成绩中及格的人数 COUNTIF B2 B47 60 等于 41 4 FREQUENCY 频率统计分布函数 以一列垂直数组返回某个区域中数据的频率分布 例如 使用函 数 FREQUENCY 可以计算在给定的值集和接收区间内 每个区间内的数据数目 由于函数 FREQUENCY 返回一个数组 必须以数组公式的形式输入 语法 FREQUENCY data array bins array Data array 为一数组或对一组数值的引用 用来计算频率 如果为一数组或对一组数值的引用 用来计算频率 如果 data array 中不包含任何数值 函数中不包含任何数值 函数 FREQUENCY 返返 回零数组 回零数组 Bins array 为一数组或对数组区域的引用 设定对为一数组或对数组区域的引用 设定对 data array 进行进行 频率计算的分段点 如果频率计算的分段点 如果 bins array 中不包含任何数值 中不包含任何数值 函数函数 FREQUENCY 返回返回 data array 元素的数目 元素的数目 利用此函数可以非常迅速地解决我们前面介绍的求各分数段人数的例子 如求政治成绩中各分数段的人数 B2 B47 为 data array C4 C7 为 bins array 值为 60 69 79 89 选定 D4 D8 为输出区域 输入公式 FREQUENCY B2 B47 C4 C7 按 Ctrl Shift Enter 键得到结果为 4 0 6 17 19 即各分数段的人数是 4 0 6 17 19 5 MAX 和 MIN MAX 返回数据集中的最大数值 语法 MAX number1 number2 Number1 number2 为需要找出最大数值的 1 到 30 个数值 可以将参数指定为数字 空白单元格 逻辑值或数字的文本表达式 如果参数为错误 值或不能转换成数字的文本 将产生错误 MIN 返回给定参数表中的最小值 语法 MIN number1 number2 Number1 number2 是要从中找出最小值的 1 到 30 个数字参数 参数可以是数字 空白单元格 逻辑值或表示数值的文字串 如果参数中有错误值或 无法转换成数值的文字时 将引起错误 利用这两个函数可统计出各科成绩的最高分和最低分 举例 如果 A1 A5 包含数字 99 87 59 27 和 32 则 MAX A1 A5 等于 99 MIN A1 A5 等于 27 上面介绍了五种数据统计的方法 适合于常规的数据统计 相信总有一种方法能够满 足你的要求的 巧用巧用 ExeclExecl 20002000 分析考卷质量分析考卷质量 按照教学常规 每次考完试教师在阅卷结束后都要抄录分数和填写卷面质量分析的相关数 据 在卷面质量分析表中要求统计考试人数 及格人数 及格率 优秀人数 优秀率 总 分 平均分 最高分 最低分 各分数段人数等许多数据 手工统计非常烦琐 也很容易 出错 能不能把抄录分数和填写卷面质量分析结合起来 在 Execl 2000 中输入学生成绩 的同时 自动统计各项数据 并打印出卷面质量分析表呢 其实只要做一个简单的工作表 就可以实现卷面质量分析自动化 首先 在工作表里建立一个学生成绩单 A 列存放学生学号 B 列存放学生姓名 C 列存放 学生成绩 A1 B1 C1 为标题 接着 在学生成绩单下方建立卷面质量分析表 考虑到该 表对各班级的通用性 要留有一定的扩展余地 如果学校最大班级人数为 70 那么就在第 74 行以下建立 建立卷面质量分析表分以下步骤 1 按照常规的卷面质量分析表的样式输入各项 如图 1 图 1 2 输入各种统计项目的计算公式 考试人数 COUNT C2 C74 及格人数 COUNTIF C2 C74 60 及格率 COUNTIF C2 C74 60 COUNT C2 C74 优秀人数 COUNTIF C2 C74 85 优秀率 COUNTIF C2 C74 85 COUNT C2 C74 60 分以下 COUNTIF C2 C74 60 60 到 70 分之间