2011年4月15日 星期五

Excel-陣列應用

在 Excel 的工作表中取得一個跑步記錄表(如下圖左),利用B欄的資料要來做統計分析(如下圖右)。B欄中的資料格式固定,透過陣列的應用,只要分別以一行公式即可算出各個統計結果。

假設B欄的資料在儲存格B2:B50中,輸入以下的公式:

(以下公式全為陣列公式,輸入完成要按 Ctrl+Shift+Enter。)

(1) 最多圈

儲存格D3:{=MAX(VALUE(MID(B2:B50,3,2)))}

因為圈數位於B欄的第3,4個字元,所以透過MID函數取得,但必須再經過VALUE函數轉換為數字才能運算。

VALUE(MID(B2:B50,3,2))可以取得所有圈數的陣列,經過MAX函數取得最大值。

(2) 最長時間

儲存格D5:{=INT(MAX(MID(B2:B50,11,2)*60+MID(B2:B50,14,2))/60)&"分"&MOD(MAX(MID(B2:B50,11,2)*60+MID(B2:B50,14,2)),60)&"秒"}

利用MID(B2:B50,11,2)取得「分數」,再乘以60轉換為秒數。MID(B2:B50,14,2)取得秒數,再和前者相加得到總秒數。

MAX(MID(B2:B50,11,2)*60+MID(B2:B50,14,2))/60)可以取得總秒數陣列中的最大值。

因為要顯示「幾分幾秒」的格式,所以要再一次轉換:

利用INT(總秒數/60)可以求得「分數」(相當於求商),MOD(總秒數,60)可以求得秒數(相當於求餘數)。

(3) 速度最快

儲存格D7:{=MAX(MID(B2:B50,3,2)*150/(MID(B2:B50,11,2)*60+MID(B2:B50,14,2)))}

(4) 速度最慢

儲存格D9:{=MIN(MID(B2:B50,3,2)*150/(MID(B2:B50,11,2)*60+MID(B2:B50,14,2)))}

(5) 平均速度(指所有速度(=距離/時間)的平均,每一圈以150公尺計算。)

儲存格D11:{=AVERAGE(MID(B2:B50,3,2)*150/(MID(B2:B50,11,2)*60+MID(B2:B50,14,2)))}

(6) 設定速度的顯示單位

因為速度要顯示單位「m/s」,所以透過儲存格格式自訂:「0.00_ " m/s"」,即可在代入數字後,自動會補上單位。

沒有留言:

張貼留言

檢視其他文章

好康東東