excel2016一对多查询公式讲解

 　　我们在查询数据的时候，经常会用到VLOOKUP函数。

　　但是有时候，我们会遇到这样的问题，在不止一个，而不是一个的情况下得到一个合格的结果。这个时候，VLOOKUP就会犯难了。

　　比如下图，左边的A1:C10是学生列表。现在，根据单元格F1中“EH类”的指定条件，我们需要在单元格F2:F10中提取该类的所有学生列表。

　　今天，我将讨论函数查询的一体化例程：Index  Small。

　　在单元格F2中输入进行以下两个数组计算公式，按住Ctrl  Shift键，按回车键，然后通过向下可以填充：

　　=index(B:B,small(if(A$1:A$10=F$1,row($1:$10),4^8),row(A1))),"")

　　这个公式看起来比上面的VLOOKUP解决方案更苗条，更吸引眼球。坦白说，和我十年后明星的土匪号——吻合。

　　1.公式解释

　　if(a$1:a$10=f$1,row($1:$10),4^8)

　　这部分先判断A1:A10的值是否等于F1。如果一个相等，则返回a列类对应的行号，否则我们返回4 8，即65536。通常，工作表的这个位置没有数据。

　　结果是一个内存阵列：

　　65536; 2; 3; 65536; 65536; 65536; 65536; 8; 65536; 10}

　　小函数取中频函数的结果数。随着公式的向下填充，第一、第二、第三.依次提取n个最小值，依次得到满足类条件的行号。

　　然后，使用index函数，以small函数可以返回的行号之前作为一个索引值，在b列提取对应的名称结果。

　　当SMALL函数得到的结果为65536时，表示合格的行号已经用完。此时index函数也返回单元格B65536的引用，结果是一个无意义的零。为了避免这个问题，您可以添加一个小尾巴“”

　　使用””是避免出现无意义零值的聪明方法，但当搜寻结果是数字或日期时，这个方法会转换为文字值，不利于数据的准确表达和重新统计分析。