关注点分离

编程最重要的设计原则之一就是 关注点分离 （separation of concerns），有时候也称作 模块化 （modularity）。意思就是说，一系列相关的功能应当被视作程序中一个独立的部分来处理，从而可以在不影响应用程序其他部分的情况下，轻松地替换这一部分。笼统地讲，一个应用程序通常被分为如下3层 ^[2] 。

• 表示层（presentation layer）
• 业务层（business layer）
• 数据层（data layer）

为了便于解释，想一想图1-1所示的简单汇率转换器，对应的currency_converter.xlsx文件在配套代码库的xl文件夹中。

图1-1：currency_converter.xlsx

这个应用程序的工作原理是这样的：在A4和B4中分别输入Amount（金额）和Currency（货币），Excel会在D4中把输入的金额转换为对应的美元金额。很多工作表应用程序是这样设计的，并且每天都会用到业务中。下面来把它划分成3层。

表示层

这一层是你可以看到并与之交互的部分，也就是所谓的用户界面。A4单元格、B4单元格和D4单元格及它们的标签构成了这个汇率转换器的表示层。

业务层

这一层负责特定应用程序的逻辑。D4单元格定义了金额如何被转换成美元金额。公式 =A4 * VLOOKUP(B4, F4:G11, 2, FALSE) 的意思就是让金额乘以汇率。

数据层

顾名思义，这一层负责访问数据。D4单元格的 VLOOKUP 部分负责完成这项工作。

数据层会访问从F3开始的汇率表数据，这一块单元格就像是这个应用程序的数据库一样。如果观察得够仔细，你就会发现每一层都有D4。这样一个简单的应用程序把表示层、业务层和数据层全部混在了一个单元格中。

对于这样一个简单的汇率转换器来说，这可能不是什么问题。不过很多时候一个Excel文件一开始很小，但很快就会变得越来越大。如何解决这种问题呢？大部分专业Excel开发者会建议你为每一层（用Excel的话来说，应该叫 输入层 、 计算层 和 输出层 ）单独分配一张工作表。同时还可以为每一层指定一种对应的颜色，比如为所有的输入层单元格设置蓝色的背景色。在第11章中，我们会依照这种层次来构建一个真实的应用程序：用Excel作为表示层，而将业务层和数据层都迁移到Python中。在Python中，组织代码会更容易。

既然明白了什么叫关注点分离，那么接下来了解一下什么是DRY原则。

DRY原则

由Andy Hunt和David Thomas编写的《程序员修炼之道》一书让DRY原则大受欢迎。DRY的意思是“不要自我重复”（don't repeat yourself）。没有重复的代码意味着代码行数更少，错误也更少，代码自然也就更容易维护。如果你的业务逻辑位于单元格公式中，那么就难以遵守DRY原则，因为没有一种机制可以让你在其他工作簿中重用这些公式。不幸的是，这就意味着每当要启动一个新的Excel项目时，最常见的做法就是从一个之前的项目或者模板中把这些含有公式的工作簿复制过来。

如果你会写VBA，那么函数是重用代码的最常用的方法。函数可以让你在各处都能访问到同一个代码块，比如用各种宏调用函数。如果你有几个很常用的函数，你可能会想让它们能够被多个工作簿共享。要达成这样的目的，标准做法是依靠插件，但是VBA插件并没有稳健的分发方式和更新方式。为了解决这一问题，微软引入了Excel内部插件商店。但这只适用于用JavaScript编写的插件，所以对VBA程序员来说并没有用。这就意味着我们在写VBA代码时还是要频繁地复制粘贴。假设我们需要在Excel中用到 三次样条函数 （cubic spline function），它可以在坐标系中基于几个给定的点插值成一条曲线。固定收益交易员经常用这种函数，他们通过一些已知的到期日 / 利率比组合来得到所有到期日的利率曲线。如果你在网上搜索“三次样条Excel”，很快就能找到你想要的VBA代码。但问题在于，写这些代码的人初衷可能是好的，但代码并没有附带文档，也没有经过测试。这些代码可能对于大多数的输入能正常工作，但对于一些不常见的边界条件效果又如何呢？如果要交易上百万的固定收益投资组合，那么你肯定想用一些可靠的东西。当内部审核员发现代码的来源时，至少要让他们也觉得这些代码是可靠的。

