Excel数据挖掘：ChatGPT助力数据分析
文章主题：复杂Excel数据,ChatGPT,数据处理,VBA代码
这节课我们学习用 ChatGPT 处理复杂 Excel 数据
一、用ChatGPT 处理复杂 Excel 数据的方法
由于目前 ChatGPT 还不支持上传文件以及对大数据量的处理，
所以我们只能换一种思路结合 Excel 和 ChatGPT 的协作
1.整体思路
我们告诉 ChatGTP 我们想要实现什么样的效果，
让 ChatGPT 告诉我们结果、公式 和 vba 代码，
我们直接按照 ChatGTP 给出的方法执行就行
接下来我们还是通过一个案例来进行讲解
2.数据案例
我们还是使用上节课的数据，大约30条，数据如下：
3.希望通过数据分析出什么
这是一次活动推销的结果的数据，希望通过分析数据：
a.需要分析出哪些维度对推销结果的影响最大
b.如何确定银行定期产品推销中最具价值的客户
c.优化的建议有哪些
d.实行完优化建议后，预期的效果会是什么样
4.清理无用数据的方法
一般我们会先把垃圾数据删掉，比如：恶意填写、错填、漏填的数据。
我喜欢把表格中的无效数据称为：unknown
清理无效数据的方法如下
发送给 ChatGPT：
我手头有一個Microsoft Excel檔案，其中包含多個欄位。為了刪除包含未知數據的行，我將利用VBA程式碼來完成這個任務。這個程式碼能夠遍歷所有欄位和行，檢查是否有任何一列包含未知數據。如果有，它會自動刪除該行的整個行。這個方法可以有效地清理含有未知數據的Excel表格，並保持文件的整潔與高效。
(VBA ：通过这种代码可以实现 Excel 的复杂逻辑)
ChatGPT 回复：
如果不会用 VBA 代码，
可以发送给 ChatGPT：
如何在 microsoft excel 中使用这段 vba 代码
ChatGPT 回复如下：
您可以按以下步骤在 Microsoft Excel 中使用 VBA 代码：
打开 Excel 文件，按下 Alt+F11 进入 Visual Basic 编辑器。
在左边的“项目”窗口中，双击您要向其添加代码的工作簿，然后在新窗口中插入一个新的模块。
在新模块中，复制上面给出的 VBA 代码。
回到 Excel 工作表，按下 Alt+F8 打开宏窗口。
选择您刚刚添加的代码，并单击“运行”按钮。
在程序代码执行完毕之后，Visual Basic编辑器将会关闭，而您将回到Excel工作表的状态。在此之前，建议您仔细审查数据集，以确认是否有未知值被误删。
请注意，运行 VBA 代码可能会直接修改您的 Excel 文件，因此在运行代码之前，请务必备份您的数据集。
接下来我们把 VBA 代码按照使用方式复制到 VBA 编辑框中
点击：执行
最后我们关闭这个 vba 编辑框，
可以发现我们的数据已经清理干净，
没有 unknown 的数据了
5.维度分析
同样我们告诉 ChatGPT
希望让它按照维度来帮我们分析，并告诉我们该怎么做
比如我们希望先分析一下年龄、学历和资产余额维度这几个维度对推销结果的影响最大，
那我们可以
发送给 ChatGPT：
在本次市场营销活动中，我们使用了Excel这一工具来分析各个维度对销售成果的影响程度。首先，我们需要对年龄、学历和资产余额这三个维度进行分析，找出哪个因素对销售业绩的影响最为显著。为此，我们可以编写一段VBA代码来实现这一目标。以下是针对此需求的一段简洁的VBA代码：“`vbaSub AnalyzeDimensions() Dim ws1 As Worksheet, ws2 As Worksheet Dim dataRange As Range Dim ageRange As Range, eqRange As Range Dim resultRange As Range Dim i As Long ‘ 设置工作表对象 Set ws1 = ThisWorkbook.Worksheets(“Sheet1”) ‘ 请根据实际情况修改工作表名称 Set ws2 = ThisWorkbook.Worksheets(“Sheet2”) ‘ 请根据实际情况修改工作表名称 ‘ 清除工作表内容 ws1.Cells.Clear ‘ 获取数据范围 Set dataRange = ws1.Range(“A2:D100”) ‘ 请根据实际情况修改数据范围 ‘ 分别筛选年龄、学历和资产余额范围 ageRange = dataRange.Columns(1) eqRange = dataRange.Columns(3) resultRange = dataRange.Columns(4) ‘ 计算每个维度的占比 ageRange.Cells.Formula = “=SUMIF(A$2:A$100,”>=”&ageRange.Value,1)/ COUNTA(A$2:A$100)” eqRange.Cells.Formula = “=SUMIF(B$2:B$100,”>=”&eqRange.Value,1)/ COUNTA(B$2:B$100)” resultRange.Cells.Formula = “=SUMIF(C$2:C$100,”>=”&resultRange.Value,1)/ COUNTA(C$2:C$100)” ‘ 将结果写入Sheet2 If Not ws2 Is Nothing Then With ws2 .Range(“E2”).Value = “Age” .Range(“F2”).Value = “Equipment Amount” .Range(“G2”).Value = “Profit Impact” .Range(“H2”).Value = “Result” .Range(“E3”).Value = ageRange.Value .Range(“F3”).Value = eqRange.Value .Range(“G3”).Value = resultRange.Value .Range(“H3”).Value = “0” .Range(“I2”).Formula = “=IF(COUNTIF($C$2:$C$100,$C2)=1, “Yes”, “No”)” .Range(“J2”).Formula = “=IF(COUNTIF($D$2:$D$100,$D2)=1, “Yes”, “No”)” .Range(“E4:H4”).Formula = “=IF(AND(ISNUMBER(ageRange.Value),ISNUMBER(eqRange.Value)), “Yes”, “No”)” .Range(“E5:H5”).Formula = “=IF(AND(ISNUMBER(ageRange.Value),ISNUMBER(resultRange.Value)), “Yes”, “No”)” .Range(“E6:H6”).Formula = “=IF(AND(ISNUMBER(eqRange.Value),ISNUMBER(resultRange.Value)), “Yes”, “No”)” .Range(“I7”).Formula = “=IF(AND(ISNUMBER(ageRange.Value),ISNUMBER(eqRange.Value)), “Yes”, “No”)” .Range(“I8”).Formula = “=IF(AND(ISNUMBER(eqRange.Value),ISNUMBER(resultRange.Value)), “Yes”, “No”)” .Range(“I9”).Formula = “=IF(AND(ISNUMBER(resultRange.Value),ISNUMBER(eqRange.Value)), “Yes”, “No”)” .Range(“J10”).Formula = “=IF(AND(ISNUMBER(eqRange.Value),ISNUMBER(resultRange.Value)), “Yes”, “No”)” End With Else MsgBox “Sheet2 does not exist. Please create it before running the code.”, vbExclamation End IfEnd Sub“`这段代码将自动计算出年龄、学历和资产余额三个维度在数据范围内的占比，并将结果写入名为“Sheet2”的工作表中。如果“Sheet2”不存在，代码会提示用户先创建该工作表。
下面列出了数据的结构和对应的解释
“””
Feature Description
age 年龄（数值）
职业（分类：管理员，蓝领工人，企业家，家庭佣工，管理职位，退休，自雇，服务行业，学生，技术人员，失业，未知）
在婚姻状况这一方面，我们可以将其细分为四种类型，分别是已婚（married）、离婚（divorced）、单身（single）以及未知（unknown）。
在教育领域中，学力的评估与划分是一个重要的课题。通常而言，我们将学历划分为四个层次，即初级、中级、高级和未知。这些层次的设定有助于我们更好地理解和评价一个人的受教育程度。
default 失信状况（分类：yes, no）
balance 资产余额（数值）
housing 房屋贷款（分类：yes, no, unknown）
loan 个人贷款（分类：yes, no, unknown）
contact 联系方式（分类：cellular, telephone）
day 最后一次电话营销的日期（数值：月份中的哪一天）
在过去的一个月里，我们进行了最后一次电话营销活动。
duration 通话时长（数值：以秒为单位，0的话最终输出结果必然是0）
campaign 联系次数（数值：此活动中联系该客户的次数）
pdays 距上次联系完客户后的天数（数值：999代表未联系过该客户）
previous 这次活动前与这位客户联系的次数（数值）
poutcome 上次营销的结果（分类：yes, no, unknown）
deposit 定期存款（分类：yes, no）客户是否已购买定期存款
ChatGPT 回：
同样，
在 Excel 中按下 Alt+F11 ，我们把代码放入到 Excel 中的 VBA 编辑框
最后我们在 Sheet 2 中
就可以得到分析出来的结果
6.看不懂数据怎么办？
我们在来问问 ChatGPT
发送给 ChatGPT：
在分析过程中，我们得出了四列数据，它们分别代表了不同特征的均值、方差、标准差。其中，均值（Mean）是所有数据值的平均值，它反映了特征的一般趋势；方差（Variance）则表示各个数据值与其均值之间的差异程度，用来衡量数据的离散程度；而标准差（Standard Deviation）则是方差的平方根，用于进一步量化数据的离散程度。通过对这四列数据的分析，我们可以更好地理解各个特征的分布情况以及数据的变化幅度。
ChatGPT回：
Feature: 数据的特征，即所分析的维度
Mean： 平均值，是该特征所有样本值的总和除以样本个数，它可以表示该特征的集中趋势。
Variance: 算术平均数与各观察值偏差平方和的算术平均数。也就是说，每个数值与特征平均值的差值的平方之和的平均值，可以反映出样本的离散程度。
Standard Deviation:标准差是更常用的的用以衡量变量之间的差异性的统计量。简单的说，标准差是方差的算术平方根。标准差越小，则表示从平均数开始，每个数据都比较接近平均水平。反之，则表示很多数据离平均水平较远。
7.可视化分析
前面 ChatGPT 分析出来的结果是表格形式不易理解，
也不清楚哪个维度影响最大，
如果我们也不知道该如何可视化进一步分析，那我们可以接着问：
发送给 ChatGPT：
你认为该以什么样的图形化展示比较好
ChatGPT回：
根据这三个维度的数据，可以采用多条柱形图（Multi-Bar Plot）进行可视化展示，将每个维度的平均值、标准差分别绘制成一条柱形，并按大小排序，以便于观察哪个维度对推销结果的影响最大。另外，也可以考虑使用散点图（Scatter Plot）进行可视化展示，将每个样本的三个维度的值展示在平面坐标系中，以便于观察每个样本在三个维度上的分布情况和特征之间的关系。
希望进一步分析，再发送给 ChatGPT：
那基于 Sheet2 分析出来的数据，使用 多条柱形图 进一步分析出来哪一个维度对推销结果的影响最大，并以图形在 Sheet3 展示，如果 Sheet3不存在请自行创建；请用比较简洁的 vba 代码实现
ChatGPT 回：
同样，在 Excel 中按下 Alt+F11 ，我们把代码放入到 Excel 中的 VBA 编辑框
最后，
我们可以得到 Sheet3 的可视化表
8.基于分析结果得出结论
当 ChatGPT 生成了可视化的分析，
进一步让 ChatGPT 帮我们分析出来结论
发送给 ChatGPT：
这是分析出来的结果，解释分析一下
“””
Feature Mean Standard Deviation
age 42.47619048 18.35
education 3.047619048 1.45
balance 22080.95238 34856.84
ChatGPT 回：
根据 Sheet2 分析的数据结果，我们可以得出如上的表格。
Feature: 数据的特征，即所分析的维度，包括 age, education 和 balance。
Mean: 该维度所有样本值的平均值，代表该特征的集中趋势。
Standard Deviation: 标准差是用以衡量变量之间的差异性的统计量，表示该特征的样本数据的离散程度。
通过对比不同特征的标准差，我们可以判断不同特征对推销结果的影响，标准差越大，说明该特征对推销结果的影响越大。
结合上表，我们可以发现 balance 的标准差远大于其他两个特征，因此，资产余额这一特征对推销结果的影响最大。
9.提出优化建议和预测
有了分析出来的结论后，那
可以让 ChatGPT 给出优化的建议，并且预测数据
发送给 ChatGPT：
a.基于这个结果，给出优化建议
b.列出几个可行的措施
c.如果落实了这几个措施，会对结果产生多大影响，用一句话总结，并给出一个大致的比例
二、总结
基于上述案例我们做了什么?
我们可以再次让 ChatGPT 帮我们总结
发送给 ChatGPT：
分析一下这次的对话，列出步骤
ChatGPT 回：
以下是这次对话的步骤：
第一个问题的提出：用户提出了一个 Excel 相关的问题和需求。
第一个问题的回答：AI 帮助回答了用户的第一个问题。
第二个问题的提出：用户向 AI 提出了一个新的问题和需求，希望得到一段 VBA 代码，实现对 Excel 文件中的数据进行筛选。
第二个问题的回答：AI 给出了一段简洁的 VBA 代码来实现用户的需求。
第三个问题的提出：用户向 AI 提出了另一个需求，希望根据年龄、学历和资产余额这三个维度进行分析，以便找出哪个维度对推销结果的影响最大。
第三个问题的回答：AI 根据用户的需求给出了一段 VBA 代码，并解释了代码的作用，同时也提供了多条柱形图和散点图的可视化方式，以便更好地展现和分析数据。
对分析结果的解释和优化建议：AI 解释了根据分析结果得出的结论，并提供了可行的几个措施来优化推销的效果。
对落实措施的影响进行分析：AI 对采取措施后推销成功率的提高进行了预测。
ChatGPT 和 Excel 的协议可以说非常的强大，
但前提是需要我们准确的告诉 ChatGPT 应该做什么，以什么的格式去做，
特别是对于 Excel 的术语（比如第一行，第一列、第一个Sheet… 等）
因为 ChatGPT 没办法知道计算出来的结果是什么，
所以我们希望 ChatGPT 基于什么样的结果进行分析时，
则需要把通过代码算出来的结果再次告诉 ChatGPT。
三、当然你也可以直接用我刚才的案例数据练手
1.案例数据下载链接
https://temp-1257364845.cos.ap-nanjing.myqcloud.com/%E8%A7%A3%E9%94%81excel%E6%A1%88%E4%BE%8B%E5%88%86%E6%9E%90.xlsx
2.或者让 ChatGPT 帮你生成新的案例数据
发送给 ChatGPT：
根据下面这个信息，根据每个字段和描述生成30条数据，并以表格方式展示
“””
字段名 理解
age 年龄（数值）
job 职业（分类：admin, bluecollar, entrepreneur, housemaid, management, retired, self-employed, services,student, technician, unemployed, unknown）
marital 婚姻状况（分类：divorced, married, single, unknown）
education 学历（分类：primary, secondary, tertiary and unknown）
default 失信状况（分类：yes, no）
balance 资产余额（数值）
housing 房屋贷款（分类：yes, no, unknown）
loan 个人贷款（分类：yes, no, unknown）
contact 联系方式（分类：cellular, telephone）
day 最后一次电话营销的日期（数值：月份中的哪一天）
month 最后一次电话营销的月份（分类：jan, feb, mar, apr,…,nov, dev）
duration 通话时长（数值：以秒为单位，0的话最终输出结果必然是0）
campaign 联系次数（数值：此活动中联系该客户的次数）
pdays 距上次联系完客户后的天数（数值：999代表未联系过该客户）
previous 这次活动前与这位客户联系的次数（数值）
poutcome 上次营销的结果（分类：yes, no, unknown）
deposit 定期存款（分类：yes, no）客户是否已购买定期存款
“””
四、注意事项
在对话的过程中可能会出现字数超长的情况，
可以尝试把已经无用的代码记录删除掉
AI时代，拥有个人微信机器人AI助手！AI时代不落人后！
免费ChatGPT问答，办公、写作、生活好得力助手！
搜索微信号aigc666aigc999或上边扫码，即可拥有个人AI助手！
相关文章
