与 30万 粉丝一起学ExcelVIP学员的问题，要根据负责人，动态引用所有相关数据。效果如动画所示。这种最常用的有4个公式，跟着卢子一起来看看。1.FILTER这个公式，一定要用动画演示，才能展示魅力。输入公式，回车，自动扩展区域，生成所有结果。=FILTER(B4:C11,D4:D11=G2)语法说明：=FILTER(返回区域,条件区域=条件)这是office365特有的函数，即便是数组公式，也无需按三键，区域能够自动扩展，这是其他版本无法比拟的。2.筛选公式（也叫万金油公式）这是10年前的老方法了，只要提到相关的问题，都是这个套路。数组公式，需要按Ctrl+Shift+Enter三键结束。=IFERROR(INDEX(B:B,SMALL(IF($D$4:$D$11=$G$2,ROW($4:$11)),ROW(A1))),"")语法说明，这是固定的套路，只需更改里面提到的区域即可。=IFERROR(INDEX(返回区域,SMALL(IF(条件区域=条件,ROW(行号区域)),ROW(A1))),"")3.VLOOKUP+辅助列数组公式对于很多人来说，不容易理解和使用，因此最近几年才有了这个辅助列的查找方法。负责人出现多次，用COUNTIF判断次数后连接起来，这样就变成唯一值。=D4&COUNTIF(D$4:D4,D4)而负责人连接ROW，也能起到类似的作用，因此就可以用VLOOKUP进行查找，查找不到的嵌套IFERROR让错误值显示空白。=IFERROR(VLOOKUP($G$2&ROW(A1),$A:$C,COLUMN(B1),0),"")4.LOOKUP+辅助列跟方法3类似，都是通过A列的辅助列进行查找。=IFERROR(LOOKUP(1,0/($G$2&ROW(A1)=$A$4:$A$11),B$4:B$11),"")学无止境，每隔几年，总会有新公式出来，越学习，写的公式越简洁，效率越高。
}

Excel中有一个几乎“万能”的函数组合，在N多种问题的解决过程中都少不了她的身影，她就是Index+small+if+row组合！这个万金油的组合公式在日常工作中的应用非常广泛，今天我们先通过一个例子的剖析，让大家了解公式的原理，下次再来拓展几个经典应用。很多朋友在Excel中用公式做查询的时候，都必然会遇到的一个麻烦问题，那就是一对多的查找问题，大多数朋友做查询都是从vlookup、index-match组合入门的，然而遇到一对多查询的时候，如果不加辅助列，往往会束手无策，今天我们要讨论的Index+small+if+row组合，就是专门解决一对多查询的一个通用公式，如果你能完全掌握这个思路的话，不夸张的说，在Excel中基本上就没有什么查询可以难住你了，除非极为少见的个例需要用到另一个高级函数indirect（RC引用）。好了，言归正传，先看一个效果图，到底一对多查询是怎样的：由动画演示可以看出，只要输入一个学号，就会根据学号在左边的成绩表中查询该学生的相关成绩。得到这个查询结果，只用了一个数组公式：=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出现）可以看看效果：这是一个公式下拉的结果，好像是对a列进行了排序一样的效果，有点意思吧~~~又啰嗦了一堆，算是把small大概说了一下，现在回到我们的问题，还是看看图：我们需要的是4、17、19（实际上是4、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才能算是入门了吧！}