我们会在1.2.2节中看到，利用包管理器，Python可以很方便地分发代码。不过在那之前，先来学习测试，它是稳定软件开发大厦的一块基石。

测试

如果你让Excel开发者测试一下他们的工作簿，他们很可能只是随随便便点几下，看看宏有没有正常工作，改几个输入看看输出结果是否正常。然而，这是一种极具风险的做法，因为Excel很容易出现一些难以察觉的问题。例如，你可能用“写死”的值覆盖了公式，也可能忘了调整隐藏列中的公式。

而如果你让专业软件开发人员测试他们的代码，他们会写 单元测试 （unit test）。顾名思义，这是一种可以测试程序各个组件的机制。举例来说，单元测试会确保程序中的每一个函数都正常工作。大部分编程语言会提供一种自动执行单元测试的方法。执行自动测试可以使你的代码库的可靠性大幅提升，并且在一定程度上，测试会确保你在编辑代码时不会破坏当前正常工作的代码。

回顾一下图1-1中的汇率转换工具，可以输入下面的数据进行测试：以100EUR（欧元）作为金额，1.05作为EURUSD（欧元 / 美元）汇率，检查D4单元格是否正确返回105美元。这个测试的用处在哪儿呢？假设你不小心删掉了D4单元格中的公式，不得不重写一遍，但是把“金额乘以汇率”写成了“除以汇率”——毕竟货币运算总是让人晕头转向。现在再来进行上面提到的测试，你会发现测试失败了，因为100欧元 /1.05并不会得到测试所期望的105美元。经过这样的测试，在把工作表交给用户之前，你就可以检测和修复公式中的错误。

大多数传统编程语言提供了一种甚至多种轻松编写单元测试的框架，但是Excel并未提供。好在单元测试的概念本身很简单，而我们还可以把Python强大的单元测试框架用到Excel上。对单元测试的深入讲解已经超出了本书的范围，不过你可以看一看我的这篇博客文章（“Unit Tests for Microsoft Excel”），在文章中我会通过实际的例子向你介绍相关知识。

通常程序员会对单元测试进行配置，每当代码被提交到版本控制系统的时候，它就会自动运行。接下来我们会介绍什么是版本控制系统，以及为什么很难将其运用到Excel文件上。

版本控制

专业程序员还有一个特点就是会使用 版本控制 （version control）系统，或者称为 源代码控制 （source control）系统。 版本控制系统 （version control system，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开发者就必须坚守某种命名规则——当然这本身没什么问题。但是，把文件的版本迭代记录保存在本地并不能让你享受到源代码控制的好处，也就是无法顺畅地进行合作、同行评审、推进进度和审查日志。如果你想让工作簿更加安全和稳定，就不能忽略这些流程。通常专业程序员都会结合像GitHub、GitLab、Bitbucket和Azure DevOps这样的Web平台来使用Git，这些平台可以让你提出所谓的 拉取请求 （pull request）和 合并请求 （merge request）。这些操作可以让开发者正式地请求负责人将他们的更改合并到主数据库中。一次拉取请求会提供如下信息：

• 更改的作者；
• 更改发生的时间；
• 在 提交信息 （commit message）中描述的更改目的；
• 在 diff视图 （其中新代码以绿色高亮显示，删掉的代码以红色高亮显示）中展示的更改细节。

这样其同事或者团队领导就可以审核更改并发现问题。一般只要多一个人检查就会多发现一两个问题，也可能会给程序员宝贵的反馈。既然有这么多好处，那为什么Excel开发者还是喜欢用本地文件系统和命名规则来进行版本控制，而不用专业的Git呢？

