【原】数据对比！从所有名单中提取出未经核酸检测的名单

你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天来分享一个数据对比（查找）的案例。现在有两份名单，一份是所有人的名单，另一份是已经核酸检测过的名单。要求是对比这两份名单，提取出未经核酸检测的名单。

如下图所示，A列数据是所有人的名单，C列数据是已检测过的名单。对比这两列数据，提取出未检测过的名单，结果如E列所示。

-01-

函数法

这个问题实际是从一个全集中，取出一个子集的补集。简单来说，就是在所有的名单中，已知了一部分名单，求剩余部分的名单。

在E2单元格输入下面的公式，按ctrl+shift+enter三键，下拉填充。

=INDEX(A:A,SMALL(IF(COUNTIF(C$2:C$21,A$2:A$21),4^8,ROW($2:$21)),ROW(A1)))&""

COUNTIF(C$2:C$21,A$2:A$21)这部分是重点，在C2:C21中统计出A2:A21中每个人的个数，如果A2:A21中的人在C2:C21中出现，那么返回1，没有出现返回0。结果为{1;0;0;0;1;1;1;1;1;0;1;1;1;1;1;1;0;0;0;0}。如下图B列所示。

1对应的那些名字是已检测过的名单，因为它们在已检测名单中出现过。0对应的那些名字是未经检测过的名单，因为它们在已检测名单中没有出现过。

countif两个区域的顺序一定要搞清楚，是在已检测名单中统计所有名单上每个人的个数。

IF(COUNTIF(C$2:C$21,A$2:A$21),4^8,ROW($2:$21))这部分用if函数判断，如果是已检测过的名单（数字为1的），那么返回4^8；否则，如果是未经检测过的名单（数字为0的），那么返回相应的行号。结果如下图B列所示。

接下来，用small函数从if函数的结果中提取出第1个最小值3，当公式向下填充时，依次提取第2，3，……个最小值，如下图E列所示。这样就把未经检测的名单行号从小到大依次提取出来 。

最后，用index函数返回A列对应行的姓名，也就是我们最开始的那个公式。公式后&""是为了把空单元格的0变为空文本。没有&""结果如下，会出现0。

-02-

vba代码法

这几天在学习字典（dictionary），所以就来分享下代码的方法。为了和函数的结果对比，将代码的结果输出到F列。点一下按钮结果就出来了。当已检测名单改变后，再重新点一下按钮，结果就出来了。

代码如下：

Sub 未检测名单() Dim d As Object Dim arr, brr, i, j, n, crr() Set d = CreateObject("scripting.dictionary") '创建字典对象，赋给对象变量d arr = Range("c2", Cells(Rows.Count, "c").End(xlUp)) '将C2到C列最后一个非空单元格赋给数组变量arr For i = 1 To UBound(arr) d(arr(i, 1)) = "" '将arr的元素(C列名单)循环装入字典 Next i brr = Range("a2", Cells(Rows.Count, "a").End(xlUp)) '将A2到A列最后一个非空单元格赋给数组变量brr ReDim Preserve crr(1 To UBound(brr), 1 To 1) '重定义结果数组crr的大小，使其和brr大小一样 For j = 1 To UBound(brr) If Not d.Exists(brr(j, 1)) Then '如果brr的元素(A列名单)在字典中不存在，则... n = n + 1 '未检测人数累加1 crr(n, 1) = brr(j, 1) '将brr的当前元素写入crr相应的位置 End If Next j Range("f2:f9999").ClearContents '清空f2:f9999的内容 If n > 0 Then Range("f2").Resize(n) = crr '如果未经检测人数大于0，则将crr输出到F列 Set d = Nothing '释放字典End Sub

链接：

https://pan.baidu.com/s/1gMrgYkkyjcRsh43Pk9MnTQ

提取码：qcpo