|
日常工作中，我們經(jīng)常要用到多條件求和,，方法有多種,，第一類：使用基本功能來實(shí)現(xiàn)。主要有：篩選,、分類匯總,、數(shù)據(jù)透視表、多條件求和向?qū)В?/span> 第二類：使用公式來實(shí)現(xiàn)方法,。主要有：使用SUM函數(shù)編寫的數(shù)組公式,、聯(lián)用SUMIF和輔助列（將多條件變?yōu)閱螚l件）、使用SUMPRODUCT函數(shù),、使用SUMIFS函數(shù)(限于Excel2007及以上的版本),，方法千差萬別、效果各有千秋,。 本人更喜歡用SUMPRODUCT函數(shù),。由于Excel幫助對SUMPRODUCT函數(shù)的解釋太簡短了，與SUMPRODUCT函數(shù)的作用相比實(shí)在不匹配,，為了更好地掌握該函數(shù),，特將其整理如下。 一,、 基本用法 在給定的幾組數(shù)組中,，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。 語法： SUMPRODUCT(array1,array2,array3,...) Array1,array2, array3, ... 為 2 到 30 個數(shù)組,，其相應(yīng)元素需要進(jìn)行相乘并求和,。 公式：=SUMPRODUCT(A2:B4,C2:D4)
公式解釋：兩個數(shù)組的所有元素對應(yīng)相乘，然后把乘積相加,，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3,。計算結(jié)果為156 一、 擴(kuò)展用法 1,、 使用SUMPRODUCT進(jìn)行多條件計數(shù) 語法： ＝SUMPRODUCT((條件1)*(條件2)*(條件3)* …(條件n)) 作用： 統(tǒng)計同時滿足條件1,、條件2到條件n的記錄的個數(shù)。 實(shí)例： =SUMPRODUCT((A2:A10='男')*(B2:B10='中級職稱')) 公式解釋： 統(tǒng)計性別為男性且職稱為中級職稱的職工的人數(shù) 2,、 使用SUMPRODUCT進(jìn)行多條件求和 語法： ＝SUMPRODUCT((條件1)*(條件2)*(條件3) *…(條件n)*某區(qū)域) 作用： 匯總同時滿足條件1,、條件2到條件n的記錄指定區(qū)域的匯總金額。 實(shí)例： =SUMPRODUCT((A2:A10='男')*(B2:B10='中級職稱')*C2:C10) 公式解釋： 統(tǒng)計性別為男性且職稱為中級職稱的職工的工資總和（假設(shè)C列為工資） 二,、 注意事項(xiàng) 1,、數(shù)組參數(shù)必須具有相同的維數(shù)（也就是各個參數(shù)的引用行數(shù)要一致）。否則,，函數(shù)SUMPRODUCT 將返回錯誤值 #VALUE!,。 2、SUMPRODUCT函數(shù)將非數(shù)值型的數(shù)組元素作為 0 處理,。 3,、在SUMPRODUCT中，2003及以下版本不支持整列（行）引用,，必須指明范圍,，不可在SUMPRODUCT函數(shù)使用A:A、B:B,，Excel2007及以上版本可以整列（列）引用,，但并不建議如此使用，公式計算速度慢,。 4,、SUMPRODUCT函數(shù)不支持“*”和“？”通配符 SUMPRODUCT函數(shù)不能象SUMIF,、COUNTIF等函數(shù)一樣使用“*”和“,？”等通配符，要實(shí)現(xiàn)此功能可以用變通的方法,，如使用LEFT,、RIGHT、ISNUMBER(FIND())或ISNUMBER(SEARCH())等函數(shù)來實(shí)現(xiàn)通配符的功能,。 如： =SUMPRODUCT((A2:A10='男')*(B2:B10='中級職稱')*(LEFT(D2:D10,1)='龍')*C2:C10) =SUMPRODUCT((A2:A10='男')*(B2:B10='中級職稱')*((ISNUMBER(FIND('龍逸凡',D2:D10)))*C2:C10)) 注：以上公式假設(shè)D列為職工姓名,。ISNUMBER(FIND()),、ISNUMBER(SEARCH())作用是實(shí)現(xiàn)“*”的通配功能,，只是前者區(qū)分大小寫,，后者不區(qū)分大小寫。 5,、SUMPRODUCT函數(shù)多條件求和時使用“,，”和“*”的區(qū)別： 當(dāng)擬求和的區(qū)域中無文本時兩者無區(qū)別，當(dāng)有文本時,，使用“*”時會出錯,，返回錯誤值 #VALUE!，而使用“,，”時SUMPRODUCT函數(shù)會將非數(shù)值型的數(shù)組元素作為 0 處理,，故不會報錯。 也就是說： 公式1：=SUMPRODUCT((A2:A10='男')*(B2:B10='中級職稱')*C2:C10) 公式2：=SUMPRODUCT((A2:A10='男')*(B2:B10='中級職稱'),，C2:C10) 當(dāng)C2:C10中全為數(shù)值時,，兩者計算結(jié)果一樣，當(dāng)C2:C10中有文本時公式1會返回錯誤值 #VALUE!,，而公式2會返回忽略文本以后的結(jié)果,。 三、 網(wǎng)友們的精彩實(shí)例 1,、求指定區(qū)域的奇數(shù)列的數(shù)值之和 =SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1) 2,、求指定區(qū)域的偶數(shù)行的數(shù)值之和 =SUMPRODUCT(((MOD(ROW(A1:A22),2))-1)*A1:A22)*(-1) 3、求指定行中列號能被4整除的列的數(shù)值之和 =SUMPRODUCT((MOD(COLUMN(A1:P1),4)=0)*A1:P1) 4,、求某數(shù)值列前三個最大的數(shù)值之和 ＝SUMPRODUCT(LARGE(B1:B16,ROW(1:3))) 5,、統(tǒng)計指定區(qū)域不重復(fù)記錄的個數(shù) ＝SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15)) 圖文作者：ExcelHome技術(shù)論壇高級會員 龍逸凡 【老朋友】→請點(diǎn)擊右上角的按鈕，將本文分享到朋友圈,。 【新朋友】→請點(diǎn)擊標(biāo)題下的ExcelHome,，添加關(guān)注?；蛘咧苯硬檎夜娞?/span>iexcelhome |
|
|
