[Excel] 每個開發人員都需要知道的Excel公式

在這邊介紹幾個小弟經常用到的Excel公式，希望對大家使用Excel上有幫助。 1.字串處理 字串處理是用 Excel 給資訊人員最大的福利，尤其是需要批次處理時，不論是執行SQL、執行powershell 或整理報表，用Excel產生多筆的指令再去執行，真是省下很多作業的時間! (1)合併Excel的欄位值：Excel的指令是=Concatenate(字串1, 字串2, 字串3...)，譬如 =CONCATENATE("insert into tb  ([MyID],[MyDate1],[MyDate2],[MyCompany],[MyPosition],[Description])   values (",A2,",'",TEXT(C2,"yyyy/mm/dd"),"','",TEXT(D2,"yyyy/mm/dd"),"','",E2,"','",J2,"','",H2,"')") 的意思就是從A2~H2取欄位值出來，組合成 insert 指令。寫好第一行後，在Excel依次往下拉，就可以產生很多 insert 指令再貼到下Query的工具上，速度!

(2)取出固定文字的左邊或右邊：這種也常遇到，對於辦公室人員，Excel有些是要看的，但是不一定是結構性的資料，所以會有 "員工編號-XXX" 在同一個欄位出現，該如何處理呢 ? 譬如 1000-甲 要找出"-"左邊的ID，可以用 Find 這個公式找到"-"的位置，搭配Left，公式是 =LEFT(A1, FIND("-",A1)-1) ，長度要 "-1"是因為只要"100"。

如果要找右邊的，公式就寫成 =RIGHT(A1,LEN(A1)-FIND("-",A1)) 公式的內容應該是不難懂，主要是組合多個公式起來，如果一開始不確定，也可以先個別計算，這樣就可以一步一步看結果。

(3)補0(或其他需要重複的字)：主要是用REPT(重複字) 跟 LEN(值的文字長度)組合使用，譬如100,2000,30000都要補成 5碼的長度，公式是=CONCATENATE(REPT("0",5-LEN(B1)),B1)， 分解步驟的話，公式的目的是  - 重複0多次，要重複的次數：固定長度5 減掉100的長度(=3)，所以是重複0共2次  - 字串合併 00 與 100

2.資料取得對應值:在Excel 處理資料時，會遇到需要取得另一個表的值，也就是資料庫的join，可以使用: vlookup 這個公式，公式的格式是=Vlookup(來源的值,要查的範圍,要回傳的欄位位置,是否要完整比對)。 以下圖為例，公式是=VLOOKUP(A1,D:E,2,FALSE)  "我要找 A1 對應的值[第1個參數]，去D與E構成的對應表去找[第2個參數]，當A1對應到Excel Column D的值時，回傳第2行[第3個參數]，比對時要完全正確才回傳[第4個參數]。 如果查不到，就會出現#N/A的訊息。

而#N/A是不是很刺眼呢，如果又要拿來串SQL指令時，#N/A會造成CONCATENATE失敗。 該如何避免呢?Excel有另一個公式叫做 ISError ，類似寫程式的try catch例外判斷，搭配IF，如： =IF(ISERROR(VLOOKUP(A1,D:E,2,FALSE)),"--",VLOOKUP(A1,D:E,2,FALSE)) 意思是 "如果查到的結果是錯誤，就出現 --，如果不是錯誤，就出現查詢的結果"， 看不到 #N/A，爽度也提高了

要注意的是 Vlookup第2個參數的對應表必須要排序過，要不然是查不到的。 要拿來查詢的值與與對應表的值型態一樣才查的到，1(數字) 與 "1"(文字)是無法比對的上，用改格式的方式去處理會沒用，要用Text這個函數把數字改成文字，1才會變成"1" 如果資料量很大，vlookup 搭配 iserror可能會花時間，不如copy結果出來成另一個Excel在交出去。 另外， 如果你的Excel很複雜，需要查多值，請google "excel array formula vlookup" 希望可以找到你要的答案。 Vlookup也可以自己查自己，因此可以做到類似 recursive 的效果，最適合在產生樹狀結構，如組織的結構，只要把key設為上層，就可以產生出 //公司//董事會//董事長//總經理//台灣分公司//資訊部 這樣的多層結構! 3.多IF的判斷：Excel 最單純的 IF是 =IF(條件,條件成立的值，條件不成立的值)。如果要做類似多層的IF判斷時，也就是要 Switch 時或 nested if ，Excel 也是用IF來達成，公式的範例 =IF(A1="A","1",   IF(A1="B","2",   IF(A1="C","3","N")  )) 這樣Excel就會判斷 Switch(A1) {  case "A":"1"  case "B":"2"  case "C":"3"  default: "N" } 可以看圖也許比較清楚，公式內也可以按 ALT+Enter斷行作些簡單的程式排版，這樣就比較容易知道自己在寫些甚麼了!

Excel還有一些功能，像是清單整理出唯一值(如 SQL Distinct)、Column 轉 Row，都還滿常用的! 歡迎留言交流!