EXCEL+Python搞定数据处理(第一部分：Python入门-第1章：为什么要用Python为Excel编程)

参考资料：

Excel+Python飞速搞定数据分析与处理，[瑞士] 费利克斯·朱姆斯坦 著，中国工信出版社、人民邮电出版社出版(“Python for Excel, by Felix Zumstein (O’Reilly). Copyright 2021 Zoomer Analytics LLC, 978-1-492-08100-5”)

将不定时持续更新！！！

本书共四个部分，该部分为第一部分，Python基础部分。

第一部分：Python入门

• 解释了Python为何适合与Excel搭配使用。
• 介绍了Anaconda Python发行版、Visual Studio Code和Jupyter笔记本等工具。
• 提供了足够的Python基础知识以帮助读者理解后续内容。

第二部分：pandas入门

• 介绍了pandas库及其在数据分析中的应用。
• 讲解了如何使用pandas和Jupyter笔记本替代Excel工作簿，以及pandas的优势。

第三部分：在Excel之外读写Excel文件

• 讲述了如何使用Python包（如pandas、OpenPyXL等）来操作Excel文件。
• 强调了无需安装Excel即可进行文件操作的优势，以及这些操作的跨平台兼容性。

第四部分：使用xlwings对Excel应用程序进行编程

• 介绍了如何使用Python和xlwings库来自动化Excel应用程序。
• 涉及了如何打开和操作Excel工作簿，以及构建交互式Excel工具。
• 讲解了如何在Python中编写用户定义函数（UDF）。

补充材料（如代码、练习等）可以在这里下载：https://github.com/fzumstein/python-for-excel

解压并复制到 C:\Users\<username>\python-for-excel 文件夹中

前言：

        作者在前言中提到了目标读者，大家可以对号入座一下：

1.  Excel 高级用户，“Excel 高级用户”每个月都会花几小时下载、清理、复制和粘贴大量数据到关键的工作表中。
2. 应对编程有基本的了解。如果你写过函数或 for 循环（无论是用哪种编程语言写的），并且明白整型和字符串是什么。无须任何针对 Python 的经验，会简要介绍我们要用到的工具，其中也包括对 Python 本身的介绍。
3. 如果你是 VBA 老手，那么书中经常出现的对比 Python 和 VBA 的内容可以帮助你避开一些常见的陷阱，从而快速上手。
4. 如果你是 Python 开发者，并且需要了解 Python 有哪些处理 Excel 程序和文件的方式，从而为满足商业用户的需求选择合适的软件包，那么本书也是值得一看的。

一、excel是一个编程语言

1.为什么要用Python为Excel编程

①自动化可以避免很多人为的错误，避免对重要数据的损坏

②excel与python均是编程语言，且python中的numpy等包可以进行数据运算

2.编程最佳实践

（1）关注点分离（模块化）

如下图的例子所示：（这是一个汇率转换的工具，在作者提供的github的xl文件可以获取到）

        Amount输入多少钱，Currency输入这是哪种货币，输入之后就可以得到对应美金的钱。

        这里，Amount和Currency就是表示层（与用户交互的部分），in USD就是业务层（应用程序的目的执行，有语法，目的是查找Currency对应货币种类的汇率值），而右侧的两列就是数据层，用来访问数据，D4的VLOOKUP就是负责这项任务。VLOOKUP的语法结构如下：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

各参数解释如下：

• lookup_value：这是要在表格第一列中查找的值。它可以是数值、文本字符串、单元格引用或函数返回值。
• table_array：这是包含数据的单元格区域，其中第一列是要查找的值，其他列包含要返回的结果。
• col_index_num：这是返回值所在的列号。例如，如果要返回第二列的值，则此参数为2。
• [range_lookup]：这是一个可选参数，用于指定查找方式。如果设置为FALSE或0，则表示精确匹配；如果设置为TRUE或1，则表示近似匹配。

（2）DRY原则（不要自我重复）

