Excel和Power BI的数据分析有区别,如下图。
有很多伙伴希望能在Excel中应用数据模型功能,增加Excel大数据存储、运算能力。对于这个需求,我们在Excel 2016以上版本(专业增强版,学生家庭版不支持)直接可以实现,这里就是需要大家掌握Excel中Power Pivot加载程序的应用。
1. 启用Power Pivot
Power Pivot在Excel 作为一个“COM加载项”提供,默认没有启用。下面我们介绍启用改加载项的方法。
步骤1:在Excel的“文件”选项卡中选择【选项】,出现的对话窗中左边选择【加载项】,右侧窗口选择【管理:COM加载项】,然后点击【转到】按钮。
步骤2:出现的对话窗中勾选“Microsoft Power Pivotfor Excel”选项。
这里大家也可以选择其它ExcelPower 加载项。完成加载后可以在功能区上看到对应的工具选项卡。
步骤3:点击Power Pivot选项卡中的【管理】数据模型按钮(或者【数据】选项卡中的【管理数据模型】按钮),打开新的窗口,在其中可以查看加载的数据,完成建模设计工作。完成编辑可以随时退出窗口,不用单独保存,数据将与Excel同时保存。
2. Power Pivot 数据建模
PowerPivot中的数据有两个来源:
1)来自Power Query清洗转换后的数据。
2)直接从Power Pivot加载的数据。
这两种来源的数据都可以进行关系模型建立。当导入多个数据表后,可以在它的窗口中选择“关系视图”功能。在关系视图中,根据表格结构关系,直接拖放字段建立关系。
3. 度量值和计算列
与Power BI Desktop一样,Power Pivot也可以支持DAX表达式,完成数据列扩充,度量值计算。
① 创建计算列
我们以“订单明细”表为例,添加一列计算每行记录的金额。
在数据表上方输入公式,并将字段列名称改为“金额”。在浏览表格时会发现计算字段标题以“黑色”作为区别。
② 创建度量值
Power Pivot的度量值公式与Power BI中有一些区别:
-
公式中度量值名称后面需要加“ :”。
-
度量值结果可以显示在数据表下方的“计算区域”中。
这个结果是没有上下文筛选条件的总的结果。当度量值应用到数据透视表中,就会根据透视表行、列标签中的项目进行分解计算。
4. 基于数据模型的超级透视表
Excel中数据建模的结果,最直接应用于数据透视表。可以实现多表关联透视,我称之为超级透视。下面我们来看,基于数据模型创建透视表的过程。
① 选择数据模型数据源创建透视表。
② 应用数据模型中的字段列表构建透视分析,并基于透视表创建透视图。
③ 添加筛选切片器。
切片是是数据透视表中用户数据交互筛选的功能。基于数据模型中的字段创建切片器,可以让数据透视表可以按多个维度灵活筛选。
鼠标点选到数据透视表中,在功能区“数据透视表分析”选项卡中点击“插入切片器”。在切片器窗口中“全部”标签下,选择“类别”表:主类别,日期表:年度。
切片器还可以调整位置、大小、配色方案,详细方法大家可以参照《Excel数据管理:不加班的秘密》。
5. Excel数据模型导入Power BI
如果希望将Excel中的数据模型以更加丰富的可视化效果展现,并发布到网络进行分享,我们可以将模型导入到PowerBI Desktop,导入的内容包括数据、表关系、计算列、度量值。
微信扫一扫加关注,Office自学网官方微信公众号,
专注分享软件安装和视频教程,让你一号在手,办公无忧!