Excel Power Query与Power Pivot结合：TOP-N对象贡献度分析

TOP-N分析法通常用来分析客户、店铺或产品对于整体的贡献度问题。本节内容我们需要指定N个门店，分析这N个门店的产品销售总金额或毛利润对于整体的贡献度，如图所示。

在这个模型中，我们可以根据实际业务的需求，去个性化地选择以产品销售总金额或毛利润为观察对象，分析每个大区的前3名、前5名、前10名及所有门店的业绩对于整体业绩的贡献情况。

该模型主要的功能在于可以根据选择的指标动态地进行筛选，方便我们实时把握贡献最大的TOP-N的门店，开展有针对性的经营活动。下面介绍一下这个模型的具体的建立步骤。

第一部分：数据获取和准备

从Excel工作簿中将所需要的各个表加载进来，原始数据如图所示。

第1步：新新建一个Excel工作簿，依次选择"数据"-"获取数据"-"来自文件"-"从Excel工作簿"选择。如图所示。

第2步：选择原始数据的 Excel工作簿，导入后，在“导航器”界面选择需要加载的工作表，单击“数据转换”按钮。如图所示。

注意：如果明细来自于多个表，则需要对多个表或者多个文件夹中的表使用Power Query进行合并；另外，数据如果不整洁，则要对数据进行清洗，可以参照更多的Power Query的数据清洗功能。

第3步：在Power Query中设置好各个列的数据格式后，选择“关闭并上载至”选项，将数据加载到Power Pivot数据模型中，需要注意的是，如不需要，可不将原始数据加载到工作表中。如图所示。

第4步：在弹出的对话框中选择“仅创建数据连接”按钮，并勾选“将此数据添加到数据模型”选项，最后单击“确定”按钮，就将数据加载到数据模型中了。

第二部分：建立数据模型

然后我们进入到Excel的Power Pivot数据模型中，

第1步：建立日期表。日期表的作用是通过日期表可以对其他表的相关的日期进行筛选。依次选择“设计”-“日期表”-“新建”选项，Power Pivot就会新生成一个日期表，然后根据自己的需要进行修改。如图所示。

第2步：建立表间关系。表间关系如下图所示。由于此次的销售目标表暂时不需要，也可以不建立表间关系。

第三部分：建立分析维度

分析维度主要是通过DAX表达式来完成的。具体如下所示。

第1步：编写基本的度量值。

计算产品销售总金额和销售贡献度（占整体比重），度量值可以分别写为：

产品销售总金额: = SUM('订单明细表'[产品销售金额])

销售贡献度(占整体比重):   = DIVIDE([产品销售总金额],     CALCULATE([产品销售总金额],       ALL('门店表'[门店名称]))  )

计算毛利润和毛利润贡献度（占整体比重），度量值可以分别写为：

毛利润: = [产品销售总金额]- SUMX('订单明细表',     [产品销售数量] * RELATED('产品表'[产品成本价格]) )

毛利润贡献度(占整体比重): = DIVIDE([毛利润],  CALCULATE([毛利润], ALL('门店表'[门店名称])) )

第2步：建立参数表。

分别建立“前N名”和“排序依据”两个参数表。在工作表中先准备好相应的字段和值，再将其添加到数据模型中，这两个参数表不与其他任何表建立关系，如图11-2所示。如图所示。

第3步：编写计算各个大区门店产品销售总金额和毛利润排名的度量值。主要为筛选前N名做准备。度量值如下：

排名: =IF(HASONEVALUE('门店表'[门店名称]),      RANKX(      ALL('门店表'[门店名称]),      IF(MAX('排序依据'[排序依据])= "销售金额",         [产品销售总金额],         [毛利润])      ))

在上述度量值中，最重要的是RANKX函数的第二个参数。因为要使用“排序依据”这个切片器进行筛选，所以要使用IF函数配合判断切片器筛选的内容，并且匹配合适的排序依据。

第四部分：制作分析报表

将上述的数据使用数据透视表的方式制作数据报表（当然也可以使用链接回表或者多维数据集的方法制作报表）。

第1步：将上述准备好的度量值放置于数据透视表中，并且将相关的切片器添加到数据透视表中。但是当前使用“前N名”和“排序依据”这两个切片器还无法进行筛选，需要进行后续的设置。

第2步：为“排名”列设置升序排列。首先单击“门店名称”字段的筛选按钮，在弹出的下拉列表中选择“其他排序选项”选项，然后在弹出的“排序(门店名称)”对话框的“升序排序(A到Z)依据”下拉列表中选择“排名”选项，最后单击“确定”按钮，实现对每个大区的门店的排名升序排列，如图所示。

第3步：此时，可以根据“排序依据”筛选数据了，但是“前N名”切片器还无法进行工作。这里我们可以借助数据透视表的值筛选功能，给筛选设置一个规则即可。例如，当选择“前3名”时，筛选出前3名的数据。所以，度量值可以写为：

筛选条件: = IF([排名] <= MAX('前N名'[名次]), 1, 0)

第4步：单击“门店名称”字段的筛选按钮，在弹出的下拉列表中依次选择“值筛选”→“等于”选项，在弹出的“值筛选(门店名称)”对话框中进行设置。设置如图所示。

经过以上的几个步骤，这个查看TOP-N门店销售和利润贡献度的模型就建立完成了，核心点就是参数表的建立。最后在Power Pivot的管理界面中将不需要在数据透视表里显示的度量值“筛选条件”隐藏即可。

以下为福利部分

小必老师近期新出版的《Excel商务智能：Power Query和Power Pivot数据清洗、建模与分析实战》一书，在上市以来就取得不俗的表现，豆瓣上更是有了9.5分的评分。本书有50+的实战案例，有1G+的随书重难点章节的配套视频。

鉴于此，小必老师给广大的读者送来更多的福利。

更大的福利是，扫码以下的二维码，可以享受作者送出的隐形折扣，近于5折购书的资格，京东自营的隐形5折购书（只49元），直接进入京东无法享受该福利。快快扫码吧。

发布：刘恩惠

审核：陈歆懿

▼点击阅读原文，了解本书详情~