Sumif用法:Excel公式快速複製以及Sumproduct函數多條件加總

Excel條件求和最常用到SUMIF函數,在此以部門費用報表為例介紹SUMIF用法,包括如何設置參照快速複製公式,同時補充SUMPRODUCT相關的基本用法及進階多條件組合公式。

一、部門費用報表

整篇文章的範例資料是各月份不同科目、不同部門的費用報表,金額已經簡化了方便確認函數計算結果。

一張含有 桌 的圖片

自動產生的描述

二、基本Sumif用法

要針對月份進行統計,可以使用Excel的SUMIF函數:「=SUMIF(A2:A9,”2202″,D2:D9)」,它有三個參數,第1個參數是條件範圍,第2個參數是加總條件,第3個參數是加總範圍。從截圖的範例資料對照應該很容易可以瞭解SUMIF的用法,注意到在G2儲存格公式第二個參數使用的是單純的數字「2201」,而在G3儲存格是將月份用英文的雙引號括起來,代表的是文字:「”2202”」,兩個公式都是有效的,表示SUMIF函數會自動進行文字數值的轉換,比較不會有這方面的問題,本步驟公式也可以參考H2及H3儲存格。

一張含有 桌 的圖片

自動產生的描述

三、公式快速複製

上個步驟已經瞭解了SUMIF基本用法,接下來可以做一些優化:「=SUMIF($A$2:$A$9,F2,$D$2:$D$9)」,說明如下:

例如我統計的月份可能不僅僅兩個月份,如果像上個步驟一一的輸入的話,很顯然不太方便有點麻煩。像這種情況可以採取引用儲存格參照的方式,也就是第二個參數「F2」。

接著由於原始資料範圍是固定的,都是A2到A9,可以在欄號列號前面都加上「$」字號,作用是將公式參照的儲存格固定,此時再將滑鼠游標移到儲存格右下角,游標形狀它會變成小黑十字架,然後按住滑鼠左鍵拖曳者連按兩下,便就可以快速複製公式,並且在複製公式的時候,因為A2到A9是用「$」字號固定,所以它不會跟著公式複製而遞增。反過來說,如果是中間第二個參數「F2」純粹的儲存格位址,它就會跟著遞增,此部份操作參考H2到H5的公式會更加清楚。

最後補充,在固定範圍的時候,除了可以直接加上錢字號($),也可以在公式編輯列先圈選「A2:A9」,按「F4」便能快速加上錢字號。

一張含有 桌 的圖片

自動產生的描述

四、整欄範圍參照

上個步驟使用錢字號將儲存格參照固定雖然複製公式很方便,但是通常實務上報表資料是相同欄位格式,只是資料筆數有所不同,因此可以直接用一整欄作為參數範圍:「=SUMIF(A:A,F5,D:D)」。這樣子即使報表資料有更新或者增減變動,由於公式是參照到一整欄的範圍,計算結果也會自動更新,顯然更加方便。

一張含有 桌 的圖片

自動產生的描述

五、Sumproduct用法

在瞭解SUMIF用法之後,接下來介紹較為進階的SUMPRODUCT,這個函數原始設計是某兩個對應範圍兩兩相乘的乘積和。如果是在條件求和的場合,可以利用乘積和的特性,其中一個參數設定為條件式,條件成立是真,在Excel也就是1,條件不成立是假,等同於0。因此在兩兩相乘的時候,條件成立是1,1乘以另一個參數便是另一個參數值,而條件不成立為0,不管第二個參數多大,0乘以多少都是0,兩兩相乘其實得到的乘積和其實就是條件求和,和SUMIF計算同樣結果。

這裡有一點要補充說明,SUMPRODUCT由於涉及到條件式判斷,對於數值跟文字的差別會比較敏感一點,因此這裡可以在G2是輸入文字「”2201”」,沒有辦法執行成功,因此它條件是不成立的。另外G3是數值「2201」,計算成功。

Sumif用法:Excel公式快速複製以及Sumproduct函數多條件加總 5

六、Sumproduct進階

先前步驟有介紹直接寫入條件及參照儲存格的兩種用法,SUMPRODUCT也是可以。不過如同截圖所看到的幾個公式設置,SUMPRODUCT在進行條件判斷時,由於是進行嚴格意義的邏輯計算,加上是技巧性使用乘積和的概念,跟上個步驟一樣會複雜一點。

首先「A:A=2203」整個欄位等於是不行的,「A2:A9=F5」也是失敗,參照不同的。通常在Excel函數遇到像這樣子文字數值或者邏輯值的相關問題,有一個簡單方法是不管是數值文字邏輯值反正把它乘以1,Excel在相乘計算時便會自動轉換成數值,最終於可以進行SUMPRODUCT乘積和的計算,得到想要的結果。

Sumif用法:Excel公式快速複製以及Sumproduct函數多條件加總 7

七、進階Sumif用法

雖然SUMIF和SUMPRODUCT都可以實現條件求和,SUMIF顯然比較簡單直覺,不過之所以會介紹SUMPRODUCT當然是有它的強項所在,因此它在原始函數開發時便是以多重範圍進行乘積和計算,隱含了程式陣列的概念,相對較自由,因此如果想把多個不同條件作為一組條件進行加總統計的時候,可以將SUMIF及SUMPRODUCT組合運用。

例如這裡看到的想要加總不同科目或者月份的話,設計公式:「=SUMPRODUCT(SUMIF(B:B,F2:G2,D:D))」,完全發揮了這兩個函數的功能!

Sumif用法:Excel公式快速複製以及Sumproduct函數多條件加總 9

Sumproduct陣列公式

Excel進階函數應用中有一項是陣列公式,它的用法其實就是本篇文章SUMPRODUCT一樣,是針對範圍裡面每個項目作為對象,執行特定計算再進行整合統計。而SUMPRODUCT在Excel雖然屬於一般的函數,可以和SUMIF同樣簡單使用,但它本身就具有陣列公式的功能,本篇文章是介紹以真假值進行條件判斷,真假值0與1做一個技巧性的運用,以此為基礎SUMPRODUCT還能行一些更為特殊的計算,讀者有興趣可以參考贊贊小屋其他相關文章跟影片。

加強學習:贊贊小屋Excel函數文章

Sumif用法:Excel公式快速複製以及Sumproduct函數多條件加總 11
加入Line社群,口袋裡的Excel小教室!