網頁

2012年12月1日 星期六

Excel 函式 VLOOKUP 的用法

對於資料庫熟識的人而言,將兩個表格 join;或者透過相同欄位將不同資料合併在一起,這些都是常見的運算。可是對於電腦程式不是很熟的人,EXCEL 中的 vlookup 與 hlookup 倒是可以助一臂之力。

如下圖: 現在我們想將 F1:G4 的資料填入A1:B12 中。也就是比較左右兩邊表格,將具有相同座號的地點填入左方的表格。


希望的結果如下圖


在上面這個例子中,找不到相同座號的地址就填入0。現實的例子中,左右兩個表格都可能非常大,若用人工方式填入,會累死人的!

現在我們就用EXCEL來完成這個例子。
第一步就是將兩個表格依相同的欄位排序,上面這個例子已經排序完成。
第二步: 在 B2的格子內填入
=vlookup(a2, f2:g4, 2)
其中第1個參數代表欲比較的鍵值,
第2個參數就是要比較的矩陣,
第3個參數代表如果比較成功,要顯示的第2個參數內的欄位位址

為了要能快速複製B2內的函數,需將第2個參數以絕對位址表示,也就是 $f$2:$g$4
結果如下


在B5中,由於右方表格中沒有座號為2013的欄位值,電腦會自動找小於該值的地區值來取代,也就是"桃園"。在B2中,右方表格也沒有2010的欄位值,且也沒有比2010小的地區值,所以呈現 #N/A。

若要兩邊的座號值完全相同才顯示地區值時,在vlookup函式中要加入第4個參數: false。電腦預設第4個參數值是TRUE,沒有加入第4個參數就代表是TRUE。

現在將B2改成 =vlookup(a2, $f$2:$g$4, 2, false)
並自動填滿至B12, 其結果如下


以上結果雖然正確,但對於沒有相同鍵值的結果顯示 #N/A,這有點惱人!為了消除這個討厭的 #N/A,我們可以利用 =ISNA() 這個函式。

當參數等於 #N/A 時,ISNA(參數) 傳回TRUE,
否則傳回FALSE。

因此 B2 修訂為  =if(isna(vlookup(a2, $f$2:$g$4, 2, false) ), "0", vlookup(a2, $f$2:$g$4, 2, false))



這就是我們要的結果。

如果表格是橫列式的,就改用HLOOKUP取代VLOOKUP,此處就不用多說了!

-- END ---










5 則留言:

  1. 謝謝,不過vslookup多一個s

    回覆刪除
  2. 第一個參數是不是應該改成 a2:a12 ?

    回覆刪除
    回覆
    1. No!
      B2內的值利用a2為索引值去f2:g4去找相對應的答案, 並非用一個陣列去找。

      刪除
  3. 謝謝您的分享,真好用!

    回覆刪除