2008/2/29

Excel 計算集差 (VLookup)

你是否有曾經遇到過,當你有2份檔案(或是2份不同的資料),你想要在Excell中找出其中不重複的資料,但卻找不到可用的公式可以協助你處決這個問題.
這樣子的運算過程,在數學中叫"集差",看起來不難的公式,但對不起,Excell是沒有的

我們在找問題結決方法時,常常會犯了一個嚴重的錯誤,也就是想一步登天,事實上利用Excell內現有的公式,反覆計算加減後也是很容易可以得到我們要的答案



以下利用Excell Vlookup 公式來找出我們要的集差

範例: 有 A, B兩個資料,其中A包含B資料,我們要找出 A 資料中不包括 B的資料,簡單來說就是 A-B
(左邊為A資料, 右邊為B資料)



比對條件: 我們要比對的欄位包括 Name, Item, Unit, 及 Cost 要一模一樣
Step1. 建立一個新欄位(A及B都要), 把上述4個欄位加起來 (指令 = name& item&unit&cost), 變成一個獨一無二的數值


Step2. 再比對這2個獨一無二的欄位, 來找出相同的值, 使用Vlookup函數
此例公式為 =VLOOKUP(H2,$Q$2:$Q$5,1,FALSE) 共有4個參數
第一個參數: H2為A欄位中我們要比對的欄位值
第二個參數: $Q$2:$Q$5 指的是B資料的範圍
第三個參數: 如果上述資料比對成功則要顯示的東西 (1表示顯示本身第一個欄位, 0則會顯示#value!)。比對不到時一律顯示#N/A
第四個參數: false 表示資料要一模一樣, 預設為true, true時系統則會找會相似的就會顯示

『第一個參數資料會去比對第二個參數樣本,只要有符合第二個參數樣本,則系統會顯示第三個參數的值,如果都不符合,則會顯示#N/A』


Step3. 把上一步驟的公式向下拉, 找出A資料中是否有跟B資料相同,如果沒有則會顯示#N/A數值,而這個#N/A數值就是我們要的東西


Step4. 最後用排序方式把 Vlookup 數值找出 #N/A,這些資料就是A扣減B資料後的資料 :)


A-B 資料完成 :) 簡單吧

沒有留言: