从笔记本到工作簿：使用 Python in Excel 提升您的数据科学技能

使用 Excel 和 Python 创建有效数据报告的最常见方法通常需要将数据加载到 Jupyter 笔记本中（例如使用 pandas），并将笔记本本身转换为报告以与利益相关者共享。但是，如果我们不再需要在外部 Jupyter 笔记本中工作，而是可以直接在 Excel 工作簿中使用 Python 呢？

在这篇文章中，我们将探讨 **Python in Excel** 如何使 Excel 用户能够直接在 Excel 电子表格中使用 Python 和 Anaconda Distribution 。

**注意：** 要重现这篇文章中的示例，请 安装Python in Excel 试用版 。

介绍

数据报告对于与利益相关者沟通见解至关重要。Microsoft Excel 是一款功能强大的工具，使用户能够处理和可视化大量数据。由于拥有大量内置功能，例如数据透视表、数据表和各种统计函数，Excel 被广泛用于金融、会计和销售等许多行业，以便快速轻松地生成有效的数据报告。但是，数据处理永远不是一条“直线”，因为我们必须对原始数据集进行预处理（例如清理、过滤、分组、转换），才能提取有意义的见解。我们通常会将整个工作负载迁移到 Python 代码中（利用其丰富的用于数据科学的库和工具生态系统），现在我们可以使用 Python 及其强大的库（如 pandas）而无需离开 Excel 。

Python 直接集成到 Excel 中，为工作簿增强了大量用于科学计算和数据科学的软件包（例如 NumPy、pandas、SciPy、Matplotlib、scikit-learn）。

在这篇博文中，我将演示如何在 Excel 工作簿中使用新的 Python 功能来进行有效的数据报告。

从 Jupyter 笔记本到 Excel 工作簿

Excel 中的主要创新是新的 =PY 单元格，它立即在工作簿中启用 Python。您可以通过选择单元格并键入“=PY”或使用键盘快捷键在 Excel 工作簿中创建新的 Python 单元格

Ctrl+Shift+Alt+P

为了强调我们在新的 Python 单元格中工作，单元格及其编辑器的左边界将立即变为 **深绿色**（见下文）。

如您所见，扩展程序还集成到公式窗格中，以便快速创建新的单元格或查看现有单元格。

**注意：** 目前，Python in Excel 功能仅限 Windows 用户使用。将来会扩展到 Mac 用户；敬请关注更新。

它是如何工作的？

首先要明确的是，**您不需要** 在计算机上安装 Python 才能在 Excel 工作簿中运行 Python。所有执行都在 Microsoft Azure 上的沙箱环境中自动完成。因此，需要正常工作的互联网连接。由于存在沙箱执行环境，因此运行在单元格中的 Python 代码不允许访问互联网或本地文件系统。因此，以下读取数据的 Python 代码将无法运行

requests.get("https://url_to_my_fantastic_dataset")
# OR
pd.read_csv("C:\\Users\\Valerio\\Downloads\\my_fantastic_dataset.csv")
# OR
sklearn.dataset.fetch_california_housing()  # requires internet to download the data

新的 **Python in Excel** 扩展完全由 **Anaconda** 提供支持：Anaconda Distribution 自动在后端使用，因此您无需 担心环境，因为 Excel 中已提供所有主要的数据科学和机器学习软件包：NumPy、SciPy、Matplotlib、Seaborn、statsmodels 和 scikit-learn，仅举几例最流行的软件包，以及下一节工作示例中将使用的一些软件包。

电子表格与笔记本

从外部 Jupyter 笔记本过渡到 Excel 工作簿时，需要牢记两种格式之间的异同，因为它们会直接影响代码组织方式和代码执行方式。

笔记本和电子表格都是 **面向单元格的** 格式；内容被组织成多个连续单元格。但是，虽然电子表格模型是二维的（即行和列），但笔记本只是在一维（即单个列）上展开。

