學不完．教不停．用不盡

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

【公式設計與解析】

選取儲存格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)))：原理同上式。