Excel数据透视表计算字段 [英] Excel Pivot Table Calculated Field

问题描述

 月|产品|金额
 ------------------------- 
 05-'12 | Prod A | 10 
 05 -'12 | Prod A | 3 
 05 -'12 |产品B | 4 
 05 -'12 |产品C | 13 
 05 -'12 |产品C | 5 
  

从此表中，我导出了一个带有SUM（Amount）的数据透视表，显示为列的％总计。

 月|产品|金额
 -------------------------------- 
 05-'12 | Prod A | 28.89％
 05 -'12 |产品B | 8.89％
 05 -'12 |产品C | 62.22％
  

到目前为止这么好...现在我想为我的数据透视表添加一个额外的列其中给出了总金额字段中的百分比值为1000的结果。
添加计算值 = SUM（Amount）* 1000 不给我正确的值。 Excel给我。

 月|产品|金额SUM字段1 
 -------------------------------------------- ------------- 
 05 -'12 | Prod A | 28.89％| 13,000 
 05 -'12 |产品B | 8.89％| 4,000 
 05 -'12 |产品C | 62.22％| 28,00 
  

它不是使用百分比值，而是将数量和次数的总和1000.
我可以在数据透视表外部做一个简单的公式，但是表的行移动很多，所以最好在表内完成它。
如何实现？

提前谢谢。

解决方案>

您获得的值是实际总和时间1000.要获得所需的内容，您需要简单地从第一列给出适当格式的数字（* 1000）。这可以通过＃'%%%%来实现，但这确实显示了很多百分号，只有一个是足够的。

或者只是存储百分比每个条目都有助于数据和使用。

I've a simple table with some amounts by date and product name.

Month | Product | Amount
-------------------------
05-'12| Prod A  | 10     
05-'12| Prod A  | 3      
05-'12| Prod B  | 4      
05-'12| Prod C  | 13     
05-'12| Prod C  | 5      

From this table I've derived a Pivot table with SUM(Amount) displayed as % of column total.

Month | Product | SUM of Amount
--------------------------------
05-'12| Prod A  | 28.89%           
05-'12| Prod B  | 8.89%        
05-'12| Prod C  | 62.22%

So far So good... Now I want to add an extra column to my Pivot table which gives me the outcome of the percent values in the sum amount field times 1000. Adding a calculated value =SUM(Amount)*1000 is not giving me the right values. Excel gives me.

Month | Product | SUM of Amount | Field 1 
---------------------------------------------------------
05-'12| Prod A  | 28.89%         | 13,000                     
05-'12| Prod B  | 8.89%          | 4,000                 
05-'12| Prod C  | 62.22%         | 28,00                  

It isn’t taking the percent values but the sums of the amounts and times that by 1000. I could do a simply formula outside of the pivot table but the lines of the table move around a lot so it would be better to have it done inside the table. How to achieve this?

Thank you in advance.

解决方案

The values you get are the actual sums time 1000. To get what you want, you need to simply give the figures from the first column the appropriate format (*1000). This you can achieve via #'%%%%, however this does display a lot of percentage signs, where only one would be sufficient.

Alternatively just store the Percentage that each entry contributes in the data and use that.

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