我有一个简单的表,其中包含一些数量和间隔,以秒为单位和产品名称 .
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”来划分枢轴字段“数量之和” .
怎么做到这一点?