Excel 公式函數大全:教你12個必學常用公式表格功能

Excel 公式函數大全:教你12個必學常用公式表格功能

聽到公式及函數,或許會讓許多人卻步,但這也是Excel可以簡化運算及資料分析的重要功能,且公式及函數的運用方式,其實已經十分簡化,只要觀念對了,並確實了解公式及函數的意義,自然就可以化繁為簡,輕鬆製作Excel報表。更複雜的計算及資料分析,就可以利用函數來處理,Excel預設了300個以上的函數,不過一般需求使用,只要學會幾個常用的函數就很實用。

以下精選12個必學的公式/函數與觀念,可從目錄快速到達想找的答案。

文章目錄

1、SUM加總函數,不連續的加總也能省時又零失誤

除了使用「Σ」的功能鍵及自訂公式進行加總外,Excel提供的函數裡,SUM的功能就是加總,對於加總的範圍較大,又分散在不同區塊時,利用SUM函數進行加總,能夠省時又減少失誤,因為只要跟著視窗的指引,一步步用滑鼠點選,就可以精準的選定加總範圍,且少了自行輸入儲存格會失誤的風險,正確率也較高。此範例為計算12個月中,雙數月所花費的金額,因此必須分別加總6個月的各項花費。

步驟1.點選儲存計算結果的儲存格後,在工作表上方,找到代表函數的「ƒx」鍵,於出現的視窗選擇「SUM」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.在「函數引數」的視窗裡,按下「Number1」後方的紅色箭頭,視窗就會縮小,回到工作表。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.在工作表中,利用滑鼠選擇要加總的儲存格,在按下回到「函數引數」視窗的按鍵。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.如果要加總計算的範圍很分散,就分別在「Number2」、「Number3」⋯等,繼續以步驟2~3的方式選取。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟5.上步驟都選取完成後,按下「確定」,就會產生加總的結果。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟6.在步驟1按下的「ƒx」鍵後方,會有函數內容,利用滑鼠點選一下,則會以不同顏色顯示加總位置,以利於除錯比對。

Excel 公式函數大全:教你12個必學常用公式表格功能

 

2、SUMIF函數:符合條件的數字才加總

除了SUM函數用於加總外,SUMIF也是用於加總的函數,不過是在指定的範圍內,有條件的加總儲存格的數值,舉例來說,公司平時都有記錄各部門的暫付款支出,但是採用流水帳的方式記錄,若想要查詢在特定期間,每個部門的支出金額,就可以使用SUMIF函數計算。而SUMIF函數代表的公式為:SUMIF(資料範圍,你的條件,要加總的欄位)。

步驟1.先在空白的地方建立想要統計的部門,此例為編輯部、企畫部、廣告部,並先將滑鼠點向編輯部要顯示的儲存格。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.找到代表函數的「ƒ x 」鍵, 並於出現的視窗選擇「SUMIF」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.出現的視窗中,Range要選擇資料範圍,Criteria選擇條件,Sum_range則是加總的欄位。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.因此Range選A1:A10的資料範圍;Criteria選E2,是步驟1建立的部門;Sum_range為C1:C10,為加總的欄位。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟5.上步驟都選定完成後,按下確定就會回到工作表,在步驟1新增的儲存格,已經出現結果。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟6.同樣以按下滑鼠左鍵下拉的填滿方式,其他部門的金額也會立即計算出來。

Excel 公式函數大全:教你12個必學常用公式表格功能

3、AVERAGE函數計算平均值

在常用的函數中,AVERAGE是用來計算平均值,與用來加總的SUM函數一樣,當具有大量資料需要快速求得計算結果,甚至是不連續資料的計算時,使用AVERAGE函數,跟著指示操作就能輕鬆計算出平均值,此範例就以計算6科考試成績的平均分數。

步驟1.點選要顯示計算結果的儲存格,找到代表函數的「ƒx」鍵,並於出現的視窗選擇「AVERAGE」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.在出現的「函數引數」視窗中,「Number1」利用滑鼠選擇B2:H2,或手動輸入都可以。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.回到工作表畫面時,平均值就已經計算出來了。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.同樣以按下滑鼠左鍵下拉的填滿方式,後面的平均分數也會自動算出。

Excel 公式函數大全:教你12個必學常用公式表格功能

4、計算「平均值」用功能鍵快速操作

由於加總及平均值的計算,應該算是最常會在工作表中運用到的計算方式,因此除了在「常用」索引標籤的「編輯」項目裡,能看到「Σ」的功能鍵外,展開「Σ」的選單,還會看到「平均值」,也就是說,如果是資料連續的簡單計算,只要按下「平均值」功能鍵,也可以計算出平均值,而不用使用函數,當然,如果要計算的資料位置太過分散,就還是得採用函數處理。

