點(diǎn)擊藍(lán)字關(guān)注【秋葉AIExcel】

發(fā)送【7】

免費(fèi)領(lǐng) 1000+篇 Excel 精選教程！

本文作者：小爽

本文編輯：竺蘭

文末下載配套練習(xí)文件。

每年年初，領(lǐng)導(dǎo)都會(huì)制作如下排班表：

每周一會(huì)寫上第 N 周，對(duì)應(yīng)的就是當(dāng)周的負(fù)責(zé)人。

經(jīng)?？次覀児?hào)文章的小伙伴應(yīng)該知道，雖然這種表格設(shè)計(jì)看起來(lái)直觀，但在做全年數(shù)據(jù)分析時(shí)卻不夠規(guī)范。

把表格樣式改成下圖這種，不僅直觀，也更方便我們進(jìn)行數(shù)據(jù)統(tǒng)計(jì)。

現(xiàn)在問(wèn)題來(lái)了，怎樣修改呢？

其實(shí)，用函數(shù)或者 AI 都可以！

在進(jìn)行數(shù)據(jù)清洗時(shí)，拆分函數(shù)是一個(gè)非常強(qiáng)大的工具。

TextSplit函數(shù)是拆分函數(shù)，Excel2024 以上版本和 WPS 都有。

TextSplit 函數(shù)的語(yǔ)法規(guī)則：

=TEXTSPLIT(字符串,列分隔符,[行分隔符],[是否忽略空值],[是否區(qū)分大小寫],[錯(cuò)值填充方式])

除此之外，正則函數(shù)也是同樣實(shí)用。

這里我使用的是 Office365，對(duì)應(yīng)的正則函數(shù)是Regexextract函數(shù)，如果是 WPS 用戶，對(duì)應(yīng)的正則函數(shù)就是Regexp函數(shù)。

Regexextract 函數(shù)語(yǔ)法規(guī)則：

=REGEXEXTRACT(字符串，正則表達(dá)式，[匹配方式])

匹配方式中：

0 - 第一個(gè)匹配項(xiàng)(默認(rèn)值)

1 - 所有匹配項(xiàng)

2 - 捕獲第一個(gè)匹配項(xiàng)的組

分步函數(shù)公式

第一步：將二維表轉(zhuǎn)為一列。

=TOCOL(A2:G53)

第二步：對(duì)每個(gè)數(shù)據(jù)判斷，沒(méi)有存在周的，前面加一個(gè)換行符。

最后拆分成列，并下拉填充公式。

)

簡(jiǎn)單介紹一下這個(gè)函數(shù)公式：

Find函數(shù)語(yǔ)法規(guī)則：

=find(查找內(nèi)容,查找值)

如果查找得到就返回對(duì)應(yīng)的位置，如果查找不到則返回錯(cuò)誤值。

所以，我們通過(guò)判斷查找值中是否存在周，不存在的話前面加一個(gè)換行符，存在就返回它本身。

)

類似下圖，我們?cè)跊](méi)有周次的單元格前加上換行符，用作占位。

最后利用拆分函數(shù)拆分為列就可以搞定啦~

當(dāng)然，數(shù)據(jù)清洗，要么拆分，要么正則。

除了使用拆分函數(shù)來(lái)做，我們還可以使用正則函數(shù)。

函數(shù)公式如下圖所示：

=TAKE(REGEXEXTRACT(CHAR(10)&I2,"^|.+",1),,-3)

簡(jiǎn)單介紹一下這個(gè)函數(shù)公式~

char(10)是換行符。

對(duì)于正則函數(shù)：

=REGEXEXTRACT(CHAR(10)&I2,"^|.+",1)

字符串：CHAR(10)&I2

字符串前面都加上換行符。

正則表達(dá)式："^|.+"

^代表起始位置符

.+代表除換行符以外的一個(gè)或者多個(gè)字符

| 代表或的意思

匹配方式：1(所有匹配)

匹配結(jié)果如圖中所示：

最后我們巧借Take函數(shù)，從后往前取三個(gè)列。

Take 函數(shù)語(yǔ)法規(guī)則：

=take(數(shù)組，行數(shù),[列數(shù)])

正數(shù)代表前取，負(fù)數(shù)代表后取。

那么公式也容易理解了：

=TAKE(REGEXEXTRACT(CHAR(10)&I2,"^|.+",1),,-3)

當(dāng)然，我們也可以直接使用正則函數(shù)來(lái)提取。

這里，我們使用分支重置組來(lái)獲取。如下圖所示：

=REGEXEXTRACT(I2,"(?|(第\d周)\n(.+)\n(.+)|()(.+)\n(.+))",2)

前面我們是分步驟完成的，也有讀者可能更傾向于一步到位。

這就不得不用到我們強(qiáng)大的Reduce函數(shù)了。

Reduce 函數(shù)語(yǔ)法規(guī)則：

Reduce 函數(shù)會(huì)將 Lambda 應(yīng)用于每個(gè)值，在累加器中返回總值，最終將數(shù)組減小為累積值。

=REDUCE(初始值，遍歷的數(shù)組，LAMBDA(x,y,計(jì)算表達(dá)式))

第 1 個(gè)參數(shù)是初始累加值；

第 2 個(gè)參數(shù)是循環(huán)遍歷的區(qū)域；

第 3 個(gè)參數(shù)是 Reduce 函數(shù)運(yùn)算的表達(dá)式，它有兩個(gè)參數(shù)，x 指向初始累加值，y 指向第 2 參數(shù)即被遍歷的區(qū)域。

