如何在Excel中比较两列(对于匹配和差异)

观看视频-在Excel中比较两列的匹配和差异

我得到了很多的一个查询是 - “如何在Excel中比较两列?'。

这可以通过许多不同的方式来实现,使用的方法取决于数据结构和用户想要从中得到什么。

例如,您可能希望比较两列,并查找或突出显示所有匹配的数据点(在这两列中),或只显示不同点(数据点在一列中而不在另一列中),等等。

由于我得到了这么多的问题,我决定用意图编写这个大量的教程,以涵盖大多数(如果不是全部)可能的情况。

如果您发现这种有用,请将其传递给其他Excel用户。

请注意,要比较本教程中所示列的技术不是唯一的技术。

基于您的数据集,您可能需要更改或调整方法。但是,基本原则保持不变。

如果您认为可以添加此教程的内容,请在评论部分中告诉我

比较两个列以确切行匹配

这个是最简单的比较形式。在这种情况下,您需要按行比较执行行,并标识哪个行具有相同的数据,哪些行不会。

示例:比较同一行中的单元格

下面是一个数据集,其中我需要检查a列中的列中的名称是否在B列中的相同。

比较列 - 行按行 -  DataSet

如果有匹配项,我需要结果为“true”,如果不匹配,那么我需要结果为“false”。

下面的公式可以这样做:

= A2 = B2

Excel中的比较列表-匹配显示为TRUE

示例:比较同一行中的单元格(使用IF公式)

如果您想获得更具描述性的结果,可以使用一个简单的公式当名称不同时返回“匹配”并“不匹配”。

= if(a2 = b2,“匹配”,“mismatch”)

如果配方以比较Excel中的列

注意:如果要使比较区敏感,请使用以下IF公式:

= if(精确(a2,b2),“匹配”,“不匹配”)

使用上面的公式,“IBM”和“IBM”将被认为是两个不同的名称,上面的公式将返回“Mismatch”。

示例:突出显示匹配数据的行

如果要突出显示具有匹配数据的行(而不是在单独列中获取结果),则可以使用条件格式

以下是执行此操作的步骤:

  1. 选择整个数据集。
  2. 单击“主页”选项卡。单击Excel Ribbon中的主页选项卡
  3. 在样式组中,单击“条件格式”选项。单击条件格式
  4. 从下拉下来,单击“新规则”。单击“新建规则”选项
  5. 在“新格式规则”对话框中,单击“使用公式”以确定要格式化的单元格。单击“使用公式”选项
  6. 在公式字段中输入公式:=$A1=$B1用于比较条件格式化列的公式
  7. 单击“格式”按钮并指定要应用于匹配单元格的格式。设置条件格式的格式化
  8. 单击确定。

这将突出显示每行中名称相同的所有单元格。

比较两列并突出显示匹配的行

比较两列和突出显示匹配

如果要比较两个列和突出显示匹配数据,可以在条件格式中使用重复的功能。

请注意,这与每行比较时所见的不同。在这种情况下,我们不会通过行比较执行一行。

示例:比较两个列和突出显示匹配数据

通常,您将获得有匹配项的数据集,但这些可能不在同一行中。

如下图所示:

比较两列并突出显示macthes - dataset

请注意,A列中的列表比B中的一个大于一个。在列表中也有一些名称,但不在同一行中(例如IBM,Adobe,Walmart)。

如果要突出所有匹配的公司名称,可以使用条件格式进行。

以下是执行此操作的步骤:

  1. 选择整个数据集。
  2. 单击“主页”选项卡。
  3. 在样式组中,单击“条件格式”选项。单击条件格式
  4. 将光标悬停在突出显示单元格规则选项上。
  5. 单击重复值。在条件格式中选择重复值
  6. 在“重复值”对话框中,确保选择“重复”。有条件格式的重复
  7. 指定格式。指定条件格式化的格式
  8. 单击确定。

上述步骤将为您提供如下所示的结果。

在Excel中将列表进行比较时突出显示匹配数据

注意:条件格式化重复规则不区分大小写。所以'Apple'和'Apple'被认为是相同的,并将被突出显示为重复。

示例:比较两列并突出显示不匹配的数据

如果您想要突出显示在一个列表中存在的名称而不是另一个列表中的名称,您也可以使用其条件格式。

  1. 选择整个数据集。
  2. 单击“主页”选项卡。
  3. 在样式组中,单击“条件格式”选项。单击条件格式
  4. 将光标悬停在突出显示单元格规则选项上。
  5. 单击重复值。在条件格式中选择重复值
  6. 在“重复值”对话框中,确保选择“唯一”。选择独特的突出显示差异
  7. 指定格式。指定突出显示两列差异的格式
  8. 单击确定。

这将为您提供如下所示的结果。它突出显示与其他列表中不存在的名称的所有单元格。

比较两列并突出显示差异

比较两列并找到缺失的数据点

如果要识别其他列表中存在从一个列表中的数据点是否存在,则需要使用查找公式。

