2016年3月10日 星期四

Excel-根據月份和人員計算小計(SUMPRODUCT,MONTH)

有網友問到:根據下圖,在 Excel 中如何根據月份和人員計算對應的小計?其中日期並未依照月份的順序,經手人的順序也未經排序。
Excel-根據月份和人員計算小計(SUMPRODUCT,MONTH)

【公式設計與解析】
為了讓公式易於閱讀,先設定名稱:
選取儲存格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)再和金額相乘的和,即為所求。
【註】本例中的公式,可以在日期和經手人的順序,在已排序和未排序的狀況下,都會得到相同結果。

沒有留言:

張貼留言

檢視其他文章

好康東東