点击蓝字关注【秋叶AIExcel】
发送【10】
免费领 10 个 AI 美化图表提示词！
本文作者：小爽
本文编辑：小兰
同事小李作为一个线下培训师，每次直播观看的人员名称都会一一记录。
最终登记成如下的表格：
▲ 以上为模拟数据
现在他想要基于所记录的数据进行分析，比如每个人参与过哪场直播……
很明显，想要做数据分析，第一步就是把以上表格转化为一维表，如下图：
怎么做呢？
首先让我们的 AI 小助理来帮忙~
AI 函数
这里我使用的是豆包，简单的表格问题它基本都能解决。
豆包网址：doubao.com
❶ 进入豆包后，在对话框中输入提示词。
提示词编写通用框架：数据源背景+编写要求+返回格式。
PS：数据源数据，可以直接复制粘贴少部分数据到提示词中。也可以通过上传文件或者上传表格图片，这里采取直接复制粘贴。
思考片刻之后，豆包就给我们编写了两个函数公式。
❷ 回到 Excel，我们将对应函数公式输入到单元格中，下拉填充即可。
G2单元格：
=INDEX($A$1:$E$1,,MOD(SMALL(IF($A$2:$E$9<>"",ROW($A$2:$E$9)*100+COLUMN($A$2:$E$9),""),ROW(A1)),100))H2单元格：
=INDEX($A$2:$E$9,INT(SMALL(IF($A$2:$E$9<>"",ROW($A$2:$E$9)*100+COLUMN($A$2:$E$9),""),ROW(A1))/100)-1,MOD(SMALL(IF($A$2:$E$9<>"",ROW($A$2:$E$9)*100+COLUMN($A$2:$E$9),""),ROW(A1)),100))
上面是用 AI 写函数公式完成的，方便是方便，不过我们编写完公式，还得向下拉进行拖拽，直到出现错误值，才知道结尾。
要是数据太多岂不是手都得累断，所以，我们还可以让AI 生成 VBA代码去解决这个问题。
好处是，代码写好后直接运行就可以了！
对了，如果你也想让 AI 替你工作，却不知道怎么让它配合，如果你想知道更多 AI 运用场景，以简单的操作完成复杂的工作任务~
那我推荐你加入《秋叶 AI 智能办公 3 天实战营》，秋叶金牌讲师 @AI陈 带你快速掌握豆包、飞书、扣子、即梦4 大工具办公技巧，提高个人核心竞争力。
课程原价 99 元
今天免费领取
✅直播授课 ✅实操练习 ✅助教答疑
名额有限，立即扫码预约上课！
报名免费领取
秋叶独家 AI 学习资料包
6 套飞书多维表格模板
60+ 秋叶自研智能体
AI 编程
只需将提示词的函数公式改成 VBA即可。
可以看到，右边的 AI 已经帮我们写好了 VBA 代码~
在【开发工具】选项卡下，单击【Visual Basic】，进入 VBA 编辑器中。
鼠标右键新建模块，将代码复制粘贴到模块中。
点击运行代码，如下图所示 ，VBA 一下子就帮我们把数据整理好了。
当然这个代码还不具有通用性，我们可以把整个过程变成一个函数，数据源为函数的参数。
继续问 AI，改一下我们的要求即可。
同理，豆包的右边直接替我们编写好了 VBA 代码，贴到模块中。
我们测试一下这个VBA 自定义函数公式的效果。
超赞！
后面我们遇到同样的需求，这个 VBA 自定义函数公式就可以复用啦~
前两种方法都和 AI 有关，如果你用不了 AI 或者担心数据安全，那最后这 2 种方法，你也可以了解一下。
其他方法
▋方法一：PQ
记住，二维表转一维表，用PowerQuery轻松搞定！
我们将数据上传到 PQ 编辑器中。
鼠标单击【场次1】列，按住【Shift】键，再单击场次 5 列，点击鼠标右键，单击【逆透视列】。
一下子就完成了~
需要改标题的话，可以直接在编辑栏中改。
WPS 用不了 PQ 怎么办？
下面这个函数法就是专为你准备的。
▋方法二：函数
场次列
观察数据源，我们知道场次标题需要重复的次数，是由下面非空数据区域决定。
那我们其实可以先做个判断。
=A2:E9<>""
如果不为空，那我们就显示对应的标题，否则我们就显示为错误值。
=IF(A2:E9<>"",A1:E1,NA())
最后利用 Tocol 函数将数据拉成一列。
=TOCOL(数组,[忽略特殊值],[通过列扫描])
忽略特殊值有如下参数可选。
0-保留所有值(默认)
1-忽略空白
2-忽略错误
3-忽略空白和错误
由于我们需要忽略错误值，所以第二参数得写 2 或者 3。
通过列扫描有如下参数可选。
false(0)-按行扫描(默认值)
true(1)-按列扫描
最后函数公式如下图所示：
=TOCOL(IF(A2:E9<>"",A1:E1,NA()),3)
姓名列
姓名列就简单了，直接利用 Tocol 函数将数据拉成一列，同时第二参数选 1，忽略空白。
=TOCOL(A2:E9,1)
如果想将两列进行横向拼接的话，可以使用 Hstack 函数。
=HSTACK(TOCOL(IF(A2:E9<>"",A1:E1,NA()),2),TOCOL(A2:E9,1))
如何将二维的一条条数据，转为一维数据，方便我们进行数据分析，本文一共分享了 4 种方法。
❶AI 写函数。
对于数据清洗，常见的提示词模板为：数据源背景+编写要求+输出表格格式
数据源背景：数据源区域，数据源内容，标题信息
编写要求：转化的需求+需要使用的是哪种方法返回(函数/VBA...)
输出表格格式：可以使用 Markdown 格式
AI 函数公式，使用的是传统万金油函数公式做法。
INDEX+MOD+SMALL+IF+ROW+COLUMN
❷AI 写 VBA 代码。
AI 编程，使用 VBA 返回，不同提示需求，返回形式会不一样。
- 输出过程，直接运行就可以返回结果。
- 输出函数，将数据源作为参数，使得代码更具有通用性，下次遇到同类的问题，可以复用自定义函数解决。
❸PQ 逆透视列一步完成。
❹逻辑判断+Tocol 函数快速搞定。
解决问题的方法有很多种，选适合你的就可以~
不过如果你也想让 AI 替你工作，如果你想知道更多 AI 运用场景，以简单的操作完成复杂的工作任务~
那我推荐你加入《秋叶 AI 智能办公 3 天实战营》，秋叶金牌讲师带你快速掌握豆包、飞书、Coze、即梦4 大工具办公技巧，提高个人核心竞争力。
课程原价 99 元
现在仅需 0 元！
秋叶实战派老师直播授课
专业助教随时答疑
多种 AI 工具教学
长按扫码，加班主任微信
立刻预约上课！
报名免费领取
秋叶独家 AI 学习资料包
6 套飞书多维表格模板
60+ 秋叶自研智能体
发送【10】
免费领 10 个AI 美化图表提示词！
热门跟贴