一、只要重複，則顯示 =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,"僅此一個","")
十、按人名排序後，設置相同的顏色
閱讀更多 淡墨留餘香 的文章
