2016年1月13日 星期三

Excel-依日期中的月份對個人小計(SUMPRODUCT)

網友問到:在 Excel 的工作表中有一個資料清單(如下圖左),想要依『日期』欄位來統計每個人員在各個月份的時數小計(如下圖右),該如何處理?
Excel-依日期中的月份對個人小計(SUMPRODUCT)

【公式設計與解析】
選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、時數。
表<1>
儲存格F2:
=SUMPRODUCT((人員=$E2)*(VALUE(LEFT(日期,2))=COLUMN(A:A))*時數)
LEFT(日期,2):取出『日期』陣列每個儲存格左邊 2 個字(表示月份),取出的資料為『文字』型態。
VALUE(LEFT(日期,2)):利用 VALUE 函數將上式的文字轉換為『數字』型態。
COLUMN(A:A):向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:CA)=3→...。
表<2>
儲存格F15:
=SUMPRODUCT((人員=F$14)*(VALUE(LEFT(日期,2))=ROW(1:1))*時數)
ROW(1:1):向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

【延伸學習】
公式=SUMPRODUCT((人員=F$14)*(VALUE(LEFT(日期,2))=ROW(1:1))*時數)
公式中的運算子『*』,會在運算過程中將 TRUE/FALSE 陣列藉由運算轉換為 1/0 陣列。
相當於:
公式=SUMPRODUCT(--(人員=F$14),--((VALUE(LEFT(日期,2))=ROW(1:1))),時數)
--(人員=F$14):將 TRUE/FALSE 陣列藉由運算轉換為 1/0 陣列。
--((VALUE(LEFT(日期,2))=ROW(1:1))):原理同上式。

沒有留言:

張貼留言

檢視其他文章

好康東東