條件篩選數(shù)組公式的幾種經(jīng)典解法

1、條件篩選數(shù)組公式的幾種經(jīng)典解法必殺技：=INDEX(結(jié)果列，SMALL( IF (條件，ROV滿足條件的行號，較大的空行行號)，R0W(1:1)&""【原理】1、數(shù)組運算；2、空單元格與空文本合并。【特點】得到的結(jié)果是文本。【實例】如圖：篩選張三的領(lǐng)用記錄(條件是E1單元格的“張三”)。1. =INDEX(B:B,SMALL(IF(A$2:A$8=E$1,ROW(A$2:A$8),4A8),ROW(1:1)&""復(fù)制代碼【解析】：對照“必殺技”，多數(shù)人已可以瞧出一點端倪來。1、條件模塊：IF(A$2:A$8=E$1,ROW(A$2:A$8

2、),4A8)表示如果A2:A8的姓名等于E1的“張三”，則返回A2:A8的行號，否則返回4人8，即65536。 IF (如果，則，否則一一這語言不難讀吧條件模塊經(jīng)過數(shù)組運算，因為A2、A5、A8滿足條件，所以返回的是行號數(shù)組 2;65536;65536;5;65536;65536;8。2、排序模塊：SMALL(IF,R0W(1:1)公式的第1行，R0W(1:1)返回1，在第2行返回2,因此， 禾U用SMALL+RO可以將條件 模塊返回的行號數(shù)組從小到大依次排序得出。當然，此例中SMALL(2;65536;65536;5;65536;65536;8,1) 得到的是 2，第 2 小的是 5,第 3

3、 小的是 8, 第4小及以后都是65536。3、引用模塊：INDEX(引用列，SMALL得到的行號)=INDEX(B:B,2)引用 B2, =INDEX(B:B,65536)引用 B65536 4、容錯模塊：&""當公式到了第4行，3個滿足條件的記錄都已經(jīng)找出來，此時公式是=INDEX(B:B,65536)& “”因為Excel2003的最大行數(shù)是65536行，而在這一行中，一般不會有人輸入數(shù)據(jù)，是空單元格。因此，利用空單元格與空文本合并返回空文本的特性，將超出結(jié)果數(shù)量的部分不顯示出來。關(guān)于不重復(fù)值個數(shù)的統(tǒng)計方法列出不重復(fù)記錄，經(jīng)典的也跟這3種原理類似。1、

4、MATCH=ROW1.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),4A8),ROW(1:1) &""除了 IF條件模塊不一樣外，都是1樓的必殺技。溫習(xí)一下：【原理】MATCH(A$2:A$8,A$2:A$8,0) 精確定位 A2:A8在A2:A8中第一次出現(xiàn)的位置，得到1；2;3;1;5;2;1ROW(A$2:A$8)-1 用數(shù)據(jù)區(qū)域的行號扣除數(shù)據(jù)區(qū)域起始行的上一行行號1，將返回一個從1開始步長為1的等差序列1;2;3;4;5;6;7判斷MATCH=ROW立則

5、可以找到第一次出現(xiàn)的數(shù)據(jù)，第2次出現(xiàn)(就是重復(fù)值)被排除了。2、COUNTIF法:1. =INDEX(A: A,MIN(IF(COUNTIF(E$11:E11,A$2:A$8)=0,ROW(A$2:A$8),4A8) &"條件：COUNTIF(E$11:E11,A$2:A$8)=0【原理】公式第1行：E$11:E11中當然沒有A列的數(shù)據(jù)，因此條件模塊判斷都是TRUETRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRU條件模塊得至U 2;3;4;5;6;7;8宀MIN(IF)得至U 2E12單元格就得到“張三”這個結(jié)果。公式第2行：因為E11是相對引用，而 E$1

6、1是行絕對引用，第 2行的公式是COUNTIF(E$11:E12,A$2:A$8)=0E$11:E12中，已經(jīng)有“張三”出現(xiàn)，所以COUNTIF=0中張三對應(yīng)的都是 FALSE即FALSE;TRUE;TRUEFALSE;TRUE;TRUE;FALSB條件模塊得至 U65536;3;4;65536;6;7;65536宀MIN(IF)得到E13單元格就得到 A3即“李四”這個結(jié)果?！逼渌?。3、FREQUENC法:1.2. =INDEX(A:A,SMALL(IF(FREQUENCY(A$11:A$17,A$11:A$17),ROW(A$11:A$17),4A8),ROW(1:1)&&qu

7、ot;"【原理】條件模塊：利用FREQUENCY數(shù)對數(shù)值分布頻率計算第 2次及以后出現(xiàn)的數(shù)字都返 回0的原理，求出不重復(fù)行號。其他同“必殺技”。續(xù)】之條件構(gòu)建【實例1】：如圖要找出 A列有而B沒有的數(shù)據(jù)?！痉治觥?、既然是A列有，而B列沒有，那么肯定是在A里面找。因此INDEX的第1參數(shù)就是A列。2、有還是沒有？即包含不包含、出現(xiàn)沒出現(xiàn)、找到找不到的問題。1.=INDEX(A:A,SMALL(IF(ISNA(MATCH(A$20:A$26,B$20:B$24,0),ROW(A$20:A$26),4A8),ROW(1:1)&""【解析】：MATCH(A,B,

8、O)在B中精確查找A的東西，找不到就是沒有，返回#N/A錯誤。再用ISNA來判斷這個錯誤值，構(gòu)建條件模塊。此外，還可以用 COUNTIF比如：=INDEX(A:A,SMALL(IF(COUNTIF(B$20:B$24,A$20:A$26)=0,ROW(A$20:A$26),4A8),ROW(1:1) )&"這里COUNTIF說白了，就是 B中包含、或叫出現(xiàn) A的個數(shù)，如果個數(shù)為 0，也就是沒有咯。【實例2】加條件，實例1中得到的結(jié)果沒有考慮 A列自身有重復(fù)值，出現(xiàn)了 2個A?，F(xiàn)在 要去除重復(fù)部分：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),4A8),ROW(1:1) &""【解析】：1、在實例1的條件中加入了 MATCH=ROW去重復(fù)值。2、利用(MATCH=ROW)*IS