首页 > Excel专区 > Excel函数 >

Excel 函数操作题你会吗?

Excel函数 2021-12-16 17:47:32

小李是北京某政法学院教务处的工作人员,法律系提交了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区域中即可

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


标签: RANK函数rank函数怎么用rank函数的使用方法rank函数的用法rank排名函数excel函数

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.