本文作者：小爽

本文编辑：小兰

同事小李作为一个线下培训师，每次直播观看的人员名称都会一一记录。

最终登记成如下的表格：

▲ 以上为模拟数据

现在他想要基于所记录的数据进行分析，比如每个人参与过哪场直播……

很明显，想要做数据分析，第一步就是把以上表格转化为一维表，如下图：

怎么做呢？

首先让我们的 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 编程

只需将提示词的函数公式改成 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 函数快速搞定。

解决问题的方法有很多种，选适合你的就可以~