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

by Posted on

現在多數資料都已經數位化，因此平常處理文件的時候經常會用到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的數字就好，這樣就可以用+好去拉公式會更快速，但本篇鎖定的是比較不熟悉的初學者，所以才使用這種一次一次設定的教學方式。

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

Published by impochun