初识Excel内置Python

Excel内置Python的编程环境

在计算机连接互联网的情况下，打开Microsoft 365中的Excel。打开“公式”功能区，找到“Python”功能块，如图1-1所示。功能块中的命令按钮提供对Excel内置Python的相关支持。

图1-1 “公式”功能区的“Python”功能块

Python功能块中主要有4个按钮。在工作表中单击要使用Excel内置Python的单元格，单击“插入Python”按钮，该单元格进入Python模式，如图1-2所示。进入Python模式后，就可以在公式栏输入Python代码了。

图1-2 单击“插入Python”按钮进入Python模式

单击“插入Python”按钮右下角的三角形箭头，单击弹出式菜单中的“示例”选项，工作表右侧会弹出一个包含多个Excel内置Python示例的面板。单击感兴趣的示例对应的“插入示例”按钮，在工作表中插入一个新的工作表并在该工作表中显示该示例。图1-3中显示了插入第一个示例时的显示效果。认真研究这些示例，可帮助你快速学习Excel内置Python的使用。

图1-3 使用Excel内置Python自带的示例

“重置”按钮的作用，是运行Python代码的时间超过60秒时，可以单击该按钮重置运行时。单击“诊断”按钮，工作表右侧会弹出一个显示诊断结果的面板。诊断结果，指的是代码运行出错时出错的原因。诊断结果中包含当前工作簿本次运行的全部历史诊断信息。图1-4所示工作表中，C2单元格进入Python模式后，输入Python代码：xl(“C4”)+10，表示返回值为C4单元格中的值加上10得到的和。因为C4单元格中的值为空，无法与给定的整型数据10直接计算，运行出错，给出图1-4所示的诊断信息。

图1-4 使用Excel内置Python出错时的诊断信息

单击“初始化”按钮，显示图1-5所示工作表右侧面板中的初始化信息。当前的初始化信息主要包括对Matplotlib包、NumPy包、pandas包、Seaborn包和statsmodels包等几个常用第三方包的引用，代码如下所示。

import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import seaborn as sns
import statsmodels as sm

初始化后，在公式栏中输入Python代码时就不需要写上面的导入代码了，直接用别名plt, np, pd, sns和sm即可。

默认时，还导入了excel包和warnings包，引用相关方法进行了标量数据和数组数据转换方面的设置。

目前初始化设置信息是只读的，无法修改，即无法自定义初始化信息。

图1-5 Excel内置Python初始化设置

PY函数

1.1.1小节介绍了在工作表中单击要使用Excel内置Python的单元格，单击“插入Python”按钮，可使该单元格进入Python模式。也可以使用PY函数使该单元格进入Python模式。本小节介绍后者。

如图1-6所示，要在单元格B2中使用Excel内置Python，用鼠标左键单击单元格B2，在公式栏输入公式“=PY(”，进入Python模式。

注意，后面介绍Excel内置Python的各章内容中经常会说：某单元格在Python模式下，指的就是先使用上面两种方式之一使该单元格进入Python模式。

图1-6 用PY函数进入Python模式

公式栏中输入的所有代码可以看作是PY函数第一个参数的值。代码运行的结果可以以Python对象和Excel值两种形式呈现。默认时以Python对象形式返回，如DataFrame, Series, List, Tuple等。可单击公式栏前面的卡片图标进行切换。关于返回值的查看，第4章会详细介绍。

举例：编写Python代码

下面结合一个简单的例子介绍怎样使用Excel内置Python。首先生成一个DataFrame，然后调用该对象的describe方法计算DataFrame对象中每列数据的统计量。

在图1-7所示工作表B2单元格中输入“=PY(”，进入Python模式。在Python模式下，在公式栏输入下面的代码：

df=pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]],columns=['A','B','C'])

df.describe()

