当前位置：首页 > Excel小技巧 > 怎样设置excel表格中的数据透视表-Excel学习网

怎样设置excel表格中的数据透视表-Excel学习网

2019年10月17日 18:04 发布来源：Excel学习网

数据透视表不仅仅是与数据进行交互的一种方式。您也可以将它们用作常规报告和分析的丰富数据源……而无需使用弱函数GETPIVOTDATA。

您可以将数据透视表用作数据库，就像使用简单表或Excel表一样。

但是，我从未见过有关如何执行此操作的描述。这太糟糕了，因为Excel 2010使Excel用户能够使用一个或多个数据透视表作为庞大而强大的电子表格数据库。

如果不将数据透视表设置为数据库，则通常必须使用 GETPIVOTDATA函数从中返回数据。这限制了您的能力，因为GETPIVOTDATA是一种“屏幕抓取器”功能。也就是说，功能不是非常强大的函数只能返回您在屏幕上看到的数字和文本。

另一方面，如果确实将数据透视表设置为数据库，则可以将Excel的更强大功能（如SUMIFS，SUMPRODUCT，INDEX，MATCH等）与数据库一起使用。在以后的文章中，我将向您展示这些功能如何为您提供更大的功能，以便从电子表格数据库返回结果。

将数据透视表用作数据库具有许多一般优势。但是有一个特殊的优势值得考虑……

PowerPivot的优势

微软对PowerPivot的介绍为使用数据透视表作为电子表格数据库提供了新的重要优势。

PowerPivot允许工作簿在工作簿中存储数百万行数据。但是不幸的是，一个工作簿中的公式和数据透视表无法直接从存储在另一工作簿中的PowerPivot数据集中返回数据。

这是一个问题，因为每次您保存另一代PowerPivot报表时，以及每次创建新的PowerPivot报表工作簿时，都必须保存PowerPivot数据的另一个副本。因此，在不知不觉中，您就被多代相同的数据所掩埋。

在SharePoint下使用Excel Services可以帮助您管理此问题。但是，如果您不使用SharePoint，请执行以下操作：

仅设置一个工作簿来包含每个PowerPivot数据集。

在每个这些工作簿中，将一个或多个数据透视表设置为电子表格数据库。

设置报表工作簿，以从每个PowerPivot工作簿中的数据透视表数据库返回数据。

使用这种方法，您只需维护一个工作簿即可包含每个PowerPivot数据集。但是，您可以创建任意数量的报表工作簿，这些报表工作簿可以从任意数量的PowerPivot工作簿中包含的任意数量的数据透视表数据库中返回数据。

使用这种方法，您可以为每个报表工作簿提供访问大量数据的权限！您只需要管理每个PowerPivot数据集的一个副本。

但是现在，让我们在电子表格中设置数据透视表数据库…

将数据透视表设置为电子表格数据库

为了方便起见，我定义了该数据透视表，以从我在介绍Excel的三种电子表格数据库中介绍的Excel表中获取数据。但是您的数据透视表可能会链接到数据仓库，Access或其他“真实”数据库。

默认情况下，您的初始数据透视表将类似于此图。

您不能将此版本的数据透视表用作电子表格数据库，因为布局太复杂了。它不是简单的行和列。在这里，使用GETPIVOTDATA是返回其数据的唯一实用选择。

但是Excel 2010允许您选择将此数据透视表的格式更改为可以用作数据库的格式。为此，首先在数据透视表中选择任何单元格。然后在数据透视表工具中，选择…

设计，布局，小计，不显示小计

设计，布局，总计，行和列为关闭

设计，布局，报告布局，以表格形式显示

设计，布局，报告布局，重复所有项目标签

设计，布局，空白行，每个项目后删除空白行

选项，显示，+ /-按钮（隐藏按钮）

完成这六项更改后，数据透视表将如下所示。

（默认的数据透视表样式将这些边界应用于A列中每组项目之间的边界。由于它们不会妨碍您的工作，因此我从不费心寻找可以消除它们的样式。）

