贊贊小屋

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式

Excel資料分析常用到類別加總,本文以會科費用報表為例,介紹樞紐分析表、SUMIFS、陣列公式三種方法實現多維度報表匯總,最後補充加上數值大小判斷條件。

前兩節文章範例都有用到SUMIF這個函數,它是依照某個特定條件、將相對應指定欄位的數值都加總起來的函數。在Excel中,「篩選」和「樞紐分析表」也可以達到類似效果,不過「篩選」是直接在報表標題列執行,「樞紐分析表」是另外再生成一張彙總表,和SUMIF函數在儲存格中輸入公式的適用情況不同。在前兩節的基礎上,這一節文章進一步介紹SUMIF的強化版:SUMIFS,以下介紹:

一、費用明細帳

包括費用歸屬部門的明細帳。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第1張

二、樞紐分析表

跑樞紐分析表,如圖配置欄位。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第2張

三、會科部門匯總表

整理好的樞紐分析表,會計科目和費用部門清楚彙總在一張報表上。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第3張

四、SUMIFS函數

想要以函數方式,達到和樞紐分析表相同效果,因為有會計科目和費用部門兩個加總的條件,「SUMIF」不夠用,需要使用「SUMIFS」,輸入公式:「=SUMIFS(一!$C$2:$C$13,一!$A$2:$A$13,$A3,一!$B$2:$B$13,B$2)」。第一個參數欄位是想要加總的範圍,也就是明細帳裡的「發生金額」,接下來的參數欄位是兩兩一組,我們需要會計科目等於「A3」(「5100」),費用部門等於「B2」(「財務部」)。這裡的「$A3」表示將「A3」的A欄固定住,「B$2」表示將「B2」的第二列固定住,將公式往下拉的時候,固定住的欄號和列號不會跟著遞增或遞減,在下個步驟可以很清楚看到效果。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第4張

五、複製函數公式

和第三個步驟一模一樣的報表,只不過,一個是用樞紐分析表編製而成,一個是純粹套SUMIFS函數公式計算出來的,這裡因為已經精巧固定住特定的欄號和列號,可以直接將公式往右往下拉即可,不用一個一個輸入或修改。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第5張

六、陣列函數公式

除了SUMIFS函數,也可以使用陣列方式套用多條件,輸入公式:「=SUM(IF((一!$A$2:$A$13=六!$A3)*(一!$B$2:$B$13=六!B$2),一!$C$2:$C$13))」,輸入完之後,因為想要使用陣列公式,要先將滑鼠移到公式欄,先按住「Ctrl」和「Shift」不放,再按「Enter」鍵,結果可以看到公式兩邊冠上了大括號,計算出來的值,和SUMIFS函數結果是一樣。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第6張

七、SUMIFS判斷條件

既然是多條件求和,可以有第三個條件,並且可以是數學判斷式,例如100以下的金額太小,忽略不計,再加上個條件式:「=SUMIFS(一!$C$2:$C$13,一!$A$2:$A$13,$A3,一!$B$2:$B$13,B$2,一!$C$2:$C$13,”>100″)」。如圖黃色所示,會計科目「5100」因為每筆都沒有超過100,所以各部門加總金額為零。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第7張

八、陣列公式判斷條件

陣列公式也是在後面再加上一項條件:「*(一!$C$2:$C$13>100)」。

Excel報表多條件加總:樞紐分析表、SUMIFS、陣列公式 SUMIF 第8張

樞紐分析表、SUMIFS函數、陣列公式三種方式

這一節文章用了三種方法編製出相同的報表。樞紐分析表Excel預設的指令,功能齊全的同時,它是一整套固定的模式,相較之下,在單一儲存格輸入的SUMIFS函數公式較為純粹,方便作進一步的操作和移動,至於陣列公式在結構特別適合一定範圍內設定條件加總的情況。實務應用時,依據狀況不同這三個方法可以靈活應用。

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1