學不完．教不停．用不盡

在 Excel 裡，有一個時間的清單，如下圖，要如何計算時間總和？

【方式一】

由起時和迄時來找出每一筆時間的「時」和「分」，再進行加總。

1. 儲存格C2：=HOUR(B2-A2)

利用 HOUR 函數，由迄時減掉起時，再取出「時」數。

2. 儲存格D2：=MINUTE(B2-A2)

利用 MINUTE 函數，由迄時減掉起時，再取出「分」數。

3. 儲存格F3：=TIME(SUM(C2:C19),SUM(D2:D19),)

將1.和2.傳回的時數和分數代入 TIME 函數求得時間數。

4. 儲存格F3：=SUMPRODUCT(B2:B19-A2:A19)

也可以利用 SUMPRODUCT 函數計算迄時減掉起時的陣列和。

【方式二】

由文字的時間求得「時」和「分」，再進行加總。

1. 儲存格B2：=IFERROR(MID($A2,FIND(B$1,$A2)-2,1)*1,"")&IFERROR(MID($A2,FIND(B$1,$A2)-1,1)*1,"")

(1) MID($A2,FIND(B$1,$A2)-2,1)*1,"")

求得「時」的往前2個位置的數字。

(2) MID($A2,FIND(B$1,$A2)-1,1)*1,"")

求得「時」的往前1個位置的數字。

將(1)和(2)串接即為「時」數。

2. 儲存格C2：=IFERROR(MID($A2,FIND(C$1,$A2)-2,1)*1,"")&IFERROR(MID($A2,FIND(C$1,$A2)-1,1)*1,"")

原理同「1.」，可求得「分」數。

3. 儲存格E3：=TIME(SUMPRODUCT(B2:B19*1),SUMPRODUCT(C2:C19*1),)

(1) SUMPRODUCT(B2:B19*1)

求得「時」數的總和。

(2) SUMPRODUCT(C2:C19*1)

求得「分」數的總和。

(3) 將(1)和(2)代入 TIME 函數，即可求得時間總和。

【方式三】

由「起時」和「迄時」求得「歷時」，再計算加總時數。

1. 儲存格C2：=HOUR(B2-A2)&"時"&" "&MINUTE(B2-A2)&"分"

B2-A2可求得歷時，再由 HOUR 函數取得「時」數，再由 MINUTE 函數取得「分」數。

2. 儲存格E3：=TIME(SUMPRODUCT(LEFT(C2:C19,FIND("時",C2:C19)-1)*1),SUMPRODUCT(MID(C2:C19,FIND("分",C2:C19)-2,2)*1),)

(1) SUMPRODUCT(LEFT(C2:C19,FIND("時",C2:C19)-1)*1)

在 SUMPRODUCT 函數裡將儲存格C2:C19「時」的往前2個位置的數字予以加總。

(2) SUMPRODUCT(MID(C2:C19,FIND("分",C2:C19)-2,2)*1),)

在 SUMPRODUCT 函數裡將儲存格C2:C19裡「時」的往前1個位置的數字予以加總。

3.儲存格E3：=SUMPRODUCT(B2:B19-A2:A19)

利用 SUMPRODUCT 函數計算迄時減掉起時的陣列和。

【參考資料】

 SUMPRODUCT 函數參考微軟提供的說明網頁：SUMPRODUCT 函數

 TIME 函數參考微軟提供的說明網頁：TIME 函數

 FIND,FINDB 函數參考微軟提供的說明網頁：FIND,FINDB 函數