1. DRY原则的含义：
        DRY代表“不要自我重复”（Don’t Repeat Yourself），意味着避免在程序中重复相同的代码。减少重复不仅让代码更加简洁，也能减少错误的发生，并让代码更加容易维护。如果代码重复，那么任何修改都需要多次进行，容易漏掉某些地方，导致维护困难。

2. Excel中的挑战：
        在Excel中，若将业务逻辑放入单元格公式里，那么就无法方便地重用这些公式。比如，当需要处理多个Excel工作簿时，开发者往往不得不复制含有公式的工作簿。这样，公式重复出现在多个工作簿中，违反了DRY原则。

3. VBA中的代码重用：
        在VBA编程中，可以通过编写函数来避免重复代码。通过将常用的代码块封装成函数，可以在多个地方调用这些函数，确保代码的一致性和可维护性。比如，某些常用的计算或逻辑可以封装成函数，在不同的工作簿中复用，而不需要每次都写一遍相同的代码。

4. 插件的限制：
        虽然可以通过插件来共享VBA代码，但VBA插件的分发和更新机制不完善。而且，Microsoft的Excel插件商店目前只支持JavaScript插件，这使得VBA开发者依然无法像其他语言那样方便地共享和复用代码。结果就是，VBA开发者在使用这些代码时，依然需要大量复制和粘贴，不能有效避免重复。

5. 代码可靠性问题：
        举例来说，假设需要使用Excel中的三次样条插值函数来处理一些固定收益交易的计算，开发者可能从网上找到现成的VBA代码。但这些代码往往没有文档，缺乏足够的测试，可能无法处理一些边界条件或特殊情况，导致代码的可靠性和准确性无法得到保证。如果要在实际高风险的任务中使用这些代码，可能会带来隐患。

（3）测试

        Excel开发者在测试工作簿时往往采用随意的方法，这可能导致一些隐蔽问题被忽视，如“写死”的值（一个静态数值）覆盖公式或忘记调整隐藏列中的公式，从而带来风险。

        通常，程序员使用“单元测试”来测试它们的代码。单元测试是一种编程实践，它涉及编写测试代码来验证软件的各个部分（通常是函数或方法）是否按预期工作。单元测试的主要目的是确保每个组件（或“单元”）在隔离状态下能够正确执行其特定的功能。（就是先用一个已知的过程来试一下可否得到正确的结果）

        比如上面的汇率转换问题，我们可以先用一个已知的过程来对整个应用进行测试，如已知100欧元对应105美元，那么就输入100、欧元，看最后是否输出了105美元。       

        假设你有一个计算加法的函数（pytest是Python的单元测试工具）：

def add(a, b): return a + b

        你要写一个单元测试来验证这个函数是否正确。测试代码如下：

def test_add(): 
assert add(2, 3) == 5 # 2 + 3 = 5, 测试通过 
assert add(-1, 1) == 0 # -1 + 1 = 0, 测试通过 
assert add(0, 0) == 0 # 0 + 0 = 0, 测试通过

        单元测试的详细内容详见作者博客：

Microsoft Excel 的单元测试https://www.xlwings.org/blog/unittests-for-microsoft-excel        但是，Excel本身并没有内置的单元测试框架。例如，VBA并没有像JUnit、pytest这样的自动化测试工具，缺乏直接支持单元测试的功能。但是这并不意味着我们一定无法进行单元测试，比如说我们最笨的方法那就是手敲一个测试代码了。

        比如说举个小栗子给大家看一眼

Sub TestAddFunction()
    ' 测试加法函数
    If Add(2, 3) <> 5 Then
        MsgBox "Test failed for Add(2, 3)"
    ElseIf Add(-1, 1) <> 0 Then
        MsgBox "Test failed for Add(-1, 1)"
    ElseIf Add(0, 0) <> 0 Then
        MsgBox "Test failed for Add(0, 0)"
    Else
        MsgBox "All tests passed!"
    End If
End Sub

' 示例加法函数
Function Add(a As Double, b As Double) As Double
    Add = a + b
