1.1 Excel作为一门编程语言

本节首先会介绍Excel是如何被当作编程语言的，让你明白为什么工作表出问题经常上新闻。然后，我们会看看在软件开发社区中兴起的一些最佳实践，这些最佳实践可以让你规避很多典型的Excel错误。最后，我们会简单了解Power Query和Power Pivot 这两种现代Excel工具，它们涵盖了我们之后会用pandas 来实现的各种功能。

如果用Excel做过比购物清单更复杂的表格，那么你肯定用过像 =SUM(A1:A4)这样的对单元格求和的函数。如果花几秒思考一下它的工作原理，你就会发现一个单元格的值通常依赖于一个或多个其他单元格。也就是说，这些被依赖的单元格可能又会在它依赖的其他单元格上再次调用这个函数。这种函数的嵌套调用和其他编程语言的工作方式并无二致，只不过你是在表格中写代码，而不是写在一个文本文件中。如果你还是不相信 Excel是一门编程语言，那就继续往下看。在2020年年末，微软宣布会在Excel中引入lambda 函数，让你可以用Excel自己的公式语言来编写可重用的函数，也就是说不一定要使用VBA 之类的其他语言来实现这种效果。据 Excel产品部门的负责人 Brian Jones 所说，这是让Excel成为一门“真正的”编程语言1的点睛之笔。这也就意味着 Excel用户可以被称作Excel程序员了！

1可以在Excel博客上读到这篇关于lambda 函数的文章（“Announcing LAMBDA: Turn Excelformulas into custom functions”）。

不过Excel程序员和一般的程序员有点儿不一样，他们大部分是商业用户或领域专家，并没有接受过与计算机科学相关的正式教育。举例来说，他们可能是交易员、会计、工程师，等等。他们的工作表工具都是用来解决业务问题的，并且一般不会注意软件开发上的最佳实践。因此，他们的工作表工具往往会把输入、运算和输出全部混在一起，可能需要进行一些很麻烦的操作才能让工作表正常工作。这个过程涉及的很多关键性改动也毫无安全保障可言。这些软件往往缺少稳固的应用程序架构，并且通常没有文档说明，也没经过测试。有时候这些问题可能会造成灾难性的后果，如果你在交易前忘了重新计算你的交易工作簿，则可能会导致对股份进行了错误的交易——你可能就亏了。如果你不是用自己的钱在交易，那么可能就会像下文中所说的那样，你会上新闻。

1.1.1 新闻中的Excel

Excel是新闻报道中的常客，在我撰写本书期间就有两则相关新闻冲上头条。第一则和HUGO 基因命名委员会（HUGO Gene Nomenclature Committee）有关。为了让Excel不再将一部分人类基因解释为日期，这个委员会对它们进行了重命名。例如，为了让Excel不把MARCH1 基因当作1-Mar（3月 1日），委员会把它改成了MARCHF12。第二则新闻发生在英国，人们认为是Excel导致了16000 名患者没有被及时报告。出现问题的原因是检验结果以旧式Excel文件格式（.xls）保存，而这种格式最多只能保存大概 65000 行数据。这就意味着超出限制的那部分数据会被直接砍掉。这两则新闻体现了Excel在当今世界的重要性及其在表格软件中的优势地位，但最有名的“Excel事故”可能非“伦敦鲸”（London Whale）莫属。

2参见由James Vincent 所写的于2020年 8月 6日刊登在The Verge上的文章“Scientists rename human genes to stop Microsoft Excelfrom misreading them as dates”。

“伦敦鲸”是一位交易员的外号，他在交易时犯下的错误使得摩根大通公司在2012年蒙受高达 60亿美元的损失。这一重大失误的根源在于，一个用Excel实现的风险价值模型严重低估了其中一个投资组合的实际亏损风险。《摩根大通公司管理工作组关于2012年 CIO 损失的报告》（Report of JPMorgan Chase & Co. Management Task Force Regarding 2012 CIO Losses3，2013）提到：“这个模型需要不断把数据从一张工作表复制并粘贴到另一张工作表中来运作，并且必须手动完成。”除了操作上的问题，他还在运算中犯了一个逻辑错误：他除以了总和，而不是平均数。

3Wikipedia的脚注中给出了一篇相关报道的链接。

如果你还想看这类故事，可以访问由欧洲工作表风险兴趣小组（European Spreadsheet Risks Interest Group，EuSpRIG）维护的Horror Stories 网站。

为了不让你的公司也因为类似的事情上新闻，我们接下来了解一些最佳实践。这些原则可以让你更安全地使用Excel。

1.1.2 编程最佳实践

本节会介绍最重要的编程最佳实践，涉及关注点分离、DRY 原则、测试和版本控制。我们会看到，坚守这些最佳实践可以让Python和Excel用起来更流畅。

  1. 关注点分离

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

    • 表示层（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 原则。

  2. 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 可以很方便地分发代码。不过在那之前，先来学习测试，它是稳定软件开发大厦的一块基石。

  3. 测试

    如果你让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文件上。

  4. 版本控制

    专业程序员还有一个特点就是会使用版本控制（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。

4这些术语来自Microsoft Application Architecture Guide, 2nd Edition。

1.1.3 现代Excel

Excel的“现代”始于Excel2007，在这一版本中引入了功能区菜单和新的文件格式（xls 之后的xlsx）。然而Excel社区一般用“现代Excel”来指代在Excel2010中新增的工具，其中最重要的就是Power Query和Power Pivot。它们让你可以连接到外部数据源，分析那些一张工作表装不下的数据。由于它们的功能和第5章将介绍的pandas 有重合的部分，因此本节第一部分会先对它们进行简要介绍。第二部分会讲到Power BI，可以将其视作一个将Power Query和Power Pivot 相结合的独立的商业智能应用程序，并且它还带有可视化功能以及内置的Python 支持！

  1. Power Query和Power Pivot

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

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

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

  2. 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 来支持它呢？下一节会给出一些答案。

上一章目录下一章