Index函數(shù)在Excel中的妙用

劉 彬

攀枝花學(xué)院，四川攀枝花 617000

Excel中有效的使用函數(shù)，將會(huì)很大的提高工作效率，將復(fù)雜的問(wèn)題簡(jiǎn)單化。尤其是各種函數(shù)的搭配使用，有時(shí)會(huì)得到意想不到的結(jié)果，將一些看起來(lái)復(fù)雜沒(méi)有辦法解決的問(wèn)題輕松化解。我們?cè)趯W(xué)習(xí)Excel的過(guò)程中，應(yīng)多思考和探索。盡可能多的挖掘Excel的潛能。利用它提高我們的工作效率。下面我們以INDEX函數(shù)為例，通過(guò)與其他函數(shù)組合使用，探索Index函數(shù)一些我們不常見(jiàn)但卻非常實(shí)用的技巧。

1 Index+If+Small

工作中，我們會(huì)經(jīng)常遇到一些根據(jù)指定條件查找某些符合該條件值的多條記錄的情況。例如查找成績(jī)小于60的學(xué)生情況、查找某公司旗下某分公司的銷售情況等等。在Excel中我們常常采用篩選功能來(lái)完成，這種方式的優(yōu)點(diǎn)是操作方便，但是對(duì)于復(fù)雜的篩選條件，特別是同時(shí)顯示多條篩選結(jié)果以及動(dòng)態(tài)更新結(jié)果方面就顯得無(wú)能為力。而這個(gè)缺陷恰恰是我們不能容忍的。Excel中有很多查找函數(shù)，例如vlookup( )，find( )等等，vlookup函數(shù)使用的最為廣泛，但是該函數(shù)對(duì)于模糊數(shù)據(jù)查找方面辦法不多且非常的繁瑣。Index函數(shù)可以返回一個(gè)區(qū)域中指定行和列的值，如果我們通過(guò)它和條件函數(shù)的結(jié)合就會(huì)得到意外的收獲。這里我們采用Index+If+Small的方式，其一般形式為:{=INDEX(被查找工作表中某一列,SMALL(IF(條件,ROW(列值),65536),ROW(1:1)))&""}。該公式支持模糊查找，相比Vlookup函數(shù)就要應(yīng)用得更廣泛。如果這個(gè)思想能廣泛用在查找實(shí)例中，將大大的開(kāi)闊數(shù)據(jù)查詢的思路，為EXCEL中數(shù)據(jù)的查詢手段開(kāi)辟一條新的渠道。例如，某企業(yè)統(tǒng)計(jì)職工生產(chǎn)不良產(chǎn)品的情況。如下圖所示：A:C列存放原始記錄。現(xiàn)在要在右側(cè)按條件分別顯示對(duì)應(yīng)記錄。

也就是說(shuō)要做一個(gè)動(dòng)態(tài)的篩選表，根據(jù)A列的記錄情況自動(dòng)按條件統(tǒng)計(jì)。顯然傳統(tǒng)的方式無(wú)法處理，我們可在E4單元格中輸入如下數(shù)組公式：{=INDEX(A:A,SMALL(IF($C$3:$C$200<50,ROW($C$3:$C$200),4^8),ROW(1:1)))}

該公式通過(guò)判斷C列中值是否小于50來(lái)返回對(duì)應(yīng)值所在行的行號(hào)，再通過(guò)small函數(shù)從小到大依次取出行號(hào)，最后由Index函數(shù)返回對(duì)應(yīng)的單元格值。

這種方法就比我們一次次使用篩選功能要方便得多，而且結(jié)果更直觀。當(dāng)原始數(shù)據(jù)更改時(shí)，右側(cè)結(jié)果顯示區(qū)的數(shù)據(jù)也自動(dòng)調(diào)整。就更符合我們的要求。

2 Index+Match函數(shù)

Match函數(shù)在Excel中表示返回一個(gè)查找值在某行（列）中與之匹配值的序列號(hào)。在INDEX函數(shù)中，可以根據(jù)MATCH指定的匹配值返回某列中的值。這種方式對(duì)于在某個(gè)區(qū)域中根據(jù)指定條件查詢某一條記錄及與該記錄有關(guān)的數(shù)據(jù)項(xiàng)值特別實(shí)用。我們?cè)诠ぷ髦薪?jīng)常會(huì)遇到對(duì)某些值按從大到小或從低到高的實(shí)例，例如學(xué)生成績(jī)、工資收入等等。這里我們以學(xué)生成績(jī)?yōu)槔?下圖為某班學(xué)生的期末成績(jī)表（A1:E50）：

