Excel-现代数据分析-全-

Excel 现代数据分析（全）

原文：zh.annas-archive.org/md5/2b28b9716487aff5056833fba6b3f6f0

译者：飞龙

协议：CC BY-NC-SA 4.0

序言

欢迎来到 Excel 革命。通过更新您对 Excel 的思考方式和使用方法，您可以获得显著的生产力提升，并更强大地使用您的数据。本书介绍了“现代 Excel”套件的功能以及其他强大的分析工具。

学习目标

通过本书，您应能够使用现代 Excel 工具进行数据清洗、分析、报告和高级分析。特别是，您将使用 Power Query 清洗和转换数据，在 Power Pivot 中创建关系模型以构建复杂的分析，并探索 Excel 分析工具包以进一步自动化和增强您的工作。

先决条件

为实现这些目标，本书对技术和技术做了一些假设。

技术要求

为了充分利用本书，建议您使用 Windows 计算机，并安装桌面版 Microsoft 365 版 Excel。本书涵盖的功能相对较新，可能在旧版本的 Excel 中不可用。请注意，许多这些工具仍在为 Mac 开发，并且兼容性可能有所不同。由于 Excel 发展速度快，很难为每个版本提供精确的可用功能列表。

本书第七章简要解释了如何将 Excel 中的数据模型加载到 Power BI 中。它假定您作为 Windows 版 Microsoft 365 用户已经在计算机上安装了免费版 Power BI Desktop。第十二章深入探讨了如何将 Python 与 Excel 集成，指导您通过免费下载 Python 的过程。本书内的所有后续任务和练习均旨在完全在 Excel 中完成，无需外部程序。但是，作为过程的一部分，您将配置一些 Excel 加载项。

技术要求

本书专为希望探索可能不熟悉的现代功能的中级 Excel 用户设计。要充分受益，您应已熟悉以下 Excel 主题：

使用绝对引用、相对引用和混合单元格引用
构建条件逻辑和条件聚合函数（IF()语句、SUMIF()/SUMIFS()等）
结合数据源（VLOOKUP()、INDEX()/MATCH()或其他查找函数）
使用数据透视表进行数据排序、筛选和聚合
基本绘图（条形图、折线图等）

如果您希望在继续之前进一步练习这些主题，我推荐阅读 Michael Alexander 和 Dick Kusleika 合著的Microsoft Excel 365 圣经（Wiley, 2022）。

在书的第三部分中，您将探索统计学、编程及相关领域的高级概念。如果这些主题一开始看起来很具挑战性，请不要气馁。在获得熟练度方面有丰富的资源可供帮助，我将在必要时提供有用的参考资料。这本书的主要目标是展示 Excel 所提供的广阔可能性。

如果您希望在深入研究这些主题之前先增加知识，我建议阅读我的书籍进阶分析：从 Excel 到 Python 和 R（O’Reilly，2021）。它提供了关于高级分析技术、Python 编程和其他与 Excel 中现代数据分析相关的主题的全面洞见和指导。

如何我到达这里

我进入数据世界的旅程始于 2010 年代初的 Excel，那时数据科学和人工智能尚未完全席卷世界。那个时候，Excel 常常感觉像是一个封闭的系统。如果你想进行高级分析，通常建议转向 Python 或 R。对于自助式关系数据模型，Access 是推荐的选择。我试图完成的许多复杂分析和自动化任务涉及繁琐的 VBA 模块和笨重的数组公式，使用户体验不太理想。

有一段时间，Excel 看起来可能最终会被淘汰。然而，如今的 Excel 在各种功能和应用的支持下经历了显著转变。

“现代分析”是什么？为什么选择 Excel？

现代分析 指的是使用先进工具和技术来准备和分析数据，从简单的回顾性分析到预测建模和人工智能。在数据驱动决策日益演变的情境中，拥有多功能和可互操作的工具至关重要，使用户能够执行各种分析活动。

以前，Excel 在满足这些要求方面存在不足。然而，在过去的十年里，Excel 经历了重大转变，使其成为现代数据分析的真正强大工具。

本书旨在消除技术专业人员对 Excel 的常见误解，并展示其在现代分析领域的能力。通过展示 Power Pivot、Power Query 等工具的功能，本书挑战了 Excel 仅限于基本公式和函数的信念。它强调今天的 Excel 已经发展成为一个能够处理复杂数据分析任务的强大平台。

最终，这本书展示了 Excel 作为现代分析的强大而多功能的工具。它试图揭示谬论，指导技术专业人员和管理者充分利用 Excel 在有效数据分析和决策中的潜力。通过这样做，它使用户能够将 Excel 作为当代分析工具包的关键组成部分，为我们数据驱动的世界提供洞察并推动成功。

书籍概述

为了实现本书的学习目标和范围，我将内容分为三部分。

第一部分，使用 Power Query 进行数据清洗和转换

第一部分专注于 Excel 中用于数据清洗的 Power Query，以及它如何作为提取、转换和加载（ETL）工具使用。您将了解 Power Query 编辑器，学习数据剖析和各种转换技术，如过滤、拆分、聚合和合并数据。

第二部分，使用 Power Pivot 进行数据建模与分析

第二部分介绍了 Excel 的 Power Pivot，重点在于其在报告中的应用。您将学习如何定义关系、构建数据模型，并通过计算列、关键绩效指标（KPI）等增强数据模型，主要使用数据分析表达式（DAX）语言。

第三部分，Excel 数据分析工具包

本书的第三部分探讨了 Excel 中几个令人兴奋的数据分析新功能。您将学习关于动态数组函数的知识，这些函数可以实现快速和灵活的电子表格计算。此外，本书还提供了有关预测分析和人工智能的入门信息，讨论了它们在 Excel 中的潜在应用，并展示了程序的未来。书籍以一个高级主题结束：使用 Python 构建自动化工作簿。您将学习如何有效地利用 Python 和 Excel 结合，以增强您的分析能力。

章节末练习

当我阅读书籍时，我倾向于跳过章节末的练习，因为我认为保持阅读的动力更有价值。不要像我这样！

大多数章节结束时，我提供了通过实践应用所学知识的机会。练习及其解决方案位于附带库的exercises文件夹中，按章节号进行组织。我鼓励您完成这些练习，并将您的答案与提供的解决方案进行比较。通过这样做，您不仅可以增进对材料的理解，还可以为我树立一个积极的榜样。

这不是一份清单

Excel 的快速发展步伐和大量新工具可能会让人感到不知所措。为了避免失去焦点并使书籍变得难以驾驭，我精心挑选了一组特定的主题，这些主题对中级 Excel 用户具有广泛的潜力和实用性，这些选择源于我作为 Excel 顾问和培训师多年的经验。

如果您认为现代 Excel 中最喜欢或最有影响力的特性未在本书中介绍，请作为社区的重要成员分享您的见解。Excel 数据分析的领域超出了一本书的边界，Excel 社区渴望从您的见解和经验中学习。

您准备好开始现代 Excel 之旅了吗？我会在第一章中等你。

本书使用的约定

本书中使用以下印刷约定：

斜体

表示新术语、URL、电子邮件地址、文件名和文件扩展名。

等宽

用于程序列表，以及段落中引用程序元素，如变量或函数名、数据库、数据类型、环境变量、语句和关键字。

等宽粗体

显示用户应直接输入的命令或其他文本。

等宽斜体

显示应由用户提供值或根据上下文确定值的文本。

提示

此元素表示提示或建议。

注意

这个元素表示一般注释。

警告

此元素表示警告或注意事项。

使用代码示例

可以下载补充材料（例如代码示例、练习等）：https://github.com/stringfestdata/modern-analytics-excel-book。

本书旨在帮助您完成工作。通常情况下，如果本书提供了示例代码，您可以在您的程序和文档中使用它。除非您复制了代码的大部分内容，否则无需征得我们的许可。例如，编写一个使用本书中几个代码片段的程序不需要许可。出售或分发奥莱利图书中的示例则需要许可。引用本书并引用示例代码回答问题不需要许可。将本书中大量示例代码合并到产品文档中则需要许可。

如果您觉得您对代码示例的使用超出了合理使用或上述许可，请随时通过permissions@oreilly.com与我们联系。

奥莱利在线学习

注意

奥莱利媒体已经提供了超过 40 年的技术和商业培训，知识和洞察力，帮助企业取得成功。

我们独特的专家和创新者网络通过书籍、文章和我们的在线学习平台分享他们的知识和专长。O’Reilly 的在线学习平台为您提供随需应变的现场培训课程、深入学习路径、交互式编码环境，以及来自 O’Reilly 和其他 200 多个出版商的广泛文本和视频资源。更多信息，请访问https://oreilly.com。

如何联系我们

请将有关本书的评论和问题寄给出版商：

O’Reilly Media, Inc.
1005 Gravenstein Highway North
加利福尼亚州塞巴斯托波尔市，95472
800-889-8969（在美国或加拿大）
707-827-7019（国际或本地）
707-829-0104（传真）
support@oreilly.com
https://www.oreilly.com/about/contact.html

我们为这本书准备了一个网页，上面列出了勘误、示例和任何额外信息。你可以访问此页面：https://oreil.ly/modern-data-analytics-excel。

想要获取关于我们的书籍和课程的新闻和信息，请访问https://oreilly.com。

在 LinkedIn 上找到我们：https://linkedin.com/company/oreilly-media。

观看我们的 YouTube 频道：https://youtube.com/oreillymedia。

致谢

写书最迷人的一个方面，尤其是致谢部分，是它记录了你生活中的一个时刻，并突出了那些在那个时期显著的人物。

我之前的书的致谢部分可以找到很多这些名字。我特别感谢 O’Reilly 的收购团队米歇尔·史密斯和乔恩·哈塞尔，他们批准我写另一本书。我的朋友兼 O’Reilly 作者托比亚斯·兹文格曼多年来相互审查了他的工作，为这个项目提供了极具帮助的技术审查。此外，我的父母乔纳森和安吉拉·芒特在支持方面始终如一，超出了我所能期望的。不确定有多少母亲希望自己的孩子成为 Excel 作者，但我的母亲一直给予了我难以置信的支持。

我还有机会通过这个项目深入了解一些人物。我要感谢艾伦·默里、约瑟夫·斯蒂克和梅根·芬利对本书额外技术审查的宝贵贡献。特别要感谢梅根，她不仅为本书带来了令人印象深刻的技术编辑经验，还在整个写作过程中作为我的女友给予了不可思议的支持。（正如任何作者所能告诉你的，写书不可避免地成为一个家庭的事务。）此外，我要感谢杰夫·斯蒂文斯、劳拉·塞佩西和马克·德波对手稿的反馈。

此外，我要感谢 O'Reilly 的编辑团队，他们在我撰写技术书籍的过程中给予了我大量指导。特别感谢 Sara Hunter，在我着手写第二本书时，她作为无价的编辑顾问给予了我很多帮助。

最后，我要向整个 Excel 社区表示感激，他们是如此热情和鼓舞人心的一群。这款电子表格程序为我打开了更多机会，让我结识了更多令人难以置信的人。希望这本书能在某种程度上为你在 Excel 中的卓越之旅贡献一些小小的帮助。

第一部分：使用 Power Query 进行数据清洗和转换

第一章：表格：现代 Excel 的入口

Excel 拥有广泛的分析工具，这使得确定最佳起点变得具有挑战性。然而，掌握 Excel 表格是一个基础步骤。本章深入探讨了 Excel 表格的基本要素，作为连接到本书中突出显示的 Power Query、Power Pivot 和其他工具的媒介。它进一步强调了在表格内精心组织数据的重要性。要了解本章内容，请导航至伴随书籍的 ch_01 文件夹中的 ch_01.xlsx，找到 ch_01.xlsx 中的 ch_01.xlsx。

创建和引用表头

数据集如果没有列标题，就几乎无用，因为缺乏解释每列测量内容的有意义上下文。不幸的是，经常会遇到违反这一基本规则的数据集。Excel 表格充当了一个宝贵的提醒，即数据集的质量取决于具有清晰和信息丰富的标题。

在 ch_01.xlsx 的 start 工作表中，你会看到列 A:F 中的数据没有相应的标题，目前位于列 H:M 中。这种设计远远不及理想。要进行调整，请单击主数据源中的任意位置，并从功能区转到“插入”→“表格”→“确定”，如图 1-1 所示。或者，你可以在数据源内按下 Ctrl+T 或 Ctrl+L 启动相同的创建表格对话框。

图 1-1. 将数据源转换为表格

创建表格对话框会自动提示你指定数据是否包含标题。当前情况下没有。如果没有标题，则数据集会自动分配一系列名为 Column1、Column2 等的标题列。

从这里，你可以将列 H:M 的标题剪切并粘贴到主表格中，以澄清每列中所测量的内容，例如在图 1-2 中。

图 1-2. 具有标题的 Excel 表格

Excel 表格中的标题列在数据集中占据着独特的角色。虽然它们是表格的一部分，但其功能更像是元数据而非数据本身。与传统的 Excel 公式不同，Excel 表格提供了通过程序区分标题和数据的能力。

