掌握数据透视表让你以一当十

数据透视表是真正的偷懒利器，懒人们的福音。表弟、表妹们，走过路过，千万不要错过。数据透视表，你值得拥有！

一、数据透视表的用途

数据透视表是一种交互式报表，它整合了统计函数、分类汇总、排序、筛选的各项功能，不必手工输入函数公式，仅用鼠标拖动字段就可快速分析、比较大量数据，让你从纷繁复杂的数据中透视出数据的本质结构，并可快速改变报表的分析视角和结构。它就像一个变形金刚，可快速地变出各种类型的报表。数据透视表还有一个突出的优势：计算速度快。用函数来编制公式进行数据统计分析，如果要进行多条件统计，可能就要编制较复杂的公式，甚至使用数组公式。如果公式较多，更新计算时就比较慢，但使用数据透视表则具有无可比拟的优势，计算速度非常快。

在需要分析相关汇总数据时，尤其是有大量数据需要统计分析时，通常会用到数据透视表。数据透视表主要有以下几个功能和用途：

（1）以傻瓜化的操作查询、统计大量数据。

（2）对数值数据进行分类汇总和聚合，按分类和子分类对数据进行汇总，创建自定义计算和公式。

（3）展开和折叠要关注结果的数据级别，查看感兴趣区域汇总数据的明细。

（4）将行移动到列或将列移动到行（或“透视”），以查看源数据的不同汇总。

（5）对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式，使用户能够关注所需的信息。

（6）提供简明、有吸引力并且带有批注的联机报表或打印报表。

（7）可帮你从不同的角度查看数据，并且对相似数据的数字进行比较。

二、创建数据透视表

创建数据透视表的方法有以下三种。

方法1：

在【插入】选项卡上的“表格”组中，单击“数据透视表”，或者单击“数据透视表”下方的箭头，再单击“数据透视表”。

方法2：

通过数据透视表向导来创建。Excel 2007版后透视表向导已不在常用功能区，可将它添加到快速访问工具栏中。具体方法：在Excel选项对话框点击“快速访问工具栏”，选择“不在功能区的命令”，然后找到“数据透视表和数据透视图向导”，点击“添加”，然后“确定”退出。

方法3：

使用快捷键，依次按Alt、D和P键启动“数据透视表和数据透视图向导”来创建透视表。

特别提醒：

数据透视表对数据的规范性要求比较严格，不规范的数据无法使用数据透视表。要使用数据透视表至少要做到以下几点：

（1）数据表格的列标题不能为空，否则创建透视表时系统会发出如下提示。

（2）数据表格中不能有空行、空列。

（3）数据表格中不能有合并单元格。

（4）如果表格不规范，应整理成标准的清单型表格。

三、数据透视表的布局和格式

数据透视表主要由四个部分组成：筛选字段、行字段、列字段、数值字段区域。行字段相当于普通表格的行标题；列字段相当于列标题；筛选字段是透视表特有的区域，它用于对报表的筛选，可选定单项或多项。

行字段和列字段应根据需要合理排列，以方便排版阅读和满足报表使用者的需求为原则。尤其是当有多个数值字段时，更应考虑哪种排列更合理，更符合报表使用者的需求。

数值区域中的字段不限于数字，还可以是文本。如果是数字默认的统计方式为求和，如果是文本默认为计数。可以通过拖放将同一字段拖放到多个到数值区域，以便进行不同的统计。

在Excel 2007后，要重新布局数据透视表，必须在“数据透视表字段列表”任务窗格中拖动各字段。这对用惯了Excel 2003的用户来说，会很不方便。我们可以通过以下设置恢复可直接在数据透视表中拖动的布局模式：选中数据透视表，点击右键，在弹出的快捷菜单中选择“数据透视表选项”，在【数据透视表选项】对话框的【显示】选项勾选【经典数据透视表布局（启用网格中的字段拖动）】

四、数据透视表的汇总方式

数据透视表默认的汇总方式是求和，如果是文本，则默认为计数。实际上，数据透视表还提供了多项汇总方式：求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差。

如果要改变字段的汇总方式，可通过以下三种方式打开“值字段设置”对话框进行设置。

（1）选择要改变的字段所在的任一单元格→点击右键→选择【值字段设置】→打开【值字段设置】对话框。

（2）选择要改变的字段所在的任一单元格→点击右键→选择【值汇总依据】，然后在快捷菜单中选择需要的汇总方式，或点击【其他选项】打开【值字段设置】对话框。

（3）在数据透视导航窗格的【数值】区域点击要改变的字段，在弹出的【值字段设置】对话框的【值汇总方式】选项卡中选择需要的汇总方式。

五、利用数据透视表的组合功能：快速编制月报、季报、年报

我们使用数据透视表进行统计分析时，它默认以字段下的每个唯一值作为统计依据。

如果我们将上图中的【日期】字段作为行标签，它会默认以销售记录中的每一个日期作为分类依据。这种设置大部分情况下能满足我们的需求，但有时也存在例外。比如，我们很少需要统计每天的订单数据，而是按月、季、年进行统计。这种情况下，如果手工统计，工作量非常大，好在强大的Excel提供了组合的功能，完全可以解决我们的难题。

我们以上图中的“数据”表格为数据源，布局创建数据透视表。

选中数据透视表【日期】字段中的任一单元格，点击右键，选择【创建组】。在弹出的【分组】对话框中选择需要的步长。此步长设置可多选，比如可选择月、年，也可选择季度、年。

我们按年、季度、月来统计，统计表如下：

至此，就做出了按年、季度、月统计的报表。当然，我们还可按周来统计，假设按周统计是从周一到周日。其他步骤相同，只是在【组合】选项卡中【步长】中选择【日】，将天数设为7。

除了Excel提供的自动组合功能，还可以手动组合：选定要组合的记录，点击右键，选择【组合】，手动组合时，不是连续区域的字段值也可组合在一起。左手按CTRL键，右手鼠标点选要组合的字段项进行组合。

手动组合的优点是比较灵活，但如果项目较多时效率较低，并且新增项目时还需要重新组合。解决方案是：可考虑在数据源添加分组信息的辅助列，将源数据进行分组，然后再对包含辅助列的源数据进行数据透视。