执行概要

下载数据集 在这里 来跟随本教程.

皇帝的新衣:Excel 力量主教程

跨越金融领域的各个领域和子领域, 财务分析, 金融市场, 金融投资, 微软 Excel 为王. 随着的到来和指数增长 大数据, 然而, 经过数十年的数据汇总和积累, 廉价云存储的出现和物联网的兴起.e., 电子商务、社交媒体和设备互联-Excel的传统功能和能力已经被推到了极限.

更具体地说, 老一代Excel的基础结构和处理限制，例如它的行限制为1,048,576行, 或者在大数据集不可避免的处理速度变慢, 数据表和相互关联的电子表格, 降低了其作为有效大数据工具的可用性. 然而在2010年，微软为Excel增加了一个新的维度，叫做 力量主. 力量主为Excel提供了下一代商业智能和商业分析功能, 结合, 并在不影响处理速度的情况下分析几乎无限的数据集. 尽管它是八年前发布的, 然而, 大多数金融分析师仍然不知道如何使用Excel 力量主, 许多人甚至不知道它的存在.

在本文中, 我将向您展示如何使用力量主基础知识来克服常见的Excel问题，并通过一些示例来查看该软件的其他关键优势. 本力量主教程旨在指导您使用该工具可以实现的功能, 最后, 它将探索一些示例用例，其中力量主经常被证明是无价的.

什么是力量支点?它为什么有用?

力量主是微软Excel的一个功能，是作为Excel 2010和2013的一个插件引入的, 现在是Excel 2016和365的原生功能. 像微软 解释了, 力量主 for Excel "使您能够将来自多个数据源的数百万行数据导入单个Excel工作簿, 创建异构数据之间的关系, 使用公式创建计算列和度量, 构建数据透视表和数据透视图, 然后进一步分析数据，这样您就可以在不需要IT帮助的情况下做出及时的业务决策.”

微软在力量主中使用的主要表达式语言是DAX(数据分析表达式), 虽然其他可以在特定情况下使用. 再一次。, 正如微软解释的那样, “DAX是函数的集合, 运营商, 以及可以在公式中使用的常数, 或表达, 计算并返回一个或多个值. 更简单地说，DAX帮助您从模型中已有的数据创建新信息.幸运的是，对于那些已经熟悉 Excel， DAX公式看起来很熟悉，因为许多公式具有类似的语法(例如.g., 总和, 平均, TRUNC).

为了清楚起见，使用力量主和使用. 基本的Excel可以总结如下:

• 它允许您导入和操作数亿行数据，而Excel的硬约束只有100多万行.
• 它允许您将来自多个数据源的数据导入到单个源工作簿中，而不必创建受版本控制和可转移性问题影响的多个源工作表.
• 它允许您操作导入的数据, 分析它, 并得出结论，而不会减慢你的电脑到蜗牛的速度.
• 它允许您使用数据透视图和权力BI可视化数据.

在以下部分中, 我将逐一介绍以上内容，并向您展示Excel中的力量主如何发挥作用.

如何使用力量支点

1)导入大型数据集

如前所述, Excel的主要限制之一是处理超大的数据集. 幸运的是，Excel现在可以将超过100万行的限制直接加载到力量主中.

为了证明这一点, 我为一家体育用品零售商生成了一个样本数据集，其中包含9个不同产品类别和4个地区的两年销售额. 结果数据集有200万行.

使用 Data 选项卡，我创建了 新的查询 从CSV文件(参见 创建新查询 下图). 这个功能过去被称为PowerQuery, 但从Excel 2016和Excel 365开始, 更紧密地集成到Excel的Data选项卡中.

从Excel中的空白工作簿到将所有200万行加载到力量主中, 大约花了一分钟! 注意，通过将第一行提升为列名，我能够执行一些简单的数据格式化. 在过去的几年里, Power Query功能已经从一个Excel外接程序大大改进为工具栏上数据选项卡的一个紧密集成的部分. 功率查询可支点, 平, 净化, 并通过它的一套选项和自己的语言来塑造你的数据, M.

2)从多个数据源导入数据

力量主 for Excel的另一个主要优点是能够轻松地从多个数据源导入数据. 以前，我们中的许多人为各种数据源创建了多个工作表. 通常，这个过程涉及编写VBA代码并从这些不同的源复制/粘贴. 我们很幸运, 虽然, 力量主允许您将来自不同数据源的数据直接导入到Excel中，而不必遇到上述问题.

使用表1中的Query函数，我们可以从以下任何来源提取:

