I am posting this to all Excel Champs and Code Experts. I am familiar with Pivot and Slicer feature of excel however came with an requirement and I could not think of any way I can achieve it via pivot ( preferably )
我发布给所有Excel Champs和Code Experts。我熟悉excel的Pivot和Slicer功能但是有一个要求，我想不出任何可以通过枢轴（最好）实现它的方法
The Requirement is like this. I have team data in col 1 and Sales figures in col 2. Now with a simple pivot, I can have the team totals and things like that but I need to put in slicer on team name. If I select Team one , it should show Team one sales against total of other three team . PLease advice if there is a way to do that. In summary I need to compare a person's /team performance against total peers
要求是这样的。我在第1列中有团队数据，在第2列中有销售数据。现在有了一个简单的数据透视表，我可以拥有团队总数和类似的东西，但我需要在团队名称中加入限制器。如果我选择第一组，它应该显示第一组销售额与其他三个团队的总数。如果有办法，请提供建议。总之，我需要将一个人/团队的绩效与总体同行进行比较
Team Sales One 20000 Two 15000 Three 500 Four 35000 One 500
团队销售一20000两15000三500四35000一500
1 个解决方案
#1
0
You can achieve this with formula next to your PivotTable values. You will use formula against your total range of values in col2. If your columns are set to range A:B, and PivotTable is in range D:E, then put this formula =E2/(SUM($B$2:$B$30)-SUMIF($A$2:$A$30,D2,$B$2:$B$30)) in cell F2. You should get result as follows.
您可以使用数据透视表值旁边的公式来实现此目的。您将使用公式来计算col2中的总值范围。如果您的列设置为范围A：B，并且数据透视表位于范围D：E，则将此公式设置为= E2 /（SUM（$ B $ 2：$ B $ 30）-SUMIF（$ A $ 2：$ A $ 30，D2 ，$ B $ 2：$ B $ 30））在F2单元格中。你应该得到如下结果。
If you need to have result inside your PivotTable, then you should use PowerPivot and create a measure. try looking at this for more info: https://msdn.microsoft.com/en-us/library/gg399161(v=sql.110).aspx
如果您需要在数据透视表中包含结果，则应使用PowerPivot并创建度量。尝试查看更多信息：https：//msdn.microsoft.com/en-us/library/gg399161（v = sql.110）.aspx
#1
0
You can achieve this with formula next to your PivotTable values. You will use formula against your total range of values in col2. If your columns are set to range A:B, and PivotTable is in range D:E, then put this formula =E2/(SUM($B$2:$B$30)-SUMIF($A$2:$A$30,D2,$B$2:$B$30)) in cell F2. You should get result as follows.
您可以使用数据透视表值旁边的公式来实现此目的。您将使用公式来计算col2中的总值范围。如果您的列设置为范围A：B，并且数据透视表位于范围D：E，则将此公式设置为= E2 /（SUM（$ B $ 2：$ B $ 30）-SUMIF（$ A $ 2：$ A $ 30，D2 ，$ B $ 2：$ B $ 30））在F2单元格中。你应该得到如下结果。
If you need to have result inside your PivotTable, then you should use PowerPivot and create a measure. try looking at this for more info: https://msdn.microsoft.com/en-us/library/gg399161(v=sql.110).aspx
如果您需要在数据透视表中包含结果，则应使用PowerPivot并创建度量。尝试查看更多信息：https：//msdn.microsoft.com/en-us/library/gg399161（v = sql.110）.aspx