如何在Excel中制作透视表(简单的一步一步)

如果你正在阅读本教程,你很有可能听说过(甚至使用过)Excel数据透视表。这是Excel中最强大的功能之一(不是开玩笑的)。

关于使用枢轴表的最佳部分是即使您在Excel中不了解任何内容,您仍然可以用它的非常基本的理解做得非常令人敬畏。

让我们开始吧。

点击这里要下载示例数据并跟随。

什么是数据透视表?为什么要关心它?

数据透视表是Microsoft Excel中的一个工具,它可以让你快速总结巨大的数据集(只需点击几下)。

即使您是Excel世界的新手,您也可以轻松使用透视表。它就像拖放行/列标题来创建报告一样简单。

假设你有一个如下所示的数据集:

在Excel中创建一个枢轴表 -  DataSet

这是由大约1000行组成的销售数据。

它有按地区、零售商类型和客户划分的销售数据。

现在你的老板可能想从这些数据中了解一些事情:

  • 2016年南方地区的总销售额是多少?
  • 销售额排名前五的零售商是什么?
  • Home Depot的表现如何与南方其他零售商进行比较?

你可以继续使用188亚洲金博宝app给你这些问题的答案,但是如果你的老板突然提出了另外五个问题,那该怎么办呢?

每次有变化时,您都必须回到数据并创建新的公式。

这就是Excel透视表发挥作用的地方。

在几秒钟内,数据透视表将回答所有这些问题(你将在下面学到)。

但真正的好处是,它可以通过立即回答老板的问题来适应挑剔的、受数据驱动的老板。

这太简单了,你可能需要几分钟,并展示你的老板如何做到这一点。

希望,现在你了解为什么枢轴表如此令人敬畏。让我们继续使用数据集(如上所示)创建枢轴表。

在Excel中插入透视表

以下是使用上述数据创建数据透视表的步骤:

  • 单击DataSet中的任意位置。
  • 到插入- >表- >透视表。在Excel中创建透视表-插入
  • 在“创建透视表”对话框中,默认选项在大多数情况下都能正常工作。这里有几件事需要检查:
    • 表/范围:它是根据你的数据集默认填写的。如果数据没有空白行/列,Excel会自动识别正确的范围。如果需要,您可以手动更改。
    • 如果要在特定位置中创建枢轴表,则在“选择要放置的数据透视表报告”选项下,请指定位置。否则,使用Pivot Table创建一个新的工作表。在Excel中创建枢轴表 - 插入枢轴对话框
  • 单击确定。

只要单击OK,就会创建一个包含数据透视表的新工作表。

虽然已经创建了数据透视表,但您不会在其中看到任何数据。您所看到的是数据透视表的名称和在左侧的一行指令,以及数据透视表字段在右侧。

在Excel中创建一个枢轴表 - 空白枢轴表工作表

现在,在我们开始使用数据透视表分析数据之前,让我们先了解Excel数据透视表的具体细节。

Excel透视表的螺母和螺栓

要有效地使用Pivot Table,了解创建枢轴表的组件非常重要。

在本节中,您将了解:

  • 主缓存
  • 值区域
  • 行区域
  • 列区域
  • 过滤区

主缓存

只要使用数据创建枢轴表,后端就会发生一些事情。Excel拍摄数据的快照并将其存储在其内存中。此快照称为Pivot Cache。

当您使用透视表创建不同的视图时,Excel不会返回数据源,而是使用透视缓存来快速分析数据并给出总结/结果。

生成数据透视缓存的原因是为了优化数据透视表的功能。即使有数千行数据,数据透视表总结数据的速度也非常快。您可以拖放行/列/值/过滤器框中的项目,它将立即更新结果。

注意:枢轴缓存的一个缺点是它会增加工作簿的大小。由于它是源数据的副本,因此在创建枢轴表时,该数据的副本存储在Pivot缓存中。

阅读更多:什么是枢轴缓存以及如何最好地使用它

值区域

值区域是包含计算/值的原因。

Based on the data set shown at the beginning of the tutorial, if you quickly want to calculate total sales by region in each month, you can get a pivot table as shown below (we’ll see how to create this later in the tutorial).

橙色突出显示的区域是价值区域。

在Excel中创建透视表-值区域

