2023学年完整公开课版PowerPivot之“WhatIf”假设分析

数据可视化PowerPivot之“What-If”假设分析1假设（What-If）分析概述23设置日期表/日期列4多项式回归分析PowerPivot之“What-If”假设分析目录调整基础数据.PowerPivot之“What-If”假设分析2假设（What-If）分析概述PARTONEPowerPivot之“What-If”假设分析3假设（What-If）分析概述假设（What-If）分析，也叫情景分析（Senarioanalysis）、敏感性分析（SensitivityAnalysis），是指当某个参数变量发生变化时，对某一个或一组关键指标影响程度的分析技术。通常，敏感性分析是指对单一因素进行分析；情景分析更多的多因素分析方法，多种因素可以组成“基准情景”、“最好情景”和“最坏情景”。假设分析允许分析师通过切片器按钮、表单控件等方式来选择或调整变量，作为”IF输入”。实质：将筛选条件中的数值（如上下限值、参数等），通过切片器按钮展现出来，从而允许报表阅读者能对DAX函数中的筛选条件中的一部分进行交互式调整，从而依据特定条件获得不同的交互式动态报表。PowerPivot之“What-If”假设分析4学习目标：1.掌握“参数表”的创建方法2.掌握最基础的“数值筛选”方法。3.掌握“高级筛选”4.掌握多种汇总方式：求和、平均、计数5.初步掌握FILTER函数（复杂筛选）的使用。6.灵活地开展What-IF假设分析。PowerPivot之“What-If”假设分析5PARTTWO调整基础数据PowerPivot之“What-If”假设分析6调整基础数据——数据集：销售费用数据集。费用数据介绍：我们希望查看下，超过某个阈值的销售费用记录的数目和金额、以及明细，以便我们能更好地了解销售费用的分布特点、对超出阈值的销售费用重点关注调查。PowerPivot之“What-If”假设分析7调整基础数据——数据分组：数据类型，通常大致上可以分为日期型、数值型（整形、实数、货币型）、文本型这三种数据类型。通过“日期表”的创建，我们已经熟悉了“日期分组”。那么在本案例中，我们来熟悉下“文本分组”与“数值分组”。离散数据和连续数据文本分组，通常针对的是离散数据，可以通过颗粒度不同的两列之间的对应关系，创建表格（一对多关系），或使用SWITCH函数来来进行分组。典型的文本分组，如将月份按照季度进行分组；将周一至周日，按照工作日/非工作日进行分组。数值分组，通常针对的是连续数据，且无法穷举，这就需要使用逻辑条件（大于、小于）进行比较。此时无法使用匹配列来创建关系，而是通过大于、小于等方式用DAX数据分析表达式来创建关系。PowerPivot之“What-If”假设分析8PARTTHREE数据筛选PowerPivot之“What-If”假设分析9数据筛选——PowerPivot自定义筛选Step1:在PowerPivot窗口中，选择“销售费用”列中的“筛选按钮”。PowerPivot之“What-If”假设分析10数据筛选——PowerPivot自定义筛选Step2:在弹出的筛选排序对话框中，选择“数字筛选器”—》“自定义筛选器”。PowerPivot之“What-If”假设分析11数据筛选——PowerPivot自定义筛选Step3:在弹出的“自定义筛选器”对话框中，选择“显示行，其中销售费用：大于或等于“对应的空白栏中输入5000。Step4:选中“销售费用”列，在PowerPivot窗口开始选项卡中点击“自动汇总”，分别选择添加总和/平均/计数三种汇总方式的简单计算字段。这里仅选择一个阈值5000，下图显示了筛选前后的，对应的数据集和汇总结果：PowerPivot之“What-If”假设分析12数据筛选——Excel表格高级筛选Step1:明确数据全集的列表区域。按住CTRL+A选中整个数据集。然后在Excel功能区“数据”选项卡中，点击“筛选”—》高级PowerPivot之“What-If”假设分析13数据筛选——Excel表格高级筛选Step2:明确数据子集的提取方式。在弹出的“高级筛选”对话框中，选择“方式—将筛选结果复制到其他位置”。Step3:明确筛选条件的条件区域。在“条件区域（C）”对应的空白栏中，选中J2：O3的区域，Excel自动识别出该区域的Excel表格名称“条件区域”，并用条件区域[#全部]表示。在本例中，我们设定的筛选条件为销售费用>5000。Step4:明确“复制到”的提取目标区域。在“复制到”对应的空白栏中，选中一个单元格，Excel将其作为提取目标区域的左上角。提取出的数据子集（记录行）如下图所示。PowerPivot之“What-If”假设分析14数据筛选——Excel表格高级筛选我们可以进一步对数据子集进行汇总（总和/平均/及数）。PowerPivot之“What-If”假设分析15数据筛选——PowerPivot参数表与筛选函数Step1:创建参数表。考虑可能交互使用的筛选阈值参数，创建参数表（Excel表格），并添加到PowerPivot数据模型当中。何为参数表？PowerPivot数据模型中所用的参数表，是指一个与数据模型其余部分并无”关系“的表格。但该参数表能提供为DAX函数内部所使用的参数，以供数据透视表用户，使用切片器按钮，来对DAX函数进行调整。例如，我们可以对所有的销售费用数据划分阈值来分区间，逐个区间地查看数据子集。由于销售费用的值特别多，我们希望对这些数字进行分析。从0至9559，我们根据对数据集的理解，分为多个区间。PowerPivot之“What-If”假设分析16数据筛选——PowerPivot参数表与筛选函数这个表称为参数表。也就是说，该列中的值，将用于DAX计算字段当中，通过在数据透视表界面中对参数表中的参数做出选择，来对数据加工的业务逻辑进行调整。和“销售费用表“类似，我们也通过链接表的方式，将其链接到PowerPivot数据模型当中。PowerPivot之“What-If”假设分析17数据筛选——PowerPivot参数表与筛选函数Step2:创建简单计算字段“金额阈值的最大值”。考虑到我们可能会使用切片器不恰当地同时选择两个按钮，如5000和8000两个按钮同时选中，从而形成两个筛选条件的“与”的关系。在PowerPivot窗口中，使用“自动汇总”即可轻松创建一个含有MAX函数的DAX表达式。生成一个计算字段：金额阈值的最大值=MAX([金额阈值]PowerPivot之“What-If”假设分析18数据筛选——PowerPivot参数表与筛选函数Step3:创建“超出阈值的销售费用总和”计算字段。这里使用CALCULATE函数，该函数包含两个核心组件：·汇总方式。这里我们选取[以下项目的总和销售费用]，作为汇总方式，也可以使用平均/计数。·筛选条件。这里我们使用FILTER()复杂筛选条件作为CACULATE函数的筛选参数。使用FILTER()筛选函数，FILTER函数的语法形式为：FILTER(<table>,<复杂筛选>)PowerPivot之“What-If”假设分析19PARTFOUR创建数据透视表PowerPivot之“What-If”假设分析20创建数据透视表基于当前的PowerPivot数据模型，生成数据透视表。注：字段列表会提示：“可能需要表之间的关系。”由于我们使用的是参数表，因此忽略此提示。PowerPivot之“What-If”假设分析21创建数据透视表与“超出阈值的销售费用总和”计算字段类似，我们还可以借助CACULATE()和FILTER()创建“超出阈值的销售费用平均值”和“超出阈值的销售费用计数”。这种带有可选参数的数据透视表，官方的说法称为“参数化”的报告。允许根据需要，交互式地查看重点。在时间有限的情况下，允许我们关注下重点销售记录的大致情况。并通过选择不同的阈值，了解销售费用金额的分布规律。通过上述方式，很容易地交互式了解费用开支的潜在超支情况。PowerPivot之“What-If”假设分析22“What-If假设分析”的公式和函数，并不易于理解。可以借助于Excel表格高级筛选的整个流程和组件，来