如果尝试使用数据透视表解决此问题,您有几个选择:
- 使用基于范围的“传统”数据透视表。这会给你百分比差异,但你无法得到百分点不使用外部公式即可实现您所要求的差异。
- 使用基于您添加到 Excel 数据模型的数据的“OLAP”数据透视表。这会给你们两个百分比差异and 百分点差异,而不必求助于使用外部公式。
在这两种情况下,我建议您首先对数据进行逆透视,以便它位于所谓的平面文件中。目前,您正在使用交叉表数据源(即您的源具有名为“第 1 年”、“第 2 年”的列),并且如果您的数据是交叉表,则您想要执行的跨年份百分比比较类型不起作用。基本上,数据透视表并不意味着使用交叉表数据。
相反,您确实希望对数据进行布局,以便有一个名为“金额”的列和一个名为“年份”的列,然后您可以使用右键单击菜单中提供的“将值显示为”选项来显示年份之间的百分比差异。要将您的数据转换为平面文件,请参阅我的答案:将交叉表转换为列表以制作数据透视表 https://stackoverflow.com/questions/47252860/convert-cross-table-to-list-to-make-pivot-table/47253398#47253398
也就是说,您仍然可以在现有(未透视)数据布局上使用 GETPIVOTDATA 函数,这种方式对于数据透视表结构中的更改来说比仅从另一个引用中减去一个引用更加稳健:
但我再次建议将您的数据转换为平面文件。然后您还可以执行以下操作:
使用“传统”数据透视表:
You can kinda如果您将“金额”列拖到“值”区域,将“年份”列放入“列”区域,将“竞争对手”放入“行”区域,然后选择百分比“将值显示为”选项之一,则可以在独立的“传统”数据透视表中完全解决您的问题当您右键单击“值”区域中的单元格时,您会看到。
I say kinda,因为如果不使用外部公式(或不计算源数据中的百分比),您只能让它显示percent增加(见最右栏),而不是百分点根据需要增加(参见最左栏)。也就是说,我认为百分比增长不那么令人困惑。但我想这取决于你想展示什么。如果您想显示一年到下一年的市场份额变化,那么百分比是有意义的。
当然,您始终可以使用 GETPIVOTDATA 函数为您做额外的数学计算,就像我们之前所做的那样,就像我在左侧所做的那样。
使用基于 DataModel 的 OLAP 数据透视表
计算百分比增长可能需要使用使用数据模型构建的数据透视表。请参阅我的回答https://stackoverflow.com/a/49973465/2507160 https://stackoverflow.com/a/49973465/2507160这解释了一些关于数据模型的信息(尽管它没有回答这个具体问题)。
结果如下:
以下是我为此采取的措施:
第一年总计:
=CALCULATE(SUM(Table2[Value]),ALLSELECTED(Table2[Competitor]),Table2[Year] = "Year 1")
第二年总计:
=CALCULATE(SUM(Table2[Value]),ALLSELECTED(Table2[Competitor]),Table2[Year] = "Year 2")
% 第一年:
=CALCULATE(SUM(Table2[Value]),Table2[Year] = "Year 1")/[Total Year 1]
% 第二年:
=CALCULATE(SUM(Table2[Value]),Table2[Year] = "Year 2")/[Total Year 2]
页数差异:
= [% Year 2] -[% Year 1]