贊助廠商

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

搜尋本部落格文章資料

在 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((MONTH($C$2:$C$21)=VALUE(I$8))*($A$2:$A$21=$H9)*($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列中的月份被設定了自訂格式:「@"月"」。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 悄悄話
  • 訪客
  • =SUMPRODUCT((MONTH($C$2:$C$21)=VALUE(I$15))*($D$2:$D$21=$H16)*($E$2:$E$21))

    應改成

    =SUMPRODUCT((MONTH($C$2:$C$21)=VALUE(I$15))*($A$2:$A$21=$H16)*($E$2:$E$21)) 才對 ~@@~
  • HI~請問I9儲存格,函數好像有誤,數字是錯的,可否告知修正方式~謝謝
  • HI~請問I9儲存格,函數好像有誤,數字是錯的,可否告知修正方式~謝謝
  • 儲存格I9:=SUMPRODUCT((MONTH($C$2:$C$21)=VALUE(I$8))*($A$2:$A$21=$H9)*($E$2:$E$21))

    vincent 於 2017/07/28 21:46 回覆

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