Index函數在Excel中的妙用

劉 彬

攀枝花學院，四川攀枝花 617000

Excel中有效的使用函數，將會很大的提高工作效率，將復雜的問題簡單化。尤其是各種函數的搭配使用，有時會得到意想不到的結果，將一些看起來復雜沒有辦法解決的問題輕松化解。我們在學習Excel的過程中，應多思考和探索。盡可能多的挖掘Excel的潛能。利用它提高我們的工作效率。下面我們以INDEX函數為例，通過與其他函數組合使用，探索Index函數一些我們不常見但卻非常實用的技巧。

1 Index+If+Small

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

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

該公式通過判斷C列中值是否小于50來返回對應值所在行的行號，再通過small函數從小到大依次取出行號，最后由Index函數返回對應的單元格值。

這種方法就比我們一次次使用篩選功能要方便得多，而且結果更直觀。當原始數據更改時，右側結果顯示區的數據也自動調整。就更符合我們的要求。

2 Index+Match函數

Match函數在Excel中表示返回一個查找值在某行（列）中與之匹配值的序列號。在INDEX函數中，可以根據MATCH指定的匹配值返回某列中的值。這種方式對于在某個區域中根據指定條件查詢某一條記錄及與該記錄有關的數據項值特別實用。我們在工作中經常會遇到對某些值按從大到小或從低到高的實例，例如學生成績、工資收入等等。這里我們以學生成績為例,下圖為某班學生的期末成績表（A1:E50）：

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

3 Index+Small+Match

該公式組合可以用在很多場合中，例如根據指定信息從某工作表中讀取與該信息匹配的值及對應的記錄、讀取一個區域的非重復值以及處理賬單明細等等。如果我們能深入了解這類使用方式，對于我們對Excel認識的提高及工作效率的提高將會起到非常重要的作用。這里我們僅以簡單的實例進行分析。例如某列中有1000個數據，其中某些數據有重復記錄。現在要實現不論A列中的值如何改變，在B列某區域自動顯示A列中重復5次的值。傳統的方法顯然是無法實現的，這里我們可以使用如下數組公式：

=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))) 函數分析：利用countif函數統計A列中重復5次的值，再利用match函數找到重復5次的值中最小的行號。最后通過small函數把這些符合條件的值的行號取出。

當然，關于Index函數妙用的實例還很多，例如和OFFSET函數的配合使用等等，雖然這些用法不常見，很多參考資料也不涉及，但是用好了對我們的工作將會提供很大的方便。這需要我們不斷探索，不僅僅是Index函數，EXCEL中其他函數也是如此，希望我們在工作中多總結，找到更多的捷徑，這樣才能更快的提高我們的EXCEL應用能力，提高我們處理數據的技能和方法，從而提高我們的工作效率。

[1]邱燕明EXCEL2003公式與函數應用寶典[M].電子工業出版社，2004，8.

[2]李繼兵EXCEL數據處理與分析[M].中國青年出版社，2006，1.

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