Excel 数据透视表在 "shown as"值列中的差异

Question

当值显示为父列总数的百分比时，有没有办法让数据透视表自动计算 2 列之间的差异？

现在我需要手动操作，但表格是动态的，竞争对手的数量可能会有所不同。功能似乎很简单，但在谷歌搜索等后找不到它......

请参阅下面的示例图片，了解我想要实现的目标。
(由数据透视表自动化的 F 列是目标)

Answer 1

如果尝试使用数据透视表解决此问题，您有几个选择:

使用基于范围的“传统”数据透视表。这将为您提供百分比差异，但如果不使用外部公式，您将无法获得您所要求的百分比差异。

使用基于您添加到 Excel 数据模型的数据的“OLAP”数据透视表。这将为您提供两个百分比差异和 百分点差异，而不必求助于使用外部公式。

在这两种情况下，我建议您先对数据进行反透视，使其位于所谓的平面文件中。目前，您正在使用交叉制表数据源(即您的源具有称为第 1 年、第 2 年的列)，如果您的数据是交叉表，则您想要执行的跨年份百分比比较类型不起作用。基本上，数据透视表并不意味着使用交叉表数据。

相反，您确实希望您的数据布局，以便您有一个名为 Amount 的列和一个名为 Year 的列，然后您可以使用右键单击菜单中可用的 Show Values As 选项来显示跨年份的百分比差异。要将您的数据转换为平面文件，请参阅我的答案 convert cross table to list to make pivot table

也就是说，您仍然可以在现有(未透视的)数据布局上使用 GETPIVOTDATA 函数，这种方式比仅从另一个引用中减去一个引用更能适应数据​​透视表结构的更改:



但同样，我建议将您的数据转换为平面文件。然后，您还可以执行以下操作:

使用“传统”数据透视表:

如果将“金额”列拖到“值”区域，将“年份”列放在“列”区域，将“竞争对手”放在“行”区域，然后选择百分比显示之一，则可以完全在自包含的“传统”数据透视表中解决您的问题值 作为您在“值”区域中右键单击单元格时将看到的选项。

我说有点，因为不使用外部公式(或不计算源数据中的百分比)，你只能让它显示百分比增加(见最右边的列)，而不是你想要的百分比增加(见最左边的列)。也就是说，我认为百分比增长不那么令人困惑。但我想这取决于你想展示什么。如果你想显示市场份额从一年到下一年的变化，那么百分比是有意义的。




当然，您始终可以使用 GETPIVOTDATA 函数为您完成额外的数学运算，就像我们之前所做的那样，就像我在左侧所做的那样。

使用基于 DataModel 的 OLAP 数据透视表

计算百分比增加可能需要使用使用数据模型构建的数据透视表。在 https://stackoverflow.com/a/49973465/2507160 上查看我的回答这解释了一些关于数据模型的信息(尽管它没有回答这个特定的问题)。

结果如下:




这是我用来执行此操作的措施:

第 1 年总计: =CALCULATE(SUM(Table2[Value]),ALLSELECTED(Table2[Competitor]),Table2[Year] = "Year 1")
第 2 年总计: =CALCULATE(SUM(Table2[Value]),ALLSELECTED(Table2[Competitor]),Table2[Year] = "Year 2")
% 第 1 年: =CALCULATE(SUM(Table2[Value]),Table2[Year] = "Year 1")/[Total Year 1]
% 第 2 年: =CALCULATE(SUM(Table2[Value]),Table2[Year] = "Year 2")/[Total Year 2]
pp差异: = [% Year 2] -[% Year 1]