End Function

  TestAddFunction是测试代码，检查Add函数的不同输入和输出是否符合预期。如果结果不符合，弹出一个提示框。比如说，如果我们2+3的结果不是5就会出错。当然，如果说要是可以把excel和Python结合起来就更好了。我们可以借助xlwings工具，来允许我们在Excel中使用Python的库，可以用来编写单元测试。

（4）版本控制

        我之前呢也接触过git工具，类比一下word当中的一些功能就相当于下面的情况：

• Git 仓库：是一个包含所有版本历史的文档，它记录了文件从最初到当前的每一个变动（时间、作者、在提交信息（commit message）中描述的更改目的；在 diff 视图（其中新代码以绿色高亮显示，删掉的代码以红色高亮显示）中展示的更改细节），类似于 Word 中的修订历史（有点像老师利用审阅工具的批注修改的论文一样，修改的地方会用红色划掉但是不会消失）。在git这个工具箱里面有一些工具，如clone等。

• Git Clone：是对这个文档的完整复制，它将原始文档及其所有修订历史复制到你的计算机上，允许你在这个副本上独立工作，而不会直接修改原始文档。

        但是Git由于其复杂性并没有在Excel界产生一些波澜...这本书作者所在的公司就选用了xltrail。它虽然基于Git，但是相比于Git，它知道如何处理Excel文件。当然除此之外，还可以把业务逻辑转移到Python里面，自然可以利用Git来进行版本控制方法。

3.现代Excel

        和 Python 一类的传统编程语言相比，在 Excel 中进行上节中的实践很难。为了实现Excel的现代化，微软也做了一些尝试（Excel 2010）：Power Query 和 Power Pivot。

（1）Power Query 和 Power Pivot

        Power Query 可以导入和整理数据，包括 Excel 工作簿、CSV 文件、SQL 数据库，等等。在我的日常学习工作中经常将ArcGIS导出的如文件数据库、csv或者txt文件导入excel中进行处理。

        Power Pivot 是对经过 Power Query 清理的数据进行 深入分析（它支持使用 DAX（数据分析表达式） 语言来创建计算列、度量值和聚合函数，进一步扩展了分析的能力。）、建模（Power Pivot 让你能够将多个数据表进行连接，并建立起关系（类似于数据库中的关系模型）。这使得你可以在不同数据表之间进行分析，而不需要将所有数据放在一个单一的表中。） 和 展示 的工具，它能够创建复杂的报表和分析模型，并进行数据透视和高级计算。 

（2）Power BI

        Power BI自 2018 年起就支持Python 脚本了，其应用的作用是：在仪表板可视化巨大数据集的数据。Power BI 内部集成了 Power Query，因此你可以使用 Power Query 直接在 Power BI 中获取和清理数据。它负责从各种数据源导入数据并进行必要的转换和预处理。在 Power BI 中，Power Query 是获取数据的第一步。Power BI 也集成了 Power Pivot 的功能，用于进行数据建模、关系定义和创建计算列。通过 Power BI，用户可以像在 Power Pivot 中一样，使用 DAX 公式来创建度量值、计算列和复杂的数据模型。

二、用在Excel上的Python

        Excel 的主要功能是存储数据、分析数据和可视化数据。Python 在科学计算方面极其强大，天生就适合搭配 Excel 工作。此外，Python相比于其他编程语言也更加的易学。

1.可读性和可维护性

        在Python中，缩进不仅仅是代码格式化的最佳实践，它是语言语法的一部分。这意味着正确的缩进对于程序的正确运行是必需的。Python使用缩进来组织代码块，比如循环、条件判断、函数等结构体内的代码行。通过缩进，Python能够识别哪些代码行属于特定的代码块。

（1）为什么Python采用缩进

1. 提高可读性：强制缩进使得代码结构清晰，易于阅读和理解。这有助于开发者更快地理解代码逻辑，特别是在团队协作环境中。
2. 减少语法错误：与其他语言中常见的花括号 {} 不同，缩进可以减少因忘记关闭括号而导致的语法错误。
3. 统一编码风格：由于缩进是语法要求，因此所有Python代码在缩进风格上保持一致，这有助于维护和共享代码。

