数据统计之数据透视表的全面应用

1、数据统计之数据透视表的应用梁昌敬 2014年3月21日培训的目的培训的目的使参加培训的同事能够充分地了解统计工作在企业管理中的作用及做好统计工作的重要性，通过培训使工厂管理人员和专职统计人员提高知识水平和专业技术水平，学会统计方法之一，以便学以致用，让统计工作更好地在企业管理中发挥作用。返回2022-4-173目录返回2022-4-174 人类对事物数量的认识形成的定义。 汉语中的“统计”有合计、总计的意思。 指对某一现象有关的数据的搜集、整理、计算、分析、解释、表述 等的活动。统计这个词包括三个含义：统计工作、统计资料和统计学。这三者之间存在着密切的联系，统计资料是统计工作的成果，统计学来源

2、于统计工作。认知统计定义、含义返回1、按照生产部下达的工作任务，进行各项基础数据的统计、归纳、整理等工作。 2、为生产车间提供准确及时的生产进度数据，以便车间的生产安排。 3、及时向生产管理部反映车间的问题及需要，以利生产决策。4、按成本管理要求向生产管理部及时报送所需的成本统计资料：产量、工时投入和物料耗用相关数据。2022-4-175职责职责：返回1、统计生产进度，包括：订单的进度、各车间各工序进度，产品进度等。2、生产成本统计，包括完工产量、工时人工投入、材料耗用、废品损失、返工返修成本等。2022-4-176内容内容：生产统计工作内容主要分为两个方面：进度统计和成本统计。返回1、日报表

3、内容的起止时间：跟随车间作业班次调整2、报表上报的时间： 日报表每天早上10点以前； 月末在制品盘点表、月完工产品产值汇总表、各车间工时、考勤产能统计表、物料耗用统计表次月2号下午下班前；3、其它报表按生产部要求填写上报。2022-4-177报表报表：统计报表分为：统计日报表和统计月报表（统计汇总报表）有关报表事项的规定：返回数据透视表是交互式报表可快速合并和比较大量数据可旋转其行和列以看到源数据的不同汇总所进行的计算与数据在数据透视表中的排列有关可显示感兴趣区域的明细数据。2022-4-178关于透视什么是数据透视表：l 数据透视表是用来从EXCEL数据清单、关系数据库文件(Access数据

4、库、SQL Server数据库、文本文件、网站数据)或OLAP多维数据集中的特殊字段中总结信息的分析工具。l 创建数据透视表时，可指定所需的字段、数据透视表的组织形式和要执行的计算类型。l 建好数据透视表后，可对数据透视表重新安排，以便从不同的角度查看数据。数据透视表的名字来源于它具有“透视”表格的能力，例如将列首的标题转到行首。使其成为非常强大的工具。返回2022-4-179数据透视表可以做什么1、数据透视表可以解决利用函数公式对超大容量的数据库进行数据统计带来的速度瓶颈。2、数据透视表可以通过行、列和页字段的转换进行多角度的数据分析。3、数据透视表通过对字段的筛选可以对重点关注的内容进行专

5、题分析。4、数据透视表可以不同的工作表和工作簿提取数据,甚至不用打开数据源文件。5、数据透视表可以生成动态报表,保持与数据源的实时更新。6、数据透视表可以通过添加计算字段或计算项进行差异分析。7、数据透视表可以随时调用相关字段的数据源明细数据。8、数据透视图可以自动生成动态分析图表关于透视返回2022-4-1710什么情况不适用数据透视表1、数据源中首行的标题字段空缺或出现合并的标题。 -创建数据透视表后会出现空白字段创建数据透视表后会出现空白字段2、每列数据的中数据类型不一致。 -创建数据透视表后只按一种数据类型分类汇总创建数据透视表后只按一种数据类型分类汇总, ,会出现数据丢失会出现数据丢

6、失3、数据源中出现数据断行。 -创建数据透视表后会出现数据丢失创建数据透视表后会出现数据丢失4、数据源中有合并的单元格。 -创建数据透视表后会出现数据丢失创建数据透视表后会出现数据丢失5、数据源中有空白的单元格。 -创建数据透视表后会出现对数值的默认计数创建数据透视表后会出现对数值的默认计数关于透视返回2022-4-1711透视表结构页面区域页面区域列区域列区域数据区域数据区域行区域行区域返回2022-4-1712行区域行区域行区域数据透视表中最左数据透视表中最左面的标题，在数据面的标题，在数据透视表中被称为行透视表中被称为行区域，区域，对应对应【数据透视表数据透视表字段列字段列】表中表中【添

