如何在Excel中制作枢轴表(简单逐步)

如果您正在阅读本教程,您听到了大量的机会(甚至使用)Excel枢轴表。这是Excel中最强大的功能之一(无开玩笑)。

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

让我们开始吧。

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

什么是枢轴桌子,为什么要关心?

枢轴表是Microsoft Excel中的工具,允许您快速汇总大量数据集(只需点击几下)。

即使您对Excel的世界绝对是新的,您也可以轻松使用枢轴表。它与拖放行/列标题一样简单,以创建报告。

假设您有一个数据集,如下所示:

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

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

它具有区域,零售商类型和客户的销售数据。

现在,您的老板可能希望从此数据中了解一些事情:

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

你可以继续使用188亚洲金博宝app为您提供这些问题的答案,但如果您的老板突然出现了五个更多问题的列表,那么

您必须返回数据并每次发生更改时创建新公式。

这是Excel枢轴表真的很方便的地方。

在几秒钟之内,枢轴表将回答所有这些问题(如您所示的问题)。

但实际好处是它可以通过立即回答他的问题来容纳您的Finicky数据驱动的老板。

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

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

在Excel中插入枢轴表

以下是使用上面显示的数据创建枢轴表的步骤:

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

只要单击“确定”,将使用其中的Pivot表创建新的工作表。

虽然枢轴表已创建,但您不会看到其中没有数据。您只看到左侧的枢轴表名和单行指令,右侧的枢轴表字段。

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

现在在我们使用这个枢轴表跳进分析数据之前,让我们了解制作Excel枢轴表的螺母和螺栓是什么。

Excel枢轴桌的螺母和螺栓

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

在本节中,您将了解:

  • 枢轴缓存
  • 价值区
  • 行地区
  • 列区域
  • 过滤区

枢轴缓存

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

使用Pivot Table创建不同视图时,Excel不会返回数据源,而是它使用Pivot Cache快速分析数据并为您提供摘要/结果。

生成枢轴缓存的原因是优化枢轴表功能。即使您有数以千计的数据,枢轴表总结数据超快。您可以拖放行/列/值/过滤器框中的项目,并立即更新结果。

注意:枢轴缓存的一个缺点是它会增加工作簿的大小。由于它是源数据的副本,因此在创建枢轴表时,该数据的副本存储在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  - 值区域中创建枢轴表

在这个例子中,它在四个地区每个月都有总销量。

行地区

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

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

列区域

值区域顶部的标题使列区域成为列区域。

在下面的示例中,列区域包含该月(以红色突出显示):

在Excel  - 列区域中创建枢轴表

过滤区

过滤器区域是可选过滤器,您可以使用它在数据集中进一步深入钻取。

例如,如果您只想看到多行零售商的销售,您可以从下拉下拉(在下面的图像中突出显示)选择该选项,并且枢轴表将仅使用多行零售商的数据更新。

在Excel  - 过滤器区域中创建枢轴表

使用枢轴表分析数据

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

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

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

在Pivot Tabe字段部分中,您拥有字段和区域(如下突出显示):

在Excel  - 字段和区域中创建一个枢轴表

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

这是一个简单的拖放机制,您可以在其中拖动一个字段并将其放在四个区域中的一个。一旦执行此操作,它将出现在工作表中的Pivot表中。

在Excel中创建一个枢轴表 - 演示

现在让我们试着回答你的经理使用这个枢轴表的问题。

Q1:南部地区的总销量是多少?

拖动行区域中的区域字段和值区域中的收入字段。它会自动更新工作表中的枢轴表。

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

这个问题的答案是21225800。

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

Q2销售的五大零售商是什么?

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

您将获得一个枢轴表,如下所示:

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

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

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

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

这将根据总销售给您一个排序的列表。

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

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

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

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

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

现在拖动价值区中的收入领域,您将为每个客户提供销售(以及整个区域)。

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

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

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

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

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

在Excel中创建枢轴表 -  Q3C

现在有一种以上的猫才有一种方法。您还可以将该区域放入过滤区,然后仅选择南部区域。

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

我希望本教程为您提供Excel Pivot表的基本概述,并帮助您入门。

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

Excel电子书订阅

免费Excel书籍

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