【公式解析系列】多行多列条件筛选解法

楼主 gouweicao78	使用数组公式从数据中筛选出符合条件的记录列表，在 [原创]【公式解析系列】之条件筛选数组公式的几种经典解法 1、条件模块：根据要求用IF函数构建一个能够返回记录行列号数组或大数的模块； 2、排序模块：用于将记录从数组中取出，常用SMALL+ROW、MIN等 3、引用模块：用于引用单元格 4、容错模块：大数——用于返回空白单元格并结合&""返回空文本，以达到替换IF判断容错的目的。下面，讲解一下多行多列条件筛选的数组公式解法（以2003版为例）：【原理】1、数组运算；2、TEXT改变数字格式；3、INDIRECT函数R1C1引用样式。【实例】如图：筛选“张三”（条件在G1）的消费记录（数据在A2:E12）【公式】 =INDIRECT(TEXT(SMALL(IF((A$2:A$12=G$1)(B$2:E$12<>""),ROW($A$2:$H$12)1000+COLUMN(B$1:E$16),65536256),ROW(例2!1:1)),"R0C000"),0)&"" 【解析】 1、条件模块：A2:A12的姓名等于G1的条件，且记录不为空，则返回数据所在的行号1000+数据所在的列号，否则返回65536256。假设B2、C2符合条件，则分别为2（行）1000+2（列）、2（行）1000+3（列）即2002、2003 注：可以视为 [原创]【公式解析系列】之多关键字排序解法 Excel2003版共65536行256列，因此行号1000再加列号，不会影响相互之间的关系，且最大值为65536256 2、排序模块，利用SMLL+ROW排序，这里就不再重复了。 3、TEXT模块：TEXT(SMALL,"R0C000")——比如2002、200365536256将变为：R2C002、R2C003、R65536C256 4、引用模块：INDIRECT("R2C002",0)——第2参数为0或FALSE，表示R1C1引用样式，利用这个函数引用单元格 R2C002、R2C003、R65536C256——分别表示第2行第2列、第2行第3列、第65536行第256列 5、容错模块：&""的原理。这里也不再重复了。【公式解析系列】之多行多列条件筛选.rar
2楼 gouweicao78	实战一下：如图，求用一个公式将A1:E4单元格（含空单元格）中的记录不重复地列出来回顾： [原创]【公式解析系列】之条件筛选数组公式的几种经典解法在G2单元格输入公式，并按CTRL+SHIFT+ENTER结束后向下复制： =INDIRECT(TEXT(MIN(IF((A$1:E$4<>"")(COUNTIF(G$1:G1,A$1:E$4)=0),ROW(A$1:E$4)1000+COLUMN(A:E)),65536256),"R0C000"),0)&"" 除了条件模块，其他都一样。这个条件也不难理解： IF((A$1:E$4<>"")(COUNTIF(G$1:G1,A$1:E$4)=0)——如果A1:E4不为空且G$1:G1没有出现过A1:E4的值，则返回row1000+colum，否则返回大数65536256。小改一下： =INDIRECT(TEXT(MIN(IF((A$1:E$4="")+COUNTIF(H$1:H1,A$1:E$4),65536256,ROW(A$1:E$4)1000+COLUMN(A:E))),"R0C000"),0)&"" IF((A$1:E$4="")+COUNTIF(H$1:H1,A$1:E$4)——如果A1:E4为空或G$1:G1已出现过A1:E4的值，则返回大数65536256，否则返回row1000+colum。利用逻辑关系变换（可参考： [原创]逻辑判断小技巧再改一下： =INDIRECT(TEXT(MIN(IF(COUNTIF(I$1:I1,A$1:E$4&""),65536256,ROW(A$1:E$4)*1000+COLUMN(A:E))),"R0C000"),0)&"" 利用COUNTIF第2参数使用&""后将空单元格强制变为空文本，从而达到避免COUNTIF无法统计空单元格的问题。
3楼 xiatide334	很好，很经典，始终觉得版主是个超级负责的人
4楼 jackycheng001	楼主精益求精呢，看楼主的公式简化也受益匪浅~
5楼 yzcyzc1023	为什么例1行用ROW(例2!1:1)),而不用row(1:1).
6楼 kszcs	收藏,慢慢学习
7楼 wangqilong1980
8楼 laowuwyx	收藏学习！
9楼 goshin01	li hai aaaaaaaaaaa
10楼琦琦	请教一下，如何应用EXCEL公式找出各科目的最高成绩?谢谢！如何应用EXCEL公式找出各科目的最高成绩.rar
11楼 captain	我也有同样的问题：但不在连续的区域见附件求自动将表1中的各处自动累加表2中B5单元格起的位置。注意：表1中各处型号下面有可能添加或减少；备注中出现的"日期如果是小于今天日期"及出现有"暂停"、"取消"时，也不能累加到表2中B5单元格下面；几处中如果有重复的型号只收集一次。新建 Microsoft Excel 工作表.rar
12楼 captain	求最简化的公式，可以在表1或表2中用一行和一列作为辅助！
13楼 captain	谢谢陈总的解答和详解！我很愚钝了！还是有点晕！如果是不连续区域？加个条件后求其公式？就像这样的不知如何再解了！？谢谢！非常感激！快照57.gif
14楼 captain	谢谢陈总的解答和详解！我很愚钝了！还是有点晕！如果是不连续区域？加个条件后求其公式？就像这样的不知如何再解了！？谢谢！非常感激！快照57.gif
15楼 gouweicao78	请上传压缩的表格附件，而不是图片。
16楼 captain	谢谢陈总的答复！新建 Microsoft Excel 工作表.rar
17楼 gouweicao78	已另帖解决http://www.exceltip.net/thread-37631-1-1.html
18楼 captain	非常感谢陈总解答！
19楼好大一棵树王玉	学习中,练习中,进步中!真的很高兴!
20楼好大一棵树王玉	又做了一遍,感觉这回理解的能更深入一些了!
21楼 yeminqiang
22楼似水风流	我看不懂，不过很厉害，谢谢分享