首頁 »
October 31, 2015

Excel基礎班看過來!最實用5大函數在這裡!


© 由 經理人月刊 提供 image

Excel有超過數百個函數,到底該從哪學起呢?遇到報表該怎麼分析才好呢?這篇文章要教你最好用的5大Excel函數!

1. 哪些分店的業績符合預算目標?IF

假如你是日本一家服飾品牌的品牌經理,你手下管了10家分店,店長交給你2015年上半年的營業額報告(如圖)。該怎麼比較這些分店的表現如何呢?

很簡單,你可以把這些店的業績跟他們去年的預算目標相比較,你不需要一家家比對,只要運用excel當中的IF函數就可以囉。IF函數的意思是,設定條件請excel找出符合條件的數值。

所以你的條件設定為,「營業額超過或等於預算」,符合時格子會出現「達標」, 不符時,格子會出現「未達標」。在excel上輸入=IF(B3>=C3, “達標”,”未達標”)」(小提醒,算式要以等號開頭,條件放在括弧內。)

© 由 經理人月刊 提供 圖說明

假如,你想把店面分成三個等級:超達標20%以上的A級店、剛好達標表現一般的B級店、和有待改善的C級店時,你可以輸入兩個以上的條件。

首先指定第一個條件是營業額超過預算,如果符合的話再看營業額是否超過預算20%,是的話填入A,否的話填入B。一開始就不符合條件1的填入C。

實際上函數會寫成=IF(B3>=C3, IF(B3/C3>=1.2, “A”,”B”), “C”)

函數寫法=IF(條件, 符合條件, 不符條件)

2. 毛衣類商品的總營業額是多少?SUMIF

這個函數我們在之前的SUM進階版中講過,不過這裡還是要再介紹一下。當你看到三家分店的每一筆商品售出紀錄時,想知道全部分店加起來賣出的毛衣金額總額?你可以指定在商品品項(B欄)中,找出符合毛衣的類別(G4),並加總他們營業額(E欄)。

© 由 經理人月刊 提供 圖說明

函數則寫成=SUMIF(B3:B16, G4, E3:E16)。

函數寫法=SUMIF(條件範圍, 條件, 合計範圍)

3. 有幾個人回答了問卷?COUNTA

假設你針對消費者發行問卷,調查顧客滿意度,這時Excel也可以派上用場。當你拿到意見調查的回函,首先你最想知道的,當然是到底有多少個人回答了問卷?

© 由 經理人月刊 提供 圖說明

用COUNTA函數,找出姓名欄中,有輸入資料的格子有幾個就可以了。

輸入=COUNTA(A4:A13)

函數寫法=COUNTA (計算範圍)

4. 達成率超過80%的業務有幾個人?COUNTIF

假設你是業務經理,底下管了一批業務團隊,報表中可以看到每個人拜訪的客戶數量、成交次數,以及每個人的成交率。想知道你的團隊中,有多少人的業績達成率超過80%,只要請excel在在成交率那一欄(E欄)裡,找出有大於0.8的有幾個?

© 由 經理人月刊 提供 圖說明

算式輸入=COUNTIF(D3:D10, “>=0.8”)

同理,你也可以輸入不同條件,找出「女性有幾位?」「會員有幾位?」

函數寫法=COUNTIF(條件範圍, 條件)

5. 這個商品的最高實銷率?MAX

同一個商品,在不同分店銷售的情況不同時,想找出究竟哪一家店賣得最好時,可以用MAX來找,他的功用是找出範圍內的最大數字。

報表中顯示了《經理人月刊》在全台便利商店的銷售率,假如你想找出《經理人月刊》在全台北便利商店中的最高實銷率為多少,就要用Max加上計算範圍,輸入=MAX(B4:B21)。

© 由 經理人月刊 提供 圖說明

同理,若要找出最小值(最低實銷率),只要使用MIN最小值函數加上計算範圍就可以囉!

MAX的好用之處其實要和其他函數如IF做搭配,假設你拿到每天的營業額,你可以用MAX加IF,找出某位員工(或某月份內)的最高營業額數值。

函數寫法=MAX(計算範圍)

