每月一课:Excel中的数据模型

来源:澳洲CPA 时间:2022-05-30 作者:澳洲CPA编辑:无忧草
打印 RSS |
导读:教你玩转EXCEL,成为数据大师。

Excel的数据处理能力在过去十年有了显著提升,包括数据模型,今天着重介绍Power Pivot和DAX函数。

在Excel中创建数据模型时可使用Power Pivot加载项。它可同时导入多张表并创建称为“度量值”(Measures)的计算。度量值是以DAX(数据分析表达式)语言编写的计算公式,令Excel数据透视表(PivotTable)中的计算功能更加强大。

Power Pivot

通过点击“文件”(File)选项卡,然后点击左下角的“选项”(Options),可查看是否加载了Power Pivot。

点击左侧的“加载项”(Add-ins),在“管理:”(Manage)下拉菜单(对话框底部)中选择“COM加载项”(COM Add-ins)并点击“转到”(Go),确保勾选Microsoft Power Pivot for Excel并点击“确定”(OK),此时Power Pivot选项卡就会出现在功能区上。

示例

我们会用到4张Excel表格,分别是销售表、日期表、产品表和客户表。这4张表格均已使用主功能区的“套用表格格式”(Format as Table)选项进行了格式设置。

术语

Excel表格中的“列”和“行”分别对应数据库中的“字段”和“记录”。

数据模型通常包含两种类型的表格:

√ 事实表或数据表:这类表格往往保存了大量记录,如业务数据,通常包含要报告的值。在本示例中,销售表即属于事实表。

√ 维度表或查找表:这类表格中的记录较少且与特定主题相关。每条记录对应一个条目并具有唯一标识符。日期表、客户表和产品表都属于维度表。通常,每个维度表都会通过唯一代码与事实表相关联。

加载表格

点击销售表中的任一单元格,随后点击Power Pivot选项卡中的“添加到数据模型”(Add to Data Model)图标,即可打开Power Pivot窗口。

如果继续在Excel中操作,该窗口会始终保持打开状态。也可同时打开两个屏幕,一个屏幕显示Power Pivot,另一个显示Excel。对其他三张表格重复此步骤。

在Power Pivot窗口的“主页”(Home)选项卡中,点击“关系图视图”(Diagram View,选项卡右侧)图标,可显示每个表格的字段(列)列表(参见图1)。

我们需要创建每个维度表(位于图1上方)与销售表(位于图1下方)之间的关系。用鼠标点击tblDate列表中的“Dates”字段并拖曳到tblSale列表中的“Dates”字段上,即可为日期表创建关系。对tblProduct中的“Product ID”字段和tblCustomer中的“Customer ID”字段重复此步骤。

每月一课:Excel中的数据模型

图1

维度表通常位于事实表上方。图1显示了创建好的关系图。

我们需要对日期表(也称为日历表)进行定义,以便Power Pivot可以自动计算日期。

在关系图视图中,点击tblDate表,点击“设计”(Design)选项卡 点击“标记为日期表”(Mark as Date Table)图标 确保已选择“Dates”字段,然后点击“确定”(OK) 表格建立好关联后,就可以使用表格中的字段创建数据透视表。

在Power Pivot窗口的“主页”(Home)功能区,点击“数据透视表”(PivotTable)图标。选择“新工作表”(New Worksheet)并点击“确定”(OK)。数据透视表字段列表中将列出这4张表格。按图2所示拖曳字段。

每月一课:Excel中的数据模型

图2

在列(Columns)中添加客户表中的“Customer Category”字段,在行(Rows)中添加日期表和产品表中的字段,“Quantity”则是销售表中的字段。

这是刚开始使用Power Pivot时创建的典型报告。对于普通数据透视表,将字段拖动到“值”(Values)区域即可。若使用数据模型,最好在“值”区域使用度量值,而不是字段。

度量值

度量值是一种已定义名称的计算公式,可根据其在数据透视表中的位置进行计算。我们需要创建一个计算“总销售量”(Total Quantity Sold)的度量值,将销售表中的“Quantity”字段值相加。

在Excel公式中,引用的是单元格和区域,而在DAX公式中,引用的是表格和表格字段(列)。

在Excel窗口的Power Pivot选项卡中,点击“度量值”(Measures)下拉菜单并选择“新建度量值”(New Measure)。

图3显示了创建名为“总销售量”(Total Quantity Sold)的度量值的各项输入值。点击“确定”(OK)后,新的度量值就会出现在数据透视表中。图3还显示了部分更新后的报告。

每月一课:Excel中的数据模型

图3

请注意,图3中显示的度量值已根据“度量值”对话框中定义的格式进行了格式化。可通过点击“检查公式”(Check formula)按钮(公式区域正上方),对公式进行查错,这对于复杂的DAX公式尤为有用。

DAX函数:SUMX

SUMX是一个迭代函数,使用原有函数名后面加上X的形式,可对表中的记录(行)执行计算。在本示例中,我们需要计算出产品的总销售额。销售表中已有销售数量和单价,但没有总销售额。

每月一课:Excel中的数据模型

图4

SUMX函数可以逐行将数量乘以单价,然后再将每一行的计算结果相加,返回总销售额。使用“新建度量值”(New Measure)选项,“总销售额”(Total Sales)度量值的设置如图4所示。

DAX函数:Divide

创建好“总销售量”(Total Quantity Sold)和“总销售额”(Total Sales)度量值后,就可以计算平均售价。

DAX中有一个专门执行除法的函数,可避免除数为零时报错。我们将使用这个函数来计算平均售价。

每月一课:Excel中的数据模型

图5

图5显示了新度量值的设置以及部分数据透视表报告。引用度量值时,书写规范是不要包含表格名称。这意味着在DAX公式中,单独括号里的内容都是度量值,而前面有表格名称的括号,里面的内容都是表格中的字段,这使得DAX公式更易于编辑和理解。

度量值公式小贴士

在“度量值”对话框和公式区域,按下Ctrl键的同时滚动鼠标滚轮,可以放大公式。

点击这里,阅读英文原文并观看视频。

点击这里,阅读微信原文。

沪公网安备 31011802001002号