VLOOKUP函數查詢與統計模型構建研究

梁 明

(廣州航海學院 航運經貿系，廣州 510725)

1 VLOOKUP函數概述

VLOOKUP函數是Excel中的一個縱向查找函數，其功能是搜索表區域首列滿足條件的元素，確定待檢索單元格在區域中的行序號，然后再返回選定單元格的值，其表達式為VLOOKUP(lookup_value，table_array,col_index_num,range_lookup)。式中，lookup_value為首列要搜索的值；table_array為搜索數據區域；col_index_num為滿足條件的單元格在數組區域的列序號；range_lookup為指定在查找時是要求精確匹配還是大致匹配。

2 VLOOKUP函數查詢模型構建及其局限性和延展性

2.1 VLOOKUP函數查詢模型構建

例如，某工資數據清單如圖1 B:F列所示。單元格H2列是在首列中要查找的數值。在數據清單B:F列前面留出空白的A列，用于粘貼查找值所在列的數據。在圖1的查找結果區域中，查找值“田紅”編號的數值可以根據編輯欄中的公式計算得到。公式表達的意思是在A:F列找到查找值“田紅”所在行，并列示“田紅”所在行第2列的數據。后面要查找的“月份”、“姓名”、“部門”和“基本工資”，對應公式中的查找值和查找區域是相同的，列數不同，需將編輯欄公式中的“2”分別改為“3”、“4”、“5”和“6”，這樣即可查詢得到月份、姓名、部門和基本工資的數據。

圖1 某工資數據清單Fig.1 A salary list

2.2 VLOOKUP函數查詢模型的局限性及延展性

VLOOKUP函數查詢模型局限性有二：一是公式中列數需要手動更改，無法自動修改相應列數。二是利用VLOOKUP函數對首列滿足相同條件的多個數據進行查詢時，無法將滿足條件的多個數據全部顯示出來，查詢結果只能顯示找到的第一個數據(精確匹配)或最后一個數據(大致匹配)。例如，如果以“銷售部”作為查找值，VLOOKUP函數無法將所有“銷售部”的數據顯示出來，只能顯示“銷售部”的第一條數據。

VLOOKUP函數查詢模型延展性可以考慮從兩個方面進行：一是當數據清單區域錄入新一條數據后，在查找結果區域中自動顯示所有對應的查詢數據(包括非重復單個數據查詢和可重復多個數據的查詢情況)。二是對查詢結果需要匯總的數據項進行自動匯總。

3 VLOOKUP函數查詢與統計模型的構建

對原有模型進行了修改和完善，修改后的模型如圖2所示。

圖2 修改后的某工資數據清單Fig.2 A modified salary list

修改后的模型對公式進行了重新的設置或完善：

第一，在A列設置是否滿足查找條件的判別公式。公式的作用一是用于識別哪一條記錄能夠滿足所設置的查找條件，并對滿足查找條件的記錄自動排序。二是用于控制對未錄入的記錄在A列對應單元格中顯示為空，以保持數據清單界面的干凈和美觀。如圖2所示，當數據清單B:F列中未錄入數據時，A列的單元格中顯示為空(事實上A列的單元格已設置有公式)。當數據清單B:F列中錄入數據后，A列的單元格會根據公式自動判別該記錄是否滿足查找條件，并在滿足查找條件的情況下自動排序(如圖2中的A4、A8單元格所示)。A2單元格公式設置為：=IF(E2=″″,″″,IF(E2=S|HS|2,MAX(S|AS|1:S|A1)+1,″″))，表示檢查E2是否為空，如果E2為空，則A2顯示為空。如果E2不為空，則檢查E2是否等于H2。如果E2等于H2，則顯示從A1單元格開始至當前單元格的上一個單元格為止區域中的最大值加上1的值。如果E2不等于H2，則顯示為空。A2單元格的公式設置完畢后，拖動A2單元格右下角填充手柄向下復制公式。

第二，完善K2單元格的公式。公式可修改為：=IF(ROW()-1>MAX(S|A:S|A),″″,VLOOKUP(ROW()-1,S|A:S|F,COLUMN()-9,0))，式中，ROW()表示活動單元格所在行的行號，COLUMN()表示活動單元格所在列的列號。公式意思是如果活動單元格(即K2)所在行的行號減1得到的值大于A列中所列示的最大的值，則活動單元格顯示為空(此處設置是為了控制當不滿足條件時不以錯誤值#N/A的形式出現，而是以空白單元格顯示，以保持界面的干凈與美觀)。如果不滿足此條件，則在首列中查找滿足ROW()-1的值(本例中K2對應的ROW()=2，ROW()-1=1，即查找值為1)。找到滿足查找值對應的行后，顯示該行中第COLUMN()-9列的數值(本例中，K2對應的COLUMN()=11，COLUMN()-9=2，即顯示第2列的值)。K2單元格公式設置完畢后，拖動填充手柄向右復制公式至O2單元格后，再拖動填充手柄一直復制公式。值得注意的是，由于K2公式中用到ROW()和COLUMN()兩個函數，如果查詢結果不放置在K2而是另外放置到其他的單元格，則ROW()和COLUMN()的結果會相應發生變化。此時，應調整好ROW()-x和COLUMN()-x中x的值，確保公式中ROW()-x和COLUMN()-x的計算結果分別等于1和2。

第三，添加查詢匯總計算公式。如圖2所示，添加“基本工資匯總”數據。R1單元格公式設置為：=SUM(O:O)，對O列求和，如果查詢結果中還有其他列數據需要求和，操作同理。當需要進行有條件求和時，可修改求和公式。

4 VLOOKUP函數查詢與統計模型的應用及優點

VLOOKUP函數查詢與統計模型對于非重復單個數據的查詢和可重復多個數據的查詢均適用。模型的使用者只需在數據清單區域中錄入原始數據，A列就會自動進行是否滿足條件判斷，將查詢結果自動顯示出來，并自動計算匯總數額。每條新記錄錄入后，查詢結果和匯總結果自動更新，提高了使用者的工作效率，并減少了出錯率。當查詢值更新為不同列的數據時，可直接部分修改A2單元格公式，然后再拖動填充手柄向下復制公式即可，其他單元格無需變動。圖2中，查找值由“銷售部”變更為姓名“付剛”，只需將A2單元格公式=IF(E2=″″,″″,IF(E2=S|HS|2,MAX(S|AS|1:S|A1)+1,″″))中的E2改為D2即可。此外，可將查找條件與查找結果放到另一表頁，數據清單就可無限制地向右或向下擴展。改良后的VLOOKUP函數查詢與統計模型兼具查詢和統計的功能，具有一定的實用價值。