用表格分析学生成绩

图1 图 1 所示为某份原始成绩表 包括全年级八个班八个学科的成绩 这份表格放在 原始数据 工作表中 我们的任务是 能够方便地查询各个班各学科的总分 平均分 及格率 优秀率四个项目 为此 我们有如下工作要做 一 基本表格准备 在 Sheet2 工作表标签处右击 将其重命名为 班级项目查询 在该工作表的 A1 单元格录入 查询班级 在 A2 单元格录入 查询项目 点击 B1 单元格 再点击菜单命令 数据 有效性 打开 数据有效性 对话框 在 允许 下拉 列表中点击 序列 选项 在下方的 来源 输入框中输入 1 2 3 4 5 6 7 8 要注意的是这里的数字和逗号均需在英文半 角状态下输入 其它均采用默认设置好了 如图 2 所示 图2 用同样的方法设置 B2 单元格的数据有效性 只是其来源为 总分 平均分 及格率 优秀率 经过这样的设置之后 我 们就可以轻松在 B1 B2 两个单元格提供的下拉列表中选择要查询的班级和查询的项目了 如图 3 所示 图3 回到 原始数据 工作表 在 P3 X13 区域建立如图 4 所示的表格 图4 点击 Q3 单元格 录入公式 班级项目查询 B1 点击 Q4 单元格 录入公式 班级项目查询 B2 至此 基本的表 格准备工作就算是完成了 二 数据准备 首先在 班级项目查询 工作表的 B1 和 B2 单元格中分别选择好查询的班级和查询项目 然后点击 原始数据 工作 表中 Q5 单元格 录入公式 SUMIF B B Q 3 D D 回车后就可以得到所查询班级的语文科总分了 在 Q6 单元 格录入公式 Q5 COUNTIF B B Q 3 可以得到该班级语文科的平均分 在 Q7 单元格录入公式 SUMPRODUCT B B Q 3 D D 60 COUNTIF B B Q 3 得到及格率 在 Q8 单元格录入公式 SUMPRODUCT B B Q 3 D D 85 COUNTIF B B Q 3 得到优秀率 选中 Q5 Q8 单元格区域 拖动 其填充句柄向右至 X 列 这样该班级的所有科目的全部数据就都有了 我们可以选中全部数据 设置其格式为带两位 小数的数字 至于第二个表格就简单了 点击 Q13 单元格 录入公式 VLOOKUP Q 11 P 5 X 8 COLUMN 15 FALSE 然后拖动其填充句柄至 X13 单元格 就可以得到所需要的结果了 同样可以设置其数字格式 如图 5 所示 图5 三 建立图表 数据有了 打造图表就简单了 回到 班级查询项目 工作表 在某空白单元格录入公式 期末考试 B1 班 B2 图表 点击菜单命令 插入 图表 打开 图表类型 对话框 选中 簇状柱形图 类型 点击 下一步 按钮 点击 系列 选项卡 在 名称 右侧的输入框中输入 原始数据 Q 11 在 值 右侧的输入框中输入 原始数据 Q 13 X 13 在 分类 X 轴标志 右侧的输入框中输入 原始数据 Q 12 X 12 如图 6 所示 图6 点击 下一步 按钮 点击 数据标志 选项卡 选中 值 复选项 如图 7 所示 图7 点击 完成 按钮 将制作好的图表拖到工作表中合适的位置 现在所得结果如