index+match函数组合在excel中的应用

菜鸟学excel系列之经典函数组合index+match函数组合在excel中的应用我们结合如图的表格来学习index+match函数组合在excel中的应用：（下面的数据可以复制到excel里进行同步练习）金牌银牌铜牌名次国1716813国21310106国396189国46141415国550616国681211国712627国8214118国9717912国10012220国11177101国1212627国13311617国141510144国15151753菜鸟学excel系列之经典函数组合国16141045国1720619国1817742国19781414国208111610在学习之前我们首先需要了解index和match的作用，对于初次接触这两个函数的朋友来说，通过生活中的例子去理解会更容易。回想在上学的时候，我们都有过站队的经历，将一个班的同学分四列，每列20人，也就是4列20行的一个方队，那么我们只要知道了某一个同学在第几行第几列，就可以唯一确定这个同学，对于上面这个表也是类似，例如我们需要知道国10的金牌数，那么只要知道国10在第几行，金牌在第几列，那么很容易确定国10的金牌数是多少。就上图而言，红色范围的区域为B2:E21，国10处于这个范围的第10行，金牌处于这个范围的第一列，那么用index来确定国10的金牌数就可以写成=INDEX(B2:E21,10,1)。可以看到，国10的金牌数通过公式得到的结果为0，这和我们观察的结果是一致的。那么我们把公式改成=INDEX(B2:E21,5,3)，再看看又得到什么结果呢？结果是6，这个6代表什么意思呢？通过刚才的解释，应该可以理解是B2:E21这个范围内第5行第3列的数据，具体含义就是国5的铜牌数。通过以上两个例子不难看出index这个函数的用法，=INDEX(范围,行,列)就是这个函数的基本结构。但是很快发现一个问题，如果行和列都要我们自己去数的话，那么不是太麻烦了吗？如果可以根据需要自动确定行和列，该有多好。这时候就需要match上场了。下面我们根据这个表格的例子来看看match的作用。简单来说，match就是确定某个数据在一行或者一列的位置，举个例子来看：国17在国这一列的第几行，用match来写就是=MATCH(I1,A2:A21)，或者=MATCH(I1,A2:A21,0)，从下面两个图看出来结果是一样的。那么两种写法有什么区别呢？这里涉及到一个比较重要的知识点，就是模糊匹配和精确匹配，其实=MATCH(I1,A2:A21)的完整写法是=MATCH(I1,A2:A21,1)，对于match这个函数来说，基本结构是MATCH(查找值，范围，查找方式）这里的查找方式有三种，用0，1，-1来区别，如果我们要找的数据在范围里是存在的，那么三种方式没有区别，所以可以简写为MATCH(查找值，区域），关于模糊匹配和精确匹配不在这里解释了，刚才看的例子是针对一列里的情况，那么针对一行的情况也是类似的，看下面的例子：菜鸟学excel系列之经典函数组合金牌处于金银铜的第一个位置，说到这里，估计朋友们应该已经明白了match的用法了吧。现在我们回到最开始的问题，就是如果根据国和奖牌类型找到对应的数字，实现下图这样的效果按我们最初理解的，应该写成=INDEX(B2:E21,17,1)，这里的17和1分别用match来获得就可以写成=INDEX(B2:E21,MATCH(I1,A2:A21,0),MATCH(J1,B1:D1,0))，结果如图所示现在我们可以随意制定国家和奖牌类型，这个公式就可以根据具体数据来获得我们需要的结果了，如下图：以上所说的是index针对多行多列的区域去引用数据的情况，但是很多情况下，我们的引用往往只是针对某一行或者某一列去引用，还是这个例子，我们现在需要根据给定的国家去得到该国家的名次，我们当然可以这样处理，对刚才的公式进行修改，=INDEX(B2:E21,MATCH(I1,A2:A21,0),MATCH(H1,B1:E1,0))这个公式需要朋友们自己去解读一下，应该不是很难。但是其实可以更简单，先公布答案，公式为：=INDEX(E2:E21,MATCH(I1,A2:A21,0))，细心的朋友会发现，公式短了，index里少了一部分内容，在细心观察一下，会发现index的范围也变了，只是名次这一列，这时候公式的解释就变成了在名次这一列去找数据，而数据的位置就是由具体国家在国家这列的位置来确定。菜鸟学excel系列之经典函数组合再看一个例子，=INDEX(B4:D4,,MATCH(J1,B1:D1,0))，这个公式也请朋友们自己解读一下吧，这里需要注意的是，当按行找的时候，index的范围和match之间要有两个逗号，也就是index（一列数据，第几行）和index（一行数据，，第几列）的区别。再看最后一个例子，根据名次找国家的，公式为：=INDEX(A2:A21,MATCH(H2,E2:E21,0))有用过vlookup的朋友也许会发现，这个方法和vlookup的反向查找结果一样，其实灵活掌握了index+match的用法后，完全可以取代vlookup和hlookup，并且可以更加灵活的运用，当然，index+match并不仅仅是这样的一点作用，关于多条件合并引用的问题再单独讨论，总而言之，要想熟练运用index和match的组合，通过以上的说明了解基本原理，然后自己多动手练习才能有更好的效果。空间学习资料网盘学习资料