Excel 中最常用的匯總統(tǒng)計方式之一，就是求和。

每個公司對于求和的要求也不一樣。有的是對數(shù)據(jù)區(qū)域中的行進行求和，有的對列數(shù)據(jù)進行求和。還有的是對于行列交叉位置的數(shù)據(jù)進行求和的，還有的是錯位求和。

數(shù)據(jù)結(jié)構(gòu)也是千差萬別。

這不，有位仁兄發(fā)來問題求助，如下圖：

要求是：將其中的手續(xù)費按照項目類別分別統(tǒng)計求和。如下圖：

比如，常規(guī)培訓(xùn)手續(xù)費，需要將【B】列常規(guī)培訓(xùn)單元格下面的手續(xù)費都統(tǒng)計在內(nèi)。即將【D3】和【D5】的單元格求和。

場地租賃手續(xù)費同理。

問題分析

乍看到這個問題時，我有點懵！

一般情況下我們可能會像下面這樣記錄數(shù)據(jù)。如下圖：

在手續(xù)費之前加上歸屬的項目名稱。

如果這樣記錄數(shù)據(jù)的話，就跟我們平時用求和函數(shù)的套路完全一樣了。

用到的公式如下：

• =SUMIF(B:B,F2,D:D)

公式解析：

在【B】列中，查找【F2】單元格的內(nèi)容，對符合條件的，在【D】列對應(yīng)單元格中的金額求和。

但是，現(xiàn)在的問題是他們并不是像上面圖中那樣記錄數(shù)據(jù)

如果直接用 SUMIFS 雙條件求和，結(jié)果會是下面這樣。

結(jié)果是 0！

公式如下：

• =SUMIFS(D:D,B:B,LEFT(F2,4),B:B,"手續(xù)費")

問題出在哪里呢？又到底該如何才能求和呢？

解決問題

▋方法一：SUMIFS 多條件求和

根據(jù)上面的問題分析來看，原始表格記錄成這樣，的確是一個雙條件求和的問題。

就是這里的雙條件是一個錯行求和。

我們將上面的公式改成如下這樣：

公式如下：

• =SUMIFS($D$3:$D$8,$B$2:$B$7,LEFT(F2,4),$B$3:$B$8,"手續(xù)費")

公式解析：

SUMIFS 多條件求和這里有兩個條件，

第一個條件對是：「 $B$2:$B$7,LEFT(F2,4) ?」。

在單元格【$B$2:$B$7】中，查找【F2】單元格中的前四個字符的內(nèi)容。

即：常規(guī)培訓(xùn)。（這里用 LEFT 函數(shù)截取【F2】單元格左邊四個字符）

第二個條件對是：「 $B$3:$B$8,"手續(xù)費" 」。

在單元格【$B$3:$B$8】中，查找手續(xù)費的內(nèi)容。

如果同時符合條件的話，在【$D$3:$D$8】單元格區(qū)域中對應(yīng)的行金額進行求和。

這里的第二個條件對和【$D$3:$D$8】這個求和區(qū)域正好與第一個條件對錯了一行。

▋方法二：SUM 數(shù)組公式

這里我們可以將兩個條件利用文本連接符錯位連接的方法變成一個條件，然后再與求和區(qū)域相乘的方法來解決。

公式如下：

• =SUM(($B$2:$B$7&$B$3:$B$8=F2)*$D$3:$D$8)

公式解析：

這里的思路跟上面的 SUMIFS 多條件求和的思路是一樣的。

為了能讓大家看明白，我們這里加兩個輔助列演示下，如下圖：

圖中我們將【B3：B7】單元格復(fù)制到【C2：C6】，

將【E3：E7】單元格復(fù)制到【F2：F6】。

這樣排列的話，跟常規(guī)的求和套路完全一樣了。

• $B$2:$B$7&$B$3:$B$8

結(jié)果是：

{"常規(guī)培訓(xùn)手續(xù)費";"手續(xù)費常規(guī)培訓(xùn)";"常規(guī)培訓(xùn)手續(xù)費";"手續(xù)費場地租賃";"場地租賃手續(xù)費";"手續(xù)費"}

與輔助列圖中的【B2：B7】與【C2：C7】連接的結(jié)果完全一樣。

再與【F2】單元格中的內(nèi)容（常規(guī)培訓(xùn)手續(xù)費）進行比較，如果相等就返回 TRUE，否則返回 FALSE。

{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

最后與$D$3:$D$8 單元格中的金額相乘。

TRUE 相當(dāng)于 1，F(xiàn)ALSE 相當(dāng)于 0。

結(jié)果如下：

{-10;0;-10;0;0;0}

最后用 SUM 求出結(jié)果。即：-20。

知識擴展

這里我們再分享一種解題思路，就是透視表法。

從上面的原始記錄來看，是屬于一維數(shù)據(jù)表格，只要稍加變化就可以使用強大的透視表來處理了。

如下圖：

在【E】列增加一個輔助列，并在【E2】單元格輸入如下公式：

• =IF(B2="手續(xù)費",E1,B2)

公式解析：

如果【B2】單元格等于手續(xù)費，就返回【E1】單元格的內(nèi)容，否則返回【B2】單元格本身。

公式的意思很簡單。主要目的就是讓手續(xù)費歸屬于它上面一個單元格的項目內(nèi)容。

然后，我們選中這個區(qū)域作為數(shù)據(jù)源，然后依次點擊【插入】-【數(shù)據(jù)透視表】-調(diào)出透視表對話框，直接點【確定】。

在新生成的工作表中，將【輔助列】和【項目】拖到行區(qū)域，將【金額】拖到值區(qū)域。如下圖：

這樣大體上的統(tǒng)計就完成了。

進行如下設(shè)置，點擊【設(shè)計】-【報表布局】-【以表格形式顯示】。

最后篩選項目中的手續(xù)費即可。

寫在最后

今天，我們分享了一個很特別的求和方法：錯行求和。

? 利用 SUMIFS 多條件錯行求和。

? 利用 SUM 數(shù)組公式錯行求和。

? 利用輔助列+透視表的方法進行錯行求和。

當(dāng)然在實際工作中，還會遇到錯列求和、隔行求和、隔列求和等等。

大家可以參考今天的文章思路來解決。

好了，如果大家還有哪些搞不定的 Excel 問題，可以報名參加我們秋葉《3 天 Excel 集訓(xùn)營》。

這里不僅有老師、助教為你?1V1?群內(nèi)答疑解惑！還能學(xué)習(xí)更多有用的 Excel 小技巧！

僅需 1 元！

現(xiàn)在就掃碼購課加班班微信

助你成為效率達人！

??????

*廣告