（2）如何使用缩进

        在Python中，通常使用4个空格来表示一级缩进。例如：

if x > 0:
    print("x is positive")
    if y > 0:
        print("y is also positive")
else:
    print("x is not positive")

        在这个例子中，print("x is positive") 和内部的 if y > 0: 都比 if x > 0: 多了一级缩进，表明它们属于 if x > 0: 的代码块。而 print("y is also positive") 比 if y > 0: 多了一级缩进，表明它属于 if y > 0: 的代码块。最后，else: 与 if x > 0: 对齐，表明它是 if x > 0: 的对立面。

（3）其他语言的对比

• C/C++/Java/JavaScript：这些语言通常使用花括号 {} 来定义代码块，缩进虽然有助于阅读，但不是必须的。
• VBA：使用 End If、End For 等关键字来结束代码块，同样缩进不是语法要求。

2.标准库和包管理器 

        Python 通过标准库提供了丰富的内置工具，这就是大家所说的“自带电池”（batteries included）。这些标准库涵盖了从文件操作、数据处理、网络通信到多线程等方方面面的功能。例如，Python 标准库中的 zipfile 可以用来解压 ZIP 文件，csv 用来读取和写入 CSV 文件，requests 用来从互联网上获取数据等。这些工具的最大优势是：它们是 Python 自带的，无需额外安装，通常你只需几行代码就能完成常见任务。

        相比之下，如果你想在像 VBA 这样的语言中实现类似的功能，通常需要编写更多的代码，或者依赖外部插件。而且，许多 VBA 解决方案通常局限于 Windows 环境，无法跨平台工作。而 Python 的标准库则具有跨平台的优势，能够在不同操作系统（如 Windows、macOS、Linux）中使用同样的代码。

        尽管 Python 标准库已经提供了很多强大的功能，但有些功能的实现可能比较复杂，或者标准库中的实现效率不高。这时，PyPI（Python Package Index）就发挥了重要作用。

        PyPI 是一个巨大且开放的仓库，任何人都可以向其上传和分享自己的 Python 包。通过 PyPI，Python 社区可以共享各种开源库，扩展 Python 的功能。你可以通过安装这些第三方包来实现更高效、更灵活的功能。通过 Python 的包管理工具 pip（包管理器），你可以轻松安装第三方库。对于 Excel 用户来说，最有趣的当然还是用于科学计算的包。

作者补充：什么是开源？

        开源软件意味着源代码是公开的，任何人都可以查看、修改并参与维护。在 Python 的生态系统中，绝大多数的第三方包是开源的，意味着你可以免费使用、修改这些工具。Python 作为一门开源语言，它的标准库和大多数第三方库都有丰富的文档和社区支持，开发者可以在社区中贡献自己的代码、修复 bug，或者提出改进意见。

        例如，pandas、NumPy、SciPy 等科学计算工具包，最初是由志愿者开发的，但现在已经由一些非营利性组织（如 NumFOCUS）和企业赞助，得到了专业的维护和持续更新。通过开源许可证，这些包得以在全球范围内使用，许多企业和个人都在受益。

3.科学计算

        Python 的成功主要源于其作为一门 通用编程语言 的诞生，并且其科学计算能力是通过 第三方包 的形式逐步增强的。这种灵活性和扩展性使得 Python 成为了一种极为强大的工具，广泛应用于不同领域，尤其是数据科学和 Web 开发。使用单一的编程语言可以避免 语言不兼容 带来的冲突问题，简化开发过程。当数据科学家和 Web 开发者使用 Python 同时工作时，研究成果可以无缝地转化为实际应用，开发周期得以缩短。更少的转换和重复性工作减少了实现时间和成本。

        相比于使用VBA语法，Python的可读性和语法的简洁性是一大优势。

        假设我们有以下数据：

import numpy as np
import pandas as pd

# 创建权重向量 w 作为 pandas DataFrame
w = pd.DataFrame({
    'weights': [0.5, 0.3, 0.2]
})

