是否有一个excel公式在数据透视表中查找最大项目 [英] is there an excel formula to find max item in a pivot table
问题描述
我有一个数据透视表:
- 团队作为行标签
- 列标签
和应用程序数据计数
我想要一个列显示每个团队计数最高的区域。
所以如果我的表格如下所示:
<新新新新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新200新新旗新美国|欧洲|亚洲
团队A 3 2 1
团队B 1 2 3
团队C 1 1 3
团队D 1 2 3
对于A组,它将返回美国。对于D队,它将返回亚洲
是可能的吗?
两个想法:
- 在数据透视表上使用条件格式
- 沿着自定义maxif函数尝试一些东西(请参阅这个 blogpost )在枢轴上
数据源
编辑:
如果将数据透视表复制到普通表单,您可以使用索引
两次得到你想要的。假设您的示例数据位于A1:D5中,您可以放在E2中,例如
= INDEX(B $ 1:D $ 1, INDEX(B2:D2,MAX(B2:D2)))
如果您有两行最大值将返回第一个最大列。
Google细化似乎是一种非常适合这种数据操作的免费工具。
i have a pivot table with:
- Teams as the Row Label
- Regions as the Column Label
and count of Application data
i want to have a column that displays the Region with the highest count for each Team.
So if my table looks like this:
Team | US | Europe | Asia
Team A 3 2 1
Team B 1 2 3
Team C 1 1 3
Team D 1 2 3
for Team A it would return US. For Team D it would return Asia is this possible?
Two ideas:
- use conditional formating on the pivot table
- try something along a custom maxif function (see this blogpost) on the pivot datasource
EDIT:
If you copy the pivot table to a normal sheet, you could use the index
twice to get what you want. Assuming your example data resides in A1:D5, you could put in E2 for instance
=INDEX(B$1:D$1,INDEX(B2:D2,MAX(B2:D2)))
If you have a row with two maximum values, the first max column will be returned.
Google refine appears to be a free tool very well suited for this kind of data manipulation.
这篇关于是否有一个excel公式在数据透视表中查找最大项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!