|
在OFFICE官方函數(shù)列表中，常用的求和函數(shù)有三個(gè)：SUM,SUMIF,SUMIFS。 他們每一個(gè)都各自專長(zhǎng)： SUM——單行、單列、多行、多列，無(wú)條件求和; SUMIF——行、列，一個(gè)條件求和; SUMIFS——行、列，多條件求和。  根據(jù)上表可知，常用的三個(gè)求和函數(shù)，均無(wú)法滿足所有的求和要求。 是否有一個(gè)函數(shù)，可以滿足所有的求和要求？目前OFFICE最新版本為OFFICE 2019，盡管有新增函數(shù)，但并沒(méi)有包含與求和有關(guān)的項(xiàng)目。  那么有沒(méi)有函數(shù)組合，滿足多條件、多行或多列求和呢？曾經(jīng)在《如何快速解決多條件匯總難題——Excel中的三個(gè)簡(jiǎn)單方法》中，提到過(guò)數(shù)學(xué)集合的概念，并將其應(yīng)用于多條件求和。 既然集合可用于多條件，那么也將其用于多行或多列求和。 實(shí)際上多行或多列，在Excel中稱之為"區(qū)域"。因此只要能夠使函數(shù)組合識(shí)別到數(shù)據(jù)"區(qū)域"，就可以進(jìn)行計(jì)算。 與區(qū)域計(jì)算相關(guān)的函數(shù)常用的有兩個(gè)，一個(gè)是SUMPRODUCT，一個(gè)是OFFSET。 下面就以統(tǒng)計(jì)最近一屆奧運(yùn)獎(jiǎng)牌榜中國(guó)的金牌及銀牌的總數(shù)為案例分別說(shuō)明兩個(gè)方法。  方法一：SUMPRODUCT數(shù)組計(jì)算實(shí)現(xiàn)區(qū)域多條件求和 觀察SUMPRODUCT的參數(shù)是數(shù)據(jù)區(qū)域1、2……255。因此，可直接進(jìn)行多區(qū)域計(jì)算，而計(jì)算條件，可作為區(qū)域參數(shù)即可，兩者用符號(hào)"*"連接。 根據(jù)獎(jiǎng)牌榜統(tǒng)計(jì)要求，包含一個(gè)求和區(qū)域及兩個(gè)條件： 求和區(qū)域?yàn)?金牌"E、"銀牌"F兩列； 條件1是"最近一屆"，也就是在年度A列選擇最大的年份，這里使用MAX函數(shù)即可找到； 條件2是國(guó)家為"中國(guó)"。 將以上條件分別填入函數(shù)SUMPRODUCT中，公式為： SUMPRODUCT((E2:F10)*(A2:A10=MAX(A2:A10))*(D2:D10="中國(guó)"))=44 
方法二： OFFSET確定求和區(qū)域，加上SUMIFS多條件的特點(diǎn)。 根據(jù)Excel官方說(shuō)明中，OFFSET既可以返回一個(gè)單元格，也可以返回一片數(shù)據(jù)區(qū)域。其中前三個(gè)參數(shù)是必需項(xiàng)目。
同樣以統(tǒng)計(jì)最近一屆咱國(guó)家收獲金牌和銀牌數(shù)為例。這次我們使用 OFFSET來(lái)定義區(qū)域。  由于所選區(qū)域?yàn)镋、F兩行，所以要用到數(shù)學(xué)中集合的概念。公式為： OFFSET(E2:E10,,{0,1})  公式中第三個(gè)參數(shù)的含義是，向右移動(dòng)的列數(shù)。OFFSET中引入了集合{0,1}，則可理解為此公式包含兩個(gè)區(qū)域：OFFSET(E2:E10,,0)和OFFSET(E2:E10,,0)。 確定了求和數(shù)據(jù)區(qū)域后， 結(jié)合SUMIFS函數(shù)的多條件的特點(diǎn)，再公式最外層加上SUM以進(jìn)行集合運(yùn)算。組合公式為： SUM(SUMIFS(OFFSET(E2:E10,,{0,1}),A2:A10,MAX(A2:A10),D2:D10,"中國(guó)")) 
以上就是多行多列多條件求和的兩種常用方法。 你學(xué)會(huì)了嗎？ |
|
|
