请教高手，excel两列比对找相同或不同

请教高手，excel两列比对找相同或不同

C1中公式为 =IF(COUNTIF($B$1:$B$6,A1)=0,0,"")
D1中公式为 =IF(COUNTIF($A$1:$A$6,B1)=0,1,"")
E1中公式为 =C1+D1

选中C1:E1，向下填充到“秦二”那行为止。E列中就是你想要的结果

1.工具－－宏－录制新宏－确定。

2.在宏窗口上“停止录制”

3.工具－宏－宏,点击macro1－编辑，进入一个窗口，把里面的内容全部删除，粘贴下面代码：

Sub 高显两列中的不同数据1()
On Error GoTo MyErr
Dim MyRan As Range
Set MyRan = Selection
If MyRan.Areas.Count <> 2 Then
MsgBox "选择的不是两列", vbExclamation
Exit Sub
End If
Dim i As Integer, j As Integer, IsRept As Boolean, m As Integer, n As Integer, k As Integer
IsRept = False
Dim AllMatch As Boolean
AllMatch = True
Dim MatchNum As Integer
Dim MyStr As String
Dim MyStrTemp() As String
Dim MystrNum As Integer
For k = 1 To 2
If k = 1 Then '用于交换选中的两个区域
m = 1
n = 2
Else
m = 2
n = 1
End If

For i = 1 To MyRan.Areas(m).Rows.Count
MatchNum = 0
For j = 1 To MyRan.Areas(n).Rows.Count
If MyRan.Areas(m).Cells(i, 1).Value = MyRan.Areas(n).Cells(j, 1).Value Then
ReDim Preserve MyStrTemp(MatchNum)
MyStrTemp(MatchNum) = MyRan.Areas(n).Cells(j, 1).Address
MatchNum = MatchNum + 1
End If
Next j
Select Case MatchNum
Case 0
MyRan.Areas(m).Cells(i, 1).Interior.Color = RGB(255, 255, 100)
AllMatch = False
Case Is > 1
IsRept = True
For MystrNum = LBound(MyStrTemp) To UBound(MyStrTemp)
MyStr = MyStr & MyStrTemp(MystrNum) & "—"
Next MystrNum
MyStr = Left(MyStr, Len(MyStr) - 1) & vbCrLf
End Select
Next i
Next k
If AllMatch = True And IsRept = False Then MsgBox "一一对应!"
If IsRept = True Then MsgBox "某列有重复项!" & vbCrLf & MyStr
Exit Sub
MyErr:
MsgBox Err.Description
End Sub

4.选中每一列，按ctrl，再选另一列，工具－宏－宏，双击 “高显两列中的不同数据1”

不知对你有用吗？

在C列输入=if（A1=B1,1,0）,然后复制公式到其他行

用数组公式
=countif(A:A,B:B)
ctrl+Shift+Enter