《Python for Excel》读书笔记连载1：为什么为Excel选择Python？

本节为《Chapter 1：Why Python for Excel?》的第一部分，简单地讲解了Excel的历史，Excel编程的最佳实践，以及Excel为适应发展而作出的变化。

当你每天花费很多时间手动更新Excel工作簿，或者经常处理涉及到财务或者重要任务的工作簿时，你应该学习如何使用编程语言使流程自动化。与你手工操作Excel电子表格相比，自动化消除了人为错误的风险，并允许你将更多的时间花在更高效的任务上。你可以考虑VBA，也可以考虑Python，特别是对于数据量大和公式多的工作簿。

历史

Excel和Python已经存在很长时间了。

Excel于1985年由Microsoft首次推出，但只适用于AppleMacintosh。直到1987年，微软Windows才以Excel2.0的形式发布了第一个版本。在微软之前，电子表格市场已经开始发展：VisiCorp在1979年推出了VisiCalc，随后是Lotus软件公司在1983年推出了Lotus1-2-3。开始时微软并没有在Excel上领先，他们发布的Multiplan，一个电子表格程序，可以在MS-DOS和其他一些操作系统上使用，但不能在Windows上使用。

Python诞生于1991年，比Excel晚了六年。虽然Excel很早就开始流行，但Python花了更长的时间才在某些领域（例如web开发或系统管理）得到采用。2005年，当基于数组的计算和线性代数包NumPy首次发布时，Python开始成为科学计算的一个重要替代品。NumPy组合了两个先前的包，因此将围绕科学计算的所有开发工作简化为一个项目。如今，它构成了无数科学软件包的基础，包括2008年推出的pandas软件包，这在很大程度上是Python在2010年后开始在数据科学和金融领域广泛采用的原因。多亏了pandas，Python和R已经成为数据科学任务（例如数据分析、统计和机器学习）中最常用的语言之一。

Python和Excel都是很久以前发明的，这并不是它们唯一的共同点：Excel和Python都是一种编程语言。虽然你觉得这么说Python可以，但并不理解为什么也可以这么说Excel，下面就给你解释。

Excel是一种编程语言

如果你使用Excel不只是记录购物清单，那么肯定会使用=SUM(A1:A4)之类的函数来对一系列单元格求和。如果你想想这是如何工作的，就会注意到一个单元格的值通常取决于一个或多个其他单元格，这些单元格可能会再次使用依赖于一个或多个其他单元格，依此类推。执行这种嵌套函数调用与其他编程语言的工作方式没有什么不同，只是在单元格中编写代码，而不是在文本文件中编写代码。

2020年底，微软推出了lambda函数，允许你用Excel自己的公式语言编写可重用函数，而不必依赖VBA等其他语言。根据Excel产品负责人BrianJones的说法，这是最终使Excel成为“真正的”编程语言的缺失部分。这也意味着Excel用户真的应该被称为Excel程序员！

不过，Excel程序员有一个特殊的地方：他们中的大多数是没有受过正规计算机科学教育的商业用户或领域专家。例如，他们可能是交易员、会计师或工程师。因此，他们的电子表格工具是为解决业务问题而设计的，通常忽略了软件开发中的最佳实践。这样，这些电子表格工具经常在同一张表上混合输入、计算和输出，它们可能需要执行不明显的步骤才能正常工作，并且在没有任何安全保护的情况下完成关键内容修改。换句话说，电子表格工具缺乏可靠的应用程序体系结构，并且通常没有文档记录和测试。有时，这些问题可能会产生毁灭性的后果：如果你在进行交易之前忘记重新计算交易工作簿，可能会买卖错误数量的股票，这可能会导致你赔钱。

有一些使你的Excel工作更加安全的最佳实践，包括分离关注点、DRY原则、测试和版本控制。并且，当你开始将Python与Excel结合使用时，遵循这些最佳实践将更容易。

分离关注点

编程中最重要的设计原则之一是分离关注点，有时也称为模块化。这意味着一组相关的功能应该由程序的一个独立部分负责，这样就可以轻松地替换它，而不会影响应用程序的其余部分。应用程序通常分为以下几层：

• 展示层
• 业务层
• 数据层

为了便于说明，举一个简单的货币转换器示例，如图1-1所示。

这个应用程序的工作方式是：分别在单元格A4和B4中输入金额和货币，Excel将在单元格D4中将其转换为美元。

将该应用程序分解为以下几层：

展示层

你所看到并与之交互的内容，即用户界面：单元格A4、B4和D4的值及其标签构成货币转换器的表示层。

业务层

负责特定应用程序的逻辑：单元格D4定义如何将金额转换为美元。公式：

=A4*VLOOKUP(B4，F4:G11，2，FALSE)

转换为金额乘以汇率。

数据层

负责访问数据：单元格D4的VLOOKUP部分正在做这项工作。

数据层访问从单元格F3开始的交易汇率表中的数据，该表充当这个小应用程序的数据库。如果你看仔细点，可能会注意到单元格D4出现在所有三个层中：这个简单的应用程序将展示层、业务层和数据层混合在一个单元中。

图1-1

对于这个简单的货币转换器来说，这不一定是一个问题，但通常情况下，一开始只是一个小的Excel文件，很快就会变成一个更大的应用程序。如何改善这种情况？大多数专业的Excel开发人员建议为每个层使用单独的工作表，用Excel的术语，通常称为输入、计算和输出。后面，我们将基于这些层构建一个真正的应用程序：Excel将是展示层，而业务层和数据层将移到Python中，在Python中更容易正确地构造代码。

DRY原则

DRY原则：不要重复自己。没有重复的代码意味着更少的代码行和更少的错误，这使得代码更易于维护。