要看到这种差异的实际效果，请转到工作表中的空白单元格，并输入等号。指向单元格 A1:F1 作为参考，你会注意到公式变成了 Table1[#Headers]。

你还可以在其他函数中使用这个引用。例如，你可以使用 UPPER() 动态转换所有标题的大小写，例如在图 1-3 中。

图 1-3. Excel 标题引用公式

查看表格页脚

正如每个故事都有开头、中间和结尾一样，每个 Excel 表格包括标题、数据和页脚。但是，需要手动启用页脚。要执行此操作，请单击表格中的任意位置，导航到功能区上的“表格设计”，然后在“表格样式选项”组中选择“总行”，如图 1-4（#add_footers）所示。

图 1-4。将页脚添加到表格

默认情况下，表格中的总行将计算数据的最后一列的总和；在本例中为frozen。但是，您可以通过单击任何列的页脚上的下拉菜单来自定义此操作。例如，您可以查找fresh类别的最大销售金额，如图 1-5（#custom-footers）所示。

图 1-5。自定义 Excel 表格的页脚

表 1-1 总结了 Excel 表格主要组件的关键公式参考，假设表格名称为Table1。

表 1-1。Excel 表格公式参考摘要

公式	所引用的内容
`=Table1[#Headers]`	表头
`=Table1`	表数据
`=Table1[#Totals]`	表页脚
`=Table1[#All]`	表头、数据和页脚

随着您在 Excel 表格技能的进步，您将发现更多依赖于标题、正文和页脚基本结构的有用公式参考。

命名 Excel 表格

Excel 表格具有强制使用命名范围的优势，这促进了更结构化的数据处理方式。尽管引用Table1优于使用像A1:F22这样的单元格坐标，但最好选择一个反映数据代表内容的描述性名称。

要完成此操作，请转到功能区上的“公式”选项卡，选择“定义名称”组中的“名称管理器”，然后选择Table1名称的“编辑”。将名称更改为sales，然后单击“确定”。更改后，“图 1-6”（#name-manager）显示名称管理器的外观。

图 1-6。Excel 中的名称管理器

一旦关闭名称管理器，您将注意到所有对Table1的引用已经自动更新，以反映新名称：sales。

格式化 Excel 表格

作为 Excel 用户，您知道以令人愉悦的方式展示数据的重要性。表格可以是改变游戏规则的因素，即刻提升工作表的视觉吸引力。通过表格，您可以轻松添加交错行、彩色标题等。要自定义表格的外观和感觉，请单击表格内的任意位置，转到功能区上的“表格设计”。查看图 1-7（#table-design），了解各种选项，例如更改表格颜色或切换交错行。

图 1-7。表格设计自定义选项

更新表格范围

使用 Excel 表格，数据添加或删除时总计错误的问题得到了有效解决。借助结构引用的使用，公式能够无缝适应数据的变化，保证计算的准确性。此外，表格底部的总计会自动更新以反映这些变化，并且可以轻松地从外部引用中排除，保持计算的完整性。

使用结构化公式=SUM(sales[fresh])计算fresh列的总和。微软的智能感知功能在您输入时能够高效地完成名称。尝试添加或删除行，或修改sales表中的fresh数据，您会观察到动态更新和始终保持一致准确性的功能来计算总fresh销售额。

引用名称而不是单元格位置可以最大程度地减少由于表格大小和位置变化而引起的潜在公式问题。表格还在防止透视表中出现新行时缺失数据等问题方面变得至关重要。

组织数据以进行分析

尽管表格很有价值，但确保数据以合适的形式存储更为重要，以确保轻松且准确的数据分析。

以sales表格为例来进行分析。在尝试创建透视表以按地区计算总销售额时，数据存储的格式带来了挑战。理想情况下，所有销售信息应该汇总到一个单独的列中。然而，在当前设置中，每个部门（fresh、grocery和frozen）都有一个独立的销售列。Excel 并不意识到这些列实际上代表的是同一指标，即销售额。

这也是许多数据集变得难以分析的原因之一，因为它们存储的格式不利于分析。整洁数据的规则提供了解决方案。尽管哈德利·韦克汉姆在他的同名 2014 年论文中提出了三条规则，但本书专注于第一条规则：每个变量形成一列。

sales数据集通过在每行内的不同部门中拥有同一变量field的多个条目违反了整洁数据的规则。一个有用的经验法则是，如果多列正在测量同一事物，则数据可能不是整洁的。通过将数据转换为整洁格式，分析变得显著简单起来。

在图 1-8 中，您可以看到数据集转换前后的对比，突显了改进后的整洁性和分析的便利性。在第四章，您将学习如何通过几次点击对数据集执行这种基本转换。同时，您可以查看ch01_solutions.xlsx中的sales-tidy工作表，该表已经完成了转换。亲自查看一下，看看如今通过该表轻松获取各地区总销售额的方式有多简单。

图 1-8。整理前后的批发客户

结论

本章为有效利用 Excel 表格奠定了基础。要深入探讨如何最大化表格的潜力，包括使用结构引用来制定计算列，请参考 Zack Barresse 和 Kevin Jones 的《Excel Tables: A Complete Guide for Creating, Using, and Automating Lists and Tables》（Holy Macro! Books, 2014）。此外，本章还深入探讨了数据的精细组织，这是 Excel 中任何成功数据分析项目的基本方面。第二章 Chapter 2 介绍了如何使用 Power Query 简化数据转换。

练习

要在 Excel 表格中创建、分析和操作数据，请使用位于书籍companion repository中exercises\ch_01_exercises文件夹中的ch_01_exercises.xlsx中的penguins数据集：

将数据转换为名为penguins的表格。
利用公式引用将每个列标题大写。
生成一个名为bill_ratio的新列，计算bill_length_mm除以bill_depth_mm。
包含一个总行来计算平均body_mass_g。
移除表格中的带状行样式。

解决方案，请参考位于同一文件夹中的ch_01_exercise_solutions.xlsx文件。

第二章：Excel Power Query 的初步步骤

在第一章中，表格被介绍为现代 Excel 分析的入口。第 I 部分的后续章节深入探讨了现代 Excel 工具包，特别是 Power Query。这个工具解决了 Excel 传统限制的许多问题，并提供了一个用户友好的低代码环境。

什么是 Power Query？

Power Query 是一种数据连接技术，允许用户在 Excel 内轻松连接、合并和优化来自各种来源的数据。最初作为附加组件引入，它已成为现代 Excel 的核心功能，显著简化了数据导入和清洗过程。Power Query 提供了一个用户友好的界面，用于执行诸如合并表格、转换数据格式和聚合信息等复杂数据操作，而无需高级编程技能。

Power Query 作为 Excel 的神话破解者

分析师们扮演着神话破解者的角色，挑战假设，揭示真相。即使在 Excel 这样一个传奇的商业工具领域，也可能出现神话。然而，许多这些神话已经不再成立。Power Query 已成功驳斥了许多主张，将自己定位为 Excel 的终极神话破解者。接下来的部分将使用 Power Query 来反驳关于 Excel 的常见主张。

“Excel 不具备可重复性”

这是一个常见的场景：你发现自己面临压力，面对紧迫的截止日期和过度干预的经理，试图修改上周报告。报告的原始创建者不在，让你感到困惑，不知道它是如何组装的。工作簿似乎是一团删除的列和改变的值的混乱，使得难以理解所需采取的行动。

计算中的可重复性允许用户通过相同的输入和过程始终获得相同的结果。当工作簿由于容易出错的步骤、复杂的计算或其他引入不确定性的因素而无法每次打开时都获得一致的结果时，它未能实现这一目标。

传统 Excel 中缺乏可重复性已成为该软件的一个重要批评点。这一关注点导致许多技术专业人士对完全使用 Excel 持谨慎态度，担心单个删除的列或硬编码的单元格可能会损害结果的完整性。

完全基于其过去的限制而彻底放弃 Excel 的决定是错误的。现在 Excel 通过 Power Query 提供了可重复性的解决方案。使用 Power Query，用户可以创建源数据的副本，应用一致的转换步骤，并在“已应用步骤”列表中记录每个操作。这种方法确保了可重复性，并消除了追踪数据清理操作的需要，解决了以前与 Excel 缺乏可重复性有关的问题。

“Excel 没有真正的 null”

在关系型数据库中，表示未知或未指定数据的缺失或null值的概念是众所周知的。然而，Excel 缺乏保留null值的关键字，导致存储和处理方面的挑战。用户可能会采用不同的方法来表示 Excel 中的缺失值，比如将它们留空或硬编码为NA。这种差异使得难以识别真正未知值与实际等于零或有意留空的值之间的区别。

为了克服这一限制，Power Query 引入了一个专门的null值来表示缺失数据。这种增强功能促进了精确的数据分析、移除和替换缺失值，确保准确性和可重现性。

“Excel 不能处理超过 1,048,576 行”

Excel 在处理“大数据”时常被批评存在局限性。批评者声称，由于其工作表最多只能容纳约一百万行的数据，Excel 在大数据时代表现不佳。

解决方案再次在于 Power Query，它可以轻松地导入和处理数百万行及更多数据。虽然 Excel 本身确实无法处理超过百万行的数据，但 Power Query 使用户能够在其编辑器中聚合和汇总数据，然后将结果加载到 Excel 工作表中。

要了解如何在 Excel 中超越所谓的百万行限制的引人注目演示，请查看分析顾问 Orlando Mézquita 在分析五千万行时使用 Excel Power Query 的这篇文章。

Power Query 作为 Excel 的 ETL 工具

在技术世界中，许多术语乍看起来可能极为复杂。它们甚至可能被混乱的首字母缩写所掩盖。然而，仔细审视后，这些概念揭示出它们的简单性。

一个这样的术语是ETL，它代表“提取、转换、加载”。数据库管理员和数据工程师经常热烈地讨论他们的“ETL 管道”和“ETL 软件”。这可能会给人留下只有认证过的数据极客才能处理这些任务的印象。

Power Query 通过直接将其集成到 Excel 电子表格中，使 ETL 过程变得平民化。不要让技术纯粹主义者吓到你！ETL 的本质已经融入其名称中，并且可以使用 Excel 完成。

以下各节详细介绍了这个过程的逐步概述。要跟随进行，打开书中资源文件夹中的ch_02文件夹中的ch_02.xlsx。

提取

ETL 的初始步骤是从外部源“提取”数据。Power Query 具有连接到各种数据源的能力，超越了 Excel 工作簿的范围。以下是一些它可以连接到的数据源的示例：

文本和 CSV 文件
如 Oracle、Microsoft SQL Server 或 SQLite 等关系型数据库
SharePoint
XML、HTML 和 Web 数据

不过，对于这个演示，数据方便地位于 Excel 工作簿本身内。

要开始，从 ch_02.xlsx 的sales工作表中提取数据。在表内任何位置单击，然后转到“数据” → “获取和转换数据” → “从表/范围获取”，如图 2-1 所示。

图 2-1. 从表中提取数据

将数据放入 Power Query 的表格中的要求部分是我们为什么在第一章中专门讨论表格的原因。表格是访问现代 Excel 工具的重要门户。

尽管“连接并提取”已经存在于工作簿中的数据似乎有些奇怪，但这种方法是合理的，因为 Power Query 能够保留原始数据的原始形式。即使原始数据与分析在同一个工作簿中，建议也提取数据的子集（“E”中的“E”）以便进行进一步的分析。

转换

下一步是连接到这些数据并执行必要的转换（ETL 中的“T”）。

数据转换包括多种任务，以使数据可用，例如：

排序或过滤行
添加、删除、重命名或计算列
合并或重塑数据源

当你将表加载到 Power Query 中时，Power Query 编辑器会弹出，提供多种数据清洗和转换选项。这可能会令人感到有些不知所措，特别是如果你习惯了经典的 Excel 环境。然而，我们将在第一部分中逐步介绍这个程序。

你将在本书的后面学习如何执行多个数据清洗任务。现在，通过添加索引列可以实现一个简单的数据转换。要做到这一点，转到 Power Query 标签上的“添加列”，然后选择“索引列” → “从 1”，如图 2-2 所示。

图 2-2. 从 Power Query 添加索引列

加载

最后，在 Power Query 编辑器中，导航到主页选项卡，选择“关闭并加载”。此操作将把略有变化的数据提取加载到另一个 Excel 表格中，并显示在一个新的工作表中，如图 2-3 所示。

图 2-3. 从 Power Query 加载数据到 Excel 表格

祝贺你完成了一个完整的 ETL 作业：

你从 Excel 表格中提取了原始数据。
你使用 Power Query 编辑器转换了数据。
你把结果加载回了 Excel。

Power Query 编辑器之旅

现在，你已经完成了一个非常简单的整个 Power Query ETL 过程的示例，让我们更详细地看看 Power Query 编辑器。为此演示，打开 ch_02.xlsx 中的penguins工作表。

要开始，将penguins表格加载到 Power Query 中。如果你需要关于如何操作的复习，请回顾前面的部分。生成的 Power Query 编辑器应该看起来像图 2-4。

图 2-4. Power Query 编辑器

请稍作停留，深入探索并欣赏你所处的独特环境。它与 Excel 的功能区界面相似，但作为一个独立的独立程序运行。接下来的部分将探讨其各种元素。

功能区菜单

在界面顶部，您会注意到一个功能区菜单，其设计紧随 Excel 熟悉的界面，如图 2-5 所示。

图 2-5. Power Query 功能区

您应该在功能区看到四个选项卡：主页、转换、添加列和视图：

主页

就像普通的 Excel 一样，主页选项卡包含了 Power Query 中最基本的操作，如选择行、删除列等。然而，Power Query 中的主页选项卡专注于基本的数据转换和清理任务，而不是普通 Excel 的格式设置功能。

转换

转换选项卡提供了更多的数据清理和转换选项。您将在接下来的章节中有机会尝试许多这些功能。

添加列

此选项卡专门用于通过各种方法创建新列。在“转换”中，您可以使用此选项卡向数据添加索引列。在第四章中，您将使用它来创建计算列。

视图

视图选项卡定制 Power Query 编辑器的显示。首先，在“布局”组中点击“公式栏”。此举将在数据集上方显示一个类似 Excel 的公式栏，如图 2-6 所示。

图 2-6. Power Query 公式栏

Power Query 公式栏中显示的公式可能与典型的 Excel 函数不同，因为它是用专为 Power Query 设计的 M 编程语言编写的。通过 Power Query 编辑器的点选接口调整查询时，底层的 M 代码相应地改变。这允许进行调试、定制或共享结果。

尽管公式栏的存在可能暗示掌握复杂的编程语言对于利用 Power Query 的能力至关重要，但这并不一定是真的。大多数日常任务可以通过主页选项卡和其他功能有效完成，无需编写 M 代码。本书专注于这些点选选项，使公式栏对我们的目的来说变得多余。

注意

本书将不会使用 Power Query 编辑器中的公式栏进行未来的演示。您可以通过取消“视图”选项卡中“布局”组中的“公式栏”来隐藏它。

要开始在 Power Query 中编写自己的 M 代码，请首先查看高级编辑器。在 Power Query 编辑器的“查看”选项卡中，单击“高级编辑器”在“高级”组中打开此窗口。这个窗口会以单一的全面视图显示你的整个查询。

将注意力从功能区转移到编辑器左上角的查询列表上。在这里，你会找到导入的数据源，可以在之间切换查看。虽然这些数据源都存在于工作簿中，但请记住 Power Query 支持各种数据源，包括 .csv 文件、数据库、网页等。

要在特定查询上执行操作，请右键单击其名称，如penguins，将会显示一个包含各种选项的菜单，如图 2-7 所示。这些选项包括重命名查询、删除查询等。

导入的数据

图 2-7. Power Query 中的查询列表

Power Query 提供了大量的右键选项，因此不要犹豫去探索和尝试它们。

现在，将注意力转移到占据编辑器大部分空间的组件：数据本身。与在 Excel 中可以自由操作数据不同，例如隐藏列或插入公式，Power Query 强制实施编辑限制。

图 2-9. Power Query 中的应用步骤列表

要用警察乐队一首歌的歌词来表述，Power Query 努力观察和跟踪你对查询的每一步和动作。随意硬编码公式或随意隐藏列是不允许的。所有操作必须在 Power Query 的框架内以程序方式执行。

图 2-8. Power Query 中删除列的操作

查询

图 2-9. Power Query 中删除列的应用步骤

考虑一个简单的任务，删除一个列。要从penguins数据集中移除island，只需右键单击列标签，选择“删除”，如图 2-8 所示。

Power Query 详细记录每一个动作，包括删除，在显示的“应用步骤”列表中与你的数据一起显示。这确保了所有操作的透明性和可追溯性。

更加精确地说，这个删除操作在“已应用步骤”部分标记为第三步“Removed Columns”。初始步骤是连接到数据，称为源数据。第二步“Changed Type”涉及为表格设置数据类型。与 Excel 不同，Power Query 要求列中的每个值都必须是相同类型的。本书将主要依赖 Power Query 的自动数据类型转换。您可以在Microsoft 的官方文档中了解更多关于 Power Query 数据类型的信息。

通过单击“已应用步骤”列表中的任何步骤，您可以重新查看数据在特定时间点的表现。例如，如果单击“Changed Type”（在删除列之前的步骤），则island列将重新出现在您的编辑器视图中。

为了强化本章前面学习的内容，在仍然突出显示“Changed Type”步骤的数据集中添加一个从 1 开始的索引列。您将收到一条消息，确认您希望在查询中插入一个中间步骤，如图 2-10 所示。

图 2-10. 在 Power Query 中插入一个中间步骤

单击“插入”后，您会注意到“Added Index”被放置在“已应用步骤”列表中“Removed Columns”之前，尽管它是在后来的时间点添加的。这允许根据需求的演变或工作流程中添加新步骤轻松修改查询。

您可以对这些步骤执行各种操作，例如删除或重命名它们。假设您希望在查询中重新引入之前删除的island列。您有两个选择：要么单击“删除列”查询步骤左侧的 X 图标，要么右键单击同一步骤以访问一个菜单，该菜单允许您删除、重命名和重新排序步骤，以及其他任务。

警告

尽管“已应用步骤”列表提供了灵活性，但它缺少 Excel 经典功能中珍贵的一个特性：撤销操作的能力。一旦删除了一个步骤，就没有内置的选项来撤销删除。因为大多数这些步骤可以轻松复制，通常手动重复过程与依赖撤销按钮一样方便。

退出 Power Query 编辑器

在编辑器中创建所需的查询后，现在是时候退出 Power Query 并返回常规的 Excel 工作簿了。本章前面提到，单击 Power Query 编辑器主页选项卡上的“关闭并加载”会将查询结果加载到 Excel 表中。

还有其他加载选项，您可以通过单击“关闭并加载”旁边的下拉按钮，然后选择“关闭并加载到”。现在您应该能够在图 2-11 中看到对话框。

图 2-11. Power Query 加载选项

您的第一个决定是将数据加载到表、透视表（这里称为透视表报表）、透视图或选择仅连接选项。当仅加载到连接时，您的查询结果将不会加载到 Excel 中，但查询本身将保持在 Power Query 编辑器中可访问。

如果选择将数据加载到工作簿中，您可以将其放置在新工作表或现有工作表中。

此外，您还可以选择将数据添加到数据模型中。这使您能够构建关系数据模型，并在工作簿中利用高级报表功能。在本书的第二部分中，您将介绍数据模型、Power Pivot 和 DAX。

选择练习的任何选项，然后单击确定。

返回到 Power Query 编辑器

若要在 Power Query 中恢复转换数据或更改加载查询的方式，请转到 Excel 选项卡上的数据选项卡，然后选择查询和连接。在窗口右侧出现的窗格中查找您的penguins查询。

您将看到 Power Query 报告此文件中的两个错误；这些将很快被识别并处理。

右键单击penguins，您将看到几个用于处理此查询的选项。要更改查询加载到 Power Query 的方式，请选择加载到。要返回到 Power Query 编辑器，请选择编辑，如图 2-12 所示。

图 2-12. 查询和连接选项

Power Query 中的数据概要分析

到目前为止，我们已经强调了 Power Query 作为一个强大的 ETL 工具，旨在简化数据清理过程。然而，在不清楚数据“脏”的确切原因的情况下，着手清理数据集是一种不切实际的方法。

为了缓解这一挑战，Power Query 配备了一系列数据概要分析技术。本节将探讨数据概要分析在 Power Query 中的重要作用，强调其在优化数据质量中的重要性。

什么是数据概要分析？

数据概要提供有关数据特征的见解，例如缺失值、值频率和摘要统计信息。这些知识支持明智的决策和高效的数据转换。在概要分析过程中，请考虑以下问题：

数据有多精确？
是否存在明显的问题？
每个变量和观察的目的和测量是否清晰？
所有必要的数据是否都可用？是否存在空白？
在基于 Excel 的数据中，是否存在影响结果的公式错误？
数据是否已准确转录？

回答这些问题允许分析师评估数据的健康状况和可靠性，找出潜在问题，并决定数据清理、转换和分析策略。

探索数据预览选项

数据概要分析有点隐藏在 Power Query Editor 中。要访问它，请导航到功能区中的“视图”选项卡，并找到“数据预览”组，如图 2-13 所示。您可以逐个切换这五个选项来探索它们的功能。

图 2-13. Power Query 中的数据预览选项

“等宽字体”和“显示空白”

前两个选项修改了数据在 Power Query Editor 中的外观：

“等宽字体”将数据呈现为固定宽度文本。
“显示空白”将显示数据中的任何前导或尾随空格

尽管这些选项很有用，特别是用于识别需要修剪的文本，但真正的数据概要分析潜力则在后续选项中。

“列质量”和“列分布”

接下来，选择以下两个选项。每个列上方将显示一个框，提供有关数据的宝贵洞见，如有效值、错误值和空值的百分比。它还呈现了列中值的分布可视化。这些选项为数据的质量和分布提供了全面的概述，有助于有效的分析和决策。

有效单元格是什么？

当 Excel 提到“有效”数据时，它仅意味着该值不为空且不包含任何错误。重要的是要注意，“有效”数据的这一定义不考虑数据的逻辑正确性或含义。因此，Power Query 可能会认为荒谬的数据是“有效”的。例如，看一下第71行的sex列，如图 2-14 所示。

图 2-14. Power Query 中的有效单元格是什么？

缺失值

很明显，99不是sex列的有效值。这似乎是一个转录错误的结果，这些单元格错误地填充为99而不是缺失或null。要观察 Power Query 中真正的缺失值，请转到第 296 行，如图 2-15 所示。

图 2-15. 在 Power Query 中检查空值

在这里，您会注意到几个标记为null的条目，这是在 Power Query 中表示空值的正确方式。目前，“列质量显示”显示了每个这些列中少于 1%的单元格被分类为“空白”。但是，请记住，这些数据不包括错误转录的缺失值。

单元格错误

要理解错误类别，请停留在第 296 行，并关注bl_bd_ratio列。此特定列在 Excel 中是通过将bill_length_mm列的值除以bill_depth_mm列的值计算得出的。然而，在这一特定行中，公式的分母为空，导致错误。通过单击“错误”旁边的空白处，可以验证其来源是#DIV/0错误，如图 2-16 所示。

图 2-16. Power Query 数据分析中的单元格错误

此列中发现了两个这类计算错误，导致在图 2-16 中看到的错误消息。可以通过多种方式解决此错误，包括编写一个基础的 Excel 公式来处理除以零的错误，或者通过过滤掉导致错误的行来解决。在第三章中，您将学习如何过滤行并执行其他行操作。

“列分布”选项显示数据分布的可视化，以及其他一些信息，如图 2-17 所示。

图 2-17. Power Query 中的列分布

然而，这些功能都可以在“列轮廓”选项中找到，因此我们将在下一步重点关注这里。

列轮廓

最后，选择“列轮廓”复选框，选择特定列，并在数据集下方导航到“列轮廓”输出，以获取详细洞察。例如，考虑species列，这是一个定性变量。此选择将提供该列内值的详细分解，包括显示每个值观察频率的可视化，如图 2-18 所示。

图 2-18. 分析定性变量

对于像bill_depth_mm这样的定量变量，“列轮廓”输出将显示诸如平均值和标准偏差之类的其他度量，如图 2-19 所示。

注意

如果您对定性和定量变量不熟悉，可以将定性变量理解为“是什么类型？”而将定量变量理解为“多少？”或“多少个？”有关这些变量类型的深入探讨，请参阅我的书籍 进阶分析：从 Excel 到 Python 和 R。在本书的第 II 部分中，您将学习维度和度量，这些概念类似于定性和定量变量。

图 2-19. Power Query 中的列轮廓

超过千行限制

如果你处理的数据集超过一千行，请确保在 Power Query 的数据分析中包含所有观察结果。要做到这一点，请点击编辑器底部并选择“基于整个数据集的列分析”，如图 Figure 2-20 所示。

图 2-20. 覆盖数据分析的千行限制

借助 Power Query 的数据分析功能，您能够：

快速发现格式错误的单元格。
确定哪些列包含缺失值。
可视化每个变量的分布

结束数据分析

当您在 Power Query 中完成数据分析后，只需点击“关闭并加载”即可返回到 Excel，而不对查询进行任何更改。请记住，在对数据进行任何更改之前，数据分析是检查数据首要步骤。为了减少混乱，本书的其余部分将保持数据预览选项关闭状态。

结论

本章通过使用 Power Query 作为 ETL 工具来揭开与 Excel 相关的常见神话。它还探索了 Power Query 编辑器和数据分析过程。现在，您已经准备好使用 Power Query 转换数据，这将是第一部分剩余部分的重点。

练习

对于本章的练习，使用 Power Query 探索一个包含计算机价格的数据集。在书的附属库中的exercises\ch_02_exercises文件夹中打开ch_02_exercises.xlsx。执行以下操作：

将数据作为表格加载到 Power Query 中。将查询命名为computers。
向数据添加从 1 开始的索引列。
将应用步骤列表中的上一步重命名为“添加唯一标识符”。
拖放Index列，使其成为数据集中的第一列。
使用 Power Query 的数据分析功能回答以下问题。务必调整列分析以处理整个数据集。
- 数据集中计算机价格的范围是多少？
- 数据集中的平均 RAM 量是多少？
- 数据集中是否存在缺失值？如果有，位于何处？
将查询结果加载到 Excel 透视表中。

解决方案位于同一文件夹中的ch_02_exercise_solutions.xlsx中。

第三章：在 Power Query 中转换行

第二章作为 Power Query 作为 Excel 的 ETL 工具的神话破解能力的介绍。在第一部分的本章及即将到来的章节中，您将有机会进行常见数据转换任务的实践。本章的重点是行操作。

数据清理通常涉及行操作任务，如排序、过滤和删除重复项。传统的 Excel 提供了这些任务的界面引导方法，但可能会很繁琐且难以复制。Power Query 提供了一个解决方案，通过使数据清理过程可审计和可重复，无需编码。要跟随本章的演示，请在书的存储库的 ch_03 文件夹中访问 ch_03.xlsx。

在此工作簿的 signups 工作表中，您的组织派对策划委员会一直在收集 RSVP，并希望最终列表按字母顺序排序，消除重复项、空白和印刷错误。委员会厌倦了每当有新数据添加时就手动排序和删除不必要的行。他们希望能轻松刷新和重复使用工作簿，以便更多人注册或安排新的派对时使用。

将此数据加载到 Power Query 中，将查询命名为 signups。在继续之前，捕获列 A 中的所有相关行，并确保您的表包括标题。

移除缺失值

如第二章中提到的，Power Query 提供了专用的 null 值来表示缺失的数值。signups 数据包含三个空值，可能会引起混淆。要消除它们，请在功能区的主页选项卡上导航至删除行 → 删除空行，如图 3-1 中所示。

图 3-1. 在 Power Query 中删除空行

接下来，对列表按字母顺序排序。要执行此操作，请单击 Sign-up 列旁边的下拉按钮，您将找到与基本 Excel 类似的排序和过滤选项，如图 3-2 所示。选择升序排序以按字母顺序排序列表。

您可能已经注意到此数据集中多次输入了 Phyllis。要去除重复项，请返回到主页选项卡，然后选择删除行 → 删除重复项。

图 3-2. 在 Power Query 中排序行

列表大多数情况下是干净的，除了一个拼写错误：“Klevin” 在第 9 行。仅仅删除空白或重复项无法捕捉到这种错误，突显了在数据管理中领域知识的重要性。Power Query 在标准清理任务中提供帮助，但某些情况需要对数据有更深入的理解。最后一步是通过过滤它从数据集中移除此拼写错误，如图 3-3 所示。

图 3-3. 在 Power Query 中过滤误印

刷新查询

恭喜你按照这些步骤清理了派对策划数据。为了使结果更易于访问，将清理后的数据集加载到 Excel 中：在主页选项卡中，点击关闭并加载。

Power Query 提供的不仅仅是从 Excel 中繁琐且容易出错的点按数据清洗过程中解脱出来的途径；其真正的优势在于能够通过单击刷新来更新您的工作。要看到其效果，请在原始的 signups 表中添加两行数据。例如，我将插入一行空行和一行来自 Nate 的注册信息。

要重新运行查询，请导航到 Power Query 输出表，右键点击它，并选择刷新，正如图 3-4 中所示。

图 3-4. 在 Excel 中刷新 Power Query 结果

表格将自动更新，应用所有步骤到刷新后的数据。这个工作簿现在具备了一键可复制的数据清洗流程，适用于未来任何的报名表。

将数据拆分成行

你是否曾经遇到过在 Excel 中有一个用逗号分隔的项目列表，希望将它们分割成单独的单元格的情况？看一下显示在图 3-5 中的例子。你可以在 ch_03.xlsx 的 roster 工作表中找到这些数据。

图 3-5. 清理名单

这个数据集包含了按部门和姓名分类的项目报名。我们的目标是根据姓名和部门方便地对这些数据进行排序和筛选。在经典的 Excel 中，你可能尝试使用文本分列功能，但结果可能会很混乱和不理想，就像图 3-6 中展示的那样。

图 3-6. 使用文本分列将 RSVPs 拆分为列

Power Query 提供了一个方便的解决方案，用于分割数据以达到期望的结果。

首先，将 roster 数据导入到 Power Query 中并命名查询为 roster。在数据集中选择 Signups 列。在 Power Query 编辑器中，转到主页选项卡，并点击拆分列选项。从下拉菜单中选择按分隔符分割，如图 3-7 所示，以继续。

图 3-7. 使用分隔符拆分 Power Query

术语分隔符指的是数据中每个项目之间的字符。在这种情况下，分隔符是逗号，Power Query 可能会自动检测到它。如果没有，选择下拉列表中的逗号。接下来，点击高级选项。在这里，你将找到将文本拆分为行而不是标准列的隐藏选项，就像图 3-8 中显示的那样。点击行，然后选择确定。

图 3-8. 将文本转换为行

此查询即将加载，但还有最后一个任务要完成。要了解详细信息，请导航到 Power Query 编辑器功能区中的“视图”选项卡。在“数据预览”组中，确保“显示空格”已选中。关于 Power Query 的数据预览和分析功能的详细信息，请参阅第 2 章。

在 Signups 列中看到了额外的空格，这是由逗号分隔名单时留下的遗留问题。要删除它们，请右键单击列标题，然后选择“转换” → “修剪”，如图 3-9 所示。

图 3-9. 在 Power Query 中修剪空格

您现在可以关闭并加载结果到表中，如图 3-10 所示：

图 3-10. RSVP 数据拆分为行

填充标题和单元格值

有时您可能会遇到数据集的部分内容错误地被误标记为 null 或其他原因导致缺失。这可能是由外部系统的格式问题或不良的数据存储实践所致。

本节展示了 Power Query 如何帮助修复缺失的标题和值。在下一个演示中，请参考 ch_03.xlsx 中的 sales 工作表。

替换列标题

企业资源规划（ERP）提取通常包含一行额外的填充了无关信息。在这个特定的数据集中，第一行在每列都标有 ###，而实际的列标题可以在第 2 行找到。与其每周手动处理此问题，删除不必要的行，不如通过 Power Query 自动化清理工作。

将数据加载到 Power Query 中，并将查询命名为 sales，然后导航到主页选项卡，在“转换”组中，选择“将第一行用作标题”，如图 3-11 所示。

图 3-11. 在 Power Query 中使用第一行作为标题

填充空白行

现在列标题问题已解决，是时候解决误删空白行的问题了。看起来 ERP 系统未能在每个类别的每个值上重复 region 标签，这在使用数据透视表或其他功能分析数据时可能会带来困难。要解决此问题，请选择 region 列，然后转到“转换”选项卡，在“任意列”组中，选择填充 → 向下。这可以在图 3-12 中观察到。

图 3-12. 填充空白值

数据已成功清理。现在可以关闭并加载结果。

结论

Power Query 是清理数据行的强大工具，为排序、筛选、消除重复项和管理缺失值等任务提供了简化和高效的流程。第 4 章将继续讨论此重点，将注意力转向列的转换。

练习

在本书的配套存储库中的exercises\ch_03_exercises文件夹中打开ch_03_exercises.xlsx。它包含两个工作表。使用 Power Query 对数据进行操作和分析。

在states工作表上：

从数据中删除United States行。
填补region和division列中的空白。
按population从高到低排序。
将结果加载到一个透视表中。

在midwest_cities工作表上，将这些数据加载到每个城市单独占据一行的表格中。

在同一文件夹中可以找到一个已完成的版本，ch_03_exercise_solutions.xlsx。

第四章：在 Power Query 中转换列

第三章侧重于熟悉对行操作；本章的重点转移到列上。本章包括各种技术，如转换字符串大小写、重新格式化列、创建计算字段等。要按照本章的演示操作，请参考书籍存储库中 ch_04 文件夹下 ch_04.xlsx 中的 rentals 表。继续加载 rentals 表到 Power Query 中。

更改列大小写

Power Query 简化了在小写、大写和“合适”大小写之间转换文本列的过程（每个单词首字母大写）。要测试此功能，请按住 Ctrl 键并同时选择Title和Artist Name列。接下来，右键单击其中一列，导航到转换 → 每个单词大写，如图 4-1 所示。

图 4-1. 在 Power Query 中更改文本大小写

注意，Title和Artist Name在冒号和逗号后缺少空格。要解决此问题，仍然选择这两列，右键单击任意列，并选择替换值。在替换值对话框中，搜索“:”，并用冒号后跟一个空格替换它，如图 4-2 所示。

图 4-2. 替换 Power Query 中的数值

接下来，对逗号应用相同的过程：用逗号后跟一个空格替换它们。

正如在第三章中展示的，Power Query 在应用步骤列表中捕获您对数据执行的每一步操作。与传统的查找和替换过程相比，这个功能极大地简化了文本更改的审计过程。

按列分隔

在第三章中，您学习了如何将逗号分隔的文本拆分为行。现在是时候对列执行相同的操作了。右键单击Item #列，并选择分列 → 按分隔符分割。在对话框中，从下拉菜单中选择空格，然后单击确定。再次，与传统的文本到列功能相比，这个过程提供了更好的用户友好性和更广泛的功能。

初始时，分隔的列标记为Item #.1和Item #.2。要重命名它们，只需在编辑器中双击列标题即可。与 Power Query 中的所有修改一样，这些更改都通过应用步骤记录，允许根据需要轻松地撤消或调整。

更改数据类型

在 Power Query 中，每列都被分配了特定的数据类型，这定义了可以对其执行的操作。在导入数据集时，Power Query 会自动尝试为每列确定最合适的数据类型。然而，在某些情况下，可以增强或调整此自动检测。

例如，考虑UPC列。默认情况下，它被分配为整数数据类型。然而，由于我们不预期在这一列上进行重要的数学操作，将其存储为文本更合适。要做到这一点，请单击UPC列旁边的数字图标，并将其数据类型更改为文本，如在图 4-3 中所示。

进行以下数据类型更改：

将ISBN 13列转换为文本。
将Retail列转换为货币。

图 4-3. 在 Power Query 中更改列数据类型

删除列

从数据集中删除不必要的列可简化处理和分析过程。选择BTkey列，然后按 Delete 键将其从查询中移除。如果以后决定包含此列，可以通过已应用步骤列表轻松检索，如第二章中所述。

处理日期

Power Query 提供了一系列复杂的方法来管理、转换和格式化日期。它便于修改日期类型，允许用户提取诸如月份编号和日期名称等组件，然后将这些组件存储在最合适的数据类型中。

要探索此功能，让我们以几种不同的方式将其应用于Release Date列。首先创建此列的副本：右键单击列并选择“复制列”。再执行此操作两次，以生成共三个重复日期列。

右键单击第一个复制的Release Date列，然后导航到 Transform → Year → Year，如图 4-4 所示。该列将被重新格式化，并将其类型更改为仅显示年份，而不是完整日期。

图 4-4. 在 Power Query 中转换日期列

从下两列中提取月份和日期数字。双击列标题，并分别重命名为Year、Month和Day，以反映重新格式化的数据。将您的结果关闭并加载到 Excel 表格中。

在 Power Query 中成功执行一系列面向列的数据操作，做得很好。您已准备好将此查询加载到 Excel 中。

创建自定义列

添加计算列是数据清理中的常见任务。无论是利润率、日期间隔还是其他内容，Power Query 通过其 M 编程语言处理此过程。

对于下一个演示，请前往ch_04.xlsx的teams工作表。此数据集包括自 2000 年以来每支大联盟棒球队的赛季记录。我们的目标是创建一个新列，计算每支球队在赛季期间的胜率。这个计算通过将胜利次数除以总胜负次数完成。

当然，第一步是将数据加载到 Power Query 中。然后从编辑器的功能区中，转到“添加列” → “自定义列”。将自定义列命名为Wpct，并使用以下公式定义它：

[W] / ([W] + [L])

Power Query 的 M 编程语言遵循类似 Excel 表格的语法，其中列引用用单方括号括起来。利用 Microsoft 的 IntelliSense，按下 Tab 键在键入这些引用时自动完成代码。此外，您可以从“可用列”列表中双击所需的列，将其插入到公式区域。

如果一切正确，对话框底部将显示绿色的复选标记，表明未检测到语法错误，如图 4-5 所示。

图 4-5. 创建胜率计算

创建此列后，立即在 Power Query 中更改其数据类型为百分比。

加载和检查数据

我们的新列已经计算并准备就绪。在 Power Query Editor 的功能区上，转到主页 → 关闭并加载 → 关闭并加载到，然后选择透视表报表和确定。从那里，您可以分析数据，例如计算每个团队名称的平均Wpct，如图 4-6 所示。

图 4-6. 在透视表中总结结果

计算列与度量值

需要注意的是，在透视表中显示的平均Wpct是本赛季胜率的简单未加权平均值。这意味着比赛较少的赛季（例如受到疫情影响的 2020 赛季）对计算有不成比例的影响。为了验证这一点，请将透视表中的Wpct 平均值与我们自己的 Excel 计算进行比较，如图 4-7 所示。

图 4-7. 看似出现的透视表计算错误

要解决此问题，一种方法是使用动态度量值进行实时聚合和根据分析上下文进行计算。这是通过诸如 Power Pivot 的数据模型和 DAX 语言实现的，详见本书的第 II 部分。

这并不意味着在 Power Query 中应完全避免计算列。它们易于创建且计算效率高。尽管如此，如果这些列可能导致误导性的聚合，建议选择 DAX 度量值。

数据重塑

在第 1 章中，您已经了解了“整洁”数据的概念，其中每个变量都存储在一个且仅一个列中。您可能还记得sales工作表作为不整洁数据的一个例子。幸运的是，Power Query 解决了这个关键的数据存储问题。首先，导航到ch_04.xlsx工作簿的熟悉sales工作表，将此表加载到 Power Query 中以启动数据转换过程。

目标是将所有销售列“unpivot”或“melt”成一个名为sales的列，以及这些销售的标签在一个名为department的列中。为此，按住 Ctrl 键选择前三个变量：customer_id、channel和region。右键单击并选择 Unpivot Other Columns，如图 4-8 所示。

图 4-8. 在 Power Query 中对数据集进行 unpivot 操作

默认情况下，两个未整理的列将被称为Attribute和Value。将它们分别重命名为department和sales。现在可以将查询加载到数据透视表中，并分析按渠道和地区的销售情况。基于这些重塑数据创建数据透视表的结果和好处见于图 4-9。

图 4-9. 在未整理的数据集上使用数据透视表操作

结论

本章探讨了在 Power Query 中操作列的不同方式。第 5 章进一步讨论了在单个查询中使用多个数据集的方法。您将学习如何合并和附加数据源，以及如何连接外部源如.csv文件。

练习

使用书本的附属库中的exercises\ch_04_exercises文件夹中的ch_04_exercises.xlsx文件，在 Power Query 中练习转换列。对这份工作订单数据集执行以下转换：

将date列转换为月份格式，例如将1/1/2023更改为January。
将owner列转换为 Proper Case 格式。
将location列拆分为两个单独的列：zip和state。
重新整理数据集，使得subscription_cost、support_cost和services_cost合并为两列：category和cost。
引入一个名为tax的新列，计算cost列值的 7%。
将zip变量转换为文本数据类型，并将cost和tax列更新为货币格式。
将结果加载到表格中。

要查看这些转换的解决方案，请参阅同一文件夹中的ch_04_solutions.xlsx。

第五章：在 Power Query 中合并和追加数据

到目前为止，在第一部分中，您已经学习了使用 Power Query 转换单个表格的行和列的各种操作。然而，数据通常来自多个表格，包括 Excel 外部的数据源。在本章中，您将了解如何将多个文件合并成一个数据集。

由于本章重点是连接到外部文件而不是工作簿内部的表格，请通过打开一个新的工作簿开始跟进。

追加多个来源

数据通常以需要垂直堆叠文件的格式到达。例如，图 5-1 展示了一个常见情景，即销售数据分别以单独的表格呈现了一月、二月和三月的数据。在这些情况下，将它们合并为一个来源是很有帮助的。这使得例如计算第一季度的总销售额成为可能。

图 5-1. 追加数据集的简单示例

在 Power Query 中，追加操作有助于简化此过程。

连接到外部 Excel 工作簿

到目前为止，本书已经使用 Power Query 来操作工作簿内部的数据源。然而，Power Query 的实用性远不止于此。它大大扩展了与多种数据源的集成能力，特别是外部的 Excel 文件和 .csv 文件，这将是本章的主要关注点。该书配套资料库中的 ch_05 文件夹包含了从体育记者肖恩·拉曼的大联盟棒球数据库，直至 2022 赛季结束的数据集。

文件 people_born_in_usa.xlsx 和 people_born_outside_usa.xlsx 包含了有关出生在美国以及出生在美国以外的个人的信息。目标是使用 Power Query 将这两个文件垂直追加到一个表中。

要开始这个过程，请在功能区的数据选项卡上导航并选择获取数据 → 从文件 → 从 Excel 工作簿获取数据，如图 5-2 所示。

图 5-2. 在 Power Query 中连接到 Excel 文件

首先连接到 people_born_in_usa.xlsx。请记住，Excel 工作簿可以包含多个工作表、命名范围、表格等内容。这意味着您需要精确选择要加载到 Power Query 中的工作簿实体。在这种情况下，我们希望加载people_born_in_usa表格，因此请点击导航对话框中搜索栏下方的该选项，如图 5-3 所示。

图 5-3. 将外部 Excel 工作簿加载到 Power Query 中

在将数据加载到工作簿之前，您可以通过访问 Power Query 编辑器来清理或转换数据。可以通过选择“转换数据”按钮来完成此操作。但是，目前我将直接将数据加载到工作簿中。如果以后需要转换数据，则可以返回 Power Query 进行必要的调整。

要继续，请单击“加载”旁边的下拉按钮，然后在导航器对话框中选择“加载到”并选择“仅创建连接”选项。由于目标是稍后将此文件附加到另一个文件以进行联合分析，因此在此阶段无需将数据加载到单独的 Excel 表格中。

接下来，重复这些步骤以加载people_born_outside_usa.xlsx。同样，将查询加载为仅创建连接。现在，您已经仅将这两个文件加载为 Power Query 的连接。

在选定“数据”选项卡时，回到功能区的“查询与连接”中。在这里，您将找到列出为连接的people_born_in_usa和people_born_outside_usa。右键单击people_born_in_usa，选择“编辑”以打开 Power Query 编辑器，如图 5-4 所示。

图 5-4. 在“查询与连接”窗格中查看查询

附加查询

接下来，转到 Power Query 编辑器功能区的“主页”，并在“组合”组中单击“附加查询”下拉菜单。选择“附加查询作为新查询”继续，如图 5-5 所示。

图 5-5. 在 Power Query 中附加查询作为新查询

附加查询将多个表格的数据合并到现有查询中，从而扩展其大小，而“附加查询作为新查询”将它们合并到新查询中，保持原始表格不变。

注意

您希望附加的查询应具有一致的数据结构，包括相同数量的列、列名和数据类型。否则，在附加之前可能需要执行一些数据转换步骤来对齐结构。

现在，您将看到一个附加对话框，询问要合并成单个表格的表格是哪些。选择people_born_in_usa和people_born_outside_usa，如图 5-6 所示。

图 5-6. 在 Power Query 中附加两个表格

干得好！您已经附加了两个表格，创建了一个名为Append1的新查询。为了清晰起见，将其重命名为people_append。关闭并加载您的结果到 Excel 表格中。生成的查询将有 20,370 行，代表了来自两个表格的行的合计。您可以使用 Power Query 的数据分析功能确认此计数，如第 2 章中所述。

理解关系连接

将所有个体级别记录附加到一个表后，下一步是将其与其他表格连接以获取进一步的见解。原始的 Lahman 数据库包括各种个体记录的表，包括击球记录、全明星比赛出场记录等。通过利用 playerID 列，这些表可以在 Power Query 中高效地互连。

要在当前工作簿中尝试这一功能，请使用 Power Query 连接到同一文件夹中名为 hof_inductions.csv 的数据集。该数据集包含了进入棒球名人堂的入选者信息。要继续，请在 Excel 中导航到数据 → 获取数据 → 从文件 → 从文本/CSV。然后，定位并选择 hof_inductions.csv。由于 .csv 文件不支持像 Excel 那样的多个工作表或范围，它将立即显示数据，如图 5-7 所示。

图 5-7. 将 .csv 文件加载到 Power Query 中

将 hof_inductions 仅作为连接加载到您的工作簿中。

将所有相关数据加载到 Power Query 后，现在是时候找到一种方法，通过共享的 playerID 列，将 people_append 表中的信息与 hof_inductions 表中的信息整合在一起。

可以选择的一个方法是使用 Excel 查找函数，如 VLOOKUP()，来检索每个playerID值的对应姓名记录。我喜欢称呼 VLOOKUP() 为 Excel 的“胶带”，因为它可以将额外的列附加到数据集中。

但是如果 VLOOKUP() 是胶带，那么关系连接就是完整的焊接机。我这样说是因为 VLOOKUP() 主要设计用于 Excel 环境中的单条件查找。此外，它没有处理缺失值的系统化方法，这可能导致数据不一致。它还可能导致工作簿运行缓慢且不可靠，因为每次工作簿计算时都必须重新计算每个 VLOOKUP() 公式。

注意

Excel 中的新 XLOOKUP() 函数被设计为 VLOOKUP() 的现代升级，解决了其几个限制。然而，与 Power Query 中的关系连接相比，它也不能完全解决所有问题。有关 XLOOKUP() 函数的更多信息，请参阅第十章。

Power Query 提供了更全面的解决方案。它允许根据多个条件合并数据，更高效地处理大型数据集，提供系统化的方法来处理缺失值，记录转换步骤以确保数据完整性，并且可以从各种来源获取数据。

这种方法在计算效率上也更高，因为合并只需创建一次，并且仅在查询刷新时重新评估。生成的合并表是一个平面、无公式的对象，更易于使用。这使得 Power Query 成为处理复杂数据集成和转换的终极工具。

以下部分探讨了两种最常见的关系连接类型：左外连接和内连接。

左外连接：类似于 VLOOKUP()

左外连接 保留来自第一个合并表的所有记录，并在第二个表中查找匹配的值。如果找不到匹配项，则返回null结果。此连接类型非常类似于VLOOKUP()，但有一个显著区别是使用null来指示缺失值，而VLOOKUP()会返回#N/A。

在小数据集上执行左外连接的结果示例如图 5-8 所示。

图 5-8. 左外连接示例

要开始连接，请返回到 Power Query Editor 并选择people_append表。接下来，转到功能区中的合并组，并选择“合并查询为新查询”，如图 5-9 所示：

图 5-9. 在 Power Query 中合并查询为新查询

要完成连接操作，请在合并对话框的第二个下拉菜单中选择hof_inductions。点击两个表中的playerID来标识它作为连接依据的列。最后，在第三个下拉菜单中确认左外连接为所需的连接类型。

您的合并对话框应如图 5-10 所示。

图 5-10. Power Query 中的左外连接

单击“确定”，您将在名为Merge1的查询中看到合并结果。双击该名称以重命名查询为people_left。

滚动到您的people_left数据集右侧。我们查询的数据看起来有点不寻常，特别是hof_inductions列，它在数据的每一行中都设置为表。这表示一个嵌套表，包含第二个表中与第一个表中给定行匹配的所有行。

单击hof_inductions标题旁边的按钮，然后单击“确定”以展开嵌套数据，如图 5-11 所示。

图 5-11. 展开左外连接的结果

您可以从此菜单中选择hof_inductions表中匹配记录的多个列。您还可以选择使用其源表的名称作为这些列的前缀。为简单起见，我们将使用默认方法加载所有带前缀的列。然而，在实际工作中，为了更简洁的查询，您可能更倾向于减少选择的列数。

将结果加载到 Excel 表中。

people_left表，就像原始的people_append表一样，有 20,370 条记录。这是因为左外连接包含people表中的所有记录，无论左侧是否有匹配记录。连接的结果类似于VLOOKUP()，为每位球员拉取相关的名人堂记录。它的优势在于一次性从hof_inductions表中获取所有记录，并且不会因为未匹配的记录而出错。

内连接：仅保留匹配项

相比之下，内连接 只保留在结果表中在两个表中都有对应匹配的记录，如图 5-12 所示。

图 5-12. 内连接示意图

按照左外连接的逻辑，右表中的country_id为 4 的条目将不会出现在结果表中，因为左表中没有对应的匹配。同样地，左表中country_id为 99 的条目也将因为同样的原因被排除在结果之外：它在右表中缺少对应项。要包含在结果中，必须在两个表中都找到匹配项。

这种方法非常有用，可以保留仅有完整记录并消除可能存在完整性问题的数据。按照这些规则进行内连接将生成比左外连接更少的行。

按照上述步骤，在 Power Query 中执行内连接。在编辑器中选择people_append，然后依次选择主页 → 合并查询 → 合并查询为新查询。你的合并对话框应该类似于图 5-13。

图 5-13. 执行内连接的合并设置

你可以按照左外连接的方式扩展嵌套表中的匹配列，然后将查询重命名为people_inner。将得到的查询加载到一个表中。该表仅包含 323 条记录。

区分非常直接：内连接仅返回两个表中都有相应匹配的记录。并非所有的playerID实例都出现在hof_inductions表中，因为并非每位球员都入选名人堂，所以在合并表中缺少他们的playerID。

管理你的查询

在从各种来源和格式加载和合并数据方面做得非常好！随着你深入使用 Power Query，你的工作簿中可能会累积多个查询。管理和理解这些查询如何相互作用和共同运作将变得至关重要。

分组你的查询

在 Power Query 中对查询进行分组可增强组织性并通过对相关查询进行分类来简化维护工作。这种方法使得管理复杂的 Excel 项目更为容易。通过对查询进行分组，你可以清楚地区分基础查询和依赖于这些基础查询（如附加和合并）的查询。

要尝试它，请返回 Power Query 编辑器。

在编辑窗口左侧的查询列表下，按住 Ctrl 键选择源查询：people_born_in_usa、people_born_outside_usa 和 hof_inductions。右键单击，然后选择移动到分组 → 新建分组，如图 5-14 所示。

图 5-14. 在 Power Query 中创建分组查询

当新建分组弹出窗口出现时，请将此分组命名为 Sources。点击确定。您现在将看到这三个来源被分组到查询列表中的一个文件夹中。hof_append、hof_left 和 hof_inner 也自动移动到名为其他查询的组中，如图 5-15 所示。

图 5-15. 查看分组查询

查看查询依赖关系

查看查询依赖关系显示了查询之间的互相关联，有助于识别变更的影响并有效地管理依赖关系，确保复杂项目中的数据完整性并减少错误。要查看它，请转到功能区中的“视图”，然后选择“查询依赖关系”。它应该类似于图 5-16。

在这里，您可以看到直接从原始数据源（如 .csv 文件）派生的查询，这些文件的位置，涉及合并或追加的来源，加载到工作簿中的来源等等。

图 5-16. 查看查询依赖关系

当您完成查看查询依赖关系图后，请单击关闭。

结论

本章重点介绍了在 Power Query 中合并和追加数据的过程以及理解结果的重要性。这些工具允许将各种文件（如 Excel 工作簿和 .csv 文件）集成到统一的数据集中。通过有效地连接表格，数据分析变得更加高效。

在 Power Query 中还有其他的连接类型，比如外连接，它会返回两个表中匹配的行。有关连接技术的详细概述，请参阅此 Microsoft Learn 文章。

第一部分的主题是高效数据分析所需的干净数据的必要性。在建立了数据清理的坚实基础后，数据分析的下一阶段涵盖了使用 Power Pivot 进行建模和报告。这是第二部分的重点。

练习

练习：结合书中 exercises\ch_05_exercises 文件夹中的文件，使用这些文件可以获得 2013 年纽约市三大机场所有出站航班的信息。这些文件位于书的伴侣存储库中。

将ewr-flights.csv、jfk-flights.csv和lga-flights.csv文件追加到一起，包含从纽瓦克自由国际机场、约翰·肯尼迪国际机场和拉瓜迪亚机场的航班记录。将此查询命名为flights。（提示：在 Power Query 的追加菜单中选择“三个或更多表格”以加快此过程。）
使用左外连接将此查询与planes.xlsx合并，然后再进行内连接。分别将查询命名为flights_left和flights_inner。每个查询返回多少条记录？（提示：根据tailnum合并表格。）

你可以在ch_05_solutions.xlsx文件的同一文件夹中找到解决方案。

第二部分：使用 Power Pivot 进行数据建模与分析

第六章：Power Pivot 的第一步

本书的第 I 部分专注于使用 Power Query 从各种来源提取数据并将结果转换为可操作的数据集。Power Query 并不作为独立的数据分析工具，而是作为在分析之前优化数据的中介。

要继续分析之旅，请关注第 II 部分，该部分将专注于 Power Pivot，这是一个专门用于数据分析的工具。使用 Power Pivot，用户可以在数据源之间建立关系并生成高级指标，实现简化的数据分析和报告。

什么是 Power Pivot？

Power Pivot 是直接集成到 Excel 中的关系数据建模和分析工具。它使您能够在多个表格之间建立关系，并基于这些数据模型构建仪表板和报告。Power Pivot 提供了多种工具，用于创建强大的分析，显著增强了 Excel 在商业智能和报告方面的能力。

为什么要使用 Power Pivot？

要了解 Power Pivot 在 Excel 数据分析中的重要性，请从本书附带的仓库的 ch_06 文件夹中打开 ch_06.xlsx 文件。请注意，本章节不包括解决方案文件，因为所有步骤都已经为您完成。

在 sales 工作表中，有三个包含与销售、位置和产品相关的数据的表格。假设您希望为每个销售交易分配正确的产品和分支名称，以增强清晰度。

在 Excel 中，有几种方法可以实现这一点。一种流行的方法是使用 VLOOKUP() 函数从一个表格中提取值到另一个表格中，正如图 6-1 所示。

图 6-1. 使用 `VLOOKUP()` 合并数据源

虽然 VLOOKUP() 函数经常被使用，但它也有其局限性。正如在第五章中指出的那样，查找函数的输出是静态的，仅仅增加现有表格而不是创建新的数据源。当逐列创建查找语句时，这种方法的扩展变得非常繁琐。

使用查找函数意味着 Excel 必须保留并筛选其内存中的所有查找数据。随着数据量的增长和执行更多查找操作，工作簿可能变得缓慢甚至冻结。我将这些超大和笨重的 Excel 数据集称为“Frankentables”。

在第五章中，您已经了解到了使用 Power Query 合并数据源的更高效方法。如果您使用了这种方法，如图 6-2 所示，您将得到一个不带公式的新表格，但其维度与通过查找函数获得的早期结果相匹配（假设应用了左外连接）。

与查找函数相比，Power Query 更为灵活和高效，但并非每项任务都是最佳选择。类似于 VLOOKUP()，它将每个数据点整合到一个平面表格中，导致文件大小增加和记录重复。请记住，Power Query 的主要角色是数据清理，而不是数据分析。它缺乏创建年度累计计算或动态聚合等高级度量的功能。

图 6-2. 使用 Power Query 合并数据源

为了获得更强大、高效的分析体验，最好通过建立 Power Pivot 的关系数据模型来组合这些数据源。

表 6-1 总结了每种数据源组合方法的优缺点。

表 6-1. 使用 XLOOKUP()、Power Query 和 Power Pivot 组合数据源的比较

工具	优点	缺点
XLOOKUP()

易于理解
可在原生 Excel 中使用

输出灵活性有限
逐个查找列
占用内存多

Power Query

控制输出更多
更容易审计和维护

关系连接可能令人困惑
将数据加载到 Power Query 需要额外的开销

Power Pivot

可以创建复杂的数据模型
内置计算和聚合函数

设置数据模型复杂
学习曲线陡峭
对于许多 Excel 用户来说，关系建模可能不熟悉

Power Pivot 和数据模型

Power Pivot 在数据模型内运行，建立和管理关系。这种方法允许从多个来源创建数据透视表，而无需物理合并它们。

利用 DAX 公式语言，Power Pivot 可以处理数据模型上的复杂计算，包括时间智能、排名、百分位等。

Power Pivot 的一个主要优势在于其有效管理多个数据源的能力。它不需要存储内存密集型的“Frankentable”，并根据需要计算 DAX 度量。然而，由于其陡峭的学习曲线，特别是处理未合并到单个表中的数据源时，掌握 Power Pivot 可能会很具挑战性。

在 ch_06.xlsx 的 sales_pp 工作表中，我已经创建了一个数据模型，包括三个销售数据源，并将结果加载到了一个数据透视表中。现在，我可以基于所有相关表格进行分析和计算，如图 6-3 所示。

图 6-3. 使用 Power Pivot 关系整合数据源

Power Pivot PivotTable 中存在重复的表名称

在本书的 Power Pivot 示例中，每个表名在生成的透视表中显示两次：一次带有橙色圆柱形图标，一次没有。始终选择带有图标的表，因为它们直接连接到数据模型并包含添加的任何计量。如果您从外部源导入表到数据模型，而不是使用工作簿中的表，则可以解决重复表的问题。

要调整这些表之间的关系或添加像计算列或计量一样的功能，需要加载 Power Pivot 加载项。

加载 Power Pivot 加载项

要访问 Power Pivot，请在选项卡上的“文件”选项中导航到“选项”→“加载项”。在加载项窗口中，选择“COM 加载项”并单击“前往”，如图 6-4 所示。

图 6-4. 加载 Power Pivot 加载项

在 COM 加载项对话框中，选择“Microsoft Power Pivot for Excel”并单击“确定”，如图 6-5 所示。现在您已经准备好使用 Power Pivot 了。

图 6-5. 选择 Power Pivot 加载项

检查选项卡上的新 Power Pivot 选项卡，如图 6-6 所示。

图 6-6. 选项卡上的 Power Pivot 加载项

Power Pivot 加载项简介

选项卡中的 Power Pivot 提供了创建和维护数据模型及其相关功能的各种选项。让我们逐个查看这些选项，提供一个高级概述。

数据模型

在 Power Pivot 中选择“管理”将打开一个专门的界面，展示数据模型中的表。它允许您可视化这些表之间的关系并提供其他功能。花些时间探索这个编辑器，在完成后简单关闭即可。随着第 II 部分的后续章节的进行，您将获得在此界面中操作的额外经验。

计算

在“Power Pivot”选项卡的“Calculations”组中，您可以创建计算度量和关键绩效指标（KPIs），这些内容我们将在第 II 部分的后续章节中详细介绍：

计量

Power Pivot 中的计量使用 DAX 语言执行计算，聚合数据并进行高级数据处理和分析。它们聚合数值，计算总数、平均数和百分比，并且对于高级 Excel 分析至关重要。

KPIs

KPI 是一个可衡量的值，用于说明公司或组织在实现其主要业务目标方面的效果。KPI 对于评估是否达到目标、监控进展并指导决策过程至关重要。Power Pivot 允许用户在基于透视表的仪表板和报告中创建和显示 KPI。

表

Power Pivot 的这一部分允许您将工作簿表格导入数据模型。然而，建议通过 Power Query 导入数据，如第七章所讨论的那样。Power Query 提供了连接到各种数据源的功能，例如外部工作簿和.csv文件，涵盖在第 I 部分，并且它还可以在创建数据模型之前进行数据清洗。

关系

此功能可以自动识别和创建数据模型内表格之间的关系。虽然这是一个有价值的工具，在掌握了本书涵盖的基础知识之后值得探索，但至关重要的是要有足够的理解能力，以便判断数据模型是否已经准确构建。因此，我们的重点将放在手动创建关系上，而不是依赖于这个自动选项。

设置

这些设置增强了数据模型的计算性能，并揭示了潜在的问题。深入使用超出了本书的范围。

结论

本章揭示了 Power Pivot 的能力，即从多个来源简化数据，而无需将其组合成一个表，使其成为解决“Frankentables”问题的解决方案，就像 Power Query 一样打破了常见的 Excel 神话。尽管 Power Pivot 的功能对传统的 Excel 用户来说可能显得令人畏惧，但其能力是无与伦比的。

第 II 部分的后续章节将更深入地探讨 Power Pivot，研究数据模型的创建和分析的复杂性。Power Pivot 简化了发现洞见、做出明智决策和在 Excel 中制作复杂分析的过程。

练习

要检查您对本章涵盖的概念的理解程度，请回答以下复习问题：

Power Pivot 加载项的目的是什么，它可以让您做什么？
解释 Power Pivot 中数据模型的角色及其在数据分析中的重要性。
在 Power Pivot 中，DAX 度量和关键绩效指标的基本作用是什么？
在组合数据源方面，将 Power Query 连接与 Power Pivot 关系进行比较。
使用类似VLOOKUP()或XLOOKUP()的查找函数来合并 Excel 中的表格有哪些缺点？

关于这些问题的示例答案可以在书的伴侣存储库的exercises\ch_06_exercises文件夹中找到。

第七章：在 Power Pivot 中创建关系模型

第六章介绍了 Power Pivot 作为处理来自多个来源的数据时的有效工具，尤其是数据分析和报告。本章演示了如何使用 Power Pivot 进行关系数据建模。

连接数据到 Power Pivot

如第六章所示，数据模型是 Power Pivot 的基础，有助于有效的数据计算和分析的表关系管理。Power Pivot 使用直观的拖放界面简化了这项任务。本章深入讨论了数据模型，使用 ch_07.xlsx 文件。该文件包含了分析社区经常引用的零售销售数据集，位于书籍资源的 ch_07 文件夹中。

在第六章的示例中，数据模型是预定义的。在本章中，我们需要手动定义它。

虽然通过 Power Pivot 直接连接到数据源是可行的，但建议首先通过 Power Query 进行数据通道。这种方法为需要时建立这些表上的任何重复数据清理程序提供了一个便捷的平台。

要开始，请使用数据 → 从表/范围导入orders表到 Power Query。跳过任何数据转换步骤，然后在主页选项卡的关闭并加载 → 关闭并加载到中进行操作。

要将此查询加载到 Power Pivot 中，请选择将数据创建为仅连接，然后选择“将此数据添加到数据模型”，如图 7-1 所示。

图 7-1. 从 Power Query 加载查询到 Power Pivot

遵循这些步骤，可以使查询在 Power Pivot 中用于数据建模，但不会出现在单独的工作表中供检查。需要注意的是，Power Pivot 的主要目的是将此表与其他表关联起来，构建 DAX 度量等功能。直接将数据加载到工作簿中会掩盖这些功能。

对returns和users表重复执行这一过程，确保你的工作簿中包含三个仅连接的查询，并将其添加到数据模型中。

创建关系

在 Power Pivot 中，表之间的关系优化了数据分析，允许创建复杂的模型，而无需传统繁琐的数据合并工作。这种方法增强了一致性，减少了冗余，并简化了数据集管理。通过这些连接，用户可以实现动态和交互式的数据探索，提升 Excel 的分析能力。

要在orders、returns和users表之间建立关系，请导航到功能区上的 Power Pivot 选项卡。接下来，选择“管理”并选择视图组下的“图表视图”。在图表视图中，将显示三个表及其各自的列名，如图 7-2 所示。

图 7-2. Power Pivot 编辑器中的图表视图

如果您的表格不像图 7-2 中显示的顺序一样，不要紧。我们将建立这些表格之间的关系，这些关系将不受图表视图中的排列顺序影响。一旦通过这些关系了解了表格的内容，我们会将它们可视化为更一致和高效的布局。

要创建第一个关系，请先选择orders表。然后，在 Power Pivot 的设计选项卡中，转到“关系”组下的“创建关系”并单击，如图 7-3 所示。

图 7-3. 在 Power Pivot 中创建关系

要在 Power Pivot 中的orders和returns表之间建立关系，请从下拉菜单中选择returns表。在两个表格中突出显示Order ID列。完成过程，请单击“确定”，如图 7-4 所示。

图 7-4. 在`orders`和`returns`之间创建关系

类似于VLOOKUP()函数，数据关系基于表格之间的共同列。这里，共享列是Order ID。设置此关系并单击“确定”后，将显示连接两个表格的线条，如图 7-5 所示。

图 7-5. 在`orders`和`returns`之间已创建关系

要在数据模型中建立最终关系并连接所有三个表格，您可以使用同时存在于orders和users中的Region字段。与使用“创建关系”选项不同，更有效的方法是直接将Region字段在两个表格之间拖放。此操作将创建所需的关系，如图 7-6 所示。

图 7-6. 拖放以在`orders`和`users`之间创建关系

识别事实表和维度表

创建全面的数据模型后，一个不错的下一步是识别其事实表和维度表。事实表通常包含适合计算的定量数据，如平均值、最小值和最大值。另一方面，维度表包含提供事实表中测量内容背景的描述性数据。

例如，orders表包含多个可测量的数量，如销售额、利润和销售单位，可以对其进行求和、平均值等操作。这些数据代表您正在分析的业务或流程的核心指标。这些定量数据的存在表明这是一个事实表。

事实表通常缺乏对数据解释至关重要的上下文信息。例如，对于orders表，了解与公司每个区域相关的经理可能是有用的。users表作为维度表，因为它提供描述性上下文；具体来说，指出每个区域由哪个经理负责。维度表在切片、切块和深入了解数据方面发挥着至关重要的作用。

安排图表视图

在现实场景中，数据模型中常见的是一打或更多的表。正确组织图表对于用户有效理解数据至关重要。

一个有用的技术是将事实表放在图表的中心，并用维度表围绕它。这种视觉排列有助于理解表之间的关系和依赖性。要使用当前数据实现这一点，请点击并拖动returns和users表，使它们位于orders表的两侧，如图 7-7 所示。

图 7-7. 在图表视图中查看数据模型

注

在数据模型中，当一个事实表位于中心，并被维度表包围，如图 7-7 所示，这种安排被称为星型模式。星型模式是数据模型设计中的基础概念。其名称来源于模式的视觉表示，其中事实表位于中心，维度表向外辐射，类似星星的射线。

编辑关系

若要修改 Power Pivot 中定义的关系，您有几个选项。首先，您可以右键单击图表视图中的任何关系线，选择“编辑关系”，重新访问对话框，在那里您可以修改相关的表和列。其次，您可以通过右键单击关系线并从菜单中选择来暂时禁用或删除关系。

第三，您可以从集中位置管理数据模型中的所有关系。要做到这一点，请导航到功能区上的“设计”选项卡，然后单击“管理关系”。这将提供您数据模型中所有关系的全面视图，如图 7-8 所示，使您能够进行必要的调整。

图 7-8. 在数据模型中管理关系

“管理关系”对话框—如图 7-8 所示—提供了每个关系的基数和过滤方向的洞见。这些概念将在本章后面进一步探讨。

将结果加载到 Excel

一旦建立了数据模型，下一步是将结果转移到 Excel 中。这可以通过在 Power Pivot 编辑器的“主页”选项卡中导航，然后从数据透视表下拉菜单中选择一个选项来实现，如图 7-9 所示。

图 7-9. Power Pivot 加载选项

Power Pivot 提供了各种配置选项，用于将数据透视表加载到工作簿中。这些选项通常涉及数据透视表和数据透视图，因为 Power Pivot 通常用于创建基本的仪表板和报告。最终选项“扁平化数据透视表”会删除所有小计，并将数据以表格化、非嵌套的格式显示。

选择数据透视表选项。在“创建数据透视表”对话框中单击“确定”，将其插入到新工作表中。您现在应该有类似于图 7-10 的内容。

图 7-10. 由 Power Pivot 生成的数据透视表

现在，将users表中的Region字段拖到数据透视表的行区域。接下来，将orders表中的Sum of sales放置到值区域。数据模型将立即利用这些表之间的关系，特别是共享的Region值，准确执行计算。您可以在图 7-11 中查看结果。

图 7-11. 从多个表源的数据透视表

结果显示Sales字段舍入到三位小数，这是不寻常的，并且格式不清晰显示货币，使其难以阅读。可以在工作表或数据透视表中调整这些数字，但是持久修复方法是在 Power Pivot 数据模型中格式化数据。要做到这一点，请返回到功能区中的 Power Pivot 选项卡，然后单击“管理”。

在 Power Pivot 主页选项卡上，在“查看”组中单击“数据视图”。在数据视图底部选择orders工作表选项卡，以访问和操作此表。然后，单击Sales列，并将其格式调整为货币格式，同时启用千位分隔符，如图 7-12 所示。

图 7-12. 在 Power Pivot 中格式化列

欢迎在此处对源数据进行任何其他格式调整。

退出 Power Pivot 后，增强效果将清晰地显示在数据透视表中，如图 7-13 所示。

本书将继续对 Power Pivot 中列的格式进行更改，而不一定要明确地按照说明进行。

图 7-13. 数据透视表中格式化列的结果

理解基数

在 Power Pivot 中，当数字不按预期聚合或特定字段由于数据模型关系中的问题而无法使用时，通常会出现问题。这些问题通常源于对数据模型结构及其基数的不完全理解。让我们深入探讨这些要素。

较早的部分强调了在 Power Pivot 中创建关系时共享字段的重要性。每个表中唯一记录的计数在确定数据模型中关系功能的运作方式方面起到关键作用。基数是指一个表中与另一个表中条目相关的数量。

一对一基数

一个 一对一 的关系代表基数中最简单的形式，其中一个表中的每个条目唯一对应于另一个表中的单个条目。

考虑数据模型包含两个表的情况：product_details 和 supplier_details，如图 7-14 所示。

图 7-14. 一对一关系示例

在图 7-14 中，每条记录由唯一的 Product ID 标识，构成了两个表之间的关系基础。

尽管这种结构可能很有用，但通常并不是最有效的。合并表格可以最小化冗余，减少维护工作，并提高性能。作为 Excel 中数据建模工具的 Power Pivot，在实际数据模型中没有一对一基数的选项，突显了其应用的局限性。相反，Power Pivot 被调优用于一对多关系。

一对多关系

一个 一对多 的关系表示一个表中的多个记录对应另一个表中的单个记录。考虑图 7-15 中的示例。

图 7-15. 一对多关系示例

在这种模型中，来自一个表的客户可以在另一个表中拥有多个相关记录，例如订单。通过将相关记录存储在单独的表中，并通过每组的单个项目连接它们，这种方法减少了数据冗余，简化了更新和查询，并确保了数据完整性。这种高效的方法对于构建可扩展、可维护的数据库至关重要，准确捕捉业务运作的复杂性。

多对多关系

在两个不同表的实体能够形成多个连接的情况下存在 多对多 的关系。像 Power Pivot 这样的工具不直接支持这些关系。管理这些关系的常见方法是通过使用桥接或连接表。

考虑在零售设置中跟踪客户在多个促销活动中的购买情况，例如图 7-16。

图 7-16. 多对多关系示例

在本例中，每位客户和活动仅列出一次，表明每位客户可能在每个促销活动中购买多次。为了管理这种复杂性，我们引入了一个桥接表，用于映射哪些客户参与了哪些促销活动，如图 Figure 7-17 所示。

图 7-17. 多对多桥接表示例

这张表简化了多对多关系，展示了每位客户在特定促销活动中的参与情况。

为什么基数很重要？

基数在数据建模中起着至关重要的作用，确保数据的准确性和一致性。在一对多关系中，确认每个“一”实体与唯一对应的“多”实体，以及反之，尤为重要。

虽然 Power Pivot 不区分一对一和一对多关系，但掌握这一概念有助于提升 Power BI 中数据模型的性能，后者确实关注这些区别。有关深入了解 Power BI 关系，请参阅 Microsoft 的文档。

掌握各种基数，如一对一、一对多和多对多，在所有数据建模工具中都至关重要，不仅限于 Power Pivot。虽然 Power Pivot 强调一对多关系，但理解所有基数确保了组织化数据、保持完整性和平滑的工具集成。这种洞察力对故障排除和与数据同行有效沟通至关重要。简言之，对这些原则的深入理解为适应多样化数据景观提供了灵活性。

理解过滤方向

作为关系数据模型，Power Pivot 通过利用公共字段简化跨多张表的数据分析。调整这些字段上的过滤器会影响相关表，体现了过滤方向的概念，这与基数密切相关。

在此工作簿的图表视图中，通过 Region 字段连接 users 和 orders 表的关系显而易见。仔细观察表示这种关系的线条，可以看到一个小箭头指向从 users 到 orders，如图 Figure 7-18 所示。

图 7-18. `users` 到 `orders` 的过滤方向

注意

在图 Figure 7-18 中看到的星号表示表之间一对多关系中的“多”一侧。这种视觉表示快速展示了表之间关系的本质和基数。

箭头指示了过滤效果从一张表流向另一张表。对左表应用过滤会影响右表，但反之则不成立。

用用户筛选订单

要了解通过users表筛选orders的影响，首先将数据模型中的透视表插入工作簿中。将users表中的Region字段添加到筛选区域，将orders表中的Sum of Sales字段添加到值区域。例如，在调整Region字段时，选择中心，透视表将展示中心地区的销售总额，如图 7-19 所示。

图 7-19. 使用`users`表筛选`orders`

这种现象的技术术语是筛选“传播”，从users表传播到orders表。这是创建筛选时的预期行为，也是您可能已习惯的行为。

通过订单筛选用户

现在考虑使用orders表中的Region字段作为筛选区域和users表中的Manager字段作为行区域的透视表。

在对中心地区进行筛选后，发生了一些有趣的事情：数据保持完整，没有记录被省略。这在图 7-20 中有所体现。

图 7-20. 使用`orders`表筛选`users`

这带来了一个问题：为什么应用于orders表的筛选不影响users表？鉴于 Chris 是中心地区唯一的经理，他不应该是返回的唯一经理吗？答案在于筛选方向的原则。

筛选方向和基数

在 Power Pivot 中，筛选方向取决于关系类型。在一对多关系中，筛选从“一”方向到“多”方向。例如，users表可以影响orders表，但反之则不行。这种方法提高了性能，因为从记录较少的一侧向记录较多的一侧进行筛选更为高效。

从设计到实践的 Power Pivot 应用

爵士吉他手 Irving Ashby 曾将节奏吉他比作蛋糕中的香草：“你尝不出它的味道，但当它被遗漏时你会知道。”数据模型中的筛选方向正是如此。通常情况下，它在背景中悄然运行，但当出现问题时，其缺失将变得显而易见。

通过对数据模型方面的基本了解，如筛选方向和基数，我们现在可以深入探讨高级功能。探索计算列和层次结构将进一步完善数据模型，增加灵活性和功能性。

在 Power Pivot 中创建列

在第四章中，您已经了解了如何在 Excel Power Query 中创建计算列。现在，让我们深入探讨在 Power Pivot 中执行相同任务的时间和方式，同时考虑每种方法的优缺点。

Power Query 与 Power Pivot 的计算方法对比

Power Query 和 Power Pivot 是具有互补作用的不同工具，两者都可以生成计算列。要确定使用哪一个，考虑以下几点：

在准备阶段使用 Power Query 进行数据清洗和转换。它非常适合一次性任务，如合并字段或更改数据类型，在将数据加载到 Power Pivot 之前通过简化数据来优化模型。
使用 Power Pivot 进行高级分析，如动态计算或建立表之间的关系。这些操作在数据加载后完成，可以增强报告和仪表板。然而，过度使用可能会增加文件大小并降低性能。

遵循这些准则，您可以最大化 Power Query 和 Power Pivot 的能力，确保根据数据的状态和处理要求进行最佳的计算列创建。

提示

尽管这些经验法则很有帮助，但确定是在 Power Query 还是 Power Pivot 中创建计算列的最佳方法是尝试使用两种工具，看哪一种最适合您的需求。

示例：计算利润率

返回到 Power Pivot 编辑器。在数据视图中，选择orders表格。

创建一个名为Profit margin的计算列。滚动到表的末尾，在“添加列”内部点击以命名列为Profit margin,，然后按图 7-21 中显示的利润率公式（=orders[Profit]/orders[Sales]）添加。

图 7-21. 创建利润率计算列

您的计算列应该像这样：

orders[Profit] / orders[Sales]

请注意，与 Excel 表相比，您必须手动输入对表内其他列的引用，而不能通过鼠标点击或键盘击键来选择它们。

这是您首次使用 DAX 编程语言管理 Power Pivot 中数据模型。您会注意到，单独列的引用方式与基本 Excel 表的结构化列引用非常相似。您可以在数据视图中将新列格式化为百分比。

要验证此计算，将数据模型加载到新的数据透视表中。将Customer Segment拖到行部分，将Average of Profit Margin拖到值部分。为了交叉检查准确性，还需将Sum of Profit和Sum of Sales添加到值部分。

当按照图 7-22 手动计算利润率时，可能会发现与数据透视表值相比存在差异。

图 7-22. 检查利润率计算

问题出在Profit margin计算列计算的是单个利润率的基本平均值，而不考虑总利润和总销售额的聚合。要进行准确的利润率计算，需要动态和即时计算，这不能仅通过计算列来完成。而是需要使用 DAX 度量值，这将在第八章和第九章中广泛讨论。

目前，重要的是记住，在 Power Pivot 中，当存在可能对结果进行聚合时，不应使用计算列。这个问题类似于在 Power Query 中使用计算列时，当进行聚合时可能会产生失真。

在数据模型中，有些情况下计算列确实是合适的选择。其中一个例子是使用SWITCH()函数，下面的部分将详细探讨这个例子。

使用`SWITCH()`重编码列值

SWITCH()函数非常有价值，可用于应用条件逻辑来重新分配值。由于每一行都是独立评估的，通常结果不会被聚合，因此更适合将SWITCH()的结果保存为计算列，而不是度量。

举例说明，假设您想要将数字 1、2、3 和 4 分别分配给Consumer、Corporate、Home Office和Small Business四个部分。在找不到匹配项时，您希望将值重新编码为Unknown。首先在 Power Pivot 中的orders表中添加名为Segment number的新计算列，如图 7-23 所示。

图 7-23. 使用`SWITCH()`函数创建`Segment number`列

请记住，数据模型表中某列的所有值必须具有相同的数据类型。因为Segment number包含字符串Unknown，因此将其他值（1、2、3、4）转换为字符串以保持一致性是非常重要的。

将更新后的数据模型加载到新的 PivotTable 中，或者刷新现有的 PivotTable，以在分析中使用这个新列。例如，图 7-24 总结了按重新编码的段号而不是原始类别进行的销售情况。

图 7-24. PivotTable 中使用的`SWITCH()`结果

创建和管理层次结构

层次结构在我们生活的许多方面起着关键作用。以我撰写本书时的位置为例：俄亥俄州克利夫兰市，美国。这可以组织成一个层次结构，从最广泛的类别（国家：美国）开始，然后是更具体的类别（州：俄亥俄州），最后是最具体的位置（城市：克利夫兰）。将这样的层次结构集成到数据模型中，简化了数据分析和探索的过程，使得可以在不同详细级别上进行更有效的检查。

在 Power Pivot 中创建层次结构

让我们在数据模型中创建基于产品的层次结构，包括产品类别、产品子类别和产品名称。要做到这一点，请导航到图示视图。按住 Ctrl 键选择所需的层次顺序中的维度（例如，最上面是产品类别）。选择完成后，右键单击并选择“创建层次结构”。为层次结构分配一个名称，例如产品层次结构，如图 7-25 所示。

图 7-25. 在图示视图中看到的层次结构

在图示视图中，根据需要可以轻松添加、修改或删除数据模型中的层次结构。目前，将数据加载到数据透视表中以查看其运行情况。

在数据透视表中使用层次结构

关闭 Power Pivot 后，返回您的数据透视表。将产品层次结构放置在行部分，将销售总额放置在值部分。您将观察到，在数据透视表中，无法单独使用层次结构中的三个维度；它们只能作为层次结构的一部分使用。

现在，您可以单击任何产品类别左侧的小加号，以便在子类别级别和最终单个产品名称级别进入该类别，如图 7-26 所示。

图 7-26. 在数据透视表中上下钻取

切换按钮回到减号，以在层次结构中向上导航。在数据透视表分析选项卡的“活动字段”组中，您将找到其他简化层次结构操作的功能，例如同时展开或折叠整个层次结构的选项。

在将层次结构纳入数据之前，重要考虑数据质量不一致性的影响。例如，一个单一的子类别映射到多个类别时，该层次结构可能会失去其分析意义。此外，需要注意，对于不太熟悉 Excel 的用户来说，在使用层次结构时可能会面临一些挑战。

将数据模型加载到 Power BI 中

到目前为止，您已经掌握了创建数据模型的基本要素，包括如计算列和层次结构等宝贵功能。在第八章和第九章中，我们将探讨创建 DAX 度量和使用 KPI 等工具来改进数据分析和报告的方法。在我们继续之前，让我们简要探讨一种用于分析和可视化数据模型的替代方法：Power BI。我们将探讨 Power BI 的操作方式及其提供的优势。

Power BI 作为“现代 Excel”的第三个组成部分

到目前为止，本书主要集中在用于数据清理和数据分析的 Power Query 和 Power Pivot。该堆栈的第三个组件用于数据可视化的是 Power View，但最终已被停用。最初为 Excel 开发，Power View 允许创建交互式仪表板和报告。然而，随着时间的推移，Power View 的概念被整合到 Power BI 中，并且较新版本的 Excel 不再包含它或包含非常有限的功能。

微软决定将注意力从 Excel 中的 Power View 转移到 Power BI，是基于多种因素。Power BI 提供先进的数据可视化能力，使用户能够使用各种数据源构建交互式仪表板和报告。这一转变也与微软的云导向策略一致，因为 Power BI 主要作为云平台运行，支持从任何位置进行协作和数据访问。通过强调 Power BI，微软为用户提供了一个更全面、现代化和集成的商业智能解决方案，以满足不断发展的需求。

虽然 Power BI 以生成交互式仪表板而闻名，但一些分析师可能会发现它相对不太熟悉，这可能在构建和分享工作时带来挑战。从 Excel 开始构建数据模型仍然是因其在专业人士中广泛熟悉的实际选择。随着项目复杂度的增加以及对更高级仪表板的需求，从 Excel 过渡到 Power BI 成为一条可行的道路。本节将探讨如何平稳过渡的策略。

将数据模型导入 Power BI

由于本书不是关于 Power BI 的，这里的主要目标只是将数据模型加载到 Power BI 中并预览它。要实现这一目标，请确保已安装免费应用程序 Power BI Desktop。你可以在Microsoft 的官方文档中找到安装说明。如果你希望更深入地探索 Power BI，请查阅 Jeremey Arnold 的书籍，《学习 Microsoft Power BI：将数据转化为见解》（O'Reilly，2022）。

为了看到将 Power Pivot 的工作转移到 Power BI 有多么简单，请练习加载 ch_07_solutions.xlsx 文件，或尝试加载你在本章节中一直在使用的工作簿。

在 Excel 中关闭这个工作簿后，打开 Power BI Desktop 并创建一个新报表。从 Power BI Desktop 的功能区，依次点击文件 → 导入 → Power Query、Power Pivot、Power View，如图 7-27 所示。

图 7-27. 从 Power Pivot 导入到 Power BI

从这里，浏览到ch_07_solutions.xlsx，并选择它。您可能会遇到警告，例如在图 7-28 中显示的内容，指示 Power BI 将尽其所能导入数据。点击开始按钮继续导入过程。

图 7-28. 导入 Excel 工作簿警告

您可以选择复制 Excel 数据或保持实时连接。链接到 Excel 工作簿可以方便地修改数据，但在 Power BI 中的性能较慢。为了简化操作，我会复制数据而不是保持连接。

现在您应该会收到一条消息，确认 Power BI 已成功导入您的工作簿，包括其查询、数据模型关系以及任何已创建的关键绩效指标（KPI）或度量。您可能会收到一条消息，指出由于导入对象的大小过大，无法复制，因此使用了实时连接。

查看 Power BI 中的数据

要验证数据模型是否已正确导入 Power BI，请点击屏幕左侧的第三个小图标，进入模型视图。在模型视图中（类似于 Power Pivot 中的图表视图），我们可以确认表之间的关系已经正确定义，如图 7-29 所示。

图 7-29. Power BI 中的模型视图

持续滚动orders表，您还将看到您在 Power Pivot 中创建的层次结构和计算列已经移植到 Power BI 中。

您可以通过点击模型视图图标上方的小型电子表格图标来查看这些计算列，从而访问表视图。此功能类似于 Power Pivot 的数据视图，使您可以在数据源之间切换。成功导入了Profit Margin和Segment Number的计算列及其公式，如图 7-30 所示。

图 7-30. Power BI 中的表视图

Power BI 中的公式编辑器显然比 Power Pivot 中的更先进。这反映了 Power BI 的更广泛能力，提供了创建高级仪表板和报告的多种机会，这在仅仅使用 Excel 中将会非常具有挑战性。

Power BI 已成为微软的现代仪表板和报告开发平台。然而，Excel 仍保留其作为快速和用户友好工具的传统，允许更灵活和探索性的数据建模和分析。最终，Power BI 和 Excel 相辅相成，作为同一团队的不同工具，各自发挥不同的作用。

您的 Power BI 报告现在可以保存。此 Power BI 文件已在您的ch_07文件夹中保存，文件名为ch_07_solutions.pbix。

结论

本章提供了一个实际的、动手操作的方法来构建基本的数据模型，并探索 Power Pivot 中的关键特性。第二部分中的后续章节将深入探讨其数据分析和报告能力。

练习

对于此练习，在书的伴侣存储库中的 exercises\ch_07_exercises 文件夹中打开名为 ch_07_exercises.xlsx 的文件。该工作簿包含三个表：batting、people 和 hof。执行以下操作：

通过 Power Query 将表加载到 Power Pivot 中，并在 Power Pivot 数据模型中建立关系。
识别数据模型中的事实表和维度表，并根据图表视图组织模型。
这些表之间的关系的基数是多少？
使用 SWITCH() 函数在 hof 表中生成一个 is_player 列。如果 category 列指示为 Player，则赋值为 Yes，否则赋值为 No。
在 people 表中的 birthCountry、birthState 和 birthCity 字段之间创建层次结构。
将数据模型结果加载到 Excel PivotTable 中。计算球员的数量。您可以通过统计 is_player 列中为 Yes 的 playerID 的数量来实现此目标。

您可以在同一文件夹中的 ch_07_exercise_solutions.xlsx 中找到解决方案。

第八章：在 Power Pivot 中创建度量和关键绩效指标

在第七章中，介绍了 Power Pivot 和数据模型的基础知识，包括关系、层次结构和计算列。有了数据模型之后，本章深入探讨了创建 DAX 度量和关键绩效指标，帮助最终用户解释数据。

若要演示，请参阅书籍附带存储库的 ch_08 文件夹中的 ch_08.xlsx。本章使用与第七章相同的零售销售数据集，并在提供的练习文件中预定义了数据模型。

创建 DAX 度量

在第七章中，试图向 orders 表中添加 利润率 列导致了不理想的结果。要在不同类别和时间段之间进行聚合和重新计算结果，需要使用 DAX 度量。在 Power Pivot 中，可以通过两种方式创建度量：隐含方式和显式方式。要通过实践学习这些方法，请从数据模型插入一个数据透视表。

创建隐含度量

要聚合数据，比如按地区查找总订单数量，通常会直接将字段拖放到数据透视表中，如图 8-1 所示。

要调整聚合以确定各地区销售单位的平均数量，请导航到数据透视表中“订单数量总和”的下拉菜单。然后，转到“值字段设置”，在“按何种方式汇总值字段”部分，从“求和”切换到“平均值”。

图 8-1. 经典的拖放数据透视表聚合

要查看数据模型如何管理这些数据透视表计算，请导航到功能区中的 Power Pivot 选项卡，选择“管理”。从主页选项卡的“查看”组中选择“图表视图”。然后，在“高级”选项卡上，启用“显示隐含度量”。这将在 orders 表底部添加两个度量，如图 8-2 所示。

早些时候通过数据透视表创建的度量称为隐含度量。Power Pivot 会自动生成并存储这些度量。它们提供了快速探索和分析数据的便利，无需开发复杂的计算。

图 8-2. 图表视图中显示的隐含度量

然而，隐含度量在数据模型中的定制性和重复使用性方面存在挑战。仅仅聚合现有字段并不能建立新的派生度量，比如每销售单位的平均销售额。创建一个独立的显式度量，结合两个字段（销售额和订单数量），变得至关重要。此外，隐含度量的隐藏性质使得它们的管理和组织变得复杂。要解决这些问题，您可以按照以下步骤删除隐含度量：点击其中一个度量，按住 Ctrl 键，然后点击另一个度量，接着右键单击并选择“删除”按钮，如图 8-3 所示。

图 8-3. 删除 Power Pivot 中的隐含度量

创建显式度量

不要通过 PivotTable 值隐式创建 DAX 度量，而是可以使用 Power Pivot 的“度量”选项明确地制定它。退出 Power Pivot 编辑器，在功能区中导航到 Power Pivot 选项卡，选择“度量”→“新度量”，如图 8-4 所示。

图 8-4. 在 Power Pivot 中创建新度量

首先要建立一个总销售额度量，聚合orders表中的sales列。这个 DAX 计算与第一章中讨论的结构化表引用类似。您还可以继续利用 Microsoft 的 IntelliSense 自动完成函数、表格和其他元素的拼写，用于构建度量。将这个度量与orders表关联，指定表名，然后将其格式化为带有 2 位小数的货币格式。

最后，选择“检查公式”按钮验证度量。成功后，会显示“公式无错误”的消息，如图 8-5 所示。

图 8-5. 创建`总销售额`显式度量

点击“确定”后，该度量将在orders表的 PivotTable 字段中可用，以fx符号为标志。将Region放置在行中，并将新创建的总销售额度量放置在数值中，如图 8-6 所示。

图 8-6. 在 PivotTable 中使用 DAX 度量

显式度量的聚合类型无法更改，这是隐含度量不适用的限制。要修改度量的计算方式，返回功能区中的 Power Pivot 选项卡，选择“度量”→“管理度量”，然后在总销售额度量上点击“编辑”。

接着，制定一个名为总利润的度量。您的度量应该与图 8-7 相似。

图 8-7. 创建`总利润`显式度量

当用作其他指标的输入时，计算指标能够发挥光彩，促进超出隐式指标范围的高级计算。例如，利润率可以通过总利润和总销售额指标确定，如图 8-8 所示。

图 8-8. dax-profit-margin-measure

在数据透视表的数值部分添加总销售额、总利润和利润率，并将地区放在行中。使用公式仔细检查利润率的计算是否正确。与第七章中使用的计算列不同，这些计算现在是准确的，如图 8-9 所示。

隐含指标可能很方便，但显式指标提供透明度、定制性和高级计算的能力。投入额外的努力是有益的，建议您将所有 Power Pivot 指标都设为显式，无论它们的简易程度如何。

图 8-9. 双重检查`利润率`指标

表 8-1 比较了隐式和显式指标。

表 8-1. 隐式与显式指标的比较

隐式指标	显式指标

根据数据字段由 Power Pivot 自动生成

用户定义的计算

快速而轻松创建，需要最少的努力

需要更多时间和技术专长来创建

适合快速数据探索

专为特定业务需求量身定制

可能无法准确捕捉所需的度量或 KPI

准确而具体

不够可定制和灵活

更加可定制和灵活

适用于简单数据探索

适用于复杂分析

显式创建的 DAX 指标允许进行广泛的复杂分析。事实上，第九章揭示了只靠 Excel 难以实现甚至不可能实现的方法。

然而，在探索这些高级主题之前，至关重要的是通过 Power Pivot 获得一些即时的胜利，帮助用户更有效地理解和利用他们的数据。数据分析的核心在于简化数据解释和决策过程。因此，本章以关键绩效指标（KPI）讨论结尾。

创建关键业绩指标（KPI）

KPI 对于跟踪业务绩效和实现目标至关重要。在 Excel Power Pivot 中，KPI 可以为您的数据分析提供宝贵的见解。本节的目标是创建一个 KPI，比较总销售与销售目标。

要做到这一点，Power Pivot 要求这两个数字都作为显式指标创建。您已经创建了一个总销售额指标；现在使用相同的逻辑创建一个总销售目标指标，如图 8-10 所示。

图 8-10. 创建“总销售额目标”度量

要开始构建 KPI，请转到功能区上的 Power Pivot，然后选择 KPIs → 新建 KPI。将 KPI 基础字段设置为“总销售额”，将目标值设置为“总销售额目标”，如图 8-11 所示。

图 8-11. 定义基础和目标 KPI 值

此设置将比较实际销售与其目标水平。

接下来，设置状态阈值以为目标值提供背景，定义性能的可接受范围。这些阈值将结果分类为“良好”、“满意”或“差”，使用户可以快速评估相对于目标的性能，比简单的命中与未命中评估更具细腻性。

设定三级阈值，以查看哪些值超出、符合和低于预期：

当销售百分比目标低于 90%时将标记为红色。
当销售百分比目标在 90%到 100%之间时将标记为黄色。
当销售百分比目标达到或超过 100%时将标记为绿色。

在 KPI 菜单中点击并拖动阈值，以符合这些规则，如图 8-12 所示。

图 8-12. 定义关键绩效指标状态阈值

调整图标样式

接下来，您将发现各种选项来定制 KPI 的外观和设计，这在特定场景中可能会有所帮助。然而，需要注意的是，在数据可视化中使用红色、绿色和黄色的颜色是不鼓励的，因为这可能会对色觉有差异的人造成混淆和误解。

遗憾的是，Power Pivot 没有提供修改其颜色方案的能力，这是工具的一个显著缺陷。这种限制可能促使用户考虑将更复杂的仪表板和报告迁移到更为复杂的 BI 平台，如 Tableau 或 Power BI。这些平台在颜色自定义和其他可视化功能方面提供了更高的灵活性，以满足数据更个性化和视觉上更吸引人的展示需求。

警告

红黄绿色调的色彩搭配通常不建议用于数据可视化，因为这可能会使色盲人士难以准确解释数据。遗憾的是，Power Pivot 不允许修改这些颜色，强调了需要考虑更为多功能的解决方案来创建全面的仪表板和报告的必要性。

将 KPI 添加到数据透视表

设置好 KPI 后，点击“确定”。接下来，从数据模型插入新的数据透视表或使用已有的数据透视表。将“区域”放入行，将“客户段”放入列。

在 PivotTable 字段列表中orders组的底部，您应该看到一个名为Total sales的交通灯图标。选择下拉菜单，并将其中的三个字段放入 PivotTable 中，如图 8-13 所示。

图 8-13. PivotTable 中的总销售 KPI

如果您的Total sales Goal未正确格式化，您可以通过 PivotTable 中的值字段设置进行操作。

KPI 结构设计为在 PivotTable 内按以下方式运行：首先显示实际销售数字，然后是基于Sales target列设定的销售目标。此显示由视觉指示器增强，能立即理解销售数字是否达到、超过或未达到目标。

如果您不喜欢阈值并希望对其进行微调，您可以随时返回到功能区上的 Power Pivot，然后选择 KPIs → Manage KPIs，单击您的 KPI，然后单击 Edit。

基于 KPI 和显式测量的 PivotTable 仅代表 Excel 和 Power Pivot 提供的广泛数据报告和可视化能力的起点。要更全面地了解如何利用您的数据模型结果构建包含附加功能（如切片器、条件格式化等）的完整仪表板，请考虑阅读 Bernard Obeng Boateng 的《Data Modeling with Microsoft Excel》（Packt, 2023）。

结论

本章探讨了使用 Power Pivot 创建健壮报告和分析的初始步骤，强调了隐式和显式 DAX 测量之间的差异。该章还介绍了 Power Pivot 中 KPI 的概念，突出了它们在创建可操作报告中的有用性，同时指出了它们的一些局限性。

第九章通过深入研究 DAX 的更高级技术和能力，创建基于 PivotTable 的分析，这些分析通常难以或不可能构建，结束了第 II 部分。

练习

对于本章的练习，请继续开发您在第八章练习中建立的数据模型。您可以选择继续使用您自己的工作簿，或者从位于书的伴随存储库中exercises\ch_08_exercises文件夹中的ch_08_exercises.xlsx文件重新开始。执行以下任务：

创建一个 PivotTable，按出生州(birthState)分组，呈现按隐式测量计算的全垒打（HR）的总数。
删除步骤 1 中创建的隐式测量，并建立一个新的显式测量，命名为hr_total，计算全垒打的总数，并以千为单位格式化为整数。将此测量添加到 PivotTable 中。
生成另一个显式测量值，命名为 hr_pct，计算出全垒打（HR）占打数（AB）的百分比。将结果格式化为百分比。可以创建一个额外的打数总计测量来辅助此计算。
基于指标 hr_pct 制定一个 KPI，目标绝对值为 1。使用以下状态阈值：
- 少于 2%：红色状态
- 在 2% 到 3% 之间：黄色状态
- 大于 3%：绿色状态
将该 KPI 应用于透视表，透视表按行显示 teamID，按列显示 yearID。

参考同一文件夹中的 ch_08_solutions.xlsx 查看解决方案。

第九章：Power Pivot 的中级 DAX

在第八章中，您探索了用于报告的基本 DAX 度量。现在，在第 II 部分的最后一章中，我们将深入探讨中级 DAX 任务，增强 Excel 中的透视表报告。

要参与演示，请打开位于书的配套存储库的 ch_09 文件夹中的 ch_09.xlsx 文件。我们将使用与前几章相同的零售销售数据集。

此 Excel 工作簿包括一个链接到数据模型的透视表，并且具有名为 Total sales 的预定义度量。该度量计算来自 orders 表的 Sales 列的总和，并将在随后的各种演示中使用。

CALCULATE() 和过滤上下文的重要性

在传统的透视表中，所有值都遵循主要过滤器。例如，在图 9-1 中，如果您按 “Express Air” 过滤 Ship Mode，则不能同时看到总销售。您要么得到总销售，要么只有 Express Air 销售，但两者都不会同时出现。

图 9-1. 现在在过滤上下文中评估 `Total sales`

用简洁的术语来说，透视表中的每个值都遵循其“过滤上下文”。然而，CALCULATE() 函数解放了度量不受此约束，使其能够在修改后的过滤上下文中运行。这彻底改变了透视表的功能。

尽管 CALCULATE() 函数非常强大，但其语法相当简单，如表 9-1 所示。

表 9-1. CALCULATE() 的参数解释

参数	数据类型	描述
`expression`	任何有效的 DAX 表达式	要评估或计算的表达式。这可以是一个度量、一个列或另一个函数。
`[filter1], [filter2], [...]`	列、表或布尔表达式	可选。要应用于表达式的过滤器或过滤器。这可以是单个列、一个表或一个布尔表达式。

CALCULATE() 与一个条件

首先制定一个名为 Total express air sales 的度量。该度量应将 Total sales 过滤为 Ship Mode 设置为 “Express Air” 的订单，如图 9-2 所示。

图 9-2. 带有一个条件的 `CALCULATE()` 函数

一旦您制定了这个度量，将其添加到透视表中，放在 Total sales 旁边，并从透视表中移除 Ship Mode。这样，您就可以在透视表中同时看到总体销售和 Express Air 销售，如图 9-3 所示。

图 9-3. `Total express air sales` 作为独立的过滤上下文

您已经改变了透视表如何评估单个数据点。这是透视表功能的重大进步。

CALCULATE() 与多个条件

使用CALCULATE()还可以通过各种条件修改过滤器上下文。本节将探讨如何在我们的函数中整合和/or 条件。

AND 条件

鉴于高优先级订单最容易受到航空运输中断的影响，审查同时满足订单优先级为高和发货方式为 Express Air 的销售数据可能会很有帮助。

将第二个AND条件添加到CALCULATE()度量中只需添加一个额外的过滤参数，如图 9-4 所示。

图 9-4. 带有`AND`条件的`CALCULATE()`函数

OR 条件

在条件逻辑中，始终要注意结果的敏感性。即使是微小的条件更改也可能产生截然不同的结果。

例如，通过过滤订单优先级为高或发货方式为 Express Air 的订单来验证销售金额。在CALCULATE()中，使用两个|符号结合条件，如图 9-5 所示。

图 9-5. 带有`OR`条件的`CALCULATE()`函数

使用 ALL()的 CALCULATE()

CALCULATE()可以添加到过滤器上下文中，但与ALL()配对时，它明确地清除所有过滤器上下文以获取一个值。要理解这种差异，请考虑图 9-6 中的透视表（#calculate-results-affected-filter）。总销售额度量和过滤器上下文修改后的总 Express Air 销售额随着总体产品类别过滤器的变化而波动。

图 9-6. `CALCULATE()`的结果受到透视表筛选器的影响

要计算所有基准值，无论上下文过滤条件如何，都可以将ALL()函数与CALCULATE()配对使用。例如，我将创建一个名为所有总销售额的度量，如图 9-7 所示。

图 9-7. 使用`CALCULATE()`与`ALL()`清除所有过滤上下文

通过将ALL(orders)应用为过滤条件，表中的每条记录都会被考虑在内进行计算，覆盖透视表中的任何其他现有过滤器上下文。这种差异可以在图 9-8 中看到（#total-vs-total-all-sales-dax）。

这对比较特定的销售数据组合与整体总数是很有用的，无论应用的过滤器如何。

图 9-8. 透视表中的`所有总销售额`

CALCULATE()函数类似于经典 Excel 中的查找函数和透视表。它代表了向高级功能的跃进。要深入了解过滤器上下文和CALCULATE()函数，请参考《DAX 定义指南》第二版，作者是 Alberto Ferrari 和 Marco Russo（Microsoft Press，2019 年）。

时间智能函数

在量化推理的核心是一个问题：与什么比较？

Edward Tufte

在商业世界中，分析趋势至关重要。分析师会根据历史数据评估当前表现，并评估每月和每年的指标。传统的 Excel 方法对于此目的可能显得笨重，而 Power Pivot 提供了简化的方法。

Power Pivot 引入了时间智能功能，提供了能够简化基于时间数据分析的函数，例如年度总计和月度增长。这消除了复杂的公式，简化了在 Excel 中进行趋势分析的过程。

添加日历表

要有效地在 Power Pivot 中使用时间智能功能，请首先添加一个日历表。这提供了一个一致和全面的日期时间结构，增强了数据分析的准确性，并使得更复杂的基于时间的计算和比较成为可能。在数据模型中，转到 Power Pivot 选项卡，选择管理，转到设计选项卡，然后选择日期表 → 新建，如图 9-9 所示。

图 9-9. 将日期表添加到数据模型

现在您应该在数据模型中看到一个日历表。在此表的日期列和订单表中的订单日期列之间建立关系。您的数据模型应该类似于图 9-10。

图 9-10. 带有日历表的零售销售数据模型

现在，您可以在数据模型中利用来自日历表的各种日期度量，所有这些都与订单表中的订单日期相关联。

要确保日历表正常运行，请在工作簿中插入一个新的透视表。将日历表中的日期层次结构拖到行中，并将订单中的总销售额拖到值中，如图 9-11 所示。

图 9-11. 在透视表中使用日历表

创建基本的时间智能度量

DAX 提供了丰富的时间智能函数，允许您检索以往的周期、至今的周期及更多功能。例如，要计算年度销售量，可以使用TOTALYTD()公式，如图 9-12 所示。

图 9-12. 在 DAX 中创建年度销售量措施

要验证这个度量的准确性，请将其添加到数据透视表中，并将日期层次结构放置在行中。通过点击+号展开 2020 年的数据。您应该观察到每个月度量的逐月增加，如图 9-13 所示。

图 9-13. 在透视表中显示年度销售量

接下来，为了确定上一年同期的销售额，结合熟悉的 CALCULATE() 函数和 SAMEPERIODLASTYEAR() 函数，如图 9-14 所示。

图 9-14. 创建一个 `去年销售` 测量

为了确保准确性，请预览透视表中的数据以及 Total sales。您应该看到 2021 年的去年销售额和 2020 年的总销售额，如图 9-15 所示。

图 9-15. 比较今年和去年的销售

最后，建立一个 去年 YTD 销售 测量，以对比今年的年度销售和前一年的销售。

为了实现这一点，您可以将 CALCULATE() 与 DATESYTD() 和 DATEADD() 函数结合使用。此设置将检索所有年度截至日期，并使用日历表向后调整一年。结果如图 9-16 所示。

图 9-16. 创建一个 `去年 YTD 销售` 测量

您现在可以比较当前年度和去年年度的年度趋势，如图 9-17 所示。

图 9-17. 比较今年和去年的年度销售

结论

DAX 和 Excel 总是提供新的学习内容，您可以以无数种方式组合函数。本章可能即将结束，但使用 Power Pivot 在 Excel 中还有更多发现。

到目前为止，本书已探讨了数据清洗和建模方面的 Power Query 和 Power Pivot。但在 Excel 中还有更多的数据分析。第三部分触及其他功能，帮助使您的 Excel 项目更加动态和洞察力十足。

练习

要练习构建中级的 DAX 测量，使用书中伴随仓库中 exercises\ch_09_exercises 文件夹中的 ch_09_exercises.csv 中找到的自行车店销售数据集。尽管本书侧重于使用 Power Pivot 在同一工作簿中的多个表格之间建立关系，但单个 .csv 文件仍然可以使用。

通过 Power Query 将数据加载到 Power Pivot，并创建以下测量值：

accessories_rev: 当 product_category 设置为 Accessories 时返回总收入。
accessories_rev_aus: 当 product_category 设置为 Accessories 且 country 设置为 Australia 时返回总收入。
aov_all: 计算整个数据集中的总收入除以总订单数量，不考虑应用的筛选器。
profit_margin_ytd: 返回年度利润率。
profit_margin_ly_ytd: 返回前一年度的年度利润率。

请随意创建辅助度量，以帮助构建所需的度量指标。例如，在寻找年度销售总额时，首先创建一个总销售度量是很有帮助的。

确保通过在 PivotTable 中测试来确保您的度量指标按预期运行。例如，如果您正在构建一个与筛选上下文无关的度量指标，请应用筛选器以观察其行为。在开发年度累计度量时，通过日期对度量进行汇总以验证其适当的响应。此外，即使我们只使用单个表，也有利于包含一个日历表来进行与日期相关的操作。

在同一文件夹中查看 ch_09_solutions.xlsx 查看解决方案。

第三部分：Excel 数据分析工具包

第十章：介绍动态数组函数

到目前为止，本书已经介绍了使用 DAX 构建 Power Pivot 的度量标准，并在 Power Query 中较少地涉及了 M 代码。然而，它并未审视长期以来一直是 Excel 基础的传统工作簿公式和函数。即使在其他如 Power Pivot 和 Power Query 等引人注目的发展活动中，此类程序区域似乎被忽视，但它经历了显著改进，变得更加强大和有能力。

本章介绍了动态数组函数，揭示了它们的功能。您将学习如何使用 Excel 公式栏中熟悉的环境来对数据集进行排序、过滤和连接等任务。

动态数组函数解析

动态数组函数具有令人印象深刻的功能，诱人地让人立即开始尝试。然而，了解这些函数的特殊之处以及它们与传统 Excel 方法的区别至关重要。接下来的部分将探讨从数组到数组引用再到动态数组函数的路径。

在 Excel 中，什么是数组？

要跟随此演示，请在书的资源中的ch_10文件夹中打开ch_10.xlsx，转到array-references工作表。

首先和最重要的是，在 Excel 中，数组指的是一组值的集合。例如，一个基本的数组可以由放置在单元格A2:C2中的数字 3、4 和 7 组成，如图 10-1 所示。

图 10-1。一个基本的 Excel 数组

数组引用

理解在 Excel 中构成数组的内容后，本节将探讨构建数组引用的各种方法。

静态数组引用

要创建传统的 Excel 数组引用，在单元格E2中输入=A2:C2，然后按 Ctrl+Shift+Enter，表示您引用的是一组值而不是单个值，如图 10-2 所示。

图 10-2。一个基本的 Excel 数组引用

您将看到结果公式被大括号{}括起来，但不会在结果中显示所有三个值。这是因为在 Excel 中，每个单元格设计用来存放单个数据点，而不是您可能试图在此处实现的三个值。要将数组数据分布到多个单元格中，请选择范围E2:G2并输入相同的引用，如图 10-3 所示。

图 10-3。一个改进的 Excel 数组引用

Excel 处理数组的传统方法存在一些限制。使用 Ctrl+Shift+Enter 编写和管理引用的过程可能很繁琐，并且缺乏自动调整功能。

考虑这样一个场景，即在A2:C2之间插入或删除单元格时，数组引用无法自动调整大小。因此，这些数组引用可以描述为静态，因为它们不会根据电子表格结构或单元格数的变化动态适应。

动态数组引用

动态数组在 2018 年的 Excel 中引入，以克服传统静态数组的限制。现在，要引用A2:C2，只需输入=A2:C2并按 Enter，例如在图 10-4 的单元格E5中。

图 10-4. 动态 Excel 数组引用

使用此引用，Excel 智能识别数组中的单元格数量。这意味着，如果在A2和C2之间插入或删除单元格，动态数组引用将自动调整大小以适应这些变化。这种动态调整节省了时间和精力，消除了在数据布局更改时手动更新或修改引用的需要。

数组公式

对比了经典和现代 Excel 中数组引用的行为后，接下来的部分深入探讨了使用这些引用的函数的影响。

静态数组公式

考虑以下示例，它使用静态数组公式列出了交易数据集中售出的唯一产品，正如在图 10-5 中所见。您可以在ch_10.xlsx的array-functions工作表中跟随此示例。

图 10-5. 静态数组公式

不要过分担心公式的内部工作原理；我们将很快探讨一个更合理的替代方案。现在，观察一下静态数组在确定应返回的输出件数时可能存在的挑战。

类似于数组引用，这种方法缺乏对唯一值正确数量的自动调整，使其显得复杂且不直观。例如，如果在dm_sales表中添加更多交易，比如橡皮筋，在数组函数中不会反映出任何额外的唯一值，正如在图 10-6 中所见。

图 10-6. 橡皮筋（第 16 行）未列为唯一产品

要看到额外的值，您需要在列H中扩展您的数组公式再向下一行。

动态数组函数

另一方面，动态数组函数在这种情况下表现非常出色。甚至有一个专门设计来处理此任务的UNIQUE()函数，正如在图 10-7 中所见。

注意

如果在使用UNIQUE()函数后出现#SPILL错误，请确保其下方存在空单元格。当函数的结果溢出到非空相邻单元格时，会出现此错误。

动态数组函数通过即时更新输出单元格以响应输入变化，显著提升了传统数组公式的功能。这种动态行为消除了手动重新计算或刷新公式的需要，提供了无缝和高效的工作流程。

图 10-7. 使用 `UNIQUE()` 函数查找唯一值

动态数组函数概述

考虑到动态数组函数的功能，现在是时候探索一些例子了。第一个例子将深入讨论前文提到的 UNIQUE() 函数。

使用 `UNIQUE()` 查找不同和唯一值

在前一个例子中，使用 UNIQUE() 动态数组函数生成了唯一产品名称列表。要进一步探索此函数和数据集，请继续使用 ch_10.xlsx 工作簿中的 dm_sales 表。

UNIQUE() 函数有三个参数，其中两个是可选的。要理解它们的工作原理，请参考表 10-1。

表 10-1. UNIQUE() 参数

Parameter	Description
`range`	必需的参数，指定要从中提取唯一值的数据范围或数组。
[`by_col`]	可选参数，确定是否应按列或行提取唯一值。默认情况下，按行提取唯一值。如果将此参数设置为 `TRUE`，则将按列提取唯一值。如果将此参数设置为 TRUE，则将按列提取唯一值。
[`exactly_once`]	可选参数，指定是否只考虑仅出现一次的值作为唯一值。默认情况下，无论其频率如何，都会提取所有唯一值。如果将此参数设置为 `TRUE`，则仅提取仅出现一次的值。

查找唯一与不同值的区别

在数据库术语中，唯一值 指的是在给定范围内仅出现一次的值。这使得 UNIQUE() 函数的名称有些误导性。该函数实际上识别的是不同的 值，即出现一次或多次的值，而不是严格意义上的唯一值。然而，通过将函数的第三个参数设置为 TRUE，可以获得真正的唯一值，如图 10-8 所示。

图 10-8. 使用 `UNIQUE()` 查找真正的唯一值

使用溢出运算符

在 Excel 中，常见的做法是在现有计算的基础上进行额外的计算，例如聚合计算列的结果。由井号符号 (#) 表示的溢出运算符 简化了动态数组函数的聚合过程。像动态数组本身一样，它会自动扩展输出范围以适应数据，消除了手动输入数组公式和调整范围的需要。这一特性在构建 Excel 中用于聚合数据的公式时提高了效率和简洁性。

可以使用 COUNTA() 函数来确定在图 10-8 中显示的公式生成的唯一值的数量。选择范围 D2:D7 时，Excel 自动引用以 # 运算符指示的溢出范围，如图 10-9 所示。

图 10-9. 使用溢出运算符汇总动态数组

尽管本章未详细介绍，溢出运算符对于构建依赖下拉列表、动态图表和其他各种功能提供了显著的好处。

使用 FILTER() 过滤记录

Excel 的传统数据过滤下拉菜单很直观，但存在一些限制。例如，一旦应用了这些过滤，就不再能查看原始数据的完整内容了。最好是创建数据的副本，然后对该副本应用过滤，类似 Power Query 处理数据清洗的方式。此外，跨多列定义复杂的过滤逻辑规则可能既繁琐又重复。

为了解决这些限制，Excel 引入了 FILTER() 动态数组函数。FILTER() 有三个参数，详细说明在表 10-2 中。

表 10-2. FILTER() 参数

参数	描述
`array`	必需的参数，指定要过滤的数据范围或数组。
`include`	必需的参数，指定过滤条件或条件。它定义了应包含在过滤结果中的值。可以是逻辑表达式、要匹配的值，或者对数组中每个元素求值为 `TRUE` 或 `FALSE` 的公式。
`[if_empty]`	一个可选参数，指定如果过滤结果为空时返回的值。默认情况下，如果没有值符合过滤条件，该函数将返回一个包含 `#CALC` 错误值的数组。

要实现所需的结果，需要过滤 dm_sales 表，以便仅返回 product 设置为 Sticky Notes 的记录，如图 10-10 所示。

图 10-10. 一个基本的 `FILTER()` 函数

注意

FILTER() 函数默认情况下不区分大小写。在上面的示例中，“Sticky Notes” 和 “sticky notes” 都将产生相同的结果。要执行区分大小写的过滤，请将 FILTER() 函数与 EXACT() 结合使用；例如：=FILTER(dm_sales, EXACT(dm_sales[product], "Sticky Notes"))。

添加一个标题列

FILTER() 函数已经非常有用，但它缺少一个至关重要的功能—它仅返回匹配的行，而不包括数据的标题列。要包含这些标题，需要使用动态表头引用。关于结构化引用的快速回顾，请参阅第一章。如图 10-11 所示，在你的过滤输出上方包含此引用以获取动态标头标签。

图 10-11. 带有标头标签的 `FILTER()` 结果

警告

将FILTER()函数或其他动态数组函数应用于 Excel 表时，结果不包括表头。

根据多个条件过滤

FILTER()函数通过使用公式来过滤数据与传统的下拉菜单过滤方法区别开来。这种能力不仅能解锁强大的机会，同时保持了一种直观和简单的方法来建立和理解条件。

要在FILTER()函数中结合多个条件，使用*符号表示AND语句，使用+符号表示OR语句。

并且条件

要搜索 product 为“复印纸” 且数量大于 5 的记录，可以在不同的括号组合内将条件相乘：

=FILTER(dm_sales, (dm_sales[product] = "Copy Paper") *
(dm_sales[quantity] > 5))

或条件

如果你宁愿查找符合任一条件的记录，请用+符号替换*符号来创建一个OR语句：

=FILTER(dm_sales, (dm_sales[product] = "Copy Paper") +
(dm_sales[quantity] > 5))

嵌套的 `AND`/`OR` 条件

要创建带有嵌套 AND 或 OR 语句的过滤函数，将语句用括号分组。这个过滤函数包括了 sales_amt 至少为 $100 或 quantity 至少为 10 且 product 为“信封”的记录：

=FILTER(dm_sales,
(dm_sales[sales_amt] >= 100) +
((dm_sales[quantity] >= 10) * (dm_sales[product] = "Envelopes")))

按照这些准则，你可以继续添加和调整多个条件以及 FILTER() 函数。

使用 SORTBY() 对记录进行排序

SORTBY() 是一个动态数组函数，能够根据多个条件同时排序记录，其语法类似于 SUMIFS()。表格 10-3 详细介绍了参数。

表格 10-3. SORTBY() 参数

参数	描述
`array`	必需参数，指定要排序的数组或范围。
`by_array1`	必需参数，指定要排序的数组或范围。
`[sort_order1]`	可选参数，指定如何对结果进行排序。1 表示升序，-1 表示降序。默认为升序。
`[by_array2]`	可选参数，指定要排序的数组或范围。
`[sort_order2]`	可选参数，指定如何对 `by_array2` 的结果进行排序。1 表示升序，-1 表示降序。默认为升序。

举个例子，可以根据 sales_amt 的降序对数据集使用 SORTBY() 函数进行排序，如图 10-12 所示。

图 10-12. 使用 `SORTBY()` 对 Excel 表格进行排序

根据多个条件排序

SORTBY() 允许基于多个条件对数据进行排序，可以灵活地指定每个条件的升序或降序。例如，数据可以按emp_last降序排列，并按product升序排列：

=SORTBY(dm_sales, dm_sales[emp_last], -1, dm_sales[product], 1)

您可以扩展此模式以按所需顺序根据更多条件对数据集进行排序。

按另一列排序而不打印它

SORTBY() 甚至可以按照另一个范围排序，即使在结果中没有包含原始排序范围。

例如，假设您希望按销售额降序获取交易 ID 列表。不要使用整个 dm_sales 表作为第一个参数，只选择 trans_id 列。接下来的步骤应该很熟悉。结果将包括单列，如图 10-13 所示。

图 10-13. 在一列中显示 `SORTBY()` 结果

使用 `XLOOKUP()` 创建现代查找功能

到目前为止，展示动态数组函数的示例仅使用了单个表格。然而，通常情况下，数据来自多个表格，需要进行合并。尽管 Power Query 和 Power Pivot 提供了各自的技术来从不同来源合并数据，但 Excel 公式的即时、动态和交互特性仍然在诸如基于用户输入构建模型、执行实时数据分析等任务中具有优势。

XLOOKUP() 函数通过利用动态数组的能力，提供了传统 VLOOKUP() 函数的多功能替代方案。

要进一步探索此功能，请参考 ch_10.xlsx 中的 xlookup 工作表。该工作表包含与办公用品销售相关联的三个不同的表格。

`XLOOKUP()` 与 `VLOOKUP()` 比较

XLOOKUP() 对于习惯于使用 VLOOKUP() 从一个表中检索数据并基于共享查找值将其传输到另一个表的用户来说，提供了一种熟悉的体验。然而，它引入了一系列更为灵活和复杂的附加搜索方法。有关 VLOOKUP() 和 XLOOKUP() 之间主要差异的概述，请参阅表 10-4。

表 10-4. VLOOKUP() 对比 XLOOKUP()

特性	`VLOOKUP()`	`XLOOKUP()`
搜索方向	只能垂直搜索	可以垂直和水平同时搜索
返回方向	只能返回查找值右侧的值	可以返回查找值左右两侧的列中的值
错误处理	如果未找到值则返回 #N/A	可以指定未匹配项的默认值并处理错误

XLOOKUP() 总共有六个参数，如表 10-5 所示。

表 10-5. XLOOKUP() 参数

参数	描述
`lookup_value`	必填参数，指定在`lookup_array`中搜索的值。
`lookup_array`	必填参数，指定要在其中搜索`lookup_value`的范围或数组。
`return_array`	必填参数，指定要从中检索数据的范围或数组。
[`if_not_found`]	可选参数，指定如果未找到`lookup_value`则返回的值。
[`match_mode`]	可选参数，指定匹配`lookup_value`的方法。
[`search_mode`]	可选参数，指定查找`lookup_value`的搜索行为。

此演示突出了XLOOKUP()的前四个参数。要了解更详细的概述，请查看艾伦·默里（Alan Murray）的《高级 Excel 公式：释放 Excel 公式的光辉》第十二章（Apress，2022）。

一个基本的 XLOOKUP()

从一个简单的例子开始：transactions表包含一个需要与其对应的product_name匹配的product_id。在这里，product_id作为查找数组，product_name作为返回数组，如图 10-14 所示。

图 10-14. 基本的`XLOOKUP()`

XLOOKUP()与错误处理

查找编号为 99 的product_id会生成一个错误。使用#N/A作为缺失匹配的结果是有问题的。它可能会引入计算错误，并对用户造成困惑，因为他们可能不明白为什么会返回#N/A。

要定制XLOOKUP()语句中的错误消息，请指定第四个可选参数。在这种特定情况下，您发现分配给编号为 99 的产品应标记为“其他”。结果在图 10-15 中展示。

图 10-15. `XLOOKUP()`与错误处理

在表中查找产品名称后，现在是时候为分支名称执行相同操作了。

XLOOKUP()和左侧查找

对VLOOKUP()的普遍批评是它无法在查找数组的左侧进行搜索，除非使用辅助函数。相反，XLOOKUP()可以搜索 Excel 范围中的任何值，包括查找值左侧的表列。此类示例在图 10-16 中展示。

图 10-16. `XLOOKUP()`与左侧查找

多亏了它在垂直和水平两个方向上搜索的多功能性，以及从匹配的查找值两侧列中检索值，并在其公式内处理错误，XLOOKUP()已成为 Excel 中数据检索的首选公式。

其他动态数组函数

在这里展示的动态数组函数是 Excel 最初的提供功能之一，随后的增加扩展了其能力范围。

例如，RANDARRAY()函数生成一组随机数的数组，您可以指定行数和列数。这有助于生成填充有随机值的动态数组，非常适合模拟。类似地，SEQUENCE()函数在数组中创建一系列数字，使用指定的起始数、增量和数组大小。这对于生成线性间隔值或模拟和动态模型中的时间步骤特别有益。

许多其他动态数组函数旨在文本操作，包括用于垂直数组合并的VSTACK()和使用指定分隔符拆分文本的TEXTSPLIT()。要查看动态数组函数的全面列表并访问教程，请访问 Exceljet.com 上关于此主题的文章。

动态数组和现代 Excel

鉴于像 Power Query 和 Power Pivot 这样的工具的存在，动态数组函数在 Excel 中可能看起来是倒退的一步。为什么要自愿回到依赖精细公式驱动的工作簿时代，而不使用这些先进功能呢？这种态度忽视了动态数组在现代 Excel 分析堆栈中带来的价值。以下是它们成为重要组成部分的原因：

简洁性

动态数组函数通过在单个公式内执行计算来简化数据操作和分析，增强了理解和可维护性。这与在 Power Query 或 Power Pivot 中构建和管理数据清洗任务所涉及的复杂多步骤过程形成鲜明对比。

熟悉度

动态数组函数通过与熟悉的 Excel 环境集成，与本书中讨论的许多其他工具区别开来。与需要安装或单独编辑器的插件不同，动态数组公式在 Excel 中自带并且易于访问，大大简化了典型用户的采纳过程。

实时更新

动态数组函数提供了当基础数据发生变化时自动更新结果的优势。这消除了手动公式重新计算或连接刷新的需要，并实现了实时分析和洞察。在数据不断变化的动态场景中（如实时仪表板或财务模型），这种功能尤其有益。

结论

本章介绍了动态数组函数，使传统且有时笨拙的 Excel 引用和公式焕发新生。这些功能现在与 Excel 分析工具包中的 Power Query 和 Power Pivot 并驾齐驱。

虽然动态数组函数提供简单和低开销的选择，但是第三部分 Part III 中的后续章节深入探讨了更高级的工具。这些工具需要额外的设置，但提供了比单纯使用公式更为复杂的分析洞察。在这些章节中，您将学习如何通过 Python 将人工智能、机器学习技术和高级自动化功能整合到您的 Excel 工作流程中。

练习

要练习动态数组函数，请打开书的companion repository中的exercises\ch_10_exercises文件夹中的ch_10_exercises.xlsx文件。此工作簿包括两个数据集：vehicles和common。完成以下练习：

找出vehicles数据集中make列中的不同和真正唯一的值。每个有多少个？
仅显示城市里程大于 30 的车辆。
仅显示城市里程大于 30 的车辆，或者汽缸少于 6 且燃油为 Regular 的车辆。
根据高速公路里程将vehicles数据集按降序排序。
根据common数据集中的years列，按降序排序model列。
将common数据集中的years列添加到vehicles数据集中。如果找不到匹配项，则返回Not reported。

解决方案可以在同一文件夹中的ch_10_solutions.xlsx中找到。

第十一章：增强型分析和 Excel 的未来

随着数据分析领域的扩展变得越来越广泛和复杂，Excel 将会扮演什么角色？在 AI 驱动的数据生态系统中，它会变得过时吗？本章探讨了增强型分析的出现以及 Excel 在这一转变中的角色，以及一些当前的使用案例。

在我们深入探讨预测分析、人工智能以及正在商业领域发生的变革，包括我们的电子表格内部之前，我们需要意识到这个领域的动态性。新产品不断涌现。即使是像 ChatGPT 和 Microsoft Copilot 这样的老牌工具，也经常会有重大更新和变化。本章旨在专注于理解 Excel 的基础和更稳定的方面。我的目的不是提供最新进展的详尽概述，而是提供一瞥 Excel 中增强型分析的内容，并为您提供在这一领域中导航所需的技能，无论功能和工具如何演变。

数据和分析的日益复杂化

2017 年，国际数据公司（IDC），一家市场情报公司，预测从 2016 年到 2025 年，全球数据量将增加 10 倍，达到 163 个 Zettabytes 或一万亿吉字节。

随着数据总量的增加，数据的种类也在扩展。根据 AI 服务提供商 Taiger 的数据，到 2020 年，80%的数字数据是非结构化的——随着生成自然语言处理产品如 ChatGPT 的出现，这个比例可能已经增加。非结构化数据是不遵循特定格式或结构的信息，在传统数据库或 Excel 等电子表格中难以组织和分析。非结构化数据的例子包括文本、图像、视频和社交媒体帖子，所有这些都需要更复杂的处理技术来提取有用信息。

此外，实时数据的重要性也显著提升，IDC 估计到 2025 年，流式数据将占所有数据的 30%。

由研究咨询公司 Gartner 描述的数据爆炸，其特征是研究咨询公司 Gartner提到的体积、速度和多样性，这迫使我们采用先进的分析方法。数据科学帮助组织利用各种计算和统计方法在数据中发现关系和洞见，而机器学习和人工智能使计算机能够学习和模拟人类智能。这些技术使企业能够自动化决策过程，实时识别趋势，并创建个性化体验。

这场革命已经到来：94%的商业领袖在德勤的一项调查中回应说，AI 在未来五年对成功至关重要，并且劳工统计局预计未来十年数据科学家的就业人数将增长 36%，从 2021 年的 113,000 人增至。

Excel 与自助 BI 的传承

自助商业智能（BI），借助 Excel 等工具的支持，已经在企业决策中产生了革命性的影响。它使个人用户能够独立访问和分析数据，无需依赖信息技术（IT）人员的帮助。然而，自助 BI 的范围和复杂性有限。数据必须以与 Excel 兼容的方式结构化，从而限制分析仅限于描述性和诊断性分析。这意味着 Excel 无法处理需要预测性或规范性分析所需的高级算法和机器学习模型。

为了做出更多战略决策，企业需要将自助 BI 与更先进的分析工具和技术如数据挖掘、机器学习和 AI 相结合。

Excel 用于增强分析

增强分析是一种利用 AI 和机器学习技术增强数据分析过程的方法。它通过自动筛选大数据集，识别趋势、模式和异常，无需人工干预即可生成见解。这种方法显著提高了数据分析和自助 BI 的效率和准确性，使企业和个人能够基于数据见解做出明智决策。

在本章的其余部分，您将深入了解 Excel 增强分析的一些现有用例。首先，您将学习如何利用“分析数据”获取 AI 驱动的见解。接下来，您将使用 XLMiner 构建基本的预测模型。最后，您将使用光学字符识别和 Azure 机器学习进行情感分析。这些示例旨在拓展您对 Excel 在增强分析领域能力的认知，并巩固其在未来充满希望的前景。

利用“分析数据”进行 AI 增强见解

Excel 中的“分析数据”功能是一种利用 AI 更有效地提取有意义见解的增强分析产品。尽管如此，AI 并不能完全替代真正的专业知识。要充分利用通过“分析数据”生成的 AI 见解的 Excel 潜力，必须使用适当结构化的数据。

此演示的起始文件可以在书籍资源的ch_11文件夹中找到，命名为ch_11.xlsx。

分析数据可以在 Excel 中直接使用，无需下载。只需将光标放置在工作簿的第一个工作表中的wholesale_customers表上，然后导航到主页 → 分析数据即可开始使用。您将立即获得多种迷人的 AI 生成洞察力，如图 11-1 所示。选择任何洞察力即可直接将其合并到您的工作簿中。

图 11-1. 插入分析数据洞察

注意

由于人工智能生成的洞察力具有概率性质，例如分析数据生成的结果可能与本书所示不同。这凸显了在使用人工智能时对领域和数据具有扎实理解的重要性，因为您需要解释和导航复杂和动态的结果。

分析数据的强大之处在于其自然语言查询。例如，假设您正在与同事开会，需要快速获取杂货部门的总销售额。您可以直接向分析数据提出问题，立即获取所需信息，如图 11-2 所示。

图 11-2. 分析数据中的自然语言查询

尽管查询此数据集无疑令人印象深刻，但它确实具有某些限制，主要与数据的布局相关。例如，如果尝试向分析数据询问按地区的总销售额，您将收到所有地区号码的总和，如图 11-3 所示。

图 11-3. 分析数据遇到不正确构建的数据

分析数据不确定该怎么做，因为数据以不正确的格式呈现。不是将所有销售数字汇总到一个列中，而是分布在多个列中。因此，分析数据无法确定哪些列包含需要汇总的相关销售数字。这种格式错误的主要问题如图 11-4 所示。

图 11-4. 如何整理此数据集以获得更好的洞察力

将数据存储在不干净或“不整洁”的格式中是分析中的一个重要障碍。您可能在自己的工作中遇到过这个问题，但没有确切地找出问题所在。对脏数据的概念理解有助于您及早识别项目中的问题，从而节省后续大量时间。要深入了解整洁数据的理论并学习如何有效处理它，请参阅第一章。

要使 AI 充分释放其发现见解的潜力，数据必须以机器可读的整洁格式存在，其中每个变量都位于单独的列中。为了解决这个问题，我们将使用 Power Query 将 Fresh 到 Delicassen 的列进行反转[¹] 并将它们重命名为 Department 和 Sales。确保将查询结果加载到 Excel 表格中。如需了解如何在 Power Query 中进行反转和加载数据集，请参考第四章。

将数据整理成整洁格式后，查询按区域的总销售额就像轻而易举，如图 11-5 所示。

图 11-5. 在“分析数据”中找到按区域的总销售额

利用“分析数据”的强大功能，您还能从中获得哪些其他见解呢？要获取本章节以及其他演示的解决方案，请从本书伴随代码库的同一文件夹下载 ch_11_solutions.xlsx 文件。

分析数据是一款功能强大的增强分析工具，利用 AI 高效地提取有价值的见解。然而，对于获得最佳结果，数据的结构必须合理。通过理解整洁数据的概念并解决格式问题，用户可以充分利用 AI 发现有意义的见解的潜力。

使用 XLMiner 构建统计模型

XLMiner 插件为 Excel 增强了分析功能，提供了关键的数据分析和建模工具。它允许用户在 Excel 中访问高级分析功能，改进了增强分析的体验。演示的起始数据集位于 ch_11.xlsx 的 housing 工作表中。

要开始，请前往功能区，然后选择插入文件 → 插件 → 获取插件。在 Office 插件对话框中搜索 XLMiner 并点击添加，如图 11-6 所示。

图 11-6. 获取 XLMiner 插件

同意条款和条件，点击确定，你应该会在工作表的右侧看到 XLMiner 插件。正如你所见，XLMiner 提供了大量的统计工具和技术。让我们专注于“所有模型之母”，即线性回归。

我们将以 price 作为因变量，lotsize、airco 和 prefarea 作为自变量。前往 XLMiner 插件的线性回归部分，填写如图 11-7 所示的内容，然后点击确定。

图 11-7. 在 XLMiner 中设置线性回归

在 XLMiner 中使用拖放功能命名输入范围可能有些挑战，通常需要手动输入单元格位置。

在开始构建模型和进行预测之前，进行数据集的彻底检查以确保其符合所选择模型的假设是至关重要的。虽然 Python 和 R 提供了更广泛的分析和测试工具，但在 Excel 中与数据进行实际互动也可能具有优势。XLMiner 充当了一个会合点，将 Excel 的简单数据操作能力与通常专门用于专业数据科学工具的高级分析严谨的基本方面融为一体。

在运行回归后，您应该在图 11-8 中看到来自 XLMiner 的输出。

图 11-8. XLMiner 中线性回归的结果

这里有典型的回归诊断，如系数的 p 值，R 平方等。如果你想了解更多关于解释这些内容的信息，请查看我的书籍深入分析：从 Excel 到 Python 和 R（O’Reilly，2021 年）。

XLMiner 通过提供一个可访问的平台进行统计建模，增强了 Excel 的数据分析能力，吸引了各种专业水平的用户。尽管它具有用户友好的界面并与 Excel 无缝集成，但 XLMiner 并不符合全面增强分析工具的标准。这主要是因为它无法支持实时模型部署，缺乏模型随时间调整的持续学习以及对神经网络等高级建模技术的不足支持。

此外，XLMiner 的有限 AI 集成限制了其全面自动化数据分析过程的能力。为了解决更复杂的分析任务，用户可能需要探索 R 或 Python 生态系统中提供的更高级工具。

从图像中读取数据

分析员可能会面临只能以打印输出或其他模拟格式获得数据的情况。为了避免缓慢且容易出错的手动数据输入过程，Excel 提供了一项功能，允许直接将图像中的文本转换为工作簿。

将扫描的纸质文件通过光学字符识别（OCR）转换为可编辑的计算机文本文件并非新概念。自 1970 年以来，OCR 技术已经存在并经历了重大进展。今天，它在包括 Excel 在内的各种程序中广泛可用。

对于这个演示，我们有作为打印副本存在的客户评论。我们的目标是将它们导入 Excel 进行情感分析。您可以在书籍资源的ch_11文件夹中找到名为scanned_reviews.png的文件。

要开始，请打开一个新的 Excel 工作簿，并选择“数据”→“获取和转换数据”→“从图片”→“从文件”选项卡中的“图片”。然后，导航到并选择scanned_reviews.png练习文件。导入文件后，您应该在工作簿右侧看到“来自图片的数据”菜单，如图 11-9 所示。

图 11-9. 来自图片警告的数据

Excel 的 OCR 功能将图像转换为文本——看似神奇，但确实会出错。利用 AI 功能，Excel 还可以预测这些错误可能发生的位置。

在这种情况下，Excel 已将除一条记录外的所有记录标记为可能包含错误。您可以单击“审阅”以扫描和仔细检查每条记录，然后对数据进行任何调整。例如，第一条条目以数字 1 开头，而应该是代词 I，如图 11-10 所示。

图 11-10. AI 检测到的 OCR 转录错误

在审查并识别任何潜在错误条目后，单击“插入数据”按钮将结果传输到 Excel。

Excel 的 AI 在预测文本可能含有错误时表现不错，但并非完美。例如，它可能会在条目中检测到错误，而实际上并没有——这在统计学中被称为假阳性。另一方面，它可能会批准一个实际上存在错误的条目——假阴性。

在统计学和机器学习中，平衡潜在的假阳性和假阴性表示一个重大挑战。目前，我们将依赖于 Excel 的判断，但随着您在分析旅程中的进展，可能会遇到更希望自行做出决策的情况。

插入像文本这样的非结构化数据到 Excel 中可能会带来一些挑战，因为 Excel 并非专为此类数据设计。为了保持组织性，建议为每个评论分配一个单独的单元格，并手动调整。例如，第 6 和第 7 行可以合并成一个单独的评论。

尽管 OCR 技术已存在一段时间，但其集成到 Excel 中尤为方便。此功能特别有利于管理财务报表或类似数字文档，用户可以在 Excel 中进行分析。

此演示的发现将为我们的下一个演示奠定基础。

使用 Azure 机器学习进行情感分析

尽管 Excel 传统上被认为是处理小型结构化数据集的工具，但与 AI 和 ML 相关的功能的引入使得这些传统限制变得模糊。这突显了在 Excel 中增强分析的巨大潜力。其中一个典型示例是利用 Excel 进行情感分析，从而评估文本评论集合中的情感。

情感分析是一种数据分析工具，利用机器学习算法解读非结构化数据中的情感和观点。它通常将文本分类为正面、负面或中性，帮助企业根据对品牌、产品或服务的整体情感改善客户满意度和解决问题。

手动评估几条评论不是问题，但对数千条或更多条评论进行这样的任务会变得具有挑战性。继续分析从前一节图像导入的一系列评论，我们当前的目标是将每条评论的情感分类为正面、负面或中性。为了自动化此任务，我们将利用 Azure 的文本分析功能。

第一步是将 Azure 机器学习加载项集成到 Excel 中。要做到这一点，导航到文件 → 获取加载项。在 Office 加载项对话框中，搜索“Azure 机器学习”，点击添加，然后点击继续安装。安装完成后，Azure 机器学习加载项将出现在 Excel 窗口的右侧。接下来，选择第二个选项，“文本情感分析（Excel 加载项求解器）”，如图 11-11 所示。

图 11-11. 选择 Azure 情感分析

Azure 要求用于情感分析的输入数据遵循特定的格式或模式。以下是一个例子，说明将数据结构化为机器友好的格式对 AI 有效运行的重要性。

对于这个特定任务，我们需要在工作簿中创建三个列标题：tweet_text、Sentiment 和 Score。这些列名称应与 Azure 机器学习加载项的“查看模式”部分完全匹配。

第一列标题 tweet_text 是我们放置在上一步导入的餐厅评论的位置。尽管名为如此，但该列标题不仅限于处理推文。 Sentiment 和 Score 列将由 Azure 的情感分析加载项填充，如图 11-12 所示。

图 11-12. 创建情感分析的模式

要配置情感分析的输入，请转到加载项的预测部分，并定义输入区域。这应包括单元格 A1:A9，包括标题。确保选择“我的数据有标题”的选项。

对于输出区域，情感分析的结果将从单元格 B1 开始显示。设置此单元格以确认结果将填充在该位置。确认输入与图 11-13 类似后，点击预测生成情感分析结果。

图 11-13. 定义情感分析的输入和输出

点击预测按钮后，你应该看到B和C列中填充了结果。不幸的是，这个过程有时可能会出现故障。如果遇到任何问题，请仔细检查架构和输入，或尝试重新启动 Excel。

如预期，Azure 已成功将每条评论分类为正面、负面或中性，并将结果记录在Sentiment列中。Score列包含一个从 0 到 1 的数值，表示 Azure 生成的情感分数。分数越高，表示情感越正面。这些分数随后被分类为负面、中性和正面组。

如果你想知道 Azure 是如何生成这些分数的，那是通过一个只有 Azure 能解释的复杂机器学习模型。像这样的自动化工具很方便，但它们通常缺乏透明性和可解释性。尽管这些工具无疑很强大，但它们并非没有瑕疵。例如，在图 11-14 中看到的情感分析结果中，第 3 行和第 5 行分别被标记为中性和负面，尽管它们在阅读后是负面和正面的评论。

图 11-14. 情感分析中的误标注评论

故事的寓意在于充分利用人工智能的潜力，同时也要进行批判性思维，不要仅仅依赖它。虽然 AI 具有人工智能，但你具备真正的人类判断力和直觉的力量。通过结合两者的优势，你可以做出更明智的决策。

无结构数据被广泛认为是难以处理的，但 AI 很适合处理这类数据。尽管 Excel 的主要重点是结构化数据，但越来越多地将其用于无结构数据，包括文本和图像。然而，就像分析数据在特定数据布局上表现最佳一样，Azure 的情感分析插件也依赖于精确的架构，以有效地解释无结构数据的输入和输出。

情感分析只是一个开始。即将在 Excel 中集成 GPT 动力语言建模的更新，如 Copilot，在这方面代表了一个重大进步。这种集成承诺增强 Excel 的能力，并使用户能够在其 Excel 工作流中利用强大的语言建模能力。

结论

总之，预测分析和人工智能是强大的工具，可以帮助你更深入地了解数据并预测未来的结果。Excel 已经发展到能够整合这些工具，使用户能够利用它们进行从图像识别到创建报告和分析的各种操作。通过使用 AI 动力洞察分析数据、使用 XLMiner 构建预测模型，并将 Excel 与 Azure 机器学习集成，你可以在 Excel 中释放预测分析和人工智能的全部潜力。

练习

使用 Excel 的增强分析和 AI 功能进行练习，使用本书附带的配套代码库中 exercises\ch_011_exercises 文件夹中的数据集。打开 ch_11_exercises.xlsx 开始：

使用 Azure 机器学习插件对位于 imdb 工作表中的电影评论数据集进行情感分析。之后，应用 XLMiner 插件生成获得的分数的描述统计信息。
将 life_expectancy.png 图像导入 Excel。使用数据分析功能生成一条线图，显示随时间变化的平均预期寿命。可能需要调整数据格式才能实现这一目标。

您可以在同一文件夹中的 ch_11_solutions.xlsx 中找到这些练习的解决方案。

¹ 这是在原始数据集中使用的拼写方式。

第十二章：Python 与 Excel

到目前为止，本书主要关注专为 Microsoft 生态系统设计的工具，如 Power Pivot 和 Power Query。但是，本章结尾讨论的是一种日益流行并与几乎所有可想象的计算机应用兼容的重要编程语言，包括 Excel 在内。欢迎初探 Python 如何增强您的 Excel 体验。

本章故意放置在书的末尾，因为我知道这可能会引起典型 Excel 用户的担忧。尽管如此，如果您已经达到这个阶段并且有兴趣在现代分析领域提升 Excel 的能力，我强烈建议您尝试 Python。

而且，不仅仅是我个人的意见，Microsoft 本身也通过在 Excel 中开发官方的本地 Python 应用程序来认可 Python 与 Excel 之间的协同作用，显著扩展了分析师利用这两个强大工具共同实现的范围。

然而，这只是 Python 与 Excel 结合的较新的专业应用之一，并未完全展示 Python 为 Excel 用户提供的广泛功能。在本章中，我们将探讨两者之间更广泛的概念关系，并附有一些示例。如果您发现本章内容有趣，我鼓励您深入了解 Python 在 Excel 中的原生集成。

注意

本章提供的示例并未利用 Excel 中的原生 Python 集成。相反，它们展示了结合这些工具以实现超越当前 Python-Excel 集成能力的自动化的替代方法。

读者先决条件

虽然本章可以在不具备 Python 先验知识的情况下完成，但熟悉列表、索引、循环以及 pandas 和 seaborn 包等概念将极大地增强您的理解能力。

如果您在阅读前想更多了解 Python，我建议先阅读我的书籍深入分析：从 Excel 到 Python 和 R，为 Excel 用户提供了 Python 的基本介绍。要更深入地了解这个主题，请查阅 Felix Zumstein 的Python for Excel: A Modern Environment for Automation and Data Analysis（O’Reilly，2021）。

本章主要是 Python 编程的实际演示。为了从中获得最大收益，我鼓励您积极参与并使用自己的计算机。您只需下载Anaconda 发行版，即可免费获得 Python 的完全版本。

Python 在现代 Excel 中的角色

有意向的学习者经常觉得 Python，作为 Excel 相关的工具，令人生畏。许多 Excel 用户认为它应该是他们学习列表中的最后一项，因为它不是微软产品，并且需要掌握一门新的语言能力。

对于每个 Excel 用户来说，Python 可能并不是最佳选择，但对于那些希望构建复杂自动化、版本控制项目和其他高级开发产品的人来说，认真考虑它是值得的。让我们探讨 Python 在现代分析中的角色及其与现代 Excel 的关系。

日益增长的技术栈需要胶水

当我刚开始作为分析师时，我的工具箱从头到尾都是 Excel。数据管理、报告、仪表板——一切都在熟悉的绿白界面下。

几年过去了，随着 Power BI、Office Scripts、Jupyter Notebooks 甚至是 Excel 内部的 Python 集成的引入，整个景观发生了巨大变化。这种扩展反映了更广泛的技术转变：从单一的全能应用向专业化、互连的工具网络转变。

浏览这个多样化的生态系统需要一个“指挥家”或“胶水”，无缝地整合各种组件。无论是在平台间传输数据、以新方式可视化数据，还是将基于云的机器学习模型部署到用户的仪表板上，Python 都是一个出色的选择。其多才多艺涵盖了从编写简单脚本到开发复杂企业级解决方案的各个领域，使其与多种操作系统和编程语言兼容。

微软赞扬 Python 作为一种多才多艺的“胶水”语言，在 Azure、Power BI、SQL Server 等各个领域中广泛应用。Python 在开发者和组织中的普及使其拥有了一个充满活力的用户社区和丰富的资源。

网络效应意味着更快的开发时间

“每个人都在做”通常不是参与某事的好理由，但在编程语言的情况下可能有其道理。

网络效应，即价值随用户基础增长而增加的概念，适用于编程语言。随着更多程序员的加入，代码共享扩展，为使用和进一步开发提供了更大的代码库，创造了一个良性循环。

Python 作为一种中性的“胶水”语言的多才多艺性导致它在各种职业中被采纳，包括数据库管理、Web 开发和数据分析。这意味着，无论您的 Excel 项目走向何方或需要哪些工具，都很有可能找到“懂”Python 的合作者。

例如，想象一下使用 Excel 开发库存跟踪器或类似工具，只发现它对于基本工作簿来说变得过于复杂，或者因其受欢迎程度而需求转变为独立的 Web 应用程序。这种转变通常代表着一项相当大的挑战。然而，如果最初的编程是用 Python 完成的，这种转变可以显著更加高效和快速。

Python 在 Web 开发领域的多功能性和广泛支持，有助于与各种 Web 技术和平台更顺畅地集成。因此，将你基于 Excel 的解决方案演变为完全运作的 Web 应用程序所需的时间大大缩短。基于 Python 开始使你处于有利地位，并为未来的增长或调整提供了坚实的基础，从而优化项目扩展的发展轨迹。

将现代开发引入 Excel

Python 使现代 Excel 开发者能够在软件开发中实施最佳实践，包括单元测试、版本控制和包开发。

单元测试

单元测试涉及测试软件的各个组件或单元，以验证每个单元在独立运行时的正确功能。它帮助开发者在开发过程的早期阶段识别和修复错误，确保最终产品的可靠性和性能。

许多编程语言提供单元测试功能，以保证代码按预期运行。然而，Excel 并不原生支持这一能力。虽然有替代工具可用，但 Python 凭借其广泛的网络效应和丰富的包装择，成为单元测试的优秀选择。自动化单元测试提高了可靠性，减少了错误的发生几率，这对技术水平各异的个人使用的 Excel 工作簿尤为有益。

版本控制

版本控制系统追踪仓库中的更改，使用户能够查看贡献、恢复到先前版本等操作。如果你曾经为区分多个类似于 budget-model-final.xlsx 和 budget-model-FINAL-final.xlsx 的工作簿而苦恼过，你一定能体会到版本控制的实用性。

尽管 Excel 提供了有限的版本控制功能，如在 OneDrive 中查看版本历史和使用 Spreadsheet Inquire 插件，但与将代码制作过程转移到 Python 时可用的广泛功能相比，仍然不足。

包开发和分发

如果你正在寻找一个立即采纳 Python 进行日常分析任务的理由，让我来强调一个关键优势：包。

虽然我喜欢开发自己的工具，但我也相信在满足需求时利用现有解决方案的价值。Python 强大的创建和分发包能力，尤其是通过 Python Package Index，为难以与 Excel 插件或 VBA 模块匹配的工具打开了一扇门。其中绝大多数工具都是开源的，且免费提供。

无论您的目标是从应用程序编程接口（API）收集数据、分析图像还是简单地生成描述性统计信息，Python 包的广泛可用性都充分说明值得投资学习 Python。值得注意的是，其中一些包甚至专为与 Excel 平稳集成而设计。

使用 Python 和 Excel 与 pandas 和 `openpyxl` 结合使用

考虑到 Python 在现代 Excel 中的角色，让我们探讨两者如何共同工作。促进此集成的两个关键包是 pandas 和 openpyxl。让我们依次考虑这两个。

为什么选择 pandas 作为 Excel？

如果您在 Python 中处理任何类型的表格数据，没有 pandas 是无法为您提供帮助的。该包允许您执行以下操作之一：

排序和筛选行
添加、删除和转换列
聚合和重塑表格
合并或附加多个表格

这相当于 Power Query 的 Python 等效物，使您能够创建可重用的数据清理和转换工作流程。就像 Power Query 一样，pandas 可以轻松地从各种来源（包括 Excel）导入数据，甚至将分析结果再次导出到 Excel。

pandas 在处理 Excel 中的限制

尽管如此，pandas 在与 Excel 工作簿的深度交互方面功能有限。例如，它无法处理以下任务：

单元格的高级格式选项，如应用特定样式或条件格式
支持在工作簿内执行 Excel 宏或 VBA 代码
直接访问 Excel 特定功能，如数据验证、图表、数据透视表或公式
操作工作表，如修改或删除数据

幸运的是，存在几个包来提供这些更高级的 Python/Excel 功能，其中最显著的是 openpyxl。

`openpyxl` 的贡献

openpyxl（发音为 open pie Excel）是一个 Python 包，提供处理 Excel 文件（特别是 .xlsx 文件格式）的功能。它允许用户以编程方式读取、写入和修改 Excel 电子表格。openpyxl 与 pandas 无缝集成，允许用户使用 pandas 清洗数据，并使用 openpyxl 为工作簿添加附加功能。

虽然 openpyxl 有其局限性，无法覆盖每种 Excel 使用情况，但它仍然是开始自动化 Excel 任务的最佳 Python 包。

如何使用 `openpyxl` 与 pandas

让我们来看一个典型的用例，即自动化常规的 Excel 业务报告，分析师需要从多个 Excel 工作表中生成每月销售报告。对于这些及其他任务，使用 pandas 与 openpyxl 的基本工作流程如下：

读取数据：使用 pandas 从各种来源提取数据到表格 DataFrame 中。
清理和分析数据：使用 pandas 清理和操作数据，执行计算，应用过滤器，处理缺失值，并得出相关的见解。
生成报告：使用 openpyxl 创建一个新的 Excel 工作簿或选择一个现有的工作簿。用整理后的数据填充工作簿，应用条件格式，创建图表，并添加任何所需的视觉元素。
保存报告：使用 openpyxl 保存更新后的 Excel 工作簿，指定所需的文件名和位置。
分发和自动化报告：通过电子邮件、文件共享平台或任何首选方法将生成的报告发送给预期的接收者。

其他用于 Excel 的 Python 包

尽管在与 pandas 特别是结合使用时对 Excel 任务非常强大，但 openpyxl 有其局限性。幸运的是，其他可以处理特定用例的包也是可用的。以下是一些需要注意的其他包：

XlsxWriter

与 openpyxl 类似，XlsxWriter 可用于在.xlsx格式中编写数据、添加格式和创建图表到 Excel 文件中。该包在处理大型数据集时性能优化良好。尽管如此，顾名思义，XlsxWriter 只能处理向 Excel 写入数据，而 openpyxl 可以读写数据。

xlwings

此包使 Excel 任务自动化成为可能，包括与 Excel 工作簿交互、运行 VBA 宏以及在 Windows 上访问 Excel 的 COM（组件对象模型）API。它提供了 Excel 与 Python 之间的完全双向通信，这是 openpyxl 所不能实现的。另一方面，此包需要更复杂的开发环境，在 Windows 上提供许多功能。

PyXLL

这是一个付费库，允许用户使用 Python 编写 Excel 插件。PyXLL 不仅可以自动化 Excel 工作簿，还允许开发人员构建独立的数据科学、金融交易和其他用途的应用程序。这使用户可以直接在 Excel 中使用 Python 开发的应用程序，而无需执行任何 Python 代码或理解底层 Python 机制。

存在许多其他用于 Excel 相关任务的 Python 包，每个都有其独特的优势和劣势。

展示 Excel 自动化演示与 pandas 和 openpyxl

是时候停止讨论并开始构建了！在本节中，我们将使用 pandas、openpyxl 等工具从 Python 自动化生成一个小报告。

首先，我们将使用 pandas 执行在 Excel 中难以实现的复杂数据清理和分析任务。然后，我们将创建一个概述工作表，包括简要的数据摘要和两个图表，一个来自原生 Excel，另一个来自 Python。最后，我们将加载整个数据集到一个新的工作表中，并格式化结果。

这个脚本的完成版本可以在书的配套存储库的 ch_12 文件夹中的 ch_12.ipynb 中找到。如果你不确定如何打开、导航或与该文件进行交互，请参阅 进阶数据分析：从 Excel 到 Python 和 R 的第三部分，作为 Python 和 Jupyter Notebooks 的入门。

让我们导入相关的模块和数据集，开始工作：

In [1]: # Data manipulation and visualization
        import pandas as pd
        import seaborn as sns

       # Excel file manipulation
       from openpyxl import Workbook
       from openpyxl.styles import PatternFill
       from openpyxl.chart import BarChart, Reference
       from openpyxl.drawing.image import Image
       from openpyxl.utils import get_column_letter
       from openpyxl.utils.dataframe import dataframe_to_rows
       from openpyxl.worksheet.table import Table, TableStyleInfo

pandas 库能够通过 read_excel() 函数从各种格式（包括 Excel 工作簿）中导入数据。让我们导入 contestants.xlsx 文件，并将结果命名为 contestants 的 DataFrame：

In [2]: contestants = pd.read_excel('data/contestants.xlsx')

清理 pandas 中的数据

一个 pandas DataFrame 可能包含数千甚至数百万行数据，因此在每个分析步骤都打印所有行数据是不切实际且计算效率低下的。然而，通过目视检查数据来理解其内容是非常重要的，这是 Excel 用户熟知的好处。为了快速审查数据并确保其符合我们的预期，我们可以使用 head() 方法，它显示前五行数据：

In [3]: contestants.head()

Out[3]:

  EMAIL                          PRE POST SEX    EDUCATION  STUDY_HOURS
0 smehaffey0@creativecommons.org 485 494  Male   Bachelor's 20.0
1 dbateman1@hao12@.com           462 458  Female Bachelor's 14.8
2 bbenham2@xrea.com              477 483  Female Bachelor's 22.2
3 mwison@@g.co                   480 488  Female Bachelor's 21.3
4 jagostini4@wordpress.org       495 494  Female NaN        26.2

基于这些数据预览，我们发现了一些需要解决的问题。首先，似乎一些电子邮件地址的格式不正确。此外，EDUCATION 列中有一个名为 NaN 的值，看起来不合适。我们可以通过一些方法解决数据集中的这些和其他问题，这在 Excel 的功能中很难或不可能完成。

处理元数据

一个良好的数据分析和转换程序应同样擅长处理数据和元数据，例如列标题。在这方面，pandas 是一款特别适合的工具。

目前，我们的 DataFrame 的列名都是大写的。为了更容易输入列名，我更喜欢使用小写名称。幸运的是，在 pandas 中，我们可以用一行代码完成这个操作：

In [4]: contestants.columns = contestants.columns.str.lower()
        contestants.head()

Out[4]:

  email                          pre post sex    education  study_hours
0 smehaffey0@creativecommons.org 485 494  Male   Bachelor's 20.0
1 dbateman1@hao12@.com           462 458  Female Bachelor's 14.8
2 bbenham2@xrea.com              477 483  Female Bachelor's 22.2
3 mwison@@g.co                   480 488  Female Bachelor's 21.3
4 jagostini4@wordpress.org       495 494  Female NaN        26.2

模式匹配/正则表达式

这个 DataFrame 中的 email 列列出了每个参赛者的电子邮件地址。我们的目标是从该列中消除任何包含无效电子邮件地址的行。

为了实现这一目标，我们可以使用文本模式匹配，这是由一个称为正则表达式的工具包来实现的。尽管 Power Query 提供了基本的文本操作功能，如转换文本大小写，但它不支持搜索特定的文本模式——这是 Python 提供的一项功能。

制作和验证正则表达式可能很复杂；然而，有许多在线资源如 ChatGPT 可以帮助完成这个过程。这是我们将使用的正则表达式：

In [5]: # Define a regular expression pattern for valid email addresses
        email_pattern = r'^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$'

接下来，我们可以使用str.contains()方法仅保留符合模式的记录：

In [6]: full_emails = contestants[contestants['email'].str.contains(email_pattern)]

要确认有多少行已被过滤掉，我们可以比较两个 DataFrame 的shape属性：

In [7]: # Dimensions of original DataFrame
        contestants.shape

Out[7]: (100, 6)

In [8]: # Dimensions of DataFrame with valid emails ONLY
        full_emails.shape

Out[8]: (82, 6)

将我们的选择精简为仅包含有效电子邮件地址的参与者，将参与者数量从 100 减少到 82。

分析缺失值

info()方法提供了 DataFrame 的维度和附加属性的全面概述：

In [9]: full_emails.info()

    <class 'pandas.core.frame.DataFrame'>
    Index: 82 entries, 0 to 99
    Data columns (total 6 columns):
     #   Column       Non-Null Count  Dtype
    ---  ------       --------------  -----
     0   email        82 non-null     object
     1   pre          82 non-null     int64
     2   post         82 non-null     int64
     3   sex          82 non-null     object
     4   education    81 non-null     object
     5   study_hours  82 non-null     float64
    dtypes: float64(1), int64(2), object(3)
    memory usage: 4.5+ KB

在包括 Power Query 在内的许多计算机程序中，术语null表示缺失或未定义的值。在 pandas 的 DataFrame 中，这个概念表示为NaN，即“不是一个数字”。

虽然基本的 Excel 没有确切对应于null，Power Query（如第二章中所述）包含了这个值，显著增强了数据管理和检查过程。然而，在 Power Query 中以编程方式处理这些缺失值，比如在所有列中消除它们，可能会带来挑战。使用 pandas 可以简化这个任务。

例如，如果希望确定哪些列包含最高百分比的缺失值，pandas 可以轻松地进行此分析：

In [10]: full_emails.isnull().mean().sort_values(ascending=False)

Out[10]:

    education      0.012195
    email          0.000000
    pre            0.000000
    post           0.000000
    sex            0.000000
    study_hours    0.000000
    dtype: float64

由于缺失值很少，且只在一列中，我们将简单地删除任何一行，只要该行的任何一列中存在缺失观察值：

In [11]: complete_cases = full_emails.dropna()

要确认 DataFrame 中已清除所有缺失观察值，我们可以再次使用info()方法：

In [12]: complete_cases.info()

    <class 'pandas.core.frame.DataFrame'>
    Index: 81 entries, 0 to 99
    Data columns (total 6 columns):
     #   Column       Non-Null Count  Dtype
    ---  ------       --------------  -----
     0   email        81 non-null     object
     1   pre          81 non-null     int64
     2   post         81 non-null     int64
     3   sex          81 non-null     object
     4   education    81 non-null     object
     5   study_hours  81 non-null     float64
    dtypes: float64(1), int64(2), object(3)
    memory usage: 4.4+ KB

创建一个百分位数

使用 pandas，我们将为post列创建一个百分位数排名，并通过describe()运行描述性统计以确认其有效性：

In [13]: complete_cases['post_pct'] = complete_cases['post'].rank(pct=True)
         complete_cases['post_pct'].describe()

Out[13]:

         count    81.000000
         mean      0.506173
         std       0.290352
         min       0.012346
         25%       0.265432
         50%       0.506173
         75%       0.759259
         max       1.000000
         Name: post_pct, dtype: float64

在 Excel 中创建百分位数列很简单，但通过 pandas 的统计函数、处理缺失值的方法等验证结果更容易。

让我们确认我们的数据集已经通过 pandas 进行了清理和转换：

In [14]: complete_cases.describe()

Out[14]:

      pre        post       study_hours post_pct
count 81.000000  81.000000  81.000000   81.000000
mean  480.506173 481.012346 23.445679   0.506173
std   20.626514  23.037737  8.178142    0.290352
min   409.000000 398.000000 0.000000    0.012346
25%   470.000000 467.000000 18.700000   0.265432
50%   484.000000 483.000000 22.600000   0.506173
75%   494.000000 497.000000 29.000000   0.759259
max   521.000000 540.000000 42.800000   1.000000

现在让我们使用 openpyxl 创建一个样式化的摘要报告。

使用 openpyxl 汇总结果

现在我们已经使用 pandas 中的各种技术正确准备了数据，我们将使用 openpyxl 在 Excel 中创建一个摘要。这将包括关键数字和标签，以及数据可视化。

创建一个摘要工作表

要开始使用 openpyxl 构建 Excel 工作簿，我们将声明表示工作簿和工作表对象的变量：

In [14]: # Create a new workbook and select the worksheet
         wb = Workbook()

         # Assign the active worksheet to ws
         ws = wb.active

从那里，我们可以使用其字母数字引用在活动表的任何单元格中填充。我将在单元格A1:B2中插入和标记平均前后分数：

In [16]: ws['A1'] = "Average pre score"

         # Round output to two decimals
         ws['B1'] = round(complete_cases['pre'].mean(), 2)
         ws['A2'] = "Average post score"
         ws['B2'] = round(complete_cases['post'].mean(), 2)

以这种方式将数据插入工作簿仅构成基本的数据转储；它不会影响数据在 Excel 中的显示方式。根据我的数据格式化经验，我预计 A 列中的标签将需要额外的宽度。我将通过工作表的 width 属性来调整它。

In [17]: ws.column_dimensions['A'].width = 16

本章后面，我们将讨论如何实现类似自动适应的列宽调整。但现在，让我们将注意力转移到向摘要中添加图表上。

使用 Python 生成 Excel 图表有两种方法。一种方法是直接从 Python 代码脚本化创建 Excel 图表，另一种方法是在 Python 中制作图表，然后将其插入到 Excel 工作簿中。这两种策略各有优缺点，将依次探讨。

插入图表

选项 A：创建本机 Excel 图表

Excel 的数据可视化功能很受欢迎，因为它们易于使用且对于基本的可视化任务非常有效。让我们探讨如何使用 openpyxl 从 Python 创建本机 Excel 图表。

首先，我们需要指定要创建的 Excel 图表类型，并在工作表中标识图表数据的位置：

In [18]: # Create a bar chart object
         chart = BarChart()

         # Define the data range
         data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=2)

接下来，我们将将此数据源添加到图表中，并为图表的标题和轴添加标签：

In [19]: # Add data to the chart
         chart.add_data(data)

         # Set chart title, axis labels
         chart.title = "Score Comparison"
         chart.x_axis.title = "Score Type"
         chart.y_axis.title = "Score Value"

让我们进一步定制这个图表。我们将设置类别标签以反映第一列中的数据，并且还将删除图例：

In [20]: # Set category names
         categories = Reference(ws, min_col=1, min_row=1, max_row=2)
         chart.set_categories(categories)

         # Remove the legend
         chart.legend = None

在图表完全定义并样式化后，现在是将其插入工作表的时候了：

In [21]: # Add the chart to a specific location on the worksheet
         ws.add_chart(chart, "D1")

选项 B：插入 Python 图像

Python 在数据可视化方面相比 Excel 具有优势，因为它提供了更多样化的可视化选项，并允许更轻松地定制绘图。例如，Excel 缺乏一种内置解决方案，用于同时分析多个变量之间的关系。然而，seaborn 数据可视化包提供了 pairplot() 函数，提供了一种快速方便的方法来探索这些关系。

下面的区块可视化了参赛选手在所选变量之间的关系。您可以在图 12-1 中查看结果：

In [22]: sns.pairplot(contestants[['pre', 'post', 'study_hours']])

图 12-1. 使用`seaborn`创建的 Pairplot

Python 不仅包含了一些在 Excel 中难以构建的图表类型，而且它们也很容易进行定制。例如，我想按sex来查看这个可视化效果，可以通过将其传递给 hue 参数来实现。我将保存此图的结果（见图 12-2）为 sns_plot，以便稍后参考：

In [23]: sns_plot = sns.pairplot(contestants[['pre', 'post',
            'study_hours', 'sex']], hue='sex')

图 12-2. 按性别绘制的 Pairplot

接下来，让我们将这个 pairplot 的静态图像放入工作簿中。首先需要将图像保存到磁盘，然后指定放置到工作簿中的位置：

In [21]: # Save pairplot to disk as an image
         sns_plot.savefig('pairplot.png')

         # Load saved image into the worksheet
         image = Image('pairplot.png')
         ws.add_image(image, 'A20')

我特别喜欢 Python 绘图的一个方面是它们易于定制，并且可以在各种类型的图之间轻松迭代。这种试错的方法在 Excel 中更具挑战性，因为其图表选项有限，而且增强它们的视觉吸引力需要相当大的努力。

然而，重要的是要注意，导入到 Excel 中的 Python 绘图本质上是静态图像。如果底层数据发生变化，这些图表不会像本地 Excel 图表那样自动更新。此外，导入的 Python 绘图缺乏诸如标准 Excel 图表中悬停在元素上时出现的工具提示等交互功能。

注意

Python 最近与 Excel 的集成使得可以创建一些具有一定交互性并且可以根据源数据变化而更新的 Python 绘图。关于这一功能的出色演示，请参阅 Excel MVP Mynda Treacy 的此博文。

Excel 与 Python 图表

这两种方法的优缺点总结如 Table 12-1 所示。

表 12-1. Python 与 Excel 图表的优缺点

	优点	缺点
构建本地 Excel 图表

图表将随 Excel 数据的变化而更新。
用户可以与绘图进行交互并进行自定义。
绘图可以与 Excel 的其他功能（如公式和数据透视表）集成。

Excel 中存在的图表类型数量有限。
定制或迭代 Excel 图表可能会有困难。

插入 Python 绘图的图像

可以访问几个强大的绘图库，如matplotlib和seaborn。
通过源代码可以轻松审计和重现绘图。

该图是静态图像，缺乏交互性。
无法从 Excel 更新或刷新图表。

方法选择取决于不同因素，如数据刷新需求以及 Excel 中特定图表类型的可用性。尽管如此，Python 自身的灵活性和多样的选项显示了其在处理 Excel 方面的强大能力。

添加一个样式化的数据源

现在我们已经创建了总结工作表，接下来我们将创建第二个样式化的工作表，其中包含complete_cases DataFrame。首先是定义这个新工作表：

In [25]: ws2 = wb.create_sheet(title='data')

接下来，我们将遍历complete_cases的每一行，并将每一行单独插入到工作表中：

In [26]: for row in dataframe_to_row(complete_cases, index=False, header=True):
            ws2.append(row)

将 DataFrame 插入工作表是一种开始，但由此产生的数据可能对用户阅读和操作有挑战性。让我们做一些改进。

格式化百分比

默认情况下，post_pct列将以小数形式而不是更易读的百分比形式格式化。为了解决这个问题，我们需要指定工作表中此列的位置并重新格式化它。

我将使用get_loc()方法来查找 DataFrame 中post_pct列的索引位置，并将结果加 1 以适应 Excel 的基于 1 的索引，而不是 Python 的基于 0 的索引。然后，get_column_letter()函数将把这个索引号转换为 Excel 的字母列引用：

In [27]: post_pct_loc = complete_cases.columns.get_loc('post_pct') + 1
         post_pct_col = get_column_letter(post_pct_loc)
         post_pct_col

Out[27]: 'J'

确定了适当的列之后，我将为每一行应用所需的数值格式：

In [28]: number_format = '0.0%'

         for cell in ws2[post_pct_col]:
             cell.number_format = number_format

转换为表格

如在第一章中讨论的，Excel 表格在数据存储和分析中具有多种好处。我们可以使用以下代码将这个数据集转换为表格：

In [29]: # Specify desired table formatting
         style = TableStyleInfo(name='TableStyleMedium9', showRowStripes=True)

         # Name and identify range of table
         table = Table(displayName='contestants',
             ref='A1:' + get_column_letter(ws2.max_column) +
                str(ws2.max_row))

         # Apply styling and insert in worksheet
         table.tableStyleInfo = style
         ws2.add_table(table)

应用条件格式

为了提高最终用户的可读性，我们可以对工作表应用条件格式。以下代码将对超过 90 分位数的参与者应用绿色背景填充，对超过 70 分位数的参与者应用黄色背景填充：

In [30]: # Define conditional formatting style
         green_fill = PatternFill(start_color="B9E8A2",
             end_color="B9E8A2", fill_type="solid")
         yellow_fill = PatternFill(start_color="FFF9D4",
             end_color="FFF9D4", fill_type="solid")

         # Loop through data table and conditionally apply formatting
         for row in ws2.iter_rows(min_row=2, min_col=1,
                max_col=len(complete_cases.columns)):
             # Convert index to 0-based indexing
             post_pct = row[post_pct_loc - 1].value
             if post_pct > .9:
                for cell in row:
                    cell.fill = green_fill
             elif post_pct > .7:
                 for cell in row:
                    cell.fill = yellow_fill

自动调整列宽度

尽管 openpyxl 缺乏自动调整工作表列宽的功能，我们可以使用以下代码实现类似的效果。它查找工作表每列中最宽的行，然后添加足够的填充来相应调整该列的宽度：

In [31]: for column in ws2.columns:
         max_length = 0
         column_letter = column[0].column_letter
         for cell in column:
             try:
                if len(str(cell.value)) > max_length:
                max_length = len(cell.value)
            except:
                pass
         adjusted_width = (max_length + 2) * 1.2
         ws2.column_dimensions[column_letter].width = adjusted_width

完成工作簿后，我们可以将结果保存到ch12-output.xlsx中：

In [32]: wb.save('output/ch12-output.xlsx')

结论

本章深入探讨了 Python 在增强现代 Excel 中的重要作用，强调了其作为开发中“胶水”语言的多功能性以及增强 Excel 功能的能力。通过实际演示，它展示了 Python 如何自动化 Excel 任务，引入了在仅仅电子表格程序内部难以实现的功能特性。

随着微软继续将 Python 整合到其数据分析套件中，Python 与 Excel 之间的协同作用将不断发展。尽管如此，本章为同时利用 Python 和 Excel 打下了坚实的基础，释放它们的全部潜力。

练习

要在书的配套存储库中的exercises\ch_12_exercises文件夹中的websites.xlsx文件的简明汇总报告中创建，首先使用提供的ch_12_exercises.ipynb Jupyter Notebook 作为基础。完成该 Notebook 中的缺失部分，以达到解决方案，解决方案在同一文件夹中命名为ch_12_exercise_solutions.ipynb中可用。

要准确编写代码，请参考本章提供的示例。鼓励您通过集成额外的自动化功能来增强您的工作。

第十三章：结论和下一步

在前言中，我阐述了以下学习目标：

在本书结束时，您应该能够使用现代 Excel 工具进行数据清洗、分析、报告和高级分析。

我真诚地希望您感到这一目标已经达到，现在您有信心进入分析的更多领域。随着我们结束您在现代分析中的旅程，我想介绍一些主题，进一步丰富和拓展您的理解。

探索 Excel 的其他功能

我之前在前言中提到，本书无法囊括 Excel 中每一个值得注意的现代分析功能。然而，我旨在提供一份突出特色及资源列表，供您独立探索。这些“荣誉提名”将进一步深化您对主题的理解。

当然，还有更多工具值得考虑，新工具也在不断涌现。如果您发现其他值得关注的工具，请花时间了解它们，并与我和社区分享您的发现。毕竟，发挥 Excel 的全部潜力需要集体努力，而不仅仅是一本书的力量。

`LET()` 和 `LAMBDA()`

LET() 和 LAMBDA() 函数显著提升了 Excel 的效率、可读性和灵活性。这里是它们的简明介绍：

LET()

LET() 函数使用户能够在公式中为变量赋值，提升了公式的可读性并简化了复杂计算。通过在公式开始时定义变量，用户可以轻松引用它们，从而简化了复杂公式的理解和修改。

LAMBDA()

LAMBDA() 函数使用户能够在 Excel 中创建自定义函数。这一功能支持代码的模块化，并通过将复杂操作封装为可在多个公式中重复使用的方式，减少了冗余。可以编写自定义函数以满足特定的分析需求，提升生产力并支持复杂、专业的模型和报告的开发。

实质上，LET() 和 LAMBDA() 为 Excel 用户提供了高级工具，用于优化其分析过程，增强公式的清晰度，并扩展电子表格的多功能性和适应性。想要深入探索这些函数的详细内容，请参阅《高级 Excel 公式：用 Excel 公式释放才华》第十五章。

Power Automate、Office Scripts 和 Excel Online

Excel 中的分析和自动化的演进得益于将 Power Automate、Office Scripts 和 Excel Online 整合在一起，每个都独特贡献于简化工作流程和提升生产力。

Power Automate 凭借其能够跨 Excel 和其他应用程序自动化广泛的任务而脱颖而出。其能力从简单的数据输入扩展到复杂的业务流程。特别值得注意的是它与 Power Query 合作自动化数据转换的能力。

超越数据转换，Power Automate 的实用性包括自动化报告生成和实施通知系统。这种多功能性使得常规任务自动化，释放用户专注于更具战略性的活动。

将 Office Scripts 与 Excel Online 集成进一步增强了 Power Automate 的功能。Office Scripts 在 Excel Online 中可用，提供基于 Web 的脚本语言，用于记录和自动化重复的 Excel 任务。与 Power Automate 结合使用时，它使这些脚本能够根据特定的触发器或事件自动执行。例如，设计用于调整工作簿内数据的脚本可以设置为在新文件上传到指定的 SharePoint 文件夹时自动运行。

此外，Power Automate 与 Excel Online 的互动为实时协作和数据处理开辟了新的机会。它支持操作，如创建和更新工作簿，以及从存储在云端的 Excel 文件中提取数据，允许多用户在 Excel Online 上无缝协作。这种集成确保工作流不仅自动化，而且可扩展并有助于协作工作的进行。

本质上，Power Automate、Office Scripts 和 Excel Online 之间的协同作用赋予 Excel 用户在自动化、协作和效率方面先进的能力。这种组合解锁了在管理和分析数据方面的新潜力，使其成为现代 Excel 用户优化工作流程和提升生产力不可或缺的资产。虽然专门涵盖这三者的详细指南或书籍可能不容易找到，但微软的文档提供了宝贵的入门见解和示例。

深入探索 Power Query 和 Power Pivot

本书的相当部分致力于探索 Excel 中的 Power Query 和 Power Pivot，这些工具对数据清洗和分析至关重要。虽然基本功能可以在几乎无需编程或技术知识的情况下执行，但深入理解这些工具并掌握其基本概念显著提升了它们的实用性。

Power Query 和 M

深入研究 Power Query 和 M 的中级概念，如参数、自定义函数、查询优化、自动刷新和管理查询，带来了诸多优势。

参数和自定义函数为数据清理和分析工作流程提供了灵活性和定制性。参数允许您定义值，用于控制查询的各个方面，如过滤条件或连接设置，使查询更具动态性和适应性以应对变化。例如，您可以创建一个参数来指定数据检索的日期范围，从而实现轻松的更新而不改变查询的核心逻辑。

另一方面，自定义函数 允许您创建可重用的代码片段，用于不在 Power Query 标准功能中直接提供或需要应用于多个数据集的复杂转换操作。通过定义自定义函数，您可以将一系列步骤封装成一个可调用的实体，简化数据处理任务，并确保在查询中保持一致性。

查询优化 在数据集规模和复杂性增长时至关重要。优化查询确保高效的数据处理并减少处理时间。学习查询优化技术有助于简化查询、消除不必要的转换，并提高整体性能。这些知识对于处理大型数据集和复杂转换，提高生产力和分析速度至关重要。

自动刷新 和 管理查询 也是保持数据分析及时更新和可靠性的重要步骤。自动化刷新流程确保定期更新，无需人工干预，节省时间和精力。有效的查询管理允许组织、监控和排除数据转换和连接中的问题。这种熟练性确保了数据的完整性、可靠性和准确的见解，以支持明智的决策。

除了提供灵活性、效率和增强数据可靠性的这些概念外，在使用 Power Query 时，与任何其他项目一样，心中要有明确的目标是很重要的。对于大多数项目而言，目标是创建与 Power Pivot 中的数据模型无缝集成的用户友好数据源。理解数据建模原则，如规范化和模式设计，对于充分发挥 Power Query 作为数据转换工具的潜力至关重要。这种方法作为理解数据建模的桥梁，涵盖了 Power Query 和 Power Pivot。

Power Pivot 和 DAX

要充分利用 Excel 中 Power Pivot 的能力，理解并应用中级 DAX 和数据建模概念至关重要。通过掌握这些概念，用户可以最大限度地利用 Power Pivot，并有效地处理复杂的业务问题和规则。

在将度量值整合到数据模型之前，确保数据模型本身的正确设计至关重要。这涉及熟悉星型模式（在第七章中简要介绍）、雪花模式和第三范式等概念。这些概念在数据建模和高效数据存储中起着重要作用。

随着您深入研究更复杂的 DAX 度量值，优化代码效率和可读性变得尤为重要。实现这一目标的一种有效技术是使用 DAX 变量。通过在度量值中存储中间结果，DAX 变量可以提升代码的清晰度和性能。

对于那些寻求深入了解和指导的人，以及在高级 Power Pivot 和 DAX 技术上的应用，我推荐阅读 Matt Allington 的书籍，《Supercharge Excel: When You Learn to Write DAX for Power Pivot》（Holy Macro! Books, 2018）。该资源提供了在这些领域扩展技能和释放 Excel 中 Power Pivot 潜力的宝贵见解和实用指导。

Power BI 用于仪表板和报告

在第七章中，您已经了解了如何将您的工作从 Power Pivot 加载到 Power BI，并且简要介绍了 Excel 和 Power BI 如何共同工作的基本知识。进一步扩展您的技能，包括 Power BI，将为您在数据分析、可视化和报告方面带来多种好处。通过阅读本书，您已经熟悉了 Power Query 和 Power Pivot/DAX 技术，因此向 Power BI 过渡可以是一个自然且有益的步骤。

对于 Excel 用户来说，Power BI 卓越的数据可视化和仪表板功能是一个关键吸引点。与 Excel 的通用多功能性不同，Power BI 专注于创建交互式报告和仪表板，这些报告和仪表板易于分享，并且可以在各种设备上轻松访问，有助于促进协作和获取洞察力。此外，Power BI 的实时分析功能允许基于最新数据进行即时决策，这对于处理动态数据或需要持续度量监控的人士来说是一个重要特性。

对于那些刚开始使用 Power BI 的人来说，理解 Power BI 服务生态系统至关重要，包括用于报告的 Power BI Desktop 和用于分发的 Power BI 服务。精通交互式可视化创建、超越 Excel 范围的高级数据建模以及用于复杂分析和计算的 DAX 技术至关重要。此外，了解 Excel 如何与 Power BI 集成可以提高在两个平台间流畅工作的能力，改善数据分析和报告的效率和效果。

Azure 和云计算

Azure 为现代分析用户在 Excel 中提供了引人注目的优势。Excel 用户可以通过 Azure 的基于云的基础设施和服务增强其分析工作流程。Azure 提供诸如 Power BI 的集成，用于交互式报告和可视化，Azure Machine Learning 用于预测模型，以及 Azure Cognitive Services 用于分析非结构化数据。在第十一章中的情感分析中，你已经简要探索了 Azure 的能力。

这种集成将 Excel 强大的功能与 Azure 先进的能力结合起来，为数据分析、机器学习和数据驱动决策打开了新的可能性。关于 Azure 的全面介绍，我推荐阅读 Jonah Andersson 的书籍Learning Microsoft Azure: Cloud Computing and Development Fundamentals（O’Reilly，2023 年）。关于如何将机器学习和 AI 引入 Power BI 使用 Azure 进行实践探索，Tobias Zwingmann 的书籍AI-Powered Business Intelligence: Improving Forecasts and Decision Making with Machine Learning（O’Reilly，2022 年）提供了宝贵的见解和实用的指导。

Python 编程

对于现代分析用户来说，学习 Python 在 Excel 中具有极高的价值，无论是要自动化电子表格的生产（正如在第十二章中介绍的那样），还是要在 Azure 中优化机器学习模型。Python 被广泛认可为 AI 开发的主要编程语言，在 TensorFlow、PyTorch 和 Keras 等框架中得到了显著应用。

随着 AI 的不断进步，新的工具和框架不断涌现，突显了开发人员在强大的 Python 基础上的重要性。通过掌握 Python 技能，您可以在快速发展的 AI 及相关领域取得成功。此外，Python 的多功能性不仅限于 AI，还涵盖了诸如 Web 开发、数据分析和自动化等各种应用。

要开始你的 Python 之旅，我推荐从 Al Sweigart 的书籍Automate the Boring Stuff with Python: Practical Programming for Total Beginners，第二版（No Starch Press，2019）入手。它是初学者的绝佳资源，提供了学习 Python 并将其应用于实际任务的实用方法。

大型语言模型与提示工程

第十一章介绍了 Excel 中通过"分析数据"功能进行自然语言查询的概念，标志着集成像 Copilot 这样的 AI 工具以增强数据分析的开始。随着 Copilot 的不断发展，掌握 AI 驱动的工具变得越来越关键。

对于参与现代 Excel 分析的人来说，理解大型语言模型（LLMs）和提示工程至关重要。LLMs，例如 OpenAI 的生成预训练变压器（GPT），擅长理解和生成类似人类的文本，使它们在分析非结构化数据、提取见解和生成详细报告方面不可或缺。

GPT，一个关键的 LLM，包括像 ChatGPT 这样的各种实现，专为对话互动设计。要充分利用对话 AI 和 ChatGPT，必须学习提示工程的基础知识。这涉及创建有效的提示，清晰传达模型的分析目标，从而获得精确和有价值的回应。基于 GPT 模型构建的 Copilot，在这些技术的精通中获益良多。

Excel 用户会发现，学习如何制定问题、构建提示以及包含相关背景信息可以增强其数据分析过程。这些技能为发现见解和支持明智决策开辟了新的途径。

分别一句

科幻作家威廉·吉布森曾著名地说过，“未来已经来临，只是分布不均。” 这一概念深深 resonates 现代使用 Excel 的经验。由于其广泛的功能和无限的发现可能性，感到不知所措是可以理解的。此外，随着技术的快速发展，普遍存在的恐惧是被落在后面。然而，通过采用逐步方法并承认完美掌握并非必需，您可以在 Excel 中释放比最初认为可能的更多潜力，并在当前的商业环境中保持竞争力。

反思您迄今为止在这本书中取得的成就；您有充分的理由感到自豪。然而，不要对这些成就过于沉迷。还有更多要发现，不久您就会意识到这本书仅仅是皮毛。在结束这一章和这本书时，接受挑战：勇敢前行，继续学习，并推动您的现代 Excel 分析之旅。

posted @ 2025-11-14 20:39 绝不原创的飞龙阅读(18) 评论(0) 收藏举报

刷新页面返回顶部

龙哥盟

掠夺·扩张·投机·博弈

Excel-现代数据分析-全-

Excel 现代数据分析（全）

序言

学习目标

先决条件

技术要求

技术要求

如何我到达这里

“现代分析”是什么？为什么选择 Excel？

书籍概述

第一部分，使用 Power Query 进行数据清洗和转换

第二部分，使用 Power Pivot 进行数据建模与分析

第三部分，Excel 数据分析工具包

章节末练习

这不是一份清单

本书使用的约定

提示

注意

警告

使用代码示例

奥莱利在线学习

注意

如何联系我们

致谢

第一部分：使用 Power Query 进行数据清洗和转换

第一章：表格：现代 Excel 的入口

创建和引用表头

图 1-1. 将数据源转换为表格

图 1-2. 具有标题的 Excel 表格

图 1-3. Excel 标题引用公式

查看表格页脚

图 1-4。将页脚添加到表格

图 1-5。自定义 Excel 表格的页脚

命名 Excel 表格

图 1-6。Excel 中的名称管理器

格式化 Excel 表格

图 1-7。表格设计自定义选项

更新表格范围

组织数据以进行分析

图 1-8。整理前后的批发客户

结论

练习

第二章：Excel Power Query 的初步步骤

什么是 Power Query？

Power Query 作为 Excel 的神话破解者

“Excel 不具备可重复性”

“Excel 没有真正的 null”

“Excel 不能处理超过 1,048,576 行”

Power Query 作为 Excel 的 ETL 工具

提取

图 2-1. 从表中提取数据

转换

图 2-2. 从 Power Query 添加索引列

加载

图 2-3. 从 Power Query 加载数据到 Excel 表格

Power Query 编辑器之旅

图 2-4. Power Query 编辑器

功能区菜单

图 2-5. Power Query 功能区

图 2-6. Power Query 公式栏

注意

将注意力从功能区转移到编辑器左上角的查询列表上。在这里，你会找到导入的数据源，可以在之间切换查看。虽然这些数据源都存在于工作簿中，但请记住 Power Query 支持各种数据源，包括 .csv 文件、数据库、网页等。

图 2-7. Power Query 中的查询列表

现在，将注意力转移到占据编辑器大部分空间的组件：数据本身。与在 Excel 中可以自由操作数据不同，例如隐藏列或插入公式，Power Query 强制实施编辑限制。

查询

图 2-10. 在 Power Query 中插入一个中间步骤

警告

退出 Power Query 编辑器

图 2-11. Power Query 加载选项

返回到 Power Query 编辑器

图 2-12. 查询和连接选项

Power Query 中的数据概要分析

什么是数据概要分析？

探索数据预览选项

图 2-13. Power Query 中的数据预览选项

“等宽字体”和“显示空白”

“列质量”和“列分布”

有效单元格是什么？