7、添加到加到】下拉内容下拉内容“行区域行区域”。单击行字段的下拉单击行字段的下拉按钮可以查看各个按钮可以查看各个字段项，可以全部字段项，可以全部选择也可以选择其选择也可以选择其中的几个字段项在中的几个字段项在数据透视表中显示数据透视表中显示。数据透视表中最左数据透视表中最左面的标题，在数据面的标题，在数据透视表中被称为行透视表中被称为行区域，区域，对应对应【数据透视表数据透视表字段列字段列】表中表中【添添加到加到】下拉内容下拉内容“行区域行区域”。右击行字段标题可右击行字段标题可以进行字段设置，以进行字段设置，对行字段进行美化对行字段进行美化工作工作返回2022-4-1713列区域列区域列区域数

8、据透视表中最上数据透视表中最上面的标题，在数据面的标题，在数据透视表中被称为列透视表中被称为列区域，对应区域，对应【数据数据透视表字段列透视表字段列】表表中中【添加到添加到】下拉下拉内容内容“列区域列区域”。单击列字段的下拉单击列字段的下拉按钮可以查看各个按钮可以查看各个字段项，可以全部字段项，可以全部选择也可以选择其选择也可以选择其中的几个字段项在中的几个字段项在数据透视表中显示。数据透视表中显示。数据透视表中最上数据透视表中最上面的标题，在数据面的标题，在数据透视表中被称为列透视表中被称为列区域，对应区域，对应【数据数据透视表字段列透视表字段列】表表中中【添加到添加到】下拉下拉内容内容“列

9、区域列区域”。右击列字段右击列字段标题可标题可以进行字段设置，以进行字段设置，对列字段进行美化对列字段进行美化工作工作返回2022-4-1714页面区域页面区域页面区域数据透视表中最上数据透视表中最上面的标题，在数据面的标题，在数据透视表中被称为页透视表中被称为页字段字段(页面区域页面区域)，对应对应【数据透视表数据透视表字段列字段列】表中表中【添添加到加到】下拉内容下拉内容“页面区域页面区域”。单击页字段的下拉单击页字段的下拉按钮勾选按钮勾选【选择多选择多项项】，可以全部选，可以全部选择也可以选择其中择也可以选择其中的几个字段项在数的几个字段项在数据透视表中显示。据透视表中显示。返回2022

10、-4-1715数据区域数据区域数据区域数据透视表中的数字区域，数据透视表中的数字区域，执行计算，在数据透视表执行计算，在数据透视表中被称为值字段中被称为值字段(数据区域数据区域)对应对应【数据透视表字段列数据透视表字段列】表中表中【添加到添加到】下拉内容下拉内容“数据区域数据区域”。默认数值显示默认数值显示求和项求和项:，文本显示为文本显示为计数项计数项:，鼠，鼠标右击标右击求和项求和项:可以对值可以对值字段进行设置求和、计数字段进行设置求和、计数或其他或其他;可以将值字段多次可以将值字段多次放入数据区域来求得同一放入数据区域来求得同一字段的不同显示结果。字段的不同显示结果。返回2022-4-

11、1716创建透视表选择数据源区域一个单元格选择数据源区域一个单元格单击菜单栏单击菜单栏-”数据数据”选择选择”数据透视表和透视图数据透视表和透视图”默认默认”下一步下一步”直至直至”完成完成”返回2022-4-1717美化透视表数据透视表的美化教材是基于数据透视表的美化教材是基于excel 2010下作的，具体讲解同下作的，具体讲解同excel2003大同小异。大同小异。返回2022-4-1718美化透视表返回2022-4-1719美化透视表4.字段与项的重命名字段与项的重命名可以修改数据透视表默认字段和项的名称。可以修改数据透视表默认字段和项的名称。单击待修改的字段单击待修改的字段,直接进行

