什么是 Power Query / Pivot / Map / View / BI 以及他们为什么需要 Excel 用户

术语“Power Query”、“Power Pivot”、“Power BI”和其他“权力”越来越多地出现在有关 Microsoft Excel 的文章和材料中。 根据我的经验，并不是每个人都清楚地了解这些概念背后的含义、它们之间的相互联系以及它们如何帮助一个简单的 Excel 用户。

让我们澄清一下情况。

电源查询

早在 2013 年，微软内部专门创建的一组开发人员发布了一个免费的 Excel 插件。 电源查询 （其他名称是 Data Explorer、Get & Transform），它可以做很多对日常工作有用的事情：

• 上传 Excel 中的数据来自近 40 个不同的来源，包括数据库（SQL、Oracle、Access、Teradata……）、企业 ERP 系统（SAP、Microsoft Dynamics、1C……）、互联网服务（Facebook、Google Analytics、几乎所有网站）。
• 从收集数据 档 所有主要数据类型（XLSX、TXT、CSV、JSON、HTML、XML……），无论是单个还是批量——来自指定文件夹中的所有文件。 在 Excel 工作簿中，您可以一次自动从所有工作表中下载数据。
  
• 清理 从“垃圾”接收到的数据：额外的列或行、重复、“标题”中的服务信息、额外的空格或不可打印的字符等。
• 将数据带入 秩序：正确的大小写，数字作为文本，填充空白，添加正确的表格“cap”，将“粘性”文本解析为列并将其粘贴回来，将日期划分为组件等。
  
• 百般 改造 表，将它们变成所需的形式（过滤、排序、更改列的顺序、转置、添加总计、将交叉表展开为平面并折叠回来）。
• 通过匹配一个或多个参数将数据从一个表替换到另一个表，即很好的替换函数 VPR （VLOOKUP） 及其类似物。

Power Query 有两个版本：作为 Excel 2010-2013 的单独加载项，可以从 Microsoft 官方网站下载，作为 Excel 2016 的一部分。在第一种情况下，安装后会出现一个单独的选项卡电子表格：

在 Excel 2016 中，Power Query 的所有功能都已默认内置并位于选项卡上 时间 （日期） 作为一组 获取和转换 （获取和转换）:

这些选项的可能性完全相同。

Power Query 的基本特征是用于导入和转换数据的所有操作都以查询的形式存储——内部 Power Query 编程语言中的一系列步骤，简称为“M”。 步骤始终可以编辑和重播任意次数（刷新查询）。

Power Query 主窗口通常如下所示：

在我看来，这是本文中列出的对广大用户最有用的插件。 您必须使用公式严重扭曲或编写宏的许多任务现在可以在 Power Query 中轻松而精美地完成。 是的，并且随后会自动更新结果。 考虑到它是免费的，就性价比而言，Power Query 完全没有竞争力，并且是当今任何中高级 Excel 用户的绝对必备品。

的PowerPivot

Power Pivot 也是 Microsoft Excel 的加载项，但设计用于稍有不同的任务。 如果 Power Query 专注于导入和处理，那么 Power Pivot 主要用于大量数据的复杂分析。 作为第一个近似值，您可以将 Power Pivot 视为一个精美的数据透视表。

在 Power Pivot 中工作的一般原则如下：

1. 我们是第一 加载数据中 在 Power Pivot 中 – 支持 15 种不同的源：常用数据库（SQL、Oracle、Access ...）、Excel 文件、文本文件、数据馈送。 此外，您可以使用 Power Query 作为数据源，这使得分析几乎是杂食性的。
2. 然后在加载的表之间 连接已配置 或者，正如他们所说，被创造 资料模型. 这将允许将来在现有表中的任何字段上构建报告，就好像它是一个表一样。 并且再次没有 VPR。
3. 如有必要，使用以下方法将其他计算添加到数据模型中 计算列 （类似于“智能表”中包含公式的列）和 措施 （摘要中计算字段的类似物）。 所有这些都是用一种称为 DAX（数据分析 eXpressions）的特殊 Power Pivot 内部语言编写的。
4. 在 Excel 工作表上，根据数据模型，我们感兴趣的报表构建在表单中 数据透视表 和图表。

Power Pivot 主窗口如下所示：

这就是数据模型的样子，即所有已加载的具有已创建关系的表：

Power Pivot 具有许多使其成为某些任务的独特工具的功能：

• 在 Power Pivot 中 没有线路限制 （如在 Excel 中）。 您可以加载任何大小的表格并轻松使用它们。
  
• Power Pivot 非常擅长 压缩数据 将它们加载到模型中时。 一个50MB的原始文本文件，下载后很容易变成3-5MB。
• 事实上，由于“幕后”Power Pivot 拥有成熟的数据库引擎，它可以处理大量信息 非常快. 需要分析 10-15 百万条记录并构建摘要？ 而这一切都在一台旧笔记本电脑上？ 没问题！

不幸的是，Power Pivot 尚未包含在所有版本的 Excel 中。 如果您有 Excel 2010，则可以从 Microsoft 网站免费下载。 但是如果你有 Excel 2013-2016，那么这一切都取决于你的许可证，因为。 在某些版本中包含它（例如 Office Pro Plus），而在某些版本中则不包含它（Office 365 Home、Office 365 Personal 等）。您可以在此处阅读有关此内容的更多信息。

