網友提問 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))

arrow
arrow
    全站熱搜

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