• 微软Azure
• SQL Server
• Teradata
• 脸谱网
• Salesforce
• JSON文件
• Excel工作簿
• ……以及更多

进一步, 可以在Query函数或力量主窗口中组合多个数据源以集成数据. 例如, 您可以从Excel工作簿中提取生产成本数据，并通过查询到力量主从SQL服务器中提取实际销售结果. 从那里, 您可以通过匹配生产批号来组合这两个数据集，以生成每单位毛利率.

3)处理大型数据集

力量主在Excel中的另一个关键优势是能够操作和处理大型数据集，从而得出相关的结论和分析. 我将在下面运行几个常见示例，让您了解该工具的强大功能.

措施

Excel爱好者无疑会同意数据透视表是最有用的工具之一, 与此同时, 这是我们所做的最令人沮丧的任务之一. 尤其在处理更大的数据集时令人沮丧. 幸运的是, 力量主 for Excel允许我们在处理大型数据集时轻松快速地创建数据透视表.

在下图中，标题为 创建措施，注意力量主窗口是如何分成两个窗格的. 顶部窗格包含数据，底部窗格包含度量. 度量是跨整个数据集执行的计算. 我通过在突出显示的单元格中键入一个度量.

销售总额:=总和('会计数据'[金额])

这将创建一个跨Amount列求和的新度量. 同样，我可以在下面的单元格中键入另一个度量

平均销售额:= Average('会计数据'[金额])

从这里开始，看看在一个大型数据集上创建一个熟悉的数据透视表有多快.

维度表

作为财务分析师使用Excel, 我们变得善于使用复杂的公式来使技术屈从于我们的意志. 我们的主人 VLOOKUP, 条件求和，甚至是可怕的 指数(匹配()). 然而，通过使用力量主，我们可以将其中的大部分抛到窗外.

为了演示这个功能, 我创建了一个小参考表，将每个类别分配给一个类型. 通过选择“添加到数据模型”，此表将被加载到力量主中(参见 将用户创建的表添加到力量主模型 上图).

我还创建了一个与我们的数据集一起使用的日期表(参见 创建日期表 下图). 力量主 for Excel可以轻松地快速创建日期表，以便按月合并, 季度, 一周中的每一天. 用户还可以创建一个更自定义的日期表来按周分析, 财政年, 或者任何特定于组织的分组.

计算列

除了度量之外，还有另一种类型的计算:计算列. Excel用户可以轻松地编写这些公式, 因为它们非常类似于在数据表中编写公式. 我在下面创建了一个新的计算列(参见 创建计算列 )，它按金额对会计数据表进行排序. 低于50美元的销售被标记为“小”，其他的都被标记为“大”.“这个公式是不是很直观?

的关系

然后，我们可以使用Diagram View在Accounting Data表的Category字段和Category表的Category字段之间创建关系. 另外, 我们可以定义Accounting Data表的Sales Date字段和Calendar表的Date字段之间的关系.

现在，没有任何 条件求和 or VLOOKUP 功能需要, 我们可以创建一个数据透视表，按年计算总销售额, 和类型, 带有事务大小切片器.

或者，我们可以使用新的Calendar表创建一周中每天的平均销售额图表.

虽然这个图表看起来很简单, 令人印象深刻的是，创建超过200万行数据的合并只用了不到10秒的时间, 无需向销售数据添加新列.

同时能够执行所有这些合并报告场景, 我们仍然可以向下钻取到单独的行项. 我们保留了高度精细的数据.

先进的功能

到目前为止，我所展示的大多数分析都是相对简单的计算. 现在，我想演示一下这个平台的一些更高级的功能.

一次情报

经常, 当我们检查财务结果时, 我们希望将其与上一年的可比时间框架进行比较. 力量主有一些内置的时间智能功能.

去年同期销售额:=CALCULATE([总销售额]，SAMEPERIODLASTYEAR('日历'[日期]))

销售额同比增长:=if(not(ISBLANK([去年同期销售额]))),([总销售额]/[去年同期销售额],空白())

例如, 只需在力量主中的Accounting Data表中添加上述两个度量，我就可以在几次单击中生成以下数据透视表.

不匹配的粒度

作为一名金融分析师，我经常要解决的一个问题是不匹配的粒度. 在我们的例子中, 实际销售数据显示到类别级别, 但是让我们准备一个只考虑季节的预算. 进一步这种不匹配，我们将准备一个季度预算，即使通过销售数据是每日.

