1秒批處理1000條混亂時(shí)間數(shù)據(jù)! wps excel是一個(gè)超級(jí)時(shí)間管理器
我要評(píng)論網(wǎng)友求助SOS：如何批處理1000條混亂的時(shí)間數(shù)據(jù)？到底是什么工作場(chǎng)景呢，下面我們舉例還原真實(shí)的職場(chǎng)案例。
如下圖所示：
A列是一列相對(duì)格式混亂的文本型時(shí)間數(shù)據(jù)，有的是"x時(shí)x分x秒"格式，有的是"x秒"格式，也有的是"x分x秒"格式，我們想要將這些格式不太一樣的時(shí)間數(shù)據(jù)轉(zhuǎn)換為統(tǒng)一的數(shù)字型時(shí)間格式"h:mm:ss"。比如"49時(shí)25分7秒"被轉(zhuǎn)成了"49:25:07"，而像"7秒"變成了"0:00:07"。
有的小伙伴還可能注意到：有“小時(shí)”或“時(shí)”的不同情況，比如“1小時(shí)”或“49時(shí)”。
這種問(wèn)題看似很難，無(wú)法立即找到解題的方法。但是相信只要我們掌握了Excel常用的基礎(chǔ)的函數(shù)，并找到科學(xué)的嚴(yán)謹(jǐn)?shù)乃悸?，就可以嘗試解決。
第一步：提取數(shù)字
輸入公式：
=REGEXP(A2,"\d+(?="&{"小|時(shí)","分","秒"}&")")
通過(guò)REGEXP正則表達(dá)式函數(shù)提取“時(shí)/小時(shí)、分、秒”前的數(shù)字。
匹配規(guī)則解釋：
\d+：匹配一個(gè)或多個(gè)數(shù)字。
正則表達(dá)式里的(?=...)是正向肯定預(yù)查，匹配數(shù)字，符合數(shù)字后面跟著的是小時(shí)/時(shí)、分或秒的情況。
- (?=小|時(shí))：匹配“時(shí)”或“小時(shí)”前的數(shù)字（如49時(shí)→49）。符號(hào)“|”是邏輯“或”的意思。
- (?=分)：匹配“分”前的數(shù)字（如25分→25）。
- (?=秒)：匹配“秒”前的數(shù)字（如7秒→7）。
最后返回?cái)?shù)組：{小時(shí), 分, 秒}。比如49時(shí)25分7秒→{49,25,7}，而匹配不到數(shù)字后面跟著的是小時(shí)/時(shí)、分或秒的情況時(shí)，會(huì)返回錯(cuò)誤值。比如：7秒→{#N/A,#N/A,7}。
第二步：缺失單位處理
外嵌IFNA公式：
=IFNA(REGEXP(A2,"\d+(?="&{"小|時(shí)","分","秒"}&")"),)
IFNA函數(shù)有一個(gè)常見(jiàn)用法，當(dāng)?shù)谝粎?shù)為錯(cuò)誤值，且跳過(guò)第2參數(shù)時(shí)，會(huì)返回固定的“0”值。
所以，此步驟的目的是處理未匹配到的時(shí)間單位（如文本中無(wú)“時(shí)”與“分”時(shí)，默認(rèn)補(bǔ)0）。
若某單位未匹配到，對(duì)應(yīng)位置留“0”。比如：7秒→{"0", "0", "7"}
第三步：連接字符串
外嵌EXTJOIN函數(shù)：
=TEXTJOIN(":",,IFNA(REGEXP(A2,"\d+(?="&{"小|時(shí)","分","秒"}&")"),))
TEXTJOIN(":", , ...) 結(jié)構(gòu)功能：
用冒號(hào)“:”連接小時(shí)、分、秒。生成時(shí)間格式文本字符串。
比如：{49,25,7}→49:25:7
再比如：{"0","0","7"}→0:0:7
第四步：文本格式數(shù)值化
輸入公式：
=TEXTJOIN(":",,IFNA(REGEXP(A2,"\d+(?="&{"小|時(shí)","分","秒"}&")"),))*1
...*1 結(jié)構(gòu)功能：
將文本時(shí)間轉(zhuǎn)換為Excel可識(shí)別的數(shù)字序列號(hào)。
Excel將時(shí)間視為小數(shù)（1小時(shí)=1/24，1分鐘=1/1440）。
例如 49:25:7 會(huì)轉(zhuǎn)換為表示總時(shí)長(zhǎng)的數(shù)值"2.0591087962963"。
第五步：格式化為時(shí)間
外嵌TEXT函數(shù)：
=TEXT(TEXTJOIN(":",,IFNA(REGEXP(A2,"\d+(?="&{"小|時(shí)","分","秒"}&")"),))*1,"[H]:mm:ss")
TEXT(..., "[H]:mm:ss") 結(jié)構(gòu)功能：
將序列號(hào)格式化為標(biāo)準(zhǔn)時(shí)間格式。
[H]顯示超過(guò)24小時(shí)的總時(shí)長(zhǎng)（如49小時(shí)直接顯示為49）。
mm和ss補(bǔ)足兩位，如7秒→07。
推薦閱讀：wps怎么排列時(shí)間順序? WPS里利用AI編寫代碼對(duì)行內(nèi)時(shí)間正向排序技巧
相關(guān)文章
wps excel玩轉(zhuǎn)合并數(shù)據(jù)只需要1種符號(hào)+5類函數(shù)+2個(gè)實(shí)操
wps或者excel表格中經(jīng)常設(shè)計(jì)多個(gè)單元格合并到一個(gè)單元格的問(wèn)題，很多朋友不知道遇到不同的情況該怎么合并，下面我們就來(lái)分享用1種符號(hào)，5類函數(shù)，2個(gè)實(shí)操，玩轉(zhuǎn)合并數(shù)據(jù)的2025-07-01Excel和WPS的函數(shù)之爭(zhēng):DATEDIF的YD參數(shù)結(jié)果居然差一天
在Excel表格與WPS表格中，DATEDIF的結(jié)果不一致，我就自己是嘗試了，果然不一致，來(lái)跟大家講解下出來(lái)的原因，你這次支持誰(shuí)呢？詳細(xì)請(qǐng)看下文介紹2025-06-09這些常見(jiàn)應(yīng)用場(chǎng)景你一定會(huì)遇到! excel表格拆分單元格的技巧匯總
日常使用中，wps表格有合并單元格自然就要拆分單元格，但是在拆分前，必須有一個(gè)前提是我們已經(jīng)合并了多個(gè)單元格，如果是單一個(gè)單元格我們是無(wú)法拆分的喲，下面我們就來(lái)看2025-04-01Excel如何快速對(duì)齊小數(shù)點(diǎn)? wps工作表中數(shù)據(jù)以小數(shù)點(diǎn)為標(biāo)準(zhǔn)對(duì)齊的技巧
WPS處理數(shù)據(jù)想要讓數(shù)據(jù)對(duì)齊，那么對(duì)齊的方式有很多種，我們通常運(yùn)用的就有左對(duì)齊、右對(duì)齊、居中和分散對(duì)齊，那么對(duì)于數(shù)字來(lái)說(shuō)，我們也可以做到將數(shù)字用小數(shù)點(diǎn)對(duì)齊，那么具2025-02-11Excel如何整理好雜亂的人名? 人員按歸屬科室在不同列中分別顯示的技巧
醫(yī)院人員很多，排名很混亂，想要將人員按照所屬科室排列顯示，該怎么操作呢？下面我們就來(lái)看看詳細(xì)教程2025-02-11wps Excel多個(gè)數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視表時(shí)怎么自定義頁(yè)字段?
wps中創(chuàng)建的數(shù)據(jù)透視表，想要添加新字段，該怎么自定義字段呢？下面我們就來(lái)看看詳細(xì)的教程2025-02-10wps excel中引用樣式A1和R1C1有什么區(qū)別?
excel單元格的引用方式根據(jù)地址劃分可分為相對(duì)引用、絕對(duì)引用以及混合引用，根據(jù)樣式劃分，可分為A1引用和R1C1引用，下面詳細(xì)介紹2025-02-01excel如何防止輸入空格? wps數(shù)據(jù)有效性防止在指定單元格中輸入空格技
錄入數(shù)據(jù)的時(shí)候，為了對(duì)齊文本，敲幾個(gè)空格，一不小心“手滑”，敲了幾個(gè)空格，等到統(tǒng)計(jì)數(shù)據(jù)時(shí)就麻煩了！那么如何防止他人錄入數(shù)據(jù)時(shí)輸入空格呢？詳細(xì)請(qǐng)看下文介紹2025-04-30wps excel怎么計(jì)算不足2小時(shí)時(shí)間每滿30分鐘計(jì)算一次不足2小時(shí)的全部歸
今天就和大家分享如何按照這樣的規(guī)定計(jì)算加班時(shí)長(zhǎng)，不足2小時(shí)的全部歸零，足2小時(shí)的每滿30分鐘計(jì)算一次，怎么算？多學(xué)一招，提升自己的能力2025-01-31不足1小時(shí)的部分忽略該怎么計(jì)算? wps excel計(jì)算算加班時(shí)長(zhǎng)時(shí)的技巧
加班小時(shí)不足一小時(shí)應(yīng)該如何算工資呢？統(tǒng)計(jì)工作時(shí)間計(jì)算工資，發(fā)現(xiàn)有加班不滿足一小時(shí)的情況，想要將不足一小時(shí)的時(shí)間忽略，該怎么操作呢？詳細(xì)請(qǐng)看下文2025-01-31
