編按：

各位小伙伴大家好呀！我是愛生活愛Excel的小E~在處理Excel數據時經常會涉及到求和，如果我們的Excel數據源是完整規(guī)范的，用我們常見的SUM函數就能輕松搞定求和問題。如果我們的數據源像下面那樣，錯位了，不規(guī)范了，又該如何求和？其實依舊可以用SUM函數進行區(qū)域錯位求和。跟著小E一起來學習一下吧~

最近收到一個這樣的問題求助：

數據源是左邊這種格式，按月存放的商品銷量，現在需要匯總成右邊的格式。然后這位同學就不知道該咋辦了？

這個問題還挺典型的，由于數據源結構設計不當為后期統計帶來了麻煩。因此今天教程內容分成兩個部分：首先幫這位同學解決問題，會介紹兩個函數解法；然后再結合這個例子解釋一下什么才是合理的數據源結構。

解決這個問題可以用到兩個函數：SUMPRODUCT和SUMIF。

SUMPRODUCT解法1：=SUMPRODUCT(IFERROR(($A$3:$E$14=H2)*$B$3:$F$14,0))

在I2單元格輸入上述公式，按住快捷鍵確認，然后用鼠標下拉公式至I14單元格結束。

這個公式的核心部分是($A$3:$E$14=H2)*$B$3:$F$14。

H2是要匯總的商品名稱，$A$3:$E$14是包含了所有商品名稱的數據源區(qū)域。用H2單元格中的內容也就是“商品1”和這個區(qū)域中的每個數據做比較，就會得到一組邏輯值。

商品名稱是 “商品1”的位置得到的就是TRUE，其他位置全部是FALSE。

$B$3:$F$14是包含了所有銷量的數據源區(qū)域。注意，這個區(qū)域不全是代表銷量的數字，還有商品名稱在其中。因此相乘后會出現三種情況，非零數字、0和錯誤值。

非零數字就是商品名稱為“商品1”的銷量，例如第一個就是95；0是商品名稱不是“商品1”的銷量與FALSE相乘的結果；錯誤值是文本（商品名稱）與邏輯值相乘的結果，因為文本無法進行計算。

總結來說，大于零的數就是我們需要匯總的，0和錯誤值都是需要忽略的。

因此使用IFERROR函數將錯誤值變成0，然后再用SUMPRODUCT對數組進行求和就得到了最終的匯總結果。

SUMPRODUCT解法2：=SUMPRODUCT(IF($A$3:$E$14=H2,$B$3:$F$14,0))

在單元格內輸入上述公式，按住快捷鍵確認，然后用鼠標下拉公式結束。

與第一個公式不同，這個公式直接用IF函數把不是要找的商品名稱所對應的銷量都變成0，然后再用SUMPRODUCT去求和。

IF($A$3:$E$14=H2,$B$3:$F$14,0)得到的結果是這樣的一個數組：

方法2的邏輯要比方法1簡單直接，但是兩個方法的核心都用到了區(qū)域錯位的邏輯，也就是$A$3:$E$14和$B$3:$F$14這兩個區(qū)域。

每個區(qū)域都混合了商品名稱和銷量，對于人來說，視覺上感覺是兩列數據為一組，但是對于Excel來說，并沒有這些概念，Excel只是按照區(qū)域對應的位置去執(zhí)行計算，遇到不能計算的就返回錯誤值。

這種區(qū)域錯位是一種很常用的手法，大家都非常熟悉的SUMIF函數有一個本事就是支持錯位求和，因此這個問題就有了第三種解法。

方法3：=SUMIF($A$3:$E$14,H2,$B$3:$F$14)

這個公式看起來和平時用的SUMIF并沒有什么區(qū)別，真要說有區(qū)別的話，就是條件區(qū)域和求和區(qū)域的選擇，并不是我們習慣上的那種選擇方法，而是用到了錯位區(qū)域的原理。

用我們習慣的SUMIF寫法公式大概得是這樣的才行：

=SUMIF($A$2:$A$14,H2,$B$2:$B$14)+SUMIF($C$2:$C$12,H2,$D$2:$D$12)+SUMIF($E$2:$E$11,H2,$F$2:$F$11)

但這才是3個月的，如果是12個月的那不是得12個SUMIF相加嗎？

通過這樣對比大家是不是發(fā)現用了區(qū)域錯位公式就很簡單呢。

好了，問題解決了，還是需要說一下規(guī)范數據源的問題。

其實就這個問題來說，規(guī)范的數據源應該是三列，月份、商品名稱和銷量。

如果是這樣的數據源，要實現按月匯總或者按商品匯總都非常容易，用數據透視表就能實現。

月份+商品：

月份匯總：

商品匯總：

根本就不會有什么難度，所以說要用Excel做數據統計，好的數據源才是成功的99%。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

７個Excel小技巧，提高表格查看效率

Excel運用規(guī)范1：一個單元格只記錄一條信息

快速整理不規(guī)范的Excel表格的7個公式

9條最實用的計算excel中關于日期的公式！（建議收藏）

版權申明：

本文作者郅龍；同時部落窩教育享有專有使用權。若需轉載請聯系部落窩教育。