我有一个带有日期的表,以及两个值列,项目1和项目2。
我想打一个数据透视表的最大月度出口值Item 1
及Item 2
。
这按预期工作。然后,我想对这些最大值求和,以得到数据透视表中第1项和第2项的最大值的总和。
我试过使用两个计算字段:
=SUM(MAX(' Item 1'), MAX(' Item 2'))
和=SUM('Item 1','Item 2')
。
但这个数额从当月的所有值Item 1
和Item 2
,不仅最大值。使用数据透视表外部的公式很容易,但是我想在数据透视表内部执行此操作,以便将来可以对其进行图表绘制并添加新数据。下图显示了上述两个计算字段的结果(红色),以及数据透视表外部的简单公式,给出了所需的结果(绿色)。
我正在Windows 10上使用Excel Professional Plus2016。如果要尝试,请使用以下示例数据。
Date Item 1 Item 2
01/01/2020 2 8
02/01/2020 3 6
03/01/2020 5 4
04/01/2020 3 7
05/01/2020 5 4
06/01/2020 2 7
07/01/2020 3 4
01/02/2020 7 6
02/02/2020 3 4
03/02/2020 6 3
04/02/2020 3 7
05/02/2020 2 8
06/02/2020 5 7
07/02/2020 5 3
01/03/2020 3 8
02/03/2020 5 6
03/03/2020 7 3
04/03/2020 8 7
05/03/2020 3 4
06/03/2020 2 2
07/03/2020 5 5
I suspect the plain pivot table function in excel is not enough to compute the min() and max() in a certain pivot table set, since the min() and max() functions are supposed to work in a row-by-row basis only. You need to create a "measure" (e.g. max of a certain group of cells, you may say they are calculated fields which can be manipulated more) to tell excel the expression to compute the value for a group of cells for you. This does not seems to work in plain old pivot table.
However, this functionality is provided in PowerPivot within MsExcel, which is using the DAX expression to create "measures" (simular to calculated fields but can be manipulated with DAX functions). Normally this addin is not enabled, so you need to enable it in COM-addin.
If you are using MSOffice 2013 or above, you can enable the PowerPivot Addin in File >> Options, select COM-addin and then Go, and then use the additional functions in the PowerPivot module for your needs.
After enabling Powerpivot addin, you can create a new measure MaxItem1 using the maxx(datatable, datatable[item1]). then you can use this MaxItem1 measure to do sum(). You may need to use sumx(datatable, datatable[MaxItem1]+datatable[MaxItem2]).
Similarly minx() can be used to create MinItem values.
You may refer to this 5-min video for how to operate in Powerpivot. This video is not actually using the sum() function. You can look into sumx() to craft that precise function as above, which is not hard.
https://www.youtube.com/watch?v=lE2OAPh-Zf8
(我不知道在这个论坛中是否允许引用视频,如果我做错了什么,任何主持人都请纠正我。不幸的是,我目前使用的Excel是2010年,我无法创建XLS文件供您参考直接。但是我想视频很清晰,而且很容易做。)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句