【Excel教學】10大常用試算表函數公式 熟用秒速計算及整理文件

撰文:文耀倫
出版:更新:

Microsoft Office的Excel能使用不同的函數公式(formula)來處理各式各樣的數值及儲存格內容,功能比純粹用來作電子版的「格仔紙」更全面。
以下是10個打工仔工作時最常可使用到的函數公式,大家不妨把此文bookmark,方便日後隨時翻看。

熟用Excel的函數公式,工作效率高自然獲得同事掌聲。(Shutterstock)

用於Microsoft Office Excel儲存格的函數公式種類繁多,複雜起上來真的會令人用得頭昏腦脹,不過有些函數公式則屬入門級水平,非常方便初階用者使用。

以下10個函數公式懂靈活運用的話,應能大大提高日常的工作效率。

本文以這個個人收支記錄試算表為例,解說10個打工仔在工作及生活時可常用的函數公式。(Excel截圖)

10大常用函數公式

1.一般總和

+2

「SUM函數」可用於計算Excel指定範圍內的數值總和,而且無論是連接、分散還是兩者混合的儲存格,都一樣可處理。

連接儲存格數值總和:=SUM(儲存格1:儲存格2)

分散儲存格數值總和:=SUM(儲存格1,儲存格2)

連接及分散儲存格數值總和:=SUM(儲存格1:儲存格2,儲存格3,儲存格4)

當然,大家亦可使用Excel工具列的「自動加總」功能,再輸入指定儲存格或拖曳滑鼠選擇計算範圍。

2. 有條件地計算總和

+1

有時大家不是要計算指定範圍內的所有數值,而是有所取捨的,就可以用「=SUMIF」這個的函數公式。SUMIF函數先後次序分別是「條件範圍」>「條件」>「數字範圍」:

條件總和:=SUMIF(條件範圍,”條件”,數字範圍)

以上列圖輯為例,你在個人收支預算中只想計算「經常支出」的總和,便可在空白儲存格輸入「=SUMIF(F3:F12,"經常支出",G3:G12)」,便可計算出經常支出的總和是「5,700」。

3. 一般平均值

要為大量儲存格的數值計算平均值,可使用「AVERAGE函數」的幫忙。與SUM函數一樣,它是處理連接、分散或兩者混合的儲存格數值:

連接儲存格數值平均值:=AVERAGE(儲存格1:儲存格2)

分散儲存格數值平均值:= AVERAGE (儲存格1,儲存格2)

連接及分散儲存格數值平均值:= AVERAGE (儲存格1:儲存格2,儲存格3,儲存格4)

4. 有條件地計算平均值

+1

與「SUMIF函數」的使用方法相似,就是為指定範圍內符合要求的儲存格數值作出計算。「AVERAGEIF函數」的先後次序亦是「條件範圍」>「條件」>「數字範圍」:

條件平均值:=AVERAGEIF(條件範圍,”條件”,數字範圍)

以上列圖輯為例,若你想計算個人「經常支出」平均使用多少錢,便可在空白儲存格輸入「=AVERAGEIF(F3:F12,"經常支出",F3:F12)」,便可獲得答案為「1425」。

5. 有條件地分類

+2

要為一列或欄的數值進行「A或B分類」,可使用IF函數公式的幫忙,相關公式是:

條件分類:=IF(儲存格="條件","符合條件之結果","不符合條件之結果"

*留意,”條件”前的「=」,是可按需要而換成「」(大於)、「=」(大於或等於)

例如,你認為$1,000以上的支出已屬大額支出,並想為「家用」這項支出分類,便可輸入「=IF(G4>=1000,"大額支出","小額支出")」,而分類結果便會顯示為「大額支出」,代表對你來說,家用是一項大額消支出。

6. 尋找最大值/最小值

+1

要在指定範圍尋找一個最大的數值,費神「慢慢碌逐格望」真的會令人眼花,不過懂得用MAX函數就可去除這煩惱,相關的函數公式為:

在連接的儲存格中尋找最大值:=MAX(儲存格1:儲存格2)

在分散的儲存格中尋找最大值:=MAX(儲存格1,儲存格2)

在連接及分散的儲存格中尋找最大值:=MAX(儲存格1:儲存格2,儲存格3,儲存格4)

另外,如想尋找指定範圍的最小值,只要將「MAX」轉成「MIN」即可。

7. 有條件地尋找最大值/最小值

+3

Excel並沒設有MAXIF的函數公式,不過大家仍可用列陣公式作計算。相關公式如下:

條件尋找最大值:=MAX(IF(條件範圍="條件",數值範圍))

*一定要留意的是,在輸入了這公式後,是要按「CTRL + SHIFT + ENTER」才能讓系統顯示正確答案,如只按「ENTER」,答案會變成「0」

以上列圖輯為例,當大家想了解自己在「經常支出」中最大的金額是多少時,可輸入「=MAX(IF(F3:F12="經常支出",G3:G12))」並按「CTRL + SHIFT + ENTER」,便可看到經常支出最大的金額是$3,000。

同樣地,如想尋找指定範圍的最小值,只要將「MAX」轉成「MIN」即可。

8. 統計儲存格數量

如要點算指定範圍內有多少個含數值(包括所有數字及日期資料)的儲存格,COUNT函數公式便大派用場。相關數值為:

統計儲存格數量:=COUNT(儲存格1:儲存格2)

9. 有條件地統計儲存格數量

+1

如要在特定條件下統計指定範圍的儲存格數量,可使用COUNTIF函數公式協助,相關公式如下:

條件統計儲存格數量:=COUNTIF(條件範圍, "條件")

以上列圖輯為例,若你想點算自己過去一段時有多少支出項目是多於$500,便可輸入「=COUNTIF(G3:G12,">500")」,然後便可見到答案是「4」。

10. 合併儲存格內容

+3

若要把兩個或以上儲存格的內容合併,不一定要每次都手動重新輸入,用合併函數公式會更方便及準確。相關公式如下:

合併儲存格內容:=CONCATENATE(儲存格1,儲存格2)

例如,你想將B4的「家用」與G4的「3,000」兩個儲存格的文字合併,只需在空白的儲存格輸入「=CONCATENATE(B4,G4)」,便會出現「家用3,000」;若輸入「=CONCATENATE(B4,” “,G4)」,則會出現「家用 3,000」,中間會預留一個空格。

熟用Excel函數公式做事更快手

建議大家把本文bookmark,在學習階段時多作參考。另外,Excel試算表還有很多值得推介的函數公式,如「VLOOKUP」、「DAY」及「ISNUMBER」等,大家慢慢學慢慢掌握,日後自然能更有效率地完成工作。

相關文章:【Excel教學】Ctrl+F不只用來改錯字?尋找取代功能可以改格式

+4

相關文章:【Excel教學】快速轉分頁、一鍵計加數 8個Excel技巧打工仔必學

相關文章:【Adobe教學】合併檔案、刪除分頁 10個整理PDF常用小技巧

立即下載《香港01》,緊貼公務員職位空缺、勞工處筍工推介,了解CV、面試致勝技巧!下載網址:https://hk01.app.link/bkbJyY5t4O