Excel数据透视表在“显示为"列中的差异价值观 [英] Excel Pivot Table difference in columns of "shown as" values

问题描述

当值显示为父列总计的百分比时，是否有一种方法让数据透视表自动计算2列之间的差异?

Is there a way to let a pivot table calculate the difference between 2 columns automatically when the values are shown as a % of the parent column total?

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

Now I need to to manually but the table is dynamic and number of competitors may vary. Function seems so easy but can't find it after googling etc...

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

See example picture below of what I want to achieve.
(Column F automated by the pivot table is the goal)

推荐答案

如果尝试使用数据透视表解决此问题，则有两种选择:

If trying to solve this with PivotTables, you've got a couple of options:

1. 使用基于范围的传统"数据透视表.这将为您提供百分比差异，但如果不使用外部公式，就不会像您所要求的那样获得百分比点差异.
2. 使用基于已添加到Excel数据模型的数据的"OLAP"数据透视表.这将为您提供百分比差异和 百分比点差异，而无需诉诸外部公式.

1. Use a 'Traditional' PivotTable that's based on a range. This will give you percentage differences, but you can't get percentage point differences like you're asking for without using external formulas.
2. Use a 'OLAP' PivotTable that's based on data you've added to the Excel Data Model. This will give you both percentage differences and percentage point differences, without having to resort to using external formulas.

在两种情况下，我建议您先取消数据透视，以便将其保存在所谓的平面文件中.当前，您正在使用交叉列表数据源(即，您的源具有称为Year 1，Year 2的列)，并且如果您的数据是交叉表，那么您想要进行的跨年百分比比较的类型将不起作用.基本上，数据透视表并不是要使用交叉表数据.

In both cases, I recommend that you unpivot your data first, so that it is in what's known as a Flat File. Currently you're using a cross-tabulated data source (i.e. your source has columns called Year 1, Year 2), and the type of percentage comparisons across years you want to do doesn't work if your data is a crosstab. Basically, PivotTables aren't meant to consume cross-tabulated data.

相反，您确实希望对数据进行布局，以便有一个名为金额"的列和一个名为年"的列，然后您可以使用右键菜单中提供的显示为"选项以百分比形式显示各年之间的差异.要将数据转换为平面文件，请参见

Instead, you really want your data laid out so that you have a column called Amount and a column called Year, and then you can use the Show Values As options available from the right-click menu to show as percentage differences across years. To transform your data into a flat file, see my answers at convert cross table to list to make pivot table

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

That said, you can still use the GETPIVOTDATA function on your existing (unpivoted) data layout in a way that is somewhat more robust to changes in your PivotTable structure than just subtracting one reference from the other:

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

But again, I recommend transforming your data into a Flat File. Then you can additionally do the following:

使用传统"数据透视表:

如果您将金额"列拖至值"区域，将年份"列置于列"区域，然后将竞争对手"置于自定义"数据透视表中，则可以完全在一个独立的传统"数据透视表中解决问题.行区域，然后选择一个百分比值将值显示为选项，当您右键单击值"区域中的单元格时，您会看到.

You can kinda solve your problem entirely within a self contained 'Traditional' PivotTable if you drag the Amount column to the Values area, put the Year column in the Columns area, put your Competitors in the Rows area, and choose one of the percentage Show Values As options you'll see when you right-click a cell in the Values area.

我说 kinda ，因为如果不使用外部公式(或不计算源数据中的百分比)，则只能使它显示 percent 的增加(请参见最右边的列)，而不是您想要的百分点增大(请参阅最左边的列).话虽如此，我认为百分比的增加不那么令人困惑.但是我想这取决于您要显示的内容.如果您想说说从一年到下一年的市场份额变化，那么百分比是有意义的.

I say kinda, because without using external formulas (or without calculating the percentages back in your source data), you can only get it to show percent increases (see far right column), not percentage point increase like you want (see far left column). That said, I think percent increase is less confusing. But I guess it depends on what you want to show. If you want to show say change in market share from one year to the next, then percentage points make sense.

当然，您总是可以像以前一样使用GETPIVOTDATA函数为您做额外的数学运算，就像我在左侧所做的那样.

Of course, you could always use the GETPIVOTDATA function to do the additional math for you like we did earlier, like I've done on that left hand side.

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

计算百分比增加可能需要使用使用数据模型构建的数据透视表.在 https://stackoverflow.com/a/49973465/2507160 上查看我的答案，其中对数据模型进行了一些解释(尽管它不能回答这个特定问题).

Calculating percentage point increases likely requires using PivotTables built using the Data Model. See my answer at https://stackoverflow.com/a/49973465/2507160 that explains a little about the Data Model (although it doesn't answer this specific question).

结果如下:

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

Here's the measures I used to do this:

一年级总成绩 =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]

p.p.差异: = [% Year 2] -[% Year 1]

这篇关于Excel数据透视表在“显示为"列中的差异价值观的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！