Excel成绩单制作技巧求班级名次各班各科平均分各班前3名学生按班级分表.docx
- 文档编号:4826118
- 上传时间:2022-12-10
- 格式:DOCX
- 页数:12
- 大小:184.70KB
Excel成绩单制作技巧求班级名次各班各科平均分各班前3名学生按班级分表.docx
《Excel成绩单制作技巧求班级名次各班各科平均分各班前3名学生按班级分表.docx》由会员分享,可在线阅读,更多相关《Excel成绩单制作技巧求班级名次各班各科平均分各班前3名学生按班级分表.docx(12页珍藏版)》请在冰豆网上搜索。
Excel成绩单制作技巧求班级名次各班各科平均分各班前3名学生按班级分表
在平时的学校考试中,“用Excel制作成绩单”是一项非常常见的应用。
下面就对成
绩单制作过程中所涉及到的几个常见的Excel知识点逐一分析。
快速预览效果
1.求各科总分
2.求年级名次
3.求班级名次
4.求各班各科平均分
5.查询各班前3名
6.按班级拆分工作表
、求各科总分
知识点:
SUM函数
难度:
1
在I2单元格输入公式=SUM(E2:
H2),按Enter确定,然后双击向下填充
、求年级名次
知识点:
RANK函数
难度:
1
在J2单元格输入公式=RANK(I2,$I$2:
$I$275),按Enter确定,然后双击向下填充
数据一共有275行)。
注意RANK函数的第二个参数必须采用绝对引用的方式,即
$I$2:
$I$275
三、求班级名次
知识点:
SUMPRODUCT函数
难度:
5
在求班级名次之前我们先来看看SUMPRODUCT函数的基本用法。
比如下面一张销售统计表,如何计算销售总额?
可能我们想到的方法是先计算每一个
品类的销售额,然后利用SUM函数计算销售总额。
但其实我们有更简单的方法,那就是SUMPRODUCT函数,它能够用一个公式实现上
述同样的计算过程,并得到同样的结果
通过上述两个函数的对比,相信大家应该能够明白SUMPRODUCT函数的工作原理了,
那下面就回到正题上,看看在成绩单中如何利用它计算班级名次。
我们在K2单元格输入公式
,然后双击向下填充即
SUMPRODUCT((C2=$C$2:
$C$275)*(I2<$I$2:
$I$275))+1
公式中的(C2=$C$2:
$C$275)是用于判断C2单元格是否等于C2至C75范围的值,
其目的是为了筛选出C列所有和C2单元格一致的数据。
这个等式会返回一个数组,大家可以选中这部分公式之后按F9键显示结果。
操作步骤如下图所示:
这个数组开头为{TRUE;FALSE;FALSE;TRUE;TRUE}
同理,公式中的(I2<$I$2:
$I$275)也会返回一个数组,其目的在于判断I2的总分是否
小于所有的总分,按F9键显示为{FALSE;FALSE;TRUE;TRUE;FALSE...}
然后TRUE和FALSE在参与计算的时候就会转化为1和0,也即
{TRUE;FALSE;FALSE;TRUE;TRUE}x{FALSE;FALSE;TRUE;TRUE;FALSE...}={1,0,0,1,1.
..}x{0,0,1,1,0...}={0,0,0,1,0...}
而SUMPRODUCT({0,0,0,1,0...})=1
最后在SUMPRODUCT后面加的一个1,是为了修正结果,得到第一个学生正确的班
级名次2.
需要注意的是,这个SUMPRODUCT函数的计算过程类似数组公式,计算量很大,单单是计算第一个学生的班级名次,就出现了275x275这么大的计算量,如果算完所有学生的班级名次,Excel在后台默默地做大量的运算工作。
电脑配置比较差的朋友,可能会出现电脑轻微的卡顿。
四、求各班各科平均分
知识点:
AVERAGEIF函数
难度:
3
说到求平均值,有点Excel基础的朋友都知道有一个AVERAGE函数可用,其实
AVERAGEIF函数其实就比AVERAGE函数仅仅多了一个判断而已,所以并没有太大的难度。
求各班各科平均分的难度,主要在于单元格的“混合引用”上
我们只需在N2单元格输入公式=AVERAGEIF($C$2:
$C$275,$M3,E$2:
E$275),然后
向右、再向下拖动填充即可。
需要注意的是公式中的第2和第3个参数的引用方式,之所以要用$M3这样的引用方
式,是为了要锁定M列,以保证在向右拖动填充的过程中,第2个参数始终都是对应的班级这一列。
同理,E$2:
E$275这样的引用方式锁定行,是为了保证在向下拖动的过程中,始终计算的是第2行至第275行的分数。
完整的操作过程如下所示(切换引用方式可以用F4键):
五、查询各班前3名
知识点:
INDEX+MATCH函数组合、数组公式难度:
5
说到数据查询,可能大家都能想到VLOOKUP函数,和INDEX+MATCH函数组合,一般来说INDEX+MATCH函数组合更好用些,因为它不用考虑数据列的前后顺序,非常的灵活。
这里的“查询各班前3名”,就要用到上面提到的函数组合。
在进行正式的查询之前,我们先来看看一个小例子,用于解释INDEX+MATCH的工
作原理。
还是以这个成绩表为例,我们要查找“高昌健”同学的总分,就可以用这个
公式来完成:
=INDEX(I2:
I275,MATCH(M19,B2:
B275,0))
这个公式很好理解,我们应该由内而外来看,首先用MATCH函数在B2:
B275精确查
找M19对应的内容,返回对应的索引位置(8),然后INDEX函数在I2:
I275范围的
第8个位置查找数据,即总分407.5
面回归正题。
我们在查询“各班总分前3名”之前,还有一个难点没有解决,就是如何进行多条件查询,即要用“班级”和“名次”这两个字段来查找对应的“姓名
这里简单的做法是建立一个辅助列,将“班级”和“名次”这两个字段连接起来,作
为一个字段,这样就变成了单条件查询
然后在O12单元格输入如下公式,并向右、向下拖动填充。
=INDEX($C$2:
$C$275,MATCH($N12&O$11,$A$2:
$A$275,0))
这里同样要注意MATCH函数中的混合引用方式,其原理与“求各班各科平均分”类
似,可以对比着看看
这样就完成了各班总分前3名的查询,然而,辅助列的出现还是不太美观,有没有一
种方法可以不用辅助列呢?
当然有,那就是借用数组公式,将“班级”和“名次”这两个字段连接成一个新数组,作为MATCH函数的第2个参数。
具体公式如下(注意
表结构的前后变化):
=INDEX($B$2:
$B$275,MATCH($M12&N$11,
$C$2:
$C$275&$K$2:
$K$275,0))
可能大家也都注意到了上图中的公式最外面有一对大括号{},这是数组公式的特点,在
N12单元格输入上述公式之后需要按CTRL+SHIFT+ENTER三键确定输入,而不是
ENTER一个键。
公式中的$C$2:
$C$275&$K$2:
$K$275和之前的辅助列返回的结果,除了第一行标题
之外,完全一样。
大家可以选中这部分公式,按F9键查看结果,如下图所示。
六、按班级拆分工作表
知识点:
插件的使用
难度:
1
拆分工作表的方法很多,常用的方法是插件和VBA,前者一键完成,最为便捷,后者
需要有一定的VBA基础,但也相对不难。
考虑到办公的效率,这里仅介绍借助插件进行工作表的拆分
有很多插件都可以完成工作表的拆分任务,这里以“易用宝”为例,这个插件可以在
ExcelHome官网免费下载。
安装完插件之后,在【易用宝】菜单找到【工作表管理】命令按钮,选择【拆分工作
表】,拆分区域选择A1:
K275,主拆分字段选择第3列(班级),将所有的【可选拆分项】都添加到右侧的【待拆分项】,直接点击【分拆】按钮即可,其它选项保持默
面拆分之后的结果
七、总结
在这个成绩单的制作过程中,涉及到了简单的函数应用,比如SUM、RANK,以及稍
微复杂的函数应用,比如AVERAGEIF、INDEX+MATCH,在函数应用过程中需要重点把握的是单元格或区域的引用方式,其中以混合引用最不好理解,大家在以
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 成绩单 制作 技巧 班级 名次 各科 平均 班前 学生
![提示](https://static.bdocx.com/images/bang_tan.gif)