步驟1.先選擇要儲存結果的位置,再展開「Σ」的選單,按下「平均值」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.接著就會自動判斷要計算平均值的資料,確認無誤即可以按下Enter鍵,完成平均值的計算。

Excel 公式函數大全:教你12個必學常用公式表格功能

5、ROUND函數:將數字四捨五入至指定位數

與數學相關的函數中,ROUND也是經常會使用到的函數,主要是將數字四捨五入至指定位數,例如,若儲存格A1顯示的數值為35.7825,若想要將該數值四捨五入至小數點後兩位,就可使用函數ROUND(A1, 2)進行。而ROUND函數代表的公式為ROUND(number, num_digits),中文解釋則是ROUND (要執行四捨五入計算的數字,執行四捨五入計算時的位數)。

步驟1.點選要顯示計算結果的儲存格, 找到代表函數的「ƒx」鍵, 並於出現的視窗選擇「ROUND」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.於「函數引數」視窗裡的「Number」選擇要執行四捨五入計算的數字, 此例為H2。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.緊接著就在「Num_digits」後方輸入「2」,代表該數值四捨五入至小數點後兩位, 再按下確定。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.回到工作表中,利用按滑鼠左鍵下拉的填滿方式,就會看到所有數值都提供至小數點後兩位的結果。

Excel 公式函數大全:教你12個必學常用公式表格功能

6、ROUNDUP函數:數值無條件進位

學了ROUND函數,就可順便再記一個ROUNDUP,顧名思義是由ROUND延伸出來,用意是將數值無條件進位,例如A1的儲存格資料為72.3,利用ROUNDUP函數計算後,就會變成73。而ROUNDUP函數代表的公式為ROUNDUP(number,num_digits),文字解釋則是ROUNDUP (要執行無條件進位的數字,數字進位的位數)。

步驟1.點選要顯示計算結果的儲存格,找到代表函數的「ƒx」鍵,並於出現的視窗選擇「ROUNDUP」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.接著於「Number」選擇儲存格位置,此例為H2,在「Num_digits」後方輸入「0」,代表將數字進位到整數。

Excel 公式函數大全:教你12個必學常用公式表格功能

7、CONUT函數:計算儲存格為有效數值的數量

在Excel函數中,統計函數也是經常被使用的類別,當中CONUT函數可用來統計資料數量,因為當需要統計的資料相當龐大,並分布在多個工作表時,必須先計算有效資料的數量,再進行計畫性的分析,因此透過CONUT函數就能快速在多個工作表中,計算出有效的資料量,也就是有效數字的儲存格數量。

步驟1.隨意點選空白儲存格,找到代表函數的「ƒx」鍵,並於出現的視窗選擇「統計」類別下的「COUNT」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.於「函數引數」視窗裡的「Value1」下,分別選擇要計算的工作表內容,最後就會得出有效資料的數量。

Excel 公式函數大全:教你12個必學常用公式表格功能

8、CONUTIF函數:計算符合條件的資料數量

CONUTIF則是用來統計在指定範圍內,有符合條件的資料數量,通常會應用在龐大工作表中的統計,當想要快速找出符合條件限制的資料量有多少時,就可以利用CONUTIF函數。例如想要計算全班的段考成績中,不及格的分數有多少個。而COUNTIF函數代表的公式為COUNTIF (Range, Criteria),文字解釋則是COUNTIF (要計算的儲存格,條件限制)。

步驟1.隨意點選空白儲存格, 接著按下代表函數的「ƒx」鍵,並於出現的視窗選擇「統計」類別下的「COUNTIF」。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.於「函數引數」視窗裡, 先在「Range」下選擇統計的儲存格範圍。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.緊接著就在「Criteria」後方輸入限制的條件,例如此例要找到小於60的數量,就手動輸入<60。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.回到工作表中,在步驟1選定的儲存格上,就會標出統計出來的數量。

Excel 公式函數大全:教你12個必學常用公式表格功能

9、IF函數:讓符合條件的計算產生A結果,否則產生B結果

在Excel提供的邏輯函數裡,IF函數算是十分實用的一個。在IF函數的使用上,是藉由假設是與否的條件,來達到資料分析的結果,當符合設定條件時,則出現「TRUE」的結果,不符合條件時,則出現「FALSE」的結果。而IF函數代表的公式為IF(Logical_test,Value_if_true,Value_if_false),文字解釋則是IF (限制的條件,符合條件傳回的結果,不符合條件傳回的結果)。

