我有一个简单的表,其中包含一些数量和间隔,以秒为单位和产品名称 .
Month | Product | Amount | Interval in sec
------------------------------------------
05-'12| Prod A | 10 | 5
05-'12| Prod A | 3 | 5
05-'12| Prod B | 4 | 5
05-'12| Prod C | 13 | 5
05-'12| Prod C | 5 | 5
从这个表中我得到了一个数据透视表,其中包含SUM(金额),AVERAGE(以秒为单位的间隔)按月和产品 .
Month | Product | SUM of Amount | AVG of Interval in sec
--------------------------------------------------------
05-'12| Prod A | 13 | 5
05-'12| Prod B | 4 | 5
05-'12| Prod C | 18 | 5
到现在为止还挺好 . 现在我想为我的数据透视表添加额外的列,并以秒为单位给出间隔量的数量/ AVG的结果 .
添加计算值 =SUM(Amount)/AVERAGE(Interval)
并没有给我正确的值 . Excel给了我:
Month | Product | SUM of Amount | AVG of Interval in sec | Amount per sec
-------------------------------------------------------------------------
05-'12| Prod A | 13 | 5 | 1.3
05-'12| Prod B | 4 | 5 | 0.8
05-'12| Prod C | 18 | 5 | 1.8
它实际上做的是=每个月的Sum(Amount)/ SUM(以秒为单位)和基于第一个表中的值的Product . 但我正在寻找:
Month | Product | SUM of Amount | AVG of Interval in sec | Amount per sec
-------------------------------------------------------------------------
05-'12| Prod A | 13 | 5 | 2.6
05-'12| Prod B | 4 | 5 | 0.8
05-'12| Prod C | 18 | 5 | 3.6
因此,通过枢轴字段“以秒为单位的AVG”来划分枢轴字段“数量之和” .
怎么做到这一点?
3 回答
如果其他人遇到此问题,我发现的解决方案是在数据集中添加帮助“计数”列,其中每个记录在“计数”字段下输入1 .
然后,为了达到平均值的效果,您可以使用:
SUM(FIELD_TO_AVERAGE)/SUM(COUNT)
我相信这应该适用于需要平均值的所有情况,但我没有对它进行过广泛的测试 .
您需要在公式中引用数据透视表数据,如下所示:
=GETPIVOTDATA("Sum of Amount",$A$3,"Product","A")/GETPIVOTDATA("Average of Interval",$A$3,"Product","A")
Edit: 从电子表格中:将所需列添加到数据透视表A:
您还可以通过向原始数据表添加额外列来生成列:每个条目的数量/秒,然后当您旋转所有数据时,该列的产品将是您想要的结果 .
Edit (2):
上面的公式是单元格公式而不是透视表公式,不能使用引用抱歉我没有说清楚 . 我正在研究一个数据透视表计算公式,但现在上面作为一个与您的数据透视表一起的列应该产生你需要的 .
在源(计数器)中创建一个字段并在每一行中放置“1” . 把它放到枢轴中并用这个字段除去秒的SUM - 然后它将是一个平均值 . 将原始字段除以此平均值,这样您的最终最终分数将是每个计数器的每秒秒数的数量,这将成为诀窍:)