巧妙利用数据透视表对学生成绩进行分析.docVIP

巧妙利用数据透视表对学生成绩进行分析 本实例以一个简单表格为例，通过excel的数据透视功能来分析每个学校不同学科的及格率、优秀率及差异系数。 1、假设下表为各校上交整理后的学生成绩总表，把空行空列及空单元格处理掉，如果多一些班级、性别等字段也可以，数据透视时不使用就行了。 2、为了统计各科及格率及优秀率，在表格后面对应前面学科增加相应列，如相对语文学科增加“语文及格”、“语文优秀”列，然后在G2单元格输入“=if(d2=60,1,0)”（注意输入时要在英文输入状态），然后拖动公式至本列结束。目的是把及格人标记为1，不及格标记为零，以便后面对及格人数进行统计，含义是如果d2单元格中的成绩大于60分就记为1，否则记为0。其它列以此类推最后再添加一个“记数”列，这是为统计学生人数设置的标志。 3、选择“数据——数据透视表和数据透视图” 4、单击“下一步”，选定数据区域。 5、单击“下一步”，选择一个存放数据透视表的单元格 6、单击“布局”，打开布局对话框，拖动相应字段如图所示。“学校”放在“行”处表示要根据不同学校进行数据统计。都统计哪些项目呢，学生总人数（求和项：记数）、语文成绩（求和项：语文）、及格人数（求和项：语文及格）、优秀人数（求和项：语文及格），所以这些项目放在了数据区域内，以后可以根据这些数据进行相应的计算。 7、刚拖放到数据区域的各数据项都是求和计数，这显然不符合我们的要求，双击字段可以进行修改。 （1）“求和项：计数”的修改：本项的目的是记录学校总人数，表格中“记数”我们都填充了“1”，所以求和的话能计算出总人数，汇总方式选择“求和”不再改变。形成透视表时这列名称会显示为“求和项：记数”比较难看，可以修改为：总人数。 （2）“求和项：语文”的修改：本项是为了统计各校语文科目的平均分，所以汇总方式更改为“平均值”，名称处修改为“平均分”，单击“数字”修改单元格格式。 （3）“求和项：语文及格”的修改：求和可以统计出语文成绩60分以上的人数，名称改为“及格人数”。 （4）“救和项：语文优秀”的修改：求和统计成绩在80分以上的人数 8、单击“确定”、“完成”可以形成数据透视表，如下图。 9、统计表格中统计各项和我们要求有较大差异，是纵向排列的。在字段“数据”上单击右键，选择“顺序——移至列”。效果如下图。 10、透视“及格率”：在工具栏上选择“数据透视表——公式——计算字段”，打开“插入计算字段”对话框，名称改为“及格率”，公式处填写“语文及格/记数”，这是用语文及格人数除以总人数。注意“语文及格”、“记数”可以通过双击下面的字段名插入。插入完成后要把里面的空格删除掉。 11、修饰“及格率”透视。单击“添加”后，效果如下图。选中“求和项：及格率”，单击工具栏“数据透视表——字段设置”，打开对话框，调整名称及数字格式（注意：如果名称处提示冲突可以加个空格）。在“数据透视表——顺序——向左移”调整顺序。 12、依照上面作法，增加优秀率统计。 13、添加“标准差”统计：使用工具栏：“数据透视表——数据透表向导——布局”，再拖一个“语文”统计项到数据区。 双击“求和项：语文”进行设置。汇总方式为“总体标准差”，名称改为“标准差”。进行数字格式设置。 14、求取“差异系数”。差异系数根据“标准差”而来，但数据透视表不允许再使用标准差进行计算，所以我们手工在右侧添加“差异系数列”，使用“标准差/平均分”填充，然后设置单元格格式为百分数即可。 15、在表格上方做个标题，可以醒目知道的表格内容。 至此，一个学科统计视图完成。