很多網友對 SUMPRODUCT 函數的使用很有興趣,而這個函數的確是很好用。

SUMPRODUCT 函數可以看成是 SUM+PRODUCT 的運作,這是一個「乘積和」的概念。兩個陣列參數,先兩兩相乘,再予以加相(參考下圖)。

Excel-運用SUMPRODUCT函數計算個數和總和

Excel-運用SUMPRODUCT函數計算個數和總和

當我們以在 SUMPRODUCT 函數中加入條件判斷時,因為判斷式會傳回 TRUE/FALSE 陣列,通常再執行算數運算(例如:「*1、+0、-0、/1」等),可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。

在公式中執行例如:(B4:G4>50)*B4:G4,其中的「*」相當於執行邏輯 AND 運算。

Excel-運用SUMPRODUCT函數計算個數和總和

 

以下來練習以 SUMPRODUCT 函數來分別計算個數和總和。

【例:計算一列中大於50的個數和總和】

Excel-運用SUMPRODUCT函數計算個數和總和

大於50的個數:=SUMPRODUCT((B4:G4>50)*1)

SUMPRODUCT((B4:G4>50)*1)

=SUMPRODUCT({False,True,False,True,False,False}*1)

=0+1+0+1+0+0

=2

大於50的總和:=SUMPRODUCT((B4:G4>50)*B4:G4)

SUMPRODUCT((B4:G4>50)*B4:G4)

=SUMPRODUCT({False,True,False,True,False,False}*B4:G4)

=0*10+1*70+0*40+1*80+0*20+0*40

=150

 

【例:計算一欄中大於50的個數和總和】

Excel-運用SUMPRODUCT函數計算個數和總和

大於50的個數:=SUMPRODUCT((D2:D7>50)*1)

大於50的總和:=SUMPRODUCT((D2:D7>50)*D2:D7)

 

【例:計算矩陣中大於50的個數和總和】

Excel-運用SUMPRODUCT函數計算個數和總和

大於50的個數:=SUMPRODUCT((B2:G7>50)*1)

大於50的總和:=SUMPRODUCT((B2:G7>50)*B2:G7)

學不完.教不停.用不盡文章列表

    文章標籤

    Excel SUMPRODUCT

    全站熱搜

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