看不懂沒(méi)關(guān)系，接下來(lái)我們通過(guò)案例來(lái)幫助你理解。

一次函數(shù)公式

函數(shù)公式如下圖所示：

)

我們來(lái)看一下這個(gè)函數(shù)公式，思路跟前面分步做法是一樣的。

難點(diǎn)就在于Reduce函數(shù)。

)

跟著我的思路，來(lái)看看上面函數(shù)公式的運(yùn)行步驟，看完之后，你對(duì) Reduce 函數(shù)就會(huì)有更深刻的理解了。

第一步：將數(shù)據(jù)區(qū)域轉(zhuǎn)換一列。

TOCOL(A2:G53)

第二步：循環(huán)每一個(gè)值

       )

Lambda 有兩個(gè)參數(shù)。

Lambda(s,v,...)

s 代表參數(shù)的累計(jì)值，第一次循環(huán)為初始值，后面的循環(huán)為上一次循環(huán)的返回值。

v 代表循環(huán)的每一個(gè)值。

我們來(lái)看看第一次循環(huán)。

Vstack 函數(shù)的作用是將數(shù)組進(jìn)行垂直拼接。

第二次循環(huán)。

直到最后一次循環(huán)結(jié)束，Reduce 函數(shù)公式會(huì)將最后的累計(jì)結(jié)果 s 進(jìn)行返回。

對(duì)于這種針對(duì)數(shù)組，循環(huán)每一個(gè)數(shù)據(jù)進(jìn)行處理后，累計(jì)垂直拼接的需求，我們也可以改成套路公式：

)

Reduce 函數(shù)是不是非常強(qiáng)大，但好像有點(diǎn)難理解？

如果你的數(shù)據(jù)不敏感或者做了脫敏處理，也許用 AI，能幫你更好地解決問(wèn)題。

WPS AI 處理

這里我使用的是WPS 靈犀 AI。

進(jìn)入 WPS 中，單擊側(cè)邊欄的靈犀，就可以進(jìn)入對(duì)話窗口。

上傳表格文件，輸入提示詞。

排班表中，Sheet1 中放著數(shù)據(jù)表。

背景：工作簿中有一個(gè)Sheet表，里面有排班表格，表頭為周一，周二，周三，周四，周五，周六，周日。周一列下面會(huì)寫明第n周 姓名 日期。其余列寫明姓名 日期。

要求：將數(shù)據(jù)清洗為一維表的格式。每周的周次只出現(xiàn)一次，其余留空。

具體格式如下：

|周次|姓名|日期|

|第1周|小叮|2025/01/06|

||小蘭|2025/01/07|

|第2周|..

返回格式：將處理后的結(jié)果將表格返回，并將處理后的工作簿結(jié)果返回。

最后，等待靈犀 AI 思考結(jié)束后。

下載 Excel 表即可。

數(shù)據(jù)處理得還可以~

看到這里，你是不是覺(jué)得，AI 真的很強(qiáng)大、很方便。

其實(shí)，在智能辦公時(shí)代，像這樣借助 AI 來(lái)解決問(wèn)題、提高效率的場(chǎng)景還有很多。

如果你想解鎖更多 AI 辦公神技，比如：

• 快速搞定各種工作文案；

• 一鍵生成年終總結(jié) PPT；

• 批量設(shè)計(jì)高級(jí)感海報(bào)、配圖；

• 輕松搞定復(fù)雜的數(shù)據(jù)分析……

那就千萬(wàn)別錯(cuò)過(guò)《秋葉 AI 智能辦公 3 天實(shí)戰(zhàn)營(yíng)》！

在這里，秋葉實(shí)戰(zhàn)派導(dǎo)師 @AI陳，將用 3 天時(shí)間，帶你從 0 到 1，系統(tǒng)掌握 AI 辦公的核心技能，助你擺脫低效、重復(fù)的工作！

《秋葉 AI 智能辦公 3 天實(shí)戰(zhàn)營(yíng)》

課程原價(jià) 99 元

現(xiàn)在僅需 0 元！

直播授課+實(shí)戰(zhàn)練習(xí)+助教答疑

名額有限，立即掃碼預(yù)約上課

遇到一張復(fù)雜的表格，如何改成更適合做數(shù)據(jù)分析的格式。

本文通過(guò)對(duì)排班表進(jìn)行處理來(lái)舉例，介紹了函數(shù)和 AI兩種方法。

函數(shù)法：

這里我們采取分步函數(shù)公式，還有一次函數(shù)公式。

分步函數(shù)公式中，利用 Tocol 函數(shù)將數(shù)據(jù)區(qū)域轉(zhuǎn)為一列后，再利用拆分函數(shù)或正則函數(shù)提取對(duì)應(yīng)的值。

一次函數(shù)公式中，思路一樣，不過(guò)多加了一步，采取 Reduce 函數(shù)循環(huán)將處理后的數(shù)據(jù)進(jìn)行垂直拼接。

AI法：

假如數(shù)據(jù)不怎么敏感，我們可以用 AI 來(lái)幫我們干活。比如作為 WPS 辦公的靈犀 AI，上傳文件，輸入需求，AI 就會(huì)直接生成處理好的表格。

掌握函數(shù)，是底層能力；善用 AI，是時(shí)代紅利。

處理數(shù)據(jù)時(shí)，你完全可以根據(jù)自己的需求（尤其是數(shù)據(jù)安全性），選擇更適合的方法。

好啦，今天的分享就到這里，如果你還遇到了其他 Excel 難題，歡迎在留言區(qū)交流哦~

發(fā)送【排班表】

免費(fèi)下載練習(xí)文件！