Excel数据透视表中的最大值总和 [英] Sum of Max in PivotTable for Excel

问题描述

我有一个来自下表的数据透视表：

I have a PivotTable that comes from the following table:

    +---------+---+-----+
    |    A    | B |  C  |
  +-+---------+---+-----+
  |1|   Date  |Id |Value|
  +-+---------+---+-----+
  |2|4/01/2013|1  |4    |
  +-+---------+---+-----+
  |3|4/01/2013|2  |5    |
  +-+---------+---+-----+
  |4|4/01/2013|1  |20   |
  +-+---------+---+-----+
  |5|4/02/2013|2  |20   |
  +-+---------+---+-----+
  |6|4/02/2013|1  |15   |
  +-+---------+---+-----+

我想先按ID汇总，然后按日期汇总，使用最大值按ID汇总，然后用Sum按日期汇总。结果表如下所示：

And I want to aggregate first by Id and then by Date, using Max to aggregate by Id and then Sum to aggregate by Date. The resulting table would look like this:

    +---------+----------------+
    |    A    | B              |
  +-+---------+----------------+
  |1|   Date  |Sum(Max(Id,Date)|
  +-+---------+----------------+
  |2|4/01/2013|25              |
  +-+---------+----------------+
  |3|4/02/2013|35              |
  +-+---------+----------------+

上面的25来自获取每个日期的每个ID的最大值（最大值（2013年1月4日，1/6）-> 20和最大值（2，4/01/2013）-> 5，因此这些最大值的总和为25。

The 25 above comes from getting the Max per Id per Date (Max(1, 4/01/2013) -> 20 and Max(2, 4/01/2013) -> 5, so the Sum of those Max is 25.

我可以通过添加Date轻松地进行两个级别的聚合和Id列放入数据透视表的行部分，但是在为值选择聚合函数时，我可以选择最大值，获取最大值的最大值，或者选择总和，获取总和。最多。

I can do the two levels of aggregation easily by adding the Date and Id columns into the Rows section of the PivotTable, but when choosing an aggregation function for Value, I can either choose Max, getting a Max of Max, or Sum, getting a Sum of Sum. That is, I cannot get a Sum of Max.

理想情况下，解决方案不是计算数据透视表，然后从那里复制或获取公式，因为那样会破坏

Do you know how to achieve this? Ideally, the solution would not be to compute a PivotTable and then copy from there or get a formula, because that would break easily if I want to dynamically change fields.

谢谢！

推荐答案

这是何w我会在SQL中这样做：

This is how I would do it in SQL:

SELECT DATE, SUM(MAXED_VAL) as SummedMaxedVal
FROM (
     SELECT DATE, ID, MAX(VALUE) as MAXED_VAL
     FROM table
     GROUP BY DATE, ID
)
GROUP BY DATE

这篇关于Excel数据透视表中的最大值总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！