如何使用VLOOKUPN函式實現不同EXCEL表格間的資料匹配

excel表格怎麼選擇男女

使用vlookupn函式實現不同excel表格之間的資料關聯

如果有兩個以上的表格，或者一個表格內兩個以上的sheet頁面，擁有共同的資料——我們稱它為基礎資料表，其他的幾個表格或者頁面需要共享這個基礎資料表內的部分資料，或者我們想實現當修改一個表格其他表格內共有的資料可以跟隨更新的功能，均可以透過vlookup實現。

例如，基礎資料表為“姓名，性別，年齡，籍貫”，而新表為“姓名，班級，成績”，這兩個表格的姓名順序是不同的，我們想要講兩個表格匹配到一個表格內，或者我們想將基礎資料表內的資訊新增到新表格中，而當我們修改基礎資料的同時，新表格資料也隨之更新。

這樣我們免去了一個一個查詢，複製，貼上的麻煩，也同時免去了修改多個表格的麻煩。

簡單介紹下vlookup函式的使用。以同一表格中不同sheet頁面為例：

兩個sheet頁面，第一個命名為“基礎資料”第二個命名為“新表”。如圖1：

圖1

選擇“新表”中的B2單元格，如圖2所示。單擊［fx］按鈕，出現“插入函式”對話方塊。在類別中選擇“全部”，然後找到VLOOKUP函式，單擊［確定］按鈕，出現“函式引數”對話方塊，如圖3所示。

圖2

圖3

第一個引數“lookup_value”為兩個表格共有的資訊，也就是供excel查詢匹配的依據，也就是“新表”中的A2單元格。注意一定要選擇新表內的資訊，因為要獲得的是按照新表的排列順序排序。

第二個引數“table_array”為需要搜尋和提取資料的資料區域，這裡也就是整個“基礎資料”的資料，即“基礎資料！A2：D5”。為了防止出現問題，這裡，我們加上“$”，即“基礎資料！$A$2：$D$5”，這樣就變成絕對引用了。

第三個引數為滿足條件的資料在陣列區域內中的列序號，在本例中，我們新表B2要提取的是“基礎資料！$A$2：$D$5”這個區域中B2資料，根據第一個引數返回第幾列的值，這裡我們填入“2”，也就是返回性別的值（當然如果性別放置在G列，我們就輸入7）。

第四個引數為指定在查詢時是要求精確匹配還是大致匹配，如果填入“0”，則為精確匹配。這可含糊不得的，我們需要的是精確匹配，所以填入“0”（請注意：Excel幫助裡說“為0時是大致匹配”，但很多人使用後都認為，微軟在這裡可能弄錯了，為0時應為精確匹配），此時的情形如圖4所示。

按［確定］按鈕退出，即可看到C2單元格已經出現了正確的結果。如圖5：

把B2單元格向右拖動複製到D2單元格，如果出現錯誤，請檢視公式，可能會出現，D2的公式自動變成了“=VLOOKUP（B2，基礎資料！$A$2：$D$5，2，0）”，我們需要手工改一下，把它改成“=VLOOKUP（A2，原表！基礎資料！$A$2：$D$5，4，0）”，即可顯示正確資料。繼續向右複製，同理，把後面的E2、F2等中的公式適當修改即可。一行資料出來了，對照了一下，資料正確無誤，再對整個工作表進行拖動填充，整個資訊表就出來了。向下拉什複製不存在錯誤問題。

這樣，我們就可以節省很多時間了。

兩個EXCEL裡資料的匹配

工作上遇到了想在兩個不同的EXCEL表裡面進行資料的匹配，如果有相同的資料項，則輸出一個“YES”，如果發現有不同的資料項則輸出“NO”，這裡用到三個EXCEL的函式，覺得非常的好用，特貼出來，也是小研究一下，發現EXCEL的功能的確是挺強大的。這裡用到了三個函式：VLOOKUP、ISERROR和IF，首先對這三個函式做個介紹。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

VLOOKUP：功能是在表格的首列查詢指定的資料，並返回指定的資料所在行中的指定列處的資料。函式表示式是：

VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

1。 Lookup_value為“需在資料表第一列中查詢的資料”，可以是數值、文字字串或引用。

2。Table_array 為“需要在其中查詢資料的資料表”，可以使用單元格區域或區域名稱等。

