=INDEX(A:A,SMALL(IF(MATCH(A$1:A$100&"",A$1:A$100&"",)=ROW($1:$100),ROW($1:$100),4^8),ROW(A1)))&""
這個函數(shù)里的 &""是什么意思 我沒看懂
1)MATCH(A$1:A$100&"",A$1:A$100&"",)
2)最尾的一個ROW(A1)))&""
把=INDEX(A:A,SMALL(IF(MATCH(A$1:A$100&"",A$1:A$100&"",)=ROW($1:$100),ROW($1:$100),4^8),ROW(A1)))&""拆開看看(這是個復(fù)合函數(shù),層次別拆亂了):
=INDEX(A:A,行數(shù)
行數(shù)為:SMALL(區(qū)域,位次)
區(qū)域為:IF(查找不重復(fù)數(shù)據(jù)的行數(shù)=相應(yīng)的行數(shù),相應(yīng)行數(shù),最大行數(shù))
查找(在單元格區(qū)域中搜索指定項,然后返回該項在單元格區(qū)域中的相對位置):
MATCH(A$1:A$100&"",A$1:A$100&"",),查找A1、A2、A3。。。在A1:A100中依次的位置數(shù)據(jù),生成一個100個數(shù)字組成的數(shù)組。
&"":是單元格的值合并上""(空白),1:100行中有空白單元格,比對查找時查找內(nèi)容及被查找區(qū)域都含上&""不致出錯。
生成結(jié)果中式子如拖得過多,多余部分也顯示為空格(""的作用)。
方法二:
http://hi.baidu.com/jh_richey/item/ca029e0f34e9d469d45a11d5
提取不重復(fù)值的幾個函數(shù)公式(很實用的哦)
依次如下:
=IF(SUM(1/COUNTIF($A$2:$A$15,$A$2:$A$15))>=ROW(A1),INDEX($A$2:$A$15,SMALL(IF(ROW($A$2:$A$15)-1=MATCH($A$2:$A$15,$A$2:$A$15,0),ROW($A$2:$A$15)-1,"0"),ROW(A1))),"")
=IF(SUM(1/COUNTIF($A$2:$A$15,$A$2:$A$15))>=ROW()-1,INDEX($A$2:$A$15,SMALL(IF(ROW($A$2:$A$15)-1=MATCH($A$2:$A$15,$A$2:$A$15,),ROW($A$2:$A$15)-1,"0"),ROW($A$2:$A$15)-1)),"")
=IF(AND(COUNTIF(D$1:D1,$A$2:$A$15)),"",INDEX($A$2:$A$15,MATCH(,COUNTIF(D$1:D1,$A$2:$A$15),)))
=INDEX(A2:A15,MATCH(,COUNTIF(E$1:E1,A2:A15),))
=IF(SUM(1/COUNTIF($A$2:$A$15,$A$2:$A$15))>=ROW()-1,INDEX($A$2:$A$15,MATCH(1,--ISNA(MATCH($A$2:$A$15,$F$1:F1,0)),0)),"")
=IF(AND(COUNTIF($G$1:G1,$A$2:$A$15)),"",INDEX($A$2:$A$15,SMALL(IF(FREQUENCY(MATCH($A$2:$A$15,$A$2:$A$15,),MATCH($A$2:$A$15,$A$2:$A$15,))>0,MATCH($A$2:$A$15,$A$2:$A$15,),""),ROW(1:1))))
=IF(SUM(IF($A$2:$A$15<>"",1/COUNTIF($A$2:$A$15,$A$2:$A$15)))>=ROW()-1,INDEX($A$2:$A$15,SMALL(IF($A$2:$A$15<>"",IF(ROW($A$2:$A$15)-1=MATCH($A$2:$A$15,$A$2:$A$15,0),MATCH($A$2:$A$15,$A$2:$A$15,0))),ROW(A1))),"")
=INDEX(A:A,MIN(IF(COUNTIF(B$1:B1,OFFSET($A$2,,,COUNTA($A:$A)-1)),65536,ROW(OFFSET($A$2,,,COUNTA($A:$A)-1)))))&""
=T(INDEX(A:A,MIN(IF(COUNTIF(J$1:J1,OFFSET($A$2,,,COUNTA($A:$A)-1)),65536,ROW(OFFSET($A$2,,,COUNTA($A:$A)-1))))))
=TEXT(INDEX(A:A,MIN(IF(COUNTIF(K$1:K1,OFFSET($A$2,,,COUNTA($A:$A)-1)),65536,ROW(OFFSET($A$2,,,COUNTA($A:$A)-1))))),";;;@")
以上都為數(shù)組公式,CTRL+SHIFT+ENTER
============================================================================
=LOOKUP(1,0/ISNA(MATCH(A$2:A$15,L$1:L1,)),A$2:A$15)
=================================================================
補充二個:
=INDEX($A$2:$A$15,SMALL(IF(MATCH($A$2:$A$15,$A$2:$A$15,)=ROW($1:$14),ROW($1:$14)),ROW(1:1)))
=INDEX(A:A,MIN(IF(COUNTIF(M$1:M1,$A$2:$A$15),4^8,ROW($2:$15))))&""
方法三:
http://jingyan.baidu.com/article/fedf07377e253135ac8977e8.html 這個簡單易學(xué)