五分钟，学会⼀对多、多对多查询经典公式
⼩伙伴们好啊，今天⽼祝和⼤家分享两个数据查询的经典公式⽤法。
A~D列是⼀些员⼯信息，要根据F2单元格指定的学历，提取出所有“本科”的⼈员姓名。
G2单元格输⼊以下公式，按住Shift+ctrl不放，按回车，再将公式向下拖动到出现空⽩单元格为⽌：
=INDEX(C:C,SMALL(IF(B$2:B$11=F$2,ROW($2:$11),4^8),ROW(A1)))&""
公式看起来好长啊，不⽤担⼼，咱们⼀点点的拆解。
IF(B$2:B$11=F$2,ROW($2:$11),4^8)
IF(B$2:B$11=F$2,ROW($2:$11),4^8)
IF函数的意思是判断⼀个条件是不是成⽴，如果成⽴返回第⼆参数，否则返回第三参数。
本例中，要判断的条件是B$2:B$11=F$2，如果B列的学历等于F2单元格中指定的“本科”，就返回2~11⾏对应的⾏号ROW($2:$11)，否则返回4^8的结果65536，最终得到⼀个内存数组的计算结果：
{2;65536;65536;65536;6;65536;65536;65536;10;65536}
把这个结果放到⽰意图中，会更直观：
有⼩伙伴会说：为啥⽤65536啊，先不要着急，咱们⼀会⼉再说。
接下来，再⽤SMALL函数，在这个内存数组中提取内容。
SMALL函数的作⽤是返回⼀组数值中的第n个最⼩值，⽐如公式SMALL(A:A,3)，就是返回A列中的第三个最⼩值了。
本例中，SMALL函数⽤IF函数的计算结果作为第⼀参数，要在这个内存数组中提取第n个最⼩值，这⾥的n由谁来指定呢？就是公式最后部分的ROW(A1)。
ROW(A1)的作⽤是返回A1单元格的⾏号，结果是1。当公式向下复制时，参数会依次变成ROW(A2)、ROW(A3)、……，也就是得到从1开始、依次递增的序号。最终的⽬的是给SMALL函数⼀个动态的参数，依次从内存数组中提取出第1⾄n个最⼩值。
咱们回头再看看上⾯的⽰意图，SAMLL函数先提取出内存数组中的第1个最⼩值，结果是2。
这个2有啥⽤呢？继续往下看：
下⾯该轮到INDEX函数出场了，这个函数的作⽤是根据指定的位置信息，从数据区域返回对应位置的内容。刚刚的2就是位置信息，INDEX函数从C列中返回第⼆个单元格的内容，结果就是第⼀个符合条件的姓名“刘⼀⼭”。
公式向下复制到G3单元格，ROW(A1)变成了ROW(A2)，返回A2的⾏号2，SMALL函数再从内存数组中提取第2个最⼩值，结果是⼏呢？再看看上⾯的⽰意图，OK，是6。INDEX函数最终再返回C列中的第6个单元格中的内容“王希建”。
如果所有符合条件的⾏号都提取完了，公式还向下复制，这个时候SMALL函数的结果就是65536了，
最终INDEX函数返回C列第65536个单元格中的内容。
通常情况下，咱们的⼯作表没有这么多数据，也就是65536是空⽩单元格，INDEX函数引⽤空⽩单元格时，会返回⼀个⽆意义的0，所以咱们在公式的最后部分加上⼀个&""，使⽆意义的0不再显⽰。
这⾥的65536可以是其他任意⼀个较⼤的数值，只不过⼈们使⽤习惯了，只要你⾼兴，换成63565、65356都没问题。
还没有看晕的⼩伙伴，接下来咱们再说说多对多查询的公式。
多对多查询通常分为两种情况：⼀是要提取出同时符合多个条件的所有记录。⼆是要提取出多个条件符合其⼀的所有记录。
如下图所⽰，要提取出性别为“⼥”，部门为“财务”的所有⼈员姓名：
这⾥变成性别和部门两个条件了，两个条件要同时符合，公式怎么⽤呢？
H2单元格输⼊以下公式，按住Shift+ctrl不放，按回车，再将公式向下拖动到出现空⽩单元格为⽌：
=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&""
这个公式看起来更长了，不⽤担⼼，咱们仔细看看，公式中的⼤部分内容和前⾯讲过的公式⼏乎是⼀样的，有所不同的地⽅就是这⾥：
($B$2:$B$11=$F$2)*($D$2:$D$11=$G$2)
也就是把多个条件分别写到括号内，再⽤乘号把多个条件对应相乘。只有这⼏组条件同时符合了，对应相乘后的结果才是1，否则相乘结果是0。
在IF函数的第⼀参数中，0的作⽤相当于逻辑值FALSE，不等于0的数值则相当于逻辑值TRUE。也就是两个条件同时符合了，就返回对应的⾏号，否则返回65536。
公式的其他部分，和前⾯讲的第⼀个公式计算过程都是相同的。
多对多查询的第⼆种情况是多个条件符合其⼀。
⽐如下图中，要提取性别为“⼥”或是部门为“财务”的所有⼈员姓名。
可以在G2单元格输⼊以下公式：
=INDEX(C:C,SMALL(IF(($B$2:$B$11=$F$2)+($D$2:$D$11=$G$2),ROW($2:$11),4^8),ROW(A1)))&""
五分钟就学会的快速美食食谱这⾥告诉⼤家⼀个处理的⼩窍门：多个条件分别写到括号内，再⽤加号把多个条件对应相加就好。
这⼏组条件中只有⼀个符合了，对应相加后的结果就不是0，如果所有条件都不符合，相加结果才是0。
其他计算过程和第⼆个公式是完全⼀样的，细节咱们不再展开说了。
好了，今天咱们的分享就是这些吧，祝⼩伙伴们⼀天好⼼情！
图⽂制作：祝洪忠
ExcelHome云课堂
发布评论