2010年12月29日 星期三

Excel-陣列的應用

在 Excel 中取得一個資料表,其中有多個天數的跑步記錄,如何可以快速求得累計的時間總和呢?並且要能分別呈現時、分、秒。

觀察E欄時發現,其儲存格內容的格式固定,即時間中的分和秒都出現在固定位置。

如果以公式:「VALUE(MID(B2,8,2))」,可以求得儲存格B2中分的數量。

如果以公式:「VALUE(MID(B2,11,2))」,可以求得儲存格B2中秒的數量。

因為MID函數求得的是文字(例如"17”),所以透過VALUE函數轉換成真正的數字(例如17)。

但是有這麼多個儲存格如何一次計算呢?公式改為:

如果以陣列公式:「{SUM(VALUE(MID(B2:B26,8,2)))}」,可以求得所有儲存格中分的數量總和。

如果以陣列公式:「{SUM(VALUE(MID(B2:B26,11,2)))}」,可以求得所有儲存格中秒的數量總和。

接著要如何轉換成時、分、秒的數量呢?

你大概會想要將時間全部換算成秒,再設計公式來求時、分、秒吧?

以下有個巧妙的方式:

儲存格E2:{=HOUR(TIME(0,SUM(VALUE(MID(B2:B26,8,2))),SUM(VALUE(MID(B2:B26,11,2)))))}

儲存格E3:{=MINUTE(TIME(0,SUM(VALUE(MID(B2:B26,8,2))),SUM(VALUE(MID(B2:B26,11,2)))))}

儲存格E4:{=SECOND(TIME(0,SUM(VALUE(MID(B2:B26,8,2))),SUM(VALUE(MID(B2:B26,11,2)))))}

因為TIME函數可以將大於59的分或秒數,自動調整在60以下,所以公式:

TIME(0,分的總和,秒的總和)

其中時的部分以0填入,Excel會自動將分和秒調整成60秒以下。

再透過HOUR、MINUTE和SECOND函數分別取得時、分、秒。

如此,完全不用再寫公式來計算時、分和秒的數量。

很有趣又有效率吧!

沒有留言:

張貼留言

好康東東