編按：

有一組關(guān)于時長的數(shù)據(jù)，我想把它們統(tǒng)一轉(zhuǎn)換成以小時為單位，該如何做？

案例數(shù)據(jù)如下圖，其中，A列中的數(shù)據(jù)就是關(guān)于時長的數(shù)據(jù)記錄。從數(shù)據(jù)的格式方面來看，并不是所有數(shù)據(jù)都按照“XX天XX小時XX分鐘XX秒”這種比較規(guī)范的格式來錄入的，數(shù)據(jù)“混搭”比較嚴(yán)重。

今天，讓我們一起來學(xué)習(xí)一下此類問題的處理方法。

首先，我們先來分析一下時間的換算關(guān)系。以A2中的數(shù)據(jù)為例，11天21小時16分鐘52秒如果想換算成小時的話，應(yīng)該是這樣的一個換算方式：11*24+21*1+16/60+52/3600。具體的邏輯為：在天數(shù)方面，一共是11天，每天有24小時，所以11天對應(yīng)的小時數(shù)為11*24=264小時；在小時方面，一共是21小時，所以用小時數(shù)乘1就行了，也就是21*1=21小時；在分鐘方面，一共是16分鐘，而一小時有60分鐘，所以用分鐘數(shù)除以60就可以將分鐘轉(zhuǎn)換為小時，即16分鐘＝16/60＝0.26666667小時；在秒方面，一共是52秒，而一小時有3600秒，所以我們用秒數(shù)除以3600就可以將秒轉(zhuǎn)換成小時，即52秒＝52/3600=0.01444444小時。最后，我們再計(jì)算一下總和，就可以換算成小時數(shù)了。

為了便于大家理解，阿碩對本例中每一個時長的運(yùn)算邏輯做了整理，如下圖所示。在這里有一點(diǎn)請大家注意，由于小時數(shù)乘以1還是等于其自身，所以在下表以及后續(xù)的操作中，我們對小時不做乘1的處理。

好了，講完了時間換算的邏輯，就可以得出解決今天這個問題的思路了：先將原始數(shù)據(jù)轉(zhuǎn)換成運(yùn)算表達(dá)式（使用SUBSTITUTE函數(shù)），然后再對這個運(yùn)算表達(dá)式進(jìn)行求和（使用EVALUATE函數(shù)）。

下面，就讓我們一起來操作吧！

1.使用SUBSTITUTE函數(shù)替換“天”

我們在B2中輸入“=SUBSTITUTE(A2,"天","*24+")”，然后向下復(fù)制填充公式，得到的結(jié)果如下圖所示。可以看到，A2中的“11天”變成了“11*24+”，這就為天數(shù)的轉(zhuǎn)換做好了準(zhǔn)備。小伙伴們在這里一定要注意一點(diǎn)，那就是在24后面還要有一個加號（“+”）。因?yàn)槿绻粚戇@個加號的話，我們構(gòu)造的表達(dá)式在后續(xù)的運(yùn)算中就會出問題啦！

2.使用SUBSTITUTE函數(shù)替換“小時”

將天數(shù)替換完成之后，我們再來替換小時。這時，我們使用函數(shù)嵌套來進(jìn)行操作。我們使用上一步驟中的SUBSTITUTE函數(shù)，作為本步驟中SUBSTITUTE函數(shù)的第一參數(shù)。

我們將B2中的公式修改為“=SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小時","+")”，然后向下復(fù)制填充公式，得到的結(jié)果如下圖所示?？梢钥吹?，A2中的“21小時”變成了“21+”，這就為小時的轉(zhuǎn)換做好了準(zhǔn)備。（注：將“天”替換成“*1+”也是可以的哦~）

3.使用SUBSTITUTE函數(shù)替換“分鐘”

將小時替換完成之后，我們再來替換分鐘。我們使用SUBSTITUTE函數(shù)嵌套來進(jìn)行操作。我們將B2中的公式修改為“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小時","+"),"分鐘","/60+")”，然后向下復(fù)制填充公式，得到結(jié)果的如下?？梢钥吹?，A2中的“16分鐘”變成了“16/60+”，這就為分鐘的轉(zhuǎn)換做好了準(zhǔn)備。

4.使用SUBSTITUTE函數(shù)替換“秒”

將分鐘替換完成之后，我們再來替換秒，我們繼續(xù)使用SUBSTITUTE函數(shù)嵌套。由于“秒”是我們最后一個要替換的時間單位，所以在直覺上，我們的第一想法是只要將“秒”替換成“/3600”就行了，不需要再額外寫一個加號。我們把B2中的公式修改為“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小時","+"),"分鐘","/60+"),"秒","/3600")”，然后向下復(fù)制填充公式，得到的結(jié)果如下圖所示。

此時，我們再觀察一下數(shù)據(jù)，可以發(fā)現(xiàn)，B6、B9、B10、B11單元格均以加號作為結(jié)尾，對于這樣的邏輯表達(dá)式，如果直接進(jìn)行運(yùn)算的話，是會產(chǎn)生問題的?？磥碇庇X不是太可靠，我們還需要再改進(jìn)一下函數(shù)。

