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.