match函数查找重复值;match函数匹配有重复数据 收藏 0

Excel一键核对数据，快到没朋友

日常工作不时会需要对比数据，查找差异，查找重复值等。有的是对比同一工作表中的数据，有的是对比不同工作表之间的数据。希望接下来介绍的多种Excel数据对比方法，让大家能在不同情况下都能快速完成数据的对比。

第一部分：同一表内数据比较

1.严格比较两列数据是否相同

所谓严格比较就是指数据按位置对应比较。

1）快捷键对比Ctrl+

如下图所示，选中需要对比的两列数据A列和B列，然后按下快捷键Ctrl+，不同的数据B5、B9、B10、B15则会处于选中状态。

2）定位法对比（快捷键F5或Ctrl+G）

以下表为例，框选A列和B列的列标题快速选择两列数据，然后按快捷键F5（或Ctrl+G）即可调出定位窗口，选择定位条件为“行内容差异单元格”，单击“确定”按钮，不同的数据会处于选中状态。

注意：以上两种方法可以快速比对两列数据的差异但均不会区分字母大小写。

3）IF函数对比

（1）不需要区分字母大小写的if函数对比

下表A、B两列都是数字，不存在字母，不需要区分大小写。

可以在C2单元格输入公式=IF(A2=B2,\”相同\”,\”不相同\”)，输入好之后拉动手柄向下拖动，直到本列数据截止，相同不同结果一目了然，如下表。

（2）区分字母大小写的if函数对比

如遇对比数据含字母，并且需要区分大小写，则上述公式不能准确对比。此时可将C2公式更改为=IF(EXACT(A2,B2)=TRUE,\”相同\”,\”不相同\”)，然后下拉填充公式，最终如下图所示。

2.找出两列数据的重复值

1）IF + MATCH函数查找重复值

现在要对下表找出连续两个季度中奖的名单，又有什么方法呢？

其实，就是要通过对比A列与B列，找出重复值。

我们可以用IF+MATCH函数组合公式，在C2单元格输入公式：

=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)),\”\”,A2)

然后下拉复制公式，则可完成查找任务。对比查找结果见下表：

公式解析：

MATCH用于返回要查找的数据A2在区域$B$2:$B$25中的位置。如果查到会返回一个行号（表示有重复），没有查到则返回错误#N/A（表示无重复）。

公式中加入ISERROR函数，用于判断MATCH返回的值是否是个错误#N/A，是错误#N/A则返回TRUE，不是错误#N/A则范围FALSE。

最外围的IF函数，根据ISERROR（MATCH（））是TRUE还是FALSE，返回不同值。如果是TURE（也就是没有重复），则返回空；如果是FALSE，则返回A2。

如果我们要查找出1季度中奖但2季度没有中奖的名单，我们就可以将上述函数公式改成为：=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)), A2, \”\”)。

2）IF+COUNTIF函数查找重复值

下表A、B两列都是客户的姓名，需要找到两列重复的客户名称，并在C列标识出来。

操作方法为在C2单元格输入公式=IF(COUNTIF(A:A,B2)=0,\”\”,B2)，然后下拉完成excel两列数据对比。请看下面演示！

COUNTIF函数是对指定区域中符合指定条件的单元格计数的一个函数。

3）IF+VLOOKUP函数查找重复值

如下表所示，有这样两组员工号。不知道哪些是A、B两组都有的。我们也可以用if+VLOOKUP函数公式来完成比对。

在C2单元格中输入公式：=IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,)),\” \”,A2)，然后下拉复制公式，则可找到Excel两列数据中的重复值。

公式解析：

ISNA函数用于判断值是否为错误值#N/A(即是值不存在),如果是,则返回TRUE;否则返回FALSE。

公式里面需要在查找区域的数据前都加上$符号，固定查找区域。否则在下拉填充的时候，查找区域也会跟着变化，这将会影响查找对比的结果。

应用扩展：用Vlookup找不同

该公式稍作调整即可在找出不同值，或缺少值、错误值（非严格比较，不讲究位置或顺序）。譬如上面的B组是标准数据，要把A组中与B组不同的值找出来，公式可以写成：

=IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,)), A2, \” \”)

第二部分：跨表数据比较

1.严格比较两个表的数据是否相同

当两个格式完全一样的表格进行对比查找差异时，可以采用下方的方法。

1）条件格式法对比两表差异

现以下面两表为例，要比对出哪些数值存在差异并突出显示出来。

首先，先选中一个表，新建规则，并选择“使用公式确定要设置格式的单元格”，然后输入=A9<>A1 , 对相对应的单元格进行判断，判断其是否相等。请看下面演示！

温馨提示：

如果要清除条件格式，先选中要清除格式的单元格区域，依次执行“开始”- “条件格式”–“ 清除规则”–“ 清除所选单元格的规则”（或清除整个工作表的规则）。

2）选择性粘贴法对比两表差异（该法只适合数字的比较）

如下图所示，两表格式相同姓名排序相同，要求快速找出两个表格的数据差异。

复制其中一个数值区域，然后按快捷键Ctrl+Alt+V选择性粘贴，设置为“减”运算，单击“确定”后，非0部分即差异所在。请看下面演示！

此方法只适合快速定位差异数据，看一眼就算的那种，因为会破坏原数据表格。

3）IF函数对比两表差异

