Excel *** 作技巧之 数据透视表布局及美化

1.创建多维度数据透视表

鼠标单击数据区域的任一单元格→插入―数据透视表（默认创建到新工作表）→鼠标右击刚刚创建的数据透视表的任一单元格→数据透视表选项→在数据透视表选项对话框中点击显示→勾选“经典数据透视表布局（启用网格中的字段拖放）”

根据我们的需要，分别拖动行字段、列字段及数字。如果不明白各种字段是什么意思，就多拖动几次试试，寻找最适合的一种。初步创建完我们的数据透视表，但是不是特别美观，需要进一步完善。

2 完善

由于一个地区有多个销售员，数据透视表创建好以后，地区没有重复显示，我们可以通过合并且居中实现合并或者通过重复所有项目标签进行重复显示：

数据透视表选项→在数据透视表选项对话框中点击显示→布局和格式→合并且居中排列带标签的单元格（通过此 *** 作后，所有的行标签和列标签都合并且居中了，老师不建议合并，我感觉合并居中了好看，毕竟这不是数据源表。用设计→报表布局→重复所有项目标签会把地区在每行都显示出来）

鼠标右击金额区域的任一单元格→排序   可实现对同一个区域内的销售员的销售金额进行升序或者降序排列

2.3.1 套用格式：选中数据透视表中的任一单元格会出现分析、设计两个按钮→设计→挑选合适的样式模板（如有不满意的 可以自己设置好，进行保存）

2.3.2 数字千分位：

2.3.2.1 选中数字区域→右击→数字格式→数值→设置小数位数并勾选千分位分隔符

2.3.2.2 选中数字区域→直接点击千位分隔样式“逗号”，并通过右侧的增加或减少小数位数按钮进行调整小数位数

2.3.3  批量删除多余项（比如例题中我们列标签都带着求和项这三个字）：数据选中透视表中的任一单元格→CTRL+H→将求和项这三个字替换为空格。

2.3.4 将汇总二字替换为小计（个人习惯小计和合计）

2.3.5 小计行填充颜色

用鼠标单击任一小计二字所在单元格的上边框（一定是上边框哦），即可选中所有的小计行，此时，直接单击我们想要的颜色即可全部填充上了。

关于填充颜色呢，在我们套用格式时，也有自己套用上颜色的格式，根据个人喜好进行选择。

点击+、-按钮即可实现，可以 *** 作一下试试哦

数据源发生变动时，需要手动刷新收据透视表，右击数据透视表中任一单元格→刷新

我们需要按月或者按年进行统计数据时：右击日期区域的任一单元格→创建组→根据我们的要求进行选择。以下两个图为创建日期组前后的对比，差别是不是一目了然！

我只用过数据透视表进行分类汇总，比如统计一个项目各类款项分别多少，深层的用法没有接触到，我这两天就想着我们的财务分析的基数数据能用数据透视表进行透视，今天试了一下不成功，但是我不死心，我一定要试出来。我就不信那么神奇的数据透视表解决不了我的问题。以后有新的见解及技巧会及时更新。写完后发现今天我列的逻辑不是特别清晰，今天先这样，后期一并完善吧。

数据透视表不仅仅是与数据进行交互的一种方式。您也可以将它们用作常规报告和分析的丰富数据源……而无需使用弱函数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表和数据库返回值的内容

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

1、打开一个班各学科成绩的数据表——点击插入——数据透视表——选择一个表或区域。

2、选择要添加到报表的字段那里选择班别、姓名以及各学科的名称，这样就出现有两个行标签：班别和姓名标签。

3、在数据透视表内容那里右击——在d出的对话框中选择“数据透视表选项”,打开透视表选项对话框。也可以点击菜单中的分析——点击数据透视表分类中的选项。

4、打开数据透视表选项后，默认是展示“布局和格式”——然后点击“显示”——勾选“经典数据透视表布局（启用网格中的字段拖放）（L）”——点击确定或直接按回车键。

5、这样两个行标签“班别”和“姓名”就并列显示在一起了，点击选择数据透视表——点击菜单栏的设计——找到布局分类中的分类汇总。注意：一定要点击选择数据透视表才会出现设计菜单。

6、点击分类汇总字体下方的倒三角图标——在展开的列表选项中选择“不显示分类汇总”，这样就更完美的把多行标签并列显示在一起。

注意事项：

Excel虽然提供了大量的用户界面特性，但它仍然保留了第一款电子制表软件VisiCalc的特性：行、列组成单元格，数据、与数据相关的公式或者对其他单元格的绝对引用保存在单元格中。

---