跳到主要內容

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"))

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

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

留言

張貼留言

熱門文章

差不多食譜實驗：小烤箱烤長茄子？ Oven-roasted Long Aubergine?

發佈了「 差不多食譜：小烤箱烤茄子 Oven-roasted Aubergine 」之後，有位朋友在YouTube上留言問說，這個方法也可以用在市場上比較常看到的那種長茄子嗎？當時我只能回答不知道，因為沒試過。現在我有答案了！結果是一半一半！
張貼留言
閱讀完整內容

差不多食譜：香煎南瓜片 Pan-fried Pumpkin

托爺爺的福，在萬聖節那碗「 南瓜盅飯 」後，我還有個老家的南瓜可以用。但要怎麼好好運用這顆南瓜，卻是個不小問題。差不多食譜曾經試過「 醋漬南瓜 」、「 牛奶南瓜 」，還有「 南瓜濃湯 」幾種運用新鮮南瓜的料理，難度雖然都不高，看起來卻都不像平時會出現在餐桌的家常菜。這次我們不妨搞得家常一點，用廚房裡都會有菜刀和煎鍋 （除非你家不開伙） ，來弄個超級家常的「香煎南瓜片」。
張貼留言
閱讀完整內容

上車睡覺、下車尿尿

台灣人對於旅遊的形容，往往是「上車睡覺、下車尿尿」。這是因為行程被極度壓縮，試圖要在最短的時間裡面看最多的東西，於是每個景點都只是蜻蜓點水般匆匆帶過，停留的時間往往等同於排隊上廁所的時間。 這個紫南宮的行程已在「人山人海的紫南宮」當中提到過，只是當初沒寫到為什麼要去參觀紫南宮。事實上，紫南宮最富盛名的是可以借錢的土地公。然而，這點對我們家人而言一點吸引力都沒有，反倒是那號稱七星級的廁所，才是吸引我們前往參拜的重點。或許你會說：「有沒有搞錯，開一個鐘頭的車去上廁所！」沒錯，行程就是這麼規劃的。 在過年期間要到紫南宮上廁所還不是件容易的事。首先，你必須先開車到紫南宮，這大概是最容易的部份。接著，你就得面臨搶停車位窘境。 過年期間，不論哪個景點都是人山人海，免費停車位更是難找。紫南宮雖然提供一大片停車場，在過年期間卻也不敷使用，每台進入停車場的車都虎視眈眈，只要哪邊出現一個車位，起碼就有四、五台車等著。 好不容易停好車，接下來就得穿越那群要去借錢、還錢、拜拜的人群。在那個地方，怎麼前進的都不知道，反正總會有人把你向前推進。 好不容易到達造價上億的廁所，人山人海的盛況依舊。還好這群並非全部都來上廁所，要不然廁所早就被塞爆了。聽老爸老媽說，上次他們來的時候廁所裡面都沒人，還可以在裡面拍照、欣賞噴水。但這次裡面卻塞了不少人，我也不好意思拿著那麼大的一台相機拍人家上廁所的模樣，只能拍拍外觀。 由於是在盛產竹筍的竹山鎮，廁所的外觀也用竹筍作為造型，每個竹筍下方都是一間造價昂貴的廁所。一個令我覺得很貼心的設計在於，廁所不僅僅只是男廁／女廁，還有一個殘障廁所，方便行動不便的人。 雖然招牌寫著「金筍迎客」，但是看起來那個筍子明明就是不鏽鋼的，除非把它理解成金屬。我還特地爬上廁所的樓頂，想說能不能看到些什麼特別的風景。可是過年期間，看到的除了人群還是人群。 上完廁所後，簡單徒手祭拜了提供廁所的土地公，我們又再次穿越那重重的人群。不過，我還是偷偷跑去滾了一下那個象徵「財源滾滾」的金元寶。最後，又踏上那個停車戰場，從容地離去。 我不奢望有賺大錢的機會，只要生活能過得去，偶爾還能買些奢侈品犒賞自己就夠了。不過這個願望似乎也不簡單！
張貼留言
閱讀完整內容