|
有许多大神在网上发布了提取excel不重复值的组合公式，对大神们的敬佩之情，犹如长江之水，涛涛不绝啊~~~，下面把搜集的一些公式记录下来 如图，A列是数据列，后面B到L列是不重复值公式提取出来的，都已经验证！  下面是各列的公式，共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))),"") 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列最精简。由于本人天资愚钝，其中有的公示，还没有弄明白！ 大神的神作啊，不是所有的人都能弄明白的！ |
|
|
