Excel 常用函數介紹

        身為專案管理人員, 在上班時常會遇下列數種狀況，需要進行大量的資料比對分析, 如果沒有善用工具將會花去你不少時間， 令人感到挫折：

  1. 老闆要知道產品成本變化，你(妳)需要快速比對這一版和上一版的材料清單(BoM)差異，你手上的最新的BOM並沒有明確標示有那些變更，但你的BoM有數百個料件須要比對。
  2.  老闆想要知道目前產品成本的組成分佈，想要知道電子料、塑膠材料、金屬材料以及包裝材料等所占的成本比重。
  3.  開發產品問題的設計分類，在產品(軟體)開發過程中會有上百個設計問題點，如何將專案遇到的問題快速分類(EE, ME, SW, FW等)並再細分出目前的問題解決狀況(Open, Verify, Close.....等)。

底下就介紹幾個好用的excle函數來幫我們聰明的快速完成資料比對吧!

VLOOKUP

     Vlookup顧名思義就是在垂直(V)的每一列中，找出(lookup)特定值的資料後回傳該列的指定欄位值，它的使用方式如下圖，在函數的括弧中需依序填入：找尋目標、找尋範圍、回傳欄位編號、是否需完全相同等資訊。

         如果以第1個狀況為例，我們需要比對兩個不同的材料清單(版本1&2)，此時可以使用vlookup來幫忙我們快速得到結果。 假設我們要比對的清單如下圖，如果我們想要知道版本1及版本2是否有不一樣料號的零件，我們可以在版本1的H2欄位輸入"=VLOOKUP(B2,版本2!$B$2:$G$11,1,FALSE)"，上述公式表示我們想要找的是在版本2的B2~G11的範圍中，是否有等同B2欄位值的列，如果有請回傳範圍中該列的第1個欄位值。重複此步驟於H3~H11欄位(可以將公式複製貼上)，比對結果可以參考下圖，此例可以發現兩個版本的材料清單中沒有不同的料號(如果有在H欄將會看到N/A)。

        我們還可以進一步比對相同料號在不同版本間的用量是否有出入，此時我們可以繼續在版本1的I2欄位輸入"=VLOOKUP(B2,版本2!$B$2:$G$11,4,FALSE)"，上述公式表示我們想要找的是在版本2的B2~G11的範圍中，是否有等同B2欄位值的列，如果有請回傳範圍中該列的第4個欄位值(用量)。重複此步驟於I3~I11欄位後，比對結果可以參考下圖，黃色螢光處就是兩個版本不同處。

       如果我們還想要比較相同料號在不同版本間的價格是否有出入，我們可以將在版本1的I2欄位公式改為"=VLOOKUP(B2,版本2!$B$2:$G$11,5,FALSE)"，上述公式表示我們想要找的是在版本2的B2~G11的範圍中，是否有等同B2欄位值的列，如果有請回傳範圍中該列的第5個欄位值(價格)。

版本 1

版本 2

料號比對結果

用量比對結果

Sumif

      Sumif顧名思義就是特定範圍中，找出特定情況(if)下的資料加總(sum)。它的使用方式如下圖，在函數的括弧中需依序填入：判斷範圍、比對條件、加總範圍等資訊。

        如果以第2個狀況為例，我們想知道目前產品成本的組成分佈，此時可以使用sumif來幫忙我們快速得到結果。如我們要分別計算版本1的EE、Metal、Plastic、五金等類別的材料總成本，我們可以在I2欄位輸入"=SUMIF(D2:D11,"EE",G2:G11)"，上述公式表示在D2~D11範圍中找出等於EE的列，然後加總同為該列的G欄位值，即表示加總所有分類為EE的材成本；重複此步驟於I3~I5欄位後，分別可以得到EE、Metal、Plastic、五金等類別材料成本計算結果，詳情參考下圖。

Countif

       countif顧名思義就是特定範圍中，找出特定情況(if)下, 符合該狀況的個數加總(count)。它的使用方式如下圖，在函數的括弧中需依序填入：判斷範圍、比對條件等資訊。

        如果以第3個狀況為例，我們需要將開發過程中所遇到的問快速分類(EE, ME, SW, FW等)並再細分出該些問題的解決狀況(Open, Verify, Close.....等)。此時我們就可以利用countif來幫助我們快訴獲得分類結果。



(未完待續.....)