網友問到:如何由 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。

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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