2012年2月8日 星期三

Excel好用的函數:INDIRECT, SUMIF

Excel

呈現資料總覽(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是怎麼樣運作的。


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

AccountBook

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

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

Tab
要獲得每個類別的總計,先使用上面介紹的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"))

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

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

沒有留言 :

張貼留言

Related Posts Plugin for WordPress, Blogger...