Excel数据透视表-计算总和的平均值 [英] Excel pivot table - average of calculated sums

问题描述

我确定这很简单，但是如何获取数据透视表以显示计算出的字段总和的平均值?在简化的示例中，我过滤了x1的资金，数据透视表显示了每人剩余资金的总和.现在如何获得人的平均水平 (因此，人工计算为3300/3)?

I'm sure this is simple, but how do I get a pivot table to display an average for a calculated sum of fields? In the simplified example, I've filtered out fund x1, and the pivot table is showing the sums of the remaining funds per person. Now how do I get an average by person (so, manually calculated, 3300/3)?

我尝试使用计算所得的字段，但无法弄清楚它的工作原理，因为分母将根据有多少人使用我过滤的资金而变化.如果我在计算字段中使用平均值，则返回到对资金进行平均.

I tried using a calculated field, but cannot figure out how it will work because the denominator will change based on how many people will have the funds I'm filtering on. If I use the averaging inside the calculated field it goes back to averaging the funds.

我尝试将计算结果放到数据透视表之外，但这确实有效，但是当我进行过滤时，我的计算字段不再与数据透视表数据相邻，而是仅在工作表上自行浮动.

I tried putting the calculation outside the pivot table, and this works, but of course as I filter, my calculated field is no longer adjacent to the pivot table data, instead just floating off on the worksheet by itself.

TIA.

这里是每个请求的字段列表-如果我尝试在值框中添加金额平均值"，则会取平均金额，而不是每个人的金额. :

Per request here is the field list - if I try adding an "average of amount" to the value box it averages the fund amounts, instead of the fund amount per person. :

推荐答案

这是有效的解决方案:

首先，您应该安装或启用Power Pivot.引用Microsoft:

Firstly you should install or enable Power Pivot. Quoting Microsoft:

Power Pivot是一个Excel加载项，可用于执行强大的数据 分析并创建复杂的数据模型.

在较新的Excel版本中，已经安装了Power Pivot，您可以通过以下方法启用它:

In newer Excel versions Power Pivot is already installed and you can enable it by going to:

文件>选项>高级>数据>启用数据分析加载项:Power Pivot，Power View和Power Map

File > Options > Advanced > Data > Enable Data Analysis add-ins: Power Pivot, Power View, and Power Map

好的，现在您有了Power Pivot，并且可以看到Power Pivot选项卡.请按照以下步骤操作:

Alright, so you have Power Pivot now and you can see Power Pivot tab. Please follow the steps below:

1. 选择数据，然后在Power Pivot选项卡上单击添加到数据模型"图标.
2. 在Power Pivot窗口中添加一列，该列将计算不同数量的 数据中的人员. = DISTINCTCOUNT([person])为其命名，例如 "DistPersNo". 这是至关重要的一步– Power Pivot使您能够 计算所选列中的唯一值.
3. 使用公式= [amount]/[DistPersNo]添加另一列，将其命名为 人均".
4. 在Power Pivot窗口中，单击PivotTable并将新的数据透视表添加到 您的工作表.
5. 在数据透视表中，将人员"添加到行中，并将数量"添加到值中.现在，如果您将"PersonAverages"添加到值(其总和)并过滤出资金"x1"，您将获得所需的结果，即值1100.

1. Select your data and click add to "data model" icon on Power Pivot tab.
2. In Power Pivot window add column which will count distinct number of persons in the data. =DISTINCTCOUNT([person]) name it for example "DistPersNo". This is crucial step – Power Pivot enables you to count unique values in selected column.
3. Add another column with formula =[amount]/[DistPersNo] name it "PersonAverages".
4. In Power Pivot window click PivotTable and add new pivot table to your worksheet.
5. In Pivot Table add 'persons' to rows and 'amount' to values. Now, if you add 'PersonAverages' to values (sum of it) and filter out fund 'x1' you will achieve desired result i.e. value of 1100.

希望有帮助.

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