一、只要重复，则显示 =IF(COUNTIF(A:A,A2)>1,"重复","")
条件格式：=COUNTIF($A$2:$A$14,A2)>1 设置颜色
二、第一次出现时不显示“重复” =IF(COUNTIF(A$2:A2,A2)>1,"重复","")
三、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","")
四、统计不重复的个数 =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))
五、提取不重复的姓名 数组
=INDEX(A:A,1+MATCH(,COUNTIF(B$1:B1,A$2:A$10),))&""
六、两列数据查重
=IF(COUNTIF(A:A,B2),"已有","")
七、不同表格查重方法 =COUNTIF(Sheet3!$B$1:$B$7,Sheet2!$B$1:$B$7)
八、多条件查重
设计一个vba代码
Public Sub abc()
Dim ar, br(1 To 60000, 1 To 10), I, k, flag, n
Dim d As Object, e
Set d = CreateObject("Scripting.Dictionary")
Set e = CreateObject("Scripting.Dictionary")
Range("A1").CurrentRegion.Interior.Color = xlNone
Range("A1").CurrentRegion.Sort Key1:=[a1], Order1:=xlDescending, Key2:=[b1], Order2:=xlAscending, Key3:=[c1], Order3:=xlAscending, Header:=xlYes
ar = Range([a1], [d65536].End(3))
For I = 1 To UBound(ar)
d(ar(I, 1) & ar(I, 2) & ar(I, 3)) = d(ar(I, 1) & ar(I, 2) & ar(I, 3)) + 1
If ar(I, 1) <> 0 And Not e.exists(ar(I, 1) & ar(I, 2) & ar(I, 3)) Then e(ar(I, 1) & ar(I, 2) & ar(I, 3)) = I
Next
k = d.keys
flag = vbRed
For I = 0 To UBound(k)
If d(k(I)) > 1 And e.exists(k(I)) Then
If flag = vbRed Then flag = vbBlue Else flag = vbRed
Cells(e(k(I)), 1).Resize(d(k(I)), 4).Interior.Color = flag
End If
Next
End Sub
九、查不重复 =IF(COUNTIF(A:A,A1)=1,"仅此一个","")
十、按人名排序后，设置相同的颜色
閱讀更多 淡墨留餘香 的文章
