Excel 實用技巧大公開(上)

第16屆微軟生涯體驗計劃已經如火如荼展開第二個月,經過一個月培訓課洗禮下的小編我,決定把身為行政助理OAI常使用到的Excel技能不藏私的分享給各位,未來想加入行政助理行列的同學們可千萬別錯過這篇文章囉!

技巧一、格式化條件

行政助理的第一個技巧是「格式化條件 (Conditional Formatting)」,在資料龐大雜亂的Excel檔案中,畫面上充斥大量我們不一定需要的資訊,這時候透過「格式化條件」,即可依照你制定的規則,清晰的標示出符合設置條件的資料。

EX. 若主管想知道薪資異動後個部門薪資最高者是誰時,透過「格式化條件」就可以立刻知道答案了。

       Step1. 選取想要知道最高薪水的部門範圍,點入選「設定格式化條件」中的 「開新增規則」

     Step2. 選取類型:使用公式來決定要格式化哪些儲存格

     Step3. 撰寫公式:=H10=MAX($H$10:$H$17)

                           =一開始的選取範圍=MAX_代表尋找最大值的公式簡寫=我們想要搜尋的範圍($_代表鎖定這個範圍的欄列)

                ( P.S若想深入練習此功能,可以點擊下方excel函數連結做應用唷~)

     Step4. 未來的應用可能有好多層規則在一組資料中,所以這時候我們需點入設定格式化條件中的「管理規則」,即可新增規則、跟修改過去你所寫的規則條件(由上往下依序去你自訂的執行規則)。

技巧二、基礎函數_條件使用

 

行政助理的第二個技巧是「基礎函數 (If, SumIf)」,在所有的報表分析當中,最基本的技能就是比較不同筆資料的差距。

  公式一、= If(資料=”條件”, 正確的結果, 不正確的結果)
  = IF(C2=”YES”, 1, 2 )代表 If(C2=是, 則回傳1 , 相反則回傳2)

If函數可以讓你在值和預期結果間進行邏輯比較,因此IF 陳述式會產生有兩種結果:符合條件 True (代表1);不符合條件False (代表0)。

EX. 以上面的公式來解決以下問題,若主管想知道公司底下十間分店的上半年營業額報告,該如何比較這些分店的表現是否達標呢(如附圖)?

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

      Step2. 假如接下來主管想讓你依照業績把店面分三個等級 : 超達標20%以上的”A”、剛好達標”B”、和有待改善的”C”,條件式中你可以輸入超過兩個以上的條件

  IF(B3>=C3, IF(B3/C3>=1.2, “”,””),“”)

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

  公式二、= SumIf(條件範圍, 條件,合計範圍)

EX. 當主管了解所有分店的每一筆商品售出紀錄後想知道各類別商品在所有分店賣出的總營業額時,你該怎麼做?

       Step3. 你可以指定在商品品項(B欄)中,找出符合指定的類別(G4),並加總他們營業額(E欄)。

技巧三、資料整理_資料剖析

行政助理的第三個技巧是「資料剖析(Data Analysis)」,它可以將「黏」在一起的資料拆分開,把拆分後的資料變成新的一欄。例如,工作表中包含一列的「全名」,您可將該列拆分為兩個列,分別為「名字」和「姓氏」的列。

身為行政助理,幫忙主管整理活動參加表單、收集參訪回饋單是日常工作的一環。然而通常拿到的第一手資料都是系統自動產出,尚未分類別的資料,整理起來可說是相當耗時、耗力。匯入資料後,藉由「資料剖析(Data Analysis)」,選擇想要的型態(如下圖,是以固定距離作為拆分依據)就可以幫助我們節省更多時間

 

以上是這週的Excel進階篇的小技巧,希望能夠幫助正在實習.上班或是需要使用Excel的大家更有效率地處理資料!歡迎大家持續追蹤我們的部落格,獲得更多實用的Office技巧喔!


撰稿/Jenny Huang
製圖/Jenny Huang
審稿/Angela Lee