湖北
點擊藍字關注【秋葉AIExcel】
發(fā)送【7】
免費領 1000+篇 Excel 精選教程！
本文作者：小爽
本文編輯：竺蘭
文末下載配套練習文件。
每年年初，領導都會制作如下排班表：
每周一會寫上第 N 周，對應的就是當周的負責人。
經?？次覀児栁恼碌男』锇閼撝?，雖然這種表格設計看起來直觀，但在做全年數據分析時卻不夠規(guī)范。
把表格樣式改成下圖這種，不僅直觀，也更方便我們進行數據統(tǒng)計。
現在問題來了，怎樣修改呢？
其實，用函數或者 AI 都可以！
在進行數據清洗時，拆分函數是一個非常強大的工具。
TextSplit函數是拆分函數，Excel2024 以上版本和 WPS 都有。
TextSplit 函數的語法規(guī)則：
=TEXTSPLIT(字符串,列分隔符,[行分隔符],[是否忽略空值],[是否區(qū)分大小寫],[錯值填充方式])
除此之外，正則函數也是同樣實用。
這里我使用的是 Office365，對應的正則函數是Regexextract函數，如果是 WPS 用戶，對應的正則函數就是Regexp函數。
Regexextract 函數語法規(guī)則：
=REGEXEXTRACT(字符串，正則表達式，[匹配方式])
匹配方式中：
0 - 第一個匹配項(默認值)
1 - 所有匹配項
2 - 捕獲第一個匹配項的組
分步函數公式
第一步：將二維表轉為一列。
=TOCOL(A2:G53)
第二步：對每個數據判斷，沒有存在周的，前面加一個換行符。
最后拆分成列，并下拉填充公式。
)
簡單介紹一下這個函數公式：
Find函數語法規(guī)則：
=find(查找內容,查找值)
如果查找得到就返回對應的位置，如果查找不到則返回錯誤值。
所以，我們通過判斷查找值中是否存在周，不存在的話前面加一個換行符，存在就返回它本身。
)類似下圖，我們在沒有周次的單元格前加上換行符，用作占位。
最后利用拆分函數拆分為列就可以搞定啦~
當然，數據清洗，要么拆分，要么正則。
除了使用拆分函數來做，我們還可以使用正則函數。
函數公式如下圖所示：
=TAKE(REGEXEXTRACT(CHAR(10)&I2,"^|.+",1),,-3)
簡單介紹一下這個函數公式~
char(10)是換行符。
對于正則函數：
=REGEXEXTRACT(CHAR(10)&I2,"^|.+",1)字符串：CHAR(10)&I2
字符串前面都加上換行符。
正則表達式："^|.+"
^代表起始位置符
.+代表除換行符以外的一個或者多個字符
| 代表或的意思
匹配方式：1(所有匹配)
匹配結果如圖中所示：
最后我們巧借Take函數，從后往前取三個列。
Take 函數語法規(guī)則：
=take(數組，行數,[列數])
正數代表前取，負數代表后取。
那么公式也容易理解了：
=TAKE(REGEXEXTRACT(CHAR(10)&I2,"^|.+",1),,-3)
當然，我們也可以直接使用正則函數來提取。
這里，我們使用分支重置組來獲取。如下圖所示：
=REGEXEXTRACT(I2,"(?|(第\d周)\n(.+)\n(.+)|()(.+)\n(.+))",2)
前面我們是分步驟完成的，也有讀者可能更傾向于一步到位。
這就不得不用到我們強大的Reduce函數了。
Reduce 函數語法規(guī)則：
Reduce 函數會將 Lambda 應用于每個值，在累加器中返回總值，最終將數組減小為累積值。
=REDUCE(初始值，遍歷的數組，LAMBDA(x,y,計算表達式))
第 1 個參數是初始累加值；
第 2 個參數是循環(huán)遍歷的區(qū)域；
第 3 個參數是 Reduce 函數運算的表達式，它有兩個參數，x 指向初始累加值，y 指向第 2 參數即被遍歷的區(qū)域。
看不懂沒關系，接下來我們通過案例來幫助你理解。
一次函數公式
函數公式如下圖所示：
)
我們來看一下這個函數公式，思路跟前面分步做法是一樣的。
難點就在于Reduce函數。
)跟著我的思路，來看看上面函數公式的運行步驟，看完之后，你對 Reduce 函數就會有更深刻的理解了。
第一步：將數據區(qū)域轉換一列。
TOCOL(A2:G53)
第二步：循環(huán)每一個值
)Lambda 有兩個參數。
Lambda(s,v,...)
s 代表參數的累計值，第一次循環(huán)為初始值，后面的循環(huán)為上一次循環(huán)的返回值。
v 代表循環(huán)的每一個值。
我們來看看第一次循環(huán)。
Vstack 函數的作用是將數組進行垂直拼接。
第二次循環(huán)。
直到最后一次循環(huán)結束，Reduce 函數公式會將最后的累計結果 s 進行返回。
對于這種針對數組，循環(huán)每一個數據進行處理后，累計垂直拼接的需求，我們也可以改成套路公式：
)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 天實戰(zhàn)營》！
在這里，秋葉實戰(zhàn)派導師 @AI陳，將用 3 天時間，帶你從 0 到 1，系統(tǒng)掌握 AI 辦公的核心技能，助你擺脫低效、重復的工作！
《秋葉 AI 智能辦公 3 天實戰(zhàn)營》
課程原價 99 元
現在僅需 0 元！
直播授課+實戰(zhàn)練習+助教答疑
名額有限，立即掃碼預約上課
遇到一張復雜的表格，如何改成更適合做數據分析的格式。
本文通過對排班表進行處理來舉例，介紹了函數和 AI兩種方法。
函數法：
這里我們采取分步函數公式，還有一次函數公式。
分步函數公式中，利用 Tocol 函數將數據區(qū)域轉為一列后，再利用拆分函數或正則函數提取對應的值。
一次函數公式中，思路一樣，不過多加了一步，采取 Reduce 函數循環(huán)將處理后的數據進行垂直拼接。
AI法：
假如數據不怎么敏感，我們可以用 AI 來幫我們干活。比如作為 WPS 辦公的靈犀 AI，上傳文件，輸入需求，AI 就會直接生成處理好的表格。
掌握函數，是底層能力；善用 AI，是時代紅利。
處理數據時，你完全可以根據自己的需求（尤其是數據安全性），選擇更適合的方法。
好啦，今天的分享就到這里，如果你還遇到了其他 Excel 難題，歡迎在留言區(qū)交流哦~
發(fā)送【排班表】
免費下載練習文件！
特別聲明：以上內容(如有圖片或視頻亦包括在內)為自媒體平臺“網易號”用戶上傳并發(fā)布，本平臺僅提供信息存儲服務。
Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.
