实用Excel技巧分享:三种常用的核对数据方法

时间:2023-05-05
阅读:

想必常用Excel的小伙伴多多少少都会遇到核对数据的问题,平时大家都是怎么做的呢?今天就给大家分享三种最常用的核对数据的方法,赶紧来看看吧!

实用Excel技巧分享:三种常用的核对数据方法

如何核对两列数据的差异是使用Excel的伙伴们经常遇到的一类问题。如下图,左列是全部的订单号,右列是已发货的订单号,要判断两列订单号是否有差异。

实用Excel技巧分享:三种常用的核对数据方法

这类问题说起来很简单,使用VLOOKUP函数或者COUNTIF函数都能解决,但是实际情况比较复杂。两列数据,谁是对比值(查找值或条件值)谁是参照值(查找范围或计数区域),直接影响函数结果所包含的实际意义。

今天老菜鸟就把这些问题做个梳理,希望小伙伴们今后遇到数据核对问题时可以很清楚的解决。

一、使用VLOOKUP函数核对数据

1.C列作为对比值(查找值)

公式=VLOOKUP(C2,A:A,1,0)可以得到下图所示的结果:

实用Excel技巧分享:三种常用的核对数据方法

在这个公式中,查找值(第一参数,即对比值)是C列已发货的订单号,查找范围(第二参数,即参照值)是A列所有的订单号。

结果会出现两种情况:

(1)得到一个订单号(A列的),这种情况说明查找值(C列的订单号)在查找范围(A列)中有对应的数据。就本例而言,表示已发货的订单号在全部订单号的范围中;

(2)得到一个错误值(#N/A),这种情况说明查找值(C列的订单号)在查找范围(A列)中没有对应的数据。就本例而言,表示已发货的订单号不在全部订单号的范围中,是有问题的订单号。需要核查该订单号记录是否有误或者是属于其他机构的订单号了。

2.A列作为对比值(查找值)

公式=VLOOKUP(A2,C:C,1,0) 可以得到下图所示的结果:

实用Excel技巧分享:三种常用的核对数据方法

公式2与公式1的区别在于调换了查找值和查找范围的位置,也就是在C列中查找A列中所对应的订单号,结果同样是两种情况,但是意义却发生了变化。

可以得到订单号的,说明这个订单已经发货(因为在C列有对应的数据);得到错误值的,说明这个订单还没有发货,就需要跟进后续的业务操作了。

通过公式1和公式2的结果对比,需要明白一点,当对比值和参照值发生变化时,结果的意义是完全不一样的。因此,在核对数据前,首先要搞清楚需要解决什么问题,然后才能确定用什么作对比值,用什么作参照值。

拓展应用:

如果想将错误值显示为需要提示的信息,可以使用IFERROR函数予以配合。例如公式1可以修改为:=IFERROR(VLOOKUP(C2,A:A,1,0),"单号有误需核实"),公式2可以修改为:=IFERROR(VLOOKUP(A2,C:C,1,0),"未发货"),结果如图所示:

实用Excel技巧分享:三种常用的核对数据方法

如果想更直接地显示出是否已发货,数据是否正常,可以搭配IF函数和ISERROR函数,公式2可以修改为:=IF(ISERROR(VLOOKUP(A2,C:C,1,0)),"未发货","已发货"),公式1可以修改为:=IF(ISERROR(VLOOKUP(C2,A:A,1,0)),"单号有误需核实","正常"),结果如图:

实用Excel技巧分享:三种常用的核对数据方法

实际上,在核对两列数据的差异时,还可以使用COUNTIF和MATCH两个函数,以下简单说明用这两个函数进行核对的方法。

二、使用COUNTIF核对两列数据

COUNTIF是一个条件计数函数,需要两个参数,格式为COUNTIF(计数区域,条件值)。在本例中,还是有两个公式,分别为:=COUNTIF(A:A,C2)=COUNTIF(C:C,A2),结果如图所示。

实用Excel技巧分享:三种常用的核对数据方法

COUNTIF得到的结果同样有两种,0和大于0的数,表示条件值(第二参数,即对比值)在计数区域(第一参数,即参照值)中出现的次数。结果为0表明没有出现过,与VLOOKUP的#N/A意义相同。

如果要加提示信息,可以结合IF函数实现,对应的公式为:=IF(COUNTIF(A:A,C2)=0,"单号有误需核实","正常")=IF(COUNTIF(C:C,A2)=0,"未发货","已发货"),结果如图所示。

实用Excel技巧分享:三种常用的核对数据方法

三、使用MATCH核对两列数据

MATCH函数的功能是得到查找值在查找区域中的位置序号。函数需要三个参数,格式为:MATCH(查找值,查找区域,查找方式),就本例而言的两个公式是:=MATCH(C2,A:A,0)=MATCH(A2,C:C,0),结果如图所示。

实用Excel技巧分享:三种常用的核对数据方法

MATCH函数的结果与VLOOKUP的类似,区别是VLOOKUP得到的是对应的内容(订单号),而MATCH得到的是订单号所在的行号。

个人以为,在核对两列数据时使用MATCH函数更加方便,不但可以对比两列数据的差异,而且还能得到结果的具体的位置,这在某些时候是很重要的。

MATCH函数结合IFERROR的方法与VLOOKUP完全一致,就不再举例了。

实际上MATCH是一个非常有用的函数,喜欢本篇教程的小可爱记得要多多支持我们哦,后期会为大家介绍更多的实例!

返回顶部
顶部