基于Excel数据分析处理功能统计学生成绩

　　摘要： 每次考试结束需对学生成绩进行统计分析，合理巧妙运用Excel数据分析处理功能，能使许多复杂的问题简单化，并可以大大提高工作效率。本文针对学生成绩表的多条件统计平均分、及格人数和及格率等需求，给出公式或函数法和数据透视表法两种解决方案，供教学和教师统计分析成绩时参考。
　　关键词： 公式或函数数据透视表多条件及格率平均分
　　
　　引言
　　每次全国普通高校非计算机专业计算机联合考试（一级）考试阅卷结束后，由自治区将成绩以Excel电子表格形式下发到各学校（以下简称成绩表）。成绩表的表头各字段名如下：
　　
　　
　　教师需对成绩表进行汇总统计与分析，了解全校各系、各年级、各专业的平均分、及格人数和及格率等情况，便于修订教学计划。
　　针对学生成绩表的多条件统计分析需求给出公式或函数法和数据透视表法两种解决方案。
　　1.公式或函数法
　　1.1 SUMPRODUCT函数
　　SUMPRODUCT函数是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
　　语法SUMPRODUCT（array1，［array2］， ［array3］， ...）
　　其中，数组参数Array1必需，Array2，array3，……可选，其相应元素需要进行相乘并求和。
　　SUMPRODUCT函数的基本功能是返回相应的区域或数组乘积的和。但也可利用SUMPRODUCT函数进行多条件计数和多条件求和。
　　若要统计同时满足条件1、条件2到条件n的数据个数，则可使用
　　SUMPRODUCT（（条件1）*（条件2）*…*（条件n））
　　若要统计同时满足条件1、条件2到条件n的数据的和，则可使用
　　SUMPRODUCT（（条件1）*（条件2）*…*（条件n）*求和区域）
　　1.2定义名称
　　为了在公式计算中方便输入和减少输入量，给需使用的单元格区域定义简单易记的名称，它们分别为年级、院系名称、理论成绩和操作成绩。
　　操作步骤：选择菜单“插入”→“名称”→“定义”命令，在定义名称对话框中输入名称和引用位置，确定即可。定义名称对话框如图1所示。
　　
　　
　　
　　
　　
　　
　　
　　
　　图1定义名称对话框
　　名称引用位置中使用了一个动态的公式“=OFFSET（成绩表!$F$2，，，COUNTA（成绩表!$A:$A）-1，1）”定义数据源，这样就不用管共有多少条记录了，COUNTA（成绩表!$A:$A）-1会自动计算记录数，并且如当一条新的纪录添加或删除时，数据源会自动扩展。
　　1.3创建统计表
　　添加一个新的工作表，用于存放统计数据，命名为“统计表”，表头设计如下：
　　
　　
　　
　　在A和B列分别输入年级和院系名称。
　　在C3单元格输入“=SUMPRODUCT（（年级=$A3）*（院系名称=$B3）*（理论成绩>=0））”；
　　在D3单元格输入“=SUMPRODUCT（（年级=$A3）*（院系名称=$B3）*（理论成绩>=60））”；
　　在E3单元格输入“=SUMPRODUCT（（年级=$A3）*（院系名称=$B3）*（操作成绩>=60））”；
　　在F3单元格输入“=D3/C3”；在G3单元格输入“=E3/C3”；
　　在H3单元格输入“=SUMPRODUCT（（年级=$A3）*（院系名称=$B3）*理论成绩）/C3”；
　　在I3单元格输入“=SUMPRODUCT（（年级=$A3）*（院系名称=$B3）*操作成绩）/C3”。
　　填充数据，并将小数位设置为两位，部分统计结果显示如下：
　　
　　
　　
　　
　　2.数据透视表法
　　2.1创建空白的数据透视表
　　选定数据清单中的任意单元格，选择菜单“数据”→“数据透视表和数据透视图”命令，在弹出的对话框中单击“完成”按钮。Excel将产生一个含有空白的数据透视表的工作表，其中显示字段列表和数据透视表工具栏。
　　2.2对数据透视表进行布局
　　若需统计各年级、各院系的理论成绩的平均分、及格人数和及格率等，则可从字段列表中将“年级”字段拖到页字段区，将“院系名称”拖到行字段区，将“理论成绩”字段拖到列字段区，将“理论成绩”、“学号”和“学号”字段依次拖到数据区。将数据透视表中的“数据”拖到右边的单元格。
　　若要改变数据透视表的布局，就将欲删除的字段拖出数据透视表，然后将新的字段从字段列表中拖到数据透视表相应区域即可。
　　2.3设置字段的显示方式和汇总方式
　　将“理论成绩”分为<60和≥60两种情况显示。操作方法：右击数据透视表中的“理论成绩”，在弹出的快捷菜单中选择“组及显示明细数据”→“组合”，在弹出的“组合”对话框中将步长设置为60。
　　Excel对“理论成绩”字段默认的汇总方式为“求和”。若要更改汇总方式为“平均值”，可右击“求和项:理论成绩”，选择“字段设置”，或先单击“求和项:理论成绩”，再单击数据透视表工具栏上的“字段设置”按钮，均可调出“数据透视表字段”对话框；在“数据透视表字段”对话框中，单击“汇总方式”列表框中的“平均值”，然后单击“确定”按钮。这就将“理论成绩”的汇总方式改成了“平均值”。
　　将“学号2”字段的显示方式更改为“占同行数据总和的百分比”。操作方法：右击“计数项:学号2”，选择“字段设置”，在调出“数据透视表字段”的对话框中，单击“选项”，在“数据显示方式”列表框中选择“占同行数据总和的百分比”，然后单击“确定”按钮。
　　2.4重命名字段
　　当向数据区域添加字段后，它们都将被重命名，例如“理论成绩”变成了“平均值项:理论成绩”，这样加大了字段所在列的宽度，表格显得不紧凑。如需重命名字段，只要单击数据透视表中需要修改的标题单元格，在编辑栏中输入新标题即可。我们将“平均值项:理论成绩”改为“理论平均分”，“计数项:学号”改为“人数”，“计数项:学号2”改为“人数占比”，将“0~59”改为“不及格”，将“60~119”改为“及格”。
　　稍作修饰，将小数位数设置为两位小数，隐藏“人数汇总”和“人数占比汇总”。至此，可得到如图2所示的数据透视表。
　　
　　
　　
　　
　　
　　图2数据透视表
　　数据透视表创建好后，任何时候都可根据需要，对数据透视表重新进行布局，即只需拖动字段按钮就可，并可非常方便地调整显示方式和汇总方式，以便从不同的角度查看分析数据，从中寻找有价值的信息，满足新的数据分析的要求。
　　结语
　　Excel具有强大的数据分析和数据处理功能，包含了9大类、400多个内置函数和大量的分析工具。公式或函数法和数据透视表法都是Excel分析和处理数据的重要手段，合理选取，巧妙应用，能使数据处理方便且高效。
　　
　　参考文献：
　　［1］魏零.巧用Excel数据透视表统计分析学生成绩［J］.科技信息，2010.7（19）.
　　［2］Excel Home.Excel数据透视表应用大全［M］.北京：人民邮电出版社，2009.6.
　　［3］陈国良.Excel 2010函数与公式［M］.北京：电子工业出版社，2010.12.