Power Pivot 实现数据建模

一、简介

Excel中的透视表适合小规模数据；如果想在稍微大一些的数据中进行高性能透视表分析，就要使用Power Pivot；再大一些数据，可能就需要大数据分析服务来进行分析。

Power Pivot，可以让没有技术背景的企业业务人员快速通过Excel进行数据建模，执行复杂数据分析，制作可自动更新的企业级数据报告。

传统Excel对比Power Pivot

Mac版本的excel没有power pivot 功能，所以同样用不了power bi。

二、创建一个简单的多表数据模型

第一种方法 VLOOKUP

VLOOKUP(a表匹配字段，b表查询字段 需匹配字段作为第一被选择的列，b表匹配结果列，0或1表示是否精确匹配)

功能：在表格的首列查找指定的数值，并返回表格当前行中指定列处的数值。

结构：=VLOOKUP(查找值,查找区域,列序数,匹配条件)

解释：=VLOOKUP(找谁,在哪里找,第几列,0或1)

说明：

1、第一参数：查找值（找谁），比如说根据【姓名】来查找【成绩】，【姓名】就是查找值

2、第二参数：查找区域（在哪里找），查找的数据区域也就是所选择的区域，注意所选择的区域要根据查找值位于第一列开始选择，比如说根据【姓名】来查找【成绩】，那数据表所选的区域要从【姓名】列开始选择。建议设置为绝对引用，在选定区域后按F4键就可以快速切换，就是在行和列的前面添加$符号，拖动公式时，区域就不会发生改变；

3、第三参数：列序数（第几列），也就是返回的结果在数据表中位于第几列，包含隐藏的列；

4、第四参数：匹配条件（0或1），若为0或FALSE代表精确匹配，1或TRUE代表近似匹配；

注：查找值在数据表中多次出现，导致有多个结果，函数仅仅会返回第一个找到的结果。

VLOOKUP函数16种经典用法（史上最全，记得收藏） - 知乎

第二种方法Power Query

power query的合并查询

第三种方法 Power Pivot多表查询

1.导入数据

 管理数据模型进入power pivot界面

主页--从其他源--选择Excel导入表  导入后界面分为两部分：数据预览区域和计算区域。 计算区域可以对预览区域的数据进行一些计算，比如求和等。

可以重复上述步骤导入多张数据表。

 Excel求和只会有加和数字，但是Power Pivot会有文字+求和数字，这是Excel函数和Power Pivot DAX函数的区别。 

点击求和数字，可以修改显示文字和数字格式。

2.两表连线建立关系 

两张表能够连线建立关系必须满足如下

（1）两张表的连线字段必须一张表是唯一值，另一张表唯一值或重复值，即1：N。不能够N：N

1. 导入多张数据表
2. 点击“关系图视图”，会进入多表界面，显示每张表的所有字段。
3. 鼠标左键将关联字段连线。就会出现联系线段，线段三个内容 1 * 箭头。1和* 表示连线字段哪边是唯一值，哪边是重复值；箭头代表箭尾的表可以查询箭头指向表的数据。反之不可以调用。只有唯一值的表可以调用*值的表。详述见下
4. 连线建立后，点击数据透视表就会带着此数据模型回到Excel透视表界面。

 Power Pivot中连线成功的表使用方法

（1）连线成功后，会自动分配1端表和*端表，1端表所有字段可以作为“行”同时让 *端表的字段作为 “值”，反之*端表作为“行”同时让1端表作为“值”会报错，但是单独*端表作为“行”同时作为“值”没问题。

如下图，”行“中为两个1端的字段，“值”为*端的字段。

 3.数据透视表

两张表建立关系后，点击数据透视图回到Excel数据透视表界面。

4.数据透视图 

数据透视表和数据透视图，如果原始表格数据发生变化，即使未保存，此时点击Power Pivot菜单中的刷新也会同步变化统计值。

刷新下拉箭头，全部刷新是指会将所有表格都进行比对刷新。

1.给关心的字段添加切片器。

   左键--添加切片器

 2.添加透视图

选中数据透视表中某行数据--点击数据透视图--选择类型--插入到制定位置

5.Power View

Power Pivot数据模型确定以后，在Excel界面点击插入--Power View即可进入view界面。然后选择字段--选择图标类型，即可生成统计图。

如下，虽然两个图标单独建立，但是之间也会有交互。比如选择第一图的北区，那么第二图也会显示北区的各产品分类的数值。

三、Power Pivot学习

1.Power Pivot界面认知 

菜单栏

1. 粘贴 可以将数据粘贴到Pivot，适用于数据不变化的情况。比如省份区域对应表。 
2. 显示隐藏 可以右键列选择将某列隐藏，此时关系图界面、数据透表界面等的字段也会隐藏；可以通过此功能显示出隐藏的字段。常用与隐藏辅助列。

 

 设计栏

1. 创建关系等同于主页下面的关系图视图，用来连线；管理关系既看到有哪些关系。不常用，关系图视图更直观。
2. 表属性。可以显示隐藏某些字段。想要时勾选，不想要时去勾选。  一般在导入数据时有些字段不想要会去掉勾选，导入后如果需要去掉的字段此时可通过表属性再次勾选去勾选的字段；如果要对某列数据进行筛选，也在表属性设置，这样导入的数据就不会包含筛选掉的数据。如果导入后通过下拉列筛选，数据只是隐藏，透视表还是会进行汇总统计。

 高级