此处，数据透视表的排列方式非常类似于Excel表格。它具有规则的行和列，您的公式可以轻松引用，但有一个例外：公式没有实际方法可以实际引用此数据透视表中的数据。原因如下：

在Excel表格中，Excel会自动命名表格及其中的每一列。这使我们的公式可以按名称轻松引用数据。但是对于数据透视表，Excel不能为我们的公式提供任何帮助。因此，我们必须使用范围命名技巧，以允许工作表公式轻松引用数据透视表。

因此，让我们设置名称...

设置范围名称，以便公式可以轻松引用数据透视表

让我们仔细定义此步骤。否则，您将需要付出更多的努力。

数据透视表字段列表使您可以很好地控制数据透视表的形状。它可以控制表格的水平和垂直尺寸。

但是，当您将数据透视表用作数据库时，如果可以将更改限制在水平维度上，则可以使工作变得更加轻松。另一方面，您的解决方案必须适应垂直尺寸的变化。

限制对水平尺寸的更改

Excel使您能够添加或删除数据透视表字段，这会导致数据透视表水平扩展或收缩。您还可以更改表中字段的顺序。

但是，当您使用数据透视表数据库时，您应该计划限制这两个操作。

显然，如果删除公式依赖的字段，则会破坏公式。而且没有办法避免这个问题。

另一方面，如果在数据透视表数据库中添加或重新排列字段，则可以设置自动适应这些更改的范围名称。但是，这需要更多的工作来设置。因此，在本文中，我假设您总是将新字段添加到数据透视表的最右边，并且您不会重新排列现有字段。

适应垂直尺寸的变化

当您在数据透视表中更改过滤器时，它必然会垂直扩展或收缩。这正是您想要的。

Excel公式可以轻松适应这些更改。您只需要使用动态范围名称。

如何为数据透视表设置动态范围名称

像大多数普通范围名称一样，动态范围名称也会引用工作表中的区域。但是与普通范围名称不同，动态范围名称可以响应于数据更改而更改其引用。

例如，如果数据透视表高十行，则动态范围名称只能引用这十行。但是，如果数据透视表的高度增长到一百万行，则动态范围名称可以自动调整以引用所有这些一百万行。

动态范围名称是使用返回引用的电子表格函数（通常为 OFFSET或 INDEX函数）定义的。这些函数返回的引用指向工作表的新区域，以响应其参数值的更改。

在下图中，我将向您展示如何使用OFFSET函数定义Sales.DateTime范围名称。但是首先，我需要设置一个公式来返回数据透视表中的行数。

设置数据透视表的Sales.NumRows范围

首先，我在数据透视表上方插入了几行。我将数据透视表的名称更改为Sales。我添加了下面的单元格A1中显示的文本。我使用“创建名称”对话框将单元格A1中的名称分配给单元格B1。然后，在显示的单元格中输入以下公式：

B1：= COUNT（$ A：$ A）

此公式依赖于Excel的两个特征。首先，COUNT函数仅计算单元格中的数字，而不计算文本。（另一方面，COUNTA函数同时计算数字和文本。）其次，日期是数字。

因此，由于此表中的每一行都有一个日期，因此我们可以通过计算A列中的日期数来计算数据透视表中的行数。

关于此设置，您还应该了解三件事：

首先，请注意，日期与COUNTA公式位于不同的列中。这是避免循环计算错误的必要条件。

其次，如果您的数据中不包含日期也可以。通常，您可以在一列数字值中计算数字。如果那不可能，则可以使用COUNTA函数对一列中的数字和文本进行计数。但是，在这种情况下，应减去数据库中未包含的内容的单元格数。

为了说明，您还可以在显示的单元格中使用此公式：

B1：= COUNTA（$ A：$ A）-3

在这里，我减去3可以得出单元格A1，A3和A4中内容的计数。