在Excel中有一些非常经典的函数组合，大家比较熟悉的有INDEX-MATCH组合，还有INDEX-SMALL-IF-ROW组合（也叫万金油组合），当然还有很多其他的组合，今天分享的这个组合同样非常有用，下面会通过四个常见的问题让大家见证这对组合所带来的美妙时刻，当然还是要先认识一下今天的两个主角：COUNTIF和IF这两个大伙都非常熟悉的函数。COUNTIF函数的使用方法：COUNTIF（范围，条件），函数可以得到符合条件的数据在范围中出现的次数，简单来说这个函数就是条件计数用的；IF函数的用法：IF（条件，满足条件的结果，不满足条件的结果），用一句话来说，如果给IF一个条件（第一参数），当条件成立的时候给返回一个结果（第二参数），当条件不成立的时候返回另一个结果（第三参数）。关于这两个函数的基本用法，之前的教程多次讲过，不再赘述，下面先来看看他们两相遇以后发生的第一个问题：核对订单时遇上的问题假设A列是全部的订单号，D列是已经发货的订单号，现在需要在B列对已发货的订单进行标记（为了防止大家眼花，箭头仅指出了两个对应的订单号）：对于这个问题，我想各位一定不陌生，这问题在对账的时候经常用吧，也可能有些小伙伴已经迫不及待的喊着VLOOKUP了，实际上B列的公式是这样的：=IF(COUNTIF(D:D,A2)>0,"已发货","")首先用COUNTIF进行统计，看A2单元格的订单号在D列出现了几次，如果没有出现的话就是没发货，反之就是已发货。因此用COUNTIF(D:D,A2)>0作为IF的条件，如果订单在D列出现了（出现次数大于0），那么返回"已发货"（注意汉字要加引号），否则返回空白（两个引号代表空白）。第一个问题都看明白了吧，再来看第二个问题：COUNTIF查重复案例：重复订单怎么找A列是来自多个文员登记的订单统计表，汇总后发现有一些是重复的（为了方便查看，可以先将订单号排序），现在需要在B列对有重复的订单进行标注：这同样是一个上榜率非常高的问题，解决办法也很简单，B列公式为：=IF(COUNTIF(A:A,A2)>1,"有","")与前一个问题类似，这次直接计算每个订单在A列出现的次数，不过条件要变一下，不是大于0而是大于1了，这一点也很好理解，只有出现次数大于1的才是重复订单，因此使用COUNTIF(A:A,A2)>1作为条件，再让IF返回我们需要的结果。当找到重复订单后，第三个问题也就出来了，要在订单号后面标准是否保留的信息，如果有重复的则保留一个：这个问题乍一看还挺麻烦，实际上对于问题2的公式稍作修改就可以实现：=IF(COUNTIF($A$2:A2,A2)=1,"保留","")注意这里的COUNTIF，范围不再是整列，而是$A$2:A2，这种写法随着公式下拉，统计的范围会随着变化，得到的结果是这样的：不难看出，结果为1的都是首次出现的订单号，也是我们需要保留的信息，因此用来做条件的时候就用了等于1。前面三个问题都是与订单号有关的，最后这个问题是和供货商考核有关的，这可是决定了是否能够续约的关键问题哦。根据公司规定，对每个供货商有六项考核指标，A为最好，E为最差，六项指标中有两个或两个以上的E，则不续约：规则还算比较简单，来看看公式是不是同样简单：=IF(COUNTIF(B2:G2,"E")>1,"否","")这一次COUNTIF的范围变成了行，在B2:G2这个范围内统计"E"出现的次数，同样注意要加引号，当统计结果大于1时，说明该供货商就有两项以上的差评（如果你非要用大于等于2，我也没意见），再使用IF得到最终结果。最后要说的这个问题，财务岗位的伙伴一定不陌生，有时候我们会遇到这种情况：在一列数据中有一正一负的情况，这时候需要把未抵消的数据标注（提取）出来，比如图中的例子：这个问题或许曾令很多人头疼，其实使用今天的这两个函数组合很容易就解决了，公式为：=IF(COUNTIF(A:A,-A2)=0,A2,"")注意这里COUNTIF中的条件-A2，也就是找与A2可以互相抵消的数字，如果没有的话，通过IF得到A2，反之得到空值，使用了一个负号就巧妙的解决了一件麻烦事。通过上面的五个案例，大家或许会有一种感觉，这两个函数的组合比起其他一些函数组合相对容易理解，只要找到正确的思路，很多问题都可以用这对组合来处理。事实也是如此，善于使用COUNTIF来进行各种条件计数，再配合IF函数就能得到更加多样的结果，ifcountif筛选重复数据是经常使用的。解决问题不一定要很难的函数，简单函数用好了也是非常愉快的一件事。相关学习推荐：excel教程以上就是Excel函数学习之countif()函数的使用方法的详细内容，更多请关注php中文网其它相关文章！}