条件筛选数组公式的几种经典解法.doc

条件筛选数组公式的几种经典解法必杀技：=INDEX(结果列，SMALL（IF（条件，ROW满足条件的行号，较大的空行行号），ROW(1:1)&【原理】1、数组运算；2、空单元格与空文本合并。【特点】得到的结果是文本。【实例】如图：筛选张三的领用记录（条件是E1单元格的“张三”）。条件筛选记录.jpg (19 KB, 下载次数: 35)下载附件 保存到相册 【公式解析系列】之条件筛选数组公式的几种经典解法2009-3-25 20:36 上传1. =INDEX(B:B,SMALL(IF(A$2:A$8=E$1,ROW(A$2:A$8),48),ROW(1:1)&复制代码【解析】：对照“必杀技”，多数人已可以瞧出一点端倪来。1、条件模块：IF(A$2:A$8=E$1,ROW(A$2:A$8),48)表示如果A2:A8的姓名等于E1的“张三”，则返回A2:A8的行号，否则返回48，即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)引用B655364、容错模块：&当公式到了第4行，3个满足条件的记录都已经找出来，此时公式是=INDEX(B:B,65536)&“”因为Excel2003的最大行数是65536行，而在这一行中，一般不会有人输入数据，是空单元格。因此，利用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。关于不重复值个数的统计方法列出不重复记录，经典的也跟这3种原理类似。1、MATCH=ROW法：1.2. =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),48),ROW(1:1)&除了IF条件模块不一样外，都是1楼的必杀技。温习一下：【原理】MATCH(A$2:A$8,A$2:A$8,0)精确定位A2:A8在A2:A8中第一次出现的位置，得到1;2;3;1;5;2;1ROW(A$2:A$8)-1用数据区域的行号扣除数据区域起始行的上一行行号1，将返回一个从1开始步长为1的等差序列1;2;3;4;5;6;7判断MATCH=ROW成立，则可以找到第一次出现的数据，第2次出现（就是重复值）被排除了。2、COUNTIF法：1. =INDEX(A:A,MIN(IF(COUNTIF(E$11:E11,A$2:A$8)=0,ROW(A$2:A$8),48)&条件：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;8MIN(IF)得到2E12单元格就得到“张三”这个结果。公式第2行：因为E11是相对引用，而E$11是行绝对引用，第2行的公式是COUNTIF(E$11:E12,A$2:A$8)=0E$11:E12中，已经有“张三”出现，所以COUNTIF=0中张三对应的都是FALSE，即FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE条件模块得到65536;3;4;65536;6;7;65536MIN(IF)得到3E13单元格就得到A3即“李四”这个结果。其他同理。3、FREQUENCY法：1.2. =INDEX(A:A,SMALL(IF(FREQUENCY(A$11:A$17,A$11:A$17),ROW(A$11:A$17),48),ROW(1:1)&【原理】条件模块：利用FREQUENCY函数对数值分布频率计算第2次及以后出现的数字都返回0的原理，求出不重复行号。其他同“必杀技”。续】之条件构建【实例1】：如图要找出A列有而B没有的数据。【分析】1、既然是A列有，而B列没有，那么肯定是在A里面找。因此INDEX的第1参数就是A列。2、有还是没有？即包含不包含、出现没出现、找到找不到的问题。1. =INDEX(A:A,SMALL(IF(ISNA(MATCH(A$20:A$26,B$20:B$24,0),ROW(A$20:A$26),48),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),48),ROW(1:1)&这里COUNTIF说白了，就是B中包含、或叫出现A的个数，如果个数为0，也就是没有咯。【实例2】加条件，实例1中得到的结果没有考虑A列自身有重复值，出现了2个A。现在要去除重复部分：1. =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),48),ROW(1:1)&【解析】：1、在实例1的条件中加入了MATCH=ROW来去重复值。2、利用(MATCH=ROW)*ISNA进行数