制作数据透视表课件.pptx
时间反复无常，鼓着翅膀飞逝,制作数据透视表,制作数据透视表时间反复无常，鼓着翅膀飞逝制作数据透视表Excel应用技术之提高篇-2数据透视表和数据透视图合并计算主要内容,Excel应用技术之提高篇-2,数据透视表和数据透视图合并计算,主要内容,单表数据源创建数据透视表和数据透视图,多表创建动态数据透视表,多表创建单页字段数据透视表,多表创建自定义页字段数据透视表,利用数据透视表快速统计重复项目,数据透视表和数据透视图,5,Excel的数据透视表是一种可以轻松的对数据进行交叉分析的三维表格。它的特点在于表格的不固定性，可以随时根据实际需要进行调整，从而得到不同的视图。它可以将数据的排序、筛选和分类汇总3个过程结合在一起，可以转换行和列查看数据的不同汇总结果，可以显示不同页面来筛选数据，还可以根据需要显示指定区域的明细数据，非常便于用户在一个清单中重新组织和统计数据。,数据透视表和数据透视图,数据透视表和数据透视图,制作步骤分三步：,一、 单表数据源做数据透视表和数据透视图,7,一、 单表数据源做数据透视表和数据透视图,选择要分析的数据区域,1.创建数据透视表,一、 单表数据源做数据透视表和数据透视图,一、 单表数据源做数据透视表和数据透视图,一、 单表数据源做数据透视表和数据透视图,改为“订货日期”,改为“城市”,关闭数据透视表字段列表,12,一、 单表数据源做数据透视表和数据透视图,13,2.创建组,右键单击,一、 单表数据源做数据透视表和数据透视图,14,一、 单表数据源做数据透视表和数据透视图,2.创建组,一、 单表数据源做数据透视表和数据透视图,3.筛选数据(1),筛选数据,一、 单表数据源做数据透视表和数据透视图,3.筛选数据(2),筛选数据,一、 单表数据源做数据透视表和数据透视图,3.筛选数据(3),筛选数据,一、 单表数据源做数据透视表和数据透视图,4.美化表格,19,一、 单表数据源做数据透视表和数据透视图,5.增加列,一、 单表数据源做数据透视表和数据透视图,5.增加列,一、 单表数据源做数据透视表和数据透视图,6.改变报表布局,一、 单表数据源做数据透视表和数据透视图,一、 单表数据源做数据透视表和数据透视图,24,一、 单表数据源做数据透视表和数据透视图,7.创建数据筛选页,数据筛选页的效果,一、 单表数据源做数据透视表和数据透视图,7.创建数据筛选页,一、 单表数据源做数据透视表和数据透视图,7.创建数据筛选页,27,一、 单表数据源做数据透视表和数据透视图,8.创建数据透视图,一、 单表数据源做数据透视表和数据透视图,8.创建数据透视图,可以筛选地区,可筛选年份,小结： 该案例讲解了利用数据透视表和数据透视图从产品的销售明细中得到了各地区各季度各年份的销售情况，可以灵活立体三维分析数据。,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,首先，在功能区上鼠标右键，选择【自定义快速访问工具栏】。,添加数据透视表和数据透视图向导：首先，在功能区上鼠标右键，选择【自定义快速访问工具栏】。,Alt+D+P,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,单击“列标签”，选择“金额”和“数量”，如图：,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,二、多表创建单页字段数据透视表,三、多表创建自定义页字段数据透视表,以上题的数据源为例讲解,页字段:在数据透视表或数据透视图报表中指定为 页方向的字段,三、多表创建自定义页字段数据透视表,页字段别忘了，要和上面配套哦,三、多表创建自定义页字段数据透视表,三、多表创建自定义页字段数据透视表,三、多表创建自定义页字段数据透视表,三、多表创建自定义页字段数据透视表,三、多表创建自定义页字段数据透视表,三、多表创建自定义页字段数据透视表,多表创建数据透视表,创建多重合并计算数据区域的数据透视表：数据源可以是同一个工作簿的多张工作表，也可以是其他工作簿的多张工作表，但待合并的数据源工作表结构必须完全一致，当数据内容更改时，可以刷新数据透视表来更新数据透视表，但当数据源的数据量更改时则不可以更新数据透视表。,怎么创建动态的数据透视表呢：即记录量增减时，能自动更新建数据透视表,？,51,四、多表创建动态数据透视表,四、多表创建动态数据透视表,四、多表创建动态数据透视表,四、多表创建动态数据透视表,四、多表创建动态数据透视表,OFFSET(),格式： OFFSET(引用,行位移,列位移,行数,列数)功能：以指定的引用为参照系，通过给定偏移量得到新的引用。“行位移,列位移,行数,列数”均采用整数截断。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。,例如：在A1中输入公式 =SUM(OFFSET(A2,1,2,5,3) 得到的结果是区域$C$3:$E$7 的和。,56/146,四、多表创建动态数据透视表,57,函数：sum(offset(b3,2,1,3,2)、sum(c5:d7)、sum(11,13,15,15,18,21)等价,四、多表创建动态数据透视表,58,四、多表创建动态数据透视表,1.定义数据源名称(公式/定义的名称/名称管理器),=OFFSET(河东区!$A$1,COUNTA(河东区!$A:$A),COUNTA(河东区!$1:$1),四、多表创建动态数据透视表,参照位置,所有行,所有列,COUNTA($A:$A)统计A列有数据的行数,COUNTA($1:$1)统计第1行有数据的列数。,四、多表创建动态数据透视表,1.定义数据源名称(公式/定义的名称/名称管理器),四、多表创建动态数据透视表,2.打开数据表和数据透视图向导视图,62,四、多表创建动态数据透视表,2.打开数据表和数据透视图向导视图,四、多表创建动态数据透视表,在选定区域框中输入“河东区”（即第一步中定义的名称），并单击“添加”按钮，按同样的方法将四个区域添加到所有区域下拉列表中。 并选中页字段数目“1”单选按钮。,3.添加数据源,四、多表创建动态数据透视表,选中“北辰区”，在“字段1”文本框中输入“北辰区”，再选中“河西区”，在“字段1”文本框中输入“河西区”，按同样的方法把别的区域做完,4.给页字段命名,类似上题的筛选项哦,四、多表创建动态数据透视表,四、多表创建动态数据透视表,67,四、多表创建动态数据透视表,右键单击,5.生成数据透视表,68,四、多表创建动态数据透视表,选择任意一个第一列上数据。同样方法修改“行标签”为“品名”,修改“页1”为“地区”,四、多表创建动态数据透视表,四、多表创建动态数据透视表,6.组合数据,选中电器类产品,四、多表创建动态数据透视表,6.组合数据,选中，右键单击,72,四、多表创建动态数据透视表,组合数据,四、多表创建动态数据透视表,6.组合数据,按同样方法分组,四、多表创建动态数据透视表,单击【数据透视表工具】|【设计】|【报表布局】|【以表格形式显示】。,7. 更改报表布局,四、多表创建动态数据透视表,将“品名2”改名为“类别”，在分类汇总下拉列表框中选择“求和”，然后更改单元格“数据组1”的名字为“电器”，同样更改“”数据组2为“其它”等等,8.更改部分名称，符合常态理解,选中电器组合项下的任一单元格，如A6，单击鼠标右键，弹出快捷菜单，选单击“数据透视表选项”，打开“数据透视表选项”对话框，“布局和格式”选项卡中的“布局”，勾选“合并且居中排列带标签的单元格”。,四、多表创建动态数据透视表,单击右键,9.选择显示列字段,四、多表创建动态数据透视表,四、多表创建动态数据透视表,使用定义名称的方法创建的数据透视表是动态的，即在数据源表中可增减记录。但是要求数据源区域中的首行和首列不能有空白单元格，否则将无法用定义名称取得正确的区域。数据有变化时，选择透视表区域，单击右键，选择“刷新”,10.美 化 表 格,四、多表创建动态数据透视表,使用定义名称的方法创建的数据透视表是动态的，即在数据源表中可增减记录。但是要求数据源区域中的首行和首列不能有空白单元格，否则将无法用定义名称取得正确的区域。 数据有变化时，选择透视表区域，单击右键，选择“刷新”,数据透视表的刷新,方法1：在数据透视表的任意单元格上单击鼠标右键，在弹出的快捷菜单中单击【刷新】。如下图。,方法2：在【数据】选项卡中单击【全部刷新】。,四、多表创建动态数据透视表,数据透视表的刷新,方法3，打开工作簿时刷新，右键单击数据透视表的任意单元格，弹出如下快捷菜单，选择“数据透视表选项”。,选择此项,82,数据透视表的排序,在需要排序的列的任意非字段单元格上单击右键，在弹出的快捷菜单中选择【排序/升序】,如下图。,四、多表创建动态数据透视表,五、使用数据透视表快速统计重复项目,使用Excel函数公式可以统计数据列表中某个字段的重复次数，但更为快捷的方式是使用数据透视表。,统计各部门的人数,五、使用数据透视表快速统计重复项目,五、利用数据透视表快速统计重复项目,插入切片器,选择数据透视表工具下的选项，点击插入切片器 在弹出的“插入切片器”对话框中勾选“姓名”和“组别” 选项。单击“确定”按钮。,86,五、利用数据透视表快速统计重复项目,关闭切片器,单击清除筛选器按钮或使用Alt+C键即可关闭切片器。 如果要删除切片器，选择某个切片器，按Delete键即可。,87,五、利用数据透视表快速统计重复项目,五、利用数据透视表快速统计重复项目,六、在数据透视表中添加计算项,数据透视表提供了强大的自动汇总功能，除了“求和”、“计数”、“求平均”、“方差”等多种汇总方式。,如不能满足需求还可以在数据透视表中添加计算项的方法达到目的。,六、在数据透视表中添加计算项,六、在数据透视表中添加计算项,先选中数据透视表的“列标签”单元格，在【数据透视表工具】的【选项/域、项目和集】中选择“计算项”，如图：,92,七、更改数据透视表的数据源,当数据源中添加新的记录时，刷新数据透视表，新增的记录不会自动添加到数据透视表中，（当记录内容更改时，刷新纪录，能实现数据透视表的更新），此时可以更改数据透视表的数据源，使数据透视表的数据源包括新增记录。,七、更改数据透视表的数据源,原数据,七、更改数据透视表的数据源,原数据透视表,七、更改数据透视表的数据源,更改数据源,七、更改数据透视表的数据源,七、更改数据透视表的数据源,重新选择数据源,七、更改数据透视表的数据源,合并计算,利用合并计算创建分户报表,有选择的合并计算,多工作表筛选不重复的值,利用合并计算对数值型数据进行校对,利用合并计算对文本型数据进行校对,一、认识合并计算,100,Excel中的“合并计算”功能可以汇总或合并多个数据源区域中的数据。合并计算的数据源区域可以是同一个工作表中的不同表格，也可以是同一工作簿中的不同工作表，还可以是不同工作簿中的表格。,求各城市的销售数量和金额,选中某一单元格，作为合并计算后结果的存放起始位置，再单击【数据/合并计算】命令按钮，打开【合并计算】对话框。,一、认识合并计算,结果,演示,修饰后,注意：在使用按类别合并的功能时，数据源列表必须包含行或列标题，并且在【合并计算】对话框的【标签位置】组合框中勾选相应的复选框。合并的结果表中包含行列标题，但在同时选中的【首行】和【最左列】复选项时，所生成的合并结果表会缺失第一列的标题。合并后，结果表的数据项排列顺序是按第一个数据表的数据项顺序排列。合并计算过程不能复制数据表的格式，如果设置结果表的格式，可以使用【格式刷】。,一、认识合并计算,二、利用合并计算创建分户报表,合并计算可以按类别进行合并，如果引用区域的行、列方向均包含了多个类别时，则可以利用合并计算功能将引用区域中的全部类别汇总到同一表格上并显示所有明细。,二、利用合并计算创建分户报表,三、有选择的合并计算,五月份销售情况,六月份销售情况,七月份销售情况,怎么按日期汇总销售数量和销售金额？,三、有选择的合并计算,在需要进行“汇总”的工作表A1:C1单元格区域，分别输入所需汇总的列字段名称：“日期”，“销售数量”和“销售金额”，然后选中A1:C1单元格区域，这是最关键的步骤。单击【数据/合并计算】命令，打开【合并计算】对话框。,三、有选择的合并计算,三、有选择的合并计算,修改为日期格式,三、有选择的合并计算,做数据透视表后的样子,三、有选择的合并计算,修改数据透视表后的样子,三、有选择的合并计算,四、多工作表筛选不重复的值,四、多工作表筛选不重复的值,注意：合并计算的按类别求和功能，不能对不包含任何数值的数据区域进行合并操作，但只要选择合并的区域内包含一个数字即可进行合并，利用这一特性，在表1的B2单元格输入0，进行合并即可,添加辅助计算列,四、多工作表筛选不重复的值,四、多工作表筛选不重复的值,合并后的结果,五、利用合并计算对数值型数据进行校对,五、利用合并计算对数值型数据进行校对,五、利用合并计算对数值型数据进行校对,不一样的数据,五、利用合并计算对数值型数据进行校对,六、利用合并计算对文本型数据进行校对,添加辅助列，如下图：,六、利用合并计算对文本型数据进行校对,筛选后,六、利用合并计算对文本型数据进行校对,1、最灵繁的人也看不见自己的背脊。非洲2、最困难的事情就是认识自己。希腊3、有勇气承担命运这才是英雄好汉。黑塞4、与肝胆人共事，无字句处读书。周恩来5、阅读使人充实，会谈使人敏捷，写作使人精确。培根,