功率图

该插件于 2013 年首次出现，最初名为 GeoFlow。 它旨在用于地理数据的可视化，即地理地图上的数字信息。 用于显示的初始数据取自同一 Power Pivot 数据模型（请参阅上一段）。

Power Map 的演示版（顺便说一句，与完整版几乎没有区别）可以再次从 Microsoft 网站完全免费下载。 完整版包含在一些 Microsoft Office 2013-2016 软件包中以及 Power Pivot - 以按钮的形式 3D地图 标签 插页 （插入 - 3D 地图）:

功率地图的主要特点：

• 地图 既可以是平坦的，也可以是庞大的（地球）。
• 您可以使用几种不同的 可视化类型 （直方图、气泡图、热图、区域填充）。
• 您可以添加 时间测量，即动画过程并观察它的发展。
  
• 地图是从服务加载的 Bing地图，即您需要非常快速的互联网连接才能查看。 有时，正确识别地址存在困难，因为。 数据中的名称并不总是与 Bing 地图的名称相匹配。
  
• 在 Power Map 的完整版（非演示版）中，您可以使用自己的 可下载的地图，例如，在建筑平面图上可视化购物中心的访客或住宅楼中的公寓价格。
• 根据创建的地理可视化，您可以直接在 Power Map（示例）中创建视频，以便稍后与未安装加载项或将其包含在 Power Point 演示文稿中的人共享。
  

权力观

此插件首次在 Excel 2013 中引入，旨在通过交互式图形、图表、地图和表格使您的数据栩栩如生。 有时这些术语用于此目的。 XNUMX月XNUMX日 （仪表板） or XNUMX月XNUMX日 （记分卡）. 最重要的是，您可以在 Excel 文件中插入一个没有单元格的特殊工作表 - Power View 幻灯片，您可以在其中添加文本、图片和许多不同类型的可视化，基于您来自 Power Pivot 数据模型的数据。

它看起来像这样：

这里的细微差别是：

• 初始数据取自同一个地方——来自 Power Pivot 数据模型。
• 要使用 Power View，您需要在计算机上安装 Silverlight - Microsoft 的 Flash 类似物（免费）。
  

顺便说一下，在微软网站上，有一个非常不错的关于 Power View in 的培训课程。

Power BI

与以往不同的是，Power BI 不是 Excel 的插件，而是一个单独的产品，是一整套用于业务分析和可视化的工具。 它由三个关键要素组成：

1.Power BI桌面 – 用于分析和可视化数据的程序，其中包括 Power Query 和 Power Pivot 附加组件的所有功能以及来自 Power View 和 Power Map 的改进的可视化机制。 您可以从 Microsoft 网站免费下载和安装它。

在 Power BI Desktop 中，您可以：

• 从上面加载数据 70 种不同的来源 （如在 Power Query + 附加连接器中）。
• 绑定 要建模的表（如在 Power Pivot 中）
• 向数据添加额外的计算 措施 и DAX 上的计算列 （如在 Power Pivot 中）
• 创建漂亮的基于数据的 交互式报告 具有不同类型的可视化（与 Power View 非常相似，但更好、更强大）。
• 发布 在 Power BI 服务站点上创建报告（请参阅下一点）并与同事共享。 此外，可以赋予不同的人不同的权利（阅读、编辑）。
  

2.Power BI在线服务 – 简而言之，这是一个您和公司中的每个用户都将拥有自己的“沙箱”（工作区）的站点，您可以在其中上传在 Power BI Desktop 中创建的报告。 除了查看之外，它甚至还允许您对其进行编辑，在线再现 Power BI Desktop 的几乎所有功能。 您还可以在此处从其他人的报告中借用单个可视化，从中收集您自己作者的仪表板。

它看起来像这样：

3. Power BI 移动版 是适用于 iOS / Android / Windows 的应用程序，用于连接到 Power BI 服务并在手机或平板电脑的屏幕上方便地查看（而不是编辑）创建的报告和仪表板。 您可以在此处下载（完全免费）。

例如，在 iPhone 上，上面生成的报告如下所示：

所有这一切同时保持交互性和动画 + 被禁锢在屏幕上用笔触摸和绘图。 很舒服。 因此，公司的所有关键人员随时随地都可以使用商业智能——只需要访问 Internet。

Power BI 定价计划. Power BI Desktop 和 Mobile 开箱即用，大多数 Power BI 服务功能也是免费的。 因此，对于个人使用或在小公司内使用，您无需为上述所有内容支付一分钱，您可以放心地留在计划中 Free. 如果您想与同事共享报告并管理他们的访问权限，您必须前往 贝丝 （每位用户每月 10 美元）。 还有一些吗 高级版 – 适用于需要单独的数据存储和服务器容量的大型公司（> 500 个用户）。

• 在 Excel 中使用 Power Query 项目甘特图
• 如何使用 Power Pivot 在 Excel 中创建数据库
• 在 Power Map 中的地图上沿路线移动的可视化