小李是北京某政法学院教务处的工作人员，法律系提交了2012级四个法律专业教学班的期末成绩单，为更好地掌握各个教学班学习的整体情况，教务处领导要求她制作成绩分析表，供学院领导掌握宏观情况。请根据考生文件夹下的“Excel素材.xlsx”文档，帮助小李完成2012级法律专业学生期末成绩分析表的制作。具体要求如下：

1. 在考生文件夹下，将“Excel素材.xlsx”文件另存为“Excel.xlsx”(“.xlsx”为扩展名)，后续操作均基于此文件，否则不得分。

2. 在“2012级法律”工作表最右侧依次插入“总分”、“平均分”、“年级排名”列;将工作表的第一行根据表格实际情况合并居中为一个单元格，并设置合适的字体、字号，使其成为该工作表的标题。对班级成绩区域套用带标题行的“表样式中等深浅15”的表格格式。设置所有列的对齐方式为居中，其中排名为整数，其他成绩的数值保留1位小数。

3. 在“2012级法律”工作表中，利用公式分别计算“总分”、“平均分”、“年级排名”列的值。对学生成绩不及格(小于60)的单元格套用格式突出显示为“黄色(标准色)填充色红色(标准色)文本”。

4. 在“2012级法律”工作表中，利用公式、根据学生的学号、将其班级的名称填入“班级”列，规则为：学号的第三位为专业代码、第四位代表班级序号，即01为“法律一班”，02为“法律二班”，03为“法律三班”，04为“法律四班”。

5. 根据“2012级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中，工作表标签颜色设置为红色。要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中行标签为班级。为数据透视表格内容套用带标题行的“数据透视表样式中等深浅15”的表格格式，所有列的对齐方式设为居中，成绩的数值保留1位小数。

6. 在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10:H30区域中。

对 应 步 骤

1、复制Excel素材，右击重命名为Excel即可。

2、在右侧三列依次输入：总分、平均分、年级排名;选中A1：O1→单击合并后居中

为标题换个字体，增大字号

选中A2：O102区域，套用表格样式→选中等深浅样式15

勾选表包含标题，确定

所有列居中对齐

选中各科成绩区域右击设置单元格格式→数值→保留1位小数

选中排名区域右击设置单元格格式→数值→保留0位小数

3、总分等于各科成绩总和，直接用sum函数求和即可;sum(数据)

输入=sum() 然后在括号里面选取这些数据即可。

同理求平均分用average函数。average(数据)

求排名要用rank函数，rank函数参数(排名的数据，排名的区域)

写函数根据参数套用即可,根据总分进行排名，排名的数据为M3单元格，排名的区域为总分这一列

即=RANK(M3,M3:M102) (提示：因为我们套用了表格样式，所以就显示成了总分这个公式名称，这些名称不需要自己输入，做题的时候直接选择M3单元格，和M3:M102就可以了)

(rank函数不会请看链接：RANK函数 )

选中各科成绩区域即D3:L102→条件格式→突出显示单元格规则→小于

输入60→单击旁边的下拉三角→自定义格式为黄色填充，红色文本

4、本题用if函数判断即可，if函数(判断的条件，满足条件的值，不满足条件的值)

思路为：如果学号=01，那么返回“法律一班”，如果学号=02，那么返回“法律二班”，如果学号=03，那么返回“法律三班”，剩下的都返回法律四班，即不满足条件的值。

好，现在我们要做的就是把学号第3位和第4位提取出来，根据上面的思路判断即可。

提取函数用mid(提取的数据，提取的位置，提取几位)

(不会提取的看链接：Excel|提取函数)

即：从学号中提取，第3位开始提取，提取两位=mid(B3,3,2)

好，学号提取完成，可以按照思路进行判断了。

5、选择表中的所有数据(除了标题行)→插入→数据透视表→位置选择新工作表→确定

重命名这个新工作表为“班级平均分”，并切右击工作表标签设置为红色

把班级拖动到行，各科拖动到值。

选择数据透视表的标题项→右击→值字段设置→值汇总方式为平均值，把每一个科目都改为求平均值。

平均值改好后如图：

选择数据透视表→数据透视表工具选项卡→设计→中等深浅15

(提示：设置数据透视表样式，千万不要套用表格样式)

选择数据透视表整体→居中对齐→垂直和水平都要居中，否则会扣分

再选中数值区域→设置单元格格式→保留1位小数

6、选择数据透视表区域(不要选择总计一行)→插入→二维簇状柱形图

然后将图表拉大，放置于A10:H30区域中即可

最后保存就可以啦，跟着练习哦

阅读全文