第三，请注意，我为单元格B1分配了名称Sales.NumRows。通过以“ Sales”开头与该数据透视表相关的所有名称（其中“ Sales”为数据透视表的名称），您的范围名称将在Excel中更易于管理。另外，将另一个数据透视表添加到工作簿时，基于新表的名称，您可以具有相似的范围。例如，您可能具有Costs.NumRows，SKUs.NumRows等。

以下是您可能会想到的三个问题的答案……

在范围名称中使用句点是否安全？

是。Excel将范围名称中的句点与其他任何字符一样对待。但是通过如图所示使用它们，您可以更轻松地识别与每个数据透视表数据库关联的名称。

如果我们更改数据透视表数据库的名称，是否需要更改范围名称的名称？

不必要。但是，如果您确实使名称保持同步，则公式将更容易理解。这是因为，如果您使用以“ Sales”开头的范围名称从名为“ Sales”的数据透视表中返回数据，则公式会更清晰。特别是当您在创建公式几个月后查看公式时。但是，Excel对您为范围名称分配什么名称确实没有关系。

为什么需要在单独的单元格中设置COUNT或COUNTA计算？我不能仅将它用作定义动态范围的公式的一部分吗？

由于COUNT和COUNTA必须检查它们引用的范围内的每个单元格，因此它们需要花费较长的时间来计算。也就是说，如果我们要在以下公式中使用它们，则每次使用该公式时都需要重新计算。但是，当我们将计算放在一个单元格中时，它只需要计算一次-这样可以大大减少计算时间。

设置动态范围名称

现在，您可以使用OFFSET函数设置Sales.DateTime动态范围名称。通常，该函数采用以下形式：

=偏移（参考，行，列，高度，宽度）

要定义动态名称，首先通过选择“公式”，“定义的名称”，“定义名称”来启动“新名称”对话框。在“新名称”对话框中，在“名称”编辑框中输入Sales.DateTime，然后输入公式…

= OFFSET（数据！$ A $ 4,1,0，Sales.NumRows，1）

…在“引用”编辑框中。然后选择确定。

在此公式中，该函数从单元格A4开始，该单元格是数据透视表的左上角单元格。引用指向下一行（第二个参数），并停留在同一列（第三个参数）。然后，引用将按Sales.NumRows值指定的行数垂直扩展。最后，参考设置为一列宽。

您可以类似地设置其他行。完成后，将为该工作表定义名称，如下所示：

Sales.DateTime = OFFSET（数据！$ A $ 4,1,0，Sales.NumRows，1）

Sales.Product = OFFSET（数据！$ B $ 4,1,0，Sales.NumRows，1）

Sales.Color = OFFSET（数据！$ C $ 4,1,0，Sales.NumRows，1）

Sales.Amount = OFFSET（数据！$ D $ 4,1,0，Sales.NumRows，1）

（这是为每个名称设置公式的一种简单方法：设置名字时，将“偏移”公式复制到“新名称”对话框的“引用”框中。接下来，在创建其他名称时，粘贴公式放入每个新名称的“引用”框中，然后根据需要在第一个参数中编辑列字母。）

采取后续步骤

既然已经设置了名称，您的公式就可以引用数据透视表数据库，就像它们引用其他数据库一样，如以下示例所示：

简单表： = SUM（Amount）

Excel表格： = SUM（Table1 [Amount]）

数据透视表： = SUM（Sales.Amount）

但是，当然，您通常需要使用更多的电子表格功能，而不仅仅是SUM。这就是我在必须知道的两个函数中从Excel表和数据库返回值的内容

另外，如果您正在寻找有关此主题的其他帮助，我可以通过三种方式为您提供帮助。

原创文章如转载请注明：转载自Excel学习网 [ http://www.excelxx.com/]
需要保留本文链接地址：http://www.excelxx.com/jiqiao/10705.html

分享到：

上一篇：excel表格的数据透视表怎么设置-Excel学习网下一篇：如何将一个excel表格的数据匹配到另一个表中-Excel学习网