当前位置:首页 > 办公 > Excel教程 > 正文内容

Excel 函数操作题你会吗?

酷网3周前 (09-28)Excel教程45

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

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

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39029.html

分享给朋友:

相关文章

Excel批量录入序列和日期步骤

Excel批量录入序列和日期步骤

  我们在使用Excel办公的时候,有的时候就会需要按照日期顺序在Excel中进行日期进行排序,这个时候如果还是按照基础操作一个一个录入数据的话,效率低下,浪费时间。今天就教大家如何批...

excel表格怎么放大打印?

excel表格怎么放大打印?

  相信有时候大家有时候会遇到excel表格需要放大打印时不知道怎么去做,下面小编来介绍一个excel表格设置放大打印的方法,有兴趣的朋友一起来看一看吧。   1、点击文件&m...

必看!最好用的28个Excel公式

必看!最好用的28个Excel公式

一起来看看这28个公式,你还记得几个? 1.根据成绩的比重,获取学期成绩。   =C8*$C$5+D8*$D$5+E8*$E$5 引用方式有绝对引用、混合引用、相对引用,可以借助F4键快速切换。 如果学了SUM...

电脑Excel文件损坏该怎么办?电脑Excel文件损坏修复详细步骤

电脑Excel文件损坏该怎么办?电脑Excel文件损坏修复详细步骤

  Excel文件是我们平时使用的一个必不可少的工具,我们经常在生活或是工作中用到它,如果我们碰到Excel文件突然损坏了该怎么处理呢?下面请遇到同样问题的小伙伴跟我一起解决Excel...

三个非常好用的隐藏函数

三个非常好用的隐藏函数

三个非常好用的隐藏函数!DATEDIF、NUMBERSTRING、DATESTRING这三个函数,在Excel的函数列表里是找不到,帮助文件里也没有用法说明,但的确功能强大,而且在工作中应用非常广泛。 DATEDIF 用...

每人报名项目多少不一,到底有多少人报名?

每人报名项目多少不一,到底有多少人报名?

今天是我们学校一位学生,统计班级有多少人报名参加运动会比赛。有的学生报了一个项目,有的报了两个,还有报了三个,这么多学生这么多项目,他数也数不过来,于是问有没有公式? 把数据简化如下: 其实,这就是统计有多少个不重复值...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。