【Excel函數(shù)實例】INDEX、MATCH實現(xiàn)數(shù)據(jù)自動查找與呈現(xiàn)

1、【excel函數(shù)實例】index、match實現(xiàn)數(shù)據(jù)自動查找與呈現(xiàn)這兩個函數(shù)都屬于查找與引用函數(shù)，index函數(shù)的作用主要是取指定單元格的值；match函數(shù)的作用用于查找；這兩個函數(shù)經(jīng)常成對使用，配合起來就能實現(xiàn)數(shù)據(jù)的自動查找與呈現(xiàn)。我們經(jīng)常看到這樣的情況，考試完成后，我們?nèi)ゲ樵兂煽?，工作人員詢問你的考號是多少，然后工作人員在計算機里輸入考號，你的成績就自動呈現(xiàn)出來了。今天，我們就是要用excel的index和match函數(shù)來實現(xiàn)類似的功能，同時也讓大家能理解index與match函數(shù)的用法。一、建立如下表格。要完成今天我的實例，首先要準(zhǔn)備好一個成績表，然后設(shè)計成下圖的表樣。二、創(chuàng)建取姓名的公

2、式。根據(jù)上面的表格，我們第一步不用考慮太多，只考慮當(dāng)輸入考號時，能在右邊的成績數(shù)據(jù)中找到此考號，并取出此考號相對應(yīng)的姓名即可。1、查找考號。由上表可知，我們輸入的考號在b8單元格，要查找的考號范圍在c2到c563之間。（注：我的表格中最大考號為22013562，即有562位學(xué)生）而match函數(shù)正好是查找函數(shù)，它能查找指定區(qū)域里的值，并得到該值在此區(qū)域的相對位置。其格式為：match(要查找的數(shù)據(jù)，查找的區(qū)域，參數(shù))match有三個參數(shù)：-1、0、1。其意思不一樣：-1是查找小于或等于指定數(shù)據(jù)的單元格；0是查找等于指定數(shù)據(jù)值的單元格；1是查找大于或等于指定數(shù)據(jù)的單元格。因此，本例中，要查找的數(shù)

3、據(jù)就是考號（即b8），查找的范圍是c2到c563（即c2:c563），必須查找學(xué)號完全一樣的，參數(shù)應(yīng)該是0。產(chǎn)生的查找公式為：match（b8，c2:c563，0）。這個公式應(yīng)該能理解吧！由于學(xué)生成績的區(qū)域，與考號輸入的單元格是固定不可變的，因此將數(shù)據(jù)區(qū)域改為靜態(tài)引用。完善后的公式：match（$b$8，$c$2:$c$563，0）特別說明：在本例中，也可以不靜態(tài)引用。如果b8輸入考號22013003，公式match（b8，$c$2:$c$563，0）的執(zhí)行結(jié)果是多少呢？答案是3。為什么呢？因為它返回22013003在c2到c563中的相對位置，對照上表，從c2單元格的22013001算起，它

4、剛好處在第3的位置上，所以返回值為3。2、取出該考號的姓名。還是以考號22013003為例，用match函數(shù)只能查到了他的相對位置，卻不能取出姓名來，怎么辦呢？取值就要用index函數(shù)來實現(xiàn)了。index的作用取指定區(qū)域里行列交叉處單元格的值。index的格式：index（數(shù)據(jù)區(qū)域，行，列）特別提醒：這個行與列，不是excel整個表格的行與列，而是指定區(qū)域內(nèi)的行與列。數(shù)據(jù)區(qū)域：可能有人會說，當(dāng)然也應(yīng)該是c2到c563；不對了，因為我們要返回姓名，c2到c563只有考號，沒有姓名，因此要擴(kuò)大區(qū)域。用c2到d563就行了。但為了幾個公式統(tǒng)一，我用c2到i563區(qū)域。這樣沒有問題，因為姓名包含在其中

5、。行：22013003在第幾行呢，答案c2到c563區(qū)域里的第3行。列：姓名在區(qū)域里的第幾列呢？一看就是第2列，對吧。得到公式：index（c2:i563，3，2），這個公式就可以取22013003的姓名。但考號是不停變化的，但不管考號如何變，姓名在第2列不會變，只會變的是在第幾行。第幾行怎么確定呢？大家沒有忘記怎么查找考號的吧？就是match函數(shù)來確定是第幾行。是不是就搞定了呢？最終取姓名的公式就得出來了，將第3行代換為公式：index（c2:i563，match（$b$8，$c$2:$c$563，0），2）也變成靜態(tài)引用吧，完善后的取姓名的公式如下：index（$c$2: $i$563，m

6、atch（$b$8，$c$2:$c$563，0），2）不知大家是否明白了呢？下面我們在b9單元格輸入上面這個公式，然后任意輸入一個考號，驗證一下是否會取出正確的姓名呢？效果如下圖所示：由上圖可以看出，我們已經(jīng)能根據(jù)輸入的考號取出該考號的學(xué)生姓名了。第二步完成了。三、完成其它公式。其它公式在取姓名的公式稍加變化即可。由于考號所在的行不變，只是列變化。因此取第3列就是班級，取第4列就語文成績，取第5列就是數(shù)學(xué)成績，以此類推。取班級的公式：index（$c$2: $i$563，match（$b$8，$c$2:$c$563，0），3）取語文成績的公式：index（$c$2: $i$563，match（$b$8，$c$2:$c$563，0），4）取數(shù)學(xué)成績的公式：index（$c$2: $i$563，match（$b$8，$c$2:$c$563，0），5）取英語成績的公式：index（$c$2: $i$563，match（$b$8，$c$2:$c$563，0），6）取總分的公式：index（$c$2: $i$563，match（$b$8，$c$2:$c$563，0），7）下圖是應(yīng)用上全部公式的效果：是不是大功告成了呢？不是的。四、完善公式?，F(xiàn)在看起來，我們需要的結(jié)果都出現(xiàn)了，的確是完成了。只要輸入正確的考號都會出現(xiàn)要查找