单击Ctrl+Enter键，运行代码，B2单元格中返回一个DataFrame对象。单击该对象，B2右上角出现一个图标。单击该图标，在弹出式菜单中单击“arrayPreview”选项，展开DataFrame对象的内容，如图1-7所示，在C3::F10单元格区域中列出了各列数据的常见描述统计量，包括数据个数、均值、标准差、最小值，分位数等。

图1-7 Excel内置Python示例

可见，Excel内置Python处理数据时，数据的输入和输出都是在当前工作簿内部完成的，如果熟悉Python，操作很简便。

用ChatGPT或Copilot生成Python代码

使用ChatGPT或Copilot可以帮你自动生成Python代码，从而大大提高用Python处理数据的工作效率。Excel内置Python全面支持pandas包，《智能分析 ChatGPT+Excel+Python超强组合玩转数据分析》一书系统介绍了使用ChatGPT生成pandas代码处理数据的方法，可以参阅。本书第5章也有补充介绍。

下面结合一个实例介绍用ChatGPT生成pandas代码，并稍微修改后用于Excel内置Python分析数据的过程。请把重点放在了解操作过程，先不要求弄懂代码中的细节问题。

图1-8所示工作表中A-C列是不同班学生的考试成绩，要求计算各班学生考试成绩的平均分。这是一个分组统计问题，可以用pandas包的groupby函数对数据根据班级进行分组，然后对每组成绩分数用mean方法计算平均值。

图1-8 各班学生成绩

根据题意编写好ChatGPT的提示词，如下所示：

你是pandas专家，文件路径为：D:/Samples/ch03/02 各班学生成绩.xlsx。该Excel文件的第一个工作表中A-C列为给定数据。A-C列分别为“班级”、“姓名”和“成绩”。用pandas导入Excel文件的数据，引擎为"openpyxl"。请计算各班学生成绩的平均分，平均分保留1位小数。输出各班平均成绩，例如：一班 88.5。添加代码注释。

ChatGPT软件的外观很简单，类似于图1-9中的样子。用户只需要在底下的文本框中向ChatGPT提问即可，答案会显示在文本框上方。向ChatGPT提的问题就是提示词。提示词的好坏，直接关系到ChatGPT提供的答案的好坏。

使用上面的提示词向ChatGPT提问，返回解决本示例问题的pandas代码，如图1-9中所示。

图1-9 用ChatGPT生成代码

ChatGPT生成的代码为：

import pandas as pd
# 读取Excel文件数据
df = pd.read_excel('D:/Samples/ch01/各班学生成绩.xlsx', engine='openpyxl')
# 计算各班学生成绩的平均分
average_scores = df.groupby('班级')['成绩'].mean().round(1)
# 输出各班平均成绩
for index, value in average_scores.items():
    print(f'{index}班 {value}')

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存到D:/Samples/1.py。运行脚本，在IDLE Shell窗口输出各班的平均成绩。

>>> == RESTART: D:/Samples/1.py =
一班 90.6
三班 90.1
二班 88.9

在Python IDE(例如IDLE,PyCharm等)中使用Python代码，本书中称为外置Python的用法。所以用ChatGPT生成的pandas代码常常可以直接用于外置Python解决问题。

要将ChatGPT生成的pandas代码用于Excel内置Python，需要适当作一些修改。主要是数据输入和输出部分需要修改。第4章会详细介绍这部分内容。

在图1-10所示工作表中，E2单元格在Python模式下，在公式栏输入下面根据ChatGPT生成的代码修改后得到的代码：

df=xl("A1:C26",headers=True)
df.groupby('班级')['成绩'].mean().round(1)

在公式栏中单击Ctrl+Enter键，E2单元格返回一个Series对象，展开该对象，得到各班的平均成绩，如图1-10所示。

图1-10 使用Excel内置Python处理数据

所以代码中需要修改的地方，一个是Excel内置Python中用xl函数获取工作表中的数据，二是Excel内置Python不能将数据分析的结果输出到文件，只能输出到工作表中。pd别名在Excel内置Python中可直接使用。