【公式解析系列】之条件筛选数组公式的几种经典解法

gouweicao78 · 发表于 2010-10-20 08:56

署名作者: gouweicao78

适用版本: 2010 2007 2003以前版本

语言环境: 简体中文

通过对《Vlookup和Lookup函数一一过招》（请百度搜该文）的学习，大家对单一结果的查询有了掌握。那么对满足条件的多个结果，怎样在表中一行行地列出来呢？除了添加辅助列用LOOKUP或VLOOKUP、INDEX+MATCH等办法查询外，最常用的就是数组公式了。下面就有关解题思路进行解释：

必杀技：=INDEX(结果列，SMALL（IF（条件，ROW满足条件的行号，较大的空行行号），ROW(1:1)))&""

【原理】1、数组运算；2、空单元格与空文本合并。
【特点】得到的结果是文本。
【实例】如图：筛选张三的领用记录（条件是E1单元格的“张三”）。

=INDEX(B:B,SMALL(IF(A$2:A$8=E$1,ROW(A$2:A$8),4^8),ROW(1:1)))&""

复制代码

【解析】：对照“必杀技”，多数人已可以瞧出一点端倪来。
1、条件模块：IF(A$2:A$8=E$1,ROW(A$2:A$8),4^8)
表示如果A2:A8的姓名等于E1的“张三”，则返回A2:A8的行号，否则返回4^8，即65536。
IF（如果，则，否则——这语言不难读吧
条件模块经过数组运算，因为A2、A5、A8满足条件，所以返回的是行号数组{2;65536;65536;5;65536;65536;8}。

2、排序模块：SMALL(IF,ROW(1:1))
公式的第1行，ROW(1:1)返回{1}，在第2行返回{2}……因此，利用SMALL+ROW可以将条件模块返回的行号数组从小到大依次排序得出。当然，此例中SMALL({2;65536;65536;5;65536;65536;8},1)得到的是2，第2小的是5，第3小的是8，第4小及以后都是65536。

3、引用模块：INDEX(引用列，SMALL得到的行号)
=INDEX(B:B,2)——引用B2，=INDEX(B:B,65536)——引用B65536

4、容错模块：&""
当公式到了第4行，3个满足条件的记录都已经找出来，此时公式是=INDEX(B:B,65536)&“”
因为Excel2003的最大行数是65536行，而在这一行中，一般不会有人输入数据，是空单元格。因此，利用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。
请参考：《在Excel中究竟何为“空”？》（请百度搜该文）

TuXcBPVk.rar (13.47 KB, 下载次数: 156)

gouweicao78 · 发表于 2010-10-20 09:01

关于不重复值个数的统计方法，可以参照：[原创]统计不重复值的三种解法简介（请百度搜该文）

列出不重复记录，经典的也跟这3种原理类似。
1、MATCH=ROW法：

<br/>=INDEX(A:A,SMALL(IF(MATCH(A$2:A$8,A$2:A$8,0)=ROW(A$2:A$8)-1,ROW(A$2:A$8),4^8),ROW(1:1)))&""<br/>

复制代码

除了IF条件模块不一样外，都是1楼的必杀技。
温习一下：
【原理】MATCH(A$2:A$8,A$2:A$8,0)——精确定位A2:A8在A2:A8中第一次出现的位置，得到{1;2;3;1;5;2;1}
ROW(A$2:A$8)-1——用数据区域的行号扣除数据区域起始行的上一行行号1，将返回一个从1开始步长为1的等差序列{1;2;3;4;5;6;7}
判断MATCH=ROW成立，则可以找到第一次出现的数据，第2次出现（就是重复值）被排除了。

2、COUNTIF法：

=INDEX(A:A,MIN(IF(COUNTIF(E$11:E11,A$2:A$8)=0,ROW(A$2:A$8),4^8)))&""

复制代码

