excel中Sumproduct函數(shù)的運用方法
excel中Sumproduct函數(shù)的運用方法
以往,為如何多條件求和而煩惱,總是用輔助列,用SumIf()來解決,不盡人意之處太多太多。查過SUMPRODUCT()函數(shù)的使用方法,其解釋為“求二個或二個以上數(shù)組的乘積之和”,就片面地理解為這與多條件求和無關(guān)。今天,學(xué)習(xí)啦小編就教大家在Excel中Sumproduct函數(shù)的使用方法。
Excel中Sumproduct函數(shù)的使用方法如下:
我們以“A1:A10”與“B1:B10”兩個組為例,第一個數(shù)組各行的值分別為1-10,第二個數(shù)組各行的值分別為11-20,如果我們用公式“=SUMPRODUCT((A1:A10)*(B1:B10))”,其結(jié)果為935,其計算過程如下圖:
現(xiàn)在我們將第一個數(shù)組加上條件又會有什么結(jié)果呢?如“(A1:A10)=4”之類。我們先來看“=SUMPRODUCT(A1:A10=4)”,其結(jié)果為“零”,可能是系統(tǒng)視為缺省為乘以“零”,因此結(jié)果為零,如果我們將公式改為“=SUMPRODUCT((A1:A10=4)*1)”,因為A1:A10中有一個4,因此其值為1,如果有兩個4,其他值就為2。
現(xiàn)在我們將第一個數(shù)組加上條件又會有什么結(jié)果呢?如“(A1:A10)=4”之類。我們先來看“=SUMPRODUCT(A1:A10=4)”,其結(jié)果為“零”,可能是系統(tǒng)視為缺省為乘以“零”,因此結(jié)果為零,如果我們將公式改為“=SUMPRODUCT((A1:A10=4)*1)”,因為A1:A10中有一個4,因此其值為1,如果有兩個4,其他值就為2。
如果A1:A10的值不是1-10,而其中有三個4,其他結(jié)果又發(fā)生了相應(yīng)的變化,如下圖:
這樣,SUMPRODUCT條件求和的功能就實現(xiàn)了。
下面是一張單位生產(chǎn)量報表的簡版,它主要統(tǒng)計“當日產(chǎn)量”,“當月產(chǎn)量”和“當年產(chǎn)量”,其數(shù)據(jù)來源于每日的產(chǎn)量記錄,如下圖:
上面報表查詢要求,當用戶輸入要統(tǒng)計的“年,月,日”(H2、I2、J2)時,就要相應(yīng)統(tǒng)計出“本日數(shù)”,“本月數(shù)”,“本年數(shù)”,一切基于查詢?nèi)盏臄?shù)據(jù)。
在“本月數(shù)”單元格的公式中,我們錄入如下公式:
=SUMPRODUCT((A2:A63=DATE(H2,I2,J2))*(B2:B63))
其意義是:統(tǒng)計日期為本日(DATE(H2,I2,J2))的產(chǎn)量數(shù)據(jù)。
在“本月數(shù)”單元格中,我們錄入如下公式:
=SUMPRODUCT((YEAR(A2:A63)=H2)*(MONTH(A2:A63)=I2)*(A2:A63<=DATE(H2,I2,J2))*(B2:B63))
這就有一個較為復(fù)雜的邏輯界定。
其一,我們統(tǒng)計本月的數(shù)據(jù),就要用條件MONTH(A2:A63)=I2)。
其二,我們僅有上面條件不足以統(tǒng)計出正確數(shù)據(jù),因為必須要考慮到歷史查詢情況,就是說,查詢?nèi)諡?0日,但是10-31日是有數(shù)據(jù)的,因此還必須加上如些條件)(A2:A63<=DATE(H2,I2,J2)),就是當月數(shù)據(jù)還要小于查詢?nèi)铡?/p>
其三,有些時候,數(shù)據(jù)中有一年以上的數(shù)據(jù),所以僅有上面兩個條件還不行,如查詢本月2月,就可能把去年2月的數(shù)據(jù)也統(tǒng)入其中了,還得加上條件(YEAR(A2:A63)=H2),既“年”等于XX年。