假设您有一个如下所示的数据集,并且您想要识别出现在列a而不是列B中的公司,

比较两列并突出显示macthes - dataset

为此,我可以使用以下内容VLOOKUP公式

= ISERROR(VLOOKUP(A2,$ 2 $ 2:$ b $ 10,1,0))

这个公式使用VLOOKUP函数来检查a中的公司名称是否出现在B列中。如果它存在,它将从列B返回该名称,否则将返回一个#N/ a错误。

返回#n / a错误的这些名称是B列中缺少的名称。

ISError函数如果存在VLookup结果,则返回true是错误,如果不是错误,则为false。

比较列表并找到缺少的数据

如果要获取没有匹配的所有名称的列表,则可以过滤结果列以使所有单元格真实。

您还可以使用匹配功能来做同样的事情;

=不是(Isnumber(匹配(A2,$ 2:$ B $ 10,0)))

注意:我个人更喜欢使用Match函数(或指数/匹配的组合)而不是Vlookup。我发现它更灵活,强大。你可以阅读Vlookup与索引/匹配之间的区别在这里

比较两列并拉动匹配数据

如果您有两个数据集,并且您希望将项目与另一个列表中的项目进行比较并获取匹配数据点,则需要使用查找公式。

示例:提取匹配数据(精确)

例如,在下面的列表中,我想获取第2列的市场估值值。要执行此操作,我需要查找列1中的值,然后获取相应的市场估值值。

比较Excel中的两个列表并获取匹配数据

以下是将执行此操作的公式:

= VLOOKUP(D2,$ 2:$ B $ 14,2,0)

或者

=指数($ 2 $ 2:$ 14,比赛(D2,$ 2,$ 14,0),2)

查找和拉匹配数据-市场估值Excel

示例:拉动匹配数据(部分)

如果您获取数据集,则使用上显示的查找,在两列中的名称中存在次要区别。公式不会上班

这些查找公式需要完全匹配以提供正确的结果。vlookup或vlookup或匹配选项近似匹配选项匹配功能,但这不能在这里使用。

假设您有数据集,如下所示。请注意,列2中没有填写的名称(例如jpmorgan而不是jpmorgan chase和exxon而不是exxonmobil)。

拉动匹配数据-部分匹配

在这种情况下,您可以使用偏见查找通配符

以下公式将给出这种情况的正确结果:

= VLOOKUP(“*”&D2&“*”,$ 2:$ b $ 14,2,0)

或者

=指数($ 2 $ 2:$ 14,匹配(“*”&D2&“*”,$ 2:$ 14,0),2)

带有通配符的列中的部分比较

在上面的示例中,星号(*)是通配符,可以表示任何数量的字符。当查找值在两侧侧翼时,包含列2中的查找值的列1中的任何值都将被视为匹配。

例如,* exxon *将是exxonmobil的匹配(如*可以代表任何数量的字符)。

您也可能喜欢以下Excel提示和教程:

Excel电子书订阅

免费Excel书籍

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作

