ChatGPT 从入门到精通,联动Excel 的八种方法
近期 AI 工具大爆发, 一天之内,AI 绘图两大神器之一的 Midjourney 中文版可申请试用了,ChatGPT 开始实时联网 + 开放插件了。
这是基础教程第 5 篇,前期教程如下:
昨天介绍了如何用 ChatGPT 在办公室摸鱼,今天讲讲 CHATGPT 联动 Excel。
「一、概述:ChatGPT 如何联动 Excel?」
我问了 GPT,它回答说两种,主要是 python,VBA。
作为 Excel 达人的我总结了八种方法,现在毫无保留传授给你。
在开始之前,需要准备好一些工具。
首先,你需要进入国内可直达的免费 ChatGPT 镜像网站,可以边看边操作。
具体操作如下:首先,点击本文下方的“阅读原文”链接,访问 https://91aitools.cn ;其次,点击“ChatGPT 镜像(推荐)”按钮;在出现的界面中,选一个笑脸+没有飞机的图标即可。今天作者选的还是 ai-node.com。
「二、ChatGPT 联动 EXCEL 的 七种方式」
1、联动方式一:让 ChatGPT 制作一个 EXCEL 表。
适用范围:练手 + 生成非测算用表格,如计划表。
*提示语如下:
模拟一张excle表格,员工花名册,要包含20个员工。
*ChatGPT答复如下:
我们在谈话界面,选中表格,从左至右,从上到下选中,然后选择复制;打开 excel 软件,按 ctrl+a,然后 ctrl+p。得到一个表格如下,保留备用。
2.联动方式二:Chat Excel 网站
适用范围:表格不复杂,但是数量大,需要尽快处理的表格,如人事报表等
Chat Excel 网站 是清华大学做的一个在线 excel AI网站
网址:https://chatexcel.com (91tools 的 AI 测算中也能找到)
点击后,进入,界面如下:
点击右上角“上传文件”,把刚才生成的员工花名册传上去。
在提示框输入你要的过滤条件,如“把年纪在 30 岁以上的男同事选择出来”,然后点“执行”。
你也可以问:员工平均年龄是多少?平均工资?但要注意上传前把数据类型调整好,比如工资、工龄,调整为数字。
3、联动方式三:向 ChatGPT 咨询 Excle 使用方法
你在测算中,可以问 AI你碰到的任何问题,比如函数怎么用,一些专有名词,一些政策法规啥的。比如
(1)问函数怎么用
“模拟一张表格,实现 MATCH 和 INDEX 查找”
(2)问 IRR 是什么?
(3)问政策
辅助我们设定经济指标。
比如 “人防车位的面积计入规划车位么?北京地区度人防面积的规定?”
4、联动方式四:ChatPGT 生成带公式的 Excel
提示词:“请用 python 写一段代码,模拟数据,把地价-售价,对净利润的影响双敏感做成一个带公式的 excel 表,excel 字段用中文,最后实现可视化”
这里要三个工具结合使用 ChatGPT+python+excel。
ChatGPT 给出了如下代码:
# 在Python中搭建“地价&售价一销售净利润率”的双敏感性分析的步骤如下:
import pandas as pd
import numpy as np
# 随机生成1000条记录
np.random.seed(123)
data = pd.DataFrame({'地价': np.random.randint(80000, 200000, size=1000),
'售价': np.random.randint(120000, 300000, size=1000)})
# 将销售净利润率计算出来并添加到数据集中
data['销售净利润率'] = ((data['售价']-data['地价'])/data['售价'])*100
# 保存数据集到CSV文件
data.to_csv('数据集.csv', index=False)
print("数据集已保存到CSV文件。")
# 导入所需的Python库和数据集
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
# 设置matplotlib库使用Windows通用字体
plt.rcParams['font.sans-serif'] = ['SimHei']
data = pd.read_csv('数据集.csv')
# 对数据集进行清洗和处理,删除缺失值等
data.dropna(inplace=True)
data.reset_index(drop=True, inplace=True)
# 计算销售净利润率,并将其作为新的一列添加到数据集中
data['销售净利润率'] = ((data['售价']-data['地价'])/data['售价'])*100
# 绘制散点图,分别以地价、售价、销售净利润率为x、y、z轴,可视化数据集
fig = plt.figure(figsize=[10,8])
ax = fig.add_subplot(111, projection='3d')
xs = data['地价']
ys = data['售价']
zs = data['销售净利润率']
ax.scatter(xs, ys, zs, s=20, alpha=0.5)
ax.set_xlabel('地价')
ax.set_ylabel('售价')
ax.set_zlabel('销售净利润率')
plt.show()
# 构建网格点的二元组,求出所有可能的地价和售价组合的销售净利润率
x = np.arange(data['地价'].min(), data['地价'].max(), 10)
y = np.arange(data['售价'].min(), data['售价'].max(), 10)
X, Y = np.meshgrid(x, y)
Z = ((Y-X)/Y)*100
# 绘制等高线图,显示销售净利润率的变化趋势
fig, ax = plt.subplots()
cp = ax.contour(X, Y, Z, levels=np.arange(60,110,10), colors='k', linewidths=0.6)
plt.clabel(cp, cp.levels, inline=True, fontsize=8)
plt.show()
# 绘制3D表面图,直观展示地价、售价和销售净利润率的关系
fig = plt.figure(figsize=[10,8])
ax = fig.add_subplot(111, projection='3d')
ax.plot_surface(X, Y, Z, cmap='coolwarm')
ax.set_xlabel('地价')
ax.set_ylabel('售价')
ax.set_zlabel('销售净利润率')
plt.show()
# 这样,通过以上步骤,我们就可以搭建出一个“地价&售价一销售净利润率”的双敏感性分析,通过可视化数据,直观了解变量之间的关系,以便做出更加准确的商业决策。
复制这段代码到python编辑器中运行。
生成了excel表,还可视化的生成了散点图,如下:
5、联动方式五:ChatGPT 生成 VBA 代码
提示词:
“嗯,我们一步一步来,假设我的子表 1 已经勾稽好了所有的测算公式,B1 是我手动输入的土地单价,A1 是土地面积,C1 单元格是我的计算得到的土地总价(c1=b1\*a1),d1 是预计的一年后土地销售单价,是一个固定值,e1 是我计算得到的收益率 e1=(d1-b1)/b1,我在 B1 中输入不同的数据,e1 单元格会得到不同的收益率。我想实现如下功能:我在子表 1 的 f1 到 f10 中,输入不同的数值,h1 到 h10 会得到不同的收益率。如何用 vb 实现?请写出代码”
ChatGPT 生成如下代码:
Sub CalculateProfitRates()
' 定义工作表变量并设置为子表1
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") '替换为您的工作表名称
' 获取固定值和土地面积
Dim fixedValue1 As Double, fixedValue2 As Double, landArea As Double
fixedValue1 = ws.Range("D1").Value
fixedValue2 = ws.Range("E1").Value '根据实际情况替换为子表1中的其他单元格引用
landArea = ws.Range("A1").Value
' 使用For循环遍历F1到F10单元格
Dim i As Integer
For i = 1 To 10
' 从F列中获取土地单价值
Dim landUnitPrice As Double
landUnitPrice = ws.Range("F" & i).Value
' 如果土地单价值不为0,则进行计算
If landUnitPrice <> 0 Then
' 计算公式1
Dim result1 As Double
result1 = CalculateFormula1(landUnitPrice, fixedValue1, landArea)
' 计算公式2
Dim result2 As Double
result2 = CalculateFormula2(landUnitPrice, fixedValue2) '根据实际情况传递所需参数
' 将计算得到的结果写入H列和后续列的相应单元格
ws.Range("H" & i).Value = result1
ws.Range("I" & i).Value = result2
Else
' 如果土地单价值为0,则清空H列和后续列的相应单元格
ws.Range("H" & i).Value = ""
ws.Range("I" & i).Value = ""
End If
Next i
End Sub
' 定义计算公式1的函数
Function CalculateFormula1(landUnitPrice As Double, fixedValue1 As Double, landArea As Double) As Double
' 在此处编写计算公式1,使用landUnitPrice、fixedValue1和landArea等变量
CalculateFormula1 = '将此处替换为您的计算公式1'
End Function
' 定义计算公式2的函数
Function CalculateFormula2(landUnitPrice As Double, fixedValue2 As Double) As Double
' 在此处编写计算公式2,使用landUnitPrice和fixedValue2等变量
CalculateFormula2 = '将此处替换为您的计算公式2'
End Function
GPT对代码进行了注释,还给出了在excle 中使用 VBA 的方法:
6、联动方式六:在 Excel 中集成 ChatGPT
提示词:
请举一个详细的例子,如何将 ChatGPT 集成到 Python 代码中,附上详细的操作步骤和源代码。
GPT 在开始说“要在 Excel 中集成 ChatGPT,你需要做的是通过 API 来调用 GPT-3 模型”给了一个编程案例。
我提示了“用插件”并启动了 5 月 15 日 才开放的 plus 的联网功能,ChatGPG 给出提示如下:
正版office 的小伙伴可以试一试。
7、联动方式七:在Excel中创造宏函数。
先在Excel中启动宏,
然后在VBA的模块代码添加以下内容:
Function ChatGPT(prompt As String) As String
Dim url As String, apiKey As String
Dim response As Object, re As String
Dim midString As String
apiKey = "your_api_key"
url = "https://api.openai.com/v1/engines/text-davinci-003/completions"
Set response = CreateObject("MSXML2.XMLHTTP")
response.Open "POST", url, False
response.setRequestHeader "Content-Type", "application/json"
response.setRequestHeader "Authorization", "Bearer " + apiKey
response.Send "{""prompt"":""" & prompt & """,""max_tokens"":1024, ""temperature"":0.1}"
re = response.responseText
midString = Mid(re, InStr(re, """text"":""") + 8)
ChatGPT = Split(midString, """")(0)
ChatGPT = Replace(ChatGPT, "\n", "")
End Function
8、联动方式八:用 ChatGPT 代替 Excel
我用一个常见的强排指标来考考ChatGPT:
提示:“请根据客户提供的 95 亩住宅用地,建筑高度不超过 80 米,容积率 2,建筑密度 20%的条件,为客户提供关于公寓和别墅等住宅类型的规划建议,包括建筑层数、建筑数量以及绿化等方面的信息。1.目的:在符合各项经济指标要求下,追求销售总价最高。2.采用高低配,高低配的意思同一宗土地上,既修别墅,又修公寓。3,销售单价,公寓 2.5 万每平方米,别墅 3.8 万。请问,要达到销售总价最高,别墅,公寓各修多少栋,各自的总建筑面积是多少”
ChatGPT 答复如下:我觉得它就是把 excel 求最优解的过程用语言描述了一遍。
终于写完了,这一篇技术操作略多,下次来点轻松的,AI写文案或者PPT。
另,笔者准备把一些常见工作场景下,如何应用 AI的案例系统梳理一遍,在基础教程外,做一些场景教程。大家什么需求和建议都可以后台留言。
*长按关注,以便及时收到后继教程*
● 再送大家几份礼物:
1、关注本公众号,回复“AI“,免费获得学习大礼包。
2、另外,为了方便交流,做了个知识星球“AI 学院” 。不懂就问,有问必答。准备了10张3天免费体验卡。长按加入,先到先得。