**Excel** 工作簿可以包含一个或多个电子表格——因此底层数据模型实际上是三维的（3D）（电子表格、行、列）。Excel 中 Python 代码的执行将遵循相同的 3D 模型，从第一个电子表格的左上角单元格开始，并以**行优先**的方式继续。

在 Excel 中编写 Python 代码时，请牢记此模型。例如，如果您在位于 C10 的 Python 单元格中导入软件包，由于所有单元格共享一个**全局命名空间**——类似于 Jupyter 笔记本单元格——该软件包也将在位于 C11 及以后（在当前电子表格中）的任何其他 Python 单元格中可用，以及随后出现的任何其他电子表格。但是，该软件包在 B1 中将不可用，因为该软件包位于导入该软件包的位置之前的一行。

尽管底层数据模型存在这些差异，但在 Excel 工作簿中编写 Python 代码时，遵循的开发最佳实践与 Jupyter 笔记本中的单元格相同：选择代码片段，而不是难以阅读和维护的较长代码列表。

在工作簿中编写 Python 代码而不是在 Jupyter 笔记本中编写 Python 代码时的**主要区别** 是，单元格中包含的 Python 代码会自动“隐藏”，并且每个单元格的内容会自动被代码产生的任何返回值替换。如果代码没有直接返回值，则默认情况下会将“None”显示为单元格内容。

如果还不清楚，请不要担心。我们将在稍后通过示例深入研究单元格输出。

示例：使用 Python in Excel 分析财务数据

现在让我们探讨 **Python in Excel** 如何有效地工作和应用的具体示例。

您可以直接下载此 财务示例 Excel 工作簿 。下载并打开 Excel 后，我们要做的第一件事是将 Sheet1 电子表格重命名为“Financial_Data”，然后添加一个名为“Report”的新电子表格。

为电子表格指定有意义的名称通常是一个好习惯，这也会使我们在本节中稍后更容易引用它们。

在新的“Report”电子表格中，让我们添加第一行 Python 代码，在左上角单元格（A1）中写入以下代码

import pandas as pd

# This will be automatically converted into a pandas.DataFrame
df = xl("'Financial_Data'!A1:P701", headers=True)
# Make sure that the date column is interpreted as datetime
df["Date"] = pd.to_datetime(df["Date"])
# return value of the cell
df

使用 Ctrl+Enter 提交 Python 代码并触发执行。

执行后，这将在工作簿中生成数据的 pandas.DataFrame 表示形式。由于我们使用了 **新的** xl() 函数，因此我们能够选择 Excel 单元格的 **范围**，这些单元格在移植到 Python 运行时环境中时会自动转换为 pandas.DataFrame。

现在让我们让事情更有趣一些。与其复制所有数据，不如利用 pandas 的过滤和分组功能生成数据的聚合版本。

现在让我们返回到同一个 A1 单元格，并将内容修改为以下代码：

import pandas as pd

# This will be automatically converted into a pandas.DataFrame
df = xl("Financial_Data!A1:P701", headers=True)
# Make sure that the date column is interpreted as datetime
df["Date"] = pd.to_datetime(df["Date"])

# Aggregate by 'Country' and 'Segment' and then sum all the values in the 'Sales' column
country_segments = df.groupby(["Country", "Segment"], as_index=False)
                    .agg({"Sales": "sum"})

# return value of the cell (grouped data)
country_segments

如果我们尝试运行此代码，我们会 **得到一个错误**！⚠️

这是一个很好的机会来了解 Python 代码中出现代码错误时的情况。

发生了什么事？

显然，pandas 在 df 数据帧中找不到名为 Sales 的列。这是因为原始列名（即标题）包含空格：“  Sales  ” 而不是“Sales”)。因此，我们应该从列名中删除此格式，以便以编程方式引用它们！记住吗？数据处理永远不是一条“直线”！

让我们通过添加一个解决方法来修复列名，重写我们的代码片段。

import pandas as pd

