Как выделить строки, которых нет в другом списке в Excel: 3 способа

Частая задача в Excel: есть два списка — например, актуальные клиенты из CRM и прошлый выгрузочный список. Нужно быстро найти строки, которых нет в другом списке: кого не пригласили в рассылку, какие артикулы пропали из прайса, какие сотрудники не попали в новый отчёт.

В этой статье разберём 3 удобных способа, как сравнить два списка в Excel и подсветить строки, которых нет во втором списке:

• через условное форматирование с формулой;
• через вспомогательный столбец с флагом «нет в другом списке»;
• через динамический список с функцией ФИЛЬТР (Excel 365) и альтернативу для старых версий.

Все примеры подойдут для типичных задач: сравнение двух выгрузок, поиск пропавших артикулов, контроль списков e-mail и т.д. Если ты ещё не знаком с базовыми приёмами сравнения, загляни в статью о сравнении двух столбцов в Excel .

Исходные данные: что мы сравниваем

Представим, что у нас есть:

• Список 1 — актуальные клиенты (столбец A);
• Список 2 — клиенты, которые уже есть в другой системе (столбец E).

Задача: подсветить или отфильтровать тех клиентов из столбца A, которых нет во втором списке (столбец E). Логика такая же и для артикулов, e-mail, кодов, ФИО и других идентификаторов.

В примерах диапазоны будут такие:

• Список 1: A2:A100;
• Список 2: E2:E100.

Перед началом сравнения полезно привести данные в порядок: убрать лишние пробелы, невидимые символы, дубликаты. Для этого можно использовать статьи: как убрать лишние пробелы в Excel и как безопасно удалить дубликаты .

Способ 1. Условное форматирование: подсветить строки, которых нет в другом списке

Это быстрый визуальный способ: мы подсвечиваем цветом только те значения из первого списка, которых нет во втором. Работает через условное форматирование и функцию СЧЁТЕСЛИ.

Шаг 1. Выделяем первый список

Выдели диапазон со списком, который нужно проверить, например A2:A100.

Шаг 2. Создаём правило формата с формулой

1. На вкладке «Главная» нажми «Условное форматирование» → «Создать правило…».
2. Выбери вариант «Использовать формулу для определения форматируемых ячеек».
3. В поле формулы введи:
   =СЧЁТЕСЛИ($E$2:$E$100; A2)=0

Логика:

• СЧЁТЕСЛИ($E$2:$E$100; A2) — считает, сколько раз значение из A2 встречается во втором списке;
• =0 — значит, такого значения во втором списке нет;
• правило срабатывает только для тех строк, которых нет во втором списке.

Шаг 3. Задаём формат

Нажми «Формат…» и выбери заливку (например, мягкий жёлтый) или цвет шрифта. Подтверди правило. В результате подсветятся только те значения из первого списка, которых нет во втором.

Плюсы и минусы способа

• Плюсы: быстро, наглядно, подходит для проверок «на глаз» и презентации коллегам.
• Минусы: нет отдельного флага, сложнее фильтровать; правило может быть «невидимым» для новичков.

Способ 2. Вспомогательный столбец с флагом «нет в другом списке»

Этот вариант чуть длиннее, зато:

• формула видна прямо в таблице;
• можно фильтровать по флагу;
• легко объяснить коллегам, что здесь происходит.

Шаг 1. Добавляем вспомогательный столбец

Допустим, в столбце B рядом с первым списком будет флаг. В ячейку B2 введи формулу:

=ЕСЛИ(СЧЁТЕСЛИ($E$2:$E$100; A2)=0; "Нет в другом списке"; "Есть во втором списке")

Скопируй формулу вниз до конца списка.

Как это работает

• СЧЁТЕСЛИ($E$2:$E$100; A2) возвращает количество вхождений значения A2 во втором списке.
• Если результат равен 0 — значение нигде не найдено.
• ЕСЛИ(...; "Нет в другом списке"; "Есть во втором списке") превращает цифру в понятный текст.

Шаг 2. Фильтруем строки, которых нет во втором списке

Включи автофильтр и оставь только строки со значением «Нет в другом списке» — перед тобой будет чистый список «потерянных» клиентов, артикулов или e-mail.

Вариант с логическим флагом

Если тебе удобнее работать с логическими значениями (ИСТИНА/ЛОЖЬ), можно использовать сокращённый вариант:

