學不完．教不停．用不盡

有網友問到：根據下圖，在 Excel 中如何根據月份和人員計算對應的小計？其中日期並未依照月份的順序，經手人的順序也未經排序。

 

【公式設計與解析】

為了讓公式易於閱讀，先設定名稱：

選取儲存格A1:C24，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：接件日期、金額、經手人。

儲存格F2：=SUMPRODUCT((MONTH(接件日期)=ROW(1:1))*(經手人=F$1)*金額)

複製儲存格F2，貼至儲存格F2:H13。

公式中用到二個條件判斷：

(1)

(MONTH(接件日期)=ROW(1:1)：這個條件是利用 MONTH 函數先找出接件日期陣列中每個日期的月份值，一月傳回 1、二月傳回 2、…、十二月傳回 12。再來和 ROW 函數的傳回值比對，其中當公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ … →ROW(12:12)=12。

(2)

(經手人=F$1)：判斷經手人陣列是否和儲存格F1內容相符，傳回 TRUE／FALSE 陣列。

 

在 SUMPRDOCUT 函數中：

「(MONTH(接件日期)=ROW(1:1))*(經手人=F$1)*金額」運算時，其中「*」運算會將 TRUE／FALSE 陣列轉換為 1／0 陣列，結果相當於執行邏輯 AND 運算。即二個條件都成立者(AND運算為二個參數都為TRUE時，結果為TRUE)再和金額相乘的和，即為所求。

【註】本例中的公式，可以在日期和經手人的順序，在已排序和未排序的狀況下，都會得到相同結果。