Excel好用的函數：INDIRECT, SUMIF

呈現資料總覽（Overview）的時候，Excel有兩個函數非常好用，那就是INDRIRECT和SUMIF。讓我自己在記帳的時候，總算可以不用每個月手動做小計，然後再抄錄到年度總覽的表格了。



先講比較簡單的SUMIF，其實就和SUM的作用相同，是做總計的，但是多了一個IF的條件，只有符合條件的才會被加進去。在Excel裡面會提示

=SUMIF(range, criteria, [sum_range])

range: 範圍陣列，用作判斷條件的值
criteria: 判斷準則
[sum_range]: 用來加總的數值，如果沒有這個的話，則用範圍陣列當作加總。

範例：

• =SUMIF(D:D, 3)  // 如果D欄等於3，加總
• =SUMIF(C:C, ">5")  // 如果C欄大於5，加總
• =SUMIF(A:A, "<2")  // 如果A欄小於2，加總

範例2：

• =SUMIF(D:D, "Food", C:C)  // 如果D欄的值等於Food，則對C欄加總
• =SUMIF(C:C, ">5", A:A)  // 如果C欄大於5，則對A欄加總
• =SUMIF(A:A, "<2", H:H)  // 如果A欄小於2，則對H欄加總

第二個則是要說的是INDIRECT，是種非直接傳遞數值的方法。傳回的是一串文字所指定的參照位址，等於是藉著某個欄位當作參照的轉介，連結到實際要取得的數值。Microsoft本身就提供了滿詳細的INDIRECT說明，直接操作一次就可以瞭解INDIRECT是怎麼樣運作的。


基於上面兩個函數，就能輕鬆把自動更新的總覽給呈現出來。

下方的圓餅圖則是依據A欄的分類和N欄的Sum所畫出的年度分佈圖。資料變動時，圖形也會跟著變動。

總覽表的內容則是利用公式搭配上面介紹的兩個函數，自動依據各個分頁的內容表現。每個分頁的內容就像下圖那樣。總覽表格需要的是「金額」和「分類」兩個欄位，呈現出每個類別的總金額。

要獲得每個類別的總計，先使用上面介紹的SUMIF，針對每個類別去加總。

拿食物(Food)類做例子，儲存格內容會是
=SUMIF(D:D, "Food", C:C)

接著，更新一下公式，讓它讀取特定的分頁。Excel裡頭，資料範圍的格式是「分頁!欄:欄」。一月的分頁是January，把公式改成
=SUMIF(January!D:D, "Food", January!C:C)

這裡當然可以手動更改公式中每個月的分頁，不過這麼一來Excel最強大的拖曳功能就沒辦法使用。在這邊我用INDIRECT的方式，去讀取表格上一定要有的月份名稱，當作資料表的名稱使用。

同樣拿一月當作例子，記錄January的儲存格是B4，那麼January!D:D就可以替換成INDIRECT(B4 & "!D:D")。在這個例子裡面，這樣表示沒有問題，可是如果B4裡有空格，那麼這個公式就會出錯。比較保險的方式，是在分頁前後加上單引號(')，讓January變成'January'。上頭的INDIRECT函數就得將內容改成INDIRECT("'" & B4 & "'!D:D")。那麼，那個SUMIF會變成
=SUMIF(INDIRECT("'" & B4 & "'!D:D"), "Food", INDIRECT("'" & B4 & "'!C:C"))

最後，我希望不單是月份可以用拖曳，連類別也要可以用這個方式。所以把"Food"這個表示類別的數值，也替換成某個記錄類別的儲存格(A10)。所以公式到最後就變成
=SUMIF(INDIRECT("'" & B4 & "'!D:D"), A10, INDIRECT("'" & B4 & "'!C:C"))

做好某一格之後，就用拖曳的方式填滿整個表格，與內容同步更新的總覽表就完成了。接著在下方和最右方加上小計，每個月份和每個類別的小計也就出來了。

這個表格有個限制，它的分類是固定的。如果在每個月份裡面打了既定類別外的分類，就無法呈現在這個總覽表裡頭。但因為是自己用，小心這個限制就行了。