使用Excel的力量主，这种不一致很容易解决. 中创建两个额外的引用表或维度表 数据库 命名法, 现在我们可以创建适当的关系来分析实际销售额和预算金额.

在Excel中，下面的数据透视表可以快速地组合在一起.

进一步, 我们可以定义计算实际销售额和预算销售额之间差异的新方法，如下所示:

实际与预算差异:=除以([总销售额]，[总预算销售额])-1

使用此度量，我们可以在数据透视表中显示方差.

占总数的百分比

最后，让我们检查特定类别的销售额占所有销售额的百分比.g., 品类对整体销售额的贡献), 以及某一特定品类的销售额占同类型所有销售额的百分比(e.g.，品类对季节性销售的贡献). 我创建了以下两个衡量标准:

总销售额占总销售额的百分比:=[总销售额]/CALCULATE([总销售额]，All('会计数据'))
总销售额占类型的百分比:=[总销售额]/CALCULATE([总销售额]),所有(的会计数据(类别)))

这些度量现在可以部署在一个新的数据透视表中:

注意计算是如何执行的 这两个 类别和季节类型级别. 我喜欢在如此大的数据集上快速而轻松地执行这些计算. 这些只是power Pivot的优雅和绝对计算能力的几个例子.

压缩

另一个好处是文件大小缩小. 原来的文件大小是91MB，现在是4MB以下. 这是原始文件的96%的压缩.

这是怎么发生的?? 力量主使用 xVelocity 引擎来压缩数据. 简单地说，数据存储在列中而不是行中. 这种存储方法允许计算机压缩重复的值. 在我们的示例数据集中，在所有200万行中只有四个区域重复. 力量主 for Excel可以更有效地存储这些数据. 结果是，对于具有许多重复值的数据，存储这些数据的成本要低得多.

需要注意的一点是，我在这个样本数据集中使用了整美元的金额. 如果我加了两个小数点来表示分, 压缩效果将减少到原始文件大小的80%，这仍然令人印象深刻.

介绍表格

力量主模型也可以扩展到整个企业. 假设您建立了一个力量主模型，开始在组织中获得许多用户, 或者数据增长到一千万行, 或两个. 此时，您可能不希望有30个不同的用户刷新模型或进行更改. 该模型可以无缝地转换为SSAS表格. 所有的表和关系都被保留, 但是现在你可以控制刷新频率了, 分配角色(e).g., 只读, 读取和处理)给不同的用户, 并且只部署一个链接到表格模型的小型Excel前端. 结果是，您的用户可以使用一个小工作簿访问已部署的Tabular模型, 但却无法获得公式和方法.

4)数据可视化与分析

立方体公式

我的客户经常要求我创建符合严格定义的布局的报告. 我有客户要求特定的列宽度, RGB色码, 以及预定义的字体名称和大小. 考虑下面的仪表板:

如果我们所有的销售都使用Excel的力量主，我们如何在不生成数据透视表的情况下填充销售数字? 使用立方体公式! 我们可以在任何Excel单元格中编写CUBE公式，它将使用我们已经构建的力量主模型执行计算.

例如，在“2016年总销售额”下的单元格中键入以下公式:

