iT邦幫忙

2021 iThome 鐵人賽

DAY 22
0
自我挑戰組

大學生必知的30個Excel技巧系列 第 22

Day–22 快速認識Excel SUM家族

今日練習檔 ԅ( ¯་། ¯ԅ)

接下來的幾天,我將把重點放在Excel的常用公式,不過在開始之前,我們先來看看一題經典的練習位址的題目,接著後半段我將介紹SUM家族,那我們開始吧 ᕕ( ᐛ )ᕗ

首先是位址的部分,可以搭配練習檔中的「位址練習」一起看。這是一張學期成績表,如下圖所示:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667oStiaihtR2.png
要計算學期成績,計算方式就是將三個成績相加,並且每個成績都要乘以相對應的比重,以1號同學為例,就是90*0.3+70*0.35+80*0.35,不過當然不能直接這樣打,要用公式的方式。大家可以自己試試看,需要用到絕對及相對位址唷!

好的,那首先第一步當然是先輸入「=」,這樣Excel才知道你要計算了~接著就是用滑鼠選擇C6,接著輸入「*C3+」,後面兩個成績以此類推,會得出下列結果:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667G9XH8hv857.png
可以看到1號是正確的,但在自動填滿後下面可以說是錯的離譜,不但數值過大,甚至還有錯誤訊息。其實這個狀況會很常遇到,遇到時不要緊張,先檢查下面那一格的公式是否正確(F7),點選後再點選上方的公式欄,就會有顏色的輔助,比較好檢查,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/201276677Bu5j3QtJK.png
可以看到比重列完全跑掉了,經過前兩天試煉的你,我相信你一定知道為什麼?其實就是我們從 F6->F7 列+1,因此公式的列全部都加一,因此在比重格的列應該要加上「$」鎖住才行。

這邊要注意,要改的話要回到第一格改會比較好喔!改成如下圖,並再次按下自動填滿。
https://ithelp.ithome.com.tw/upload/images/20210907/20127667QyCNxwZTmK.png
如此就會發現完成了ㄟ~~,沒想到吧,其實正確跟錯誤之間往往只有一字之隔,大家在實作時如果遇到錯誤也不要氣餒,只要細心一點檢查就一定可以將問題迎刃而解 ᕦ(ò_óˇ)ᕤ

SUM家族

終於要進到公式的介紹,第一個要介紹的就是最常見也最簡單的SUM,除了SUM之外,還有SUM的爸爸SUMIF,以及媽媽SUMIFS

進到這題,可以搭配今日練習檔中「SUM家族」進行練習,請原諒我沒有排版 <(_ _)> 首先題目樣子為:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667C0WiYJDqpE.png
首先我們先來計算金額總額,在今天之前,您可能知道的做法是先點選E3,並在公式列輸入「=C2+C3+C4+...+C10」,但這種方法僅限於個數少的資料,像這樣有9筆資料以上最好就要使用公式SUM,使用方法相當簡單,一樣先選取E3,並且在公式列輸入「=S」(符號一率半形、英文大小寫皆可),這時就會出現如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667RzF9uo9F5C.png
可以看到Excel自動跑出一些S開頭的公式,利用鍵盤上、下進行選取,選到SUM上面按下「Tab」「Tab」「Tab」,是Tab不是Enter,因為很重要所以說三次。接著會變成「=SUM(」,並且要將要加總的格子選起來,可以直接用滑鼠拖拉,或者是用輸入的,注意中間是「冒號:」不是「逗號,」,完成後結果如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667VzJDSYHTpN.png
最後再輸入最後一個「)」並按下Enter就會自動加總囉!
https://ithelp.ithome.com.tw/upload/images/20210907/20127667P2dLTZFwLu.png
補充:
若要相加不相連儲存格,則是需要壓著control鍵(Mac為command鍵),才可以相加,並且會以不同的顏色標示出來,兩個範圍間也會以「,」隔開,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667pbq3x0tf2V.png
接著是要計算廣告部總額,因此邏輯為判斷A欄的每一格是否是廣告部,如果是就把相對應的金額加總。像這樣只有一個條件的就是使用SUMIF

