怎样设置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表和数据库返回值的内容另外，如果您正在寻找有关此主题的其他帮助，我可以通过三种方式为您提供帮助。

---

南岗区15944399505： 如何在excel中做数据透视表 - ？
旁顾盐酸： 数据透视表操作步骤:1 首先,选中原始数据区域,点击【插入】-【数据透视表】.2 如果刚才已经选中了数据区域,在【数据透视表向导】中最上面的选择区域框可以看到选中的单元格区域,如果刚才没有选中,也可以在此选取.3 数据透...

南岗区15944399505： 如何在excel中建立数据透视表 - ？
旁顾盐酸： 1)打开需要进行数据透视表设置的EXCLE表格,并点击数据清单中的单元格.2)点击“数据菜单”下的“数据透视表和图表报告”命令,弹出“数据透视表和数据透视图向导-3步骤之1”对话框,进行选择后,按“下一步”按钮.3)在弹...

南岗区15944399505： 怎么在Excel中建立数据透视表 - ？
旁顾盐酸： 先把数据区域选中,再点“数据”----“数据透视表和透视图”,根据你的需要,进行选择,点击下一步直至完成.一般而言,是只用点击下一步就可以了.

南岗区15944399505： excel表格怎样创建数据透视表 - ？
旁顾盐酸： 打开需要进行数据透视表设置的EXCLE表格,并点击数据清单中的单元格.点击“数据菜单”下的“数据透视表和图表报告”命令,弹出“数据透视表和数据透视图向导-3步骤之1”对话框,如图所示,进行选择后,按“下一步”按钮.在弹...

南岗区15944399505： 怎么在Excel表格中做数据透视表 - ？
旁顾盐酸： 要统计的表格先搞好,表头要标准格式. 用数据透视表简单步骤:选中要统计的表格-数据-数据透视表和数据透视图-下一步-下一步-布局(把不能计算的字段如姓名.日期.牌号.移到左边行的位置,要计算的如数量.重量.金额等.移到右下面数据位置.如果统计方式是计数或求和,双击一下数据的内容-确定)-确定-完成.如不符要求,再把数据和姓名往右各移一格,合计和汇总都会出来的..总之,每个名称都可以移动,不会影响原表,多试几次就知道了.

南岗区15944399505： 如何在excel中创建数据透视表 - ？
旁顾盐酸： 打开excel表格,选择要创建数据透视表的源数据.在工作表中,随便点击一个单元格(只要与数据有关的单元格即可),然后选择插入选项中的数据透视表. 如图所示弹出一个创建透视表的窗口: 1、请选择要分析的数据: 选择一个表或区...

南岗区15944399505： excel表格怎样做数据透视表 - ？
旁顾盐酸： 选择“插入”选项卡中“数据透视表”或“数据透视图”.选择后弹出“创建数据透视表及数据透视图”窗口 在“表/区域”内将第一个表内需要分析的数据选中,点击确定 产生一个空白的数据透视表及数据透视图 在右侧“数据透视表字段列表...

南岗区15944399505： EXCEL中的数据透视表具体操作步骤? - ？
旁顾盐酸： 1、单击“插入”选项卡下的“数据透视表”工作组中的“数据透视表”命令,将弹出“创建数据透视表”对话框. 2、在弹出的“创建数据透视表”对话框中的“选择一个表或区域”的引用选择框中已经自动将当前活动单元格所在的单元格区...

南岗区15944399505： EXCEL表格里面的透视怎么做? - ？
旁顾盐酸： 1,首先要保证数据源是一个每列都有列标题的数据表,某列没有列标题的自己加上2,单击数据源中任一非空白单元格,选择“数据D＂菜单中到“数据透视表和数据透视图”,会弹出一个对话框,选中“microft office excel数据列表或数据...

南岗区15944399505： 在excel中怎么做透视数据表格啊 - ？
旁顾盐酸： 表格/数据透视表和数据透视图命令,共分三大步 1.选择数据源类型和创建的报表类型 2.选择数据源 3.需要设置两项:一是布局,而是数据透视表存放的位置