跳到主要內容

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

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

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

留言

張貼留言

熱門文章

OREO聖誕麋鹿 #差不多食譜

聖誕節就快到囉！今年我們用現成的材料，來做些簡單好玩的小東西。第一個登場的，就是有著紅鼻子的麋鹿。 3隻「OREO聖誕麋鹿」差不多需要這些材料： OREO餅乾 ...... 3片 蝴蝶餅 ...... 3-6片（掰斷有失敗的機會） 餅乾棒 ...... 3根（我用百力滋） 眼睛糖 ...... 6顆 紅色m&m's ...... 3顆（紅鼻子） 融化的巧克力 ...... 適量（黏著用） 「OREO聖誕麋鹿」差不多是這麼做的： Step 1. 準備好材料 計算一下自己想要做多少隻OREO聖誕麋鹿，按照材料表把需要的餅乾糖果給買回來。材料備齊之後，就把巧克力拿去用微波爐融化備用。沒有微波爐的也可以隔水加熱，想辦法把巧克力融化就可以。 Step 2. 製作麋鹿 再過來就要開始製作麋鹿了！先把OREO轉開，留下有奶油的那邊，待會方便把餅乾稍壓一下固定。 把蝴蝶餅掰斷成鹿角的樣子，沾上巧克力，擺到OREO上面。餅乾棒同樣沾上巧克力，一樣放上OREO。接著蓋上OREO，稍微壓一下。 這邊可以大膽去擺，在巧克力凝固前都可以調整。要是發現黏不太住，多沾點巧克力再試試。 Step 3. 擺出表情 接下來要用眼睛糖和紅色m&m's做出麋鹿的表情。用筷子將眼睛糖和m&m's沾上巧克力，黏到眼睛鼻子相應的位置就可以。 沒有辦法買到眼睛糖的，可以用白巧克力先畫出眼白，再用黑巧克力點出眼珠。紅鼻子也可以自己換成橘色、綠色、藍色等等不同的m&m's。 Step 4. 讓巧克力凝固 表情做好之後不要馬上移動，得要有點耐心讓巧克力凝固。要不然悲劇馬上就會發生，不是表情變扭曲，就是整支麋鹿散架。 等巧克力凝固後，就可以像棒棒糖那樣拿起來玩耍了。再來杯熱牛奶，整隻麋鹿給他泡下去也很有趣。 最後幫大家整理一下「OREO聖誕麋鹿」的做法： 巧克力用微波爐加熱融化備用。 轉開OREO。 蝴蝶餅掰成鹿角的形狀，沾巧克力後放上OREO餅乾。 餅乾棒同樣沾上巧克力，擺上OREO餅乾。 蓋上餅乾，在眼睛糖和紅色m&m’s背後沾上巧克力，黏到眼睛和鼻子相應的位置。 稍微等一下，讓巧克力凝固就好囉！
張貼留言
閱讀完整內容

木乃伊熱狗捲 Hot Dog Mummies - 差不多食譜 #萬聖節

這次我們繼續過萬聖節，來做個更簡單的「木乃伊熱狗捲」。看完材料表，相信你已經知道該怎麼做了。 「木乃伊熱狗捲」差不多需要這些材料： 小熱狗 ...... 4條（大熱狗可以切半使用） 起酥片 ...... 1片 蛋黃 ...... 1個 起司片 ...... 1片 海苔 ...... 1片 番茄醬 ...... 適量 「木乃伊熱狗捲」差不多是這麼做的： Step 1. 起酥片切條 從冷凍庫拿出起酥片，在室溫放一陣子，等它稍微變軟之後再開始切成細條。稍微放軟再切比較不會破掉。 Step 2. 捲熱狗 第二步，要開始加上熱狗來處理。方法也很簡單，就是把切好的起酥條一圈一圈纏上小熱狗，一條用完再接另一條繼續纏，怎麼纏都沒關係。 要是你的熱狗比較大根，可以切短一點來弄，造型會比較可愛。 Step 3. 刷蛋液送進烤箱 弄好之後來個蛋黃，稍微攪散就可以塗到纏著熱狗的起酥條上。蛋黃液是讓顏色稍微金黃一點，沒有塗蛋黃液的那些烤起來都白白的。 接著就可以送進烤箱了，用攝氏200度烤10分鐘左右，把起酥皮烤出你要的顏色就可以。 Step 4. 裝上眼睛，沾點番茄醬 出爐稍微放涼，趁這個時間來做眼睛。拿起司片弄成圓圓的眼睛，再拿一點海苔當眼球，組合好隨意擺到木乃伊熱狗上。這是萬聖節用的，真的只要亂擺就可以囉！ 最後，隨意沾點番茄醬在繃帶上面，我們的木乃伊熱狗捲就完成囉！ 結尾再幫大家複習一下「木乃伊熱狗捲」的做法： 酥片拿出來退冰，稍微軟化之後切成細條。 將起酥條一圈圈纏上小熱狗。 刷上蛋液，送入攝氏200度的烤箱烤到金黃。 稍微放涼，擺上起司片和海苔做的眼睛，再沾點番茄醬就好囉！
張貼留言
閱讀完整內容

差不多食譜：手工巧克力餅乾 Chocolate Cookies

又是手工餅乾，最近一連出了兩份餅乾食譜，這個「手工巧克力餅乾」已經是第三份了。會不會有更多呢？我可以告訴大家，這是肯定的。 要怪就怪這個陰鬱的冬季雨天，哪裡都不方便去，也懶得出去。餅乾櫃空在那邊已經很久了，雖然有時候會嘴饞，但也沒有迫切去補貨的必要。反正經常開伙，平常該有的材料都會有，自己弄個成分完全透明的零食，也是個不錯的選擇。再說，用烤箱進行烘焙時，房間會變得比較乾燥，也比較溫暖。在夏天是個折磨，但到了冬天，這種感覺還滿不錯的。 話不多說，開始進行這一道「手工巧克力餅乾」的準備工作。
張貼留言
閱讀完整內容