3.8 单科成绩—名次组合图——交互式图表（二）
案例背景
每逢考试结束，考试成绩登统完毕后，一般情况下学校要组织召开学生家长会，班主任要在家长会上向学生家长通报本班考试的整体情况，而学生家长除了关注班级整体情况外，应该更关心自己的孩子的考试成绩以及自己孩子的成绩在班级中的位置，细心地家长可能还会关心自己的孩子每门课程以及总成绩的考试成绩和在班里的位置。
在3.7节中介绍了通过利用数据的“自动筛选”隐藏行数据的特性与图表源数据联动的特性，通过筛选不同的学生实现图表的交互功能，本节将通过使用Excel“窗体”来选择代表不同的课程考试成绩的列，切换图表中的“源数据”从而实现图表的交互功能。
本案例以2004年天津市某重点中学理科实验班部分学生高三阶段全市模拟成绩为例，在3.7节的基础上进一步介绍“单科成绩——名次组合图”交互式图表的制作与操作方法。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 选择性粘贴的“转置”功能 自动筛选
●图表应用 折线图，图表的编辑，设置“数据点”的格式，自动更新图表标题 柱形图
●函数应用 CHOOSE函数的应用
AVERAGE函数 ，MAX函数，MIN函数，ROUND函数
●综合应用 EXCEL“窗体”的使用，交互式图表
最终效果展示
3.8.1创建“班级考试成绩表”
Step 1创建工作簿、重名工作表
创建工作簿“组合图.xls”，然后将工作表重命名为“组合图表”，并删除多余工作表。
Step 2输入班级考试成绩
①在单元格区域A1：G1，分别输入成绩表的字段名：“学号“，“姓名“，“语文“，“数学“，“英语“，“综合“，“总分“。
②在单元格区域输入A2:F24输入学生原始信息和成绩。
③在单元格G2中输入公式：“=SUM(C2:F2) ”，按键确认得到第一个学生的“总分”。
④选中单元格G2，左键双击单元格G2右下角的填充柄即可完成其余学生总分的计算。
Step 3统计班级“平均分”、“最高分”和“最低分”
①在单元格B26输入“平均分”，B27输入“最高分”，B28输入“最低分”，
②在单元格C26输入公式：“=ROUND(AVERAGE(C2:C24),1) ”，然后按键确认。
③在单元格C27输入公式：“=MAX(C2:C24) ”，然后按键确认。
④在单元格C28输入公式：“=MIN(C2:C24) ”，然后按键确认。
⑤选中单元格区域C26：C28，向右拖曳单元格C28右下角的填充柄至单元格G28松开鼠标，即可完成其余课程和“总分”的“平均分”、“最高分”和“最低分”的统计。
3.8.2创建交互图表数据区
Step 1创建Excel“窗体”数据源
①选中单元格区域C1:G1，按组合键复制。
②单击单元格I2，单击菜单“编辑”→“选择性粘贴”，弹出“选择性粘贴”对话框，在“选择性粘贴”对话框中勾选“转置”复选框。
③然后单击“确定”按钮。
Step 2插入Excel窗体“组合框”
①单击菜单“视图”→“工具栏”→“窗体”，在桌面上调出“窗体”组合工具栏命令按钮。
②单击选中“窗体”组合工具栏中的“组合框”按钮，选中单元格J1，按照单元格J1大小初步设置“组合框”按钮的大小。
③右键单击单元格J1 中的“组合框”按钮，在弹出的下拉列表中选择“设置控件格式”命令，弹出“设置控件格式”对话框。
④切换到“控制”选项卡，在“数据源区域”复选框中输入：“$I$2:$I$6”，在“单元格链接”复选框中输入：“$I$1”，在“下拉显示项数”复选框中输入“5”。
⑤然后单击“确定”按钮。
Step 4为交互图表设置动态“源数据”
①在单元格J2输入如下公式，然后按键确认。“=INDEX($C$1:$G$24,ROW(),$I$1) ”
②选中单元格J2，向下拖曳单元格J2右下角的填充柄至单元格G24即可完成公式填充。
注：由于确定单元格区域J2：J24中的公式“=INDEX($C$1:$G$24,ROW(),$I$1)”中列的值由单元格I1中的数值就定，而单元格I1中的数值又是根据单元格JI中的窗体的选择而变化，因此单元格区域J2：J24中公式返回的引用区域是可变的。
③在单元格K1输入“名次”，在单元格K2输入公式：“=RANK(J2,$J$2:$J$24) ”，然后按键确认，即可计算出单元格J2中的数值在单元格区域J2：J24中的位置。
④选中单元格J2，双击单元格J2右下角的填充柄即可完成公式填充。从而计算出单元格J3到单元格J24中的数值各自在动态区域J2:J24中的位置。
⑤选中单元格区域C26: C28, 按组合键复制,单击单元格J26，按组合键粘贴即可计算出动态区域J2:J24中数值系列的“平均分”、“最高分”和“最低分”。
3.8.3创建交互式图表
Step 1选取图表源数据
按下键，陆续选中单元格区域B2：B24，B26:B28，J2:24，J26：J28。
Step 2插入图表
①单击菜单“插入”→“图表”。
②弹出“图表向导-4步骤之1-图表类型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选择”柱形图“，在”子图表类型 “选项框中选择”蔟状柱形图“。
③单击 “下一步“按钮，弹出 “图表向导-4步骤之2-图表数据源”对话框，此时“数据区域“默认的是已经选好的单元格区域，“系列产生在”默认的是“列”。
④单击 “下一步“按钮，弹出 “图表向导-4步骤之3-图表选项” 对话框，在“标题“选项卡的”图表标题“框中输入”考试成绩—名次组合图“；在”分类（x）轴“框中输入”学生“；在”数值（y）轴“框中输入”分数“。
⑤单击 “下一步“按钮，弹出 “图表向导-4步骤之4-图表位置” 对话框，在“将图表“选项框中选择”作为其中的对象插入“。
⑥单击”完成“按钮即可初步得到图表”考试成绩—名次组合图“。
Step3移动图表
单击图表的边框，待图表边框出现8个黑色方块时拖动图表到第28行“最低分”成绩区域以下的合适位置。
Step 4调整图表大小
单击图表的边框，图表边框出现8个黑色方块，然后将鼠标移到需要调整的方块上，待鼠标指针变成双向箭头时拖动到合适的大小即可。
Step 5修改系列名称
图16的图例显示“学生成绩”为系列1，我们可以将图例中的“系列1”修改为更为贴切的系列名称。
单击图表区选中图表，单击菜单“图表”→“源数据”，弹出“源数据”对话框，在“系列1”右侧的名称框中直接输入准备替换的系列名称“学生成绩”。
Step 6添加数据系列
①单击“添加”按钮，系列复选框中出现“系列2”，在其右侧名称框中输入“系列2“的名称“名次”。
②单击“值”右侧的“拾取”按钮，在弹出的“源数据-值”对话框中用鼠标选取名次区域“Sheet1!$K$2:$K$24”。
③在“分类（X）轴标志（T）“复选框中用鼠标选取名次标志区域“（Sheet1!$B$2:$B$24，Sheet1!$B$26:$B$28）”。
④然后单击“确定”按钮。
此时图表中增加了一个新的系列“名次“。
Step7修改图表类型
①单击选中图表中的“名次”数据系列，再单击菜单“图表”→“图表类型”命令。
②在弹出的“图表类型”对话框的“标准类型”选项卡中选择“图表类型”为“折线图“，并单击”按下不放可查看示例“按钮确认图表。
③然后单击“确定”按钮。
Step8添加次坐标轴
①单击选中图表中的“名次”数据系列，再单击菜单“格式”→“数据系列”命令。
②弹出“数据系列”对话框，切换到“坐标轴“选项卡，将”系列绘制在“改为“次坐标轴”
③然后单击“确定”按钮。
如图25所示学生成绩名次系列折线图已经初步完成，“名次”系列已经与“学生成绩”系列图形区分开，但是从视觉效果上还不够完善，主要有以下几方面的问题。
代表每个学生名次的图形不明显，这个问题可以通过设置“数据标志”解决。
人们习惯考试成绩越高，“名次”越高，可是由于分数越高，体现“名次”的数值越小，在图表中显示的图形自然是越低，这个问题可以通过设置坐标轴的格式来解决。
Step9设置“次坐标轴“格式
①鼠标指向“次坐标轴”，当出现“次坐标轴”字样文本框时，单击右键从弹出的列表中选择“坐标轴格式“。
②弹出“坐标轴格式“对话框，切换到”字体“选项卡，在”字号“复选框中选择”9“。
③切换到”刻度“选项卡，在”主要刻度单位“选项框中将原先的”5“修改为”3“，单击”数值次序反转“命令按钮。
④然后单击“确定”按钮。
Step10设置“数据系列”的数据标志
①单击选中图表中的“名次”数据系列，再单击菜单“格式”→“数据系列”命令。
②弹出“数据系列”对话框，切换到“数据标志“选项卡，在”数据标志包括“选项框中勾选”值“命令按钮，“并勾选”图例项标示“选项框。
③然后单击“确定”按钮。
Step11设置“数据标志“的自定义格式
①单击选中图表中的“名次”数据系列，单击右键，从弹出的列表中选择“数据标志格式“。”
②弹出“数据标志格式“对话框，切换到”数字“选项卡，在”分类“框中选择”自定义“，在”类型“框中输入自定义格式代码：” "第"#"名"”。
③然后单击“确定”按钮。
Step12设置“主坐标轴“格式
①鼠标指向“主坐标轴”，当出现“主坐标轴”字样文本框时，单击右键从弹出的列表中选择“坐标轴格式“。
②弹出“坐标轴格式“对话框，切换到”字体“选项卡，在”字号“复选框中选择”9“。
③切换到”刻度“选项卡，在“最小值”选项框将原先的” 0“修改为”60“，在”主要刻度单位“选项框中将原先的”20“修改为”10“。
④然后单击“确定”按钮，即可完成对主坐标轴格式的设置。
Step13查看，并编辑图表
①光标单击单元格区域A1:G24内任一单元格，然后单击菜单“数据”→“筛选”→“自动筛选”命令，完成对成绩表中学生成绩数据区域的筛选设置。
②单击单元格B1的下拉菜单，选择学生“陈静”，此时图表只显示学生“陈静”的分数和“名次“以及“平均分”、“最高分”和“最低分”的分数，以便进行对比分析。
③单击选中“学生成绩“数据系列，单击右键，从弹出的列表中选择”数据系列格式“。
④弹出”数据系列格式“对话框，切换到”数据标志“选项卡，在”数据标志包括“选项框中勾选”值“命令按钮，然后单击“确定”按钮。
Step14设置“数据点”格式
①单击选中图表中“平均分”图形，单击右键，从弹出的列表中选择“数据点格式”。
③弹出“数据点格式”对话框，在“内部”选项框中选择“象牙色”，然后单击“填充效果”按钮。
④弹出“填充效果”对话框，在“底纹样式”选项框中选择“斜上”。
⑤然后单击“确定”按钮返回“数据点格式”对话框.
⑥再单击“确定”按钮即可完成数据点“平均分”的格式设置。
⑦重复上述操作步骤将数据点“最高分”的图形设置为“红色”，“底纹样式”为“中心辐射”。
⑧重复上述操作步骤将数据点“最低分”的图形设置为“绿色”，“底纹样式”为“角部辐射”。
⑨单击单元格B1的下拉菜单，选择学生“陈曦”，此时图表只显示学生“陈曦”的分数和“名次“以及“平均分”、“最高分”和“最低分”的分数，并且可以看到上面对数据点“平均分”、“最高分”和“最低分”的格式设置已经被保存住。
Step15实施图表“源数据”列的切换
①单击单元格J1中的“窗体”中的下拉按钮，从弹出的下拉选框中选择第2个选项“数学“。
②再单击单元格B1的下拉菜单，选择学生“林楠”，图表显示学生“林楠”“数学“的分数和 “名次“以及班级“平均分”、“最高分”和“最低分”的分数，
③单击单元格J1中的“窗体”中的下拉按钮，从弹出的下拉选框中选择第3个选项“英语“。
Step16编辑“图表区“
①单击图表区，单击右键，从弹出的列表中选择“图表区格式”。
③弹出“图表区格式”对话框，在“边框”选项框中勾选“阴影”和“圆角”按钮，在“区域”选项框中选择“淡青绿”色，然后单击“填充效果”按钮。
④弹出“填充效果”对话框，在“颜色”选项框中选择“双色”，在“颜色2”中选择“淡黄色”在“底纹样式”选项框中选择“斜下”。
⑤然后单击“确定”按钮返回“图表区格式”对话框，再单击“确定”按钮即可完成图表区格式设置。
Step17编辑“数值轴标题“
①单击选中数值轴标题，单击右键，从弹出的列表中选择“坐标轴标题格式”。
②弹出“坐标轴标题格式”对话框，切换到“对齐”选项卡，在“方向“选项框中选择”0“度。
③单击“确定”按钮，然后用鼠标直接将“标题“拖放到数值轴上部合适位置。
Step18编辑“图例“
①单击选中“图例“，单击右键，从弹出的列表中选择“图例格式”。
②弹出“图例格式”对话框，在“区域”选项框中选择“无”，然后单击“确定”按钮完成对“图例“的格式设置工作。
Step19设置标题自动更新
①在单元格J30输入如下公式，然后按键确认。
“=CHOOSE(I1,I2,I3,I4,I5,I6)&"课程模拟考试成绩—名次组合图"”
②单击选中图表标题，在编辑栏输入公式：“=Sheet1!$I$30”，然后按键确认。
这时图表标题引用的是单元格J30的内容，而J30的内容是随着单元格J1的内容变化而改变的，从而也就实现了图表标题的动态变化。
至此“单科成绩—名次组合图“交互式图表全部制作完毕。
关键知识点讲解：
函数名称：CHOOSE
主要功能：可以使用 index_num 返回数值参数列表中的数值。使用函数 CHOOSE 可以基于索引号返回多达 29 个基于 index number 待选数值中的任一数值。例如，如果数值 1 到 7 表示一个星期的 7 天，当用 1 到 7 之间的数字作 index_num 时，函数 CHOOSE 返回其中的某一天。
使用格式：CHOOSE(index_num,value1,value2,...)
参数说明：Index num 用以指明待选参数序号的参数值。Index_num 必须为 1 到 29 之间的数字、或者是包含数字 1 到 29 的公式或单元格引用。
●如果 index num 为 1，函数 CHOOSE 返回 value1；如果为 2，函数 CHOOSE 返回 value2，以此类推。
●如果 index num 小于 1 或大于列表中最后一个值的序号，函数 CHOOSE 返回错误值 #VALUE!。
●如果 index num 为小数，则在使用前将被截尾取整。
Value1,value2,... 为 1 到 29 个数值参数，函数 CHOOSE 基于 index_num，从中选择一个数值或执行相应的操作。参数可以为数字、单元格引用、已定义的名称、公式、函数或文本。
函数说明
●如果 index num 为一个数组，则在函数 CHOOSE 计算时，每一个值都将计算。
●函数 CHOOSE 的数值参数不仅可以为单个数值，也可以为区域引用。
例如，下面的公式： =SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))
相当于： =SUM(B1:B10)
然后基于区域 B1:B10 中的数值返回值。
函数 CHOOSE 先被计算，返回引用 B1:B10。然后函数 SUM 用 B1:B10 进行求和计算。即函数 CHOOSE 的结果是函数 SUM 的参数。
应用示例1：
应用示例2：
案例公式解析
3.8.2的Step 4之①单元格J2中的公式为：“=INDEX($C$1:$G$24,ROW(),$I$1) ”
因为在单元格J2中，ROW()返回“2”，而当时单元格$I$1中的值为“1”，因此公式可化简为：“=INDEX($C$1:$G$24,2,1) ”，公式返回结果是单元格区域$C$1:$G$24第2行第1列的值。
3.8.2之Step19中单元格J30中公式为：
“=CHOOSE(I1,I2,I3,I4,I5,I6)&"课程模拟考试成绩—名次组合图"”
因为当时单元格I1中的值为“3”，单元格I3中的数据为“英语”因此CHOOSE函数化简为：CHOOSE(I1,I2,I3,I4,I5,I6)= CHOOSE(3,语文,数学,英语,综合,总分)，函数返回结果“英语”，单元格J30中公式返回没结果“英语课程模拟考试成绩—名次组合图”。
|