在本例中,它有四个地区每个月的总销售额。

行区域

值区域左侧的标题使行区域成为行。

在下面的示例中,Rows区域包含区域(以红色突出显示):
在Excel  - 行区域中创建枢轴表

列区域

“值”区域顶部的标题构成“列”区域。

在下面的例子中,Columns区域包含月份(用红色突出显示):

在Excel中创建透视表-列区域

过滤区

筛选器区域是一个可选筛选器,您可以使用它进一步深入数据集。

例如,如果您只想查看Multiline零售商的销售情况,您可以从下拉菜单中选择该选项(在下面的图片中高亮显示),透视表将只更新Multiline零售商的数据。

在Excel -过滤器区域创建透视表

使用枢轴表分析数据

现在,让我们尝试通过使用我们创建的Pivot表来回答问题。

点击这里要下载示例数据并跟随。

要使用Pivot Table分析数据,您需要决定如何在最终结果中查看数据摘要。例如,您可能需要左侧的所有区域以及它旁边的总销售。一旦您有这种清晰度,您可以简单地拖放枢轴表中的相关字段。

在透视表字段部分,你有字段和区域(如下高亮显示):

在Excel中创建透视表-字段和区域

基于用于枢轴表的后端数据来创建字段。区域部分是您放置字段的位置,并根据字段的位置,您的数据在Pivot表中更新。

这是一个简单的拖放机制,您可以简单地拖动一个字段,并将其放在四个区域之一。一旦你这样做了,它就会出现在工作表的透视表中。

在Excel中创建透视表-演示

现在让我们尝试回答您的经理使用数据透视表时提出的问题。

Q1:在南方地区的总销售额是多少?

拖动行区域中的区域字段和值区域中的收入字段。它会自动更新工作表中的数据透视表。

请注意,只要您在值区域中删除收入领域,就会成为收入的总和。默认情况下,Excel会汇总给定区域的所有值,并显示总计。如果需要,您可以更改此以计算,平均或其他统计指标。在这种情况下,总和是我们需要的。

这个问题的答案是21225800。

在Excel中创建一个枢轴表 -  Q1A

销售额排名前五的零售商是什么?

将Customer字段拖动到值区域中的行区域和收入字段。如果地区部分存在任何其他字段,您希望删除它,只需选择它并将其拖出即可。

你会得到一个透视表如下所示:

在Excel - Q2a中创建透视表

请注意,默认情况下,项目(在这种情况下客户)按字母顺序排序。

要获得前五名零售商,您可以简单地对该列表进行排序,并使用前五名客户名称。要做到这一点:

  • 右键单击“值”区域中的任何单元格。在Excel中创建枢轴表 -  Q2B
  • 点击排序- >从最大到最小排序。在Excel中创建一个枢轴表 -  Q2C

这将给你一个基于总销售额的排序列表。

在Excel中创建一个枢轴表 -  Q2D

Q3:家庭仓库的表现如何与南方其他零售商进行比较?

您可以为此问题做大量分析,但这里让我们尝试比较销售。

拖动行区域中的区域字段。现在拖动区域字段下方的行区域中的客户字段。当您这样做时,Excel将理解您希望首先按区域划分数据,然后由区域内的客户分类。你有一些如下所示的东西:

在Excel中创建一个枢轴表 -  Q3A

现在拖动Values区域中的Revenue字段,您将得到每个客户(以及整个区域)的销售额。

在Excel中创建一个枢轴表 -  Q3B

您可以根据以下步骤根据销售数据对零售商进行排序:

  • 右键单击具有任何零售商销售值的单元格。
  • 点击排序- >从最大到最小排序。

这将立即通过销售价来排序所有零售商。

现在,您可以快速扫描南部地区,并确定家庭仓库销售额为3004600,它在南部地区的四名零售商比四家零售商更好。

在Excel中创建枢轴表 -  Q3C

现在,剥猫皮的方法不止一种。你也可以把区域放在过滤器区域,然后只选择南部区域。

点击这里要下载示例数据。

我希望本教程能给你一个Excel透视表的基本概述,并帮助你开始使用它。

以下是您可能喜欢的一些枢轴表教程:

Excel电子书订阅

免费的EXCEL的书

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作