|
Excel中有一个几乎“万能”的函数组合，在N多种问题的解决过程中都少不了她的身影，她就是Index+small+if+row组合！这个万金油的组合公式在日常工作中的应用非常广泛，今天通过一个例子的剖析，让大家了解公式的原理，遇到此类公式都可以自己进行套用。 很多朋友在Excel中用公式做查询的时候，都必然会遇到的一个麻烦问题，那就是一对多的查找问题，大多数朋友做查询都是从vlookup、index-match组合入门的，然而遇到一对多查询的时候，如果不加辅助列，往往会束手无策，今天我们要讨论的Index+small+if+row组合，就是专门解决一对多查询的一个通用公式。 好了，言归正传，先看一个效果图，到底一对多查询是怎样的：
由动画演示可以看出，只要输入一个学号，就会根据学号在左边的成绩表中查询该学生的相关成绩。 得到这个查询结果，只用了一个数组公式：=IFERROR(INDEX($B$2:$D$28,SMALL(IF($A$2:$A$28=$H$2,ROW($1:$27),99),ROW(A1)),COLUMN(A1)),"") 除去IFERROR（排除错误显示）、COLUMN（多列引用）之外，就是今天要说的INDEX+SMALL+IF+ROW组合了。 公式有点长，下面咱们就一起来层层扒开，化繁为简，希望每个朋友都能够理解原理再加以应用。不过要想彻底理解这个公式，还得有一些储备知识，例如简单的数组应用，index、small、if和row这几个函数的基本用法也是要了解的。本文末尾会有相关链接，可以选择去复习。 先从index说起，这个函数基本功能是给出一个区域，然后根据对应的行列位置返回查找结果，本例中index查找的数据区域如下图所示。 index一共需要三个参数，第一个参数是数据范围，刚才说了，第三个参数是要查找的内容位于这个范围的第几列，本例中因为要对应查找三列数据，并且列的位置是对应的，所以用了column函数，如下图所示： 这两点都理解之后，重点就该到index的第二个参数了，这个参数的作用是要查找的内容位于数据区域的第几行，如果是一对一的查找，我们可以指定行号或者用match函数来取得行号，但这里是一对多，例如学号008，分别对应了三个行号，如下图所示： 重要提醒：对这三行数据在表格中的行号我特别标注出来了，分别是4、17和19，但请注意一点，index第一参数所给到的区域并不是从第一行开始的，而是从第二行开始，这一点很重要！ 实际上，这三个数据在index给到的范围中，分别位于第3、16和18行，分析到这一步，似乎有点眉目了，我们希望的结果或许应该是这样的：对于学号008，第一次出现时应该是index（数据范围，3，列位置）；第二次出现时应该是index（数据范围，16，列位置）；第三次出现时应该是index（数据范围，18，列位置）；如果还有第四次出现的话，也至是第二参数（行位置）在变化。 以上这段话请务必理解，因为这就是一对多的核心思路，为了将这一想法变成结果，就出来了small+if+row，而这三个函数的组合实际上才是真正的万金油！ SMALL函数：本来这个函数的作用是在给出的一组数据中挑出指定的第几个最小值，第一个参数是一组数字，第二个参数是一个数字……（感觉晕乎乎） 如上图，small 的第一个参数是一组数字，第二个参数是1个数字，这是代表a列这组数据中最小的一个，结果是1； 如果把第二参数改成2，意思就是这组中中第二小的数据，结果是2； 第二参数改成3，结果还是2（因为有好几个2）…… 注意，这里的第二参数我们是手动输入的，因为把第一个公式下拉的话，第二参数1是不会变的，如果需要第二参数会自己变，就需要借助ROW函数！（row出现） 可以看看效果： 现在回到我们的问题，还是看看图： 我们需要的是4、17、19（实际上是3、16、18，如果不明白的话从头看，前面重点说过的），要通过small得到这几个数字，有个思路就是：学号=008的，按对应的行号标注，学号≠008的，都看作比28大的数（这个数其实是行号，因为一共27个数），而要实现这个目的，只能通过if实现，IF($A$2:$A$28=$H$2,ROW($1:$27),99)，现在来看这一段是不是有点明白了，$A$2:$A$28=$H$2这句的意思就是学号是否等于我们给到的学号，如果是，得到ROW($1:$27)，否则都等于99（现在应该明白这个99的作用了吧，本例中99可以改成28的哦），当然如果数据比较多，99就不行了，所以很多时候会用65536（这个数字是旧版的Excel的最大行号），对于使用新版（2007以上）Excel来说，这个数字就变成1048576，看上去很长，也不好记，高手们就想了个办法，用2^10来代替这个数（2^10是2的10次方），更简单的还有用8^8，9^9来写的，作用都一样，就是一个够大的数，呵呵，好像有点跑题了…… 我们来看看IF($A$2:$A$28=$H$2,ROW($1:$27),99)这一段的运算结果吧： 注意到3、16、18了吗？ 这段if是否已经明白？
如果明白的话，small的第一个参数（一组数）我们就得到了，那么：SMALL(IF($A$2:$A$28=$H$2,ROW($1:$27),99),ROW(A1))这段small是否可以明白呢？ 不明白的话往上翻，看看中间部分解释small的…… 如果明白的话，3、16、18都得到了，引用前面思路分析的一段话：我们希望的结果或许应该是这样的：对于学号008，第一次出现时应该是index（数据范围，3，列位置）；第二次出现时应该是index（数据范围，16，列位置）；第三次出现时应该是index（数据范围，18，列位置）； 将这个small放入index就得到了INDEX($B$2:$D$28,SMALL(IF($A$2:$A$28=$H$2,ROW($1:$27),99),ROW(A1)),COLUMN(A1))…… 记得公式是数组的，写完或者修改后都需要按住Ctrl和shift再回车的！ 至于最外层的iferror，只是为了显示效果干净一点。 末尾要说的一点话关于：这个万金油公式，网上或许会有很多的解释，但是没有像我写的这么啰嗦的，不知道各位看的是否明白？其实这个公式真的很难，涉及到很多的知识点，最起码的数组公式的理解，逻辑值的应用，甚至很简单的row函数，column函数，if函数等等，要想用精都不是一朝一夕的事情。但是也只有真的理解了这类经典的函数组合，熟练的掌握了函数嵌套后，你的Excel才能算是入门了吧！
| 
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-2-19 22:40
