4.3 多班级学生单科成绩分数段统计（二）
案例背景
某中学张教师任教初三1班、2班和3班的数学课，期末考试后，需要统计自己任教的3个班级的学生的期中考试、期末考试和总评成绩各个分数段成绩分布情况，以便进行综合分析。
本案例通过尝试使用数组公式技术来统计3个班级的学生的期中考试、期末考试和总评成绩各个分数段成绩分布情况。
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 图表的应用——折线图 图表的编辑
●数组公式的设计与操作
最总效果展示
本节是在4.2节的基础上尝试使用更为简捷的数组公式技术来统计初三1班、2班和3班学生的数学课期中考试、期末考试和学期总评成绩各个分数段成绩的人数分布情况，因此在使用数组公式统计各分数段人数前只需将4.2节的原始成绩和分数段统计表复制粘贴过来，然后清除统计数据即可。
4.3.1使用数组公式统计各分数段人数
Step 1重名工作表
将4.2节案例“多班级单科成绩分数段统计表.xls”工作簿的工作表“sheet2”重命名为“数组公式”。
Step2复制粘贴原始成绩和分数段统计表
①光标单击“频率函数”工作表标签，工作表左上角单击行列交汇处选中整个工作表，按组合键复制，单击“数组公式”工作表标签，选中单元格A1，按组合键粘贴。
②按键后分别选中单元格区域H3:J7，H12:J16，H21:J25，按键清除分数段统计数据。
Step3定义名称
①选中1班期中考试成绩单元格区域C2:C56，光标单击“名称框”输入“_1班期中”。
②选中2班期中考试成绩单元格区域C57:C111，光标单击“名称框”输入“_2班期中”；选中3班期中考试成绩单元格区域C112:C168，光标单击“名称框”输入“_3班期中”。
③按照上面定义名称的方法分别将单元格区域D2:D56、D57:D111和D112:D168定义为“_1班期末”、“_2班期末”和 “_3班期末”。
④分别将单元格区域E2:E56、E57:E111和E112:E168定义为“_1班总评”、“_2班总评”和 “_3班总评”。
Step4统计“期中考试成绩各分数段”的人数
①选中单元格H3，输入公式“=SUM((_1班期中>=0)*(_1班期中<60))”，按组合键确认。
②选中单元格H4，输入公式“=SUM((_1班期中>=60)*(_1班期中<70))“，按组合键确认。
③选中单元格H5，输入公式“=SUM((_1班期中>=70)*(_1班期中<80))“，按组合键确认，选中单元格H6，输入公式“=SUM((_1班期中>=80)*(_1班期中<90))“，按组合键确认。
④选中单元格H7，输入公式“=SUM((_1班期中>=90)*(_1班期中<=100))“，按组合键确认。至此1班各分数段人数统计完毕。
⑤选中单元格区域H3:H7，光标移至单元格H7右下角，向右拖曳填充柄至J列松开鼠标。
⑥选中单元格区域I3:I7，单击菜单“编辑”→”替换“弹出“查找和替换”对话框，在“查找内容”选项框中输入“1班”，在“替换为”选项框中输入“2班”。
⑦单击“全部替换”按钮，弹出提示对话框，告之用户“Excel已完成搜索并进行了10处替换”。单击“确定”按钮，从而得到2班各分数段的人数的统计。然后关闭“查找和替换”对话框。
⑧选中单元格区域I3:I7，单击菜单“编辑”→”替换“弹出“查找和替换”对话框，在“查找内容”选项框中输入“1班”，在“替换为”选项框中输入“3班”，单击“全部替换”按钮，弹出提示对话框，告之用户“Excel已完成搜索并进行了10处替换”。
⑨单击确定按钮，从而得到3班各分数段的人数的统计。然后关闭“查找和替换”对话框。
Step5统计“期末考试成绩各分数段”的人数
①选中单元格区域H3:J7，按组合键复制，选中单元格区域H10:J16，按组合键粘贴。
②单击菜单“编辑”→”替换“弹出“查找和替换”对话框，在“查找内容”选项框中输入“期中”，在“替换为”选项框中输入“期末”。
③单击“全部替换“按钮，弹出提示对话框，告之用户“Excel已完成搜索并进行了30处替换”，
④单击确定按钮。得到“期末考试成绩各分数段”的统计数据，然后关闭“查找和替换”对话框。
Step6统计“学期总评成绩各分数段”的人数
①选中单元格区域H3:J7，按组合键复制，选中单元格区域H21:J25，按组合键粘贴。
②单击菜单“编辑”→”替换“弹出“查找和替换”对话框，在“查找内容”选项框中输入“期中”，在“替换为”选项框中输入“总评”。
③单击“全部替换“按钮，弹出提示对话框，告之用户“Excel已完成搜索并进行了30处替换”。
④单击确定按钮。得到“学期总评成绩各分数段”的统计数据，然后关闭“查找和替换”对话框。
至此完成了3个班期中考试、期末考试和学期总评成绩各分数段人数的统计工作全部完成。
4.3.2创建期中考试分数段统计折线图
图表可以用来表现数据间的某种相对关系，在常规状态下我们一般运用柱形图比较数据间的多少关系; 用饼图表现数据间的比例分配关系；用折线图反映数据间的趋势关系。我们将根据4.3.1统计出的各个班期末考试成绩各分数段人数创建折线图表来直观的了解该分布所反映的问题。
Step 1修改分数段分类标准表示方法
①在单元格G3，G4，G5，G6，G7，G8中，分别输入0-60，60-70，70-80，80-90，90-100。
②选中单元格区域G3：G8，按组合键复制，光标选中单元格区域G12：G16，按组合键粘贴。
Step2 创建折线图表
①单击菜单“插入“→”图表”。弹出 “图表向导-4步骤之1-图表类型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选择”折线图“，在”子图表类型 “选项框中选择默认的第一种”折线图“。
②单击 “下一步“按钮，弹出 “图表向导-4步骤之2-图表数据源”对话框，单击“数据区域”选项框右侧的按钮，用光标选中单元格区域G2:J7，再单击该按钮返回。
③然后单击 “下一步“按钮，弹出 “图表向导-4步骤之3-图表选项” 对话框，在“标题“选项卡的”图表标题“框中输入”期中考试成绩分数段人数分布图“；在”分类（x）轴“框中输入”分数段“；在”数值（y）轴“框中输入”人数“。
④单击 “下一步“按钮，弹出 “图表向导-4步骤之4-图表位置” 对话框，在“将图表“选项框中选择”作为其中的对象插入“。
⑤单击”完成“按钮即可初步得到 “期中考试成绩分数段人数分布图“图表。
4.3.3 编辑“期中考试分数段统计”折线图表
Step 1移动图表
光标单击图标的白色图表区，当鼠标指针变为双箭头十字图标时，拖动图表到合适的位置。
Step 2调整图表大小
单击图表的边框，图表边框出现8个黑色方块，然后将鼠标移到需要调整的方块上，待鼠标指针变成双向箭头时拖动到合适的大小即可。
Step 3为图表区设置图案
①右键单击图表区任意处，从弹出的下拉列表框中选择“图表区格式“。
②弹出“图表区格式“对话框，在”图案“选项卡的”边框“选项框中勾选”阴影“和”圆角“选项，在”区域“选项框中选择”浅蓝色“。
③单击“填充效果“按钮，弹出” 填充效果“对话框，在“颜色”选项框中单击双色按钮，在“颜色2” 选项框中选择“浅绿色”，在”底纹样式“选项框中单击”斜下“按钮。
④单击确定按钮返回到“图表区格式“对话框。然后单击确定按钮，即可完成图表区图案的设置。
Step 4修饰分类轴标题和数值轴标题
①左键单击分类轴标题“分数段”，用鼠标将其拖动到合适位置。
②右键单击数值轴标题“人数”，从弹出的下拉列表中单击“坐标轴标题格式”，弹出坐标轴标题格式”对话框。
③切换到“对齐”选项卡，在“方向”选项框下面的“度”选项框输入“0”，这时“文本指针框”的文本指针由原来的指向90度垂直方向变为指向0度水平方向。
④单击“确定”按钮。
⑤左键单击数值轴标题“人数”，用鼠标将其拖动到合适位置。
Step 5为“图例”设置图案
①右键单击“图例”“人数”，从弹出的下拉列表中单击“图例格式”，弹出“图例格式”对话框，切换到“图案”选项卡。
②在“图例格式”对话框的“区域”选项框中单击按钮“无”。
③单击“确定”按钮，完成对“图例”的修饰。
Step 6为数据折线设置图案
①左键单击图表中代表“1班”的数据折线，选中“1班”蓝色折线，单击右键，在弹出的下拉列表中单击“数据系列格式”。
②在弹出的“数据系列格式”对话框的“线形”选项框中单击“平滑线”选项。
③单击“确定”按钮，完成对1班数据折线的图案设置。
④按照将“1班”数据折线设置成平滑线的方法，陆续将2班和3班数据折线设置成平滑线。
4.3.4创建、美化期末考试分数段统计折线图
Step1 创建折线图表
①选中准备插入图表的单元格区域G11:J16。
②单击菜单“插入“→”图表”。弹出 “图表向导-4步骤之1-图表类型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选择”折线图“，在”子图表类型 “选项框中选择”数据点折线图“。
③单击 “下一步“按钮，弹出 “图表向导-4步骤之2-图表数据源”对话框。
④然后单击 “下一步“按钮，弹出 “图表向导-4步骤之3-图表选项” 对话框，在“标题“选项卡的”图表标题“框中输入”期末考试成绩分数段人数分布图“；在”分类（x）轴“框中输入”分数段“；在”数值（y）轴“框中输入”人数“。
⑤单击 “下一步“按钮，弹出 “图表向导-4步骤之4-图表位置” 对话框，在“将图表“选项框中选择”作为其中的对象插入“。
⑥单击”完成“按钮即可初步得到 “期末考试成绩分数段人数分布图“图表。
Step2 美化折线图表
①参照“期中考试成绩分数段统计折线图”的图表位置、大小将图表调整到合适的大小和位置。
②参照“期中考试成绩分数段统计折线图”的图表区图案的设置方法为图表区添加图案。
③陆续将3个班的数据折线设置为平滑线形式。
④将“图例”的颜色设置为“无”。
⑤右键单击绘图区任意处，从弹出的下拉列表框中选择“绘图区格式“。
⑥从弹出的“绘图区格式“对话框中单击“填充效果”按钮，在弹出的“填充效果”对话框的“颜色”选项框中单击“预设”按钮，在左边随后弹出的“预设颜色”选项框中选择您认为合适的配色效果，在这里不妨选择“雨后初晴”。
⑦单击确定按钮返回“绘图区格式“对话框，再单击确定按钮完成对绘图区的修饰。
至此初三1班，2班和3班数学课期中考试与期末考试数学课成绩分数段统计图表的创建与修设美化任务全部完成。
关键知识点讲解：
1.逻辑值与数值的关系
在Excel中，逻辑值只有TRUE和FALSE两个，它们与数值的关系为：
⑴在数值运算中，TRUE=1，FALSE=0，
⑵在逻辑运算中，0= FALSE，所有非0数值= TRUE。
这两条准则非常重要，在Excel函数公式的简化级计算中用途很广。
2. 逻辑关系运算
以上9种计算简单列举了常用逻辑运算的结果，这也是在数组公式运算中常见的、最常用的理论关系。
3.应用示例
统计A1:D1数据区域中大于“0”的数值个数，其公式可设计为：“=SUM((A1:D1>0)*1)“
了解了以上的运算后，我们知道由于SUM函数的参数是数组，而且这个数组是由逻辑值组成的，那么要对这些逻辑值求和，就必须首先将逻辑值进行运算（*1或+0），也就是首先将有逻辑值组成的数组（TRUE，FALSE，FALSE，TRUE）通过*1，先转化成数值数组（1，0，0，1）SUM函数才能够正确求和。
案例公式解析：
单元格H4统计的是“1班“期中考试成绩在”60—70“之间的学生数，单元格H4中的公式为：
“=SUM((_1班期中>=60)*(_1班期中<70)) “
因为1班有55名学生，因此，公式中的数组(_1班期中>=60)为一个由55个逻辑值TRUE和FALSE组成的一维数组(TRUE，TRUE，TRUE，TRUE，……)，(_1班期中<70)也是一个由55个逻辑值TRUE和FALSE组成的一维数组(FALSE，FALSE，TRUE，TRUE，……)，而(_1班期中>=60)*(_1班期中<70)得运算结果为一个由55个数值组成的一维数组：
（0，0，1，1…….），这是数组公式简化为：
“=SUM(( 0，0，1，1，…….)) “
按组合键，在单元格H4返回统计结果为“13“。
|
