KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как найти дубликаты или уникальные значения в двух таблицах Excel

АвторСуньДата изменения

При работе с несколькими листами в Excel часто возникает необходимость сравнивать данные — чтобы найти дублирующиеся или уникальные значения, а также выявить различия между двумя листами. Иногда достаточно просто проверить, присутствует ли нужное значение на другом листе, а иногда требуется построчное сравнение соответствующих ячеек.

В этом руководстве мы рассмотрим несколько практических способов сравнения двух листов в Excel: как выделять повторяющиеся или уникальные значения, отмечать совпадения с помощью формул, извлекать совпадающие данные в отдельный список и сравнивать соответствующие ячейки между листами.

Сравнивает, существуют ли значения на другом листе

Это сравнение проверяет, присутствует ли каждое значение с одного листа где-либо на другом листе. Совпадающее значение может находиться в любой строке и любой ячейке — точное совпадение позиций не требуется.

Помечает значения как Дубликаты или Уникальные с помощью вспомогательного столбца

Если вы хотите видеть наглядный результат рядом с каждым значением, используйте вспомогательный столбец. Этот метод возвращает Повторяющееся, если значение присутствует на другом листе, и Уникальное, если его там нет.

  1. Предположим, что значения, которые вы хотите проверить, находятся в Sheet1!A2:A10, а список для сравнения — в Sheet2!A:A.
  2. В Лист1введите следующую формулу в ячейку B2:
    =IF(COUNTIF(Sheet2!A:A,A2)>,0,"Duplicate","Unique")
  3. Перетащите маркер заполнения вниз, чтобы распространить формулу на остальные ячейки.

Каждое значение на Листе 1 теперь помечается как Повторяющееся, если оно присутствует на Листе 2, или как Уникальное, если его там нет.

Пометить значения как дубликаты или уникальные с помощью вспомогательного столбца

📝 Примечание:

Вы также можете воспользоваться функциями MATCH, VLOOKUP или XLOOKUP, чтобы проверить наличие значения на другом листе. Однако если ваша задача — просто определить, встречается значение или нет, COUNTIF обычно проще в использовании.

Выделяет дублирующиеся или уникальные значения между двумя листами

Если вам нужно лишь визуально выделить повторяющиеся или уникальные значения, отличным решением станет условное форматирование с функцией COUNTIF.

  1. Выделите диапазон на первом листе, например Лист1!A2:A10.
  2. Нажмите Главная > Использовать условное форматирование > Создать правило.
  3. В диалоговом окне Создание правила форматирования выберите Использовать формулу для определения форматируемых ячеек.
  4. Введите одну из следующих формул в зависимости от желаемого результата:
    • Чтобы выделить Дублирующиеся значения, введите эту формулу:
      =COUNTIF(Sheet2!A:A,A2)>0
    • Чтобы выделить уникальные значения, используйте эту формулу:
      =COUNTIF(Sheet2!A:A,A2)=0
  5. Нажмите Формат, выберите Цвет заполнения и нажмите ОК.
    Создать новое правило условного форматирования
  6. Снова нажмите ОК, чтобы применить правило.

Теперь совпадающие ячейки будут автоматически выделяться.

Выделить повторяющиеся значения между двумя листами

Выделение и подсветка одинаковых или Разное значение между двумя листами

Kutools для Excel позволяет быстро находить, выделять и подсвечивать повторяющиеся или уникальные значения между двумя листами. Благодаря мгновенному выделению совпадающих или отличающихся ячеек их можно легко копировать, удалять, форматировать или редактировать массово.

Kutools для Excelпредлагает более 300 расширенных функций для упрощения сложных задач, повышая креативность и эффективность.Интегрирован с возможностями ИИ, Kutools автоматизирует задачи с высокой точностью, делая управление данными простым.Подробная информация о Kutools для Excel…         Бесплатная пробная версия…
  1. После установки Kutools для Excel нажмите Kutools > Выделить > Выбрать одинаковые/разные ячейки.
  2. В диалоговом окне Выбрать одинаковые/разные ячейки:
    • В поле Найти значения в выберите диапазон, в котором нужно найти и выделить дубликаты или уникальные значения.
    • В поле Согласно выберите диапазон для сравнения на втором листе.
  3. Выберите Отдельная ячейка в разделе На основе, если хотите сравнивать значения независимо от их расположения.
  4. Выберите Одинаковые значения, чтобы найти дублирующиеся значения, или выберите Разные значения, чтобы найти уникальные.
  5. Установите флажок Заполнить цвет фона, если хотите выделить результаты, затем нажмите ОК.
    Диалоговое окно «Одинаковые и разные ячейки»

