4.1 班级学生成绩分数段统计
案例背景
当某中学初三2班班主任从任课教师回收单科成绩表，汇总形成本班“成绩汇总表”后，需要对本班各科考试成绩进行进一步的统计分析，还需要统计本班学生各科期末考试的各个分数段成绩分布情况，以便进行综合分析。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 文本连接符&，隐藏行，图表的应用，饼图，柱形图
●函数应用 COUNTIF函数
●公式的设计与操作
最终效果展示
4.1.1创建学生成绩分数段统计表
Step 1创建工作簿、重名工作表
创建工作簿“学生成绩分数段统计表.xls”，然后将工作表重命名为“分数段统计”，并删除多余工作表。
Step 2 输入原始成绩
将3.5节案例中图2复制粘贴到“分数段统计表”中，修改表头为“初三2班期末考试成绩分布情况统计表”删除全部“期中”和“总评”成绩列，增加“总分”列，并计算出每个学生总分。如下图
Step 3设置期末考试成绩各科分数分段标准
①在单元格A44，A45，A46，A47，A48中，分别输入“0-”，“60-”，“70-”，”80-”,”90-”。
②在单元格B44，B45，B46，B47，B48中, 分别输入“59”，“69”，“79”，”89”,”100”。
③选中单元格区域A44:H48，为该区域添加边框。
④选中单元格A44，单击常用工具栏“右对齐”按钮，选中单元格B44，单击常用工具栏“左对齐”按钮，选中单元格区域A44：B44，去掉“内框”。
⑤单击常用工具栏“格式刷”按钮，光标选择A45:B48，完成格式的复制。
Step 4统计“语文期末”60分以下成绩的人数
①选中单元格C44，单击常用工具栏“插入函数”按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTIF“。
②单击“确定”按钮弹出“函数参数“对话框，在第一个参数框”Range“中用光标选中”语文期末“成绩区域”C3:C43“，在第二个参数框”“Criteria”中输入条件“<60”。
③然后单击确定按钮，即可得到”语文期末“60分以下成绩的人数。
Step 5统计“语文期末”成绩中60-69分数段的人数
①选中单元格C45，单击常用工具栏“插入函数”按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTIF“。
②单击“确定”按钮弹出“函数参数“对话框，在第一个参数框”Range“中用光标选中”语文期末“成绩区域”C3:C43“，在第二个参数框”“Criteria”中输入条件“<70”。
③然后单击“确定”按钮，首先得到”语文期末“70分以下成绩的人数。
④将光标直接单击编辑栏，将光标移动到公式最后，添加公式内容“ -COUNTIF(C3:C43,"<60") “, 按键确认即可计算出”语文期末“成绩中60-69分数段的人数。
Step 6统计“语文期末”成绩中70-79，80-89分数段的人数
①选中单元格C46，单击常用工具栏“插入函数”按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTIF“。
②单击“确定”按钮弹出“函数参数“对话框，在第一个参数框”Range“中用光标选中”语文期末“成绩区域”C3:C43“，在第二个参数框”“Criteria”中输入条件“<80”。
③然后单击“确定”按钮，首先得到”语文期末“80分以下成绩的人数。
④将光标直接单击编辑栏，将光标移动到公式最后，添加公式内容“-C44 - C45”, 按键确认即可计算出”语文期末“成绩中70-79分数段的人数。
⑤选中单元格C47，直接在编辑栏里输入公式“=COUNTIF(C3:C43,"<90")- C44- C45-C46”，然后单击确定按钮即可计算出80-89分数段的人数。
Step 7统计“语文期末”成绩中90-100分数段的人数
选中单元格C48，直接在编辑栏里输入公式“=COUNTIF(C3:C43,">=90"),然后单击确定按钮即可计算出90-100分数段的人数。
Step 8统计“数学”，“英语”，“物理”，“化学”各分数段人数
选中单元格区域C44:C48，向右拖曳单元格C48右下角的填充柄至H列，松开鼠标即可完成其余4科成绩各分数段人数的统计。
Step9设置期末考试成绩“总分”分数分段标准
①由于总分中最低分为272，因此以270分为最低分，按每20分为一分数段，在A49：A58单元格区域中，分别输入“270-”，“290-”，……，“430-”，“450-”
②在B49：B58单元格区域中，分别输入“289”，“309”，……，“449”，“500”。
③选中单元格区域A48：H48，单击常用工具栏“格式刷”按钮，光标选择单元格区域A49:H58，完成格式的复制。
Step10统计“总分”各分数段人数
①选中单元格H49，单击常用工具栏“插入函数”按钮，弹出“插入函数“对话框，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择”COUNTIF“。
②单击确定按钮弹出“函数参数“对话框，在第一个参数框”Range“中用光标选中”总分“成绩区域”H3:H43“，在第二个参数框”“Criteria”中输入条件“<290”。
③然后单击确定按钮，即可得到”总分“成绩在分数段270-289内的人数。
④选中单元格H50，直接在编辑栏里输入公式：“=COUNTIF($H$3:$H$43,"<310")-COUNTIF($H$3:$H$43,"<290")”，然后单击确定按钮即可计算出290-309分数段的人数。
⑤参照计算计算290-309分数段的人数的公式，分别计算出“310-329”，“330-349”，……，“430-449”各分数段的人数。
⑥选中单元格H58，直接在编辑栏里输入公式：
“=COUNTIF($H$3:$H$43,">=450")”，然后单击确定按钮即可计算出450-500分数段的人数。
Step11设置各分数段人数的“合计”
①在单元格A59输入合计。 ②选中单元格C49，直接在编辑栏内输入公式：“=SUM(C44:C58)”, 按键确认，即可计算出“期末语文”各分数段人数合计。
③光标移回到单元格C49，向右拖曳右下角的填充柄至H列，松开鼠标即可完成公式复制填充。
④选中单元格区域A48：H48，单击常用工具栏“格式刷”按钮，光标选择A59:H59，完成格式的复制。
⑤单击行标“44”，选中第44“行，单击菜单”插入“→”行“，插入一行”空白行“，在单元格A44输入”分数段“，选中单元格区域A44:B44，单击常用工具栏”合并及居中“。
Step12隐藏学生成绩
选中第“3：43“行，单击菜单”格式“→”行“→”隐藏“。
至此各门课程和总分的分数段的统计工作全部完成。
4.1.2创建“单科成绩分数段统计“图表
Step 1创建图表数据区框架
①单击菜单“插入“→”工作表“，插入一张新的工作表，并将其重名为”图表“。
②在单元格A1中输入“分数段“，单击工作表标签”分数段统计“，选中单元格区域B2:H2，按组合键复制。 ③单击工作表标签”图表“，选中单元格B1，按组合键粘贴。
④选中单元格A2，输入公式“=分数段统计!A45&分数段统计!B45 “，按键确认。
⑤选中单元格A2，向下拖曳单元格A2右下角的填充柄至第17行，松开鼠标即可完成公式复制填充。
Step 2向图表数据区输入统计数据
①单击工作表标签”分数段统计“，选中统计数据区域C45:H60，按组合键复制。 ②单击工作表标签”图表“，选中单元格B2，单击菜单”编辑“→”选择性粘贴“，弹出”选择性粘贴“对话框。
③在“粘贴”选项框中勾选“数值”，单击“确定”按钮。
④选中单元格区域A1:H17，单击常用工具栏“边框”按钮，为数据区设置边框。
Step3创建图表
①选中单元格区域A1：F6，单击菜单“插入“→”图表”。
②弹出“图表向导-4步骤之1-图表类型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选择”柱形图“，在”子图表类型 “选项框中选择”蔟状柱形图“。
③单击 “下一步“按钮，弹出 “图表向导-4步骤之2-图表数据源”对话框，此时“数据区域“默认的是已经选好的单元格区域”A1：F6“。
④单击 “下一步“按钮，弹出 “图表向导-4步骤之3-图表选项” 对话框，在“标题“选项卡的”图表标题“框中输入”初三2班期末考试成绩分数段统计图“；在”分类（x）轴“框中输入”各学科成绩“；在”数值（y）轴“框中输入”人数“。
⑤单击 “下一步“按钮，弹出 “图表向导-4步骤之4-图表位置” 对话框，在“将图表“选项框中选择”作为其中的对象插入“。
⑥单击按钮”完成“即可初步得到图表”初三2班期末考试成绩分数段统计图“。
4.1.3编辑“单科成绩分数段统计“图表
Step 1移动图表
单击图表的边框，待图表边框出现8个黑色方块时拖动图表到任意合适的位置。
Step 2调整图表大小
单击图表的边框，图表边框出现8个黑色方块，然后将鼠标移到需要调整的方块上，待鼠标指针变成双向箭头时拖动到合适的大小即可。
Step 3设置图表区图案和文字
①右键单击图表区从弹出的下拉列表框中选择“图表区格式“
②弹出“图表区格式“对话框，在”图案“选项卡的”边框“选项框中勾选”阴影“和”圆角“选项，在”区域“选项框中选择”浅黄色“。
③单击“填充效果“按钮，弹出” 填充效果“对话框，在”底纹样式“选项框中单击”斜上“按钮。
④然后单击确定按钮返回到“图表区格式“对话框。
⑤切换到“字体“选项卡，在”字体“选项框中选择”华文行楷“；在”字形“选项框中选择”加粗“，在“字号”选项框中选择“10”。
⑥单击确定按钮，即可完成图表区的图案和文字的设置。
4.1.4创建“总分分数段统计“饼图图表
Step 1选择“总分分数段统计“图表数据区域
按键分别选中单元格区域A1，G1，A7：A16和 G7:G16，作为 “总分分数段统计“图表数据区域。
Step 2创建饼图图表
①单击菜单“插入“→”图表”，弹出 “图表向导-4步骤之1-图表类型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选择”饼图“，在”子图表类型 “选项框中选择”三维饼图“。
②单击 “下一步“按钮，弹出 “图表向导-4步骤之2-图表数据源”对话框，此时“数据区域“默认的是已经选好的单元格区域”。
③单击 “下一步“按钮，弹出 “图表向导-4步骤之3-图表选项” 对话框，在“标题“选项卡的”图表标题“框中输入”总分分数段统计图“。切换到“数据标志“选项卡，在”数据标志包括“选项框中勾选”百分比“选项。
④单击按钮“下一步“，弹出 “图表向导-4步骤之4-图表位置” 对话框，在“将图表“选项框中选择”作为其中的对象插入“。
⑤单击按钮”完成“即可初步得到图表”总分分数段统计图“。
4.1.5编辑“总分分数段统计“图表
Step 1移动图表并调整图表大小
单击图表的边框，待图表边框出现8个黑色方块时拖动图表到任意合适的位置。然后将鼠标移到需要调整的方块上，待鼠标指针变成双向箭头时拖动，将图表调整到合适的大小。
Step 2设置图表区图案和文字
①右键单击图表区从弹出的下拉列表框中选择“图表区格式“。
②弹出“图表区格式“对话框，在”图案“选项卡的”边框“选项框中勾选”阴影“和”圆角“选项，在”区域“选项框中选择”浅绿色“。
③单击“填充效果“按钮，弹出” 填充效果“对话框，在”颜色“选项框中单击”双色“按钮，在”颜色2“选项框中选择”浅黄“；在”底纹样式“选项框中单击”斜下“按钮。
④然后单击确定按钮返回到“图表区格式“对话框。
⑤切换到“字体“选项卡，在”字体“选项框中选择”华文中宋“；在”字形“选项框中选择”加粗“，在“字号”选项框中选择“16”。
⑥单击确定按钮，即可完成图表区的图案和文字的设置。
至此完成“总分分数段统计“图表的修饰美化工作。
关键知识点讲解：
图表的组成
①图表区:图表区包含了整个图表的所有数据信息以及图表辅助说明信息，例如图表、图表标题、数据系列图例、坐标轴以及数据表等。
②绘图区：绘图区是图表的主要组成部分，他以用户指定的图表类型显示工作表的数据信息。
③坐标轴：分为主坐标轴和次坐标轴，大多数图表只有两个主坐标轴—分类轴(x)轴和数据轴（y）轴。根据实际需要，用户可以添加次坐标轴（同样也包含两个次坐标轴—分类轴(x)轴和数据轴（y）轴）。每一个坐标轴都有对应的标题和刻度线标签，用户可以手动编辑这些信息，也可以根据实际需要选择隐藏或者显示坐标轴的这些信息。
④数据系列：工作表的数据信息是可以按照用户指定的图表类型以数据系列的方式显示在绘图区的。类轴(x)轴上的每一个分类都对应着一个或者多个数据，不同分类上颜色相同的数据便构成了一个数据系列。不同的数据系列以不同的颜色显示出来，图表右侧给出了不同颜色所代表的数据系列。用户可以对数据系列进行重命名或者颜色编辑。
⑤数据表:用户可以将数据表作为图表的参考信息添加到图表中，这样更有助于对数据进行分析。下图是一个几乎容纳了所有组成元素的比较完整的图表（除了数据表）。在实际的图表制作过程中，并不需要为图表添加所有的组成元素。当不需要这些元素（例如图标和数据表）时，用户可以删除或者隐藏这些信息，从而使图表更加简洁。图表中的所有文本都可以进行字体以及颜色的编辑。用户可以最大程度地将图表修饰得更加美观。
图表具有较好的视觉效果，可方便用户查看数据的差异、图案和预测趋势。
您可以在工作表上创建图表，或将图表作为工作表的嵌入对象使用。您也可以在网页上发布图表。若要创建图表，就必须先在工作表中为图表输入数据，然后再选择数据并使用“图表向导”来逐步完成选择图表类型和其他各种图表选项的过程，或使用“图表”工具栏来创建以后可设置格式的基本图表。
图表是与生成它的工作表数据相链接的。因此，工作表数据发生变化时，图表也将自动更新。
|
