【Excel公式教學】數據分析必用的14個Excel公式

2019-11-07

Excel公式
ExcelExcel公式教學Excel常用公式

對很多人而言,Excel是一個既熟悉、又陌生的存在:雖常有耳聞,或使用它作資料輸入,但你真的了解Excel中讓你「值回票價」的功能嗎?

Excel公式用途大至計算帳目、業績,把握銷售數據,以作市場分析或回顧;小至整理客戶、貨物資料,以及更新狀況,如計算合約或會期完結時間,功能可說是應有盡有。只要學會善用Excel公式,無論是行政或會計方面,皆能讓你事半功倍!

以下筆者整理了14個Excel常用公式,輔上簡單實用的應用例子,即使是不通數理或電腦技能的你,在下列的excel formula 教學中也能輕鬆掌握Excel公式技巧!

一、 業績計算的Excel公式

以下為以計算總和為主的Excel公式教學範例。

(1) 一般總和 = SUM(數字範圍)

這是最簡單的excel公式加總的計算方式,例如想計算C2至C10的銷售量總額,便可於C11(或其他)格中輸入「=SUM(C2:C10)」。

(2) 條件求和 = SUMIF(條件範圍, "條件",數字範圍)

這條算式加入了篩選項目,例如想計算C2至C10中「檸檬茶」的銷售量總額,便可於格中輸入「=SUMIF(B2:B10,"檸檬茶",C2:C10)」。

(3) 多條件求和 = SUMIFS(數字範圍,條件1範圍,"條件1",條件2範圍,"條件2")

與上一條公式操作相若,例如想計算單次銷售量多於100的檸檬茶銷量總額,則可輸入「=SUMIFS(C2:C10,B2:B10,"檸檬茶",C2:C10,">100")

注:條件可為文字,亦可以數量範圍,如上述的條件1(文字)及條件2(數量範圍)。

(4) 乘積總和 = SUMPRODUCT((列/欄1數字範圍),(列/欄2數字範圍))

此算式涉及Excel公式乘法的功能,它會將欄1的每個儲存格對應欄2次序相對的儲存格,並計算其相乘的結果之總和。需要注意的是兩個必須有相同的維度,即相同的列數或欄數

例如計算所有產品的銷售總金額(即銷售量*價錢),可輸入「=SUMPRODUCT((C2:C10),(D2:D10))」

Excel公式加總

二、 數據分折的Excel常用函數

以下為以Excel分析數據時的常用函數,如平均值、最大值、項目數量等公式大全。

(1) 一般平均值 = AVERAGE(數字範圍)

這個可用作計算某範圍內的平均值,例如計算所有產品的銷售數量平均值,便可輸入「=AVERAGE(C2:C10)」。

(2) 指定條件的平均值 = AVERAGEIF(條件範圍, "條件",數字範圍)

與上一條算式做法相約,但為加入了Excel公式IF的篩選條件,是一種進階用法,例如想計算某項產品(如牛奶)銷量的平均值,便可輸入「=AVERAGEIF(B2:B10,"牛奶",C2:C10)」。

若要做多條件的平均值,則與多條件求和做法相約,即「=AVERAGEIFS(數字範圍,條件1範圍,"條件1",條件2範圍,"條件2")」。

(3) 最大值 = MAX(數字範圍)

此條Excel Formula可用以尋找某範圍中單次最高銷售量的數目,例如想知道上半年(即C2至C5)的最高單次銷量,便可輸入「=MAX(C2:C5)」。

(4) 指定條件的最大值

輸入「=MAX(IF(條件範圍="條件",數字範圍))」;之後再按Ctrl + Shift+Enter,令其成為「{=MAX(IF(條件範圍="條件",數字範圍))}」

注:手動添加{}會變成無效的「#VALUE!」

這個可以篩選尋找最大值的條件,例如尋找全年牛奶的單次最高銷售數量,可輸入「=MAX(IF(=B2:B10,"牛奶",C2:C10))」,再按Ctrl + Shift+Enter。

值得留意為若想於(3)及(4)中尋找最小值,只需將MAX轉為MIN即可。

Excel常用函數

(5) 條件統計 = COUNTIF(條件範圍, "條件")

這個用法可用作統計某項目或產品分類曾出現過多少次,例如統計檸檬茶的銷售次數,便可輸入「=COUNTIF(B2:B10, "檸檬茶")」。

(6) 多條件統計 = COUNTIFS(條件範圍1, "條件1", 條件範圍2,"條件2")

這個用法可對(5)的統計數字作進一步篩選,例如統計銷量多於100的檸檬茶銷售次數,便可輸入「=COUNTIFS(B2:B10, "檸檬茶",C2:C10, ">100")」。

Excel formula

(7) 條件及結果 = IF(儲存格<=數字, "符合條件之結果","不符合條件之結果")/=IF(儲存格>=數字, "符合條件之結果","不符合條件之結果")

這個用法可用於快速計算及分析數據表現,例如銷量等於或高於100為表現「佳」,低於100為表現「差」,則可輸入「=IF(C2<=100,"差","佳")」。

注:可以點擊右下角的點,拉延至下,以劃分其他產品的銷售表現。

Excel條件公式

三、行政資料的Excel常用公式

以下為作資料整理時的Excel 常用公式教學。

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

與上一項Excel公式用法有點類似,不過是次的條件篩選並非數字範圍,而是以公式文字作條件篩選。例如想劃分A2客戶所在地於本地或外地,我們可輸入「=IF(B2="香港","本地","非本地")」。

Excel資料整理公式

(2) 日期的季度劃分 = LEN(2^MONTH(日期))&"季度"

此功能讓你輕易劃分圖表中的日期季度,例如想顯示3月5日的檸檬茶屬於第幾季度,便可輸入「=LEN(2^MONTH(A2))&"季度"」。

注:日期輸入需為「日/月/年」。

Excel公式 - LEN

(3) 剩餘天數 = DAY(儲存格-TODAY())

此功能非常方便,亦運用了Excel 公式減法及「TODAY」的功能為你倒數,例如想顯示合約到期的剩餘天數,只要輸入「=DAY(C2-TODAY())」即可。

注:需要於選取該欄(D2至D7)範圍,左鍵點選選單中的「儲存格格式(Format Cells)」,然後點選「自訂(Custom)」及「一般(General)」,才能成功轉移成顯示天剩餘數。

Excel公式 - DAY

除上述以外,Excel還有很多其他功能,如公式VLOOKUPMatch教學等,未能一一盡錄。不過能把握好上述14個公式已可以讓你處理數據、資料上井井有條,但如果你面對的是需慎重處理的財務編制及繁複的數據分析,建議你交由Workeroom的專業Excel及VBA人才為你代勞。

若你擅於處理Excel及VBA的數據處理,歡迎你免費註冊及加入Workeroom,接取Freelance工作!