⑴如果range_lookup 為TRUE或省略，則table_array 的第一列中的數值必須按升序排列，否則，函式VLOOKUP 不能返回正確的數值。如果range_lookup 為FALSE，table_array 不必進行排序。

⑵Table_array 的第一列中的數值可以為文字、數字或邏輯值。若為文字時，不區分文字的大小寫。

3。 Col_index_num 為table_array 中待返回的匹配值的列序號。

Col_index_num 為1 時，返回table_array 第一列中的數值；Col_index_num 為2 時，返回table_array 第二列中的數值，以此類推；如果Col_index_num 小於1，函式VLOOKUP 返回錯誤值#VALUE！；如果Col_index_num 大於table_array 的列數，函式VLOOKUP 返回錯誤值#REF！。

4。Range_lookup 為一邏輯值，指明函式VLOOKUP 返回時是精確匹配還是近似匹配。如果為TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小

於lookup_value 的最大數值；如果range_value 為FALSE，函式VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值#N/A。

ISERROR：它屬於IS系列，IS系列用來檢驗數值或引用型別，有九個相關的函式：

ISBLANK（value） ：判斷值是否為空白單元格。

ISERR（value） ：判斷值是否為任意錯誤值（除去#N/A）。

ISERROR（value） ：判斷值是否為任意錯誤值（#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？ 或#NULL！）。

ISLOGICAL（value） ：判斷值是否為邏輯值。

ISNA（value） ：判斷值是否為錯誤值#N/A（值不存在）。

ISNONTEXT（value） ：判斷值是否為不是文字的任意項（注意此函式在值為空白單元格時返回TRUE）。

ISNUMBER（value） ：判斷值是否為數字。

ISREF（value）：判斷值是否為引用。

ISTEXT（value） ：判斷值是否為文字。

IF：執行邏輯判斷，它可以根據邏輯表示式的真假，返回不同的結果，從而執行數值或公式的條件檢測任務。函式表示式為：IF（logical_test，value_if_true，value_if_false），其中含義如下所示：

logical_test：要檢查的條件。

value_if_true：條件為真時返回的值。

value_if_false：條件為假時返回的值。

下面介紹下透過上述的三個函式如何達到我想要的要求的，下圖是工作中的兩個EXCEL 表，sheet1和sheet2，現在要將sheet2的每一行資料在sheet1中查詢匹配，如有sheet1中存在，則在sheet2中的E列顯示“存在”，否則顯示“不存在”。

sheet2

sheet1

首先使用了VLOOKUP函式將sheet1中的資料在sheet2中進行查詢，

=VLOOKUP（A2，sheet1！$A$2：$C$952，1，FALSE），其中A2表示用來匹配項的資料，將A2在sheet1的所有列中查詢就是使用第二個條件：sheet1！$A$2：$C$952，“$”表示絕對引用，複製的時候不會隨著單元格位置變化而變化，1表示匹配成功後返回第一列的資料，否則返回#N/A，FALSE表示返回精確匹配值。

注：絕對引用和相對引用只要在公式欄裡面對應的資料下按F4功能鍵即可切換。

當有返回結果後剛開始直接使用IF去判斷了，公式是：

=IF（VLOOKUP（A2，sheet1！$A$2：$C$952，1，FALSE）=A2，“存在”，“不存在”），這個時候發現當匹配成功的時候輸出了“存在”，當匹配不成功是卻輸出了“#N/A”，一直沒法實現想要的結果，後來發現VLOOKUP只能輸出指定的值或者“#N/A”，而與A2判斷的結果也為“#N/A”，作為IF函式是無法識別“#N/A”，這樣導致不會輸出“不存在”，所以要想辦法將IF的第一個條件的結果是“Ture”or “False”，於是就找到了函式ISERROR（Value），這個輸出的結果是“Ture”or “False”，於是公式就變成了

=IF（ISERROR（VLOOKUP（A2，sheet1！$A$2：$C$952，1，FALSE）），“不存在”，“存在”），大功告成，輸出自己想要的結果，當在shhet2中的專案能在sheet1中找到時輸出“存在”，找不到時輸出“不存在”。

總結：VLOOKUP的函式比較好用，可以尋找並且匹配，但是要注意只能是匹配項在首列，如果不是則要用HLOOKUP函式。EXCEL的函式功能還是挺強大的，好好研究對於我們資料統計和處理是非常有幫助的，目前對於VLOOKUP、ISERROR和IF三個函式有一定的認識，以後還得繼續研究學習。