楼主
wcymiss |
Q：有两列数据，如何分别找出A列有B列无、B列有A列无以及两列都有的数据？
A：方法一、自定义序列
步骤：1、工具-选项-自定义序列里，将A列数据导入，然后对B列数据进行排序，“选项-自定义排序次序”里选择刚自定义的序列“A列”。
2、工具-选项-自定义序列里，将刚排序好的B列数据导入，然后对A列数据进行排序，“选项-自定义排序次序”里选择刚自定义的序列“B列”。
3、此时A、B两列上半部分相同数据即为两列都有的数据，下半部分不同的，分别为A列有B列无、B列有A列无的数据，复制粘贴到需要的地方即可。
注意：自定义序列最大只能适合255条数据。
 两列比较.rar
|
2楼
wcymiss |
方法二、高级筛选。
步骤，在F2输入公式：- =countif(b:b,a2)=0
，然后数据-筛选-高级筛选，如下图填入相关内容：
按确定后，C列数据即为A列有B列无的数据。
同上类似操作，公式改为：- =COUNTIF(A:A,B2)=0
，可得B列有A列无的数据。
公式改为：- =COUNTIF(A:A,B2)
，可得两列都有的数据。 |
3楼
wcymiss |
方法三：普通公式
C2公式：- =IF(COUNTIF(B:B,A2),"",A2)
或- =IF(ISNA(VLOOKUP(A2,B:B,1,)),A2,"")
然后下拉。
D2公式：- =IF(COUNTIF(A:A,B2),"",B2)
或- =IF(ISNA(VLOOKUP(B2,A:A,1,)),B2,"")
然后下拉。
E2公式：- =IF(COUNTIF(B:B,A2),A2,"")
或- =IF(ISNA(VLOOKUP(A2,B:B,1,)),"",A2)
然后下拉。
求两列差异的普通公式比较多，但公式结果都包含了空单元格，需要结合自动筛选删除空单元格才能得到所需数据。 |
4楼
wcymiss |
方法四：数组公式，需要按三键回车然后下拉。
C2公式：- =INDEX(A:A,SMALL(IF(COUNTIF(B$2:B$105,A$2:A$187),4^8,ROW($2:$187)),ROW(A1)))&""
D2公式：- =INDEX(B:B,SMALL(IF(COUNTIF(A$2:A$187,B$2:B$105),4^8,ROW($2:$105)),ROW(A1)))&""
E2公式：- =INDEX(B:B,SMALL(IF(COUNTIF(A$2:A$187,B$2:B$105),ROW($2:$105),4^8),ROW(A1)))&""
数组公式产生的结果和普通公式相比，没有空行，不需要进一步处理。但数组公式只适合数据量比较少的情况。数据量大的话数组公式运行相当缓慢，不建议使用。 |
5楼
wcymiss |
方法五：vba代码
- Sub A列有B列无()
- Dim d, arr, brr, i&, r1&, r2&, j&, crr()
- Set d = CreateObject("scripting.dictionary")
- r1 = ActiveSheet.Cells(Rows.Count, 1).End(3).Row '取得A列最后数据单元格行号
- r2 = ActiveSheet.Cells(Rows.Count, 2).End(3).Row '取得B列最后数据单元格行号
- arr = Range("a2:a" & r1) '将A列数据导入数组
- brr = Range("b2:b" & r2) '将B列数据导入数组
- For i = 1 To UBound(brr)
- d(brr(i, 1)) = i '将B列输入装入字典
- Next
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then '如果A列的数据在字典中不存在（也就是在B列中不存在），则
- j = j + 1 '增加数组crr个数
- ReDim Preserve crr(1 To 1, 1 To j) '重定义crr数组
- crr(1, j) = arr(i, 1) '将A字典中不存在数据，存入数组crr
- End If
- Next
- ActiveSheet.Range("c2").Resize(Rows.Count - 1, 1).ClearContents '清除C列
- ActiveSheet.Range("c2").Resize(j, 1) = Application.Transpose(crr) '将数组crr拷贝至C列
- Set d = Nothing
- End Sub
- Sub B列有A列无()
- Dim d, arr, brr, i&, r1&, r2&, j&, crr()
- Set d = CreateObject("scripting.dictionary")
- r1 = ActiveSheet.Cells(Rows.Count, 1).End(3).Row
- r2 = ActiveSheet.Cells(Rows.Count, 2).End(3).Row
- arr = Range("a2:a" & r1)
- brr = Range("b2:b" & r2)
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = i
- Next
- For i = 1 To UBound(brr)
- If Not d.exists(brr(i, 1)) Then
- j = j + 1
- ReDim Preserve crr(1 To 1, 1 To j)
- crr(1, j) = brr(i, 1)
- End If
- Next
- ActiveSheet.Range("d2").Resize(Rows.Count - 1, 1).ClearContents
- ActiveSheet.Range("d2").Resize(j, 1) = Application.Transpose(crr)
- Set d = Nothing
- End Sub
- Sub 两列都有()
- Dim d, arr, brr, i&, r1&, r2&, j&, crr()
- Set d = CreateObject("scripting.dictionary")
- r1 = ActiveSheet.Cells(Rows.Count, 1).End(3).Row
- r2 = ActiveSheet.Cells(Rows.Count, 2).End(3).Row
- arr = Range("a2:a" & r1)
- brr = Range("b2:b" & r2)
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = i
- Next
- For i = 1 To UBound(brr)
- If d.exists(brr(i, 1)) Then
- j = j + 1
- ReDim Preserve crr(1 To 1, 1 To j)
- crr(1, j) = brr(i, 1)
- End If
- Next
- ActiveSheet.Range("e2").Resize(Rows.Count - 1, 1).ClearContents
- ActiveSheet.Range("e2").Resize(j, 1) = Application.Transpose(crr)
- Set d = Nothing
- End Sub
vba的字典十分高效，此法适合对大量数据进行比对。 |
6楼
wcymiss |
方法六：sql法
步骤：1、数据-导入外部数据-导入数据，然后选择本工作簿的本工作表，确定后，在弹出的“导入数据”窗口里，“数据的放置位置”选择C1，点击“编辑查询”，在命令文本中输入语句：
- select A列 as A列有B列无 from [方法六$a1:a187] where A列 not in(select B列 from [方法六$b1:b105])
2、同上操作，“数据的放置位置”选择D1后，点击“编辑查询”，在命令文本中输入语句：
- select B列 as B列有A列无 from [方法六$b1:b105] where B列 not in(select A列 from [方法六$a1:a187])
3、同上操作，“数据的放置位置”选择E1后，点击“编辑查询”，在命令文本中输入语句：
- select A列 as 两列都有 from [方法六$a1:a187] where A列 in (select B列 from [方法六$b1:b105])
|
7楼
huishi |
收下了，谢谢，LZ辛苦了 |
8楼
eliane_lei |
谢谢分享！
|
9楼
洛小乐719 |
好复杂呀。看不懂。 |
10楼
海洋之星 |
学习，学习 |