数据透视表 是 Excel 中最强大的工具之一。 它们使您只需单击几下鼠标即可分析和总结大量数据的各种摘要。 在本文中，我们将熟悉数据透视表，了解它们是什么，学习如何创建和自定义它们。
本文是使用 Excel 2010 编写的。这些年来数据透视表的概念没有太大变化，但在每个新版本的 Excel 中创建它们的方式略有不同。 如果您的 Excel 版本不是 2010，请准备好本文中的屏幕截图与您在屏幕上看到的不同。
有点历史
在电子表格软件的早期，Lotus 1-2-3 规则球。 它的统治地位如此完整，以至于微软开发自己的软件 (Excel) 作为 Lotus 的替代品的努力似乎是在浪费时间。 现在快进到 2010 年！ Excel 在电子表格中的主导地位超过了 Lotus 代码在其历史上的表现，而且仍然使用 Lotus 的人数接近于零。 这怎么可能发生？ 发生如此戏剧性的转折的原因是什么？
分析师确定了两个主要因素：
- 首先，Lotus 认为这个名为 Windows 的新奇 GUI 平台只是昙花一现，不会持续太久。 他们拒绝构建 Lotus 1-2-3 的 Windows 版本（但只持续了几年），预测他们的软件的 DOS 版本将是所有消费者所需要的。 微软自然而然地专门为 Windows 开发了 Excel。
- 其次，微软在 Excel 中引入了一个名为 PivotTables 的工具，而 Lotus 1-2-3 中没有该工具。 Excel 独有的数据透视表被证明非常有用，以至于人们倾向于坚持使用新的 Excel 软件套件，而不是继续使用没有它们的 Lotus 1-2-3。
数据透视表与普遍低估 Windows 的成功一起，为 Lotus 1-2-3 演奏了死亡行军，并迎来了 Microsoft Excel 的成功。
什么是数据透视表？
那么，表征什么是数据透视表的最佳方式是什么？
简单来说，数据透视表是一些数据的摘要，创建它是为了便于分析这些数据。 与手动创建的总计不同，Excel 数据透视表是交互式的。 创建后，如果它们没有提供您希望的图片，您可以轻松修改它们。 只需单击几下鼠标，即可翻转总计，使列标题变为行标题，反之亦然。 您可以使用数据透视表做很多事情。 与其试图用语言描述数据透视表的所有功能，不如在实践中更容易地展示它……
您使用数据透视表分析的数据不能是随机的。 它应该是原始的原始数据，例如某种列表。 例如，它可以是公司在过去六个月中的销售清单。
看下图所示的数据：
请注意，这不是原始数据，因为它已经进行了总结。 在单元格 B3 中，我们看到 30000 美元，这可能是 James Cook 在 30000 月份所做的总结果。 那么原始数据在哪里呢？ XNUMX美元的数字从何而来？ 得出此月度总额的原始销售清单在哪里？ 很明显，有人在组织和整理过去六个月的所有销售数据并将其转化为我们看到的总计表方面做得非常出色。 你觉得花了多长时间？ 小时？ 十点钟？
事实是上面的表格不是数据透视表。 它是根据存储在其他地方的原始数据手工制作的，至少需要几个小时来处理。 只需几秒钟即可使用数据透视表创建这样一个汇总表。 让我们弄清楚如何……
如果我们回到原来的销售清单，它看起来像这样：
您可能会感到惊讶，在数据透视表的帮助下，我们可以在几秒钟内从这个交易列表中创建 Excel 中的月度销售报告，我们在上面进行了分析。 是的，我们可以做到这一点，甚至更多！
如何创建数据透视表？
首先，确保您在 Excel 工作表中有一些源数据。 金融交易清单是最典型的发生。 事实上，它可以是任何内容的列表：员工联系方式、CD 收藏或贵公司的油耗数据。
因此，我们启动 Excel……并加载这样的列表……
在 Excel 中打开此列表后，我们可以开始创建数据透视表。
从此列表中选择任何单元格：
然后在选项卡上 插入 （插入）选择命令 数据透视表 （数据透视表）：
将出现一个对话框 创建数据透视表 （创建数据透视表）给您两个问题：
- 使用什么数据来创建新的数据透视表？
- 在哪里放置数据透视表？
由于在上一步中我们已经选择了一个列表单元格，因此将自动选择整个列表来创建数据透视表。 请注意，我们可以选择不同的范围、不同的表，甚至可以选择一些外部数据源，例如 Access 或 MS-SQL 数据库表。 此外，我们需要选择新数据透视表的放置位置：新工作表或现有工作表之一。 在此示例中，我们将选择选项 - 新工作表 （到新表）：
Excel 将创建一个新工作表并在其上放置一个空数据透视表：
一旦我们单击数据透视表中的任何单元格，就会出现另一个对话框： 数据透视表字段列表 （数据透视表字段）。
对话框顶部的字段列表是原始列表中所有标题的列表。 屏幕底部的四个空白区域允许您告诉数据透视表您希望如何汇总数据。 只要这些区域是空的，表格中也没有任何内容。 我们所要做的就是将标题从顶部区域拖到下面的空白区域。 同时，根据我们的说明自动生成数据透视表。 如果我们犯了错误，我们可以从底部区域删除标题或拖动其他标题来替换它们。
面积 价值观 （含义）可能是四个中最重要的。 该区域中放置的标题决定了将汇总哪些数据（总和、平均值、最大值、最小值等）。这些几乎总是数值。 标题下的数据是该区域的一个很好的候选者 金额 我们原始表的（成本）。 将此标题拖到该区域 价值观 （价值观）：
请注意标题 金额 现在标有复选标记，并且在该区域 价值观 （值）出现了一个条目 金额总和 （金额字段Amount），表示该列 金额 总结。
如果我们查看数据透视表本身，我们将看到列中所有值的总和 金额 原表。
所以，我们的第一个数据透视表就创建好了！ 方便，但不是特别令人印象深刻。 我们可能希望获得比我们目前拥有的更多的数据信息。
让我们转向原始数据并尝试确定可用于拆分此总和的一列或多列。 例如，我们可以形成我们的数据透视表，以便单独计算每个卖家的总销售额。 那些。 行将添加到我们的数据透视表中，其中包含公司中每个销售人员的姓名及其总销售额。 要达到这个结果，只需拖动标题 销售人员 （销售代表）到该地区 行标签 （字符串）：
变得更有趣了！ 我们的数据透视表开始成形……
看到好处了吗？ 只需单击几下，我们就创建了一个手动创建需要很长时间的表。
我们还能做什么？ 好吧，从某种意义上说，我们的数据透视表已经准备好了。 我们已经创建了原始数据的有用摘要。 重要信息已收到！ 在本文的其余部分，我们将介绍一些创建更复杂数据透视表的方法，并学习如何自定义它们。
数据透视表设置
首先，我们可以创建一个二维数据透视表。 让我们使用列标题来执行此操作 付款方法 （付款方式）。 只需拖动标题 付款方法 到该地区 列标签 （列）：
我们得到结果：
看起来很酷！
现在让我们制作一个三维表格。 这样的桌子会是什么样子？ 让我们来看看…
拖动标题 小包装 （复杂）到该地区 报告过滤器 （过滤器）：
注意他在哪里……
这使我们有机会根据“支付了哪个假日综合体”来过滤报告。 例如，我们可以查看所有综合大楼的卖家和付款方式的细分，或者只需单击几下鼠标，更改数据透视表的视图并仅针对订购综合大楼的人显示相同的细分 追日者.
所以，如果你理解正确的话，那么我们的数据透视表就可以称为三维。 让我们继续设置……
如果突然发现数据透视表中应该只显示支票和信用卡支付（即无现金支付），那么我们可以关闭标题的显示 现金 （现金）。 为此，旁边 列标签 单击向下箭头并取消选中下拉菜单中的框 现金:
让我们看看我们的数据透视表现在是什么样子。 如您所见，该列 现金 从她身边消失了。
在 Excel 中格式化数据透视表
数据透视表显然是一个非常强大的工具，但到目前为止，结果看起来有点平淡乏味。 例如，我们加起来的数字看起来不像美元金额——它们只是数字。 让我们解决这个问题。
在这种情况下，您很容易做您习惯的事情，只需选择整个表格（或整个工作表）并使用工具栏上的标准数字格式按钮来设置所需的格式。 这种方法的问题在于，如果您将来更改数据透视表的结构（发生这种情况的可能性为 99%），格式将会丢失。 我们需要的是一种使其（几乎）永久化的方法。
首先，让我们找到入口 金额总和 in 价值观 （值）并单击它。 在出现的菜单中，选择项目 值字段设置 （值字段选项）：
将出现一个对话框 值字段设置 （值字段选项）。
点击按钮 数字格式 （数字格式），将打开一个对话框。 单元格格式 （单元格格式）：
从清单 产品类别 （数字格式）选择 基本会计和财务报表 （财务）并将小数位数设置为零。 现在按几次 OK回到我们的数据透视表。
如您所见，这些数字被格式化为美元金额。
当我们处理格式时，让我们为整个数据透视表设置格式。 有几种方法可以做到这一点。 我们使用更简单的一种……
点击 数据透视表工具：设计 （使用数据透视表：构造函数）：
接下来，通过单击该部分右下角的箭头展开菜单 数据透视表样式 （数据透视表样式）查看广泛的内联样式集合：
选择任何合适的样式并查看数据透视表中的结果：
Excel 中的其他数据透视表设置
有时您需要按日期过滤数据。 例如，在我们的交易列表中有很多很多的日期。 Excel 提供了一种按日、月、年等对数据进行分组的工具。 让我们看看它是如何完成的。
首先删除条目。 付款方法 来自该地区 列标签 （列）。 为此，将其拖回标题列表，并在其位置移动标题 预订日期 （预订日期）：
如您所见，这暂时使我们的数据透视表无用。 Excel 为每个进行交易的日期创建了一个单独的列。 结果，我们得到了一张非常宽的桌子！
要解决此问题，请右键单击任何日期并从上下文菜单中选择 公司 （团体）：
将出现分组对话框。 我们选择 再送 （月）并单击 OK:
瞧！ 这张表更有用：
顺便说一句，该表与文章开头显示的表几乎相同，其中销售总额是手动编制的。
还有一个非常重要的点你需要知道！ 您可以创建的不是一个，而是多个级别的行（或列）标题：
......它看起来像这样......
列标题（甚至过滤器）也可以这样做。
让我们回到表格的原始形式，看看如何显示平均值而不是总和。
要开始，请单击 金额总和 并从出现的菜单中选择 值字段设置 （值字段选项）：
列表 汇总值字段 （操作）在对话框中 值字段设置 （值字段选项）选择 一般 （平均）：
同时，当我们在这里的时候，让我们改变 自定义名称 （自定义名称）与 平均金额 （金额字段金额）到更短的内容。 在此字段中输入类似 平均:
媒体 OK 看看会发生什么。 请注意，所有值都已从总计变为平均值，并且表格标题（在左上角单元格中）已更改为 平均:
如果需要，您可以立即将金额、平均值和数量（销售额）放在一个数据透视表中。
以下是有关如何执行此操作的分步指南，从一个空的数据透视表开始：
- 拖动标题 销售人员 （销售代表）到该地区 列标签 （列）。
- 拖动标题三下 金额 （成本）到地区 价值观 （值）。
- 对于第一个字段 金额 将标题更改为 合计 （金额），该字段的数字格式为 基本会计和财务报表 （金融）。 小数位数为零。
- 第二场 金额 姓名 平均e、为其设置操作 一般 (Average) 并且该字段中的数字格式也更改为 基本会计和财务报表 （财务）小数点后零位。
- 对于第三场 金额 设置标题 计数 和他的手术—— 计数 (数量)
- 在 列标签 （列）字段自动创建 Σ 值 (Σ Values) – 将其拖到该区域 行标签 （线）
这是我们最终的结果：
总金额、平均值和销售数量 - 都在一个数据透视表中！
结论
Microsoft Excel 中的数据透视表包含许多功能和设置。 在这么小的一篇文章中，他们甚至没有接近涵盖所有内容。 完全描述数据透视表的所有可能性需要一本小书或一个大型网站。 大胆而好奇的读者可以继续探索数据透视表。 为此，只需右键单击数据透视表的几乎任何元素，然后查看打开了哪些功能和设置。 在功能区上，您会找到两个选项卡： 数据透视表工具：选项 （分析）和 设计 （构造函数）。 不要害怕犯错，您可以随时删除数据透视表并重新开始。 您有一个长期使用 DOS 和 Lotus 1-2-3 的用户从未有过的机会。