2014年8月13日 星期三

Excel-依年/月統計各類假別的次數(SUMPRODUCT)

有網友問到,如下圖的 Excel 資料表,如何根據下圖左的出差記錄(只顯示部分資料),摘要依假別分年/月統計次數?(參考下圖右)

【準備工作】

選取A欄至D欄中有資料的區域,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:日期、早班、午班、晚班。

【輸入公式】

儲存格H2:
=SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(早班=H$1))+
  SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(午班=H$1))+
  SUMPRODUCT((YEAR(日期)=$F2)*(MONTH(日期)=$G2)*(晚班=H$1))

其中:

條件(1)  YEAR(日期)=$F2:判斷在日期陣列的年份(2014年)是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

條件(2)  MONTH(日期)=$G2:判斷在日期陣列的月份(1份)是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

條件(3)  早班=H$1:判斷在早班陣列中的內容是否和儲存格F2(出差)相同,傳回 TRUE/FALSE 陣列。

公式中的「*」會在運算時將 TRUE/FALSE 陣列轉換為 1/0 陣列。

午班=H$1:判斷在午班陣列中的內容是否和儲存格F2(午班)相同,傳回 TRUE/FALSE 陣列。

晚班=H$1:判斷在晚班陣列中的內容是否和儲存格F2(晚班)相同,傳回 TRUE/FALSE 陣列。

複製儲存格H2,貼至儲存格H2:J25。

沒有留言:

張貼留言

檢視其他文章

好康東東