網友問到:在 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))):原理同上式。

全站熱搜

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