學不完．教不停．用不盡

在 Excel 裡，如何根據「XX天XX時XX分」格式來計算加總的天、時、分？

如下圖，鐵定不能直接加總！所以必須經過一些轉換的程序，所以需要一些輔助欄位。

從原始時間列表看來，可能的格式包含：「XX天XX時XX分」、「XX時XX分」、「XX分」，如何來設計公式？

【公式設計與解析】

1. 調整時間表示

儲存格B2：=SUBSTITUTE(IF(SUBSTITUTE(A2,"時","")<>A2,SUBSTITUTE(A2,"時",
":"),"0:"&A2),"分","")

複製儲存格B2，貼至儲存格B2:B11。

利用 SUBSTITUTE 函數將「時」和「分」文字刪掉，並且在沒有「時」的資料上加上「0:」。

2. 取出「天」

儲存格C2：=IFERROR(MID(A2,1,FIND("天",A2)-1),0)*1

複製儲存格C2，貼至儲存格C2:C11。

利用 FIND 函數找到「天」的位置，並利用 MID 函數取出「天」之前的文字。

因為可能儲存格裡沒有「天」的資料，所以會傳回錯誤訊息，因此再以 IFERROR 函數將其顯示為「0」。

3. 取出「時:分」

儲存格D2：=MID(B2,IFERROR(FIND("天",B2)+1,1),99)*1

複製儲存格D2，貼至儲存格D2:D11。

利用 FIND 函數找到「天」的位置，並利用 MID 函數將「天」以後的內容全數取出。

4. 小計「天」

儲存格C12：=SUM(C2:C11)

利用 SUM 函數加總「天」數。

5. 小計「時:分」

儲存格D12：=SUM(D2:D11)

利用 SUM 函數加總「時:分」數。

6. 總計天和時:分

儲存格C13：=(SUM(C2:C11)+INT(SUM(D2:D11)))&"天"&HOUR(MOD(SUM(D2:D11),1))&"時"&MINUTE(MOD(SUM(D2:D11),1))&"分"

(1) SUM(C2:C11)+INT(SUM(D2:D11))

利用 INT 函數取出「時:分」的加總結果中的「天」，即不包含「時:分」的部分。

再和儲存格C2:C11的天數加總。

(2) HOUR(MOD(SUM(D2:D11),1))

利用 MOD 函數取出「時:分」的加總結果中的「時:分」，即不包含「天」的部分。

再以 HOUR 函數取出「時」。

(3) MINUTE(MOD(SUM(D2:D11),1))

利用 MOD 函數取出「時:分」的加總結果中的「時:分」，即不包含「天」的部分。

再以 MINUTE 函數取出「分」。