C任务实施
打开任务六中的素材文件“期中考试成绩统计表.xlsx”,完成下列要求：、
1、对工作表“sheet1”中的数据表进行格式化。
（1）将A1：J1合并及居中，用样式中的“标题”修饰表的标题。
（2）将表中数据（A2：J31）的边框设为外边框为蓝色最粗实线，内边框为最细绿色实线。
（3）表中所有数据对齐方式设为垂直水平居中。
（4）将表中单元格B32:C32、B33:C33、B34:C34、B35:C35、B36:C36分别合并居中。
（5）利用条件格式将表格中所有成绩（D3:G31）中不及格成绩以红色文本显示。
2、对工作表“sheet1”中的数据进行相关计算。
（1）在单元格（H3:H31）中计算出各位同学的平均分,在单元格（D32:G32）中计算出四门学科的班级平均分。
（2）在单元格（D33:G33）中算出每门学科的考试人数。
（3）在单元格（D34:G34）中算出每门学科的考试及格人数（60及60分以上）。
（4）在单元格（D35:G35）中算出每门学科的考试不及格人数（60分以下）。
（5）在单元格（D36:G36）中算出每门学科的及格率（及格率=及格人数/考试人数）。
（6）根据各人的平均分，用排名函数RANK，在单元格（I3:I31）中从高到低算出每位同学的名次。
（7）用IF函数，根据各人的平均分，在单元格（J3:J31）中给出等级，如果平均分不低于60，则为及格，否则为不及格。
（8）将平均分列（H3:H31）、各科目平均分(D32:G32）、及及格率(D36:G36）中的数据设为一位小数，并将及格率(D36:G36）以百分比表示。将表格（A2：J31）行和列设为合适行高和列宽。
3、利用工作表“sheet1”中的各学科名称（D2:G2）和各学科的平均成绩（D32:G32）制作一张带数据标志的折线图。图表标题为“各科平均分”，图例不显示，在数据点上方显示数据标签，图表区填充水滴纹理，将图表置于A38：K55单元格区域内。
4、对工作表“sheet2”中的数据表进行数据处理。
（1）将工作表“sheet1”中的表格数据A1:J31复制到“sheet2”中，将表格中平均分不及格的同学用自动筛选功能筛选出来，将工作表名改为“平均分不及格学生”。
（2）将工作表“sheet1”中的表格数据A1:J31复制到“sheet3”中，将表格中有科目不及格的同学用高级筛选功能筛选出来，将工作表名改为“不及格学生”。
（3）将工作表“sheet1”中的表格数据A1:J31复制到“sheet4”中，用分类汇总功能按性别分别汇总出各学科的平均分。并将工作表名改为“男女生平均分”。
【操作方法】
打开任务六中的素材文件“期中考试成绩统计表.xlsx”
1、对工作表“sheet1”中的数据表进行格式化。
（1）选定A1：J1单元格区域，执行【开始】选项卡中的【对齐方式】命令组的“合并及居中” 
按钮，选中标题，选择【开始】选项卡的【样式】命令组的“单元格样式”中的“标题”样式即可。
（2）选择A2：J31的
单元格区域，单击【开始】选项卡中命令组右下角的按钮，打开【设置单元格格式】对话框，在弹出的对话框中选择“边框”选项，选择最粗实线，颜色选择蓝色，单击“外边框”，接着选择最细实线，颜色为绿色，单击“内部”，确定即可。
（3）选择A2：J36的单元格区域，选择【开始】选项卡的【对齐方式】命令组的“垂直居中”按钮
和“文字居中”按钮
即可。
（4）选中单元格B32:C32，执行【开始】选项卡中的【对齐方式】命令组的“合并及居中”按钮，然后分别选中单元格B33:C33、B34:C34、B35:C35、B36:C36执行同样操作。
（5）选择单元格D3:G31，执行【开始】选项卡→【样式】→【条件格式】→【突出显示单元格规则】，选择“小于”，数值框输入60，在“设置为”选择框中选择红色文本。
格式化后如图4-6-1。
图4-6- 1格式化后的表格
2、对工作表“sheet1”中的数据进行相关计算。
（1）选中单元格H3，单击【公式】选项卡中的按钮
下的平均值命令，公式为“= AVERAGE(D3:G3)”，确定，其余用填充柄向下填充至H31结束。选中单元格D32，单击下的平均值命令，将公式改为“=AVERAGE(D3:D31)”，确定，其余用填充柄向右填充到G32结束。
（2）选中单元格D33，单击中的计数命令，将公式改为“=COUNT(D3:D31)”，确定，其余用填充柄向右填充至G33结束。
（3）选中D34，插入函数COUNTIF ，将COUNTIF函数的二个参数分别设为 (D3:D31,">=60")如图4-6-2，确定后，编辑栏显示的公式为：=COUNTIF((D3:D31,">=60"))，其余单元格用自动填充柄的自动填充功能。
图4-6- 2参数设定
（4）选中D35，插入函数COUNTIF ，在编辑栏中显示“=COUNTIF(D3:D31,"<60")”，其余单元格用自动填充柄的自动填充功能。
（5）选中单元格D36，并在其中输入计算公式“=D34/D33”，确定即可。其余单元格用自动填充柄的自动填充功能。
（6）选中I3，单击插入函数
，插入排序函数RANK ，参数设置如图4-6-3，在编辑栏中显示“=RANK(H3,$H$3:$H$31,0)”，用自动填充柄的自动填充功能向下填充到I31结束。
图4-6- 3排名函数的参数设定
（7）选中J3单元格，单击插入函数，插入IF函数，在三个参数中分别输入如图4-6-4所示的值，在编辑栏中显示“=IF(H3>=60,"及格","不及格")”（也可输入公式：=IF(H3<60,"不及格","及格"))，确定，用自动填充柄的自动填充功能向下填充到J31结束。
图4-6- 4等级IF函数参数的设定
（8）选中单元格H3:H31、D32:G32、D36:G36（按Ctrl选择可同时选中不连续区域），
执行【开始】选项卡中的【数字】命令组的“减小小数位数”和“增加小数位数”按钮，将单元格内数值小数位数调整为1位；选中单元格D36:G36，执行【开始】选项卡中的【数字】命令组的按钮即可。选择单元格A2：J31，执行【开始】选项卡中的【单元格】命令组的【格式】下的“自动调整行高”和“自动调整列宽”命令即可。
计算完成后，结果如下图4-6-5：
图4-6- 5计算完成后的结果
3、制作图表。
（1）打开工作表“sheet1”，选择数据各学科（D2:G2）和各学科的平均成绩（D32:G32）。
（2）插入图表。单击【插入】选项卡的“图表”按钮，选择折线图中的带数据标志的折线图。
（3）单击【图表工具】功能区中的【布局】选项卡，单击【标签】命令组中的【图表标题】按钮，选择“图表上方”，选中图表标题，将标题内容修改为“各科平均分”。再单击 【标签】命令组中的【图例】选项，选择 “无”。
（4）单击【图表工具】功能区中的【布局】选项卡，单击【标签】命令组中的【数据标签】选项，选择“上方”。
（5）在图表空白区右击，在快捷菜单中选择【设置图表区域格式】，打开【设置图表区格式】对话框，在“填充”中选择“图片或纹理填充”，在“纹理”下拉选项中选择“水滴”。
（6）移动图表，将整张图表置于A38：K55区域内。
完成的图表如下图4-6-6所示：
图4-6- 6制作的图表
4、对工作表“sheet2”中的数据表进行数据处理。
（1）将工作表“sheet1”中的表格数据A1:J31复制到“sheet2”中，将鼠标指针定位在数据区的任一单元格中，选择选项卡【数据】→【筛选】命令，单击列标题“平均分”右侧的自动筛选按钮，打开相应下拉列表，并从中选择“不及格”选项，则筛选出平均分不及格的学生，结果如图4-6-7。双击工作表标签“sheet2”，输入“平均分不及格学生”。
图4-6- 7平均分不及格的学生筛选结果
（2）将工作表“sheet1”中的表格数据A1:J31复制到“sheet3”中，将列标题A2:J2复制到A33:J33区域，在复制的列标题下方不同行分别输入条件“<60”，建立好的条件区如图4-6-8。
图4-6- 8建立的条件区域
将鼠标指针定位在工作表数据区的任一单元格中，选择【数据】→【筛选】→【高级】命令。打开“高级筛选”对话框，在“方式”选项区域中选择“在原有区域显示筛选结果”单选项，“列表区域”选择“A2:J31”，条件区域选择“A33:J37”，如图4-6-9所示。确定后，显示的就是科目不及格的学生情况，具体结果如图4-6-10。
图4-6- 9高级筛选区域参数设置
图4-6- 10高级筛选的结果
（3）将工作表“sheet1”中的表格数据A1:J31复制到“sheet4”中，将鼠标指针定位在“性别”列的任一单元格中，选择选项卡【数据 】→【排序】的命令或，将表中数据按性别进行了排序。选择选项卡【数据】→【分类汇总】命令，打开“分类汇总”对话框，在“分类字段”下拉列表中选择“性别”，在“汇总方式”下拉列表中选择“求平均值”，在“选定汇总项”选项区域中选中“语文”、“数学”、“英语”、“物理”等四个复选框，如图4-6-11所示。确定后，得到汇总结果，如图4-6-12。
图4-6- 11
图4-6- 12汇总后的结果
C任务评价
| 任务完成情况 | 自我评价 | 小组评价 |
| 工作表格式化 | □完成□待完善原因： | ☆☆☆☆☆ |
| 图表制作 | □完成□待完善原因： | ☆☆☆☆☆ |
| 数据处理 | □完成□待完善原因： | ☆☆☆☆☆ |
