怎样设置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的3种电子表格数据库中介绍的Excel表中获得数据 。但是您的数据透视表可能会链接到数据仓库，Access或其他“真实”数据库。

默许情况下，您的初始数据透视表将类似于此图。您不能将此版本的数据透视表用作电子表格数据库，由于布局太复杂了。它不是简单的行和列。在这里，使用GETPIVOTDATA是返回其数据的唯一实用选择。但是Excel 2010允许您选择将此数据透视表的格式更改成可以用作数据库的格式。为此，就某些情况而言，首先在数据透视表当选择任何单元格。然后在数据透视表工具中，选择…设计，布局，小计，不显示小计设计，布局，总计，行和列为关闭设计，布局，报告布局，以表格情势显示设计，布局，报告布局，重复所有项目标签设计，布局，空白行，每一个项目后删除空白行选项，显示，+ /-按钮（隐藏按钮）完成这6项更改后，数据透视表将以下所示。

（默许的数据透视表样式将这些边界利用于A列中每组项目之间的边界。由于它们不会妨碍您的工作，因此我从不费心寻觅可以消除它们的样式。）此处，数据透视表的排列方式非常类似于Excel表格。它具有规则的行和列，您的公式可以轻松援用，但有一个例外：公式没有实际方法可以实际援用此数据透视表中的数据。缘由以下：在Excel表格中，Excel会自动命名表格及其中的每列。这使我们的公式可以按名称轻松援用数据。但是对数据透视表，Excel不能为我们的公式提供任何帮助。因此，这种情况下，我们必须使用范围命名技能，以允许工作表公式轻松援用数据透视表。因此，这种情况下，让我们设置名称…设置范围名称，以便公式可以轻松援用数据透视表让我们仔细定义内涵此步骤。否则，您将需要付出更多的努力。数据透视表字段列表使您可以很好地控制数据透视表的形状。它可以控制表格的水平和垂直尺寸。但是，当您将数据透视表用作数据库时，如果可以将更改限制在水平维度上，则可使工作变得更加轻松。另外一方面，您的解决方案必须适应垂直尺寸的变化。限制对水平尺寸的更改Excel使您能够添加或删除数据透视表字段，这会致使数据透视表水平扩大或收缩。您还可以更改表中字段的顺序。但是，当您使用数据透视表数据库时，您应当计划限制这两个操作。明显，如果删除公式依赖的字段，则会破坏公式。而且没有办法避免这个问题。另外一方面，如果在数据透视表数据库中添加或重新排列字段，则可以设置自动适应这些更改的范围名称。但是，这需要更多的工作来设置。因此，这种情况下，在本文中，我假定您总是将新字段添加到数据透视表的最右侧，并且您不会重新排列现有字段。适应垂直尺寸的变化当您在数据透视表中更改过滤器时，它必定会垂直扩大或收缩。这正是您想要的。Excel公式可以轻松适应这些更改。您只需要使用动态范围名称。如作甚数据透视表设置动态范围名称像大多数普通范围名称一样，动态范围名称也会援用工作表中的区域。但是与普通范围名称不同，动态范围名称可以响应于数据更改而更改其援用。举例来看，如果数据透视表高10行，则动态范围名称只能援用这10行。但是，如果数据透视表的高度增长到一百万行，则动态范围名称可以自动调剂以援用所有这些一百万行。动态范围名称是使用返回援用的电子表格函数（通常为 OFFSET或 INDEX函数）定义内涵的。这些函数返回的援用指向工作表的新区域，以响应其参数值的更改。在下图中，我将向您展现如何使用OFFSET函数定义内涵Sales.DateTime范围名称 。但是首先，我需要设置一个公式来返回数据透视表中的行数。设置数据透视表的Sales.NumRows范围首先，我在数据透视表上方插入了几行。我将数据透视表的名称更改成Sales。我添加了下面的单元格A1中显示的文本。我使用“创建名称”对话框将单元格A1中的名称分配给单元格B1。然后，在显示的单元格中输入以下公式：B1：= COUNT（$ A：$ A）此公式依赖于Excel的两个特点。首先，COUNT函数仅计算单元格中的数字，而不计算文本。（另外一方面，COUNTA函数同时计算数字和文本。）其次，日期是数字。

因此，这种情况下，由于此表中的每行都有一个日期，因此我们可以经过计算A列中的日期数来计算数据透视表中的行数。关于此设置，您还应当了解3件事：首先，请注意，日期与COUNTA公式位于不同的列中。这是避免循环计算毛病的必要条件。其次，如果您的数据中不包括日期也能够。通常，您可以在一列数字值中计算数字。如果那不可能，则可使用COUNTA函数对一列中的数字和文本进行计数。但是，在这类情况下，应减去数据库中未包括的内容的单元格数。为了说明，您还可以在显示的单元格中使用此公式：B1：= COUNTA（$ A：$ A）⑶在这里，我减去3可以得出单元格A1，A3和A4中内容的计数。第3，请注意，我为单元格B1分配了名称Sales.NumRows。经过以“ Sales”开头与该数据透视表相干的所着名称（其中“ Sales”为数据透视表的名称），您的范围名称将在Excel中更容易于管理。另外，将另外一个数据透视表添加到工作簿时，基于新表的名称，您可以具有类似的范围。举例来看，您可能具有Costs.NumRows，SKUs.NumRows等。以下是您可能会想到的3个问题的答案……在范围名称中使用句点是不是安全？是。Excel将范围名称中的句点与其他任何字符一样对待。但是经过如图所示使用它们，您可以更轻松地辨认与每一个数据透视表数据库关联的名称。如果我们更改数据透视表数据库的名称，是不是需要更改范围名称的名称？没必要要。但是，如果您确切使名称保持同步，则公式将更容易理解。这是由于，如果您使用以“ Sales”开头的范围名称从名为“ Sales”的数据透视表中返回数据，则公式会更清晰。特别是当您在创建公式几个月后查看公式时。但是，Excel对您为范围名称分配甚么名称确切没有关系。为何需要在单独的单元格中设置COUNT或COUNTA计算？我不能仅将它用作定义内涵动态范围的公式的一部份吗，我们将继续深入。由于COUNT和COUNTA必须检查它们援用的范围内的每一个单元格，因此它们需要花费较长的时间来计算。也就是说，如果我们要在以下公式中使用它们，则每次使用该公式时都需要重新计算。但是，当我们将计算放在一个单元格中时，它只需要计算一次-这样可以大大减少计算时间。设置动态范围名称现在，您可使用OFFSET函数设置Sales.DateTime动态范围名称。通常，该函数采取以下情势：=偏移（参考，行，列，高度，宽度）要定义内涵动态名称，首先经过选择“公式”，“定义内涵的名称”，“定义内涵名称”来启动“新名称”对话框。在“新名称”对话框中，在“名称”编辑框中输入Sales.DateTime，然后输入公式…= OFFSET（数据！$ A $ 4,1,0，Sales.NumRows，1）…在“援用”编辑框中。然后选择肯定。在此公式中，该函数从单元格A4开始，该单元格是数据透视表的左上角单元格。援用指向下一行（第2个参数），并停留在同一列（第3个参数）。然后，援用将按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表和数据库返回值的内容另外，如果您正在寻觅有关此主题的其他帮助，我可以经过3种方式为您提供帮助。