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

条件筛选数组公式的几种经典解法必杀技：=INDEX(结果列，SMALL（IF（条件，ROW满足条件的行号，较大的空行行号），ROW(1:1))) 【原理】1、数组运算；2、空单元格与空文本合并。【特点】得到的结果是文本。【实例】如图：筛选张三的领用记录（条件是E1单元格的“张三”）。 条件筛选记录.jpg (19 KB, 下载次数: 35) 下载附件 ?保存到相册 【公式解析系列】之条件筛选数组公式的几种经典解法 2009-3-25 20:36 上传 =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)——引用B655364、容错模块：当公式到了第4行，3个满足条件的记录都已经找出来，此时公式是=INDEX(B:B,65536)“”因为Excel2003的最大行数是65536行，而在这一行中，一般不会有人输入数据，是空单元格。因此，利用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。 关于不重复值个数的统计方法列出不重复记录，经典的也跟这3种原理类似。1、MATCH=ROW法： =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))) 除了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)=0E$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法： =INDEX(A:A,SMALL(IF(FREQUENCY(A$11:A$17,A$11:A$17),ROW(A$11:A$17),4^8),ROW(1:1))) 【原理】条件模块：利用FREQUENCY函数对数值分布频率计算第2次及以后出现的数字都返回0的原理，求出不重复行号。其他同“必杀技”。续】之条件构建 【实例1】：如图要找出A列有而B没有的数据。【分析】1、既然是A列有，而B列没有，那么肯定是在A里面找。因此INDEX的第1参数就是A列。2、有还是没有？即包含不包含、出现没出现、找到找不到的问题