使用计算的字段在数据透视中更正总计 [英] Correct Grand Total in Pivot Using Calculated Field

问题描述

我有一个像这样的excel数据透视表

I have an excel pivot table which looks like

Date        Sum of DURATION   Capped Daily Total
03-Jan                  9.5                  8.0
04-Jan                  6.0                  6.0
05-Jan                 10.5                  8.0
06-Jan                  4.5                  4.5
Grand Total            30.5                  8.0

如您所见，有上限的每日总计总计不正确. 在此示例中:

As you can see, the Grand Total for Capped Daily Total is incorrect. In this example:

1. Date列作为行添加，并且来自源数据.分组依据:天数
2. Sum of DURATION被添加到值"部分，并且来自源数据.
3. Capped Daily Total是计算字段.使用的公式是= MIN('DURATION', 8)

1. Date column is added as a Row and comes from source data. It has been Grouped By: Days
2. Sum of DURATION is added in the Values section and comes from the source data.
3. Capped Daily Total is a computed field. The formula used is = MIN('DURATION', 8)

在计算总计时，似乎excel已计算了总计，然后使用公式= MIN('DURATION (HOURS)', 8)将其限制为8小时.我不希望该公式适用于总计-仅适用于行.总计应该是一个简单的SUM公式.

When calculating the grand total it appears that excel has computed the Grand Total and then used the formula = MIN('DURATION (HOURS)', 8) to cap it to 8 hours. I don't want the formula to apply to the Grand Total - only to the rows. The grand total should be a simple SUM formula.

如何在Excel中达到此要求?

How can I achieve this requirement in Excel?

推荐答案

这是数据透视表的问题，请参见

This is a issue with pivot tables see link. To get around this you can add the Daily Capped Total formula to you table itself instead of the pivot table and the field will calculate correctly.

获取总数的另一种解决方案是将公式放在数据透视表的外部，然后将列的总和减去总计.

Another solution to get the total would be to place a formula outside the pivottable and sum the column minus the grand total.

=SUM(G:G)-GETPIVOTDATA("Sum of Daily Capped Total",$E$1)

根据对原始数据的描述，可以使用此公式.它会查找该范围内日期的第一个匹配项，如果它与当前行号匹配，它将相加和最小值以得到您的要求.

Based off you description of the raw data you could use this formula. It looks for the first occurrence of the date in the range and if it matches the current row number it will sum and min to get your requirement.

=IF(MATCH(A2,$A$2:$A$15,0)=ROW()-1,MIN(SUMIF($A$2:$A$15,A2,$B$2:$B$15),8),0)

这篇关于使用计算的字段在数据透视中更正总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！