=СЧЁТЕСЛИ($E$2:$E$100; A2)=0

Тогда столбец B будет содержать ИСТИНА для строк, которых нет во втором списке, и ЛОЖЬ для тех, которые есть. По этим флагам тоже удобно фильтровать.

Где пригодится такой подход

• проверить, какие клиенты не попали в отчёт по продажам;
• найти артикулы, которых нет в новом прайс-листе;
• выявить адреса e-mail, которые не добавлены в рассылку;
• отобрать строки для дальнейшей очистки или удаления.

Если тебе часто приходится приводить списки в порядок, посмотри ещё статью о 7 способах обработки списков в Excel .

Способ 3. Динамический список через ФИЛЬТР (Excel 365) и альтернатива для старых версий

В Excel 365 и Excel 2021 можно сразу получить готовый список значений из первого столбца, которых нет во втором, без вспомогательных столбцов и ручной фильтрации. Для этого используем функцию ФИЛЬТР.

Шаг 1. Пишем формулу ФИЛЬТР

Выбери пустую ячейку, например D2, и введи:

=ФИЛЬТР(A2:A100; СЧЁТЕСЛИ($E$2:$E$100; A2:A100)=0)

Что делает эта формула:

• СЧЁТЕСЛИ($E$2:$E$100; A2:A100) возвращает массив чисел — по одному для каждой строки первого списка;
• условие =0 оставляет только те элементы, которых нет во втором списке;
• ФИЛЬТР выдаёт готовый «чистый» список значений из A2:A100, которых нет в E2:E100.

Преимущество в том, что формула пересчитывается автоматически: если списки обновятся, результат тоже изменится.

Добавляем УНИК, чтобы убрать дубли

Если в первом списке возможны повторы и ты хочешь получить только уникальные значения, оберни результат в функцию УНИК:

=УНИК(ФИЛЬТР(A2:A100; СЧЁТЕСЛИ($E$2:$E$100; A2:A100)=0))

В итоге ты получишь список уникальных значений, которые не встречаются во втором списке.

Что делать в старых версиях Excel (без ФИЛЬТР и УНИК)

Если у тебя Excel без динамических массивов, есть два удобных пути:

1. Использовать вспомогательный столбец с флагом (Способ 2) и затем отфильтровать строки.
2. Использовать Power Query:
   • загрузить оба списка в Power Query;
   • выполнить объединение (Merge) как антисоединение: оставить только строки из первого списка, которых нет во втором;
   • вернуть результат в Excel как отдельную таблицу.

Подготовка таблицы к анализу (преобразование текста к числу, удаление мусора, чистка форматирования) подробно разбирается в статье о 10 способах очистить таблицу перед анализом .

Типичные ошибки при сравнении списков

• Лишние пробелы и невидимые символы.
  Значения могут выглядеть одинаково, но отличаться пробелом в конце или неразрывным пробелом. Перед сравнением полезно очистить данные. Для этого можно использовать функции очистки текста и приёмы из статей про лишние пробелы и невидимые символы .
• Неверный диапазон во втором списке.
  Если во второй части формулы $E$2:$E$100 ты случайно укажешь меньше строк, часть значений просто не будет участвовать в сравнении.
• Смешивание кодов и описаний.
  Для сравнения лучше использовать стабильные идентификаторы (артикул, ID, e-mail), а не текстовое описание товара или клиента.
• Сравнение по «грязным» данным.
  Если в одном списке есть дубликаты, а в другом — нет, это может влиять на результат. Иногда полезно сначала убрать дубликаты, а уже потом сравнивать списки.

Краткое резюме: какой способ выбрать

• Нужно быстро посмотреть «на глаз»?
  Используй условное форматирование с формулой СЧЁТЕСЛИ.
• Нужен понятный флаг и фильтр?
  Добавь вспомогательный столбец с ЕСЛИ(СЧЁТЕСЛИ(...)=0; "Нет в другом списке"; ...).
• Нужен готовый динамический список?
  В Excel 365 используй ФИЛЬТР и УНИК, в старых версиях — комбинацию вспомогательного столбца и Power Query.

Если ты хочешь системно подтянуть базу по формулам, логике и работе со списками, загляни в бесплатный курс «Excel с уверенностью: Шаг 1 — Основы» — там всё идёт от простого к сложному, с живыми примерами и практикой.