智能分析：ChatGPT+Excel+Python超强组合玩转数据分析

Python+Copilot/ChatGPT

Excel未来已来

话说天下大势，合久必分，分久必合。

于数据分析而言，近几年是实实在在的多事之秋。

首先是顶流Python高举卷王之王的大旗向传统王者VBA抢班夺权，pandas, xlwings、OpenPyXL和Matplotlib等第三方包已经具备VBA和Power Query的几乎所有功能。

然后是2023年初ChatGPT席卷而来，语言大模型开始接管一切，改变一切。

人们从质疑、嘲笑、观望、到骂骂咧咧地开始接受。

8月，微软开启Excel内置Python的Beta测试，Python名正言顺地嵌入Excel，与VBA并驾齐驱。

11月1日，微软经过7个多月的测试后，正式发布Microsoft 365 Copilot。

什么是Copilot？

我的理解是ChatGPT+微软独家秘制语料库！

10月17日，国内金山软件与科大讯飞宣布合作，要在WPS中做中国版的Copilot。

智能分析的理想已经照进现实，真实并且热烈！

01

提示词：追求生成的代码不需修改

“ChatGPT何许人也，仿佛一夜之间，如此汹汹？”

“不必惊慌，无它，一聊天机器人尔！专家讲的自然语言、神经网络、深度学习和大模型，咱们不用理会，只需要向它提问题，等它回答完以后，如此，这般……即可，操作简单而不失粗暴。”

向ChatGPT提的问题美其名曰提示词。

提示词的好坏，直接关系到答案的好坏。所以，提示词里有黄金，提示词里颜如玉。君不闻新晋职业提示词工程师者，年薪百万乎？

ChatGPT可以帮我们写文章、写文案、写诗、画画，才华横溢。但是作为数据分析师，我们让它写代码，并且殷切希望写的代码不需要修改！

如果代码需要修改，对于不懂编程的同学代码是毫无意义的，因为不会改；对于略懂编程的同学，可能调试代码找错花的时间比自己编写用得还多，不划算。

记住，我们追求的目标是让ChatGPT生成的代码不需要修改。

下面结合一个实例介绍用ChatGPT生成pandas代码分析数据的过程。

图1所示工作表中A-C列是不同班学生的考试成绩，要求计算各班学生考试成绩的平均分。这是一个分组统计问题。

图1

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

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

ChatGPT软件的外观很简单，类似于图2中的样子。用户只需要在底下的文本框中向ChatGPT提问即可，答案会显示在文本框上方。使用上面的提示词向ChatGPT提问，返回解决本示例问题的pandas代码，如图2中所示。

图2

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

操作的整个过程不需要用户懂编程，编程的工作ChatGPT帮你做了，真正实现了办公自动化。

02

ChatGPT+知识体系

分治思想+面向问题重构+提示词模板库

ChatGPT以问答的方式解决问题，所以，要想用ChatGPT系统地解决数据分析相关的所有问题，首先有必要将数据分析相关的知识体系按照面向问题的方式进行重构，得到一个由数据分析典型问题组成的问题库。

然后针对每个典型问题，结合有代表性的实例经过反复试验得到ChatGPT提示词，并根据该提示词提炼出解决此一类问题的提示词模板。

所以，最终得到一个能解决数据分析典型问题的提示词模板库。

有了提示词模板库，当我们遇到数据分析问题时，首先用分治的思想将复杂问题按顺序分解为简单单一的问题。

然后与模板库中的典型问题进行比对，根据对应的提示词模板组织新的提示词用ChatGPT生成代码进行解决。

少数问题需要具有编程思维才能写出有效的提示词，比如要详细描述算法、需要明确指定工具函数等，就需要让编程高手来写提示词并提炼出模板。

提示词模板库里面有很多解决问题的钥匙，只要找到对应的钥匙，就能解决问题。可以建立一个公共的模板库，也可以建立一个属于你自己的模板库。不断丰富这个模板库，解决问题的能力会越来越大。

图3是一个典型的提示词模板库。每个目录中包含对应典型问题的Excel数据，提示词模板和可以运行的代码。

图3

03

ChatGPT数据分析

编程语言和工具的选择

Excel数据分析目前可用的编程语言主要有VBA和Python，其中Python又有pandas, xlwings和OpenPyXL等不同的包。Python的这几个包既可以单独完成任务，又可以优化组合，更高效地完成分析任务。

经过大量测试，目前可以肯定的是，ChatGPT生成的Python代码的成功率要大于生成的VBA代码的成功率，写提示词的难度也更小。成功率指的是代码运行不出错，并且正确解决问题。

比如下面是用代码实现中国式排名的例子，同样的数据，生成Python pandas代码的提示词为：

你是pandas专家，文件路径为：D:/Samples/07 数据排名/中国式排名/短跑成绩排名.xlsx。该Excel文件的第一个工作表中A1:B8为给定数据，A-B列分别为“姓名”和“短跑成绩(秒)”数据。用pandas导入Excel文件的数据，第1行为索引行，引擎为"openpyxl"，请根据短跑成绩进行排名，用时越少排名越靠前。排名为整数，采用中国式排名，名次相同时取最小名次，数据添加在最后一列。根据排名对行数据进行升序排列。给代码添加注释。

