Excel读书笔记18——掌握数据透视表让你以一当十

2023-02-26

本文主要是 Excel读书笔记18——掌握数据透视表让你以一当十 相关的知识问答，如果你也了解，请帮忙补充。

参考知识1 数据透视表是真正的偷懒利器，懒人们的福音。表弟、表妹们，走过路过，千万不要错过。数据透视表，你值得拥有！赶快来学习吧。

一、数据透视表的用途

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

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

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

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

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

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

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

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

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

二、如何创建数据透视表

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

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

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

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

图3-49中的表格是“合同台账”（见示例文件“表3-13 数据透视表”），登记了2013年、2014年的合同签订情况。

要求：使用数据透视表对此表格做一个各片区各类产品的合同统计汇总表（见图3-50）。

具体操作步骤如下。

Step1：打开示例文件“表3-13 数据透视表”，选中“数据”表格数据区域的任一单元格，在【插入】选项卡上的“表格”组中，单击“数据透视表”，或者单击“数据透视表”下方的箭头再单击“数据透视表”，检查一下弹出的“创建数据透视表”对话框中“表/区域”范围是否正确，如图3-51所示。

本示例中由于已将数据表格A1: H1105区域设置为表格（通过点击【插入】选项卡的“表格”按钮），故表/区域默认为“表1”，否则会自动设置为“数据!$A$1:$H$1105”。将A1:H1105区域设置为表格的好处如第一章第三节中“可扩展性原则”所述：当合同台账新增记录时，数据透视表会将新增的记录添加到源数据中，否则需要手动更改数据透视表的表/区域。

Step2：放置数据透视表的位置默认为新工作表（可更改为现有工作表，注意不能与源表区域重合），点击“确定”，即可创建一个空的数据透视表，如图3-52所示。

Step3：将右边数据透视表字段列表中“片区”字段拖入列标签区域，将“产品名称”字段拖入行标签区域，将销售数量、销售金额字段拖入数值区域。拖入后透视表结构如图3-53所示。

Step4：此表格将求和字段作为列标签来排列，结构不符合我们的要求，故应将列标签中的数值字段拖入行标签。变更后表格的结构如图3-54所示。

Step5：对透视表进行格式设置，根据需要进行美化。

需要特别提醒的是：数据透视表对数据的规范性要求比较严格，不规范的数据无法使用数据透视表（关于数据的规范性要求请参见第一章）。要使用数据透视表至少要做到以下几点：

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

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

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

（4）其他规范性要求请参见第一章清单型表格的要求。如果表格不规范，应整理成标准的清单型表格，整理的具体方法参见本书第二章第一节“不规范表格及数据的整理技巧”。

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

数据透视表主要由四个部分组成：筛选字段、行字段、列字段、数值字段区域。行字段相当于普通表格的行标题；列字段相当于列标题；筛选字段是透视表特有的区域，它用于对报表的筛选，可选定单项或多项。如图3-56中的数据透视表，“片区”就是筛选字段，B1单元格选择了“东部”片区，图3-56中的报表就只是东部片区的数据。

筛选字段可选择多项，如图3-57中将“选择多项”勾选上，然后就可选择多个项目。

行字段和列字段应根据需要合理排列，以方便排版阅读和满足报表使用者的需求为原则。尤其是当有多个数值字段时，更应考虑哪种排列更合理，更符合报表使用者的需求。如图3-58中在行标签区域将数值字段放在产品字段之前，就会形成不同的报表结构。

数值区域中的字段不限于数字，还可以是文本。如果是数字默认的统计方式为求和，如果是文本默认为计数。可以通过拖放将同一字段拖放到多个到数值区域，以便进行不同的统计。具体请参见本节第六点“数据透视表的显示方式”的举例。

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

如果习惯于将行标签的单元格进行合并居中排列（见图3-60），可以选择数据透视表，点击右键，点击“数据透视表选项”，在“数据透视表选项”对话框的“布局和格式”选项卡中勾选“合并且居中排列带标签的单元格”，如图3-61所示。

如果对Excel默认的报表格式不满意，还可以在数据透视表【设计】选项卡的“布局”组分别进行“分类汇总”“总计”“布局”“空格”的设置，以及设计数据透视表报表的样式。如图3-62所示。

如要要取消各类别的分类汇总，可选择数据透视表的任一单元格，然后点击右键，将“分类汇总‘XX’”的勾去掉，这样操作更快捷。

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

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

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

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

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

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