59关于“如何在Excel中比较两列的思考(对于匹配和差异)”

  1. 需要帮忙。这是我的问题。
    ID Rate ID month利率
    R000034567 00 51287 R000034567 12
    R000034565 00 00 4587 R000034565 6
    R000034562 00 6528 R000034562 8

    试图根据ID填充到最后一列的速率。公式是什么可以比较两个ID列。

  2. 你好,我有另一个关于“比较两列和突出不匹配的数据”的问题。这有助于确定惟一的值在2列A和B,但失败如果假设有两个相似的值列,值不退出列B,它应该突出它,因为它是一个不匹配的列和列B,但它不会这样做。它不应该检查同一列中的重复,因为这不是我的目标。我要严格地比较A列和B列

  3. 我有一个问题,我有一组价值(由记事本分隔的新行),我希望突出显示与这些值匹配的那些列。
    假设我在我的记事本中有新的线路,下面的数字 - ABC,XYZ,MTX,实验室等。
    我有一个列,其中有200个条目,其中一些条目包含高于值。如何突出这些列?

  4. 突出显示在使用重复选项中的不同位置的列中匹配的示例,遗憾的是还在同一列中突出显示重复项,并且不需要在另一列中存在。

  5. 您好,先生,您能帮我找到在一定时期内条目/交易最多的顶级进口商吗?我可以把样品文件发给你吗?谢谢

  6. 有没有办法将数据与“包含类似”镜头进行比较?我希望能够找到像“哈利和儿子”和“哈利和儿子”和“哈利和儿子有限公司”的名字。等等。

  7. 我无法在使用日期时在两列的每行中寻找匹配时准确地完成上述任何操作 - 我可以看到匹配日期,但公式给出负代替正面匹配。有什么帮助解决?

  8. 你好-我一直在寻找,但我认为我还没有找到一个教程为我的用例。我想知道你是否有或者能回答我的问题。我有两套两列匹配的数据(第一组有一组名称和值的日期段1,第二组有一组名称和值的日期段2)但同时一些名字重复在两种数据集也有名字,第一组和第二组所特有的。我希望将两个数据集通用的名称(及其对应的值)放在同一行中,然后在这些通用名称下面列出每个列表惟一的名称。有到这的路吗?

  9. 非常灵通,请分享您的电子邮件,我发送了我的问题寻求帮助。谢谢你保持。此致。

  10. 我真的很喜欢你的简单解释,但仍然找不到编辑我的号码的工具。谢谢

  11. 我有两列。我需要复制与该列相同的名称或不同名称的副本名称
    。请

  12. 我在第一列中有一些值,第二列中的某些值我希望在其他工作表中的其他列中的第一列和第二列中重复的所有值

  13. 我有3列。一个是第一个选项卡,另一个在第二个选项卡上。我希望第一个选项卡上的列在第二个选项卡中找到列中的任何匹配值。如果有一个匹配/多匹配匹配,我希望它可以抓住第二个选项卡上的3列中的值并返回总和。希望这是有道理的,有人可以帮助!

  14. 你绝对是今天的英雄。您的教程向我展示了交叉引用数千个数据点的方法,并在短短几分钟内将我需要的缺失数据拉过来。谢谢你!

  15. 这是一篇伟大的文章,非常感谢。
    许多人不知道人们可以使用SQL语句来访问Excel电子表格。我写了一个名为selectcompare的工具,它促进了数据集的比较,以及其他Excel电子表格。网站上有很多文档如何设置数据连接和写入选择语句。

  16. 我有2个列表:
    1)一个有分数的名字。名称按字母顺序排列。

    2)另一个具有相同的名称。没有得分,名称是小组。不是按字母顺序排列。

    在那里我可以组合两个列表,所以分数出现在名称旁边;不影响团体?

    • 您好,您可以尝试使用VLookup函数以获取所请求名称的分数。

  17. 对于任何严肃的数据分析师来说,这似乎都是一本方便的书

  18. 你们的每个公式我都试过了。没有工作,所以我一定是做错了什么-这不是唯一的问题。

    我在A列中有433个名称,298列B. B列中的所有名称也都在A中,我将所有这些都格式化为相同的拼写,所有姓氏。所以我想要的是a列中的135个名称,它不在B列中出现在C列中C.

    如果Excel无法做到这一点(这将惊讶于我),甚至只是突出显示所有重复项(即两个列中的名称),而不是列A中的唯一名称,也会有所帮助。

    我可以在一列中重复我可以获得最好的,即,它将在每列中突出显示“主教主教”(两个不同的人)中的“主教主教”(两个不同的人)。但无论我所做的事情都无所谓,我无法突出显示“亚当斯”,因为它在两个列中,也不是“亚伯拉罕”,因为它只在A列中A.

    帮助吗?

    • 您可以使用Vlookup或索引/匹配查找B中的那些名称,其中也出现在a中,然后它将返回#n / a的那些未找到的名称。您可以根据适用的方式对/过滤。

  19. 做得好。拯救我们的时间是非常有用的。如果您的可以帮助我参与以下内容也会受到赞赏。
    我想在借贷两栏找到相同的值。如何在excel公式中选择这些值(这只是在excel中更新的对账项)

  20. 谢谢!!在搜索其他地方十分钟后,这确实给了我想知道的内容。

  21. 我需要帮助比较两列的重复和唯一值。我知道我可以使用条件格式来执行此操作,但是我的问题是,例如,在第1列中,我已经重复了,所以当我应用条件格式时,它正在挑选同一列中的重复,但是我只想拿起重复两列。例如。

    第1列可以有:
    红色的
    黄色的
    红色的
    绿色

    第2列可以是:
    黄色的
    蓝色的
    紫色的
    绿色

    当我应用条件格式时,它会选择红色作为重复的,因为它在第一个中出现两次,但只想查找比较两列的重复,所以我希望它拿起:黄色和绿色。

    我怎样才能实现这一目标?

    • 嗨Tricia,我有同样的问题就像你一样。如果现在你知道如何解决它请让我知道。万分感谢!

  22. 我需要帮助来比较我们卖的日常用品。然后我需要把对比价目表放上去。我如何检查Price是否已经在那里,这样我就不需要再做一次?

  23. 非常感谢您的支持,这使我在工作中得到了极大的支持,请分享公式,以从数据分开的重复项。

  24. 就像你读过我的想法,知道这就是我已经在寻找的东西。
    谢谢

      • 非常有用,谢谢Sumit。如果你能协助另一个类似的案子。我需要使用条件形成,如果我想比较两个数字,并使用颜色来区分区别,不管是正的还是负的。例如:如果value是单元格A的值低于单元格B的值,则高亮显示为红色,否则高亮显示为绿色。我需要用这个来强调不同产品线上的销售目标与实际销售之间的差异。

评论被关闭。