步驟1.點選要顯示結果的儲存格,按下「ƒx」鍵,並於出現的視窗選擇「邏輯」,找到「IF」函數。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.於「函數引數」視窗裡,先在「Logical_test」下設定假設的條件,此例為H2<60。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.接著在「Value_if_true」設定假設條件為「是」時,會出現的結果,此例為不及格。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.下一步在「Value_if_false」設定假設條件為「否」時,會出現的結果,此例為及格。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟5.上步驟都確定後,回到工作表上,步驟1選定的儲存格上,就會標示出結果。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟6.利用按滑鼠左鍵下拉的填滿方式,整個表格也會自動出現IF函數獲得的結果。

Excel 公式函數大全:教你12個必學常用公式表格功能

10、EDATE函數:自動計算年與月的到期日

在Excel提供的日期及時間函數裡,EDATE函數是用來計算起始日開始,再經過幾個月後,正確的到期日期,計算的過程中會自動跨年及判斷月份的天數,即使二月有29或28日,也都能夠成功標示。此外,因為EDATE函數是Excel 2007以後才提供,而儲存格中的日期通常會以稱為序列值的連續數字來儲存日期,所以當發現日期出現奇怪的數字時,只要變更日期格式即可。而EDATE函數代表的公式為EDATE (Start_date, Months),文字解釋則是EDATE (開始日期,之前或之後的月份數)。

步驟1.點選要顯示結果的儲存格,按下「ƒx」鍵,並於出現的視窗選擇「日期及時間」,找到「EDATE」函數。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.於「函數引數」視窗裡,先在「Start_date」下選擇開始的日期,此例選擇B2儲存格。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.下一步在「Months」選擇計算的月份,也可直接選擇工作表上的儲存格,再按下確定即可。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.回到工作表中若發現計算出的結果不是日期,於「常用」索引標籤的「數值」項目,下拉「通用格式」選單。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟5.於出現的選單裡,選擇想要顯示的格式,此以「簡短日期」為例。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟6.再回到工作表就會發現,已變成日期顯示,且每個月的天數都會自動計算,之後同樣可利用填滿功能將表格完成。

Excel 公式函數大全:教你12個必學常用公式表格功能

11、什麼是「相對位置」與「絕對位置」

在工作表中進行公式及函數的計算,必須先了解「相對位置」與「絕對位置」,由於儲存格的代號是先出現行再出現列,例如A1就是A行的第1個位置,工作表中進行儲存格的填滿時,預設都是相對位置的變化,但有時候在進行計算時,必須要固定行或列的位置,就可以選擇在鎖定的行或列代碼前,加上「$」符號,設定為絕對位置,例如A$1表示1列為絕對位置。

步驟1.當設定兩個儲存格相乘時, 若沒有特別指定,行與列預設都是「相對位置」的變化。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.當指定乘數的列為絕對位置時,只要在數字前方加上「$」符號,計算出的答案就已經不同。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟3.將乘數與被乘數的列都設定為絕對位置,也就是數字前分別加上「$」符號,又獲得不一樣的結果。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟4.再將乘數與被乘數的行都設為絕對位置,也就是字母前分別加上「$」符號,又是不一樣的結果。

Excel 公式函數大全:教你12個必學常用公式表格功能

12、常用函數用首字搜尋,計算範圍讓滑鼠快選

公式與函數是Excel裡相當重要的計算與分析功能,簡單的計算利用公式就能快速完成,較複雜的分析就可仰賴函數的運算,而Excel內建了11大類別的函數,包括財務、日期及時間、數學與三角函數、統計⋯等,共超過300個以上的函數,如果對特定函數已經相當熟悉,其實只要輸入函數的首字就可快速搜尋,再配合滑鼠選取計算範圍,很快就能完成計算與分析。

步驟1.在要顯示結果的儲存格上,先按下「=」,並輸入函數的第一個字母,就會列出所有函數,點選後還有功能說明。

Excel 公式函數大全:教你12個必學常用公式表格功能

步驟2.上步驟選定函數後,就可以在工作表中,利用滑鼠選擇要計算的範圍。

Excel 公式函數大全:教你12個必學常用公式表格功能 

本文同步刊載於PC home雜誌
 
資料化繁為簡,報表自動分析-14招速學 Excel公式及函數
歡迎加入PC home雜誌粉絲團

 

WL.
作者

PC home雜誌、T客邦產業編輯,主要負責蘋果、手機、筆電、桌機、平板等產業領域,以及相關硬體的教學以及評測、軟體相關教學報導。

使用 Facebook 留言

發表回應

謹慎發言,尊重彼此。按此展開留言規則