条件：COUNTIF(E$11:E11,A$2:A$8)=0
【原理】
公式第1行：E$11:E11中当然没有A列的数据，因此条件模块判断都是TRUE，{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}→条件模块得到{2;3;4;5;6;7;8}→MIN(IF)得到2→E12单元格就得到“张三”这个结果。
公式第2行：因为E11是相对引用，而E$11是行绝对引用，第2行的公式是COUNTIF(E$11:E12,A$2:A$8)=0
E$11:E12中，已经有“张三”出现，所以COUNTIF=0中张三对应的都是FALSE，即{FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}→条件模块得到{65536;3;4;65536;6;7;65536}→MIN(IF)得到3→E13单元格就得到A3即“李四”这个结果。……其他同理。

3、FREQUENCY法：

<br/>=INDEX(A:A,SMALL(IF(FREQUENCY(A$11:A$17,A$11:A$17),ROW(A$11:A$17),4^8),ROW(1:1)))&""<br/>

复制代码

【原理】条件模块：利用FREQUENCY函数对数值分布频率计算第2次及以后出现的数字都返回0的原理，求出不重复行号。其他同“必杀技”。

[此贴子已经被作者于2010-10-20 14:05:16编辑过]

gouweicao78 · 发表于 2010-10-20 09:02

从1楼、2楼看，必杀技已经破解了多半，关键在于条件模块的构建。
【实例1】：如图要找出A列有而B没有的数据。

【分析】1、既然是A列有，而B列没有，那么肯定是在A里面找。因此INDEX的第1参数就是A列。
2、有还是没有？即包含不包含、出现没出现、找到找不到的问题。

=INDEX(A:A,SMALL(IF(ISNA(MATCH(A$20:A$26,B$20:B$24,0)),ROW(A$20:A$26),4^8),ROW(1:1)))&""

复制代码

【解析】：MATCH(A,B,0)——在B中精确查找A的东西，找不到就是没有，返回#N/A错误。再用ISNA来判断这个错误值，构建条件模块。
此外，还可以用COUNTIF，比如：

=INDEX(A:A,SMALL(IF(COUNTIF(B$20:B$24,A$20:A$26)=0,ROW(A$20:A$26),4^8),ROW(1:1)))&""

复制代码

这里COUNTIF说白了，就是B中包含、或叫出现A的个数，如果个数为0，也就是没有咯。

【实例2】加条件，实例1中得到的结果没有考虑A列自身有重复值，出现了2个A。现在要去除重复部分：

=INDEX(A:A,SMALL(IF((MATCH(A$20:A$26,A$20:A$26,0)=ROW(A$20:A$26)-19)*ISNA(MATCH(A$20:A$26,B$20:B$24,0)),ROW(A$20:A$26),4^8),ROW(1:1)))&""

复制代码

【解析】：1、在实例1的条件中加入了MATCH=ROW来去重复值。
2、利用(MATCH=ROW)*ISNA进行数组相乘，表示同时满足这2个条件。

【引申】条件可以再构建，怎样合适就怎样做。数组公式的魅力尽在于此！
【简化】本帖中，ROW(A$2:A$8)之类的用法，因为ROW返回行号与列根本无关，都可以用ROW($2:$8)来简化。其它仍有简化空间，为方便阅读，特此写全。

题外话：其实，很多令人头痛的提问，并不是问题难，而是问问题的人表述能力的匮乏，让解题者难以捉摸出题人设定的条件。

raulerini · 发表于 2010-10-20 12:35

学习了！！收藏起来。谢谢gouweicao兄。。[em01]

wiexcel · 发表于 2010-10-20 12:39

久仰狗尾草大名

mn860429 · 发表于 2010-10-20 13:48

[em02]谢谢草版。

ljh_xh · 发表于 2010-10-20 14:10

收藏起来

hhzjxss · 发表于 2010-10-20 19:55

不错的东东，谢谢分享！

鲁鲁90 · 发表于 2010-10-20 20:55

[em17]

yaoxuejian · 发表于 2010-10-21 11:05

学习一下·

		自动登录	找回密码
密码			注册

[分享] 【公式解析系列】之条件筛选数组公式的几种经典解法

【续】之求不重复值的三种经典解法

【续】之条件构建