数据透视表如何做分析（不可能的透视表之项目完成率）

我们，让Excel变简单

数据和问题

这是一个在很长时间内困扰我的问题。因为这个问题，导致我在很多项目中不得不采取我不愿意使用的粗暴方法。

假设我们有数据如下图：

数据存放在超级表（Table）BugetTrace中，记录了各部门不同客户的计划和实际数据。

我们希望做出如下的结果报表：

在这个报表中，我们统计各部门各项目的完成率（实际/计划）

当然我们可以写公式完成。但是根据“Excel工作的标准模式”，在制作报表时，公式永远不是第一选择。我们希望用数据透视表来完成，以便实现报表工作的自动化。

尝试使用数据透视表完成结果报表

很简单就可以完成下面的数据透视表：

因为源数据中没有完成率这个数据，所以没有办法使用透视表直接得到这个结果，需要在透视表中进行计算。

由于我们的表格是将计划和实际作为两行存放在数据表中的一列中的，所以应该使用“计算项”进行计算：

选中透视表区域的列字段中的任意单元格，

在“数据透视表工具-分析”选项卡看中，点击“字段、项目和集”，点击“计算项”：

在对话框中，将名称修改为“完成率”，在公式中输入：=实际/计划，点击“添加”，

点击确定后，透视表中添加了计算项：

乍一看上去挺对的，但是仔细看就会发现结果有问题：

以市场部为例，科目1和科目2的完成率是正确的，但是到市场部汇总行的完成率就是错误的，因为市场部总的完成率是通过将科目1和科目2的完成率加在一起实现的。

这显然与我们理解的完成率不一样。用数据透视表是做不出我们期望的结果的。

使用Power Pivot制作结果报表

这个问题可以使用Power Pivot来轻松完成。

选中超级表（BudgetTrace）区域的任意单元格，在“Power Pivot”选项卡中点击“添加到数据模型”：

在弹出的“Power Pivot for Excel”中，点击“主页”选项卡下的“数据透视表”：

弹出对话框：

点击确定后，添加透视表。

在Power Pivot选项卡中，点击度量值，点击新建度量值：

在对话框中，将度量值名称修改为“计划”，公式中输入：

=CALCULATE(Sum('表1'[金额]),'表1'[项目]="计划")

点击确定，在透视表字段列表中出现这个度量值：

同样的步骤，创建一个新度量值“实际”：使用公式：

=CALCULATE(Sum('表1'[金额]),'表1'[项目]="实际")

再创建一个度量值“完成率”，使用公式：

=CALCULATE(SUM('表1'[金额]),'表1'[项目]="实际")/CALCULATE(SUM('表1'[金额]),'表1'[项目]="计划")

将透视表字段按下列方式布局，创建透视表：

得到透视表：

为部门添加分类汇总，并且修改完成率的数字格式为百分比：

大功告成！

总结

我在各种场合下都说过，创建报表最好的工具是数据透视表，因为它可以让我们的工作自动化，从而极大的提高工作效率。不要担心做不出那些报表，基本上超过95%的报表需求都可以用透视表实现。但是，总是有一些报表不能用普通的数据透视表实现，这个问题也困扰了我很久，直到我发现了Power Pivot这个工具。Power Pivot绝对是一个数据分析的利器，希望大家都能够掌握并使用这个工具。

END

,