Excel数据分析(Power BI)之一 创建数据模型

上一节我们介绍了Power BI的准备知识(阅读:Power BI的准备知识—将Excel作为数据库)。本节我们介绍如何创建数据模型。

数据模型可以为多个数据表创建关系,它运行在内存中,对数据表的数据进行压缩,所以可以处理多达数百万行数据。

数据模型是多个数据表和之间关系的集合,为Power BI提供了基础结构。本节我们介绍如何通过Pivot Table从多个表中进行数据汇总分析。

注:Power BI从Excel 2013中可以使用,但是Excel 2013中有些功能需要激活,有些功能需要特殊安装。在Excel 2016中,这些功能已经直接集成了。如果没有特别说明,本章及以后的演示都通过Excel 2016完成。

注:在Excel 2013中如何激活Power BI各项功能,请阅读本期公众号的扩展阅读文章:

如何在Excel 2013中激活Power BI

01

创建数据模型

为了表之间的关系,我们用一个简单的例子创建数据模型。在这个例子中,我们根据两个数据表(合同表和客户信息表)创建数据模型。这两个表经过了规范化设计,意味着消除了其中的冗余数据。例如,我们在客户表中不显示客户名称,而是使用客户代码。然后我们在合同表和客户信息表中建立关系,这样就可以根据客户代码从客户信息表中取得客户名称等信息了。

创建一个使用数据模型的数据透视表的步骤如下:

  1. 将两个数据区域转换为表(Table),使用Ctrl+T快捷键(具体见上一节:Power BI的准备知识—将Excel作为数据库)

  2. 点击合同表的任意单元格,在“表格工具-设计”菜单中,最左边的“表名称”中,输入“contracts”,同样方法,将另一个表名称改为“customers”

  3. 选中“contracts”表,在“插入”菜单中,点击“数据透视表”,在“创建数据透视表”对话框中,勾选“将此数据添加到数据模型”,点击“确定”。

  4. 在右边的“数据透视表字段”面板中,选择“全部”,然后将customers表中“客户代码”列拖拽到列字段中,将contracts表中的“金额”字段拖拽到值字段中。注意,会出现“可能需要表之间的关系”,点击“创建”
  5. 在出现的对话框中,第一个表选择contracts,列选择“客户代码”(外来,即外键)。第二个表选择customers,相关列选择“客户代码”(主要,即主键)。点击“确定”。

    注:主键和外键的名称可以不相同。

  6. 将contracts表中的产品名称拖拽到透视表的行字段,我们就得到了不同产品在不同客户中的销售额汇总

  7. 你可以象操作普通透视表一样操作这个基于数据模型的数据表,进行各种汇总分析。

02

添加计算列

可以在PowerPivot中添加计算列(需要注意的是,跟普通透视表添加计算列的方法不同)。下面的例子演示了如何添加计算列。
  1. 选择“Power Pivot”菜单,点击“管理”

  2. 在弹出的Excel文件中,选择“contracts”

  3. 注意该表的最右边一列,名称叫做“添加列”

  4. 选择该列的任意单元格,输入“=”,开始一个公式

  5. 鼠标点选“金额”列,然后输入“/12”

  6. 按回车键,输入完成

  7. 在该列上点击鼠标右键,选择“重命名列”,将名称修改为 “月均销售额”。如下图:

03

创建新度量(Calculated Field)

首次修改透视表结果:将“产品名称”列从行字段移走,将“客户名称”列从列字段移到行字段。

点击“Power Pivot”菜单,点击“度量值”下拉箭头,点击“新建度量”,

将“度量值名称”修改为“每单平均金额”。在“公式”处输入公式

=SUM(contracts[金额])/COUNTROWS

点击确定,结果如下图

04

总结

本章通过一个简单的例子介绍了如何创建数据模型,以及如何在模型(Power Pivot)中创建计算列和度量值,希望能够让大家可以尽快上手,建立对Power Pivot和数据模型的直观理解。如何管理模型,以及计算列和度量值会在以后的章节中深入介绍。
注:取得本文案例文件的方式:
  1. 关注本公众号

  2. 点击底部菜单“联系客服”,与客服取得联系,索取“Power BI - 创建数据模型”案例文件

点个赞

再走吧

(0)

相关推荐