2016年6月11日 星期六

Excel-由清單摘要計算次數(SUMPRODUCT)

網友問到:如何由 Excel 中的資料清單(如下圖左),摘要各種次數(如下圖右)?
在下圖中中一個「日期、品項、購買人」的清單,如何摘要『品項/購買人』、 『品項/月份』、『購買人/月份』的報表?
Excel-由清單摘要計算次數(SUMPRODUCT)

【公式設計與解析】
(1) 報表一/儲存格F3:
=SUMPRODUCT(($B$2:$B$26=$E3)*($C$2:$C$26=F$2))
條件一:($B$2:$B$26=$E3),判斷儲存格E3和儲存格B2:B26是否相符,傳回 TRUE/FALSE 陣列。
條件二:($C$2:$C$26=F$2)),判斷儲存格F2和儲存格C2:C26是否相符,傳回 TRUE/FALSE 陣列。
在 SUMPRODUCT  公式中的『*』相當於執行邏輯 AND 運算,即條件一和條件二均傳回 TRUE 時,結果才會為 TRUE。並且在『*』運算過程中,會將 TRUE/FALSE 轉換為 1/0。最後再計算 1/0 的和,即為所求(次數)。
複製儲存格F3,貼至儲存格F3:I8。

(2) 報表二/儲存格F13:
=SUMPRODUCT(($B$2:$B$26=$E13)*(MONTH($A$2:$A$26)=COLUMN(C:C)))
公式原理與報表一類似,其中公式:MONTH($A$2:$A$26)=COLUMN(C:C),
MONTH($A$2:$A$26):在陣列中取出儲存格A2:A6中每個儲存格日期的『月份』。
COLUMN(C:C):傳回3,當公式向右複製時,COLUMN(C:C)=3→COLUMN(D:D)=4→COLUMN(E:E)=5→...。
MONTH($A$2:$A$26)=COLUMN(C:C) 即為找出儲存格A2:A26中日期的月份是否為『3』,傳回 TRUE/FALSE 陣列。
複製儲存格F13,貼至儲存格F13:I18。

(3) 報表三/儲存格F23:
=SUMPRODUCT(($C$2:$C$26=$E23)*(MONTH($A$2:$A$26)=COLUMN(C:C)))
原理同報表一和報表二。
複製儲存格F23,貼至儲存格F23:I26。

沒有留言:

張貼留言

好康東東