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