分类：活用数据透视表统计销售数据

财会工作总是离不开数据的统计与计算。在实际工作中，常常会需要同时统计多种数据，如产品类别、产品型号、销售数量、销售部门、销售月份、销售金额等数据。当产品类别、型号以用销售部门等分类较少时，使用Excel静态表格来统计也能够满足用户需要，但一旦出现产品品种、销售部门和人员多时，会计工作的统计量就会大大增加，仅靠单一静态表格来统计，不但工作量大，而且也不利于数据的多角度查看与分析。此时，就可以借助于Excel中的数据透视表，灵活多变地从不同角度去统计与分析数据。数据透视表的特性可以说代表了Excel最具有技术性的成分之一，本章将以销售数据的统计与分析为例，详细介绍Excel 2016中数据透视表的创建与使用。

Excel 向数据透视表添加字段

向数据透视表添加字段除了直接将字段拖动到区域中外，还有另外两种方法，一种是勾选字段前面的复选框，另一种是使用右键快捷菜单。现分别介绍如下。

勾选复选框添加字段

打开实例文件“数据透视表1.xlsx”，在“数据透视表字段”列表窗格中的“选择要添加到报表的字段”区域勾选要添加的字段前面的复选框，如图13-18所示。Excel 2016会根据该字段的特点自动将它添加到最适当的区域，这里将“数量”字段添加到“∑值”区域，并自动按“求和”方式汇总，如图13-19所示。此时工作表中的数据透视表如图13-20所示。

图13-18　勾选字段前的复选框

图13-19　自动添加到“Σ值”区域

图13-20　数据透视表效果

使用右键快捷菜单添加字段

在“数据透视表字段”列表窗格中的“选择要添加到报表的字段”区域右击要添加的字段，如“产品类别”，从弹出的快捷菜单中单击“添加到行标签”命令，如图13-21所示。随后，该字段添加到“行”标签区域中，如图13-22所示。此时的数据透视表如图13-23所示，“行标签”中有两个字段，并按“销售部门”和“产品类别”分别对销售金额和数量进行求和。

图13-21　从右键菜单中选择

图13-22　添加到“行”标签区域

图13-23　透视图效果

Excel 更改数据透视表字段列表视图

在默认的方式下，Excel 2016中的数据透视表字段列表都会以字段节和区域节层叠的视图方式来显示数据透视表字段列表。用户也可以根据自己的习惯更改数据透视表字段列表的视图方式。

步骤01：在“数据透视表字段”窗格中单击“字段视图”下三角按钮，从展开的下拉列表中单击“字段节和区域节并排”选项，如图13-16所示。

步骤02：字段节和区域节并排显示的视图效果如图13-17所示。如果只想在数据透视表字段列表中显示字段节，可以从“字段视图”下拉列表中单击“仅字段节”选项，此时视图中只显示字段节，不显示区域节。

图13-16　选择视图方式

图13-17　字段节和区域节并排显示

提示：用户也可以设置只显示区域节，不显示字段节。只显示区域节有两种视图方式，“仅2×2区域节”是指将区域节显示为2行2列的格式，“仅1×4区域节”是指将区域节显示为1列4行的格式。

Excel 使用外部数据源创建数据透视表

在Excel 2016中，用户可以从Windows系统的数据源中导入外部数据创建数据透视表，常见的可用于创建数据透视表的外部数据格式是数据库文件。在创建数据透视表之前，用户需要将源文件保存在“我的文档\我的数据源”目录下。

步骤01：添加数据源。“将销售记录.accdb”数据库文件另存到“我的文档”文件夹下的“我的数据源”文件夹中，如图13-10所示。

图13-10　将文件存入指定位置

步骤02：选择要分析的数据。新建一个工作簿，打开“创建数据透视表”对话框，在“请选择要分析的数据”区域中单击选中“使用外部数据源”单选按钮，然后单击“选择连接”按钮，如图13-11所示。

步骤03：选择连接数据。随后打开“现有连接”对话框，“销售记录”文件会显示在该列表框中。选中文件后，单击“打开”按钮，如图13-12所示。

图13-11　“创建数据透视表”对话框

图13-12　选择连接数据

步骤04：返回“创建数据透视表”对话框，单击“确定”按钮，Excel会在工作簿中指定的位置创建数据透视表模板，如图13-13所示。

步骤05：添加字段。在“数据透视表字段”窗格中，拖动“产品类别”字段到“行”标签区域，拖动“销售金额1”字段到“Σ值”区域，如图13-14所示。

图13-13　创建数据透视表模板

步骤06：得到的数据透视表效果如图13-15所示。

图13-14　数据透视表字段

图13-15　数据透视表效果

Excel 创建数据透视表的同时创建数据透视图

如果用户希望在创建数据透视表的同时还创建数据透视图，则还可以通过Excel 2016自带的同时创建数据透视表和数据透视图选项进行添加。

步骤01：打开实例文件“1季度销售数据.xlsx”，在“数据透视图”下拉框中单击“数据透视图和数据透视表”按钮，Excel会同时在工作表中创建数据透视表模板和数据透视图模板，如图13-6所示。

步骤02：随后打开“创建数据透视表”对话框折叠显示，拖动鼠标选择“1季度销售数据”工作簿中的工作表Sheet1中的单元格区域A1:H42，选择放置数据透视表的位置为“新工作表”，单击“确定”按钮，创建图表，如图13-7所示。

图13-6　数据透视表和数据透视图

步骤03：拖动“年月”字段到报表“筛选”区域，“销售部门”到“轴”区域，“销售金额”到“Σ值”区域，如图13-8所示。单击关闭按钮，得到如图13-9所示的数据透视表和数据透视图。

图13-7　选择数据区域

图13-8　数据透视图字段

图13-9　数据透视图和数据透视表

Excel 基于工作簿的数据创建数据透视表

在Excel 2016中，用户可以根据数据源区域创建一个空白的数据透视表模板，然后再根据需要在模块中添加和更改字段位置来调整数据透视表的布局。打示例文件“1季度销售数据.xlsx”工作簿，接下来，基于已有Excel表格创建数据透视表。

步骤01：在“插入”选项卡中，单击“数据透视表”选项，如图13-1所示。

图13-1　单击“数据透视表”选项

步骤02：随后打开“创建数据透视表”对话框，保留选中默认的“选择一个表或区域”单选按钮，单击“表/区域”框右侧的单元格引用按钮，如图13-2所示。

图13-2　选择一个表或区域

步骤03：随后打开“创建数据透视表”对话框折叠显示，拖动鼠标选择“1季度销售数据”工作簿中的工作表Sheet1中的单元格区域A1:H42，然后单击“单元格引用按钮”展开对话框，如图13-3所示。

步骤04：选择放置数据透视表的位置并为数据添加数据模型。在“选择放置数据透视表的位置”区域单击选中“现有工作表”单选按钮，选择“位置”为Sheet1工作表中的单元格K22，勾选“将此数据添加到数据模型”单选项，然后单击“确定”按钮，如图13-4所示。

图13-3　选择数据区域

步骤05：拖动“年月”字段到“报表筛选”区域，“销售部门”到“行标签”区域，“销售金额”到“Σ数值”区域，得到如图13-5所示的数据透视表。

图13-4　选择放置数据透视表的位置

图13-5　添加字段