首先點選E6,並輸入「=SUMIF」(一樣可透過輸入S後按下Tab鍵快速選取,可減少打錯的風險),結果如下:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667Z8COr2883o.png
此時會看到Excel有跑出一個匡格提示您該如何填寫,不過他是寫英文,您可能不知道rangecriteria是什麼意思,不過不用擔心,您可以點選上圖用紅色框框匡起來的Fx(插入函數),點下去之後會跳出一個視窗(或是在畫面的右側)出現中文的設定窗格,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667p8OHvUPgKU.png
此時下面就會有每個條件的中文解釋,不過還是太複雜,因此才有我的存在哈哈!首先第一格就是要判斷的儲存格,我們在這題要判斷什麼?是不是就是判斷A2:A10哪些為廣告部?因此在這邊我們就可以輸入「A2:A10」,或這是點選旁邊那個小小的圖示就可以直接用選取的,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/201276670WE7SoUlJV.png
如果成功的話就會看到上面會變成Range={“編輯部,”企劃部”....},其實就是對應到表中的A2,A3,....。接著就是Criteria,這個是條件(準則)的意思,因此在這邊我們要輸入的是「”廣告部”」,記得要加上雙引號,這樣才會是文字型態。或者是你點選內容為廣告部的儲存格(A5、A6、A9),如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667MxcOZcDF1Z.png
補上若您輸入「廣告部」會產生的結果:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667ZTeeXMxhor.png
緊接著是最後一欄,也就是要相加的儲存格,而這邊無庸置疑的當然是C2:C10,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667knWVwelILn.png
可以看到整張圖非常繽紛,分別為藍、紅、紫,也對應到相應的位置,讓使用者在編寫時可以非常清楚,完成後按下「完成」,就完成此格囉~

最後是SUMIFS,可以發現和SUMIF相比多了個S,也就是可以有多個判斷式,在這邊我們要判斷是廣告部且為印刷費,因此就不多說,在上方輸入「=SUMIFS」並按下「插入函數」,結果如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667p7F932Bdfs.png
可以看到這邊有跟前面兩個有所不同,第一格為Sum_range,因此是要先輸入要相加的儲存格,也就是C2:C10,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667EegpUOHni9.png
接著下方兩格為第一個條件,分別為「判斷一個範圍」是否等於「一個值」,因此首先是判斷A2:A10是否等於A5(或A6、A9、”廣告部”),這幾個是一樣的東西,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667YrFjHVhG3b.png
接著還有第二個條件,因此要按下+號,並且要按下兩下,因為一個是判斷位址,一個是值,按下兩下後結果為下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/20127667dmIAwSN5dR.png
可以看到又跑出了一個判斷區間寫Criteria_range2,因此我們要判斷是否為印刷費,如下圖:
https://ithelp.ithome.com.tw/upload/images/20210907/201276679kHyQQ1vqW.png
可以看到每一格後面就會有一個灰色的字顯示該格出來的結果,這個可以幫助使用者檢查是否有錯誤,接著按下完成就大功告成啦!
https://ithelp.ithome.com.tw/upload/images/20210907/20127667EjCdh4LqA3.png

結論及補充:
經過今天的講解後,不知道大家對於位址以及SUM家族是否有更近一步的認識了呢?如果你覺得好像有又好像沒有的話,相信我,你已經會了,只要多使用幾次便能得心應手。有關於SUMIFS其實可以加無限個條件,並不僅限於兩個,不過要注意不能只有範圍而沒有值喔!Excel會提醒您「輸入過少的引數」。另外對於初學者我一率建議要叫出「插入函數」,透過人性化的介面才可以讓您設定的更快更美。若您真的已經很熟悉,也可以在上方的公式列直接輸入唷!

軒軒的笑話時間:
小明今天考完試
拿考卷回家給老爸看
老爸看完:數學0分、國語1分…
此時,空氣凝結、氣氛恐怖無比
小明感覺到一場腥風血雨即將發生在自己身上,顫抖中….
老爸深呼吸一口氣,語重心長地說道:「小明啊,




你文科比較強喔!」
/images/emoticon/emoticon12.gif


上一篇
Day-21 Excel位址精選題目練習
下一篇
Day-23 你沒想到的Excel average知識
系列文
大學生必知的30個Excel技巧30

尚未有邦友留言

立即登入留言