原文標(biāo)題:《學(xué)會(huì)這個(gè)函數(shù),一個(gè)頂四個(gè)!》
Excel 里難度最大的,應(yīng)該就是函數(shù)公式了。
舉個(gè)例子:
上面這個(gè)公式,SUM 函數(shù)很簡(jiǎn)單吧,相信大家都知道是求和。
結(jié)果口算都能算出來(lái),是 10。
這個(gè)函數(shù)又是什么意思呢?
部分同學(xué)這會(huì)兒就答不上來(lái)了。
其實(shí)非常簡(jiǎn)單,SUM 是相加,PRODUCT 是相乘,
所以計(jì)算結(jié)果就是把所有的數(shù)字相乘:24。
你肯定會(huì)表示不服:我就是不知道而已,知道的話肯定立馬就答出來(lái)了。
沒(méi)關(guān)系,再增加一點(diǎn)難度:
現(xiàn)在你已經(jīng)學(xué)會(huì)了 SUM 和 PRODUCT 函數(shù),那把這兩個(gè)函數(shù)結(jié)合到一起,計(jì)算結(jié)果應(yīng)該是什么?
答:結(jié)果是 10。
為什么呢?
這節(jié)課,我們就一起來(lái)學(xué)一學(xué) SUMPRODUCT 這個(gè)簡(jiǎn)單又復(fù)雜的函數(shù)!
基本用法
SUMPRODUCT 的作用,就是把兩列數(shù)據(jù)相乘,之后再相加。
比如上面的計(jì)算結(jié)果,就是兩列每個(gè)單元格相乘,然后相加,結(jié)果是 40。
所以這個(gè)效果通常會(huì)用來(lái)計(jì)算兩列需要相乘的相加的場(chǎng)景,
比如說(shuō)銷(xiāo)量和產(chǎn)品單價(jià):
計(jì)算總的銷(xiāo)售額,一個(gè) SUMPRODUCT 公式搞定:
=SUMPRODUCT(E2:E10,F2:F10)
有高手領(lǐng)路,是不是超級(jí)簡(jiǎn)單!
SUMPRODUCT 的參數(shù)如下:
- 參數(shù) 1,要相乘的第 1 列數(shù)據(jù);
- 參數(shù) 2,要相乘的第 2 列數(shù)據(jù);
- 參數(shù) 3,依次類(lèi)推,最多可以選擇 255 列數(shù)據(jù)。
福利小技巧
再送兩個(gè)福利小技巧:
▋參數(shù)相乘效果一樣
下面兩個(gè)公式的結(jié)果是一樣的,本質(zhì)上都是兩列數(shù)據(jù)相乘:
當(dāng)出現(xiàn)錯(cuò)誤值的時(shí)候,繼續(xù)在「研發(fā)部」里看看能不能找到 ——
=SUMPRODUCT(E2:E10,F2:F10)=SUMPRODUCT(E2:E10*F2:F10)
原理和下面 SUM 函數(shù)內(nèi)相加是一樣的。
=SUM(1,2,3)=SUM(1+2+3)
▋單列數(shù)據(jù)即相加
如果只有 1 列參數(shù),那么效果和用 SUM 函數(shù)是一樣的。
所以,第 1 個(gè)案例為什么是 10,你應(yīng)該明白了吧,因?yàn)橹挥幸涣袛?shù)據(jù)。
▋乘以數(shù)字
再來(lái)一個(gè)案例。
現(xiàn)在老板覺(jué)得產(chǎn)品單價(jià)太低了,每個(gè)產(chǎn)品單價(jià)都要增加 5 塊錢(qián),那么總銷(xiāo)售額應(yīng)該怎么算?
不用每個(gè)單價(jià)都加上 5,直接一個(gè) SUMPRODUCT 公式搞定。
=SUMPRODUCT(E2:E10,F2:F10+5)
看見(jiàn)了嗎?很簡(jiǎn)單,直接在求和的列當(dāng)中加上 5 就可以了。
這個(gè)計(jì)算過(guò)程,實(shí)際上是對(duì)這個(gè)區(qū)域每個(gè)單元格逐一加了 5:
所以,加一個(gè) 5,和加一列 5 本質(zhì)是一樣的。
漲 5 塊覺(jué)得還是不夠高,直接價(jià)格翻倍乘以 2,應(yīng)該怎么算?
公式是這樣的:
=SUMPRODUCT(E2:E10,F2:F10*2)
他的計(jì)算原理和加號(hào)是一樣的,如下,
▋條件計(jì)數(shù)
畢竟是 SUM 和 PRODUCT 兩個(gè)函數(shù)的合體,SUMPRODUCT 用法還不止這些。
看下面這一個(gè)問(wèn)題,想要統(tǒng)計(jì)每個(gè)部門(mén)的人數(shù),公式是這樣的:
=SUMPRODUCT((B2:B10=G2)*1)
思路解析:
B2:B10=G2,其實(shí)就是看看 B2、B3、……、B10 單元格,是不是和 G2,也就是「銷(xiāo)售一部」長(zhǎng)的一樣。
如果一樣,則為 true,也就是 1,那么得出的結(jié)果為 1;
如果不一樣,則為 false,也就是 0,那么得出的結(jié)果為 0。
最后加起來(lái)就是符合條件的數(shù)量啦。
如果有多個(gè)條件,就再乘以這個(gè)條件判斷的邏輯值。比如要判斷每個(gè)部門(mén)女生數(shù)量:
公式如下:
=SUMPRODUCT((B2:B10=G2)*(D2:D10="女"))
再比如,要統(tǒng)計(jì) 2010 年以前入職的每個(gè)部門(mén)女生數(shù)量,再乘以一個(gè)條件值:
公式是這樣的:
=SUMPRODUCT(B2:B10=G2)*D2:D10="女")*YEAR(E2:E102010)
用法你總結(jié)出來(lái)了嗎?
一句話,有多少個(gè)條件就乘以多少次:
▋條件求和
就這樣而已嗎?SUMPRODUCT 肯定不服。
現(xiàn)在我們要統(tǒng)計(jì)每個(gè)部門(mén)的銷(xiāo)售量,
對(duì)應(yīng)的 SUMPRODUCT 公式是這個(gè)樣子:
=SUMPRODUCT($E$2:$E$10*$F$2:$F$10*($B$2:$B$10=H2))
計(jì)算過(guò)程是這樣的:
如果有多個(gè)條件,比如計(jì)算每個(gè)部門(mén)「產(chǎn)品 C」的銷(xiāo)售額:
公式如下:
=SUMPRODUCT(($E$2:$E$10)*($F$2:$F$10)*($B$2:$B$10=H2)*(D2:D10="產(chǎn)品 C"))
和前面的計(jì)數(shù)原理一樣,把數(shù)據(jù)列和條件列乘起來(lái)就可以了。
寫(xiě)在最后
到這里你可能會(huì)有點(diǎn)疑惑:
這些計(jì)算,用 SUMIFS 和 COUNTIFS 都可以實(shí)現(xiàn)啊,為什么還要用 SUMPRODUCT ???
原因不是明擺著的嗎?一個(gè)函數(shù)頂四個(gè)!
要知道,條件數(shù)量不一樣,你可能要在 SUMIF、SUMIFS、COUNTIF 和 COUNTIFS 之間來(lái)回切換,
而 SUMPRODUCT 只用一個(gè)函數(shù)就可以了。
另外 SUMPRODUCT 用邏輯運(yùn)算符直接比較判斷,公式邏輯更加的清晰。
更重要的,它還可以計(jì)算更復(fù)雜的條件判斷。
本文來(lái)自微信公眾號(hào):秋葉 Excel (ID:excel100),作者:拉登 Dony,編輯:竺蘭
廣告聲明:文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接(包括不限于超鏈接、二維碼、口令等形式),用于傳遞更多信息,節(jié)省甄選時(shí)間,結(jié)果僅供參考,IT之家所有文章均包含本聲明。