很多人认为Excel超级透视表跟数据透视表是差不多的东西。其实，除了在最终的展示方式上基本一样，二者在数据分析上的实现方式是截然不同的。
本文试图先从总体上帮助你了解Power Pivot的原理，掌握Excel数据建模分析的框架。
首先我们从“Power Excel”说起。
Power Excel是我自己“创造”的一个概念，并不对应一个单独的产品或服务。我用这个概念来描述现在使用Excel进行数据处理/分析/展示的一种知识框架。
这是Power Excel的一个组成：
Excel
Power Query
Power Pivot
其中，Excel是我们大家熟悉的工具，当然，随着动态数组和LAMBDA函数的出现，要求我们越来越多的采用“函数式编程”的方式进行数据的处理。当然，采用传统方式仍然是可行的。
Power Query就是一个ETL工具（Extract，Transform，Load），作用就是抽取数据（Extract，例如，从不同的数据源获取需要的全部或部分数据），数据转换（Transform，包括数据清洗，根据业务规则进行加工等），加载（Load，将数据整合加载的需要的位置，比如Excel表格，Excel数据模型，Power BI数据集等）。
Power Pivot是数据分析工具，其结果是呈现在Excel工作表中，同时，其强大的分析能力依赖于背后的数据模型。
Power Query和Power Pivot本身是Excel的插件，可以在Excel中直接使用。从Excel 2016开始，它们被集成在Excel中，作为Excel的一部分功能。
注：Power Query和Power Pivot从很早以前就开始存在了，只不过最近几年才慢慢被越来越多的人熟悉。我以前写过一个Excel系列的“编年史”，有兴趣的朋友可以看Excel 2016/2019/2021/Office 365，到底应该使用哪个版本（1）：Excel的前世今生）
注：一开始，微软本来的方向是Power Query整合和处理数据，Power Pivot对数据建模分析，然后用Power View展示分析结果。后来发现这么做挺好，干脆将三者一整合，成了Power BI，现在Power BI成为了单独的服务产品，并且还有Power BI Desktop产品可以免费使用。不过Power BI里的Power Query和Power Pivot跟Excel中的几乎没有区别。
业务上的很多分析是普通的透视表不能完成的。
比如，
如果要对上表中的各区房价进行分析，用数据透视表只能进行平均值分析：
但是，平均值是很容易受极端值的影响的，个别超大或超小的值导致平均值的代表性不足。业务上经常需要进行中位数分析：
这个用普通数据透视表就无法做到。但是用超级透视表就可以很简单完成。具体做法请参见【不可能的数据透视表】如何用数据透视表计算中位数。
传统的透视表直接对Excel单元格区域或超级表中的数据进行分析。而超级透视表必须要求先将数据加入到数据模型中才行：
而数据模型的强大威力，实际上是基于其背后的DAX。
下图展示了进行中位数分析的过程：
数据模型非常简单，可以这样理解：
数据模型就是一组创建了关系的表格。
这些表格跟Excel中的表格非常类似：
简直一模一样。
这些表之间可以创建关系：
所谓关系就是两个表通过一个共同的列关联在一起。其作用就相当于在Excel中通过VLOOKUP等函数对两个表进行关联。这么做的好处是再也不需要将多个表通过函数合并为一张大表了，保留原来的结构，只要创建关系就好了。
我曾写过一篇文章：一个严肃的问题——还需要VLOOKUP函数吗？这里介绍了关系和VLOOKUP函数的关联。
DAX是英文 Data Analysis Expressions的缩写。
DAX是一门公式编程语言！
很复杂吧！
其实换句话说，DAX就是一系列的函数组成，可以处理各种数据计算。
数据模型中的计算就是由DAX进行的，然后通过超级透视表进行分析和展示。
DAX的“编程”就好像我们介绍过的Excel函数式编程一样。
而且，DAX函数本身就是发展自Excel，两者有大量的函数都是一模一样的的：
上图是DAX中的文本函数和日期函数，仔细看，有大量跟Excel中同名的函数。
我们可以使用这些函数创建辅助列（数据模型中叫做计算列）：
也可以创建度量值：比如上面例子的中位数，或者下面的例子：
这个创建度量值的区域是关联到模型中每个表格的计算区域，专门创建度量值。（关于度量值的介绍请看文末视频）。
度量值就好像是Excel中的自定义函数一样，通过公式定义了一种计算逻辑。但是这个度量值可以在超级透视表中进行展示：
在DAX中写公式和在Excel中写公式很相似。
这是简单的公式：
=MONTH([日期])这是复杂的公式：
=var nsrmtd =CALCULATE(SUM('NSR Data'[NSR]))var nsrytd =TOTALYTD(SUM('NSR Data'[NSR]),'Dim_Date'[Date])var selectedperiod =SELECTEDVALUE('Dim_Period'[Period],"MTD")var selectedManufacture =SELECTEDVALUE('Dim_Manufacturer'[Manufacturer])returnSWITCH(selectedperiod,"MTD", if(selectedManufacture="TCCC", nsrmtd),"YTD", if(selectedManufacture="TCCC", nsrytd))
不要觉得陌生，理解逻辑后很简单。其实跟现在Excel中写公式很相似，不信就看看下面的Excel公式：
=LET(products, SORT(UNIQUE(Funds[名称])),maxdate, MAXIFS(Funds[日期],Funds[名称],products),mindate, MINIFS(Funds[日期],Funds[名称],products),ev,SUMIFS(Funds[累计净值],Funds[名称],products,Funds[日期],maxdate),pv,SUMIFS(Funds[累计净值],Funds[名称],products,Funds[日期],mindate),nper, COUNTIF(Funds[名称],products),cagr, (ev/pv)^(1/nper)-1,rslt, HSTACK(products, cagr),rslt)
如果你不熟悉这种公式的写法，建议从现在起开始熟悉起来。因为，在Power Excel的框架下，普遍提出了“函数式编程”的概念，包括Excel，Power Query，Power Pivot都是如此，仍然固守原有的习惯，会导致在数据处理和分析上越来越困难。
下面我们展示几个例子来为大家介绍DAX或者超级透视表能做什么样的分析（仅仅是走马观花的展示，详细解释请关注后续的文章）
1. 文本透视
详细介绍参阅不可能的透视表之如何在透视表中显示文本
2. 客单价分析
详细介绍参阅客单价分析——不用Power Pivot，还真是不太容易搞定！
3. 日期函数应用-只分析1月份的数据占比
这里使用了这样的度量值：
Qty_Jan_%:=var datetable=CALENDARAUTO()var mindate = MINX(datetable,[Date])var adate = DATE(2022,2,1)var qty_jan =SUMX(CALCULATETABLE('供应明细','供应明细'[时间] < adate &&'供应明细'[时间] > mindate),[产品饮用量])returnDIVIDE(qty_jan, SUM('供应明细'[产品饮用量]))
4. 不同百分比的计算方式
通过一个简单的公式就可以做到：
Vol%:=DIVIDE([vol],CALCULATE([vol],ALL('产品表')))
5. 还可以非常方便的处理层级数据，例如组织结构，市场架构，BOM等数据：
6. 时间智能函数非常强大，例如，可以非常容易地统计分析月/季/年初（末）的数据：
只要使用简单的公式：
月末:=CLOSINGBALANCEMONTH(SUM('供应明细'[产品饮用量]),'日历'[Date])月初:=OPENINGBALANCEMONTH(SUM('供应明细'[产品饮用量]),'日历'[Date])季末:=CLOSINGBALANCEQUARTER(SUM('供应明细'[产品饮用量]),'日历'[Date])年末:=CLOSINGBALANCEYEAR(sum('供应明细'[产品饮用量]),'日历'[Date])
详细介绍请看视频
加入E学会，永久免费学习更多Excel应用技巧
http://www.tropic.com.cn/portal/learn/class_list
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单：知识库->自定义函数
面授培训 底部菜单：培训学习->面授培训
Excel企业应用 底部菜单：企业应用
也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。
联系客服
