本帖最后由 祝洪忠- 于 2019-6-21 14:27 编辑
和大家分享两个数据查询的经典公式用法。
先来说说一对多查询。
所谓一对多，就是符合某个指定条件的有多个结果，要把这些结果都提取出来。下面咱们就说说一对多查询的典型用法，先看数据源： 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，如果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。
其他计算过程和第二个公式是完全一样的，细节咱们不再展开说了。
图文制作：祝洪忠
| 
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2019-6-21 14:24
发表于 2019-6-21 15:01
