重复值与唯一值小结

lss001

提取单列重复值与唯一值
a，提取唯一值(仅唯一)
辅助列=IF(AND(COUNTIF(A:A,A1)=1,COUNTIF(
$A$1:A1,A1)=COUNTIF(A:A,A1)),A1,"")
非辅助→数组公式<附带排序功能>
=IFERROR(SMALL(IF(FREQUENCY($A$1:$A$10,$A$1:$A$10)=1,$A$1:$A$10),ROW(A1)),"")
个数=SUMPRODUCT((COUNTIF(A:A,A:A)=1)/1)

b，提取唯一值(含重复)
辅助列=IF(COUNTIF($A$1:A1,A1)=1,A1,"")
非辅助<附带排序功能>
=IFERROR(SMALL(IF(FREQUENCY($A$1:$A$10,$A$1:$A$10),$A$1:$A$10),ROW(A1)),"")
非辅助→数组公式→单值<附带排序功能>
=IFERROR(SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($1:$10),$A$1:$A$10,""),ROW(A1)),"")
非辅助→数组公式→多值<附带排序功能>
=IFERROR(SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($1:$10),$A$1:$A$10,""),ROW($a$1:$a$10)),"")
个数=SUMPRODUCT((1/COUNTIF(A:A,A:A))
*************************************************
提示:行应把ROW($a$1:$a$10)
改为COLUMN($A$1:$J$1)

c，提取重复值(单个)
辅助列=IF(AND(COUNTIF(A:A,A1)>1,COUNTIF(
$A$1:A1,A1)=COUNTIF(A:A,A1)),A1,"")
非辅助→数组公式<附带排序功能>
=IFERROR(SMALL(IF(FREQUENCY($A$1:$A$10,$A$1:$A$10)>1,$A$1:$A$10),ROW(A1)),"")
个数=SUMPRODUCT((COUNTIF(A:A,A:A)>1)*(1/COUNTIF(A:A,A:A)))

d，提取重复值(全部)
辅助列=IF(COUNTIF(A:A,A1)>1,A1,"")
非辅助→数组公式<附带排序功能>
=IFERROR(SMALL(IF(COUNTIF($A$1:$A$10,$A$1:$A$10)>1,$A$1:$A$10),ROW(A1)),"")
个数=SUMPRODUCT((COUNTIF(A:A,A:A)>1)/1)

e，提取重复2次的值(单个)
辅助列=IF(AND(COUNTIF(A:A,A1)=2,COUNTIF(
$A$1:A1,A1)=COUNTIF(A:A,A1)),A1,"")
非辅助→数组公式<附带排序功能>
=IFERROR(SMALL(IF(FREQUENCY($A$1:$A$10,$A$1:$A$10)=2,$A$1:$A$10),ROW(A1)),"")

f，提取重复2次的值(全部)
辅助列=IF(COUNTIF(A:A,A1)=2,A1,"")
非辅助→数组公式<附带排序功能>
=IFERROR(SMALL(IF(COUNTIF($A$1:$A$10,$A$1:$A$10)=2,$A$1:$A$10),ROW(A1)),"")
*************************************************
*如果不用排序功能可参考重复文本唯一值公式*

g，重复文本唯一值(含重复)
数组公式→非辅助→单值
=IFERROR(INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),ROW($A$1:$A$10),""),ROW(A1))),"")
数组公式→非辅助→多值
=IFERROR(INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW($A$1:$A$10),ROW($A$1:$A$10),""),ROW($a$1:$a$10))),"")

h，多条件(或数组)唯一值(含重复)
*条件→
IF(($A$1:$A$10=1)*($B$1:$B$10<2),$C1:$C10)
数组公式→非辅助→单值<附带排序功能>
=IFERROR(SMALL(IF(MATCH(IF(($A$1:$A$10=1)*($B$1:$B$10<2),$C1:$C10),IF(($A$1:$A$10=1)*($B$1:$B$10<2),$C1:$C10),0)=ROW($A$1:$A$10),IF(($A$1:$A$10=1)*($B$1:$B$10<2),$C1:$C10),""),ROW(A1)),"")
数组公式→非辅助→多值<附带排序功能>
=IFERROR(SMALL(IF(MATCH(IF(($A$1:$A$10=1)*($B$1:$B$10<2),$C1:$C10),IF(($A$1:$A$10=1)*($B$1:$B$10<2),$C1:$C10),0)=ROW($A$1:$A$10),IF(($A$1:$A$10=1)*($B$1:$B$10<2),$C1:$C10),""),ROW($a$1:$a$10)),"")

提取多列重复值与唯一值
i，提取AB列重复值与唯一值
A列B列都有
辅助列=IF(COUNTIF(B:B,A1)>=1,A1,"")
A列有B列无
辅助列=IF(COUNTIF(B:B,A1)<1,A1,"")
A列无B列有
辅助列=IF(COUNTIF(A:A,B1)<1,B1,"")

j，AB两列合并后取不重复值
=INDEX($A$1:$B$10,SMALL(IF(MATCH($A$1:$A$10&$B$1:$B$10,$A$1:$A$10&$B$1:$B$10,0)=ROW($A$1:$A$10),ROW($A$1:$A$10),4^8),ROW(A1)),COLUMN(A1))

k，提取多列唯一值(含重复)
数组公式:
=INDIRECT(TEXT(MIN(IF((COUNTIF($D$1:D1,$A$2:$C$10)=0)*($A$2:$C$10<>""),ROW($A$2:$C$10)*10^5+COLUMN($A$2:$C$10),2^20*10^5+2^14)),"r0c00000"),)&""

补充内容 (2021-11-6 11:34):
l，提取多列重复2次(唯一值)
=IFERROR(INDIRECT(TEXT(MIN(IF((COUNTIF(A$1:C$9,A$1:C$9)=2)*(COUNTIF(D$1:D1,A$1:C$9)=0),ROW(A$1:C$9)*10^3+COLUMN(A$1:C$9),10^5)),"R0C000"),)&"","")