宋永成

在Excel中，有時(shí)我們會(huì)根據(jù)關(guān)鍵字從其它工作表中查找與之相對(duì)應(yīng)的數(shù)據(jù)，如根據(jù)姓名從圖1所示的全年級(jí)的學(xué)生成績表中查找某一學(xué)生的總分成績，通常我們會(huì)使用VLOOKUP函數(shù)完成任務(wù)。但是VLOOUP只能查找到符合指定條件的第一個(gè)數(shù)據(jù)，對(duì)于其它符合條件的數(shù)據(jù)就無能為力了。所以上表中如果有學(xué)生重名，那么根據(jù)姓名查找就總是返回第一個(gè)同學(xué)的成績。那么像這種有重復(fù)數(shù)據(jù)的情況，又如何來解決呢？在Excel中，雖然VLOOKUP函數(shù)本身有它的局限性，但是轉(zhuǎn)換—下思路，要解決這個(gè)問題其實(shí)還是有辦法的。

一、改換關(guān)鍵字，仍用VLOOKUP函數(shù)

VLOOKUP函數(shù)之所以有問題，關(guān)鍵原因是有重復(fù)數(shù)據(jù)（姓名有重復(fù)）的存在。如果換個(gè)沒有重復(fù)的關(guān)鍵字，那這個(gè)問題也就不是問題了。觀察本例發(fā)現(xiàn)，所有重名的學(xué)生并不在同一個(gè)班級(jí)中，也就是說“班級(jí)+姓名”是不可能重復(fù)的。那么我們就以“班級(jí)+姓名”為關(guān)鍵字。在A列之前添加一個(gè)空列，這樣“班級(jí)”和“姓名”列就延至B列和c列了。將鼠標(biāo)定位于A1單元格，輸入公式“=B1&C1”，拖動(dòng)其填充句柄至最后一行?，F(xiàn)在A列的中數(shù)據(jù)就變成無重復(fù)的數(shù)據(jù)了。

假定要查詢成績的學(xué)生姓名位于B16：B19單元格區(qū)域，A16：A19單元格為學(xué)生們的相應(yīng)班級(jí)，如圖2所示。在C16單元格輸入公式“=VLOOKUP（$A16&$B16，$A$1：$I$13，9，F(xiàn)ALSE）”，然后拖動(dòng)其填充句柄至c19單元格就可以得到結(jié)果了，那些重名的同學(xué)成績也可以輕松分辨出來。

二、另辟蹊徑，改用其它函數(shù)

上面的方法固然簡單，卻也有局限性，你必須先知道要查詢的學(xué)生是哪個(gè)班級(jí)的。假定只有姓名，如何才能查詢正確的結(jié)果呢？或者能不能把這個(gè)姓名的所有同學(xué)成績都列出來呢？這個(gè)要求，恐怕就不是某單一函數(shù)所能做的了，得多個(gè)函數(shù)才行。

在C16單元格輸入公式“=INDEX（$I$1：$I$13，SMALL（IF（$C$2：$C$13=B16，ROW（$C$2：8C$13），65536），COUNTIF（$B$16：$B16，B16）））”，將鼠標(biāo)定位于編輯欄，然后按下“Ctrl+Shlft+Enter”組合鍵產(chǎn)生數(shù)組公式（特征即是公式外層的一對(duì)花括號(hào)）。拖動(dòng)c16單元格的填充句柄向下填充公式至c19單元格，可以看到各個(gè)名為“張三”的學(xué)生總分成績了。

對(duì)于公式，我們不妨這樣理解：內(nèi)層的IF結(jié)構(gòu)的計(jì)算結(jié)果，也就是判斷C2：C13區(qū)域，如果等于B16，就返回對(duì)應(yīng)的所在行號(hào)，不相等的話，就返回65536（以此數(shù)代表一個(gè)極大值）。單純計(jì)算“IFf8c$2：$c$13=B16，ROW（$C$2：$C$13），65536）”部分，會(huì)得到結(jié)果“f2；65536；4；65536；65536；65536；8；65536；65536；65536；65536；65536l”。公式中的COUNTIF（$B$16：$B16，B16）用于計(jì)算這是第幾個(gè)重復(fù)數(shù)據(jù)，向下拉動(dòng)公式后會(huì)根據(jù)B16及以下的數(shù)據(jù)返回結(jié)果1，2，3等。于是利用SMALL函數(shù)就可以提取到前面數(shù)據(jù)中最小的三個(gè)數(shù)“2，4，8”。這三個(gè)數(shù)正是“張三”同學(xué)所在的行數(shù)。然后我們就可以利用INEDX函數(shù)在I列中提取相應(yīng)行數(shù)的數(shù)據(jù)了。

應(yīng)用此公式，對(duì)其稍加改造，我們可以實(shí)現(xiàn)兩個(gè)有意思的功能：

1.提取所有重名學(xué)生的總分

我們只在B16單元格提供查詢學(xué)生的姓名，然后用公式提取出所有該姓名的學(xué)生總分成績。這樣我們的公式就應(yīng)該寫做“=INDEX（$I$1：$I$13，SMALL（IF（$C$2：$C$13=$B$16，ROW（$C$2：$C$13），65536），ROW（A1）））”，仍然按“Ctrl+Shift+Enter”組合鍵結(jié)束。拖動(dòng)該單元格填充句柄向下至出現(xiàn)錯(cuò)誤提示為止。這樣就能提取所有重復(fù)數(shù)據(jù)了。

2.自動(dòng)按名次排列成績表

成績表可以用RANK函數(shù)排出名次，之后我們通常要進(jìn)行排序才能按名次進(jìn)行升序顯示。利用上面的公式可以自動(dòng)完成這個(gè)任務(wù)，保持了成績表的原貌。

成績表稍加改造，增加了幾個(gè)平行的名次，用RANK函數(shù)排出名次。

在第15行復(fù)制表頭。在116單元格寫下公式“=SMALL（$152：$I$13，ROW（A1））”，按回車鍵結(jié)束。拖動(dòng)其填充句柄向下至出現(xiàn)錯(cuò)誤提示為止。此公式可以得到成績表中名次的升序排列。相同名次的也可以全部列出。

在A16單元格輸入公式“=INDEX（A$1：ASK3，SMALL（IF（$I$2：$I$13=$116，ROW（$I$2：$I$13），65536），COUNTIF（$I$16：$I16，$116））），按“Ctrl+Shift+Enter”組合鍵結(jié)束，產(chǎn)生數(shù)組公式。拖動(dòng)A16單元格的填充句柄向右復(fù)制公式至H16單元格。再選中A16：H16單元格區(qū)域，向下拖動(dòng)其填充句柄至出現(xiàn)錯(cuò)誤提示為止。

刪除所有的錯(cuò)誤提示，就會(huì)出現(xiàn)按名次排列的成績表了，如圖3所示。

成績表中的重復(fù)數(shù)據(jù)是不可避免的，比如重名、名次相同、分?jǐn)?shù)相同等。只要我們掌握并靈活運(yùn)用函數(shù)，那么重復(fù)數(shù)據(jù)也可以輕松分辨。