2010年6月22日 星期二

Excel-使用SUMPRODUCT製作摘要表

在 Excel 中,SUMPRODUCT函數對於製作數字統計相關的摘要表非常容易,再來練習以下的報表製作:

(1) 在B欄要計算每個人的請假次別(不管何種假別):

儲存格B1:輸入「第1次」

儲存格B2:="第"&COUNTIF(A$2:A3,A3)&"次"

複製儲存格B2到儲存格B2:B21。

利用COUNTIF函數計算由第一個人次開始的請假次數,所以A$2:A3中的A$2採用絕對位址表示。

 

(2) 在F欄中要根據D欄中的假別,計算已累計請假多少時數:

儲存格F2:=E2

儲存格F3:=SUMIF(D2:$D$3,D3,E2:$E$3)

複製儲存格F3到儲存格F3:F21。

 

(3) 計算每個人對應的假別各是多少時數

儲存格I2:=SUMPRODUCT(($A$2:$A$21=$H2)*($D$2:$D$21=I$1)*($E$2:$E$21))

複製儲存格I2到儲存格I2:K6。

在SUMPRODUCT函數中使用「*」執行乘法,如此可以將($A$2:$A$21=$H2)和($D$2:$D$21=I$1)的邏輯運算結果(True、False)轉換成數字(1、0),以和($E$2:$E$21)執行運算。

 

(4) 計算每個人對應的每個月請的時數

儲存格I9:=SUMPRODUCT(($A$2:$A$21=$H9)*($D$2:$D$21=I$1)*($E$2:$E$21))

複製儲存格I9到儲存格I9:K13。

原理同(3)

 

(5) 計算每個月中的各種假別的時數

儲存格I16:

=SUMPRODUCT((MONTH($C$2:$C$21)=VALUE(I$15))*($D$2:$D$21=$H16)*($E$2:$E$21))

複製儲存格I16到儲存格I16:K19。

原理同(3),其中MONTH函數為取出日期的月份,而VALUE(I$15)的用意是要將15列中的月份轉換為數值。

因為在15列中的月份被設定了自訂格式:「@"月"」。

1 則留言:

  1. 感謝您~~~~真是非常有用的函數,解決頭痛的問題了!
    好像(4)每人對應月份請假日數的公式跟(3)重複了?
    是否要改成 =SUMPRODUCT(($A$2:$A$21=$H9)*(MONTH($C$2:$C$21)=VALUE(I$8))*($E$2:$E$21))
    不好意思,不是來踢館的,請格主別誤會喔。

    回覆刪除

檢視其他文章

好康東東