Теперь повторяющиеся или уникальные значения будут выделены и подсвечены на первом листе.

Выбрать и выделить одинаковые или разные значения между листами

Kutools для Excel — более 300 незаменимых инструментов для Excel! Выполняйте задачи быстрее, проще и эффективнее.Скачать сейчас!

Извлекает дублирующиеся или уникальные значения в отдельный список

Если вы хотите извлечь повторяющиеся или уникальные значения в отдельный список, а не помечать их в исходных данных, воспользуйтесь функцией ФИЛЬТР.

📝 Примечание:

Функция ФИЛЬТР доступна только в Excel для Microsoft 365 и Excel 2021 или более поздних версиях.

  1. Щелкните по пустой ячейке, куда вы хотите поместить результат.
  2. Чтобы извлечь значения из Лист1, которые также присутствуют в Лист2, введите эту формулу:
    =FILTER(Sheet1!A2:A10,COUNTIF(Sheet2!A:A,Sheet1!A2:A10)>,0)
  3. Нажмите Enter, чтобы получить Дублирующиеся значения.
    Извлечь повторяющиеся значения в отдельный список

💡 Совет:

Чтобы извлечь уникальные значения, отсутствующие на втором листе, используйте эту формулу:

=FILTER(Sheet1!A2:A10,COUNTIF(Sheet2!A:A,Sheet1!A2:A10)=0)

Сравнивает соответствующие ячейки между двумя листами

Этот тип сравнения сопоставляет две таблицы по ячейкам, проверяя, совпадают ли значения в одинаковых позициях или отличаются. Например, Sheet1!A2 сравнивается только с Sheet2!A2.

Помечает соответствующие ячейки как Совпадающие или Разные с помощью вспомогательного столбца

Если вы хотите получить читаемый результат рядом с каждой строкой, используйте формулу ЕСЛИ для возврата значения Совпадение или Различие.

  1. В Листе1 щелкните пустую ячейку рядом с первой строкой, которую нужно сравнить.
  2. Введите следующую формулу:
    =IF(A2=Sheet2!A2,"Match","Different")
  3. Перетащите маркер заполнения вниз, чтобы сравнить остальные соответствующие ячейки.
    Пометить соответствующие ячейки как «Совпадение» или «Различие»

📝 Примечание:

Если вам нужны только результаты ИСТИНА или ЛОЖЬ, используйте эту более короткую формулу:

=A2=Sheet2!A2

Выделяет совпадающие или разные соответствующие ячейки

Вы также можете использовать условное форматирование, чтобы сравнить соответствующие ячейки в двух таблицах и выделить либо совпадающие, либо отличающиеся ячейки.

  1. Выделите диапазон на первом листе, например Лист1!A2:A10.
  2. Нажмите Главная>Использовать условное форматирование>Создать правило.
  3. В диалоговом окне Создание правила форматированиявыберите Использовать формулу для определения форматируемых ячеек.
  4. Введите одну из следующих формул в зависимости от желаемого результата:
    • Чтобы выделить ячейки, отличающиеся от соответствующих ячеек на другом листе, введите:
      =A2<>Sheet2!A2
    • Чтобы выделить ячейки, совпадающие с соответствующими ячейками на другом листе, введите:
      =A2=Sheet2!A2
  5. Нажмите Формат, выберите Цвет заполнения и нажмите ОК.
    Создать правило условного форматирования для сравнения соответствующих ячеек
  6. Снова нажмите ОК, чтобы применить правило.

Теперь Excel выделит совпадающие или разные ячейки в выбранном диапазоне на основе использованной вами формулы.

Выделить совпадающие или отличающиеся соответствующие ячейки между двумя листами

Выбирает и выделяет совпадающие или разные соответствующие ячейки

Kutools для Excel позволяет сравнивать две таблицы по ячейкам и мгновенно находить совпадающие или отличающиеся ячейки в одинаковых позициях. В отличие от использования формул или условного форматирования, этот метод сразу даёт возможность выбрать или выделить результаты для последующего редактирования, форматирования или удаления.

