☰

Excel 数据透视表公式

tag-icon • tag-icon microsoft-excel worksheet-function pivot-table
Excel 数据透视表公式

我有一张包含如下所示数据的 Excel 表。我有显示部门时间和百分比以及总成本外部值的数据透视表。

如何创建一个公式，该公式作为数据透视表的一部分，引用百分比值（实际上是以总计的百分比显示的小时数）和外部总成本单元格来创建 CostShare 列？

             Total Cost $1200.00

Dept  Hours  Percent   CostShare
A        10     17%     $ 200.00
B        20     33%     $ 400.00
C        30     50%     $ 600.00
--------------------------------
Total    60    100%     $1200.00

我确信这可以通过谷歌找到，但我似乎总是找不到重点。如果能提供一个解释这一点的页面链接就足够了，非常感谢。

答案1

请选择数据透视表的一个单元格 > 数据透视表分析 >字段、项目和集合在计算组 > 计算字段。

然后您可以更改名称，=Percent*1200在公式中输入“ ”。

在此处输入图片描述

将数字的格式更改为以下 gif。

在此处输入图片描述

答案2

如果您的数据实际上像表格所示的那样简单，那么不使用数据透视表就更容易了。

在名称框中输入名称，为总成本单元格命名

图片:

我已给它命名了total_cost。

将数据格式化为表格。将光标放在数据中的任意位置并使用Ctrl+ T。

添加一个名为CostShare。

使用以下公式：

=[@Hours]*total_cost/SUM([Hours])

它看起来像这样：

图片:

结果如下：

图片:

如果您的数据实际上比您的帖子中的更复杂，那么如果您想在数据透视表中的另一个计算中引用计算度量值（如“列的百分比”或“总计的百分比”），您可能需要使用 PowerPivot 数据模型来创建度量值。

要记住的关键是，您在计算中使用的数据需要使用功能区上 Power Pivot 选项卡上的按钮位于数据模型中。

因此，像以前一样创建一个表，但添加一个列，将总成本放在每行上。像这样：

图片:

然后，启用 PowerPivot 插件（如果尚未启用），然后将表添加到数据模型。

PowerPivot 窗口打开后，将选项卡重命名为“数据”（或您选择的任何名称）。

创建三个度量（为了简单起见）：

一个简单的度量，即小时数列的总和：

Dept Hours:=SUM([Hours])

计算部门维度中列的百分比的度量：

Dept Hours % of Total:=[Dept Hours]/CALCULATE([Dept Hours],ALL(data[Dept]))

计算成本分摊的指标：

Cost Share:=[Dept Hours % of Total]*DISTINCT(data[TotalCost])

此后，您的 PowerPivot 窗口应如下所示：

图片:

如果您随后从中创建一个数据透视表，则可以将其设置为如下所示：

图片:

相关内容