【原】Excel数据透视表不可能完成的透视：年累计月累计的技巧

作为Excel中常规的分析工具，数据透视表可以说是人人皆知，但是它只能适应简单的分析场景。对于复杂一些的分析场景，它总有各种各样的不尽如人意的地方，甚至干错就束手无策了。这是就需要用到“超级透视表”了。非常简单就可以搞定各种复杂分析场景

今天介绍一个Power Excel的应用案例：计算年累/月累。

这是一个很常见的工作，但是在传统的Excel框架中，几乎不可能完成。

要求很简单：

左表是源数据，记录了不同产品的销售明细。右表是结果表，要求汇总不同产品在每月的销量，月累计销量以及年累计销量。

所谓月累计就是从每月1号开始累计，比如，

1号的月累=1号销量

2号的月累=1号销量+2号销量

......

而年累就是从1月1号开始，

3月8号的年累=1月1号销量+1月2号销量+......+3月8号销量。

传统实现方法的问题

---

传统上，我们需要在源数据中添加辅助列，

其中年累的公式如下：

=SUMIFS(I:I,H:H,"<="&H3,G:G, G3)

月累的公式类似，不过需要多加一个条件：

=SUMIFS(I:I,H:H,"<="&H3,H:H,">"&EOMONTH(H3,-1),G:G, G3)

然后通过透视表得到汇总后的年累月累，

但是，在这个透视表中，涉及到月份或者年度汇总时，比如图中A产品5月的合计月累和年累就是一个错误的结果。

这是普通透视表几乎无法解决的问题。

超级透视表方案

---

这就是我们提倡“Power Excel”方案的原因。在Power Excel中，我们综合利用Excel，Power Query，Power Pivot，原来无法解决的问题都可以迎刃而解。

首先将源数据添加到数据模型中：

然后进入数据模型，在设计选项卡中，添加一个日期表（其实这一步不是必须的，但是这是一个强烈推荐的步骤）：

结果如下：

在源数据和日期表的对应日期字段上创建关系：

在Excel中的Power Pivot选项卡中创建度量值：

这是MTD度量值，表示月累计，使用的公式时：

=TOTALMTD(SUM(Salesdata[数量]),'Calendar'[Date])

使用同样的方法创建YTD度量值：

在主页选项卡中，创建数据透视表：

在Excel中，设置透视表布局：

得到透视表如下：

现在每个月份和年份的MTD和YTD都计算正确了。

---

详细解释请看视频

---

加入E学会，永久免费学习更多Excel应用技巧

http://www./portal/learn/class_list

详情咨询客服（底部菜单-知识库-客服）

Excel+Power Query+Power Pivot+Power BI

---

Power Excel 知识库    按照以下方式进入知识库学习

Excel函数   底部菜单：知识库->Excel函数

自定义函数  底部菜单：知识库->自定义函数

Excel如何做  底部菜单：知识库->Excel如何做

面授培训  底部菜单：培训学习->面授培训

Excel企业应用  底部菜单：企业应用

也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。