生成VBA代码的提示词为：

你是Excel VBA专家，第一个工作表中A1:B8为给定数据，A-B列分别为“姓名”和“短跑成绩(秒)”数据，第1行为变量名称。遍历第2行到末行，首先按照短跑成绩对各行数据进行升序排列，得到各行对应的序号。排序后的姓名和短跑成绩数据放在第E列和第F列，变量名为“姓名”和“短跑成绩(秒)”，数据从第2行开始往下放。排序后行数据处于第几行序号就是几。比如，假设排序后短跑成绩(10 11 11 12 13 13 13 15)对应的序号为(1 2 3 4 5 6 7 8)，对于短跑成绩相同的情况，比如成绩都为11的有两个，它们对应的序号为2和3，采用中国式排名，现在序号都取2，取最小值；成绩都为13的有三个，它们对应的序号为5,6和7，都取最小值5。处理完后，最终序号添加在第G列，变量名为“排名”。给代码添加注释。

为什么会有这么明显的差异呢？

笔者考虑主要有两点原因。

• 第一个原因是ChatGPT是用Python写的，大量使用了Python深度学习的包，而这些包跟pandas包是一脉相承的；
• 第二个原因是pandas包已经封装了很多算法，比如本例中的排名算法，pandas包中直接调用rank方法就可解决，而VBA中需要向ChatGPT详细描述算法，相当于写一个中国式排名算法的伪代码。

所以，在VBA和Python之间，我们选择Python。

然后是Python的3个常用包，即pandas, xlwings和OpenPyXL。

工作中常常用pandas包处理数据，用xlwings包和OpenPyXL包做报表，与Excel工作表打交道。

大量实战标明，ChatGPT生成的pandas代码成功率很高，大多数情况下代码不需要修改就可以解决问题。而另外两个包用ChatGPT生成的代码运行时常常出错。

所以，综上，用ChatGPT做数据分析，优先选择Python的pandas包。

04

Excel内置Python：全面支持pandas包

2023年七夕节这天，微软发布了Excel内置Python Beta测试版。Excel内置Python在微软云上运行，不需要用户在计算机上安装Python和第三方包，从而保证大家都使用相同的软件，没有版本问题。

Excel内置Python可以在Excel中以公式的形式使用Python，并且全面支持pandas包。图4演示了在Excel内置Python中用pandas包实现数据分列的效果，使用很方便。

图4

实际上，我们已经基本完成关于Excel内置Python的稿件的撰写，不出意外，大家很快就可以看到图书，敬请关注。

05

Excel内置Python

使用ChatGPT生成的pandas代码

图1对应示例用ChatGPT生成的pandas代码完成了数据分析的任务，这里试图将该代码用于Excel内置Python。要将ChatGPT生成的pandas代码用于Excel内置Python，需要将代码适当作一些修改。主要是数据输入和输出部分需要修改。

在图5所示工作表中，在计算机连接互联网的条件下，在E2单元格中输入“=PY(”，进入Python模式，在公式栏输入下面根据ChatGPT生成的代码修改后得到的代码：

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

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

图5

所以， ChatGPT生成的pandas代码稍作修改就可以用于Excel内置Python，解决数据分析问题。

06

pandas+xlwings/OpenPyXL，黄金组合

对比这3个包，它们有各自的优点和缺点，谁也不能完全代替谁。

Pandas包处理数据的速度很快，代码简洁，支持大型数据，但是它不支持Excel对象模型，不能在pandas中直接读取Excel工作表指定单元格区域中的数据或者将数据写入指定单元格区域。

xlwings包对Excel对象模型支持最彻底，VBA能做的它基本都能做，但是它依赖Excel，即使用xlwings时计算机上必须安装Excel软件。xlwings包只能处理传统中小型数据。

OpenPyXL包的最大的特点是可以不依赖Excel软件操作Excel文件，也就是说计算机上不安装Excel软件也可以正常使用，所以OpenPyXL包特别适用于做软件开发。。它的缺点是对Excel对象模型的支持不彻底，很多对象和功能没有。OpenPyXL包只能处理中小型数据。

所以，实际工作中常常将这几个包结合起来用。用pandas做数据处理，用OpenPyXl或xlwings进行与Excel对象有关的操作，如数据的读写、Excel单元格格式设置等。

用ChatGPT生成代码，用pandas包生成时效果最佳，代码几乎不需要修改。

用OpenPyXL包生成的效果次之，出错较多，而且该包的文档写得比较马虎。

用xlwings包生成的效果最差，一方面API使用方式沿用的是VBA的语法，另一方面，xlwings包的新语法和API用法两种语法常常会混合起来，容易出错。

所以，用ChatGPT生成后面两个包的代码时写提示词要切记耐心仔细，反复试验。

07

ChatGPT+知识体系：专业和业务是根本

最后要强调的是，不管技术如何发展，专业和业务知识始终是根本。

对于ChatGPT而言，专业和业务上有深刻认识，思路清晰，才能问出高质量的问题。

希望大家花功夫把基础打好，然后熟练使用ChatGPT等先进技术，如虎添翼。