VLOOKUP函數(shù)查詢與統(tǒng)計模型構(gòu)建研究

梁 明

(廣州航海學(xué)院 航運經(jīng)貿(mào)系，廣州 510725)

1 VLOOKUP函數(shù)概述

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

2 VLOOKUP函數(shù)查詢模型構(gòu)建及其局限性和延展性

2.1 VLOOKUP函數(shù)查詢模型構(gòu)建

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

圖1 某工資數(shù)據(jù)清單Fig.1 A salary list

2.2 VLOOKUP函數(shù)查詢模型的局限性及延展性

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

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

3 VLOOKUP函數(shù)查詢與統(tǒng)計模型的構(gòu)建

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

圖2 修改后的某工資數(shù)據(jù)清單Fig.2 A modified salary list

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

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

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

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

4 VLOOKUP函數(shù)查詢與統(tǒng)計模型的應(yīng)用及優(yōu)點

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