Как найти дубликаты и расхождения между двумя списками
Дубликат в списке выглядит безобидно, пока из-за него один человек не получает два пропуска, две выплаты или два письма. А расхождение между двумя списками (в одном фамилия «Петрова», в другом «Петрова-Сидорова», в одном номер с пробелами, в другом без) приводит к тому, что сверка «не сходится» без видимой причины. В этой статье разберём именно эту сторону задачи: как находить повторы внутри одного списка и несовпадения между двумя, особенно когда ключом служат ФИО и номера. Базовое сравнение «кто есть, а кого нет» вынесено в отдельную инструкцию про то, как сравнить два списка в Excel.
Что считать дубликатом, а что расхождением
Определимся с терминами, иначе легко запутаться:
- Дубликат это повтор внутри одного списка: одна и та же запись встречается дважды и более.
- Только в одном списке это запись, которая есть в А и отсутствует в Б.
- Расхождение это когда человек есть в обоих списках, но один из параметров отличается: дата рождения, номер, должность.
- Ложное расхождение возникает, когда запись одна и та же, но записана по-разному (лишний пробел, «ё», регистр).
Большая часть времени уходит именно на ложные расхождения. Чем чище данные, тем меньше ручной проверки.
Шаг 1. Приведите ключ к единому виду
Создайте в каждом списке вспомогательный столбец «Ключ», на котором будет идти сравнение. Для ФИО он собирается так:
- Шаг 1. Уберите лишние пробелы функцией СЖПРОБЕЛЫ.
- Шаг 2. Приведите к одному регистру: ПРОПИСН(СЖПРОБЕЛЫ(A2)).
- Шаг 3. Замените «Ё» на «Е»: ПОДСТАВИТЬ(ПРОПИСН(СЖПРОБЕЛЫ(A2));"Ё";"Е").
- Шаг 4. Если фамилия, имя и отчество лежат в разных столбцах, склейте их через пробел.
Для номеров (телефон, СНИЛС, паспорт, номер договора) нужна другая очистка: уберите пробелы, дефисы и скобки вложенными ПОДСТАВИТЬ. И обязательно проверьте формат ячейки: длинные номера и числа с ведущим нулём Excel любит превращать в число и «съедать» ноль. Для таких столбцов задавайте формат «Текстовый» заранее, до вставки данных.
Лучший ключ это то, что уникально и не зависит от написания: табельный номер, ИНН, номер договора. ФИО годится только как запасной вариант, потому что однофамильцы встречаются чаще, чем кажется. Хорошая связка: ФИО плюс дата рождения.
Шаг 2. Найдите дубликаты внутри списка
Самый быстрый способ найти повторы глазами:
- Шаг 1. Выделите столбец «Ключ».
- Шаг 2. Нажмите «Главная», «Условное форматирование», «Правила выделения ячеек», «Повторяющиеся значения».
- Шаг 3. Выберите цвет и нажмите «ОК»: все повторы подсветятся.
Для работы с повторами формулой используйте счётчик: в соседнем столбце напишите СЧЁТЕСЛИ($B$2:$B$500;B2). Значение больше единицы значит, что запись встречается несколько раз. Чтобы пометить именно второе и последующие вхождения, ограничьте диапазон верхом: СЧЁТЕСЛИ($B$2:B2;B2). Тогда первое вхождение получит единицу, а повторы двойку и больше. Отфильтруйте значения больше единицы и решайте, что удалять.
Удалять дубли стоит не вслепую. Встроенная команда «Данные», «Удалить дубликаты» оставляет первую запись и стирает остальные, а они могут отличаться другими полями. Сначала скопируйте лист, потом чистите.
Шаг 3. Найдите расхождения между двумя списками
Когда ключи приведены к единому виду, сравнение идёт формулами:
- Шаг 1. Для записей, которых нет в другом списке: ЕСЛИ(СЧЁТЕСЛИ(список2_ключ;B2)=0;"нет во втором";"есть").
- Шаг 2. Для найденных пар подтяните параметр из второго списка через ВПР или ПРОСМОТРX.
- Шаг 3. Сравните: ЕСЛИ(D2=H2;"совпало";"расхождение").
- Шаг 4. Отфильтруйте «расхождение» и разберите каждое вручную: это может быть как ошибка в одном списке, так и реальное изменение данных.
Мини-пример на двух записях:
- Список 1: «Кузнецов Алексей Петрович», телефон 8 (900) 123-45-67.
- Список 2: «КУЗНЕЦОВ АЛЕКСЕЙ ПЕТРОВИЧ », телефон +79001234567.
- После очистки ключи совпали, а номера в одном виде сравнить можно: если и они равны, расхождения нет.
Типичные источники расхождений в ФИО
- Лишние пробелы в начале, конце или между словами.
- «Е» и «Ё», разный регистр.
- Двойные фамилии с дефисом и без.
- Сокращения: «Иванов И. И.» против «Иванов Иван Иванович». Такие записи вручную проще согласовать, чем формулой.
- Смена фамилии: формула этого не увидит, помогает только второй ключ (дата рождения, табельный номер).
- Опечатки: «Сергеев» и «Сергеевв» формулой точного совпадения не найти, их ловят глазами по списку «только в одном».
Как проверить, что результат верный
После любой сверки сделайте контрольный подсчёт. Количество уникальных записей в первом списке должно равняться числу записей «есть в обоих» плюс число записей «только в первом». То же самое для второго списка. Если цифры не сходятся, значит, остались невидимые дубли, пустые строки или записи с разным форматом ключа. Выберите несколько случайных строк из каждой группы и сверьте их вручную с исходными документами: две-три минуты проверки избавят от неприятных вопросов, когда по итогам сверки кому-то придётся что-то исправлять.
Частые ошибки
- Сравнивают сырые данные без очистки и получают лавину ложных расхождений.
- Удаляют дубли без копии листа.
- Ключ из одного ФИО при наличии однофамильцев.
- Ведущие нули в номерах потеряны из-за числового формата.
- Проверяют в одну сторону. Нужно и «из А в Б», и «из Б в А».
- Игнорируют итоговую проверку. После работы посчитайте: записей в А плюс записей в Б минус общие должно сходиться со сводкой.
Если сверка регулярная
Когда такая работа повторяется каждый месяц, лучше один раз настроить готовый файл. В бесплатной таблице сверки двух списков уже есть листы для списков А и Б, подсветка дублей и сводка: сколько записей только в А, только в Б, сколько расхождений и дублей. Вставляете данные и смотрите результат. Другие таблицы для контроля и сводов собраны в разделе «Свод».