【Excel】乱序不同行数的两列数据对比匹配

1 情境

表格需求：

以下两个表格分别为总名单和签到表，需在总名单中找到未签到人员。

表格特点：

1. [表2：签到表] 为 [表1：总名单] 的子集；
2. 两表行数不同且顺序错乱；
3. 有重名情况，不同姓名对应身份证号前十五位有完全相同的情况。

注意事项：
因为有重名和身份证号多位相同可能性，且部分函数只能处理15位字符串，故匹配数据必须以身份证号为索引，18位数字完全匹配。

2 实现方案

2.1 方法一：countif 函数

所需函数：

1. countif(数据列，单元格数据) = 单元格数据在数据列中出现的次数
2. if(判断语句，成立输出内容，不成立输出内容)

输入如下公式：

=IF(COUNTIF($E$3:$E$18,B3&"*")=1,"是","否")

下拉即可，如图：

注意：

1. 使用$绝对引用数据列，防止相对引用下拉变动；
2. countif函数只能识别15位数字，而身份证号有18位，若两组数据前15位相同则输出结果会产生错误，故需在单元格B3后连接通配符*，即B3&"*"，强制其以文本形式匹配。

2.2 方法二：vlookup 函数

所需函数：

1. vlookup(【第一参数】查找值, 【第二参数】查找区域, 【第三参数】返回查找区域的第几列数据, 【第四参数】精确查找输入参数"0"/"false" 或 模糊查找输入参数"1"/"true")
   #注意：【第一参数】查找值必须位于【第二参数】查找区域的左侧列；匹配索引需格式统一。
2. iferror(条件, 条件错误时的返回值)
   #这里用于处理vlookup产生的#N/A错误值，使之不影响后续公式运行；这个需求也可由功能更强大的xlookup函数第四参数替代。
3. len(条件) = 字符串长度

输入如下公式：

=VLOOKUP(B3,$E$3:$E$15,1,0)

下拉公式，无数据匹配处即为未签到人，如图：

进一步，考虑利用字符串长度作为限定来添加标签，由 iferror 函数将 #N/A 值赋为0，于是得到公式：

=IF(LEN(IFERROR(VLOOKUP(B3,$E$3:$E$15,1,0),0))>1,"是","否")

结果如下：

2.3 方法三：xlookup 函数

xlookup(【第一参数】查找值(多条件查询用"&"连接)，【第二参数】查找区域，【第三参数】值返回区域，【第四参数(可选参数)】无匹配结果返回值(省略则返回#N/A)，【第五参数(可选参数)】匹配类型(-1,0,1,2)，【第六参数(可选参数)】搜索模式(-2,-1,1,2))

公式如下：

=XLOOKUP(B3,E:E,B:B,"否")

进一步添加标签思路同vlookup。

3 相关情境

3.1 重复项问题

3.1.1 单表格查找重复项

方法一：高级筛选

开始>>筛选>>高级筛选：勾选 选择不重复的记录，选择 在原有区域显示筛选结果 则覆盖原表，选择 将筛选结果复制到其他位置 则生成无重复数据的新表。

方法二：条件格式

3.1.2 双表格对比重复项

首先应当利用3.1.1的方法剔除单表格中的重复项，否则会导致 countif、sumproduct 等函数计数出现问题。

方法一：排序

数据量较少情况下可排序后自行比对。

方法二：countif / sumproduct 函数

统计一个表格元素在另一表格中出现的次数，从而知晓两个表格的重合项。

方法三：vlookup / xlookup 函数

返回值