2012年10月19日 星期五

[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,都還滿常用的!
歡迎留言交流!

沒有留言:

張貼留言