|
大家好，今天来说下vlookup的一对多查询，算是为vlookup发声吧。因为我们大部分同学以为vlookup只能查找第一个值，遇到一对多查询的时候，就说vlookup不行啊。其实vlookup也很委屈的，心里想着：哼，不是我不能，而是你不行！对于vlookup的进阶用法，前人已经总结过很多，不过我也只会用一点，哈哈。 还是来看下vlookup一对多查询的用法吧。如下图所示，根据学历查询出所有的姓名，在G2单元格输入公式=IFNA(VLOOKUP(ROW(A1),A$3:D$14,2,),"")，下拉填充，完成。是不是很简单呐，公式也很短。哈哈，其实这种方法用到了辅助列，有没有发现我把A列隐藏了。 取消A列的隐藏，看到它的结果如下所示。A3单元格的公式为=COUNTIF(D$3:D3,F$2)，就是统计D$3:D3这个区域中F2的个数，此时F2是大专，也就是统计大专的个数，结果为0。公式下拉A4单元格的公式为=COUNTIF(D$3:D4,F$2)，由于第1个D3是锁定的，所以区域扩展为D$3:D4，也就是统计D$3:D4中大专的个数，还是0个。 随着公式不断的下拉，区域也在不断的扩展，到了A6单元格时，公式为=COUNTIF(D$3:D6,F$2)，区域扩展为D$3:D6，也就是统计D$3:D6中大专的个数，此时D$3:D6中有1个大专，所以结果为1。从上图可以看出，当大专第1次出现时对应的数字就是1，并且是第1个1；当大专第2次出现时对应的数字是2，并且是第1个2。 辅助列做好后，下面就可以用vlookup查询了，在G2单元格输入公式=VLOOKUP(ROW(A1),A$3:D$14,2,)，向下填充，得到下图结果。vlookup的第1参数是ROW(A1)，也就是1；第2参数是A$3:D$14这个区域；第3参数是2，因为姓名在A$3:D$14这个区域的第2列；第4参数不写，用逗号留出位置，是精确查找。也就是在A列中查找1，返回第1个1所对应的姓名。查找到了A6的1，返回了B6的“李凯凯”。 公式下拉，到了G2，公式为=VLOOKUP(ROW(A2),A$3:D$14,2,)，此时只有第1参数由1变为2，其他不变。也就是在A$3:D$14这个区域的第1列中查找2，返回第1个2对应的姓名。找到了A9的2，返回对应B9的“程昊”。 随着公式的不断下拉，会产生错误值，因为找不到了。比如，公式到了G5时，vlookup的第1参数变为4，而在A$3:D$14这个区域中找不到4，所以返回错误值#n/a。所以用要ifna来处理下#n/a错误值，如果是#n/a错误返回空文本""。 其实，vlookup的第1参数1，2，3。。。可以看作第1个大专，第2个大专，第3个大专。。。，通过编序号的方式，用vlookup实现了一对多的查询。 如果不用辅助列也可以做到，公式为=IFNA(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET(D$3,,,ROW($1:$12)),F$2),B$3:B$14),2,),"")，按ctrl+shift+enter三键结束，向下填充。 其实和用辅助列的方法是一样的，只是在实现的过程中函数的组合方式有些不同。一个是用offset的多维引用和countif的结合把辅助列的结果放在一个内存数组中，另一个是用if({1,0},,)把2列单独的数据组合成一个2列的二维数组，作为vlookup的第2参数。 另一种不用辅助列的公式为=IFNA(VLOOKUP(ROW(A1),IF({1,0},MMULT(N(ROW($1:$12)>=COLUMN(A:L)),N(D$3:D$14=F$2)),B$3:B$14),2,),"")，不用按三键，直接向下填充。 这个公式主要用的是mmult的累加求和，目的也是为了把辅助列放在一个内存数组中，效果和offset的多维引用是一样的。虽然这个公式不用按ctrl+shift+enter三键，但是公式会比较长，写的时候要考虑mmult的矩阵大小。 下次分享另一种一对多查询的方法，它的优点是不用辅助列，不用按三键，公式又不是很长。到时记得学习哦。 今天是女神节，愿每一个善良的女人都能被这个世界温柔相待，都有一个爱她的人宠着她。 文件链接： https://pan.baidu.com/s/1sPJL7Ot-g7QWuw-XHmhSig 提取码：u4m6 |
|
|
