欢迎光临
我们一直在努力

EXCEL数据透视表,分析成绩快又好 统计分析表

开学了,考试的成绩得好好分析一下,教务处还需要分析报告呢!这难不倒小荷,因为她是Excel高手,而且她知道,只要请出数据透视表,不必输入复杂的函数与公式,仅仅通过向导就可以创建一个交互式表格,用鼠标拖拖拉拉就能完成对成绩的快速统计分析。

*准备工作:
输入必要的数据,工作表命名为“总表”,以D列的政治分数(满分100)为例,还要对源数据进行补充设计。在单元格E1~I1分别输入60以下(差)、60分及以上(及格)、60-79(中等)、80-89(良好)、90分以上(优秀)。然后在单元格E2~I2分别输入下面的公式:E2:=IF(D2<60,1,0),F2:=IF(D2>=60,1,0),G2:=IF(AND(D2>=60,D2<=79),1,0),H2:=IF(AND(D2>=80,D2<=89),1,0),I2:=IF(D2>=90,1,0)。最后选择单元格区域E2~I2,将其向下填充复制到区域E264~I264。

*基础分析

   

图1

   

 

图2
如何计算8个班的政治实考人数、最高分、最低分、平均分呢?
首先单击数据区域的任意单元格,选择菜单“数据→数据透视表和数据透视图”,三个步骤都保持默认,将看到一张空的数据透视表,把此工作表命名为“基础分析”。
接下来单击空表区域内的任意位置,将会出现放置不同字段类型的提示,并且显示“数据透视表字段列表”对话框,将“班级”字段拖入“将行字段拖到此处”中,将“政治”字段拖入“请将数据字段拖到此处”中。共需拖4次,因为要对政治实考人数、最高分、最低分、平均分4个项目进行统计分析(见图1)。
在B4单元格“求和项:政治”上右击,选择“字段设置”菜单项,在弹出的“数据透视表字段”对话框中,选择汇总方式为“计数”,确定。依次把“求和项:政治2”的汇总方式选择为“最大值”;把“求和项:政治3”的汇总方式选择为“最小值”;把“求和项:政治4”的汇总方式选择为“平均值”,单击“ ”按钮,弹出“单元格格式”对话框,根据需要设置单元格格式即可(这里保留2为小数)。
最后,拖动字段改变数据透视表布局,把“数据”字段拖到“汇总”字段上即可(见图2)。可以根据需要对第四行的字段进行必要的修改。

*深入分析
1.每个班的优、良、中、差、及格人数
刚才的分析还不够,小荷还要深入分析其他的指标。选中数据透视表所在的A1:E13单元格区域,单击菜单“编辑→复制”,切换到另一工作表内,在任意单元格右击,选择“粘贴”菜单项,即可得到一个复制的透视表,把“数据”字段拖到数据透视表区域外,并把此工作表命名为“深入分析”。把“60以下(差)”字段拖入“请将数据字段拖到此处”中,依次把字段“60分及以上(及格)”、“60-79(中等)”、“80-89(良好)”、“90分以上(优秀)”字段都拖入到“请将数据字段拖到此处”中。最后利用替换法把“求和项:”替换成一个空格,这样每一个班的“优、良、中、差、及格人数”就出来了。

2.每个班的差生率、及格率、中等率、良好率、优秀率

 

图3

     

图4
单击工作表“深入分析”内的数据透视表中的任意一个单元格,然后选择“数据透视表→公式→计算字段”,打开“插入计算字段”对话框,在插入计算字段”对话框的“名称”框内输入“差生率”,将光标定位到“公式”框中,清除原有的数据“=0”,在字段列表框中双击“60以下(差)”字段,输入“/”,再输入“(+)”,把鼠标定位到 “+”的前面,双击字段列表框中的“60以下(差)”字段,然后把鼠标定位到 “+”的后面,双击字段列表框中的“60分及以上(及格)”字段。此时“公式”框中显示“=’60以下(差)’/(’60以下(差)’+’60分及以上(及格)’)”(见图3,此公式的含义是不及格的人数/班级人数为“差生率”)。接下来依次创建及格率、中等率、良好率、优秀率等计算字段。根据需要设置“差生率”、“及格率”、“中等率”、“良好率”、“优秀率”的单元格格式(这里保留2为小数,百分比,见图4)。

3.以每隔5分统计各分数段

   

图5
从30分~100分每隔5分统计各班各分数段人数,类似高考的一分一段统计。
小荷根据上面的常规操作再创建一个空的数据透视表,并把此工作表命名为“分数段”。将“班级”字段拖入“将列字段拖到此处”中;将“政治”字段拖入“将行字段拖到此处”中;再将“政治”字段拖入“请将数据字段拖到此处”中。
然后她在B4单元格“求和项:政治”上单击鼠标右键,选择“字段设置”菜单项,在弹出的“数据透视表字段”对话框中,选择汇总方式为“计数”,单击“确定”按钮。
接下来选中在“政治”字段的任意一个分数,右击,选择“组及显示明细数据→组合”菜单项,在“起始于”编辑框中输入“30”,在“终止于”编辑框中输入“100”,然后设置“步长”为“5”,单击“确定”按钮关闭对话框(见图5)。

为了检验教学质量,校长决定增加月考次数,每次都要对成绩进行统计分析。小荷笑着说:“没问题!我用透视表快速汇总大量数据,并随时选择页、行、列中的不同的字段,快速查看数据的不同统计结果,和函数结合使用简直太省力了!其他哪位老师需要我帮忙统计吗?”

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
赞(0) 打赏
分享到: 更多 (0)

轻博客

联系我们联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