|
在平时的学校考试中，“用Excel制作成绩单”是一项非常常见的应用。下面就对成绩单制作过程中所涉及到的几个常见的Excel知识点逐一分析。
快速预览效果
目录
一、求各科总分
在I2单元格输入公式=SUM(E2:H2)，按Enter确定，然后双击向下填充
二、求年级名次
在J2单元格输入公式=RANK(I2,$I$2:$I$275)，按Enter确定，然后双击向下填充（数据一共有275行）。注意RANK函数的第二个参数必须采用绝对引用的方式，即$I$2:$I$275
三、求班级名次
在求班级名次之前我们先来看看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在后台默默地做大量的运算工作。电脑配置比较差的朋友，可能会出现电脑轻微的卡顿。 四、求各班各科平均分
说到求平均值，有点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名
说到数据查询，可能大家都能想到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键查看结果，如下图所示。
六、按班级拆分工作表
拆分工作表的方法很多，常用的方法是插件和VBA，前者一键完成，最为便捷，后者需要有一定的VBA基础，但也相对不难。考虑到办公的效率，这里仅介绍借助插件进行工作表的拆分。 有很多插件都可以完成工作表的拆分任务，这里以“易用宝”为例，这个插件可以在ExcelHome官网免费下载。
安装完插件之后，在【易用宝】菜单找到【工作表管理】命令按钮，选择【拆分工作表】，拆分区域选择A1:K275，主拆分字段选择第3列（班级），将所有的【可选拆分项】都添加到右侧的【待拆分项】，直接点击【分拆】按钮即可，其它选项保持默认。
下面拆分之后的结果。
七、总结 在这个成绩单的制作过程中，涉及到了简单的函数应用，比如SUM、RANK，以及稍微复杂的函数应用，比如AVERAGEIF、INDEX+MATCH，在函数应用过程中需要重点把握的是单元格或区域的引用方式，其中以混合引用最不好理解，大家在以后的运用中要多加体会。 案例中还涉及到“数组公式”这样的稍微高阶的Excel知识，如果觉得理解有难度，大家可以先查阅相关资料，理解数组公式的特点和使用方式，然后再来研究这个案例。 最后，还介绍了Excel插件的使用，这里仅仅只是演示了Excel插件的一个小小的功能，而很多插件的功能都十分强大，可以极大地提高办公效率。除了“易用宝”插件之外，这里再给大家推荐几个插件：KUTOOLS、方方格子工具箱、Excel必备工具箱、慧办公。 |
|
|