該如何改進(jìn)呢？首先，我們在“3600”后面加上一個加號試試。我們將B2中的公式修改為“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小時","+"),"分鐘","/60+"),"秒","/3600+")”，得到的結(jié)果如下圖所示。

由上圖可以看到，所有的邏輯表達(dá)式的最后一個字符都變成加號了。不過大家別緊張，我們再微調(diào)一下函數(shù)，就可以輕松搞定！我們知道，任何數(shù)據(jù)加上0之后，數(shù)據(jù)還是等于其本身，所以，我們再通過連接運(yùn)算符（“&”），在數(shù)據(jù)后面連接上一個0，這樣可以既保證運(yùn)算結(jié)果不變，又能夠完美地解決了數(shù)據(jù)以加號作為結(jié)尾的問題。我們將B2中公式修改為“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小時","+"),"分鐘","/60+"),"秒","/3600+")&0”，得到的結(jié)果如下圖所示。

5.使用EVALUATE函數(shù)計(jì)算時間運(yùn)算表達(dá)式

在前述4步中，我們已經(jīng)構(gòu)造了一個關(guān)于時間轉(zhuǎn)換的邏輯表達(dá)式。想要對這個表達(dá)式求和的話，我們要用到一個宏表函數(shù)——EVALUATE函數(shù)。

EVALUATE是常用的宏表函數(shù)，它的作用是對以文本表示的一個公式或表達(dá)式求值，并返回結(jié)果。它的語法結(jié)構(gòu)比較簡單——只需要把要計(jì)算的表達(dá)式放在EVALUATE后面的括號內(nèi)，就行了，如下：

=EVALUATE(formula_text)。

其中，formula_text是一個以文本形式表示的表達(dá)式。

在使用宏表函數(shù)時，有一點(diǎn)要注意：宏表函數(shù)只能通過定義名稱來使用。下面，讓我們來學(xué)習(xí)具體操作。

在進(jìn)行定義名稱的操作之前，請大家先將B2中的公式等號（“=”）之后內(nèi)容復(fù)制一下（即“SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"天","*24+"),"小時","+"),"分鐘","/60+"),"秒","/3600+")&0”）。這是因?yàn)?，在接下來我們定義名稱的時候，在EVALUATE函數(shù)前面也會有等號，如果此時把SUBSTITUTE嵌套函數(shù)前面的等號也復(fù)制過去的話，就會出現(xiàn)兩個等號了，還要再額外進(jìn)行刪除操作。

復(fù)制完公式之后，我們按Ctrl+F3，則會彈出名稱管理器，如下圖所示。

點(diǎn)擊“新建”，彈出“新建名稱”對話框，如下圖所示。

接下來，我們來對“名稱”和“引用位置”這兩處進(jìn)行修改。我們將“名稱”修改為“時間轉(zhuǎn)換”；在“引用位置”后面的輸入框中，輸入“=evaluate()”，然后將剛才我們復(fù)制的那一長串不包含等號、層層嵌套的SUBSTITUTE函數(shù)粘貼到EVALUATE函數(shù)的括號中，如下圖所示。（注：由于函數(shù)太長，所以截圖不全，感興趣的小伙伴可以拖動鼠標(biāo)進(jìn)行查看）

點(diǎn)擊“確定”后，再次回到“名稱管理器”對話框。可以看到，其中多出了“時間轉(zhuǎn)換”這樣一個自定義名稱，如下圖所示。然后，我們點(diǎn)擊“關(guān)閉”，將名稱管理器關(guān)閉即可。

接下來，就是見證奇跡的時刻了！我們在B2中輸入“=時間轉(zhuǎn)換”，然后向下復(fù)制填充公式，就可以得到我們一直苦苦追尋的以小時為單位的時間啦，如下圖所示。

小彩蛋：

在完成時間的轉(zhuǎn)換之后，再為大家補(bǔ)充一個小彩蛋。EVALUATE函數(shù)是宏表函數(shù)，而宏表函數(shù)的在保存的時候，有一點(diǎn)需要注意。

我們現(xiàn)在點(diǎn)擊“保存”按鈕，則會發(fā)現(xiàn)，彈出了一個對話框，如下圖所示。

此時，大家一定要選擇“否”，并在接下來彈出的界面中，將“保存類型”選擇為“Excel 啟用宏的工作薄”，然后點(diǎn)擊“保存”，如下圖所示。（注：如果在此處選擇“是”，再打次開Excel時，則會發(fā)現(xiàn)，宏表函數(shù)無法再次使用了）

點(diǎn)擊“保存”后，Excel將包含宏表函數(shù)的表為我們進(jìn)行了另存，另存成的表格是一個啟用宏的Excel文件，其數(shù)據(jù)格式為“.xlsm”。小伙伴們?nèi)绻信d趣，可以去看一下這個新生成的Excel的圖標(biāo)，它和我們常見的Excel圖標(biāo)還是略有不同的，如下圖左側(cè)所示。

小伙伴們，今天的學(xué)習(xí)內(nèi)容就是這些，你學(xué)會了嗎？

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù)，非得用VBA才能實(shí)現(xiàn)嗎？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

4種刪除excel重復(fù)值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能！

版權(quán)申明：

本文作者阿碩；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。