SUMPRODUCT函數(shù)的用法，這個(gè)函數(shù)從字面來理解，SUM是求和，PRODUCT是乘積。綜合到一起，就是對(duì)各個(gè)數(shù)組參數(shù)計(jì)算乘積，并返回乘積之和。
啥是數(shù)組？咱們就簡單的把它理解成一組數(shù)好了，沒啥高科技含量。
接下來，咱們就一起看看這個(gè)函數(shù)有哪些典型的用法。
1、計(jì)算商品總價(jià)
如下圖所示，C列是商品單價(jià)，D列是商品數(shù)量，要計(jì)算所有商品的總價(jià)，可以使用下面這個(gè)公式：
=SUMPRODUCT(C2:C12,D2:D12)
公式把每一行中的單價(jià)與數(shù)量對(duì)應(yīng)相乘，然后再求和。
計(jì)算過程相當(dāng)于C2*D2+C3*D3+C4*D4……
本例中，也可以將中間的逗號(hào)換成乘號(hào)：
=SUMPRODUCT(C2:C12*D2:D12)
那這個(gè)乘號(hào)和逗號(hào)啥區(qū)別呢？
當(dāng)求和區(qū)域中有文本的時(shí)候，在兩個(gè)數(shù)組之間使用逗號(hào)，會(huì)把文本當(dāng)成0來處理。要是使用乘號(hào)的話，如果求和區(qū)域中有文本，就返回錯(cuò)誤值了，既然是文本，你讓小S怎么乘嘛。
但是使用逗號(hào)也是有前提條件的，就是兩個(gè)數(shù)組的行、列數(shù)必須一樣，否則會(huì)返回一個(gè)錯(cuò)誤值。
2、計(jì)算指定條件的總價(jià)
如下圖所示，要計(jì)算部門為“大食堂”的所有商品總價(jià)，可以使用以下公式：
=SUMPRODUCT((A2:A12=”大食堂”)*C2:C12*D2:D12)
這個(gè)公式中，先使用(A2:A12=”大食堂”)，判斷A列的部門是不是等于指定的部門，得到一組由TRUE和FALSE構(gòu)成的邏輯值。
但是邏輯值不能直接作為數(shù)組參數(shù)，但是可以參加四則運(yùn)算，所以咱們用乘號(hào)，分別乘以C列的單價(jià)和D列的數(shù)量。如果(A2:A12=”大食堂”)這部分的結(jié)果里是邏輯值TRUE，就相當(dāng)于1，而邏輯值FALSE的作用就相當(dāng)于是0。換句話說，符合指定部門的，就用1*單價(jià)*數(shù)量，不符合指定部門的就用0*單價(jià)*數(shù)量，最后再將各個(gè)乘積進(jìn)行求和。
3、計(jì)算指定月份的總金額
如下圖，要根據(jù)A列的日期，計(jì)算2月份的總金額。
公式為：
=SUMPRODUCT((MONTH(A2:A12)=2)*1,D2:D12,E2:E12)
MONTH(A2:A12)=2部分，先使用MONTH函數(shù)計(jì)算出A列日期的月份，然后用等式，判斷是不是等于2，這部分得到的，也是一組由TRUE和FALSE構(gòu)成的邏輯值。
接下來把邏輯值乘以1，TRUE*1結(jié)果為1，F(xiàn)ALSE*1結(jié)果為0，最終變成由數(shù)值1和0構(gòu)成的新數(shù)組。再用這個(gè)新數(shù)組作為參數(shù)1，分別與第二參數(shù)D2:D12和第三參數(shù)E2:E12相乘，最后計(jì)算出乘積之和。
也可以直接把各個(gè)參數(shù)之間的逗號(hào)寫成乘號(hào);
=SUMPRODUCT((MONTH(A2:A12)=2)*D2:D12*E2:E12)
4、計(jì)算指定月份、指定部門的總金額
如下圖，要計(jì)算2月份大食堂的總金額。
公式為：
=SUMPRODUCT((MONTH(A2:A12)=2)*(B2:B12=”大食堂”),D2:D12,E2:E12)
公式看起來很長，但是和第三個(gè)例子是一樣的，就是多了一個(gè)部門的判斷條件(B2:B12=”大食堂”)。
5、根據(jù)兩個(gè)條件計(jì)算采購量
如下圖，要根據(jù)B1:E1單元格中的日期和A2:A12單元格中的品名，來計(jì)算對(duì)應(yīng)的采購量。
公式為：
=SUMPRODUCT((B1:E1=1*”2021-2-2″)*(A2:A12=”黃瓜”)*B2:E12)
這個(gè)數(shù)據(jù)表格和前面幾個(gè)表格的結(jié)構(gòu)不一樣了，兩個(gè)條件分別在行方向和列方向，對(duì)于這種結(jié)構(gòu)的表格，計(jì)算時(shí)有一個(gè)小套路，就是分別對(duì)比水平和垂直方向的兩個(gè)條件，然后乘以數(shù)值區(qū)域。
這個(gè)數(shù)值區(qū)域的行數(shù)，要與垂直方向條件區(qū)域的行數(shù)一樣，并且列數(shù)要與水平方向條件區(qū)域的列數(shù)一樣。
上面這句話可能不太好理解，其實(shí)結(jié)合到數(shù)據(jù)中，就可以看出來了：
數(shù)值區(qū)域是B2:E12，這里的行數(shù)是2~12行，和(A2:A12=”黃瓜”)的行數(shù)是一樣的。而列數(shù)是B:E，和(B1:E1=1*”2021-2-2″)的列數(shù)是一樣的。
另外，在公式中直接寫日期的時(shí)候，還要注意，先加上一對(duì)半角引號(hào)，然后再乘以1變成日期序列值，否則Excel會(huì)把2021-2-2當(dāng)成減法了。
6、同一區(qū)域設(shè)置多個(gè)條件
如下圖所示，要計(jì)算2月份白菜和土豆的總采購量。公式為：
=SUMPRODUCT((MONTH(B1:E1)=2)*((A2:A12=”白菜”)+(A2:A12=”土豆”))*B2:E12)
(MONTH(B1:E1)=2) 這部分是計(jì)算水平方向的月份的。
((A2:A12=”白菜”)+(A2:A12=”土豆”))這部分，用來判斷垂直方向的商品名稱是不是符合條件。 注意，兩個(gè)條件之間使用了加號(hào)，計(jì)算過程咱們就不細(xì)說了，作用是表示二個(gè)條件符合其一。
7、帶通配符的求和
如下圖所示，要根據(jù)A列的手機(jī)型號(hào)，統(tǒng)計(jì)帶有“摩托羅拉”字樣的手機(jī)銷量。
公式為：=SUMPRODUCT(ISNUMBER(FIND(“摩托羅拉”,A2:A12))*B2:B12*C2:C12)
SUMPRODUCT函數(shù)的參數(shù)中不支持使用通配符，所以在計(jì)算帶有關(guān)鍵字的問題，就比較吃虧了。
ISNUMBER(FIND(“摩托羅拉”,A2:A12))這部分，先使用FIND函數(shù)在A2:A12數(shù)據(jù)區(qū)域中查找關(guān)鍵字，如果包含關(guān)鍵字，就返回表示關(guān)鍵字位置的數(shù)值，否則就返回錯(cuò)誤值。然后使用ISNUMBER函數(shù)判斷FIND函數(shù)的數(shù)組結(jié)果是不是數(shù)值，如果是數(shù)值，說明是包含關(guān)鍵字的，如果不是數(shù)值，那就是不包含關(guān)鍵字了。
ISNUMBER最終得到由邏輯值TRUE或FALSE構(gòu)成的內(nèi)存數(shù)組，然后再與后面的第二參數(shù)B2:B12和第三參數(shù)C2:C12依次相乘。
最后由SUMPRODUCT返回乘積之和。
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任，
本站有權(quán)保留或刪除有爭議評(píng)論。
參與本評(píng)論即表明您已經(jīng)閱讀并接受
上述條款。