[Excel] Vlookup應用 – 快速大量比對填入資料

現在多數資料都已經數位化,因此平常處理文件的時候經常會用到Excel來處理大量的資料(例如帳目、庫存、進出貨紀錄、銷售、人事管理、季報表之類的),但遇到兩張表或是兩個Excel檔案之間要比對同樣資料、要把A表資料填到B表上但B表又不是規則排列、且數量又很大,之類的情況經常發生,今天就簡單來教各位使用vlookup解決這類的問題,讓你用超高效率做完一堆報表,而且正確率百分百!

前情提要

本篇會使用一個我設定的案例來示範如何運用Excel函數Vlookup來進行跨檔案比對、兩份試算表或不同工作表的比對、自動帶入資料、錯誤代碼隱藏並顯示為自己要的字樣。

此案例是用微軟Office 2016 Excel示範,同時也適用其他版本的Excel(包含線上365版本)、Google Docs試算表、Openoffice、Liberoffice試算表軟體。

範例問題描述

問題:業務部門發來一個試算表「BalanceCheck」,要CSR單位把目前庫存資料數值帶入這張表,且把無庫存(無資料)的產品也標示出來,避免接單之後無法出貨產生客訴。

  • 資料表1:Stock 公司庫存清單
  • 資料表2:BalanceCheck 業務所需之庫存查詢表
資料表1 庫存工作表 Stock
資料表2 業務用 庫存檢查表 BalanceCheck 分頁

步驟1 插入 VLOOKUP 函數

先選擇資料表2的B2欄位(倉庫A/鉛筆這欄),然後按下上方公式列左邊的fx。

跳出插入函數視窗後,選擇類別「檢視與參照」,選擇最底下的「VLOOKUP」,按下「確定」按鈕。

步驟2 輸入 Lookup_value 欲搜尋的值

可以手動輸入或是直接先按Lookup_value右邊的白色框框,再按你要選擇的欄位。這個欄位要填入的是你要搜尋/比對的值,舉例來說,我在BalanceCheck這張表要檢查鉛筆這個項目在Stock表中的鉛筆項目有多少數量,那我就要選「鉛筆」(A2)欄位。同理,如果要看資料夾這項,就選資料夾。

這邊我們直接選第一列也就是鉛筆A2就好,因為等下用 + 號往下拉的時候,就會自動往下延伸到其他行的數值。

步驟3 選擇Table_array

這個欄位是要去你預計要比對的資料表中,選擇你要比對資料的範圍,舉這邊的例子,我要去比對Stock表中的各個項目倉庫A的數量,因此我就選擇A行 ~ B行即可 (技巧:滑鼠移到灰色A的上放會出下向下的箭頭,按著往右拉到B就可以選擇這兩行全部欄位了)。

詳細步驟:點擊Table_array右邊白色框框 > 選擇Stock工作表 > 選擇A:B所有欄位 > 前往下一個步驟。

步驟4 輸入Col_index_num & Range_lookup

Col_index_num 是你要比對第幾行的數值,這邊就是第2行,因為第1行是項目名稱(A4紙、大紙箱、信封…),第2行才是我們要的倉庫A庫存數量。因此這邊我們輸入阿拉伯數字 2 即可。

Range_lookup 是你要近似比對(TRUE)或完全比對(FALSE),我們要的是比對完全相同的值,因此要輸入 0 (FALSE) 才正確。

輸入完畢後,按下確定按鈕。

步驟5 複製公式至所有欄位

按下確定後會自動回到BalanceCheck頁面,你會看到剛剛設定的B2欄位已經自動去抓到Stock工作表中的數字「5」了。接著我們要把公式複製到所有倉庫A的欄位。

詳細步驟:按下B2欄位 > 滑鼠移到B2欄右下角會出現一個白色加號 > 按住加號往下拉到表格底部 (此範例就是B9欄);或是如果欄位太多不好拉,直接在白色加號快速按兩下也會只接套用公式到底部。

步驟6 完成倉庫A公式設定

到這邊已經完成倉庫A的欄位設定了,你已經可以看到抓過來的數值(庫存數量)了! 標記為#N/A的部份就是未在Stock表找到的項目所出現的錯誤訊息(意思就是沒庫存或無此品項),若想要消除此錯誤訊息改為顯示其他文字,下面會簡單講,或是直接前往參考這一篇文章

步驟7 設定倉庫B公式

一樣照剛剛的步驟,先選擇倉庫B第一格C2 > 開啟 VLOOKUP 函數引述數視窗 > 設定好數值 > 確定 > 把C2公式用白色加號往下拉到底複製公式 > 完成。

欄位說明

  • Lookup_value:一樣是要查看A2欄位的項目
  • Table_array:這邊要看到倉庫B,因此要選A ~ C
  • Col_index_num:這邊要看C (倉庫B)資料,因此要填 3
  • Range_lookup:需要完全比對,輸入 0

步驟8 設定倉庫C的公式

如同設定倉庫A & B一樣。

欄位說明

  • Lookup_value:一樣是要查看A2欄位的項目
  • Table_array:這邊要看到倉庫C,因此要選A ~ D
  • Col_index_num:這邊要看D (倉庫C)資料,因此要填 4
  • Range_lookup:需要完全比對,輸入 0

步驟9 設定錯誤訊息顯示文字提示

為了方便閱讀,可以直接不用解釋丟給(寄給)業務或客戶,這邊需要把#NA這樣的錯誤訊息改成人類看得懂的文字,除了好閱讀以外,也更加專業。

這邊需要微調所有公式,加入=if(iserror 公式,這個公式的功能是如果它看到錯誤代碼,它會自動轉換成你要顯示的文字;反之,如果它看到運算出正常的數值,它會直接顯示出來。

公式:
=IF(ISERROR(VLOOKUP公式), "你要顯示的訊息", VLOOKUP公式)

範例:
= IF(ISERROR(VLOOKUP(A2,Stock!A:B,2,0)), "你要顯示的訊息",  VLOOKUP(A2,Stock!A:B,2,0))

範例公式:

可以運用剛剛介紹的白色 + 號直接套用到其他格子。

補充

Table_array的部份我上面是要用到哪裡才選哪裡(例如我只要看AB我就只選AB、要看AC我就只選ABC…以此類推),但你也可以一次把所有需要用的選起來,舉例來說,這邊不管是倉庫A、倉庫B、倉庫C,我要看的東西都在A ~ D這四行資料之間,因此我可以所有欄位都輸入Stock!A:D,然後去變更後面index_num的數字就好,這樣就可以用+好去拉公式會更快速,但本篇鎖定的是比較不熟悉的初學者,所以才使用這種一次一次設定的教學方式。

祝各位順利,加快報表處理效率,早點下班呀!