成绩表，相信大家都不会陌生。这张简简单单的表格贯穿了我们整个学生时代。这上面的数字和名次决定了我们放学回家的状态。这里就不多说了，自行脑补。
老规矩先上案例，如图所示：
图1 成绩表分析完成效果
图1 中，我们已经对图表完成了分析。现在，我们就一起来看看如何实现图1的分析效果。
第一步，大家要准备一个班级成绩表。
图2 班级成绩表
这里，为了演示方便，只做了3个课程的成绩分析，您自己可以使用自由增加课程。原理和思想是一样的。我们看到图1中有总分和排名。
总分也就是汇总，我们可以采用E F G列的方式来进行，同样也可以用Sum函数来汇总，这里咱们使用SUM来汇总，公式=SUM(E2:G2)
排名的话可以利用RANK函数或者RANK.EQ函数，如果大家不理解这个函数，建议大家可以去我的“excel技巧：RANK系列和COUNTIF函数组合进行排序（排名不重复）”这篇文章看一下。
这里我们使用RANK函数来进行排名，这里涉及到一个总分相同的特殊名次。这里为了增强同学的自信心，这里实现的是相同分数，相同名次。公式为：=RANK(H2,$H$2:$H$16)
上述两列实现效果图如下：
图3 实现总分和名次完成效果
第二步，实现最高分表。最高分包括：班级单科成绩最高分，年级单科成绩最高分。班级总分最高分，年级总分最高分。咱们知道最高分的话，函数MAX可以获取一组数据中数值最大的数据。但是如果改组数据中的函数满足条件某个条件时就不能得到我们想要的结果。这时MAXIFS函数就可以帮助我们了。
图4 MAXIFS函数描述
图5 MAXIFS函数提示
MAXIFS函数是一个可变多参数函数。这里只分析5参数的情况，
参数1：需要取最大值的单元格区域。
参数2：条件单元格区域1
参数3：满足的条件1
参数4：条件单元格区域2
参数5：满足的条件2
.....
咱们使用MAXIFS函数来分析，一班的语文最高分L3的公式可以写成 =MAXIFS(E$2:E$16,$B$2:$B$16,$K3) 这种写法是可以填充到到O5的。（相信大家经常会看到$符号，这符号代表的是绝对引用。后面我会针对绝对引用和相对引用详细给大家说个案例。）
图6 MAX函数的描述
咱们看到MAX函数的描述，所以年级的最高分直接使用MAX函数，L6单元格的公式 = MAX(E2:E16) M6到O6可以直接利用自动填充完成。
完成效果图如下：
图7 MAX和MAXIFS函数完成的效果图
第三步，实现平均分。平均分包括：班级单科成绩平均分，年级单科成绩平均分。班级总分平均分，年级总分平均分，班级总分平均分排名。这里会用到两个函数
图8 AVERAGE函数描述
图9 AVERAGEIF函数描述
AVERAGE和AVERGEIFS函数的参数和MAX和MAXIFS函数的参数是类似。这里就不进行一一分析了。
一班的语文平均分L9的公式为：=AVERAGEIF($B$2:$B$16,$K9,E$2:E$16)
年级平均分L12的公式为： =AVERAGE(E2:E16)
一班平均分在年级中的排名P9的公式为：=RANK.EQ(O9,$O$9:$O$11)
实现效果图如下：
图10 平均分的实现
第四步，实现学生数量。班级男生数量，班级女生数量 ，班级学生总数。年级男生数量，年级女生数量，年级学生总数。这里我们用到三个函数，Countif，CountA，CountIfs。
图11 单条件Countif函数描述
图12 多条件Countifs函数描述
图13 CountA函数描述
这里三个参数同样和MAX和MAXIFS函数的参数类似。这里就不一一解释了。
一班的男生数量L15单元格公式为：=COUNTIFS($D$2:$D$16,"男",$B$2:$B$16,$K15)
一班的女生数量M15单元格公式为：=COUNTIFS($D$2:$D$16,"女",$B$2:$B$16,$K15)
一班的学生总数N15单元格公式为：=COUNTIF($B$2:$B$16,$K15)
年级男生总数为L18单元格公式为：=COUNTIFS($D$2:$D$16,"男")
年级男生总数为M18单元格公式为：=COUNTIFS($D$2:$D$16,"女")
年级学生总数N18单元格公式为：=COUNTA(B2:B16)
这里，这几个函数一起写的话，录制的GIF太大，上传不了。我直接在单元格中将公式洗好了。给大家看一下效果。
完成效果图如下。
图14 学生数量完成效果
最后，我们对表格进行美化一下：
图15 表格的美化
到这里，这个案例就结束了。最后，感谢大家的观看，我是EK。如果上述内容能够帮助到你们，希望你们能点赞，关注，评论，你们不清楚的地方我会尽力为大家解答，谢谢大家的支持。我会给大家带来更多关于EXCEL的小技巧。
评论 (0)