大家好,欢迎回来。
本课跟大家分享的是EXCEL中一个非常实用的数据统计工具——数据透视表,有心的使用者还专门地为数据透视表编写出一本书,对数据透视表进行了深入浅出的分析。
但是小编要说的是,数据透视表绝对不是复杂得让人难以学习,其实,它是简单的,而功能强大的实用工具。
不用函数,也不用写VBA,拖拉几下鼠标,就可得到数据统计中想要的结果。
下面小编通过统计一个学生成绩表,分析数据透视表的一些基本操作,看看这个数据透视表,有哪些神奇之处。
【学习内容】1、什么是数据透视表;
2、数据透视表的基本操作;
3、数据透视表的在实际需求的应用。
【学习目标】1、了解什么是数据透视表;
2、会利用数据透视表统计数据的能力;
3、具有按需求分析统计数据的能力。
【学习重难点】1、数据透视表的功能与操作;
2、按需求分析统计数据,生成需求的数据表。
【学习过程】1、数据透视表
(1)什么是数据透视表?
专业表述就是数据透视表是一种可以快速汇总、分析和处理大量数据的交互式工具。
注意的是它只是个工具。
(2)什么情况下可使用数据透视表?
主要有三个方面:一是当数据量比较大时;二是表格中的数据不断变化时;三是源数据与分析结果保持一致时;四是源数据要规范。
2、实例:统计分析学生成绩表
学生成绩统计,是教育部门最经常的教育数据分析之一,快速分析和统计学生成绩,是提高教育教学效率的关键工作环节。而EXCEL中的数据透视表,就是为这一工作而生。
成绩分析所需的数据,就是要统计各校、各班的平均分,最高分,最低分,合格人数, 优秀人数,高分人数,合格率,优秀率,高分率。
(1)打开从成绩统计系统中下载的成绩表,如图1所示,这是一个从数据库中导出来的表,一看就是个流水帐的形式,这是在管理系统中最常见的数据表结构。
(2)要使用数据透视表对进行统计,还需要先对表格进行简单的整理,为了示例展示方便,只做语文、数学和英语三个学科,如图2所示。
也就是说,用数据透视表进行统计,第一行必是标题行。
(3)插入数据透视表
选中数据表中的任意单元格,单击【插入】-【数据透视表】,在“创建数据透视表”对话框中(图3),表或区域已自动添加,选中新工作表。
单击【确定】,得到一个新的表,如图4所示。
(4)现在解决第一个问题,统计各校参考人数,计算各科平均分。
1)在右边的【数据透视表字段】,将“学校”拖放到下面的【行】处,将“姓名”、“语文”、“数学”、“英语”拖放到【值】处。也可以选中各字段前面的选择框,再调整,最后得到如图5的结果。
2)可以看到各学校的参考人数已经统计出来了,只是当前默认得到的是各学科的总分,要将其改变平均分,怎样操作?
双击左边表格的“求和项:语文”,弹出【值字段设置】对话框,如图6所示。
选择“平均值”,单击【确定】,就可以得到各校语文的平均分了,如图7所示。
同样的操作,将数学和英语都改为平均值,就可以得到各校各科的平均分,如图8。
3)现在有小伙伴们会问了,用透视表计算出来的平均分,对不对?
要检验对不对,其实很简单,双击中学1的语文平均分,就会弹出一个新的表,将中学1的数据全都筛选了出来,如图9所示,从状态栏中,可以知道语文的平均值。
4)现在的数值是算出来了,但是要拿这个表去展示给人家看,一串的数字,一下还是让人难看得清楚的,这个可以借助EXCEL的条件格式功能(下节课再讲),就可以很清晰地看到各间学校的平均分情况了,如图10所示。
5)计算平均分,到了这里是解决了,总结操作步骤,只是下面几步:
选中成绩表里的任一单元格,单击菜单栏中的【插入】-【数据透视表】,在数据透视表中,选择学校、姓名、语文、数学和英语字段,将姓名设置为“计数值”,其它的设置为“平均值”,再设置各数据的条件格式,就完成了。
(5)计算各校各班各科的平均分
将“班级”拖到【行】处,再按各科设置条件格式,就能得到下面的表,如图11所示。
(6)找出各科的最高分或最低分
最高分就是找最大值,最低分就是找最小值。
1)单击透视表的标题“最大值项:语文”,在值字段设置中,将计算类型设置为“最大值”,单击【确定】就可以了。同样的方式可以设置数学和英语学科,最后得到的如下的表(图12)。
2)同理,可得到各学科的最低分,如图13。
(7)同理,也可计算各科的标准偏差。
(8)表格的美化
选中左边数据表的任一单元格,单击【数据透视表工具】-【设计】,选择右下角的下拉三角形,可以得到表格的各种设计模板。
选择其中一个模板,如下图所示。
对各字段的名称修改为自己需要的名称,如下表所示,其格式设计,除了透视表独有的外,其它的格式设计同样适用于数据透视表格的设计。
【小结】数据透视表是EXCEL中处理规范化的,大数据量的一种快捷常规处理工具,在教育领域的学生成绩统计,其优势特别明显。
不用函数,更不用写代码,就可以快速统计和分析学生成绩数据。
本节课分享的只是数据透视表中比较基础的运用,其更多技巧所实用性操作,还需要进一步探究。
下节课,将会跟大家分享数据透视图表的制作,欢迎大家继续关注。
,