Excel成绩单制作技巧求班级名次各班各科平均分各班前3名学生按班级分表.docx

1、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

2、 确定，然后双击向下填充数据一共有 275 行)。注意 RANK 函数的第二个参数必须采用绝对引用的方式，即$I$2:$I$275三、求班级名次知识点： SUMPRODUCT 函数难度： 5在求班级名次之前我们先来看看 SUMPRODUCT 函数的基本用法。比如下面一张销售统计表，如何计算销售总额？可能我们想到的方法是先计算每一个品类的销售额，然后利用 SUM 函数计算销售总额。但其实我们有更简单的方法，那就是 SUMPRODUCT 函数，它能够用一个公式实现上述同样的计算过程，并得到同样的结果通过上述两个函数的对比，相信大家应该能够明白 SUMPRODUCT 函数的工作原理了，那下面就回到正

3、题上，看看在成绩单中如何利用它计算班级名次。我们在 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 的总

4、分是否小于所有的总分，按 F9 键显示为 FALSE;FALSE;TRUE;TRUE;FALSE.然后 TRUE 和 FALSE 在参与计算的时候就会转化为 1 和 0，也即TRUE;FALSE;FALSE;TRUE;TRUE xFALSE;FALSE;TRUE;TRUE;FALSE.=1,0,0,1,1.x0,0,1,1,0.=0,0,0,1,0.而 SUMPRODUCT(0,0,0,1,0.)=1最后在 SUMPRODUCT 后面加的一个 1，是为了修正结果，得到第一个学生正确的班级名次 2.需要注意的是，这个 SUMPRODUCT 函数的计算过程类似数组公式，计算量很大，单 单是计算第一

5、个学生的班级名次，就出现了 275x275 这么大的计算量，如果算完所 有学生的班级名次， Excel 在后台默默地做大量的运算工作。电脑配置比较差的朋友， 可能会出现电脑轻微的卡顿。四、求各班各科平均分知识点： AVERAGEIF 函数难度： 3说到求平均值，有点 Excel 基础的朋友都知道有一个 AVERAGE 函数可用，其实AVERAGEIF 函数其实就比 AVERAGE 函数仅仅多了一个判断而已，所以并没有太大 的难度。求各班各科平均分的难度，主要在于单元格的“混合引用”上我们只需在 N2 单元格输入公式 =AVERAGEIF($C$2:$C$275,$M3,E$2:E$275) ，

6、然后向右、再向下拖动填充即可。需要注意的是公式中的第 2 和第 3 个参数的引用方式，之所以要用 $M3 这样的引用方式，是为了要锁定 M 列，以保证在向右拖动填充的过程中，第 2 个参数始终都是对应 的班级这一列。同理， E$2:E$275 这样的引用方式锁定行，是为了保证在向下拖动的 过程中，始终计算的是第 2 行至第 275 行的分数。完整的操作过程如下所示( 切换引用方式可以用 F4 键)：五、查询各班前 3 名知识点： INDEX+MATCH 函数组合、数组公式 难度： 5说到数据查询，可能大家都能想到 VLOOKUP 函数，和 INDEX+MATCH 函数组合， 一般来说 INDE

7、X+MATCH 函数组合更好用些，因为它不用考虑数据列的前后顺序， 非常的灵活。这里的“查询各班前 3 名”，就要用到上面提到的函数组合。在进行正式的查询之前，我们先来看看一个小例子，用于解释 INDEX+MATCH 的工作原理。还是以这个成绩表为例，我们要查找“高昌健”同学的总分，就可以用这个公式来完成： =INDEX(I2:I275,MATCH(M19,B2:B275,0)这个公式很好理解，我们应该由内而外来看，首先用 MATCH 函数在 B2:B275 精确查找 M19 对应的内容，返回对应的索引位置（ 8 ），然后 INDEX 函数在 I2:I275 范围的第 8 个位置查找数据，即总

8、分 407.5面回归正题。我们在查询“各班总分前 3 名”之前，还有一个难点没有解决，就是 如何进行多条件查询，即要用“班级”和“名次”这两个字段来查找对应的“姓名这里简单的做法是建立一个辅助列，将“班级”和“名次”这两个字段连接起来，作为一个字段，这样就变成了单条件查询然后在 O12 单元格输入如下公式，并向右、向下拖动填充。=INDEX($C$2:$C$275,MATCH($N12&O$11,$A$2:$A$275,0)这里同样要注意 MATCH 函数中的混合引用方式，其原理与 “求各班各科平均分” 类似，可以对比着看看这样就完成了各班总分前 3 名的查询，然而，辅助列的出现还是不太美观，

9、有没有一种方法可以不用辅助列呢？当然有，那就是借用数组公式，将“班级”和“名次”这 两个字段连接成一个新数组，作为 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 和之前的辅助列返回的结果，除了第一行标题之

10、外，完全一样。大家可以选中这部分公式，按 F9 键查看结果，如下图所示。六、按班级拆分工作表知识点：插件的使用难度： 1拆分工作表的方法很多，常用的方法是插件和 VBA ，前者一键完成，最为便捷，后者需要有一定的 VBA 基础，但也相对不难。考虑到办公的效率，这里仅介绍借助插件进 行工作表的拆分有很多插件都可以完成工作表的拆分任务，这里以“易用宝”为例，这个插件可以在ExcelHome 官网免费下载。安装完插件之后，在【易用宝】菜单找到【工作表管理】命令按钮，选择【拆分工作表】，拆分区域选择 A1:K275 ，主拆分字段选择第 3 列（班级），将所有的【可选拆 分项】都添加到右侧的【待拆分项】，直接点击【分拆】按钮即可，其它选项保持默面拆分之后的结果七、总结在这个成绩单的制作过程中，涉及到了简单的函数应用，比如 SUM 、RANK ，以及稍微复杂的函数应用，比如 AVERAGEIF 、 INDEX+MATCH ，在函数应用过程中需要重 点把握的是单元格或区域的引用方式，其中以混合引用最不好理解，大家在以