2009年10月7日 星期三

Excel-製作生活記錄摘要

假設想要將日常生活的一些事項加以記錄,並且以週為單位來統計和分析,產生一個摘要表,試著以Excel來練習看看。

例如:(參考下圖)

先建好第一組日期,以下的日期則間隔7,即A8儲存格的公式為「=A3+7」;A10儲存格的公式為「=A5+7」,再往下複製。

記錄時以「V」或「X」標示是否執行,而時數部分以數字表示。

並且以格式化條件方式設定輸入「X」時,以灰色顯示(用以凸顯紅色字部分)。

其中小計儲格存J2的公式為「{=SUM(IF(C2:I2="V",1,0))}」,用以計算幾個「V」的數量。

image1

接著要自動轉換成下表:

image2 

其中:

儲存格L1的公式為「="摘要表(累積日數:"&TODAY()-A3+1&")"」

儲存格M3的內容為陣列公式「{=SUM(IF($B$2:$B$116=$L3,IF(C$2:C$116="V",1,0)))}」

分別複製到L3:S4和M6:S7。

儲存格M5的內容為「{=IF(ISERROR(AVERAGE(IF($B$2:$B$116=$L5,IF(C$2:C$116<>0,C$2:C$116)))),0,AVERAGE(IF($B$2:$B$116=$L5,IF(C$2:C$116<>0,C$2:C$116))))}」

其中有考量到儲存格內容全為「空白」時,Average公式產生的錯誤訊息。

再複製到N5:S5,分別計算星期日到星期六的每天平均。

而儲存格U3要計算比例,其儲存格公式為「=T3/(TODAY()-$A$3+1)」

即以小計值來除以累積日數。

利用相同觀念,可以擴充記錄更多的項目,也能順利產生摘要表。

沒有留言:

張貼留言

好康東東