如下图所示，表a和表b是格式完全相同的表格，现在要求核对两个表格中的数值是否完全一致，并且要能直观显示差异情况。

操作方法为，新建一个空白工作表，在A1单元格输入公式=IF(表a!A1<>表b!A1, \”表a:\”& 表a!A1&\” vs表b:\”& 表b!A1,\”\”)，然后在区域范围内复制填充公式。请看下面演示！

2.按条件找出两个表数据的差异

1）单条件找出两个表数据的差异

譬如下面是分别由两人汇总的成绩表，表格格式一致，但姓名排序不一样。现在需要对比两张表，核实汇总成绩是否正确。

这类数据核对属于单条件核对。因为是不同人汇总的，所以除了按姓名核对分数外，还需要把姓名对不上的也标出来。我们采用条件格式来完成。

需要建立两个条件格式。

第一个格式：找出姓名差异

（1）选中第2个表姓名栏数据，选择“条件格式”中的“新建规则”，在弹出的对话框中选择“使用公式确定要设置格式的单元格”，然后输入公式=COUNTIF($A$2:$A$10,A14)=0

（2）单击格式按钮，选择一种填充颜色。

确定后我们就完成第一个格式设定。

第二个格式：找出同姓名的分数差异。

（1）选中第2个表中所有分数单元格，新建规则，使用公式确定规则，输入的公式为= =VLOOKUP($A14,$A$1:$I$10,COLUMN(B1),0)-B14

（2）单击格式按钮，选择一种填充颜色。

确定后完成分数核对。总的核对结果如下：

橙色表明“刘小广”这个姓名与另一个表对不上，可能是名字写错了；蓝绿色表明杨文雯的语文分数、何丛良的英语分数、候嫚嫚的语文分数对不上，可能存在错误。

2）多条件找出两个表数据的差异

如下图所示，要求核对两表中同一仓库同一产品的数量差异，结果显示在D列。用什么方法可以完成呢？好头疼呀！

在D15单元格中输入以下公式：

=SUMPRODUCT(($A$3:$A$11=A15)*($B$3:$B$11=B15)*$C$3:$C$11)-C15

然后下拉完成该数值的对比。请看请看！！

别再用Vlookup了，试试index+match组合查询，比它好用太多了

常用函数的第五篇来了！这节我们来学习下index与match函数，这个组合相信很多人都用过，很多喜欢将index+match与Vlookup进行比较，我觉得他们各有优点，但是如果非要选择一个话，我会选择 index+match因为它更加灵活，功能也更加强大！下面我们就来学习下吧

Index函数：根据数据区域中的行列号返回区域中对应的值

语法：=INDEX(array, row_num, [column_num])

如果第一参数仅仅只有一行或者一列，我们就能省略对应的行列标号。

如需下图，我们想要找到武则天的数学成绩

公式：=INDEX(A1:D9,5,3)

在A1:D9这个数据区域中，第五行（武则天）与第三列（数学）它们的交叉处，就是我们需要的结果

语法：=MATCH(lookup_value, lookup_array, [match_type])

如下图，我们想要查找【武则天】这姓名在第一列的位置

公式：=MATCH(F3,A1:A9,0)

结果为5，就表示在A1:A9这一列数据中，【武则天】是在第五个位置的

match函数是查找数据的位置，而index函数它是根据位置来返回对应的数据。所以我们只需要将match函数嵌套在match函数中就能达到数据查询的效果。下面来看下它都能解决哪些问题吧

如下图，我们想要查找张飞的数学成绩

公式：=INDEX(D1:D9,MATCH(G3,A1:A9,0))

这个案例是index+match最常见的使用方法，将第一参数设置为需要返回的结果列，就可以将第三参数省略掉，我们仅仅只需要使用mtach函数来确定数据的位置即可

Index+match的反向查找与常规查询是一样的操作，它既可以查找右侧数据，也能查找左侧数据，非常灵活

如下图，我们想要查找3212这个学号对应的姓名

公式：=INDEX(A1:A9,MATCH(G3,B1:B9,0))

Index+match在进行数据查询的时候，如果遇到重复值也是仅仅只能返回第一个找到结果，这个时候我们就需要再增加一个条件让结果变得唯一。

如下图，我们想要找下【2班韩信】的英语成绩

公式：=INDEX(E2:E10,MATCH(H4&I4,B2:B10&C2:C10,0))

这个公式本质上还是一个index+match的常规查询的格式，关键是match函数的第一跟第二参数

连接后，我们的查找值就变为了【2班韩信】这个值是唯一的，是可以找到对应的结果的

index+match也是可以实现一次查找多行多列数据的，我们只需要在第一个单元格中输入公式，然后向下向右填充即可

公式：=INDEX($B$2:$H$11,MATCH($J3,$B$2:$B$11,0),MATCH(K$2,$B$2:$H$2,0))

现在第一参数是多列数据，所以我们只需要使用2次match函数找到对应的行列号即可

以上就是这一节的全部内容，其实关于index+match还有很多高阶的操作，只不过太难了，不建议大家学，现在新函数层数不穷，很多高阶的函数操作都已经被淘汰掉了，我的宗旨就是：什么简单学什么，怎么方便怎么来！

我是Excel从零到一，关注我，持续分享更多Excel技巧

以上内容在我的专栏中都有讲到

如果你想要学习Excel，提高工作效率

这里↓↓↓

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com