贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

有網友問到,如何根據 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)(計算個數),求得平均值。

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

有網友想要利用 Excel 在一個已經依照組別排列的清單中,自動計算小計,該如何處理?而小計只出現在該組的最後一筆位置。

觀察下圖中,各個組別最後一筆會有對應的一筆小計。

Excel-依組別在最後一筆資料計算小計(SUMIF,SUMPRODUCT)

 

【公式設計與解析】

(1) 使用 SUMPRODUCT 函數

透過 SUMPRODUCT 函數運算,當資料內容變動時,小計可以隨之變動。

儲存格D2:=IF(A2<>A3,SUMPRODUCT(($A$2:A2=A2)*$C$2:C2),"")

複製儲存格D2,貼至儲存格D2:D26。

SUMPRODUCT(($A$2:A2=A2)*$C$2:C2):計算從A欄的第一筆資料(組別)和所在儲存格符者,計算對應C欄(銷售量)的和,此為該組的小計。

然後,在 IF 函數中判斷 A2<>A3 是否成立,若成立表示為儲存格A2和儲存格A3為不同組別,則顯示小計算結果,若不成立,則顯示空白。

 

(2) 使用 SUMIF 函數

除了使用 SUMPRODUCT 函數,你也可以使用 SUMIF 函數,可以得到相同的結果。

儲存格D2:=IF(A2<>A3,SUMIF($A$2:A2,A2,$C$2:C2),"")

 

(3) 利用小計工具

因為資料已經依組別排序完成,所以你也可以使用 Excel 提供的小計工具,手動得到的結果(如下圖)。使用小計時的設定:

分組小計欄位:組別

使用函數:加總

新增小計位置:銷售量

image

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

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