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

Excel-利用時間清單計算時間總和

 

【方式一】

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

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 函數計算迄時減掉起時的陣列和。

 

【方式二】

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

Excel-利用時間清單計算時間總和

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 函數,即可求得時間總和。

 

【方式三】

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

Excel-利用時間清單計算時間總和

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 函數計算迄時減掉起時的陣列和。

 

【參考資料】

image SUMPRODUCT 函數參考微軟提供的說明網頁:SUMPRODUCT 函數
image TIME 函數參考微軟提供的說明網頁:TIME 函數
image FIND,FINDB 函數參考微軟提供的說明網頁:FIND,FINDB 函數
 

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel 時間總和
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()