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

Сравнение двух диапазонов в Excel: поиск совпадающих и несовпадающих значений

АвторАманда ЛиДата изменения

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

Несовпадающие значения в диапазоне данных выделены на основе эталонного диапазона
Несовпадающие значения выделены в диапазоне Диапазон данных после сравнения с эталонным диапазоном

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

В этом руководстве мы рассмотрим оба метода, чтобы вы могли выбрать тот, что лучше всего подходит для вашего рабочего листа и рабочего процесса.

Поиск совпадающих или несовпадающих значений с помощью Использовать условное форматирование

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

Шаг 1: Подготовка рабочего листа

Подготовьте рабочий лист со следующим:

  • Диапазон данных, содержащий значения для проверки, например A1:C7.
  • Эталонный диапазон, содержащий значения для сравнения, например E1:E8.
    Лист с диапазоном данных и эталонным диапазоном

    Примечание:

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

Шаг 2: Создание правила для поиска совпадающих значений

  1. Выделите диапазон, содержащий значения для сравнения, например A2:C7.
  2. Щелкните Главная>Использовать условное форматирование>Создать правило.
    Параметр «Новое правило» на вкладке «Главная» ленты
  3. Выберите Использовать форму для определения форматируемых ячеек.
  4. Введите следующую формулу:
    =COUNTIF($E$2:$E$8,A2)>0

    Примечание:

    В приведённой выше формуле $E$2:$E$8 — это эталонный список, а A2 — первая ячейка в выбранном диапазоне. Настройте ссылки на диапазоны в соответствии с вашим листом.

  5. Щелкните Формат, выберите нужный Цвет заполнения и нажмите OK.
    Диалоговое окно «Создание правила форматирования»
  6. Щелкните OK еще раз, чтобы применить правило.

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

Совпадающие значения в диапазоне данных выделены на основе эталонного диапазона

Шаг 3: Создание правила для поиска несовпадающих значений

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

=COUNTIF($E$2:$E$8,A2)=0

Примените её тем же способом с помощью функции «Условное форматирование».

Несовпадающие значения в диапазоне данных выделены на основе эталонного диапазона

Примечание:

Функция СЧЁТЕСЛИ проверяет все ячейки в эталонном диапазоне на точное совпадение. Чтобы выполнить частичное совпадение, измените формулу и используйте такие функции, как ПОИСК, вместо точного сопоставления. Например, приведённая ниже формула выделяет ячейку, если она содержит любой текст из эталонного списка.

=SUMPRODUCT(--($E$2:$E$8<,>,""),--ISNUMBER(SEARCH($E$2:$E$8,A2)))>0

Преимущества

  • Встроено в Excel, дополнительные надстройки не требуются
  • Обеспечивает гибкое сопоставление благодаря возможности настройки формулы под ваши данные
  • Подходит для быстрой визуальной проверки

Недостатки

  • Требует ручной настройки формул
  • Менее удобно для пользователей, незнакомых с формулами Использовать условное форматирование

Выбор и выделение совпадающих или несовпадающих значений с помощью Kutools для Excel

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

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

Шаг 1: Открытие функции Выбрать одинаковые/разные ячейки

После установки Kutools для Excel щелкните Kutools > Выделить > Выбрать одинаковые/разные ячейки.

Шаг 2: Настройка параметров для поиска совпадающих или несовпадающих значений

  1. Выделите первый диапазон со значениями для сравнения в поле Найти значения в.
  2. Выберите диапазон для сравнения в поле Согласно.
  3. Выберите Отдельная ячейка в разделе На основе.
  4. Выберите Одинаковые значения, чтобы найти совпадения, или Разные значения, чтобы выявить несовпадающие ячейки.
  5. Укажите Цвет заполнения или Цвет шрифта, чтобы результаты сразу выделялись.
  6. Щелкните OK.
    Диалоговое окно «Выделить одинаковые и разные ячейки»

Kutools немедленно выделит и, при необходимости, подсветит ячейки, значения которых совпадают или не совпадают с заданным диапазоном для сравнения.

Диалоговое окно «Выделить одинаковые и разные ячейки»
Несовпадающие значения выделены и подсвечены с помощью настройки «Разное значение»

Примечания:

  • Этот метод предназначен для точных совпадений. Если ячейка содержит более длинный текст, и только его часть совпадает со значением в диапазоне сравнения, такое совпадение не будет учтено.
  • И Выберите диапазон, и диапазон сравнения могут содержать один или несколько столбцов.
  • Вы можете включить параметр С учетом регистра, чтобы сделать сравнение чувствительным к прописным и строчным буквам. Например, Excel и excel будут считаться разными значениями.

Преимущества

  • Формулы не требуются
  • Позволяет напрямую выбирать совпадающие или несовпадающие результаты для дальнейших действий
  • Поддерживает сравнение как по отдельным ячейкам, так и по строкам
  • Обеспечивает более быстрый и наглядный способ сравнения диапазонов

Недостатки

  • Требуется Kutools для Excel
  • Работает с точным совпадением ячеек, а не с частичным совпадением текста

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

Какой метод подходит вам лучше всего?

МетодЛучше всего подходит дляОграничения
Использовать условное форматированиеВыделение совпадающих или несовпадающих значений с помощью встроенной функции ExcelТребует использования формул и ручной настройки
Kutools для ExcelБыстрый выбор совпадающих или несовпадающих ячеек или строк для дальнейших действий без написания формулТребуется Kutools для Excel Загрузить

Заключение

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

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

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