張玉琦、吳瑄庭
Excel6實用函數公式教學|COUNTIF、IF、IFS、COUNTA、MAX、SUMIF
Excel 中有超過數百個函數,在職場最常出現的狀況,就是遇到報表,卻不知道 Excel 函數的用法?本篇文章將帶你搞懂 Excel 常見 6 大公式,包含邏輯比較 IF、IFS、條件加總 SUMIF、統計 COUNTA、條件統計 COUNTIF、最大值 MAX 並搭配函數的實際案例說明,讓你提升工作效率!
Excel 必懂公式 1. IF 用法:哪些分店的業績符合預算目標?
假如你是一家服飾品牌的品牌經理,管了 10 家分店,店長交給你 2015 年上半年的營業額報告(如圖)。該怎麼比較這些分店的表現如何呢?
你可以把這些店的業績跟他們去年的預算目標相比較,你不需要一家家比對,只要運用 Excel 當中的 IF 函數就可以囉。IF 函數的意思是,設定條件請 Excel 找出符合條件的數值,公式寫法為 =IF(條件, 符合條件的回傳值, 不符條件的回傳值)。
條件設定為,「營業額超過或等於預算」,符合時表格會出現「達標」, 不符時表格會出現「未達標」。則可以在 Excel 上輸入 = IF(B3>=C3, “達標”,”未達標”)。
(小提醒,Excel 的各式函數要以等號 = 開頭,條件放在括弧內。)
▲ IF 函數操作範例,來源:Excel
假如,你想把店面分成三個等級:超達標 20% 以上的 A 級店、剛好達標表現一般的 B 級店、和有待改善的 C 級店時,你可以輸入兩個以上的條件。公式寫為 =IF(B3>=C3, IF(B3/C3>=1.2, “A”,”B”), “C”) 。
首先指定第一個條件是營業額超過預定目標,如果符合再看營業額是否超過預定目標預算 20%,是的話填入 A ,否的話填入 B。一開始就不符合條件一的填入 C。
Excel 必懂公式 2. IFS 用法:用預算目標將店家分成A、B、C不同等級
若要帶入的條件較多時,也可以使用 IFS 函數,IFS 用於檢查是否符合多個條件,符合便會回傳相應的值,公式為 =IFS(條件一, 符合條件一的回傳值,條件二, 符合條件二的回傳值,條件三, 符合條件三的回傳值...以此類推)。
想將店面分成三個等級時,除了使用 =IF(B3>=C3, IF(B3/C3>=1.2, “A”,”B”), “C”)的寫法,也可以寫成=IFS(B3/C3>=1.2,”A”,B3/C3>=1,”B”,B3/A3<1,”C”)。符合第一個指定條件營業額超過預定目標 20%,填入 A,若未符合條件一但超過預定目標(即 B3/C3 介於 1 至 1.2 之間),則填入 B,若營業額未超過預期目標(B3/C3<1)則填入 C。
實際上使用 Excel 中的 IF、IFS 函數寫法會寫成:
=IF(B3>=C3, IF(B3/C3>=1.2, “A”,”B”), “C”)
=IFS(B3/C3>=1.2,”A”,B3/C3>=1,”B”,B3/A3<1,”C”)
=IF(條件, 符合條件的回傳值, 不符條件的回傳值)
=IFS(條件一, 符合條件一的回傳值, 條件二, 符合條件二的回傳值, 條件三, 符合條件三的回傳值...以此類推)
Excel 必懂公式 3. SUMIF 用法:毛衣類商品的總營業額是多少?
SUMIF 是告訴 Excel, 只統計你指定條件的項目,公式寫為 =SUMIF(資料範圍, 條件, 要加總的欄位)。當你看到三家分店的每一筆商品售出紀錄時,想知道全部分店加起來賣出的毛衣金額總額?你可以指定在商品品項(B 欄)中,找出符合毛衣的類別(G4),並加總他們營業額(E 欄)。
▲ SUMIF 函數操作範例,來源:Excel
實際上使用 excel 中的 IF 函數寫法會寫成:
=SUMIF(B3:B16, G4, E3:E16)
=SUMIF(條件範圍, 條件, 合計範圍)
Excel 必懂公式 4. COUNTA 用法:有幾個人回答了問卷?
假設你針對消費者發行問卷,調查顧客滿意度,這時 Excel 也可以派上用場。當你拿到意見調查的回函,首先你最想知道的,當然是有效回答問卷的有多少人?用 COUNTA 函數,就可以快速找出特定範圍的欄位中,「有」輸入資料的格子有幾個。
▲ COUNTA 函數操作範例,來源:Excel
實際上使用 excel 中的 COUNTA 函數寫法會寫成:
=COUNTA(A4:A13)
=COUNTA (計算範圍)
Excel 必懂公式 5. COUNTIF 用法:達成率超過 80% 的業務有幾個人?
假設你是業務經理,管理一批業務團隊,Excel 報表中可以看到每個人拜訪的客戶數量、成交次數,以及每個人的成交率。想知道你的團隊中,有多少人的業績達成率超過 80%,只要讓 Excel 在成交率那一欄(E欄)裡,找出有大於 0.8 的有幾個即可。COUNTIF 函數,可以快速篩出特定範圍的欄位中,符合條件的欄位有多少個;同理,不只可以算出成交率,也可以輸入不同條件,找出「所有會員中 / 女性有幾位?」「所有會員中 / 付費訂閱有幾位?」
▲ COUNTIF 函數操作範例,來源:Excel
實際上使用 excel 中的 COUNIF 函數寫法會寫成:
=COUNTIF(D3:D10, “>=0.8”)
=COUNTIF(條件範圍, 條件)
Excel 必懂函數 6. MAX 用法:這個商品的最高實銷率?
同一個商品,在不同分店銷售的情況不同時,想找出究竟哪一家店賣得最好時,函數來找,功用是找出範圍內的「最大數字」。報表中顯示了《經理人月刊》在全台便利商店的銷售率,假如你想找出《經理人月刊》在全台北便利商店中的最高實銷率為多少,就要用 MAX 加上計算範圍,輸入 =MAX(B4:B21)。同理,若要找出最小值(最低實銷率),只要使用 MIN 最小值函數,加上計算範圍就可以囉!
操作實務上,MAX用法 的好用之處其實要和其他函數如 IF 做搭配,假設你拿到每天的營業額,你可以用 MAX 加 IF,找出某位員工(或某月份內)的最高營業額數值。
▲ MAX 函數操作範例,來源:Excel
實際上使用 Excel 中的 MAX 函數寫法會寫成:
=MAX(B4:B21)
=MAX(計算範圍)
同理,MIN 函數寫法則為:
=MIN(B4:B21)
=MIN(計算範圍)
本次整理包含 IF、IFS、SUMIF、COUNTA、COUNTIF、MAX 在內的 6 大 Excel 函數與範例,希望幫助您在工作上快速解決報表上的疑難雜症!