Excel“万金油公式”思路解析

Excel“万⾦油公式”思路解析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才能算是⼊门了吧！