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

楼主 gouweicao78	【声明】请尊重原创版权！未经允许，不得转载！通过对《 Vlookup和Lookup函数一一过招附件：数组公式（条件筛选）.rar 【原理】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中究竟何为“空”？
2楼 gouweicao78	关于不重复值个数的统计方法，可以参照： [原创]统计不重复值的三种解法简介列出不重复记录，经典的也跟这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)=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法： =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的原理，求出不重复行号。其他同“必杀技”。
3楼 gouweicao78	从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)来简化。其它仍有简化空间，为方便阅读，特此写全。题外话：其实，很多令人头痛的提问，并不是问题难，而是问问题的人表述能力的匮乏，让解题者难以捉摸出题人设定的条件。
4楼 dejia	刚刚看完EXCELHOME的精粹涉及到这部分内容，又跟此贴学习了一下。开头是挺迷糊，后来越看越明白，再后来是再看又迷糊，呵呵，估计是看多了，大脑缺氧了
5楼 gouweicao78	循序渐进，不要贪多哦（广告：*酒虽好，可不要贪杯哦）
6楼 VAT	在EXCELHOME中一直没有搞明白现在在ET终于有点懂了
7楼 BIN_YANG168	学习一下。
8楼 jackeroo	先拷贝到word里，嘿嘿。
9楼 gouweicao78	呵呵。注意1楼的声明。这将是本人书稿的部分内容，奉献出来，相信大家会用在合适的地方。文本可以拷贝，知识，只有学到脑子里才是自己的。祝ET学习之旅愉快！
10楼无心为爱	呵呵,真是有点晕了. 感谢分享!~
11楼 jackeroo	小草放心，我只用于自己学习，不会传播给别人的。以前看这些时候看不懂，现在看，呵呵，还是比较简单，多谢各位高手的无私分享。
12楼 jchwylmh	感谢楼主！
13楼江南	好详尽啊不错我已安装2007 ，什么文档我都不怕了呵呵
14楼王朝☆马汉	写的太好了，学习啊
15楼 ghmyy	先弄下来，学习学习
16楼 goldowl	Thanks a lot.
17楼倾城08	先下载，好好学习，多谢版主分享。
18楼 tntpai	马丁林,谁有马丁林,消化不良了
19楼 mingyang0925	版主超强，我已经下载了，正在学习中，有问题还得继续请教。。。。。。。
20楼 opelwang	先收藏，后学习。感谢分享。
21楼 yukaiwz	谢谢您的分享，让人一下明白了许多…… 谢谢楼主。
22楼 tryourbest	谢谢，感谢分享！收藏了
23楼灵鹤	写得很好哇，不过有部分还是看不懂。
24楼 zhangstone	楼主真是个好人，我喜欢你的open，小弟多次想你学习不少经验！拜谢了！
25楼 yncxxmj	越看越看不懂了。
26楼 ljx63426	有点晕头转向,下来慢慢领会.
27楼 tongliaozyr	下载学习，感谢楼主分享
28楼 yuezc	版主功力好深啊！
29楼 jackycheng001	在此，要感谢一下Excel的“公式求值”与F9功能，看数组公式太方便了，谢谢楼主的分享，挺实用的！
30楼 yzcyzc1023	讲的非常好，谢谢。
31楼 aob	先拷贝到word里，嘿嘿。
32楼 luckday	呵,真是有点晕了.
33楼 lrlxxqxa	其实这个公式关键就是条件筛选那里，过了那关别的都简单了
34楼 776531854	从什么地方问问题
35楼 lutqhywow	“只有学到脑子里的才是自己的”就是啊，光下载没没看，没学习。还真是一点用处都没有啊。这次谢谢版主了！
36楼 mjgdxx	很详细的讲解
37楼 herenqing	再学习学习
38楼 sam.tan	认真学习学习...
39楼龙行九天	下来学习下谢谢
40楼小敏敏	谢谢学习了
41楼真实的我	终于有点明白了
42楼一米阳光-洋洋	谢谢草版的指点,这个解法花了N久的时间一直公式求值,终于终于弄明白了.
43楼 pcxsunshine	感谢楼主的分享
44楼 pcxsunshine	刚好要这些内容,十分感谢楼主
45楼巫秀红
46楼 herenqing	学无止境，尚需理解！
47楼 wangqilong1980	非常好用，谢谢版主。
48楼 jhzghua	感谢分享!~
49楼 ZILOYY	我用excel2003好象无效
50楼 gouweicao78	2003版无效？是哪个公式？是否按Ctrl+Shift+Enter结束？
51楼 m_sampdoria	多谢版主分享。
52楼 pingdande	刚刚看完EXCELHOME的精粹涉及到这部分内容，又跟此贴学习了一下。开头是挺迷糊，后来越看越明白，再后来是再看又迷糊，呵呵，估计是看多了，大脑缺氧了
53楼 jjx511	我还是看不懂啊
54楼 jjx511	我正好有个表要做，要应用到这个，但是我怎么弄都不对啊
55楼 gouweicao78	这里是整理的文章区，一般不解答你的具体问题，请到【我的提问】版发帖提问，并将表格压缩为附件上传。
56楼 powermate	谢谢了
57楼 jedi	学习了，谢谢lz
58楼 ggsmart	*酒虽好，不要贪杯。话说：这个必须贪。
59楼 china_yilong	做个记号！
60楼 taotao740425	得装到脑子中，学习了。
61楼 ccf	经典，好帖！
62楼 tdjxxdkj	太需要这种思路和公式的解释了，如饥似渴。谢谢！
63楼 glhfgtd	太强了，谢谢楼主
64楼 wcymiss	留脚印方便查找。
65楼现代农民	学习了，谢谢分享
66楼 stevehai
67楼 huishi	先收下先，再看
68楼 APOLLO	学习了，受益匪浅
69楼 caoruijun	asdfasfdsafsafdsafdsafsdfdsa
70楼 lijing022	看了下感觉很不错，O(∩_∩)O 不知道用的好不好，每次用数字公式都显示错误了
71楼 l_z_x1111	昨天看帖子刚学会固定条件的筛选。但是发现我要做的不止如此。如附件，筛选条件为语文、数学、英语成绩，筛选条件由使用者来选择，下限分数也由使用者来输入。我只知道用数组+SMALL来做单一条件的，不过多条件的就不会了。。貌似HLOOKUP和VLOOKUP都不管用，因为要返回一整列。。不知道该用哪个函数来查找。。多谢了~
72楼 l_z_x1111	忘了附件了。。工作簿.rar
73楼 wangpingqing	高手啊～～
74楼 bensonlei	真是精品中的精品! 学习了! 解决了很好过往的疑团与不明!
75楼传递	下载了慢慢消化，嘿嘿仔细瞧瞧
76楼水中的牛	精辟啊。
77楼 kmbobo	FREQUENCY函数不太理解，需要慢慢消化。求不重复值的方法好多，而且一些公式理解起来有难度啊！
78楼 hylees	精彩
79楼 gouweicao78	FREQUENCY函数只对付数值，如果单元格内不是数值，有些可以用MATCH、CODE等来配合。求不重复值的函数解法，核心的部分就这3种，具体应用时可能有所变异，但核心是一样的。
80楼 gouweicao78	没注意你在这个帖子里提问。一般来说，这种问题需要另外发帖，这个帖子是分享心得的，呵呵。根据72楼附件要求，要可以改变筛选条件，那么公式改为： =INDEX(A:A,SMALL(IF(INDEX($B$2:$D$13,0,MATCH($A$17,$B$1:$D$1,0))>=$B$17,ROW($2:$13),4^8),ROW(1:1)))&"" 就是IF($B$2:$B$13>=$B$17改不等式左边的引用区域为INDEX($B$2:$D$13,0,MATCH($A$17,$B$1:$D$1,0))，其中INDEX第2参数为0，返回一列。
81楼 abovethesun	请教一下红圈里的代码是什么意义，谢谢
82楼 l_z_x1111	多谢~
83楼 dvdiso	附件例表 B3列公式为何不能修改，一修改就什么都出不了了，比如=INDEX(B:B,SMALL(IF(A$2:A$15=K$1,ROW(A$2:A$15),4^8),ROW(1:1)))&""
84楼 heshixun3f711	谢谢分享!
85楼白鸽gaga	XIEXIEFENXIANG
86楼童心	学习一下
87楼白云_蓝天	必杀技：=INDEX(结果列，SMALL（IF（条件，ROW满足条件的行号，较大的空行行号），ROW(1:1)))&"" 若从多个工作表中提取数据呢？该数组公式如何修改？
88楼白云_蓝天	=INDEX('1月'!G:G,SMALL(IF('1月'!$B$3:$B$12000=$C$2,ROW($3:$11998),4^8),ROW(1:1)))&"" 公式是从“1月份”表取数，若同时从2月
89楼白云_蓝天	=INDEX('1月'!G:G,SMALL(IF('1月'!$B$3:$B$12000=$C$2,ROW($3:$11998),4^8),ROW(1:1)))&"" 公式是从“1月份”表取数，若同时从2月......12月份表中取数呢，公式如何修改？
90楼白云_蓝天	代码部门日期数量票号 320 焕然五金 2010-05-21 6 00297768 370 长空服务 2010-05-21 5 00087551 370 长空服务 2010-05-21 5 00087596 320 焕然五金 2010-05-21 2 00297774 320 焕然五金 2010-05-21 19 01737076 320 焕然五金 2010-05-21 19 01737132 370 长空服务 2010-05-21 14 00087569 370 长空服务 2010-05-21 13 00087556 1370 家政服务 2010-05-21 13 00087583 320 焕然五金 2010-05-21 37 01737095 1370 家政服务 2010-05-21 13 00087583 代码部门日期数量票号 320 焕然五金 2010-05-21 6 00297768 焕然五金 2010-05-21 2 00297774 焕然五金 2010-05-21 19 01737076 焕然五金 2010-05-21 19 01737132 焕然五金 2010-05-21 37 01737095 在单元格D15中录入代码320时，从1月表中提取数据的公式： {=INDEX(B:B,SMALL(IF($B$2:$B$20=$H$15,ROW($2:$20),4^8),ROW(1:1)))&""} 若同时提取1-3月的相关数据，公式如何修改？
91楼罗刚君	上传工作簿
92楼 gouweicao78	建议在【我的提问】版发帖提问，上传压缩后的工作簿。根据描述，用公式的方案不是很方便。
93楼兲兲兲	学习了**！
94楼 larkzh	终于明白了4^8的意思，非常感谢！
95楼 fzh2618182	下来认真学习一下，谢谢！
96楼 sue_huangyong	对于第一个例子，选出张三领用的清单，如果我需要引用这个清单，但不需要包含那些空值，如何操作呢？
97楼 hl_irnt	学习了
98楼猴子	哎呀！咋就这么爽啊、、、好好学习天天向上
99楼 feisky1219
100楼 feisky1219
101楼芋臻子	总结得很好，只是还没懂
102楼 tsoyzm	需要这样的分析
103楼水吉果果	精华啊！欢呼学习****谢谢
104楼 tsoyzm	这个太好了，感谢楼主
105楼 wawaxu	好晕那。不知道从何学起，只怪自己基础太差了。
106楼好大一棵树王玉	高手中的高手,愿楼主多出好书!
107楼好大一棵树王玉	=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)))&"" 一定要用A:A,而不能用A2:A8,我就这错了!希望大家注意!
108楼好大一棵树王玉	真的很高兴,我已经能看懂了,就是不知能不能运用!
109楼龙兴天下	楼主好强大**！
110楼闻启学	学习一下先多谢楼主
111楼 eyat	深入浅出，好东西
112楼 ludenic	精彩，好资源。thanks.
113楼 JustinTianyi	看着头有点大啊，不过能稍稍理解一些了，谢谢版主的精彩讲述
114楼 hustclm	好东西啊，看明白了95%，接下来就是活学活用到自己以后的工作了，主要是作者讲的解题的思想和思路，真的非常重要，感谢！
115楼闻启学	我来学一下这个一直我都是个谜不了解为何
116楼 kyser	mark 回去看看
117楼 cartor	大受启发啊!
118楼 jijiafei	学习
119楼 philips0108	Small.Row 函数错误.zip 按照楼主的说法，建了个SMALL&Row 的工式，为什么不能用？
120楼 gouweicao78	数组公式，请按Ctrl+Shift+Enter结束
121楼 ruoyu	excel真是博大精深，要学会不容易呀
122楼 qj0378	，学习了！
123楼 icenotcool
124楼 ksteddy	谢谢您的分享，在学习中
125楼 862119359	楼主威武，终于明白了index+small是什么意思了，帮了大忙
126楼 E林好汉	谢谢分享！
127楼 swbuing	学习学习学习
128楼 313228239	谢谢楼主大神
129楼 lrlxxqxa
130楼 Snooze	很复杂的样子
131楼疯狂的老舅	深奥，看不明白啊
132楼我不愿让你一个	老师你得帮帮，我有问题请教你啊，跪谢你了
133楼我不愿让你一个	如何对这22选5，任意5码的组合做一个筛选条件新建 Microsoft Excel 工作表.rar
134楼 poiuyman5	VERY GOOD
135楼 bjlpggjw	感谢楼主分享，支持。