1. 默认字符集、表行为常在和view交互时使用
2. 同义词 给字段起个别名 。view可以实现交互式问答，此时问答可以使用别名。比如字段英文、字段不同地方的方言等。

2.Power Pivot 导入数据

导入数据三个方法。被导入数据需要关闭才能导入成功。

（1）从其他源导入

（2）复制粘贴 适用于数据不变化的情况。比如省份区域对应表

（3）链接表的形式

链接表

添加到数据模型既是链接表形式。

链接表形式常用作改动较多的表，此时透视表也在此sheet画出，如果改动直接刷新透视表即可更新。如果不是链接表形似，改动数据，需要回到pivot界面点击菜单栏更新才能将数据更新到透视表。

如果多张表中有链接表形式上传的sheet表数据，先打开此链接表sheet，选择添加到数据模型，然后在打开的数据模型中导入其他数据表，此时多表在一个数据模型pivot界面；如果先打开空白表，点击管理数据模型进入pivot界面导入非链接形式的表形成一个数据模型，再打开链接表，点击添加到数据模型形成另一个数据模型，此时会有两个pivot界面数据模型，因此建议第一种方式。

DAX函数不同于excel函数=开始开头调用公式，而是以以“计算值的名称:= ”开头， ‘’单引号作为引用表中某单元格或某行汇总数据的智能感知。

3.计算列和计算字段 

计算列为新增列，对某行不同列的数据进行计算；选中新增列单元格写DAX公式，此公式会自动运用到整个新增列，自动进行所有计算，不同于excel第一个单元格写公式，然后拖动选中整个列才能计算。

计算字段或度量值为对某列所有行汇总或者某些单元格值进行计算 。计算结果会自动根据切片器或者分组而变化。

 

度量值：比如计算订单平均金额，就适合使用度量值。

先汇总求一个总订单数，再汇总求一个总金额数，再新建一个度量值计算两者的商。

度量值和计算列都可以实现某个需求时，优先推荐使用度量值，节省内存和空间。

除了可以在power pivot界面计算区域新建度量值，也可以在excel界面新建度量值。 

4.按列排序

如果透视表中 “行” 名称没有按照想要的顺序排列，可以通过按列排序进行处理。

（1）增加一列 

产品分类--筛选指定类别--给每个类别添加一个大小数字

 

 （2）选中产品分类---按列排序---选择排列辅助字段

 

（3） 透视表刷新后发现按照指定辅助列数值大小进行了排序

    此时可以隐藏辅助排序列。

 

5.Power Pivot的关系

两张表能够连线建立关系必须满足如下

（1）两张表的连线字段必须一张表是唯一值，另一张表唯一值或重复值，即1：N。不能够N：N。比如上图的省份区域表的省份字段，每个值都只有一个；销售记录表的客户省份，会有很多客户是同一个省份，有重复值。身份和客户省份建立连接后，1端为省份区域表，*端为销售记录表。

（2）建立连接后，1端可以作为透视表的行，*作为列值，反之不可以，即1端表的所有字段可以查询*端表的所有字段的汇总值。

（3）可以按照箭头跨表控制

     按照箭头走向，可以跨表控制。 箭头走向连在一起的表，不需要再手动建立联系，建立的话会使用虚线表示没必要建立联系。

6.DAX函数

使用规则

 

（1）注意使用英文字符 

（2）点击计算列单元格 输入框输入= 鼠标选择其他单元格进行计算；点击计算区域 输入框输入 "名称:= "进行计算

（3）点击计算列单元格 输入框输入='或[  单引号会提示所有表的字段；[ 只提示当前表的字段

（4）DAX运算符

• [金额]&“元” 文本连接

   

• AND OR等

   下图为excel中用法，DAX中and or只能两个参数，如果需要多个判断可以嵌套and。

    

    DAX中常用 if来多条件判断，而不是and 。  if中可以使用&&表示and，||表示或。

      

常见函数 

在输入框输入函数名，会自动提示。

related、relatedtable 用在已经建立关系的表中。relatedtable是将建立联系的字段作为分类字段，然后获取到多段的数据进行处理。

 calculate特性：不受自身切片器控制，是指calculate函数内部的筛选条件作为切片器时，此切片器改变不影响calculate计算结果。比如下图中 是否订单作为透视表中的切片器时，选择0或1，calculate的计算结果都一样。

数据分析示例

增长率

（1）如下 ，差异百分比会计算出同比、环比增长率。需要表格里面有每月数据和两年数据，多年数据才能计算同比；多月数据才能计算每月的同比。 

（2）总计百分比可以计算出每个单元格占某列总计的百分比。

（3）父行汇总的百分比。  会计算每个分组中每个单元格占本分组的百分比，而不是占全部的百分比

 

设计--分类汇总--在组的底部显示所有分类汇总 会为每个分组生成一个汇总值

产品分析 

KPI 

在power pivot界面中的度量值，右键--kpi 如下图。 

（1）如果目标值选度量值。度量值为分母，选定的基本字段为分子。会根据计算结果显示不同颜色 

（2）如果目标值为绝对值。需要选定的基本字段为百分比。绝对值修改为1.

 

此时增加了kpi的度量值，可以在透视表增加一列状态。