会做Excel操作题吗?

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

1.在考生文件夹下,将“Excel Material.xlsx”文件另存为“Excel.xlsx”()。xlsx”是扩展名)。后续操作以此文件为准,否则不得分。

2.在“2012法学”工作表的最右侧依次插入“总分”、“平均分”、“年级排名”栏;根据表格的实际情况将工作表的第一行合并成一个单元格,设置合适的字体和大小,使之成为工作表的标题。将带有标题行的“表格样式中等深度15”的表格格式应用到课程成绩区域。将所有列的对齐方式设置为居中,其中排名为整数,其他等级的值保留到小数点后一位。

3.在“2012级法学”工作表中,使用公式分别计算“总分”、“平均分”和“年级排名”栏的值。学生不及格(低于60分)的单元格格式突出显示为“黄色(标准色)填充红色(标准色)文本”。

4.在“法律班2012”工作表中,使用公式,根据学生的学号填写班级名称。规则是:学号第三位是专业代码,第四位代表班级序号,即01是法学一班,02是法学二班,03是法学三班,04是法学三班。

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

6.在“班级平均分数”工作表中,为每门课程的班级平均分数创建一个二维聚类柱形图,其中水平聚类标签是班级,图例项是课程名称,并将图表放置在表格下方的A10:H30区域。

Excel 函数操作题你会吗?-大盘站插图

逐步地

1.复制Excel素材,右键,重命名为Excel。

Excel 函数操作题你会吗?-大盘站插图1

2.在右边三栏依次输入总分、平均分、年级排名;选择A1: O1 →点击合并并居中。

Excel 函数操作题你会吗?-大盘站插图2

更改标题的字体并增加字号。

Excel 函数操作题你会吗?-大盘站插图3

选择A2: O102区域,应用表格样式→选择等深样式15。

Excel 函数操作题你会吗?-大盘站插图4

检查包含标题的表格并确认。

Excel 函数操作题你会吗?-大盘站插图5

将所有列居中对齐。

Excel 函数操作题你会吗?-大盘站插图6

选择各科分数区域,右键设置单元格格式→数值→保留1位小数。

选择排名区域,右键设置单元格格式→数值→保留0位小数。

Excel 函数操作题你会吗?-大盘站插图7

3.总分等于所有科目成绩之和,可以用sum函数直接求和;总和(数据)

输入=sum(),然后在括号中选择这些数据。

Excel 函数操作题你会吗?-大盘站插图8

同样,用average函数求平均分。平均值(数据)

Excel 函数操作题你会吗?-大盘站插图9

排名函数和排名函数参数(排名数据,排名面积)应用于排名。

函数可以根据参数编写应用,根据总分排名。排名数据为M3单元格,排名区域为总分栏。

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

(rank函数不会看到链接:RANK函数)

Excel 函数操作题你会吗?-大盘站插图10

选择各科得分区域,即D3:L102→条件格式→高亮单元格规则→小于

Excel 函数操作题你会吗?-大盘站插图11

输入60→点击旁边的下拉三角形→自定义格式为黄色填充红色文字。

Excel 函数操作题你会吗?-大盘站插图12

Excel 函数操作题你会吗?-大盘站插图13

Excel 函数操作题你会吗?-大盘站插图14

4.这个问题可以用if函数来判断,if函数是判断的条件,满足条件的值,不满足条件的值。

思路是:如果学号=01,那么返回法律类1,如果学号=02,那么返回法律类2,如果学号=03,那么返回法律类3,剩下的返回法律类4,也就是不符合条件的值。

好了,现在要做的就是提取第三个和第四个学号,按照上面的思路进行判断。

提取功能使用mid(提取的数据、提取的位置、提取的位)

(无法提取的见链接:Excel|提取函数)

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

Excel 函数操作题你会吗?-大盘站插图15

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

Excel 函数操作题你会吗?-大盘站插图16

5.选择表格中的所有数据(标题行除外)→插入→透视表→定位选择新工作表→确定。

Excel 函数操作题你会吗?-大盘站插图17

将新工作表重命名为“Class Average Score ”,剪切并右键单击工作表标签,将其设置为红色。

Excel 函数操作题你会吗?-大盘站插图18

将班级拖到行上,将科目拖到值上。

Excel 函数操作题你会吗?-大盘站插图19

选择透视表的表头项目→右键→值字段设置→平均值汇总方式,将各科目改为平均值。

Excel 函数操作题你会吗?-大盘站插图20

Excel 函数操作题你会吗?-大盘站插图21

平均值改变后,见下图:

Excel 函数操作题你会吗?-大盘站插图22

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

(提示:设置透视表样式,不要应用表格样式)

Excel 函数操作题你会吗?-大盘站插图23

整体选择透视表→居中对齐→纵横居中,否则会丢分。

选择数值区→设置单元格格式→保留1位小数。

Excel 函数操作题你会吗?-大盘站插图24

Excel 函数操作题你会吗?-大盘站插图25

6.选择数据透视表区域(不要选择汇总行)→插入→ 2D聚类柱形图。

Excel 函数操作题你会吗?-大盘站插图26

然后放大图表,将其放在A10:H30区域。

最后,你可以保存它。遵循惯例。