在考务工作中巧用Excel透视表进行数据分析

□韩英杰

作为一名考务工作人员，经常会面对大量的分类统计工作，尤其是对多个集合中的多个类别进行统计，如果使用常规方法，那就需要逐个集合进行分类统计，最后再将统计结果合并，这样的操作既繁琐又容易出错。笔者经过实践，发现这类问题可以借助Excel透视表来处理，透视表通过可视化的操作，将相关字段定义到“行”“列”“计数项”三项中，可以快速完成各类复杂的统计工作。笔者将结合几个考务工作中常见的典型实例，谈一谈如何使用Excel的“透视表”实现统计工作。

一、使用透视表制作“考生成绩统计表”

“考生成绩统计表”实际上是以“某一个总分”为一个类，统计考得该分数的考生人数和大于等于该分的累计人数，“考生成绩统计表”是考生填报志愿的重要依据，一般“考生成绩统计表”包含三列，分别是“总分”“人数”“累计人数”（如图1所示），下面我们分别讲解如何制作“人数”和“累计人数”两列数据。

图1

1.制作“人数”列。

（1）打开成绩单，保留“姓名”和“总分”两列必要的数据。先用鼠标点击任意一个非空的单元格（例如图2中的A1单元格），然后点击“数据”菜单中的“数据透视表”。

图2

（2）屏幕上显示透视表对话框，确认单元格区域参数是全部有效数据的区域后，点击“确定”按钮（如图3）。

图3

（3）在工作表的右侧出现“数据透视表”的设置区，将字段列表中的“总分”字段拖拽到“行”区域中，将“姓名”拖拽到“值”区域中。

完成拖拽后，左侧会出现两列数据，其中“总分”是成绩单中所有总分，默认是从小到大排序，“计数项：姓名”是对应每个总分的人数，也就是考得相同的总分总共有几个人，这个人数是通过对姓名数据的个数进行统计的（注：即使里面有相同姓名的人也不会影响统计结果，因为系统是按照姓名的个数进行统计的）。

（4）为了符合从高分到低分的排序，可以点击“总分”右边的向下箭头，从中选取“降序”（如图5）。

图5

这样“考生成绩统计表”中的“总分”和对应的“人数”列就生成了，如图6所示。

图6

2.生成“累计人数”列

图6中已经统计了每个总分的个数，下面将为每个总分生成“累计人数”。

第一步：在C4单元格输入“=B4”；第二步：在C5单元格输入“=C4+B5”，表示该行总分对应的累计人数是上一累计人数加上当前总分对应的人数，回车确认后得到了对应554分的累计人数；第三步：双击C5的右下角“十”字，自动填充C列剩余的单元格。

这里需要注意的是，上面的“=C4+B5”公式中“B5”字符需要手工输入，不要使用使用鼠标点击B5单元格，否则会出现公式调用错误。

如图7，这样就可以生成“考生成绩统计表”中的“累计人数”数据了。

图7

3.格式编辑，生成标准的“考生成绩统计表”

将上表中全部数据进行复制，然后右键点击新建工作表中的空白单元格，点击“选择性粘贴”-“粘贴为数值”的方式粘贴到一个新建的工作表中，即可得到标准的“考生成绩统计表”。如图8所示。

图8

二、使用透视表实现分数段人数统计

如下图所示，需要统计一个年级各班总分500分以下，以及500分至600分之间，每隔20分一个分数段的人数。统计的结果如图9所示。

图9

制作方法分为三步：

通过透视表对各班的各个分值的考生人数进行统计。

（1）成绩单要保留必要的姓名、班级、总分三个字段。如图10所示。

图10

（2）对每个分数对应的人数进行汇总。对成绩单执行透视表，在“数据透视表”的设置区域中，将字段列表中的“班级”拖入到“行”，“总分”拖入到“列”，“姓名”拖入到“值”。如图11所示。

图11

当前列表中的数值的含义是“某班考取某分数的人数”。例如H8单元格中的1代表“4班考取168分有1名考生”。如图12所示。

图12

这是对每一个分数对应的人数进行汇总，那么如何按照分数段进行人数汇总呢？我们还需要下面的操作。

（3）对分数进行分段汇总

点击“总分”右侧的向下箭头，在弹出的菜单中选择“组合”，如图13所示。

图13

在弹出的对话框中将“起始于”填上500，将“终止于”填写600，将“步长”填写20。如图14所示。

图14

点击“确定”后，表中出现分数分组统计结果，如图15。

图15

将上面的结果数据复制后选择性粘贴为数值到一个新工作表中，经过格式编辑，即可实现最终要求。

三、统计每个校区中不同班级各分数段考生的数量

当前很多大一些的学校都有多个校区，如图16中这个学校的成绩单中有“东校区”“南校区”两个校区，每个校区中又有多个班，那么我们是否可以统计出两个校区中各班的分段成绩统计表。

图16

按照常规思维，先将东校区的数据筛选出来组成一个表，再将南校区筛选出来新建一个表，然后对这两个表分别进行分段统计，最后将两个表复制粘贴到一起。但是，利用透视表可以一次性完成多个校区、班级的分数段统计工作。

如图17所示，将“校区”和“班级”依次拖入“行”中，要保证“校区”在“班级”的上面，然后将“总分”拖入“列”中，将“姓名”拖入“值”中。

图17

这时，可以看到系统将所有考生按照校区分成了上下两个表格，然后在每个校区中又按照班进行了统计，这是因为在右边的透视表设置区域中，“行”里面“校区”在“班级”的上面，所以就有了先按照“校区”划分，再按照“班级”划分表的结构。

现在我们可以点击左边表中的“总分”进行分组，这一步类似本文“二、使用透视表实现分数段人数统计”，进一步统计各分数段的人数，设置起点500分，终止600分，每20分一个组，那么就有下面这个表格。如图18。

图18

综上所述，当需要对一组数据中的不同集合中不同分类进行统计时，我们可以采用透视表来实现。在透视表设计过程中，大家要分清“行”“列”“值”这三个参数，即可将一个复杂的数据分析结果用透视表清晰地展示出来。