提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

快速比较两个范围,以在Excel中选择/修改重复值或唯一值

通常情况下,您可以使用“条件格式”功能来查找Excel中两个范围之间的差异。 但使用起来太复杂。 Kutools for Excel选择相同和不同的单元格 实用程序可以帮助您根据Excel中的每行或单个单元轻松选择或修改重复值或唯一值。

比较两个范围,并根据每行查找相同或不同的记录

比较两个范围,并根据单个单元格找到相同或不同的单元格

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

点击 Kutools > 选择>选择相同和不同的单元格。 查看截图:

拍摄比较范围001  2 拍摄比较范围02

比较两个范围,并根据每行查找相同或不同的记录

1。 按住 CTRL 键选择您想要比较的两个范围,如下所示(请参见屏幕快照)。 注意:此实用程序将在范围A中选择重复项。您可以在不同的工作表和工作簿中指定范围A和范围B.

拍摄比较范围03

2。 应用此实用程序并在 选择相同和不同的单元格 对话框中,可以指定以下选项:

(1。)如果您没有在Step1中选择范围,则可以单击 按钮来选择 在(范围A)中查找值 根据(范围B) 你想要比较。

(2。)如果数据中有标题,而您想忽略比较,请检查 我的数据有标题 选项。

(3。)选择 每一行 在下面 基于 部分。

(4。)指定 相同的价值 or 不同的价值 查找部分 在两个范围之间搜索重复或唯一的行。

(5)。您可以通过检查来随意填充背景颜色或字体颜色的重复值或唯一值 填充背景颜色 or 填充字体颜色 在下面 处理结果 部分。

(6。)如果你想选择使用范围内的整行,请检查 选择整个行 选项。 检查 区分大小写 选项,如果你想区分大小写比较。

拍摄比较范围04

3。 点击 OK,并选择相同或不同的记录以及如下图所示的彩色:

拍摄比较范围05
拍摄比较范围06

备注:

1. 我的数据有标题 选项会在比较两个范围时忽略标题单元格。

2。 “ 在(范围A)中查找值 根据(范围B) 在比较两个范围时必须具有相同的列数。

3。 在比较两个范围时,此实用程序将在范围A中选择重复项或唯一值。 如果您想在范围B中选择重复项或唯一值,您只需交换两个范围。

4。 您可以在不同的工作表中指定范围A和范围B.


比较两个范围,并根据单个单元格找到相同或不同的单元格

此实用程序还可以帮助您比较两个范围,并根据单个单元格找到相同或不同的单元格值。 请做如下:

1。 按住 CTRL 键选择您想要比较的两个范围,如下所示(请参见屏幕快照)。 注意:此实用程序将在范围A中选择重复项。您可以在不同的工作表和工作簿中指定范围A和范围B.

拍摄比较范围07

2。 在 选择相同和不同的单元格 对话框中,可以指定以下选项:

(1。)如果您没有在Step1中选择范围,则可以单击 按钮来选择 在(范围A)中查找值 根据(范围B) 你想要比较。

(2。)选择 单细胞 在下面 基于 部分。

(3。)指定 相同的价值 or 不同的价值查找部分 在两个范围之间搜索重复或唯一的单元格。

(4)。您可以通过检查来随意填充背景颜色或字体颜色的重复值或唯一值 填充背景颜色 or 填充字体颜色 在下面 处理结果 部分。

(5。)检查 区分大小写 选项,如果你想区分大小写比较

拍摄比较范围08

3。 完成设置后,请点击 Ok,选择基于单细胞的相同或不同细胞并在范围A中着色如下:

拍摄比较范围09
拍摄比较范围10

备注:

1。 在比较两个范围时,此实用程序将在范围A中选择重复项或唯一值。 如果您想在范围B中选择重复项或唯一值,您只需交换两个范围。

2。 您可以在不同的工作表中指定范围A和范围B.


