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，在函数应用过程中需要重点把握的是单元格或区域的引用方式，其中以混合引用最不好理解，大家在以
升级会员 