Excel | SUMPRODUCT計算指定年份與月份的銷售總額

  問題來源

後台一位朋友說遇到難題:公司要求把近三年的銷售額按照年份與月份進行匯總,三年的銷售數據有四萬多行,他問韓老師有沒有函數可以做到快速統計。

韓老師用下圖所示的簡單數據,講一下匯總方法:

Excel | SUMPRODUCT計算指定年份與月份的銷售總額

關鍵操作

公式:

在F2單元格輸入公式:

=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)=E2)*($B$2:$B$15)),向下填充,即得所有指定年份與月份的銷售總額。

公式分解:

(YEAR($A$2:$A$15)=D2):

YEAR函數計算$A$2:$A$15單元格的年份,並與D2單元格的年份進行比較,如果等於D2年份,返回TURE,否則返回FALSE。所以此部分返回一組TURE與FALSE的數組(數組1):

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}。

(MONTH($A$2:$A$15)=E2):

MONTH函數計算$A$2:$A$15單元格的月份,並與E2單元格的月份進行比較,如果等於E2月份,返回TURE,否則返回FALSE。所以此部分返回一組TURE與FALSE的數組(數組2):

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}。

($B$2:$B$15):

{182865;78980;217647;151397;118792;172058;1818;42762;51971;12345;46367;151659;184879;62688}。

=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)=E2)*($B$2:$B$15)):

相當於=SUMPRODUCT((數組1)*(數組2)*($B$2:$B$15)),數組1、數組2與($B$2:$B$15)對應位置相乘然後相加和。

結果:

Excel | SUMPRODUCT計算指定年份與月份的銷售總額

素材下載

鏈接:http://pan.baidu.com/s/1i5ghdzB

密碼:bxuo