枢轴计算公式：SUM（Field1）/ AVG（Field2） [英] Pivot Calculated formula: SUM(Field1)/AVG(Field2)

问题描述

 月|产品|金额|间隔秒
 ------------------------------------------ 
 05 -'12 | Prod A | 10 | 5 
 05 -'12 | Prod A | 3 | 5 
 05 -'12 |产品B | 4 | 5 
 05 -'12 |产品C | 13 | 5 
 05 -'12 |产品C | 5 | 5 
  

从这个表中我得出了一个数据透视表（SUM）（金额），AVERAGE（间隔秒） ）按月和产品。

 月|产品|金额SUM AVG间隔时间
 ----------------------------------------- --------------- 
 05 -'12 | Prod A | 13 | 5 
 05 -'12 |产品B | 4 | 5 
 05 -'12 |产品C | 18 | 5 
  

到目前为止这么好。现在我想添加和额外的列到我的数据透视表中，给出了秒/秒间隔的数量/ AVG的结果。

添加一个计算值 = SUM（Amount）/ AVERAGE（Interval）没有给我正确的值。 Excel给我：

 月|产品|金额SUM秒间隔AVG每秒金额
 ------------------------------------------- ------------------------------ 
 05 -'12 | Prod A | 13 | 5 | 1.3 
 05 -'12 |产品B | 4 | 5 | 0.8 
 05 -'12 |产品C | 18 | 5 | 1.8 
  

它实际上是每个人的= SUM（Amount）/ SUM（以秒为单位）月和产品基于第一个表中的值。但我正在寻找：

 月|产品|金额SUM秒间隔AVG每秒金额
 ------------------------------------------- ------------------------------ 
 05 -'12 | Prod A | 13 | 5 | 2.6 
 05 -'12 |产品B | 4 | 5 | 0.8 
 05 -'12 |产品C | 18 | 5 | 3.6 
  

所以字面上可以通过枢轴字段'间隔的AVG秒'

如何实现？

解决方案

遇到这个问题，我发现一个解决方案是在您的数据集中添加一个帮助器Count列，其中每个记录在计数字段下输入1。

然后，为了达到平均值的效果，可以使用：

SUM（FIELD_TO_AVERAGE）/ SUM（COUNT ）

我认为这在所有需要平均值的情况下都应该起作用，但我没有很广泛地测试。 >

I've a simple table with some amount and interval in sec by date and product name.

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

From this table I've derived a PivotTable with SUM(Amount), AVERAGE(Interval in sec) by Month and Product.

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

So far so good. Now I want to add and extra column to my PivotTable with gives me the outcome of SUM of Amount / AVG of Interval in sec.

Adding a calculated value =SUM(Amount)/AVERAGE(Interval) is not giving me the right values. Excel gives me:

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

What it actually is doing is =SUM(Amount)/SUM(Interval in sec) for every Month and Product based on the values in the first table. But I'm looking for:

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

So literally divide pivot field 'Sum of Amount' by pivot field 'AVG of Interval in sec'.

How to achieve this?

解决方案

In case anyone else comes across this problem, a solution that I've found is to add a helper "Count" column to your data set, where each record has a 1 entered under the "Count" field.

Then, in order to achieve the effect of an Average, you can use:

SUM(FIELD_TO_AVERAGE)/SUM(COUNT)

I believe this should work in all cases where an average is needed, but I haven't tested it very broadly.

这篇关于枢轴计算公式：SUM（Field1）/ AVG（Field2）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！