周 威

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

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

1 問題的提出

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

圖1 工作量統(tǒng)計表1

圖2 工作量統(tǒng)計表2

2 函數(shù)介紹

2.1 VLOOKUP 函數(shù)

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

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

其中：

Lookup_value：需要在其中查找匹配數(shù)據(jù)的開始單元格

Table_array：兩列或多列數(shù)據(jù)（用絕對地址）

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

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

2.2 IF 函數(shù)

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

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

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

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

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

3 解決方法

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

圖3 引用統(tǒng)計表2 數(shù)據(jù)

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

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

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

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

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

圖4 選擇性粘貼

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

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

圖5 標記重復(fù)記錄