VLOOKUP 函數與『資料處理過濾增益集』中『工作表資料擷取』功能的比較

2014-10-30

VLOOKUP 函數是常用的查詢參照的函數，這個函數主要的功能就是比對一個區塊資料陣列的每一列資料的第1個資料，以取得該資料列成員資料，此函數的功能與『資料處理過濾增益集』中『工作表資料擷取』的功能相近，經常有網友問到為什麼還要設計一個Excel已有的增益集功能？我想針對這兩者做一個比較應該可以解釋網友的問題。

首先要比較之前，我先就兩者的使用方法舉個例子來分別說明；

假設下表為某班學生的成績(電腦產生假的資料，工作表1)

假設我們現在有下列這些學生的成績需要挑出，倒到別的工作表(假設為工作表3如下表所示)，這種工作在學校經常進行，如果筆數很多不適合肉眼搜尋，我們就必須透過軟體幫忙。

我們分別利用VLOOKUP 函數與『資料處理過濾增益集』的『工作表資料擷取』來完成這個工作。

首先說明利用VLOOKUP 函數來完成，步驟如下：

步驟1.選取工作表3中C2資料格，然後點選功能區中的[公式]\[插入函數]，叫出[插入函數]表單

步驟2.在表單中[搜尋函數]中輸入VLOOKUP或選取類別下拉選單選擇[檢視與參照]，然後在[選取函數]中選取VLOOKUP 函數，然後按下[確定]，叫出[函數引數]表單。

步驟3.在[函數引數]表單設定引數，以下針對引數說明

Lookup_value 是指要作為尋找資料列比對的值，這個值相當於列的索引的概念，也就是尋找的資料範圍中每一列的第1個值，可以輸入值或資料格參照，這個值原則是不能重複，如果重複VLOOKUP 函數只會比對出第1個找到的那一列，以本例來說Lookup_value 這個引數，我們必須選擇座號這欄的資料格為引數，如果有學生座號重複則只會呈現第1個出現的座號查尋的結果。

Table_array 是指要尋找資料列範圍陣列，以本例為學生成績的範圍參照(工作表1!A2:D38)，特別需要注意的是；範圍的第1欄為Lookup_value 引數比對的目標，所以搜尋資料範圍第1個欄位一定必須是含括Lookup_value引數中的值，如果沒有就會找不到，舉例如果我們把要挑選學生的姓名當成Lookup_value，則Table_array的範圍必須改成(工作表1!B2:D38)，如果還是原來(工作表1!A2:D38)，則函數會以座號和姓名比對，這樣會搜尋不到或錯誤的資料。

Col_index_num 是比對到所要資料列位時要擷取的欄位，以本例我們要取得的是Table_array 中第3欄的資料(標題為『選擇』這一欄的資料)則Col_index_num的引數我們要輸入3。

Range_lookup 這個引數是一個邏輯值，如果省略或輸入True則VLOOKUP函數比對Lookup_value時，會找尋Table_array第1欄中最接近Lookup_value的值，如輸入False則會找尋完全相等的值。

整個引數設定後如下表

按下[確定]鈕即可得到計算結果。

步驟4.設定完C2的公式=VLOOKUP(A2,工作表1!A2:D38,3,FALSE)，我們可以利用Excel自動填滿的功能來快速完成，但是我們要搜尋的範圍是固定的，所以必須把Table_array引數(工作表1!A2:D38)改為(工作表1!$A$2:$D$38)這樣自動填滿時Table_array的範圍才不會變動，將引數改完之後將滑鼠游標移至工作表3的C2資料格右下角，等游標變成黑色十字，按住滑鼠左鍵不放向下拖拉執行自動填滿即可完成C欄的資料。

步驟5.以本範例我們尚須取得『總分』此欄位資料，所以我們必須在D2資料格中重複C2的步驟，然後Col_index_num的引數我們要輸入4，因為『總分』欄位在Table_array中是第四欄位。D2輸入的公式為=VLOOKUP(A2,工作表1!$A$2:$D$38,4,FALSE)，再執行自動填滿的步驟即可完成。

接下來我們示範用『資料處理過濾增益集』的『工作表資料擷取』來完成這個工作，為了區別Vlookup範例，新增『工作表4』將要擷取的座號姓名放到『工作表4』。

步驟1.點選Excel功能區[增益集]\[正規比對資料過濾]\[工作表資料擷取]，叫出[工作表資料擷取]表單。

步驟2.設定[工作表資料擷取]表單的引數，分別就各引數進行說明
參數1.[進行比對的條件範圍]:以本範例為例，請用滑鼠選取『工作表4』中『座號』與『姓名』兩欄資料所在之範圍，再按下[進行比對的條件範圍]對應的[選取]鈕，此引數是要撈取資料之條件，如同VLOOKUP函數中Lookup_value引數，不過因為VLOOKUP函數一次只能尋找一個值，所以Lookup_value引數只能設定1個值，而[工作表資料擷取]是進行批次比對，所以直接將要比對的範圍選取(工作表4!$A$2:$B$5)。

參數2. [進行資料寫入欄位]:以本範例此引數為設定要將篩選出來的資料，寫入『工作表4』的欄位，因為要篩選的是『選擇』與『總分』兩欄位的資料，所以選擇C、D兩欄的資料，點選[進行資料寫入欄位]對應的[選取]鈕，將引數帶入。

參數3.[資料比對範圍]：以本範例此引數如同VLOOKUP函數中的引數Table_array的第1欄，但不同的是[資料比對範圍]是可以多欄比對，不限定比對1個欄位，原則是欄位數要與[進行比對的條件範圍]選取的欄位數相同，所以我們選擇『工作表1』中『座號』與『姓名』兩欄資料所在之範圍。

參數4.[擷取資料的欄位或寫入之資料(以#為開頭)]：以本範例此引數如同VLOOKUP函數中的引數Col_index_num，也就是比對後要擷取之欄位，不同的是Col_index_num只能指定1欄，而[擷取資料的欄位或寫入之資料(以#為開頭)]可以選擇連續的多欄資料，原則是欄位數與[進行資料寫入欄位]所選的欄位數相同即可，所以選擇『工作表1』中『選擇』與『總分』兩欄資料作為擷取資料，使用者亦可以寫入固定的內容資料，即在[擷取資料的欄位或寫入之資料(以#為開頭)]引數輸入處輸入要寫入#號與內容，例如我要將篩選的資料寫入”得獎者”，則[擷取資料的欄位或寫入之資料(以#為開頭)]引數輸入處輸入”#得獎者”。

其他參數設定：[工作表資料擷取]表單的引數除了上述四個引數，另外在表單中央有六個比對方式的引數選擇，使用者可以透過以下幾個引數設定進行不同規則的擷取選擇。

以表單預設值為例，是設定在，與。
此設定代表；是以設定的[進行比對的條件範圍]作為比對的範圍，每一列為一筆比對條件，而勾選[每個欄位必須符合]則代表比對時每一列中每一格資料都要符合才算符合。然後符合比對的資料對應的列資料會寫至對應的寫入欄位，如果取消勾選[每個欄位必須符合]，則比對範圍中只要有欄位相符就會被篩選出來。如果勾選[採正規表示法比對]則比對時是採正規表示法進行表對，也就是說會將[進行比對的條件範圍]資料格中的資料當做正規比對的樣版進行比對。如果勾選[篩選不符合條件]則會將不符合的資料篩選出來，此只作用於與這兩種篩選方式。