【Excel】乱序不同行数的两列数据对比匹配 | 您所在的位置:网站首页 › excel表如何对比两列数据的不同 › 【Excel】乱序不同行数的两列数据对比匹配 |
1 情境
表格需求: 表格特点: [表2:签到表] 为 [表1:总名单] 的子集;两表顺序错乱;不同姓名对应身份证号前十五位有完全相同的情况。注意事项: 匹配数据应以身份证号为索引,且18位数字完全匹配,因为有重名和身份证号多位相同可能性,且部分函数只能处理15位字符串。 2 实现方案 2.1 方法一:countif 函数所需函数: countif(数据列,单元格数据) = 单元格数据在数据列中出现的次数if(判断语句,成立输出,不成立输出)输入如下公式: =IF(COUNTIF($E$3:$E$18,B3&"*")=1,"已签到","未签到")下拉即可,如图: 注意: 使用$绝对引用(快捷键:F4)数据列,防止相对引用下拉变动;countif函数只能识别15位数字,而身份证号有18位,若两组数据前15位相同则输出结果会产生错误,故需在单元格B3后连接通配符*,即B3&"*",强制其以文本形式匹配。 2.2 方法二:vlookup / xlookup 函数所需函数: vlookup(查找值, 查找区域, 返回查找区域的第几列数据, 精确查找输入参数"0"or"false" 或 模糊查找输入参数"1"or"true")#注意第一参数查找值必须位于第二参数查找区域的第一列;匹配索引需格式统一。iferror(条件, 条件错误时的返回值)#这里用于处理vlookup产生的#N/A错误值,使之不影响后续公式运行;这个需求也可由功能更强大的xlookup函数第四参数替代,用法见4。len(条件) = 字符串长度xlookup(【第一参数】查找值(多条件查询用"&"连接),【第二参数】查找区域,【第三参数】值返回区域,【第四参数(可选参数)】无匹配结果返回值(省略则返回#N/A),【第五参数(可选参数)】匹配类型(-1,0,1,2),【第六参数(可选参数)】搜索模式(-2,-1,1,2)) 匹配类型: 0 - 完全匹配。 如果未找到,则返回 #N/A。 这是默认选项。-1 - 完全匹配。 如果没有找到,则返回下一个较小的项。1 - 完全匹配。 如果没有找到,则返回下一个较大的项。2 - 通配符匹配,其中 *, ? 和 ~ 有特殊含义。 搜索模式:1 - 从第一项开始执行搜索。 这是默认选项。-1 - 从最后一项开始执行反向搜索。2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序,将返回无效结果。2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序,将返回无效结果。输入如下公式: =VLOOKUP(A3,D:E,2,0)下拉公式,无数据匹配处即为未签到人,如图: 进一步,考虑利用字符串长度作为限定来添加标签,由 iferror 函数将 #N/A 值赋为0,于是得到公式: =IF(LEN(IFERROR(VLOOKUP(A3,D:E,2,0),0))>1,"已签到","未签到")结果如下: 开始>>筛选>>高级筛选:勾选 选择不重复的记录,选择 在原有区域显示筛选结果 则覆盖原表,选择 将筛选结果复制到其他位置 则生成无重复数据的新表。 首先应当利用3.1.1的方法剔除单表格中的重复项,否则会导致 countif、sumproduct 等函数计数出现问题。 方法一:排序数据量较少情况下可排序后自行比对。 方法二:countif / sumproduct 函数统计一个表格元素在另一表格中出现的次数,从而知晓两个表格的重合项。 方法三:vlookup / xlookup 函数返回值 |
CopyRight 2018-2019 实验室设备网 版权所有 |