会做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区域。
逐步地
1.复制Excel素材,右键,重命名为Excel。
2.在右边三栏依次输入总分、平均分、年级排名;选择A1: O1 →点击合并并居中。
更改标题的字体并增加字号。
选择A2: O102区域,应用表格样式→选择等深样式15。
检查包含标题的表格并确认。
将所有列居中对齐。
选择各科分数区域,右键设置单元格格式→数值→保留1位小数。
选择排名区域,右键设置单元格格式→数值→保留0位小数。
3.总分等于所有科目成绩之和,可以用sum函数直接求和;总和(数据)
输入=sum(),然后在括号中选择这些数据。
同样,用average函数求平均分。平均值(数据)
排名函数和排名函数参数(排名数据,排名面积)应用于排名。
函数可以根据参数编写应用,根据总分排名。排名数据为M3单元格,排名区域为总分栏。
即=RANK(M3,M3:M102)(提示:因为我们应用了表格样式,所以显示为总分的公式名称。您不需要自己输入这些名称。做题的时候直接选择M3细胞和M3: M102就可以了)
(rank函数不会看到链接:RANK函数)
选择各科得分区域,即D3:L102→条件格式→高亮单元格规则→小于
输入60→点击旁边的下拉三角形→自定义格式为黄色填充红色文字。
4.这个问题可以用if函数来判断,if函数是判断的条件,满足条件的值,不满足条件的值。
思路是:如果学号=01,那么返回法律类1,如果学号=02,那么返回法律类2,如果学号=03,那么返回法律类3,剩下的返回法律类4,也就是不符合条件的值。
好了,现在要做的就是提取第三个和第四个学号,按照上面的思路进行判断。
提取功能使用mid(提取的数据、提取的位置、提取的位)
(无法提取的见链接:Excel|提取函数)
即从学号中提取,第三位开始提取,提取的两位=mid(B3,3,2)。
好了,学号提取完成,可以按照思路判断了。
5.选择表格中的所有数据(标题行除外)→插入→透视表→定位选择新工作表→确定。
将新工作表重命名为“Class Average Score ”,剪切并右键单击工作表标签,将其设置为红色。
将班级拖到行上,将科目拖到值上。
选择透视表的表头项目→右键→值字段设置→平均值汇总方式,将各科目改为平均值。
平均值改变后,见下图:
选择数据透视表→数据透视表工具选项卡→设计→中等深度15
(提示:设置透视表样式,不要应用表格样式)
整体选择透视表→居中对齐→纵横居中,否则会丢分。
选择数值区→设置单元格格式→保留1位小数。
6.选择数据透视表区域(不要选择汇总行)→插入→ 2D聚类柱形图。
然后放大图表,将其放在A10:H30区域。
最后,你可以保存它。遵循惯例。