Excel如何批量匹配两张不同表格的数据
匹配工作表在职场中的应用非常多,也是一个经典的Excel问题,到底应该如何操作才能比较高效地解决这类问题呢?
工作簿包含两个员工表,部分员工存在于两张表格中,部分员工仅存在于一张表格中,需要快速找到两张表格中不同的员工。
操作
方法一:条件格式法
步骤1:按Ctrl+A组合键选中员工表B,将其数据区域复制到员工表A的下方,注意字段一定要对齐,B13:E22就是员工表B的数据区域,如图3-69所示。
图3-69
步骤2:选中需要匹配的字段,如“员工姓名”,在条件格式中设置“重复值”条件格式,如图3-70所示。
图3-70
步骤3:在弹出的对话框中将“重复”改为“唯一”,并设置显示格式,可以立刻看出两张表的差异在哪里,如图3-71所示。
图3-71
从图3-71中可以看出,员工表A中的Grace在员工表B中是没有的,而员工表B中的jack在员工表A中是没有的。
方法二:VLOOKUP函数法
利用两个表的唯一关键字段(员工编号)来实现两个表的匹配。
步骤1:在员工表A的F3单元格中输入VLOOKUP函数公式=VLOOKUP(B3,员工表B!B$3:B$11,1,0)。然后双击或下拉F3单元格右下角的数据填充柄,如果匹配成功则显示相同的员工编号,如果员工表A的数据在员工表B中没有,则会显示错误,F10单元格中就显示了错误信息,如图3-72所示。
图3-72
步骤2:在员工表B中也要进行与员工表A的VLOOKUP函数匹配,同样可以看到员工表B的F10单元格中的数据在员工表A中是不存在的,如图3-73所示。
图3-73
需要注意的是,由于利用VLOOKUP函数需要对两个表进行分别的对比匹配,所以使用起来相对比较麻烦,而且需要对VLOOKUP函数的使用非常熟悉。
这里需要补充一点,VLOOKUP函数匹配如果不成功,就会出现类似图3-73所示的“#N/A”的错误显示。为了让单元格的显示比较“圆满”,VLOOKUP函数通常与IFERROR函数搭配使用,如图3-74所示。
图3-74
也就是将出错的单元格的内容变成空。IFERROR函数的用法也很简单,如果VLOOKUP函数匹配成功则显示成功的值,如果不成功则显示为空。
VLOOKUP函数的方法是不是复杂了一些?那有没有更简单的方法呢?有,就是前面提到的COUNTIF函数。
方法三:COUNTIF函数法
COUNTIF函数是Excel统计中非常重要的一个单条件统计函数,也是和“重复”相关的非常有名的一个函数。基本上在Excel中需要查找重复数据时,COUNTIF函数会立刻浮出水面,不管是条件格式,还是数据有效性的搭配使用。虽然现在有了COUNTIFS多条件统计功能,但COUNTIF函数依然还有不可替代的位置。
本方法的思路是用员工表A的数据逐个在员工表B中统计个数,如果数据等于或大于1,就表示员工表A的数据在员工表B中是存在的。
在员工表A的F3单元格中输入函数公式=COUNTIF(员工表B!B$3:B$11,B3),然后双击或下拉F3单元格右下角的数据填充柄,如果匹配成功则显示为1,如果员工表A的数据在员工表B中没有,则会显示为0,F10单元格显示为0,如图3-75所示。
图3-75
接下来,在员工表B中也要进行与员工表A的COUNTIF函数匹配。是不是比之前的VLOOKUP函数简单一些呢?COUNTIF函数本身还是很简单的,它原本的作用是为了进行条件统计,但在进行Excel办公处理的时候还是经常用于重复项的统计,比如统计图3-76中B列某数据出现的次数。
图3-76
总结: 本技巧总结了Excel表格匹配问题处理的三大解决思路,这是职场人士必须掌握的技能。