Excel练习题1-2 条件格式/MID函数/SUM函数/average函数/分类汇总/排名/汇总

蒋老师负责学生的成绩管理，根据下列要求帮助蒋老师对本次考试成绩进行整理和分析，要求操作过程中不得随意改变原数据表数据的排列顺序。

最终呈现效果

1、打开考生文件夹下的素材文档Excel.xlsx(“xlsx”为文件扩展名)后续操作均基于此文件，否则不得分。

2、按照下列要求对工作表“第一学期期末成绩”中的数据列表进行格式化操作:

①在“学号”的左侧插入一个空白列，列标题为“序号”，然后在该列中依次输入序列号“1、2、......”通过设置数字格式令其显示为“001、002、003........”、且能够参与计算。

插入空白列+设置单元格格式

②将B列“学号”数据设为文本格式；将所有成绩列的数字格式设为显示两位小数的数值。

将B列“学号”数据设为文本格式

将所有成绩列的数字格式设为显示两位小数的数值

③适当加大行高、调整列宽，改变字体、字号，并设置对齐方式。

适当加大行高（注：大于原行高）

调整列宽

改变字体、字号，并设置对齐方式

④为数据区域添加标准深蓝色的内外边框线，并通过设置条件格式使得数据区域的奇数行填充“金色，个性色4，淡色60%”。

为数据区域添加标准深蓝色的内外边框线

设置条件格式使得数据区域的奇数行填充

“金色，个性色4，淡色60%”

3.将语文、数学、英语三科中不低于110分的成绩所在的单元格以一种颜色填充，其他四科中高于95分的成绩以另一种字体颜色标出，所用颜色的深浅应保证数据能够被清晰识别。

将语文、数学、英语三科中不低于110分的成绩所在的单元格以一种颜色填充

其他四科中高于95分的成绩以另一种字体颜色标出

4.学号第3、4位代表学生所在的班级，例如:"120105"代表12级1班5号。要求通过公式提取每个学生所在的班级并按下列对应关系填写在“班级”列中:

5、分别利用Sum和Average函数计算每一个学生的总分及平均分并填入对应列中。

用SUM()函数计算总分

用AVERAGE()函数计算平均分

6、分别使用不同的方式计算每个学生的总成绩在全年级的排名，并显示为“第N名”。其中N列“年级排名(普通)”中显示美式排名，即成绩相同的名次占用位次(例如若有两个并列第1名，则没有第2名，下一个从第3名继续)；

O列“年级排名(中式)”中显示中式排名，即成绩相同的名次不占用位次(例如若有两个并列第1名，下一个仍从第2名继续)，排名时不得改变数据顺序。

美式排名

中式排名

="第"&SUMPRODUCT(($L$2:$L$121>=L2)/COUNTIF($L$2:$L$121,$L$2:$L$121))&"名"

7.复制工作表“第一学期期末成绩”，将副本放置到原表右侧;改变该副本表标签的颜色，并重新命名为“分类汇总"，删除工作表“分类汇总”中的两个排名列。

8、在工作表“分类汇总”中，通过分类汇总功能求出每个班各科的平均成绩，并设置每组结果分页显示。

通过分类汇总功能求出每个班各科的平均成绩

9、以分类汇总结果为基础，参照图片"E图表示例.png”创建一个组合图表，对每个班各科平均成绩进行比较，并将该图表放置在一个名为“柱状分析图”的新工作表的A1:M30单元格区域内。要求图表的类型、系列、坐标轴、图表标题、图例等的格式与内容与示例一致。

E图表示例.png

插入簇状柱形图

把柱形图移动到【柱状分析图】工作表

将图表放置在A1:M30单元格区域内