点击蓝字关注【秋叶AIExcel】
发送【7】
免费领 1000+篇 Excel 精选教程！
本文作者：小爽
本文编辑：竺兰
文末下载配套练习文件。
每年年初，领导都会制作如下排班表：
每周一会写上第 N 周，对应的就是当周的负责人。
经常看我们公号文章的小伙伴应该知道，虽然这种表格设计看起来直观，但在做全年数据分析时却不够规范。
把表格样式改成下图这种，不仅直观，也更方便我们进行数据统计。
现在问题来了，怎样修改呢？
其实，用函数或者 AI 都可以！
在进行数据清洗时，拆分函数是一个非常强大的工具。
TextSplit函数是拆分函数，Excel2024 以上版本和 WPS 都有。
TextSplit 函数的语法规则：
=TEXTSPLIT(字符串,列分隔符,[行分隔符],[是否忽略空值],[是否区分大小写],[错值填充方式])
除此之外，正则函数也是同样实用。
这里我使用的是 Office365，对应的正则函数是Regexextract函数，如果是 WPS 用户，对应的正则函数就是Regexp函数。
Regexextract 函数语法规则：
=REGEXEXTRACT(字符串，正则表达式，[匹配方式])
匹配方式中：
0 - 第一个匹配项(默认值)
1 - 所有匹配项
2 - 捕获第一个匹配项的组
分步函数公式
第一步：将二维表转为一列。
=TOCOL(A2:G53)
第二步：对每个数据判断，没有存在周的，前面加一个换行符。
最后拆分成列，并下拉填充公式。
)
简单介绍一下这个函数公式：
Find函数语法规则：
=find(查找内容,查找值)
如果查找得到就返回对应的位置，如果查找不到则返回错误值。
所以，我们通过判断查找值中是否存在周，不存在的话前面加一个换行符，存在就返回它本身。
)类似下图，我们在没有周次的单元格前加上换行符，用作占位。
最后利用拆分函数拆分为列就可以搞定啦~
当然，数据清洗，要么拆分，要么正则。
除了使用拆分函数来做，我们还可以使用正则函数。
函数公式如下图所示：
=TAKE(REGEXEXTRACT(CHAR(10)&I2,"^|.+",1),,-3)
简单介绍一下这个函数公式~
char(10)是换行符。
对于正则函数：
=REGEXEXTRACT(CHAR(10)&I2,"^|.+",1)字符串：CHAR(10)&I2
字符串前面都加上换行符。
正则表达式："^|.+"
^代表起始位置符
.+代表除换行符以外的一个或者多个字符
| 代表或的意思
匹配方式：1(所有匹配)
匹配结果如图中所示：
最后我们巧借Take函数，从后往前取三个列。
Take 函数语法规则：
=take(数组，行数,[列数])
正数代表前取，负数代表后取。
那么公式也容易理解了：
=TAKE(REGEXEXTRACT(CHAR(10)&I2,"^|.+",1),,-3)
当然，我们也可以直接使用正则函数来提取。
这里，我们使用分支重置组来获取。如下图所示：
=REGEXEXTRACT(I2,"(?|(第\d周)\n(.+)\n(.+)|()(.+)\n(.+))",2)
前面我们是分步骤完成的，也有读者可能更倾向于一步到位。
这就不得不用到我们强大的Reduce函数了。
Reduce 函数语法规则：
Reduce 函数会将 Lambda 应用于每个值，在累加器中返回总值，最终将数组减小为累积值。
=REDUCE(初始值，遍历的数组，LAMBDA(x,y,计算表达式))
第 1 个参数是初始累加值；
第 2 个参数是循环遍历的区域；
第 3 个参数是 Reduce 函数运算的表达式，它有两个参数，x 指向初始累加值，y 指向第 2 参数即被遍历的区域。
看不懂没关系，接下来我们通过案例来帮助你理解。
一次函数公式
函数公式如下图所示：
)
我们来看一下这个函数公式，思路跟前面分步做法是一样的。
难点就在于Reduce函数。
)跟着我的思路，来看看上面函数公式的运行步骤，看完之后，你对 Reduce 函数就会有更深刻的理解了。
第一步：将数据区域转换一列。
TOCOL(A2:G53)
第二步：循环每一个值
)Lambda 有两个参数。
Lambda(s,v,...)
s 代表参数的累计值，第一次循环为初始值，后面的循环为上一次循环的返回值。
v 代表循环的每一个值。
我们来看看第一次循环。
Vstack 函数的作用是将数组进行垂直拼接。
第二次循环。
直到最后一次循环结束，Reduce 函数公式会将最后的累计结果 s 进行返回。
对于这种针对数组，循环每一个数据进行处理后，累计垂直拼接的需求，我们也可以改成套路公式：
)Reduce 函数是不是非常强大，但好像有点难理解？
如果你的数据不敏感或者做了脱敏处理，也许用 AI，能帮你更好地解决问题。
WPS AI 处理
这里我使用的是WPS 灵犀 AI。
进入 WPS 中，单击侧边栏的灵犀，就可以进入对话窗口。
上传表格文件，输入提示词。
排班表中，Sheet1 中放着数据表。
背景：工作簿中有一个Sheet表，里面有排班表格，表头为周一，周二，周三，周四，周五，周六，周日。周一列下面会写明第n周 姓名 日期。其余列写明姓名 日期。
要求：将数据清洗为一维表的格式。每周的周次只出现一次，其余留空。
具体格式如下：
|周次|姓名|日期|
|第1周|小叮|2025/01/06|
||小兰|2025/01/07|
|第2周|..
返回格式：将处理后的结果将表格返回，并将处理后的工作簿结果返回。
最后，等待灵犀 AI 思考结束后。
下载 Excel 表即可。
数据处理得还可以~
看到这里，你是不是觉得，AI 真的很强大、很方便。
其实，在智能办公时代，像这样借助 AI 来解决问题、提高效率的场景还有很多。
如果你想解锁更多 AI 办公神技，比如：
快速搞定各种工作文案；
一键生成年终总结 PPT；
批量设计高级感海报、配图；
轻松搞定复杂的数据分析……
那就千万别错过《秋叶 AI 智能办公 3 天实战营》！
在这里，秋叶实战派导师 @AI陈，将用 3 天时间，带你从 0 到 1，系统掌握 AI 办公的核心技能，助你摆脱低效、重复的工作！
《秋叶 AI 智能办公 3 天实战营》
课程原价 99 元
现在仅需 0 元！
直播授课+实战练习+助教答疑
名额有限，立即扫码预约上课
遇到一张复杂的表格，如何改成更适合做数据分析的格式。
本文通过对排班表进行处理来举例，介绍了函数和 AI两种方法。
函数法：
这里我们采取分步函数公式，还有一次函数公式。
分步函数公式中，利用 Tocol 函数将数据区域转为一列后，再利用拆分函数或正则函数提取对应的值。
一次函数公式中，思路一样，不过多加了一步，采取 Reduce 函数循环将处理后的数据进行垂直拼接。
AI法：
假如数据不怎么敏感，我们可以用 AI 来帮我们干活。比如作为 WPS 办公的灵犀 AI，上传文件，输入需求，AI 就会直接生成处理好的表格。
掌握函数，是底层能力；善用 AI，是时代红利。
处理数据时，你完全可以根据自己的需求（尤其是数据安全性），选择更适合的方法。
好啦，今天的分享就到这里，如果你还遇到了其他 Excel 难题，欢迎在留言区交流哦~
发送【排班表】
免费下载练习文件！
热门跟贴