1、第 13 章 活用数据透视表统计销售数据财会工作总是离不开数据的统计与计算。在实际工作中，常常会遇到需要同时统计多种数据，如产品类别、产品型号、销售数量、销售部门、销售月份、销售金额等数据。当产品类别、型号以用销售部门等分类较少时也许使用 Excel 静态表格来统计也能够满足用户需要，但一旦出现产品品种多，销售部门和人员多时，会计工作的统计量就会大大增加，仅靠单一静态表格来统计，不但工作量大，而且也不利于数据的多角度查看与分析。此时，就可以借助于 Excel 中的数据透视表，它可以灵活多变地完成从不同角度去统计与分析数据。数据透视表的特性可以说代表了 Excel 最具有技术性的成分之一，本章将
2、以销售数据的统计与分析为例，详细介绍 Excel 2016 中数据透视表的创建与使用。13.1 何为数据透视表数据透视表是一种交互式的表，这是它与普通表格相比最大的区别。数据透视表可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关，用户可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。13.1.1 基于工作簿的数据创建数据透视表在 Excel 2016 中，用户可以根据数据源区域创建一个空白的数据透视表模板，然后再根
3、据需要在模块中添加和更改字段位置来调整数据透视表的布局。请打示例文件：1 季度销售数据.xlsx 工作簿，接下来，笔者将带领大家一起来基于已有 Excel 表格创建数据透视表。步骤 01：单击“数据透视表”按钮。在“插入”选项卡中，单击“数据透视表”选项，如图 13-1 所示。图 13-1 单击“数据透视表”选项 步骤 02：“创建数据透视表”对话框。随后打开“创建数据透视表”对话框，保留选中默认的 “选择一个表或区域”单选按钮，单击“表/区域”框右侧的单元格引用按钮，如图 13-2 所示。步骤 03 选择数据区域。随后打开“创建数据透视表”对话框折叠显示，拖动鼠标选择“第一季度销售数据”工作
4、簿中的工作表 Sheet1 中的单元格区域 A1:H42，然后单击“单元格引用按钮”展开对话框，如图 13-3 所示。13-2 选择一个表或区域图 13-3 选择数据区域 步骤 04：选择放置数据透视表的位置并为数据添加数据模型。在“选择放置数据透视表的位置”区域单击选中“现有工作表”单选按钮，选择“位置”为 Sheet1 工作表中的单元格 K22，勾选“将此数据添加到数据模型“单选框 然后单击“确定”按钮，如图 13-4所示。图 13-4 选择放置数据透视表的位置步骤 05：添加字段。拖动“年月”字段到“报表筛选”区域， “销售部门”到“行标签”区域， “销售金额”到“ 数值”区域，得到如图
5、 13-5 所示的数据透视表。图 13-5 添加字段13.1.2 创建数据透视表的同时创建数据透视图如果用户希望在创建数据透视表的同时还要创建数据透视图，则还可以通过 Excel 2016 自带的同时创建数据透视表和数据透视图选项进行添加。步骤 01：打开实例文件“1 季度销售数据.xlsx”,在“数据透视图”下拉框中单击“数据透视图和数据透视表”按钮，Excel 会同时在工作表中创建数据透视表模板和数据透视图模板，如图 13-6 所示。图 13-6 数据透视表和数据透视图步骤 02：选择数据区域。随后打开“创建数据透视表”对话框折叠显示，拖动鼠标选择“第一季度销售数据”工作簿中的工作表 Sh
6、eet1 中的单元格区域 A1:H42，选择放置数据透视表的位置为“新工作表” ，点击“确定”按钮，创建图表，如图 13-7 所示。图 13-7 创建数据透视表步骤 03：拖动“年月”字段到“报表筛选”区域， “销售部门”到“行标签”区域，“销售金额”到“ 数值”区域，如图 13-9 所示，点击关闭符号按钮，得到如图 13-10所示的数据透视表和数据透视图。图 13-9 数据透视图字段图 13-10 数据透视图和数据透视表13.1.3 使用外部数据源创建数据透视表在 Excel 2016 中，用户可以从 Windows 系统的数据源中导入外部数据创建数据透视表，常见的可用于创建数据透视表的外部
7、数据格式有数据库文件。在创建数据透视表之前，用户需要将源文件保存在我的文档我的数据源目录下，步骤 01：添加数据源。请将销售记录.accdb 数据库文件另存到“我的文档”文件夹下的“我的数据源”文件夹中，如图 13-11 所示。图 13-11 将文件存入指定位置步骤 02：选择要分析的数据。新建一个工作簿，打开“创建数据透视表”对话框，在“请选择要分析的数据”区域单击选中“使用外部数据”单选按钮，然后单击“选择连接”按钮，如图 13-12 所示。步骤 03：选择连接数据。随后打开“现有连接”对话框， “销售记录”文件会显示在该列表框中，选中文件后，单击“打开”按钮，如图 13-13 所示。图
8、13-12“创建数据透视表 ”对话框 图 13-13 选择连接数据 步骤 04：单击“确定”按钮。返回“创建数据透视表”对话框中，单击“确定”按钮，Excel 会在工作簿中指定的位置创建数据透视表模板，如图 13-14 所示。图 13-14 创建数据透视模板步骤 05：添加字段。在“数据透视表字段列表”窗格中，拖动“产品类别”字段到“行标签”区域，拖动“销售金额 1”字段到“数值”区域，如图 13-15 所示。步骤 06：数据透视表效果。最后得到的数据透视表效果如图 13-16 所示。单击图 13-15 数据透视表字段 图 13-16 数据透视表效果13.2 设置数据透视表的字段格式在创建好数
9、据透视表后，用户还可以根据需要添加和设置数据透视表中的字段，以达到从不同角度使用数据透视表分析数据的目的。13.2.1 更改数据透视表字段列表视图在默认的方式下，Excel 2016 中的“数据透视表字段列表”都会以“字段节和区域节层叠”的视图方式来显示数据透视表字段列表。用户也可以根据自己的习惯更改数据透视表字段列表的视图方式。步骤 01：在“数据透视表字段列表”窗格中单击“字段视图”下三角按钮，从展开的下拉列表中单击“字段节和区域节并排”选项，如图 13-17 所示。步骤 02：字段节和区域节并排的视图效果如图 13-18 所示。如果只想在“数据透视表字段列表”中显示字段节，可以从“字段视
10、图”下拉列表中单击“仅字段节”选项，此时视图中只显示字段节，不显示区域节，如图 13-19 所示。图 13-17 选择视图方式 图 13-18 字段节和区域节并排显示（温馨提示：用户也可以设置只显示区域节，不显示字段节，只显示区域节有两种视图方式， “仅 22 区域节”是指将区域节显示为 2 行 2 列的格式， “仅 14 区域节”是指将区域节显示为 1 列 4 行的格式。 ） 13.2.2 向数据透视表添加字段向数据透视表添加字段除了直接将字段拖动到区域中外，还有另外两种方法，一种是勾选字段前面的复选框，另一种是使用右键快捷菜单。现分别介绍如下。1.勾选复选框添加字段打开实例文件“数据透视表
11、 1.xlsx”,在“数据透视表字段列表”窗格中的“选择要添加到报表的字段”区域勾选要添加的字段前面的复选框，如图 13-19 所示。Excel 2016会根据该字段的特点自动将它添加到最适当的区域，这里将“数量”字段添加到“数值”区域，并自动按“求和”方式汇总，如图 13-20 所示。此时工作表中的数据透视表如图13-21 所示。图 13-19 勾选字段前的复选框 图 13-20 自动添加到“数值”区域图 13-21 数据透视表效果2.使用右键快捷菜单添加字段在“数据透视表字段列表”窗格中的“选择要添加到报表的字段”区域右击要添加的字段，如“产品类别” ，从弹出的快捷菜单中单击“添加到行标签
12、”命令，如图 13-22 所示。随后，该字段添加到“行标签”区域中，如图 13-23 所示。此时的数据透视表如力 13-24所示，行标签中有两个字段，按“销售部门”和“产品类别”分别对销售金额和数量进行求和。图 13-22 从右键菜单中选择 图 13-23 添加到“行标签”区域 图 13-24 透视图效果13.2.3 报表区域间移动字段用户还可以直接在“数据透视表字段列表”中的报表区域间移动字段，例如，将“年月”字段从“报表筛选”区域移动到“列标签”区域，可以直接将“年月”字段从“报表筛选”区域拖动到“列标签”区域，也可以使用菜单来移动。使用菜单移动的方法如下。步骤 01：在“报表筛选”区域单
13、击“年月”字段中的下三角按钮，(从展开的下拉列表中单击“移动到行标签”选项，如图 13-25 所示。步骤 02：“年月”字段被移动到“行标签”区域的最下方，如图 13-26 所示。步骤 03：此时数据透视表的效果如图 13-27 所示。图 13-25 单击“移动到行标签”选项 图 13-26 移动到“行标签”区域 图 13-27 透视表效果13.2.4 调整字段顺序当同一个报表区域有多个字段时，Excel 默认是按照用户添加字段的先后顺序排列的。例如，在上一节中，当将“年月”字段添加到“行标签”区域时，自动排列在该区域的最下方。区域中字段的顺序决定数据透视表的汇总顺序，如“销售部门”字段在“行
14、标签”最前面，则对“销售部门”字段的汇总为一级汇总。如果用户想要更改汇总字段，则只需要调整字段的顺序即可。步骤 01：在“行标签”区域单击“年月”字段中的下三角按钮，(从展开的下拉列表中单击“移至开头”选项，如图:13-28 所示。步骤 02：“年月”字段被移动到“行标签”区域的最上方，如图 13-29 所示。此时数据透视表的效果如图 13-30 所示。图 13-28 单击“移至开头”选项 图 13-29 “年月”移至开头图 13-30 数据透视表效果13.2.5 删除字段当不想在数据透视表中反映某个字段的数据时，可以将它删除。删除字段通常也有两种方法，一种是直接将字段拖到报表区域外，另一种是
15、通过菜单项来删除。在上节中创建的数据透视表中，假如不需要反映数量，则可以将“数量”字段删除。如果使用拖动法则直接将“求和项：数量”字段拖到报表区域外，也可以使用菜单命令来删除， 步骤 01：单击“求和项” “数量”字段中的下三角按钮，从展开的下拉列表中单击“删除字段”选项，如图 13-31 所示。步骤 02：点击“删除字段”后，如图 13-32 所示。此时数据透视表的效果如图 13-33所示。图 13-31 单击“删除字段”选项 图 13-32 删除后的效果 图 13-33 数据透视表效果13.2.6 字段设置用户还可以设置数据透视表中的字段，比如更改字段的名称、设置分类汇总和筛选以及数据透视
16、表的布局和打印选项等。对于数值字段，还可以设置字段的汇总方式以及值的显示方式等。1.通过字段中的下拉列表设置字段步骤 01：打开实例文件“移动字段.xlsx”,在“数据透视表字段列表”中的“行标签”区域单击“销售部门”字段中的下三角按钮，从展开的下拉列表中单击“字段设置”选项，如图 13-34 所示。步骤 02：打开“字段设置”对话框。单击“布局和打印”标签，在“布局”区域单击选中“以表格形式显示项目标签”单选按钮，然后单击“确定”按钮，如图 13-35 所示。步骤 03:此时的数据透视表布局效果如图 13-36 所示。图 13-34 单击“字段设置”选项 图 13-35“字段设置”对话框图
17、13-36 数据透视表效果2.通过功能区中的命令设置字段用户还可以通过功能区中的命令来设置字段格式。下面以设置“求和项：销售金额”字段的格式为例，具体操作步骤如下所示。步骤 01： 切换至“分析”选项卡。点击“活动字段”按钮，在弹出的下拉框中单击“字段设置”按钮，如图 13-37 所示。图 13-37 打开“字段设置”选项步骤 02： 更改名称。在(“值字段设置”对话框中的“自定义名称”框中输入“销售金额合计” 单击 “数字格式”按钮，设置小数位数，在 “设置单元格格式”对话框中设置小数位数为 2 位，如图 13-38 所示。图 13-38 设置小数位步骤 03： 设置格式后的效果。设置格式后
18、的数据透视表效果如图 13-39 所示。图 13-39 设置格式后的效果（温馨提示：“字段设置”与“值字段设置” 。对于被添加到报表的“值”区域的字段称为值字段，而其他三个区域的字段称为“字段” 。因此，当对它们进行字段设置时，对话框会分别显示为“值字段设置”和“字段设置” 。通常，字段设置除了可以更改字段的名称外，还可以设置字段的分类汇总和筛选、布局和打印等选项；而对于值字段，还可以设置值的汇总方式和显示方式。同时，还可以设置它们的数字格式。 ）教您一招：快速对数据透视表中的值字段求平均值默认的方式下，将数值字段添加到“值”区域时，系统默认的汇总方式为“求和” 。实际上，在数据透视表中，值字
19、段同的汇总方式，即计算方式可以是“求和” 、 “计数” 、 “平均值” 、 “最大值” 、 “最小值”等多种，用户可以选择需要的汇总方式来创建数据透视表。在数据透视表中双击“求和项：销售金额”字段名称，如图 13-40 所示。在打开的“值字段设置”对话框中的“值汇总方式”列表中单击“平均值” ，随后“自定义名称”框中的名称会自动更改为“平均值项：销售金额” ，如图 13-41 所示。图 13-40 设置“值字段设置”图 13-41 平均值项13.3 编辑数据透视表在完成了对数据透视表的字段设置后，用户还可以对数据透视表进行一系列的编辑操作，如选择和移动数据透视表、重命名数据透视表、更改数据透视
20、表的数据源等操作。13.3.1 选择数据透视表和选择单元格、工作表类似，用户也可以选择数据透视表。数据透视表的选择操作包括选择整个数据透视表、选择数据透视表标签以及选择数据透视表中的值，现分别介绍如下。1.选择整个数据透视表打开实例文件“数据透视表.xlsx”,切换至“分析”选项卡，点击“操作”按钮，在弹出的下拉框中单击“选择” ，从展开的下拉列表中单击“整个数据透视表”选项，如图13-42 所示，随后整个数据透视表区域被选中，如图 13-43 所示。图 13-42 单击“整个数据透视表”选项 图 13-43 选中的数据透视表2.选择数据透视表的值区域在“操作”组中再次单击“选择”下三角按钮，
21、从展开的下拉列表中单击“值”选项，如图 13-44 所示，随后数据透视表中的值区域被选中，如图 13-45 所示。图 13-44 选择“值”值区域图 13-45 选中“值”后的效果 3.选择数据透视表的标签区域在“操作”组中再次单击“选择”下三角按钮，从展开的下拉列表中单击“标签”选项，如图 13-50 所示，随后数据透视表中的标签区域被选中，如图 13-51 所示。图 13-50 单击“标签”区域 图 13-51 选中数据透视表中的标签区域13.3.2 移动数据透视表对于已经创建好的数据透视表，用户也可以改变它的位置。可以将它移动到当前工作表的其它区域，也可以将它移到新工作表中。步骤 01：
22、 单击“移动数据透视表”按钮。切换至“分析”选项卡，点击“操作”按钮，在下拉框中选择“移动数据透视表”如图 13-52 所示。图 13-52 单击“移动数据透视表”按钮 步骤 02：选择放置位置。在“移动数据透视表”对话框中单击选中“新工作表”选项按钮，单击“确定”按钮，如图 13-53 所示。图 13-53 “移动数据透视表”对话框步骤 03：移至新工作表中。Excel 会自动在当前工作表中新插入一个工作表，并将数据透视表移到该工作表中，如图 13-54 所示。图 13-54 将数据透视表移至新工作表13.3.3 重命名数据透视表系统默认的为数据透视表设置的名称为“数据透视表 1”、 “数据
23、透视表 2”，用户也可以重新将数据透视表的名称更改为更直观的名称。对数据透视表重命名有两种方法，一种是直接在“数据透视表名称”中输入，另一种是在“数据透视表选项”对话框中设置。步骤 01：切换至在“分析”选项卡中的“数据透视表”组中的“数据透视表名称：”框中可直接输入数据透视表的新名称，如图 13-55 所示。步骤 02：还可以在“数据透视表”组中单击“选项”下三角按钮，从展开的下拉列表中单击“选项” ，如图 13-56 所示，随后打开“数据透视表选项”对话框，在“名称”框中输入新的名称即可，如图 13-57 所示。图 13-55 直接输入名称图 13-56 单击“选项”图 13-57 输入名
24、称13.3.4 更改数据透视表的数据源区域对于已经创建好的数据透视表，还可以更改其数据源。例如，假设数据表中增加了新的行列，如果希望这些新增加的数据加入到数据透视表中，则可以通过更改数据源区域来实现。在“数据透视表工具选项”选项卡中的“数据”组中单击“更改数据源”下三角按钮，从展开的下拉列表中单击“更改数据源”区域，如图 13-58 所示。随后打开“更改数据透视表数据源”对话框，如图 13-59 所示，用户可以重新选择新的区域。图 13-58 单击“更改数据源”选项图 13-59 “更改数据透视表数据源”对话框（温馨提示：推迟布局更新当在 Excel 中使用大型的数据源创建数据透视表时，每次在数据透视表中添加新的字段，默认情况下 Excel 都会及时更新数据透视表。由于数据量较大，可能会使操作变得非常缓慢。在 Excel 2016 中，可以使用“推迟布局更新”选项来手工更新数据透视表，方法是在“数据透视表字段列表”窗格中勾选“推迟布局更新”复选框，此时“更新”按钮变成可用状态，如图 13-60 所示。当把需要调整的字段全部调整完毕后，再单击“更新”按钮更新数据透视表。 ）
