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