# 创建协方差矩阵 C 作为 NumPy 数组
C = np.array([
    [0.04, 0.01, 0.02],
    [0.01, 0.09, 0.03],
    [0.02, 0.03, 0.16]
])

# 计算方差
variance = w.T @ C @ w

# 打印结果
print(variance)

        输出结果将是一个标量，表示投资组合的方差。计算方差时的表达非常直给客观！（variance = w.T @ C @ w）但是如果使用VBA就会是这个样子：

variance = Application.MMult(Application.MMult(Application.Transpose(w), C), w)

        可见，其表达相比于Python非常的复杂！！！NumPy 和 pandas 背后使用了预编译的 Fortran代码和 C 代码，在处理大型矩阵时和 VBA 相比有巨大的性能提升。

4.现代语言特性

（1）VBA（Excel中的编程语言）很老，更新慢

• VBA 是 Excel 中的编程语言，自 Excel 97 开始就没有发生过太大的变化。虽然每次 Excel 更新时，VBA 会做一些小改进（比如 Excel 2016 加入了自动化 Power Query 的支持），但是 VBA 这门语言本身在过去 20 年里没有太多的新功能加入。
• 现代编程语言 （比如 Python ）有很多新特性，而 VBA 在这些方面相对落后。

（2）VBA 的错误处理很麻烦

• 在 VBA 中，如果你写的代码遇到错误（比如除数是0），你需要用一些 标签 和 跳转 来处理错误。这样写代码既复杂又不容易理解，被人戏称为“意大利面式代码”。举个例子：如果你想处理“除以零”的错误，你必须用 GoTo 语句让程序跳到一个 ErrorHandler 标签，然后再处理错误。代码看起来很繁琐，容易让人混淆。

（3） 现代语言的错误处理方式：try/except

• 而在 Python 等现代语言中，处理错误变得很简单，直接用 try/except 来处理：
• try 后面就是你要尝试运行的代码（多为错误代码），except 是当代码出错时该做什么。这样写代码简洁明了，错误处理也更加清晰。

（4） VBA 没有面向对象编程（OOP）

• VBA 也不支持 面向对象编程（OOP），这是一种让代码更加结构化和易于扩展的方法。在 OOP 中，你可以创建 类（Class）来定义事物的属性和行为，甚至可以继承别的类的功能。
• 而 Python 支持 OOP，代码更灵活、易于管理和扩展。

（5） 跨平台问题

• VBA 只适用于 Windows 上的 Excel，而 Python 可以在 Windows、Mac、Linux 等多个操作系统上使用。
• 如果你用 Python 写的代码，可以在不同的电脑和系统上运行，这就是所谓的 跨平台兼容性，而 VBA 就做不到这一点。

5.跨平台兼容性

        虽然 VBA 可以在 Windows 和 macOS 上运行，但由于一些特定功能依赖于 Windows 环境，VBA 很容易写出只能在 Windows 上执行的代码。举个例子：

• CreateObject：这段代码会调用 Windows 系统的文件系统对象（FileSystemObject），它只能在 Windows 中工作。如果你希望代码能够在 macOS 上运行，调用这种 Windows 特有的对象会导致错误。

       如果你想要确保你的 Excel 文件能够在 Windows 和 macOS 操作系统中都正常使用，需要特别注意是否使用了 ActiveX 控件。ActiveX 控件是一些可以嵌入到 Excel 表格中的交互式元素，比如按钮、下拉菜单、复选框等，这些控件在 Windows 上是可以正常使用的，但在 macOS 上是无法工作的。可以使用其他平台兼容的控件（比如 Excel 开发工具中的表单控件），这些控件在两种操作系统中都能正常运行。

三、结语

        通过以上的一些基本小知识，可以知道为什么要用Python与Excel相结合。Python 在很多方面具备 Excel 所不具备的优势，尤其是在数据处理、自动化和跨平台兼容性方面。通过将 Python 与 Excel 相结合，能够大大提高工作效率，减少错误，并扩展应用程序的能力。