現(xiàn)在要達(dá)到如下效果，當(dāng)輸入完學(xué)生成績(jī)時(shí)，在工作表另外的某區(qū)域按平均分由高到低的順序自動(dòng)顯示排名及學(xué)生信息。顯然按照傳統(tǒng)菜單命令方式是無(wú)法實(shí)現(xiàn)的。這里我們可以在 F1：J1中按學(xué)生信息表的列標(biāo)題做好目標(biāo)區(qū)域格式?？稍贔2中輸入如下數(shù)組公式：{=INDEX(A$2:A$50,MATCH(LARGE($E$2:$E$50-ROW(1:5)%%,ROW(A1)),$E$2:$E$50-ROW(1:5)%%,))} 。公式分析：我們可先按平均分排序，再由平均分查找與該平均分值相等的其他列所在行的數(shù)據(jù)。由于兩個(gè)同學(xué)的平均分有可能相同，因此利用ROW(1:5)%%產(chǎn)生一個(gè)非常小的值，再用平均分減去該值即可把相同的值變成不同的值，再利用match函數(shù)匹配平均分的值產(chǎn)生index函數(shù)相應(yīng)列中對(duì)應(yīng)的行。這樣當(dāng)我們學(xué)生成績(jī)數(shù)據(jù)產(chǎn)生更改時(shí)，目標(biāo)區(qū)域就自動(dòng)按平均分高低排序。

3 Index+Small+Match

該公式組合可以用在很多場(chǎng)合中，例如根據(jù)指定信息從某工作表中讀取與該信息匹配的值及對(duì)應(yīng)的記錄、讀取一個(gè)區(qū)域的非重復(fù)值以及處理賬單明細(xì)等等。如果我們能深入了解這類使用方式，對(duì)于我們對(duì)Excel認(rèn)識(shí)的提高及工作效率的提高將會(huì)起到非常重要的作用。這里我們僅以簡(jiǎn)單的實(shí)例進(jìn)行分析。例如某列中有1000個(gè)數(shù)據(jù)，其中某些數(shù)據(jù)有重復(fù)記錄?，F(xiàn)在要實(shí)現(xiàn)不論A列中的值如何改變，在B列某區(qū)域自動(dòng)顯示A列中重復(fù)5次的值。傳統(tǒng)的方法顯然是無(wú)法實(shí)現(xiàn)的，這里我們可以使用如下數(shù)組公式：

=INDEX(A:A,SMALL(IF((COUNTIF($A$2:$A$1000,$A$2:$A$100 0)=5)*(MATCH($A$2:$A$1000,$A$2:$A$1000,)=ROW($A$1:$A$9999)),ROW($A$2:$A$1000),4^8),ROW(1:1))) 函數(shù)分析：利用countif函數(shù)統(tǒng)計(jì)A列中重復(fù)5次的值，再利用match函數(shù)找到重復(fù)5次的值中最小的行號(hào)。最后通過(guò)small函數(shù)把這些符合條件的值的行號(hào)取出。

當(dāng)然，關(guān)于Index函數(shù)妙用的實(shí)例還很多，例如和OFFSET函數(shù)的配合使用等等，雖然這些用法不常見(jiàn)，很多參考資料也不涉及，但是用好了對(duì)我們的工作將會(huì)提供很大的方便。這需要我們不斷探索，不僅僅是Index函數(shù)，EXCEL中其他函數(shù)也是如此，希望我們?cè)诠ぷ髦卸嗫偨Y(jié)，找到更多的捷徑，這樣才能更快的提高我們的EXCEL應(yīng)用能力，提高我們處理數(shù)據(jù)的技能和方法，從而提高我們的工作效率。

[1]邱燕明EXCEL2003公式與函數(shù)應(yīng)用寶典[M].電子工業(yè)出版社，2004，8.

[2]李繼兵EXCEL數(shù)據(jù)處理與分析[M].中國(guó)青年出版社，2006，1.

[3]EXCEL公式與函數(shù)實(shí)例講解陳威[M].人民郵電出版社，2009，3.