2016年9月15日 星期四

Excel-依日期清單分月計算不同項目的數量(SUMPRODUCT)

網友提問 Excel 的問題:想要統計公司裡的三間工廠於一年中各個月的離職人數,該如何處理?
如下圖,有一個日期清單,在工廠欄位中有A廠、B廠、C廠三種可能,如何能建立分月/分廠的分析報表?
Excel-依日期清單分月計算不同項目的數量(SUMPRODCUT)

【公式設計與解析】
1. 定義儲存格範圍名稱
選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:「日期、工廠」。
2. 輸入公式
儲存格E2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(工廠=E$1))
複製儲存格E2,貼至儲存格E2:G13。
MONTH(日期):利用 MONTH 函數取出日期陣列中的每個日期的月份。
ROW(1:1):當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ ...→ ROW(12:12)=12。
條件一「MONTH(日期)=ROW(1:1)」:判斷日期的月份是否為一月(ROW(1:1)=1),傳回陣列 TRUE/FALSE
條件二「工廠=E$1」:判斷工廠的陣列中是否和儲存格E1(=A廠)內容是符,傳回陣列TRUE/FALSE
(MONTH(日期)=ROW(1:1))*(工廠=E$1):運式時 TRUE/FALSE 會轉換為 1/0 陣列。而兩個條件中的「*」,等同於執行邏輯 AND 運算,
最後,透過 SUMPRODUCT 運算二個條件傳回值(1/0 陣列)的乘積和,即為所求。

【附註】
為了幫助讀者理解,列出複製儲存格E2公式後的結果:
(1) 儲存格F2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(工廠=F$1))
(2) 儲存格G2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(工廠=G$1))
(3) 儲存格E3:=SUMPRODUCT((MONTH(日期)=ROW(2:2))*(工廠=E$1))
(4) 儲存格E4:=SUMPRODUCT((MONTH(日期)=ROW(3:3))*(工廠=E$1))

沒有留言:

張貼留言

檢視其他文章

好康東東