• 很多Excel用户要么完全不知道Git，要么是因为Git相对陡峭的学习曲线入门即放弃。
• Git允许多名用户并行地操作同一个文件的本地副本。在这些用户都提交了工作成果之后，Git通常会自动合并所有更改且无须手动干预。但这对Excel文件不起作用：如果这些文件是通过多个副本并行更改的，那么Git并不知道如何将这些更改合并到一个文件中。
• 即便处理好了上述问题，Git也无法像在处理文本文件时那样好用，因为Git无法体现出Excel文件的更改细节，这就使得人们无法进行同行评审。

考虑到上述问题，我的公司选择了xltrail。xltrail也是一个基于Git的版本控制系统，但它知道怎么处理Excel文件。xltrail隐藏了Git的复杂性，使得商业用户用起来更舒服，并且在你想用GitHub之类的平台来跟踪文件时，它也可以连接到外部Git系统。xltrail会跟踪工作簿的各个不同的组件（涵盖单元格公式、名称范围、Power Query和VBA代码），让你能够利用版本控制的突出优势，这其中就包括同行评审。

要想轻松对Excel进行版本控制，还有一个选项就是将业务逻辑迁移到Python中来，第10章会对此进行介绍。用Git来跟踪Python文件很轻松，因而你用Python编写的工作表工具管理起来也会很轻松。

虽然本节叫“编程最佳实践”，但我主要是想解释，和Python一类的传统编程语言相比，为什么在Excel中进行这些实践很难。在把注意力投向Python之前，我想先简单介绍一下微软对Excel现代化做出的尝试：Power Query和Power Pivot。

Power Query和Power Pivot

微软在Excel 2010中引入了一个叫作Power Query的插件。Power Query可以连接各种数据源，包括Excel工作簿、CSV文件、SQL数据库，等等。也可以连接像Salesforce这样的平台，或是通过扩展连接上面没有提到的数据源。Power Query的核心功能是处理一张工作表装不下的数据集。在加载数据之后，你还可以通过额外的操作来清理、操作数据，使之成为Excel可用的形式。例如，可以把一列分成两列、合并两张表、对数据进行过滤和分组，等等。自Excel 2016起，Power Query就不再是一个插件，而是可以通过功能区标签页上的“获取数据”按钮直接访问。Power Query在macOS中只支持一部分功能，但是目前仍在积极开发中，在今后的版本中应该可以得到完全支持。

Power Pivot和Power Query联系密切，从概念上来讲，在利用Power Query获取和清理数据之后，就该Power Pivot上场了。Power Pivot帮助你以一种引人入胜的方式直接在Excel中分析和呈现数据。你可以把它视作一种传统意义上的数据透视表。和Power Query一样，它也可以处理大型数据集。Power Pivot让你可以用关系和层次来定义形式上的数据模型，并且可以通过DAX公式语言添加计算列。Power Pivot也是在Excel 2010中引入的，但目前它仍然以插件形式存在，尚未支持macOS。

如果你喜欢使用Power Query和Power Pivot，并且想在其上构建仪表板，那么还应该看看Power BI。现在来看看原因。

Power BI

Power BI是在2015年发布的一个独立应用程序。它是微软针对Tableau和Qlik这类工具做出的反击。Power BI Desktop是免费的，可以从Power BI主页下载。但是要注意，Power BI Desktop只支持Windows。Power BI希望通过在交互式仪表板中可视化巨大的数据集使其更容易理解。和Excel一样，它的核心功能页依赖于Power Query和Power Pivot。Power BI的商业版可以让你在线和他人合作，并共享仪表板。普通的桌面版中不带有这些功能。对本书所涉及的内容来说，Power BI令人激动的原因在于它自2018年起就支持Python脚本了。通过Python图表库，Python既可用于数据查询部分，也可用于可视化部分。对我来说，在Power BI中使用Python感觉有点儿别扭，但重要的是这体现了微软已经意识到Python对于数据分析的重要性。相应地，人们对于Excel获得官方Python支持的希望也日渐高涨。

那么Python有什么了不起的地方使微软选择让Power BI来支持它呢？下一节会给出一些答案。