Как сравнить два списка в Excel: пошагово и с готовой таблицей
Два списка, и нужно понять, кто есть в одном и нет в другом. Такая задача встречается постоянно: приказ против табеля, список работников против списка военкомата, оплаты против счетов, участники мероприятия против приглашённых. Вручную сравнивать глазами можно до тридцати строк, дальше начинаются пропуски. В Excel это решается за пять минут, если знать три простых приёма. Разберём их по шагам, начиная с подготовки, без которой любой способ даёт неверный результат.
Подготовьте списки: без этого сравнение не сработает
Люди запрашивают «как в Excel сравнить два списка» и получают формулу, которая почему-то находит половину совпадений. Причина почти всегда не в формуле, а в данных. Прежде чем сравнивать, сделайте так:
- Шаг 1. Поместите оба списка на один лист в соседние столбцы, например A (список 1) и C (список 2). Так проще и формулам, и глазу.
- Шаг 2. Уберите лишние пробелы: в соседнем столбце напишите формулу СЖПРОБЕЛЫ от ячейки и протяните вниз. Пробел в конце фамилии невидим, но Excel считает записи разными.
- Шаг 3. Проверьте, что числа и номера имеют одинаковый тип: часть может оказаться текстом, часть числом.
- Шаг 4. Единообразно запишите регистр и букву «ё»: Excel обычно не различает регистр при сравнении, но «Фёдоров» и «Федоров» для него разные.
На эту подготовку уходит больше времени, чем на само сравнение, и она оправдана.
Способ 1: условное форматирование, самый быстрый
Подходит, когда нужно просто увидеть различия, а не получить результат в таблице.
- Шаг 1. Выделите список 1 (столбец A).
- Шаг 2. На вкладке «Главная» нажмите «Условное форматирование», затем «Правила выделения ячеек», затем «Повторяющиеся значения».
- Шаг 3. Выберите, что подсвечивать: повторяющиеся или уникальные. Для сравнения двух списков выделите их оба разом, и «уникальные» окажутся теми, кого нет в другом списке.
Недостаток: способ показывает различия цветом, но не даёт списка, который можно переслать или отфильтровать.
Способ 2: СЧЁТЕСЛИ, понятный и надёжный
Самая удобная формула для «есть ли значение в другом списке». Допустим, список 1 в A2:A500, список 2 в C2:C400.
- Шаг 1. В B2 напишите формулу: ЕСЛИ(СЧЁТЕСЛИ($C$2:$C$400;A2)=0;"только в списке 1";"есть в обоих").
- Шаг 2. Протяните формулу вниз до конца списка 1.
- Шаг 3. В D2 сделайте то же для второго списка: ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$500;C2)=0;"только в списке 2";"есть в обоих").
- Шаг 4. Включите фильтр на шапке и выберите «только в списке 1» или «только в списке 2».
Знак доллара фиксирует диапазон, чтобы он не «съезжал» при протягивании. Если забыть его, часть формул будет смотреть не туда.
Способ 3: ВПР или ПРОСМОТРX, если нужны данные из второго списка
Когда мало узнать «есть или нет», а нужно ещё подтянуть значение (например, дату рождения или сумму), пригодится ВПР. Формула ЕСЛИОШИБКА(ВПР(A2;$C$2:$D$400;2;ЛОЖЬ);"нет") вернёт значение из второго столбца второго списка или слово «нет». В новых версиях Excel то же делает ПРОСМОТРX, и работает она удобнее: не нужно считать номер столбца.
Эта связка помогает искать расхождения, а не только отсутствующие строки. Например, в обоих списках человек есть, но дата рождения у него разная. Сравните подтянутую дату с исходной: ЕСЛИ(B2=E2;"совпало";"расхождение").
Способ 4: готовая таблица сверки
Те, кто сверяет списки регулярно (например, ежемесячно), быстро устают переписывать формулы. Тогда логично использовать готовый шаблон, в который достаточно вставить два списка. Такая таблица есть: сравнение двух списков в Excel. Она показывает, кто есть только в списке А, только в списке Б, где есть расхождения и какие записи задвоены, и сразу собирает итоговую сводку. Таблица бесплатная, а файл работает на вашем компьютере, так что персональные данные никуда не уходят.
Пример: сверка приказа и табеля
Разберём типичную рабочую ситуацию с цифрами, чтобы было видно, как это выглядит на практике. Цифры условные.
Допустим, в приказе 42 человека, а в табеле 44.
- Список 1: фамилии из приказа.
- Список 2: фамилии из табеля.
- Результат: двое «только в списке 2» (их в приказ не включили), и, скорее всего, одна фамилия записана по-разному.
Именно последнее и ловится на этапе подготовки данных. Если не очистить пробелы и «ё», в «только в списке 1» окажется ещё пять человек, которые на самом деле везде есть.
Какой способ выбрать
Если вам нужно один раз взглянуть на различия, хватит условного форматирования. Если нужно получить отфильтрованный список «только в А» и «только в Б», берите СЧЁТЕСЛИ. Если из второго списка нужны данные, подойдёт ВПР или ПРОСМОТРX. А если сверка повторяется каждый месяц и в ней участвуют не двое, а несколько человек, разумнее вынести всё в готовый файл. Так результат не зависит от того, кто именно сегодня делает сверку, и не нужно каждый раз вспоминать формулы.
Частые ошибки
- Не очистили пробелы и регистр, получили ложные «расхождения».
- Сравнивают ФИО вместо ключа. Однофамильцы обманут формулу. Если есть табельный номер, сравнивайте по нему, а ФИО используйте как проверку.
- Забыли знаки доллара в диапазоне.
- Числа как текст. Телефон или номер документа записан по-разному, и совпадений нет.
- Сравнивают только в одну сторону. Нужно проверять оба направления: «из 1 в 2» и «из 2 в 1».
- Не оставили копию исходных данных, и после сортировки не восстановить порядок.
Итог
Главное правило: сначала приведите данные в порядок, потом сравнивайте, и всегда смотрите результат в обе стороны.
Для разового сравнения хватит СЧЁТЕСЛИ и фильтра. Для регулярной сверки и работы с расхождениями удобнее готовый файл: загрузите таблицу сверки двух списков и вставьте в неё свои данные. Как искать дубли и несовпадающие ФИО и номера отдельно, смотрите в связанных материалах блога; а подборка таблиц для контроля и сводов собрана в разделе «Контроль».