圖表製作 / 實習編輯吳瑄庭

首圖來源 / OTA photos via Flickr, CC liscensed

原文出自 / 《日經Associé》

原文網址: http://www.managertoday.com.tw/articles/view/51429

【延伸閱讀】

沒用過Excel?專為零基礎準備的簡單教學!

學會這些Excel快速鍵,工作效率保證翻倍!

一度慘賠400萬!40歲上班族靠「Excel表」,10年存出4千萬身價

Excel進階版:加總不只有SUM,還有不可錯過的3大公式!

工作、學業必備 10 大 office 絕技

 
 
 

Excel進階版:加總不只有SUM,還有不可錯過的3大公式!

由於上次大家對於《學會這5個Excel快速鍵,工作效率保證翻倍!》一文反應熱烈!我們決定繼續為各位整理超好用的操作技巧,幫助大家持續精進、以提升工作效率。這次要分享的是:4個加總函數

別以為總計只有SUM可以用,從今天起,你可以按照資料處理的需要,更聰明地指揮你的Excel啦。

以下範例是一個虛擬的零售店,只賣水、無酒精飲料和零食。店員每天會將每一筆交易的訊息記錄在「總訂單」,那店長(管理人員)該如何整理這份資料,看出銷售狀況呢?

一、隨時掌握「各產品類別」的銷售狀況:SUMIF

想知道「水」這個產品類別的銷售狀況,建議你在總訂單裡使用SUMIF函數。SUMIF是告訴Excel,只統計你指定條件的項目即可。公式寫為=SUMIF(資料範圍,你的條件,要加總的欄位)。以水為例,公式寫為=SUMIF(B:B,E2,C:C)。意思是請Excel只計算「產品」(B欄)裡「水」(E2)的銷售量總數(C欄)。

二、還是要教一下的基本總計:SUM

SUM是所有加總函數的根源。當你要計算每周各品類的總銷量,可以建立「周報表」,並運用SUM函數。公式寫為=SUM(要加總的資料範圍)。以水為例,公式寫為=SUM (B2:H2)。意思是請Excel把星期一(B2)到星期日(H2)的資料加總起來。

三、按不同售價計算總收入:SUMPRODUCT

假設這家店會在人潮比較多的周三、周五和周日,訂比較高的售價。因此,你無法用「售價×總銷量」來算出總收入。

這時候可以用SUMPRODUCT函數。公式寫為=SUMPRODUCT (要相乘的資料, 要相乘的資料…)。以水為例,輸入= SUMPRODUCT (B2:H2,B7:H7)。意思是把「星期一水的銷量×星期一水的售價」,加「星期二水的銷量×星期二水的售價」...一路加到「星期日水的銷量×星期日水的售價」。

四、跨分頁加總,計算整個月銷售狀況:3D SUM

你在每一週都根據上面三個算式當周的銷售資料,那到了月底,你應該會有四個分頁的報表,分別為Week1、Week2、Week3、Week4。如果要看整個月的銷量,該如何利用不同分頁數據,計算出銷售總和呢?

這個功能被稱為3D SUM,但其實函數一樣是SUM喔。公式寫為=SUM (分頁:分頁!各分頁要加總的儲存格)。請先新增分頁,我們把他命名為Total,專門用來統計當月的銷售狀況。

當你要算這個月份、每一週的星期一總共賣出了多少水,也就是要把第一週到第四週的星期一、水的銷售額加總。可以輸入以下公式=SUM (Week1:Week4!B2),意思是把「Week1分頁」到「Week4分頁」的星期一、水的銷售數量(B2)加總起來。

將上述公式複製到其他欄位,就可以輕鬆完成整張月報表了。

延伸閱讀>> 學會這5個Excel快速鍵,工作效率保證翻倍!

首圖來源 / ShutterStock

圖檔製作 / 吳瑄庭 (實習編輯)

原文網址: http://www.managertoday.com.tw/articles/view/51089

 
 
 


6大Gmail隱藏功能←上一篇 │首頁│ 下一篇→還放任回信主旨出現「RE:RE」?6個範例句,教你Email主旨這樣下