生产力推荐工具
以下工具可以大大节省您的时间和金钱,哪一个适合您?
Office Tab: 在您的Office中使用方便的选项卡,作为Chrome,Firefox和新Internet Explorer的方式。
Kutools for Excel: Excel的300高级函数 2019,2016,2013,2010,2007和Office 365。
Classic Menu for Office: 把熟悉的菜单带回到Office 2007,2010,2013,2016,2019和365,就像它是Office 2000和2003一样。

Kutools for Excel

上述功能只是Kutools for Excel的300强大功能之一。

专为Excel(Office)2019,2016,2013,2010,2007和Office 365而设计。 免费下载并使用60天。

Excel的Kutools屏幕截图

BTN阅读更多 BTN下载 btn购买

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Chris · 1 years ago
    When comparing two columns where the duplicate values in further down a column it doesn't match.
    column A, Column B
    3333, 4444
    222,333
    4444,3222


    I need to match the 4444 with the 4444 in the first column. Is there a way to do this? From your example they need to be right next to each other.
  • To post as a guest, your comment is unpublished.
    Howard Briggs · 2 years ago
    Hi
    I have a a column with multiple names of a person and another columns with a classification rating i.e. Excellent, Very Good,Good, Fair. Poor
    I need to count the number of times a particular person has been awarded Excellent in one column and in the next column Good etc.
    The person appears in the list several times so it needs to allow for more than one entry of the person.

    Many thanks
  • To post as a guest, your comment is unpublished.
    Susanne · 4 years ago
    I haven't bought the product yet, want to try it out first. But I found that I can't use the function Compare Range, the rows are "grey" and do not come up with anything.

    Do I do something wrong or don't I have the correct version for test?
    Rgds
    Susanne
    • To post as a guest, your comment is unpublished.
      Admin_jay · 4 years ago
      [quote name="Susanne"]I haven't bought the product yet, want to try it out first. But I found that I can't use the function Compare Range, the rows are "grey" and do not come up with anything.

      Do I do something wrong or don't I have the correct version for test?
      Rgds
      Susanne[/quote]
      Please try to contact me with more detailed information of your issue at jaychivo#extendoffice.com, please replace # with @.

      Or please try to create some sample data in new workbook and try it out.

      If the problem persists with the sample workbook, please attache it in your email. :-)
  • To post as a guest, your comment is unpublished.
    Jenna · 5 years ago
    Does Kutools have a feature where duplicates from a column are removed, but also case sensitive? Excel has the remove duplicates feature, but isn't case sensitive.

    For example, if I have:
    dog
    doG
    dog

    I want a feature where it only removes "dog", but not the one with the upper case. Does Kutools have such feature?
    Thanks.
    • To post as a guest, your comment is unpublished.
      Admin_jay · 4 years ago
      [quote name="Jenna"]Does Kutools have a feature where duplicates from a column are removed, but also case sensitive? Excel has the remove duplicates feature, but isn't case sensitive.

      For example, if I have:
      dog
      doG
      dog

      I want a feature where it only removes "dog", but not the one with the upper case. Does Kutools have such feature?
      Thanks.[/quote]

      Thanks for your feedback. We will try to enhance our feature in upcoming versions. :-)
  • To post as a guest, your comment is unpublished.
    Raft334 · 5 years ago
    Same with me.....the highlights disappeared when I select any cell on the worksheet
  • To post as a guest, your comment is unpublished.
    Deesale · 5 years ago
    Is it possible to identify and permanently highlight the duplicate cells in 2 columns please?
    • To post as a guest, your comment is unpublished.
      adminjay · 5 years ago
      Yes, you can. You just need to use this utility to select the duplicates and then please apply colors to highlight them. :)
      • To post as a guest, your comment is unpublished.
        nanchita · 5 years ago
        [quote name="adminjay"]Yes, you can. You just need to use this utility to select the duplicates and then please apply colors to highlight them. :)[/quote]
        Is there some special trick to selecting those cells that are highlighted? 'select cells by format' perhaps? As soon as I selected one of the duplicates, all of the highlights disappeared.
      • To post as a guest, your comment is unpublished.
        Deesale · 5 years ago
        Thanks Jay - that works great. Much appreciated

        Deesale