Kutools для Excelпредлагает более 300 расширенных функций для упрощения сложных задач, повышая креативность и эффективность.Интегрирован с возможностями ИИ, Kutools автоматизирует задачи с высокой точностью, делая управление данными простым.Подробная информация о Kutools для Excel…         Бесплатная пробная версия…
  1. После установки Kutools для Excel нажмите Kutools > Сравнить ячейки.
  2. В диалоговом окне Сравнить ячейки:
    • В поле Найти значения в (Диапазон A) выберите диапазон, в котором нужно найти совпадающие или разные ячейки.
    • В поле Согласно (Ссылочный диапазон) выберите соответствующий диапазон сравнения на втором листе.
  3. В разделе Найтивыберите один из следующих вариантов в зависимости от желаемого результата:
    • Одинаковые ячейки: Найдите совпадающие соответствующие ячейки.
    • Разные ячейки: Найдите различающиеся соответствующие ячейки.
  4. В разделе Обработка выбранных результатовукажите, следует ли заполнять Цвет фона или Цвет шрифта для результатов, затем нажмите ОК.
    Диалоговое окно «Сравнить ячейки» в Kutools for Excel

Теперь соответствующие ячейки, совпадающие или различающиеся, будут выбраны и выделены в первой таблице.

Выбрать и выделить совпадающие или отличающиеся соответствующие ячейки между двумя листами

Часто задаваемые вопросы

В чём разница между сравнением значений и сравнением соответствующих ячеек?

Сравнение значений проверяет, присутствует ли значение с одного листа где-либо на другом листе, независимо от его расположения. Сравнение соответствующих ячеек определяет, идентичны ли ячейки в одинаковых позициях — например, A2 и Лист2!A2.

Будут ли формулы автоматически обновляться при изменении данных на листе?

Да. Методы на основе формул, такие как COUNTIF, ЕСЛИ и ФИЛЬТР, автоматически обновляются при изменении исходных данных.

Можно ли автоматически выделять дублирующиеся значения между двумя листами?

Да. Вы можете использовать условное форматирование вместе с функцией COUNTIF, чтобы автоматически выделять повторяющиеся или уникальные значения между двумя листами.

В чём разница между функциями «Выбрать одинаковые/разные ячейки» и «Сравнить ячейки» в Kutools для Excel?

«Выбрать одинаковые/разные ячейки» проверяет, присутствуют ли значения из одного диапазона в другом, независимо от их расположения. «Сравнить ячейки» выполняет сравнение значений в одинаковых позициях — построчно или по отдельным ячейкам.

Почему мои соответствующие ячейки отмечаются как разные, хотя выглядят одинаково?

Это может происходить, если ячейки содержат лишние пробелы, скрытые символы, различаются числовыми форматами или регистром текста.

Можно ли сравнивать листы из разных книг?

Да. Большинство формул, правил условного форматирования и функций Kutools могут сравнивать диапазоны из разных книг.

Можно ли сравнить целые листы из разных книг и автоматически выделить все различия?

Да. Kutools для Excel предоставляет функцию Сравнить листы, которая позволяет одновременно сравнить два листа и автоматически выделить разные ячейки, ячейки, присутствующие только в основном листе, и ячейки, присутствующие только в листe для поиска.

Диалоговое окно «Сравнить листы» в Kutools for Excel

Заключение

Сравнение данных между таблицами помогает быстро определить Дублирующиеся значения, уникальные значения, совпадающие ячейки и различия между двумя наборами данных. В зависимости от ваших задач вы можете пометить результаты вспомогательными столбцами, визуально выделить их, выбрать для последующего редактирования или извлечь в отдельный список.

Важно также различать проверку наличия значений в другой таблице и поэлементное сравнение соответствующих ячеек, поскольку эти два типа сравнений служат разным целям и дают разные результаты.

Выбрав подходящий метод сравнения для вашей ситуации, вы сможете точнее выявлять различия между таблицами и эффективнее работать с большими наборами данных.

Надеемся, что это руководство оказалось для вас полезным! Хотите больше советов и практических решений по Excel? Нажмите сюда, чтобы ознакомиться со всей нашей коллекцией обучающих материалов.