2016年5月23日 星期一

Excel-計算數個時間的平均值(AVERAGE,LEFT,MID,RIGHT,陣列公式)

有網友問到,如何根據 Excel 的時間清單,來計算這些時間的平均值?如下圖,時間清單可能由數字構成時分秒,也可能由文字構成時分秒,如何來計算其平均時間?
Excel-計算數個時間的平均值(AVERAGE,LEFT,MID,RIGHT,陣列公式)

【公式設計與解析】
1. 時間為數值格式
儲存格B2:=AVERAGE(A2:A18)
如果時間清單中的時間是數值格式,則可以直接以 AVERAGE 函數求得其平均時間,結果也是一個數值。

2. 時間為文字格式
儲存格E2:{=(SUM(VALUE(LEFT(D2:D18,2)))*3600+SUM(VALUE(MID(D2:D18,
4,2)))*60+SUM(VALUE(RIGHT(D2:D18,2))))/(24*60*60)/COUNTA(D2:D18)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1) 時換算秒數:SUM(VALUE(LEFT(D2:D18,2)))*3600
在陣列公式中,使用 LEFT 函數取出每個時間的第1,2碼,計算總和後再乘以3600(一小時有3600秒)。(VALUE 函數用以將文字轉成)
(2) 分換算秒數:SUM(VALUE(MID(D2:D18,4,2)))*60
在陣列公式中,使用 MID 函數取出每個時間的第4,5碼,計算總和後再乘以60(一分鐘有60秒)。
(3) 計算秒數:SUM(VALUE(RIGHT(D2:D18,2)))
在陣列公式中,使用 RIGHT 函數取出每個時間的第7,8碼,計算總和。
(4) 計算平均,公式=(1)+(2)+(3)/(24*60*60)/COUNTA(D2:D18)
(1)+(2)+(3)/(24*60*60):將時間總和換算為秒數。
然後,再除以COUNTA(D2:D18)(計算個數),求得平均值。

沒有留言:

張貼留言

檢視其他文章

好康東東