有許多大神在網(wǎng)上發(fā)布了提取excel不重復值的組合公式,對大神們的敬佩之情,猶如長江之水,濤濤不絕啊~~~,下面把搜集的一些公式記錄下來
如圖,A列是數(shù)據(jù)列,后面B到L列是不重復值公式提取出來的,都已經(jīng)驗證!
下面是各列的公式,共11種,復制進去后,三指神功 Shift+Ctrl+Enter。
B列:=IF(SUM(1/COUNTIF($A$2:$A$15,$A$2:$A$15))>=ROW(A14),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(A14))),"")
C列:=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)),"")
D列:=IF(AND(COUNTIF(D$1:D1,$A$2:$A$15)),"",INDEX($A$2:$A$15,MATCH(,COUNTIF(D$1:D1,$A$2:$A$15),)))
E列:=IFERROR(INDEX($A$2:$A$15,MATCH(,COUNTIF(E$1:E1,$A$2:$A$15),)),"")
F列:=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)),"")
G列:=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))
H列:=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))),"")
I列:=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)))))&""
J姐:=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))))))
K列:=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))))),";;;@")
L列:=IFERROR(INDEX(A:A,MATCH(0,COUNTIF(L$1:L1,$A$2:$A$15),0)+1),"")
以上公式 ,個人覺得E列和L列最精簡。由于本人天資愚鈍,其中有的公示,還沒有弄明白!
大神的神作啊,不是所有的人都能弄明白的!
聯(lián)系客服