= CUBEVALUE(“ThisWorkbookDataModel","(措施).(总销售额)","(日历).(年).[2016]")

公式的第一部分(以黄色突出显示)指的是力量主模型的名称. 一般来说，对于较新版本的Excel 力量主，它通常是ThisWorkbookDataModel. 绿色部分定义了我们想要使用度量Total Sales. 蓝色部分指示Excel仅筛选具有“销售日期”且年份等于2016的行.

在幕后，力量主构建了一个 联机分析处理(OLAP) 包含数据、计算列和度量的多维数据集. 这种设计允许Excel用户通过直接使用CUBE函数获取来访问数据. 使用立方体公式, 我已经能够构建符合预定义布局的完整财务报表. 此功能是使用力量主 for Excel进行财务分析的亮点之一.

权力BI

Excel中的力量主的另一个优点是，您可以快速地将构建的任何力量主工作簿转换为权力BI模型. 通过将Excel工作簿直接导入权力BI Desktop应用程序或权力BI Online, 你可以分析, 可视化, 并与组织中的任何人共享您的数据. 从本质上讲，权力BI是力量主、PowerQuery和分享Point的集合. 下面, 通过将之前的Excel 力量主工作簿导入到权力BI桌面应用程序中，我创建了一个仪表板. 注意这个界面的互动性:

权力BI的一大优点是自然语言Q&A. 为了演示，我将权力BI模型上传到我的在线权力BI帐户. 在网站上，我可以提问，权力BI可以根据我的输入构建相应的分析:

这种类型的查询功能使用户能够以比Excel更容易的方式询问数据模型的问题并与数据进行交互.

权力BI的另一个好处是微软的开发人员不断地发布更新. 许多用户要求的新功能每月都会推出. 最重要的是，它是一个从力量主到Excel的无缝过渡. 因此，您投入学习DAX公式的时间可以部署在权力BI中! 对于需要在不同设备上向许多用户共享其分析的分析师, 权力BI可能值得探索.

最佳实践

一旦开始，就应该遵循一些最佳实践.

首先要深思熟虑地决定要进口什么. 你会使用销售人员的家庭住址吗? 在本工作簿的上下文中，我是否需要知道客户的电子邮件地址? 如果目标是将数据聚合到仪表板中, 那么一些可用的数据对于这些计算来说是不必要的. 花时间管理传入的数据将大大缓解数据集扩展后的问题和内存使用.

另一个最佳实践是记住力量主不是Excel. 在Excel中, 我们习惯于通过不断向右扩展工作表来创建计算. 如果我们限制这种对天定命运的渴望，Excel的力量主最有效地处理数据. 而不是在数据右侧连续创建计算列, 学会在底部窗格中写下度量. 这个习惯将确保更小的文件大小和更快的计算.

最后，我建议使用简单的英文名称作为度量. 我花了很长时间才收养了这个. 我花了几年的时间编了一些名字，比如 SumExpPctTotal但是，一旦其他人开始使用同样的练习簿，我就有很多解释要做. 现在，当我开始一个新的工作簿时，我使用度量名称，比如 费用项目占总费用的百分比. 虽然名称较长，但对其他人来说更容易使用.

真实世界用例

在这篇文章中，我只介绍了几种方法，通过这些方法，Excel的力量主可以让您在普通的Excel之外迈出重要的一步. 我认为强调一些实际用例会很有用，在这些用例中，我发现Excel的力量主非常有用.

下面是一些例子:

• 分析大型资产组合在不同时间范围内的表现; 因为Excel的力量主允许我们定义与前一个时间段进行比较的度量, 我们很快就能实现季度环比, 较上年同期, 每月的表现都是在滚动的基础上，只写了几个指标.
• 使用自定义聚合级别汇总会计数据: 通过按名称识别每个总账项目, 类别, 以及财务报表, 可以快速创建包含适当行项的报告.
• 连锁店可以识别同店销售: 使用一个表来映射商店何时上线, 财务结果可以在同店的基础上进行比较.
• 明确销售业绩优秀者和差者; 可以创建数据透视表，按销售额突出显示前5个sku和后5个sku, 毛利率, 生产时间, 等.
• 零售商可以定义使用4-4-5配置的日历表: 使用自定义日期表, 零售商可以很容易地将每一天分配到特定的4-4-5个月, 然后每天的销售结果可以滚动到相应的月份.

从笨拙的电子表格到现代工作簿

作为金融分析师，我们需要对不断扩展的数据集进行复杂的计算. 因为Excel已经是默认的分析工具, 力量轴心的学习曲线很简单, 许多函数都是Excel的本地函数.

使用CUBE函数, 力量主 for Excel无缝地融入到您现有的Excel工作簿中. 计算效率的提高是不容忽视的. 假设处理速度提高20%, 这是保守的, 每天花6个小时使用Excel的金融分析师一年可以节省300个小时!

另外, 我们现在可以分析比以前使用传统Excel大得多的数据集. 高效设计模型, 我们可以轻松地拥有10倍于传统Excel所允许的数据量, 同时保持快速的分析敏捷性. 具有将模型从力量主转换为SSAS表格的能力, 可处理的数据量为100-1,是Excel的000倍.

Excel的力量主能够对大量数据进行闪电般的快速计算，并且仍然保留深入细节的能力，可以将财务分析从笨拙的电子表格转变为现代的工作簿.

如果你有兴趣尝试Excel的力量主, 下面是一些有用的材料，可以帮助你开始.

有用的参考资料及指引

牧羊犬,R., & 辛格,一个. (2016). 力量主和权力BI: Excel用户DAX, Power Query, 权力BI指南 & 力量主在Excel 2010-2016. 美国:神圣的宏观! 书.

法拉利,., & 罗威米. (2015). DAX的权威指南:使用微软 Excel的商业智能, SQL Server分析服务, 和权力BI. 美国:微软出版社，美国.