12、修改直接进行修改,完成后回车。完成后回车。如果待修改的字段很多且有相同部分，可以采用如果待修改的字段很多且有相同部分，可以采用替换的方法统一修改替换的方法统一修改。如。如,将将求和项求和项:或或计数项计数项:去掉。去掉。返回2022-4-1720美化透视表5.改变数据透视表中的错误值显示改变数据透视表中的错误值显示当用户创建数据透视表的时候当用户创建数据透视表的时候,由于数据源中存在错误值或插入的计算字段和计算项由于数据源中存在错误值或插入的计算字段和计算项计算错误以及自定义字段显示错误计算错误以及自定义字段显示错误,往往会出现错误值。往往会出现错误值。返回2022-4-1721美化透视表数据

13、组合与取消组合数据组合与取消组合 数据透视表通过对数字、日期、文本等不同数据类型的数据项采取多种组合方式，大大增强了数据透视表分类汇总的适应性。返回2022-4-1722美化透视表数据透视表的计算字段和计算项数据透视表的计算字段和计算项1、在数据透视表中插入计算字段计算字段是通过对数据透视表中现有的字段执行计算后得到的新字段。返回2022-4-1723美化透视表数据透视表的计算字段和计算项数据透视表的计算字段和计算项2、在数据透视表中插入计算项计算项是在数据透视的现有字段中插入新的项，通过对该字段的其他项执行计算后得到该项的值。返回2022-4-1724动态透视创建动态透视表创建动态透视表创建

14、数据透视表创建后，如果数据源增加了新的行或列，即使刷新数据透视表，新增的数据仍无法出现在数据透视表中。为了避免这种情况的发生，可以为数据源定义名称或使用数据列表功能来获得动态的数据源，从而来创建动态的数据透视表。1、 定义名称法创建动态的数据透视表定义名称法创建动态的数据透视表首先要使用一个动态的公式定义数据透视表的数据源。当一个新的记录添加到表格中时，数据源会自动扩展，然后将定义的名称范围用于数据透视表，从而创建动态的数据透视表。=OFFSET(数据源数据源4!$A$1,COUNTA(数据源数据源4!$A:$A),COUNTA(数据源数据源4!$1:$1)返回2022-4-1725动态透视2

15、、使用表功能创建动态的数据透视表、使用表功能创建动态的数据透视表利用表的自动扩展特性也可以创建动态的数据透视表。利用表的自动扩展特性也可以创建动态的数据透视表。返回2022-4-1726练习一、求某车间日加工数据明细表一、求某车间日加工数据明细表对对”磅码磅码140310.XLS”表格数据进行表格数据进行数据透视数据透视，达到如下要求：，达到如下要求： 1、要求、要求” 加工车间加工车间”在在页面区域页面区域 ； “车次车次”在在列区域列区域 ；“批号、细类名称、代码、批号、细类名称、代码、 名名称、生产日期称、生产日期”在行区域在行区域； “ 件数件数 只数只数 重量重量”在在数据区域数据区

16、域 2、把数据区域的字段、把数据区域的字段”求和项：求和项：”取消取消，并把行区域的字段的，并把行区域的字段的分类汇总改为分类汇总改为“无无”； 3、添加标题添加标题“XX车间车间3月月10日加工数据明细表日加工数据明细表”；当筛选加工车间为；当筛选加工车间为“光鸡车间光鸡车间”时，时，标题自动变更标题自动变更为为”鸡加工鸡加工3月月10日加工数据明细表日加工数据明细表” 4、以个人的意愿把透视表完美展现，、以个人的意愿把透视表完美展现，(注：最终的结果这个表必须是透视表注：最终的结果这个表必须是透视表)返回2022-4-1727练习二、求某车间日加工数据明细表二、求某车间日加工数据明细表对对

17、”仓库调拨数据仓库调拨数据140322.xls”表格数据进行表格数据进行数据透视数据透视，达到如下要求：，达到如下要求： 1、要求、要求” 日期、调出仓库日期、调出仓库”在在页面区域页面区域 ； “调入仓库调入仓库”在在列区域列区域 ；“存货长代码、存存货长代码、存货名称、生产日期货名称、生产日期”在行区域在行区域； “ 件数、数量、重量件数、数量、重量”在在数据区域数据区域 2、把数据区域的字段、把数据区域的字段”求和项：求和项：”取消取消，并把行区域的字段的，并把行区域的字段的分类汇总改为分类汇总改为“无无”； 3、添加标题添加标题“=B2&A3&B3&”产品明细表产品明细表”；当筛选调出仓库为；当筛选调出仓库为“成品库成品库(鲜鲜)”时，时，标题自动变更标题自动变更为为”2014-03-22调