# This will be automatically converted into a pandas.DataFrame
df = xl("Financial_Data!A1:P701", headers=True)
# Make sure that the date column is interpreted as datetime
df["Date"] = pd.to_datetime(df["Date"])

# Fix column names by stripping white space
df.columns = [col.strip() for col in df.columns]

# groupby
country_segments = df.groupby(["Country", "Segment"], as_index=False)
                    .agg({"Sales": "sum"})

# return value of the cell
country_segments

要提交并运行代码，请在单元格中按 Ctrl+Enter。您应该获得以下结果（见下图）。

在生成输出后，我以粗体形式标记了生成的标题，即 **Sales**、**Country** 和 **Segment**，以便于阅读。事实上，始终可以更改电子表格中单元格的格式以提高可读性。同样，我们可以将新生成的 Sales 列中单元格的格式设置为货币。

现在我们有了汇总数据，让我们使用 Seaborn 生成一个汇总销售额的图表。具体来说，我们将生成一个 catplot，因为我们的聚合轴是分类的（即国家和细分）。

让我们移到 D1 单元格并输入 =PY。但是，这次让我们通过单击单元格左侧下拉菜单，将其输出标记为Python 对象。

Python 单元格的默认输出设置为“Excel 值”，该值在下拉菜单中用此图标标记：。当我们将单元格的输出设置为“Python 对象”时，菜单中的图标将变为：。

在显示单元格中的图像时，默认的“Excel 值”输出将直接在单元格中显示图像。但是，如果我们决定将图像分离出单元格，所有用于生成图像的（Python）代码都将丢失。

这通常不是问题，但为了本文的目的，我们希望将 Python 代码保留在单元格中并可视化生成的图表。因此，将单元格的输出设置为Python 对象是一种更灵活的解决方案。

现在，让我们将以下 Python 代码添加到 D1 中新创建的 Python 单元格中。

from matplotlib import pyplot as plt
import seaborn as sns

fig = plt.figure()
plot = sns.catplot(data=country_segments, x="Country", y="Sales", hue="Segment", kind="bar")
# FIX xticks labels orientation to improve readability
for axes in plot.axes.flat:
    _ = axes.set_xticklabels(axes.get_xticklabels(), rotation=45)
# The figure object will be returned as Output
fig  

当我们提交并运行代码时，您应该在工作簿中看到以下内容。

通过这种集成，Excel 现在内置支持在工作簿单元格中显示的 Python 对象。图中 D1 单元格中显示的图像对象直接引用底层 Python 对象。

最后，要可视化生成的图表，我们可以从上下文菜单中选择图像（如下图所示），或者在 E1 单元格中输入以下指令。

=D1.Image

这将显示生成的分类图表，然后可以将其放置在单元格上以进行进一步自定义。

结论

在这篇博文中，我们探讨了新的集成如何使 Excel 用户能够直接在 Excel 工作簿中使用 Python，而无需将分析移动到 Jupyter Notebook。Excel 中的 Python 由Anaconda Distribution提供支持，该发行版可立即访问丰富的用于数据科学和机器学习的 Python 包生态系统。此扩展目前仅适用于 Windows 用户，并且仍处于 Beta 阶段。因此，可能会出现错误，并且某些内容可能仍然会发生变化和改进。尽管如此，这项技术所释放的潜力确实是前所未有的，它为在 Excel 中执行数据分析提供了一种全新的方式。

您可以通过此链接查看在本博文中开发的工作簿。

免责声明：截至本文发布之日，Microsoft Excel 中的 Python 集成处于 Beta 测试阶段。功能和功能可能会发生变化。 如果您在此页面上发现错误，请随时联系我们。

简历

Valerio Maggio 是 Anaconda 的研究员和数据科学家倡导者。他还是开源贡献者，也是 Python 社区的活跃成员。在过去的 12 年中，他为许多国际会议和社区聚会做出了贡献并自愿参加，例如 PyCon Italy、PyData、EuroPython 和 EuroSciPy。