|
-01- 具体应用 1.查找出省份所对应的所有城市 如下图所示，有几个不同的省份，每个省份又对应多个不同的城市。比如现在要把福建省对应的所有城市查询出来，结果就是D列所示。在D2单元格输入公式=IFERROR(INDEX(B:B,INDEX(MODE.MULT((A$1:A$11<>D$1)*{1,2}%+ROW($1:$11)),ROW(A1))),"")，下拉完成，不用按ctrl+shift+enter三键。 从函数公式中可以看到一个函数mode.mult。可以把它看作是mode函数的升级版。我们先了解一下mode函数。它是返回数组中出现频率最高的数字。函数的结构为MODE(number1,[number2],...)，每个参数可以是数字或数组。 下面举例说明一下： 如下图所示，在F14单元格输入公式=MODE(A14:D16)，结果返回1。因为在A14:D16这个区域中，1出现的次数最多，所以返回1。 下面改一下数据，将最后一个6改为5，这时出现次数最多的数字是1和5，都出现4次，但mode函数返回的结果还是1，也就是它只返回第一个出现次数最多的数字。如果我们想要把1和5都返回到结果中，就要用到mode.mutl这个函数。 mode.mutl是返回数组中出现频率最高的数字的垂直数组，简单来说，就是它把出现次数最多的所有数字形成一个纵向一维数组。还是举例说明。如下图所示，出现次数最多的数字是1和5。那么mode.mult返回的结果就是{1;5}。由于是一个数组，一个单元格放不下，所以下图的结果只显示出1。在编辑栏选中公式按F9就可以看到结果，如下2图。 了解了mode.mult这个函数，就可以解析一对多查询这个公式了。公式看不懂没关系，可以一步一步来拆解。 第1步，看A$1:A$11<>D$1这部分，是将A列的数据和D1的比较，条件成立的返回true，不成立的返回false，结果如F列所示。 第2步，看(A$1:A$11<>D$1)*{1,2}%这部分，{1,2}%就是{0.01,0.02}，用%是为了简写。这样就是方向不同的一维数组的运算，结果是一个二维数组，如F和G列所示。 第3步，看(A$1:A$11<>D$1)*{1,2}%+ROW($1:$11)这部分，实际就是上一步的结果再加一个行号，是二维数组和一维数组的运算。结果如F和G列所示。 第4步，就要用mode.mutl函数，MODE.MULT((A$1:A$11<>D$1)*{1,2}%+ROW($1:$11))这部分就是将上一步的结果作为mode.mult的参数。在上一步的结果中，出现次数最多的数字是3，7，11。所以mode.mult返回的结果就是{3;7;11}。而3，7，11刚好就是我们要查找的行号。做到这一步，剩下的和以前说的就一样了。 第5步，就是用index取出每个行号，index({3;7;11},row(a1))。当然这里也可以用small函数。 第6步，每个行号取出来了，就用index返回要查找的结果。index(b:b,行号）。 第7步，用iferror处理错误，完成。iferror(结果,"")。 好了，今天的一对多查询就说到这里。写完后，我自己的一个感受就是，想要创新就得有思路，当然创新很难，我也做不到。如果达不到就要学别人的公式和思路，想要看懂别人的公式，就得基础好，数组的运算肯定要掌握。只要基础好，就可以慢慢拆解别人的公式，然后多练多运用就能学会。 链接： https://pan.baidu.com/s/1RwNweGYedVXV1Vylwu6D4w 提取码：3mtv |
|
|
