巧用VLOOKUP和IF函數合并工作表

周 威

（無錫商業(yè)職業(yè)技術學院，江蘇 無錫 214153）

Excel 是微軟辦公套裝軟件的一個重要的組成部分，它可以進行各種數據的處理、統計分析和輔助決策操作，廣泛地應用于管理、統計財經、金融等眾多領域。Excel 之所以功能強大、應用廣泛主要是其內置了非常豐富的函數，Excel 函數一共有11 類，主要有數學和三角函數、統計函數、文本函數日期與時間函數、查找和引用函數、財務函數、邏輯函數等。在這些函數中大多數人比較熟悉的是SUM、AVERAGE和COUNT 之類的常用函數，其他函數使用較少。其實Excel 中還有很多函數可以幫助我們高效、快速的完成工作，例如把VLOOKUP 和IF這兩個函數結合起來合并工作表，可起到事半功倍的效果。

1 問題的提出

每到學期結束學校各部門都需要統計教師的工作量，筆者所在學校教師的工作量分兩塊，一個是由教務部門統計的課堂教學工作量，另一個是由其他部門如學工處、團委統計的非課堂教學工作量。有些教師既有課堂教學工作量，又有非課堂教學工作量，而有些教師只有課堂教學工作量或者只有非課堂教學工作量（如圖1 和圖2 所示），現在需要將兩張表合并成一張表。由于兩張表中的教師相互有交叉，直接復制粘貼行不通。此時如果使用VLOOKUP 函數進行查找引用，結合IF 函數就可輕松完成任務。下面我們將介紹要用到的兩個函數。

圖1 工作量統計表1

圖2 工作量統計表2

2 函數介紹

2.1 VLOOKUP 函數

函數功能：VLOOKUP 是按列查找，最終返回該列所需查詢列序所對應的值；Vlookup 函數在Excel 中廣泛運用，特別是在做報表、登記數據和查找數據等方面。

函數格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中：

Lookup_value：需要在其中查找匹配數據的開始單元格

Table_array：兩列或多列數據（用絕對地址）

Col_index_num：為table_array 中待返回的匹配值的列序號。值為1 時，返回table_array 第一列中的數值；值為2 時，返回table_array 第二列中的數值，以此類推。

Range_lookup：為邏輯值，指定希望VLOOKUP 查找精確的匹配值還是近似匹配值（如果為TRUE 或省略，則返回精確匹配值或近似匹配值。此時第一列中的值必須以升序排序；否則VLOOKUP 可能無法返回正確的值（可以事先對工作表按升序進行排序）；如果為FALSE，VLOOKUP 將只尋找精確匹配值。在此情況下，第一列的值不需要排序。）

2.2 IF 函數

函數功能：IF 函數用于執(zhí)行真假值判斷后，根據邏輯測試的真假值返回不同的結果，因此If 函數也稱之為條件函數。它的應用很廣泛，可以使用函數IF 對數值和公式進行條件檢測。

函數格式：IF(logical_test,value_if_true,value_if_false)。

其中：Logical_test 是一個計算結果為TRUE 或FALSE 的任意值或表達式。本參數可使用任何比較運算符。

Value_if_true 是在logical_test 為TRUE 時返回的值，Value_if_true也可以是其他公式。Value_if_false logical_test 為FALSE 時返回的值。Value_if_false 也可以是其他公式。

總之，IF 函數的第一個參數的結果為真的話，則將第二個參數Value_if_true 的值作為函數的返回值，如果為假則將第三個參數Value_if_false 的值作為函數的返回值。IF 函數可以嵌套七層，用value_if_false 及value_if_true 參數可以構造復雜的檢測條件。

3 解決方法

第一步：引用工作量統計表2 中的數據填充工作量統計表1 中相應單元格。為此，在工作量統計表1 的C2 單元格輸入以下內容：“=VLOOKUP(A2，工作量統計表2!$A$2：$C$21，3，FALSE)”。含義是在工作量統計表2 的A2：C21 單元格區(qū)域中查找與A2 值相同的那行所對應的第3 列的值填充到工作量統計表1 的C2 單元格。然后按住填充柄向下拖拉到C16 單元格釋放鼠標。此時可以看到由于兩個工作表中的人員并不全部相同，工作量統計表1 中部分單元格的值在工作量統計表2 中找不到，顯示的內容為“#N/A”，如圖3 所示。

圖3 引用統計表2 數據

第二步：引用工作量統計表1 中的數據填充工作量統計表2的相應單元格。在工作量統計表2 的D2 單元格輸入以下內容：“=VLOOKUP(A2，工作量統計表1!$A$2：$D$16，4，FALSE)”。意思是在工作量統計表1 的A2：D16 單元格區(qū)域中查找與A2 值相同的那行所對應的第4 列的值填充到工作量統計表2 的D2 單元格。按住填充柄向下拖拉到D16 單元格，同樣也會有部分單元格顯示為“#N/A”。

第三步：將工作量統計表1 的A1：D16 單元格區(qū)域復制并選擇性粘貼到一個新工作表中（在選擇性粘貼時選擇“數值”），再將工作量統計表2 的A2：D21 單元格區(qū)域也選擇性粘貼到該工作表，使兩張工作表的數據合并在一起。

第四步：在新工作表中選擇C2：D36 單元格區(qū)域，使用查找替換功能將“#N/A”刪除。

第五步：在新工作表中按工號作升序排序，可以看到由于兩張表中的人員有部分重復，所以此時新工作表中有部分人員出現了兩次，因此要想辦法刪除重復記錄。

第六步：在新工作表的E1 和F1 單元格分別輸入“重復否1”和“重復否2”，然后在E2 單元格輸入以下內容：“=IF(A3=A2，“是”，“否”)”，拖拉填充柄到E36。選擇E2：E36 單元格區(qū)域，選擇性粘貼到F 列（選擇性粘貼時選擇“數值”），如圖4 所示。

圖4 選擇性粘貼

第七步：在新工作表中刪除“重復否1”列，然后按“重復否2”字段作升序排序，可以看到從第29 行開始到36 行都是重復的記錄（如圖5 所示），刪除這些記錄，再刪除“重復否2”列，并將工作重命名為“合并”，至此問題就解決了。

如果要查找替換的數據是按行排列的，那么與VLOOKUP 相類似可以用HLOOKUP 函數來完成。通過以上例子我們看到利用Excel 提供的函數可以幫助我們解決很多實際問題，把幾個函數結合起來更是可以成倍地提高工作效率。

圖5 標記重復記錄