数据透视表数据透视图和合并计算1(应用2).pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2015/4/14,#,Excel 2010,应用,2,张桂香,数据,透视表和数据透视图,合并计算,主要内容,单表,数据源创建数据,透视表和数据透视图,多表创建动态数据透视表,多表创建,单页字段,数据透视表,多表创建,自定义页字段,数据透视表,利用数据透视表快速统计重复项目,数据,透视表和数据透视图,4,Excel,的数据透视表是一种可以轻松的对数据进行交叉分析的三维表格。它的特点在于表格的不固定性，可以随时根据实际需要进行调整，从而得到不同的视图。它可以将数据的排序、筛选和分类汇总,3,个过程结合在一起，可以转换行和列查看数据的不同汇总结果，可以显示不同页面来筛选数据，还可以根据需要显示指定区域的明细数据，非常便于用户在一个清单中重新组织和统计数据。,数据透视表和数据透视图,数据透视表和数据透视图,制作步骤分三步：,1.,选择数据源类型,2.,选择数据源区域,3.,指定数据透视表位置,一、,单表数据源做数据透视表和数据透视图,6,一、,单表数据源做数据透视表和数据透视图,选择要分析的数据区域,一、,单表数据源做数据透视表和数据透视图,一、,单表数据源做数据透视表和数据透视图,一、,单表数据源做数据透视表和数据透视图,改为“订货日期”,改为“城市”,关闭数据透视表字段列表,11,一、,单表数据源做数据透视表和数据透视图,12,(,创建组,),右键单击,一、,单表数据源做数据透视表和数据透视图,13,一、,单表数据源做数据透视表和数据透视图,(,创建组,),一、,单表数据源做数据透视表和数据透视图,筛选数据,筛选数据,一、,单表数据源做数据透视表和数据透视图,美化表格,16,一、,单表数据源做数据透视表和数据透视图,增加列,一、,单表数据源做数据透视表和数据透视图,增加列,一、,单表数据源做数据透视表和数据透视图,改变报表布局,一、,单表数据源做数据透视表和数据透视图,20,一、,单表数据源做数据透视表和数据透视图,数据筛选页,数据筛选页的效果,一、,单表数据源做数据透视表和数据透视图,数据筛选页,一、,单表数据源做数据透视表和数据透视图,数据筛选页,23,一、,单表数据源做数据透视表和数据透视图,数据透视图,一、,单表数据源做数据透视表和数据透视图,数据透视图,可以筛选地区,可筛选年份,小结：,该案例讲解了利用数据透视表和数据透视图从产品的销售明细中得到了各地区各季度各年份的销售情况，可以灵活立体三维分析数据。,二、多,表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,单击“列标签”，选择“金额”和“数量”，如图：,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,二、多表创建,单页字段,数据,透视,表,三、多表,创建,自定义,页,字段,数据透视,表,以上题的数据源为例讲解,三、多,表,创建,自定义,页,字段,数据透视,表,页,字段别忘了，要和上面配套哦,三、多,表,创建,自定义,页,字段,数据透视,表,三、多,表,创建,自定义,页,字段,数据透视,表,三、多,表,创建,自定义,页,字段,数据透视,表,三、多,表,创建,自定义,页,字段,数据透视,表,三、多,表,创建,自定义,页,字段,数据透视,表,多,表,创建数据,透视,表,创建多重合并计算数据区域的数据透视表：数据源可以是同一个工作簿的多张工作表，也可以是其他工作簿的多张工作表，,但待合并的数据源工作表结构必须完全一致，,当数据内容更改时，可以刷新数据透视表来更新数据透视表，但当数据源的数据量更改时则不可以更新数据透视表。,怎么创建动态的数据透视表呢：即记录量增减时，能自动更新数据,透视,表,？,45,四,、多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,50,Offset,函数,以,指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。,语法：,OFFSET(reference,rows,cols,height,width),Reference,作为偏移量,参照系,的引用区域。,Reference,必须为对单元格或相连单元格区域的引用；否则，函数,OFFSET,返回错误值,#VALUE!,。,Rows,相对于偏移量参照系的左上角单元格，上（下）,偏移的行数,。如果使用,5,作为参数,Rows,，则说明目标引用区域的左上角单元格比,reference,低,5,行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。,Cols,相对于偏移量参照系的左上角单元格，左（右）,偏移的列数,。如果使用,5,作为参数,Cols,，则说明目标引用区域的左上角的单元格比,reference,靠右,5,列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。,Height,高度，即所要返回的,引用区域的行数,。,Height,必须为正数。,Width,宽度，即所要返回的,引用区域的列数,。,Width,必须为正数。,四、,多,表,创建动态数据,透视表,51,函数：,sum(offset(b3,2,1,3,2),等价于,sum(c5:d7,),、,sum(11,13,15,15,18,21),等价,四、,多,表,创建动态数据,透视表,52,四、,多,表,创建动态数据,透视表,定义数据源名称,=OFFSET(,河东区,!$A$1,COUNTA(,河东区,!$A:$A),COUNTA(,河东区,!$1:$1),四、,多,表,创建动态数据,透视表,参照位置,无偏移量,所有行,所有列,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,56,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,在选定区域框中输入“河东区”，并单击“添加”按钮，按同样的方法将四个区域添加到所有区域下拉列表中。并选中“,1,”单选按钮。,添加数据源,四、,多,表,创建动态数据,透视表,选中“北辰区“，在“字段,1,”文本框中,输入“北辰区”,，再选中“河西区”，在“字段,1,”文本框中输入“河西区”，按同样的方法把别的区域做完,给页字段命名,类似上题的筛选项哦,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,61,四、,多,表,创建动态数据,透视表,右键单击,生成数据透视表,62,四、,多,表,创建动态数据,透视表,同样方法修改“行标签”为“品名”,修改“页,1,”为“地区”,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,组合数据,选中电器类产品,四、,多,表,创建动态数据,透视表,组合数据,选中，右键单击,66,四、,多,表,创建动态数据,透视表,组合数据,四、,多,表,创建动态数据,透视表,组合数据,按同样方法分组,四、,多,表,创建动态数据,透视表,单击【数据透视表工具】,|,【设计】,|,【报表布局】,|,【以表格形式显示】,。,更改报表布局,四、,多,表,创建动态数据,透视表,将“品名,2,”改名为“类别”，在分类汇总下拉列表框中选择“求和”，然后更改单元格“数据组,1,”的名字为“电器”，同样更改“”数据组,2,为“其它”等等,选中电器组合项下的任一单元格，如,A6,，单击鼠标右键，弹出快捷菜单，选单击“数据透视表选项”，打开“数据透视表选项”对话框，“布局和格式”选项卡中的“布局”，勾选“合并且居中排列带标签的单元格”。,更改部分名称，符合常态理解,四、,多,表,创建动态数据,透视表,单击右键,选择显示列字段,四、,多,表,创建动态数据,透视表,四、,多,表,创建动态数据,透视表,美 化 表 格,使用定义名称的方法创建的数据透视表是动态的，即在,数据,源表中可增减记录。但是要求数据源区域中的首行和首列不能有空白单元格，否则将无法用定义名称取得正确的区域。,73,数据透视表的排序,在需要排序的列的任意非字段单元格上单击右键，在弹出的快捷菜单中选择,【,排序,/,升,序,】,如下图。,四、,多表创建动态数据透视表,数据透视表的刷新,方法,1,：在数据透视表的任意单元格上单击鼠标右键，在弹出的快捷菜单中单击,【,刷新,】,。如下图。,方法,2,：在,【,数据,】,选项卡中单击,【,全部刷新,】,。,四、,多表创建动态数据透视表,数据透视表的刷新,方法,3,，打开工作簿时刷新，右键单击数据透视表的任意单元格，弹出如下快捷菜单，选择“数据透视表选项”。,选择此项,五、使用,数据透视表快速统计重复项目,使用,Excel,函数公式可以统计数据列表中某个字段的重复次数，但更为快捷的方式是使用数据透视,表,。,统计,各,组,的,人数,五、使用数据透视表快速统计重复项目,五、利用,数据透视表快速统计重复项目,插入切片器,选择数据透视表工具下的选项，点击插入切片器,在弹出的“插入切片器”对话框中勾选,“姓名”和,“组别”,选项,。单击“确定”按钮。,79,五、利用,数据透视表快速统计重复项目,关闭切片器,单击,清除筛选,器按钮或使用,Alt+C,键即可关闭切片器。,如果要删除切片器，选择某个切片器，按,Delete,键即可。,80,五、利用,数据透视表快速统计重复项目,五、利用,数据透视表快速统计重复项目,在数据透视表中添加计算项,数据透视表提供了强大的自动汇总功能，除了“求和”、“计数”、“求平均”、“方差”等多种汇总方式。,如不能满足需求还可以在数据透视表中添加计算项的方法达到目的。,在数据透视表中添加计算项,在数据透视表中添加计算项,选中数据透视表的“列标签”单元格，在,【,数据透视表工具,】,的,【,选项,/,域、项目和集,】,中选择“计算项”，如图：,85,更改,数据透视表的数据源,当数据源中添加新的记录时，刷新数据透视表，新增的记录不会自动添加到数据透视表中，（当记录内容更改时，刷新纪录，能实现数据透视表的更新），此时可以更改数据透视表的数据源，使数据透视表的数据源包括新增记录。,更改,数据透视表的数据源,原数据,更改,数据透视表的数据源,原数据透视表,更改,数据透视表的数据源,更改数据源,更改,数据透视表的数据源,更改,数据透视表的数据源,重新选择数据源,更改,数据透视表的数据源,合并计算,利用合并计算创建分户报表,有选择的合并计算,多工作表筛选不重复的值,利用合并计算对数值型数据进行校对,利用合并计算对文本型数据进行校对,合并计算,93,Excel,中的“合并计算”功能可以汇总或合并多个数据源区域中的数据。合并计算的数据源区域可以是同一个工作表中的不同表格，也可以是同一工作簿中的不同工作表，还可以是不同,工作,簿,中,的表格。,求各城市的销售数量和金额,选中,A13,单元格，作为合并计算后结果的存放起始位置，再单击【数据,/,合并计算】命令按钮，打开【合并计算】对话框。,合并计算,结果,修饰后,注意：,在,使用按类别合并的功能时，数据源列表必须包含行或列标题，并且在【合并计算】对话框的【标签位置】组合框中勾选相应的复选框。,合并的结果表中包含行列标题，但在同时选中的【首行】和【最左列】复选项时，所生成的合并结果表会缺失第一列的标题。,合并后，结果表的数据项排列顺序是按第一个数据表的数据项顺序排列。,合并计算过程不能复制数据表的格式，如果设置结果表的格式，可以使用【格式刷】。,合并计算,利用合并计算创建分户报表,合并计算可以按类别进行合并，如果引用区域的行、列方向均包含了多个类别时，则可以利用合并计算功能将引用区域中的全部类别汇总到同一表格上并显示所有明细。,利用合并计算创建分户报表,有选择的合并计算,五月份销售情况,六月份销售情况,七月份销售情况,怎么按日期汇总销售数量和销售金额？,有选择的合并计算,在“汇总”工作表的,A1:C1,单元格区域，分别输入所需汇总的列字段名称：“日期”，“销售数量”和“销售金额”，,然后选中,A1:C1,单元格区域，这是最关键的步骤,。,单击【数据,/,合并计算】命令，打开【合并计算】对话框。,有选择的合并计算,有选择的合并计算,修改为日期格式,有选择的合并计算,修改数据透视表后的样子,做数据透视表后的样子,有选择的合并计算,多工作表筛选不重复的值,多工作表筛选不重复的值,注意：,合并计算的按类别求和功能不能对不包含任何数值的数据区域进行合并操作，但只要选择合并的区域内包含一个数字即可进行合并，利用这一特性，在表,1,的,B2,单元格输入,0,，进行合并即可,添加辅助计算列,多工作表筛选不重复的值,多工作表筛选不重复的值,合并后的结果,利用合并计算对数值型数据进行校对,利用合并计算对数值型数据进行校对,利用合并计算对数值型数据进行校对,不一样的数据,利用合并计算对数值型数据进行校对,利用合并计算对文本型数据进行校对,添加辅助列，如下图：,利用合并计算对文本型数据进行校对,筛选后,利用合并计算对文本型数据进行校对,休息！,