Excel超级透视表：用CALCULATE函数按条件汇总

  文前推荐   

超级透视表1：第一次感受超级透视表

超级透视表2：做KPI分析

超级透视表3：跨多表查找非常简单

PQ教程：提取最新报价

编按：

今天我们学习Excel超级透视表中最重要的函数CALCULATE的简化用法。

有三张工作表，分别是“小组”“产品表”“销售明细”，如下：

需要汇总出各小组一类产品的销售数据，如下：

如果不增加度量值直接用Power Pivot创建数据透视表，必须使用筛选或者切片器才能得到类似结果：

下面我们介绍新增“一类产品销售总额”度量值的方法进行统计。

Step 01 将三张工作表都添加到数据模型，并建立关系。

对此步不了解的可以点此查看。

求一类产品销售总额在透视表中涉及到两个过程：筛选出类别等于一类的销售金额、汇总产品销售金额。

产品销售金额可以用SUM函数汇总，=SUM('销售明细'[销售金额])

筛选可用类似Excel中的条件表达式，'产品表'[产品类别]='一类'。

再用CALCULATE函数把两者组合，一类产品销售总额:=CALCULATE(SUM('销售明细'[销售金额]),'产品表'[产品类别]='一类')

Step 02 在销售明细表中新建一个度量值“一类产品销售总额”。

Step 03 单击“主页—数据透视表”创建一个透视表。将度量值“一类产品销售总额”拖入“值”列表框，将“小组”“产品名称”拖入“行”列表框中即可。

操作很简单，主要是用CALCULATE函数建立了新的度量值。

CALCULATE，意为计算，可以按条件统计数据，其函数语法：

=CALCULATE(计值参数,[筛选器1], [筛选器2],…)

计值参数必须有，可以是表达式，譬如上面的SUM('销售明细'[销售金额]),也可以是一个度量值。在上方的案例中，如果我们提前创建一个度量值“产品销售总额”，产品销售总额:= SUM('销售明细'[销售金额]),则一类产品销售总额度量值可以写成“一类产品销售总额:=CALCULATE([产品销售金额]),'产品表'[产品类别]='一类')”。

筛选器可选。完整的筛选器用FILTER函数表达,如下：

一类产品销售总额:=CALCULATE([产品销售金额]),FILTER(ALL('产品表'[产品类别]),'产品表'[产品类别]='一类')

当判断式的列来自表中（如“[产品类别]”来自产品表），条件是固定的（如“一类”），则可以简化表达，略去FILTER函数，直接用逻辑符号进行判断（如'产品表'[产品类别]='一类')。