如果编写VBA，最常见的可重用代码是函数。例如，通过函数可以从多个宏访问同一代码块。如果你有多个一直在使用的函数，可能希望在工作簿之间共享它们。跨工作簿共享VBA代码的标准工具是加载宏，但VBA加载宏缺乏一种可靠的分发和更新方式。虽然Microsoft引入了Excel网络加载宏商店来解决该问题，但这只适用于基于JavaScript的加载宏，因此VBA编码人员没有选择。这意味着在VBA中使用复制/粘贴方法仍然非常常见。

测试

当告诉Excel开发人员测试他们的工作簿时，他们很可能会执行一些随机检查：单击一个按钮，查看宏是否仍然执行它应该执行的操作，或者更改一些输入，并检查输出是否合理。然而，这是一种危险的策略：Excel使引入难以发现的错误变得容易。例如，可以使用硬编码值覆盖公式，或者忘记调整隐藏列中的公式。

当告诉专业软件开发人员测试他们的代码时，他们会编写单元测试。顾名思义，它是一种测试程序各个组件的机制。例如，单元测试确保程序的单个功能正常工作。大多数编程语言都提供了一种自动运行单元测试的方法。运行自动测试将显著提高代码库的可靠性，并合理地确保在编辑代码时不会破坏当前运行的任何东西。

如果你查看图1-1中的货币转换工具，可以编写一个测试，检查单元格D4中的公式是否正确返回105美元，输入值如下：金额为100欧元，汇率为1.05欧元。这有什么帮助呢？假设你意外地删除了带有换算公式的D4单元格，并且必须重写它：不是将金额乘以汇率，而是除以汇率。当运行上述测试时，将会失败，因为100欧元/1.05将不会像测试预期的那样再产生105美元。这样，你可以在将电子表格交给用户之前检测并修复公式。

几乎所有的传统编程语言都提供了一个或多个测试框架来编写单元测试，而无需花费太多精力，但Excel却不能。幸运的是，单元测试的概念非常简单，通过将Excel与Python连接，可以访问Python强大的单元测试框架。

单元测试通常设置为在将代码提交到版本控制系统时自动运行。

版本控制

专业程序员的另一个特点是他们使用系统进行版本控制或源代码控制。版本控制系统（VCS）跟踪源代码随时间的变化，允许你查看谁修改了内容、修改时间和原因，并允许你在任何时间点恢复到旧版本。现在最流行的版本控制系统是Git。它最初是为了管理Linux源代码而创建的，从那时起，它征服了编程世界，甚至微软在2017年采用Git来管理Windows源代码。相比之下，在Excel世界中，目前最流行的版本控制系统是以文件夹的形式出现的，其中的文件存档方式如下：

currency_converter_v1.xlsx

currency_converter_v2_2020_04_21.xlsx

currency_converter_final_edits_Bob.xlsx

currency_converter_final_final.xlsx

使用Excel使版本控制更容易的另一个选项是将业务逻辑从Excel移动到Python文件中，后续章节中会介绍这一点。由于Python文件很容易用Git跟踪，因此电子表格工具最重要的部分将在你的控制之下。

虽然本节称为编程最佳实践，但主要是指出为什么Excel比Python等传统编程语言更难遵循这些最佳实践。在我们将注意力转向Python之前，简单介绍一下PowerQuery和PowerPivot，这是微软对Excel进行现代化的尝试。

现代Excel

Excel的现代时代始于Excel2007，当时引入了功能区菜单和新的文件格式（例如，xlsx而不是xls）。然而，Excel社区使用现代Excel来引用与Excel2010一起添加的工具：最重要的是PowerQuery和PowerPivot，它们允许你连接到外部数据源并分析太大而无法放入电子表格的数据。

Power Query和PowerPivot

在Excel 2010中，Microsoft引入了一个名为PowerQuery的加载项。PowerQuery连接到多种数据源，包括Excel工作簿、CSV文件和SQL数据库，还提供与Salesforce等平台的连接，甚至可以扩展到与未开箱即用的系统的连接。PowerQuery的核心功能是处理太大而无法放入电子表格的数据集。加载数据后，你可以执行额外的步骤来清理和操作数据，使其以Excel中可用的形式出现。例如，你可以将一列拆分为两列，合并两个表，或者对数据进行筛选和分组。自Excel2016以来，PowerQuery不再是外接程序，而是可以在功能区数据选项卡上通过“获取数据”按钮直接访问。

Power Pivot与PowerQuery齐头并进：从概念上讲，这是使用PowerQuery获取和清理数据之后的第二步。PowerPivot可帮助你直接在Excel中以吸引人的方式分析和显示数据。可以将其视为传统的透视表，与PowerQuery一样，它可以处理大型数据集。PowerPivot允许你定义具有关系和层次结构的正式数据模型，并且你可以通过DAX公式语言添加计算列。PowerPivot也在Excel2010中引入，但仍然是一个附加模块。

Power BI

Power BI是2015年发布的独立应用程序，这是微软对Tableau或Qlik等商业智能工具的回应。PowerBI Desktop是免费的，因此如果你想使用它，转到PowerBI主页并下载它。注意，PowerBI Desktop仅适用于Windows。PowerBI希望通过在交互式仪表板中可视化大型数据集来理解它们。它的核心依赖于与Excel相同的PowerQuery和PowerPivot功能。商业计划允许你在线协作和共享仪表板，但这些仪表板与桌面版是分开的。PowerBI自2018年以来一直支持Python脚本,通过使用Python的绘图库，可以将Python用于查询部分和可视化部分。在PowerBI中使用Python感觉有点笨拙，但重要的一点是，微软已经认识到Python在数据分析方面的重要性。因此，人们很希望有一天Python也能找到一种正式的方式进入Excel。