Дубли в Excel: как найти клиента, записанного по-разному
В базе клиентов дубли почти никогда не совпадают буква в букву: у одного менеджера «8 (000) 123-45-67», у другого «+7 000 123 45 67», а e-mail набран то строчными, то заглавными. Встроенный поиск таких дублей не видит. Показываем, как привести телефоны и почту к одному виду и найти повторы формулами, — и даём готовый шаблон.
Почему «Удалить дубликаты» не помогает
Команда «Удалить дубликаты» и подсветка повторяющихся значений сравнивают ячейки буква в букву. Для неё «8 (000) 123-45-67» и «+7 000 123 45 67» — разные значения, хотя это один номер. То же с почтой: «Belova@Example.com» и «belova@example.com » с пробелом в конце не совпадут.
Поэтому искать дубли нужно в два шага: сначала привести телефон и e-mail к одному виду, потом считать повторы.
Шаг 1. Телефон — к 10 цифрам
Из номера убирают пробелы, дефисы, скобки и «+», а потом берут 10 последних цифр — так «8…» и «+7…» превращаются в одно и то же: =ПРАВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;" ";"");"-";"");"(";"");")";"");"+";"");10).
Если в одной ячейке записаны два номера через запятую, разнесите их по разным колонкам или строкам — формула рассчитана на один номер.
Шаг 2. E-mail — без регистра и пробелов
Почту переводят в строчные буквы и обрезают лишние пробелы: =СТРОЧН(СЖПРОБЕЛЫ(C2)). После этого «Belova@Example.com» и «belova@example.com » совпадут.
Шаг 3. Считаем повторы и помечаем первую запись
Рядом с приведёнными значениями считают, сколько раз они встречаются в базе: =СЧЁТЕСЛИ($E$2:$E$500;E2). Число больше 1 — у клиента есть дубль.
Чтобы понять, какую запись оставить, сравнивают только строки выше: если ни телефон, ни e-mail раньше не встречались — это первая запись клиента, «оставить»; если встречались — «дубль». Для этого диапазон в СЧЁТЕСЛИ закрепляют только сверху: =СЧЁТЕСЛИ($E$1:E1;E2).
Что получилось на примере
В шаблоне — 12 записей. Формулы нашли 4 дубля: «Вектор ООО» с тем же номером в другой записи, «Белова Мария» без телефона, но с той же почтой в другом регистре, «Ритм» с номером, набранным слитно, и «Альфа Строй» без дефиса в названии. Уникальных клиентов — 8, доля дублей — 33%.
Обратите внимание: по названию шаблон дубли не ищет. «ООО «Альфа-Строй»» и «ООО «Альфа Строй»» совпали по телефону и почте, а не по имени. Похожие названия бывают у разных компаний, поэтому имя — повод присмотреться, но не доказательство.
Перед удалением дубля
Не удаляйте дубль сразу. В нём часто есть то, чего нет в основной записи: второй телефон, комментарий, история заказов. Сначала перенесите эти данные, потом удаляйте. Включите фильтр по колонке «Что делать» — увидите только дубли.
Шаблон: скачать
Вставьте свою базу на лист «База» в колонки A–D — формулы в колонках E–I уже протянуты на 500 строк.
Скачать шаблон поиска дублей · xlsx, 49 КБ
Когда база живёт в amoCRM
В CRM дубли появляются каждый день: клиент пишет с другого номера, менеджер заводит контакт заново. Виджет «Дубли контактов и компаний» находит их сам: номера приводятся к одному виду, так что «8 999 123-45-67» и «+7 (999) 123-45-67» — один номер, e-mail сравнивается без учёта регистра, а совпадение можно искать и по выбранным полям вроде ИНН или номера договора. Похожее имя само по себе дублем не делает — только повышает уверенность.
Перед объединением открывается сверка: две записи рядом и выбор значения там, где они расходятся. Пока вы не нажали «Объединить», в amoCRM ничего не меняется.
Подписка за аккаунт: 6 месяцев — 8 994 ₽, 12 месяцев (10 + 2 в подарок) — 14 985 ₽, 24 месяца (18 + 6 в подарок) — 26 973 ₽. Пробный период — объединение первых 50 записей; поиск, история и аналитика в нём работают без ограничений.
Дубли в Excel ищут в два шага: телефон — к 10 последним цифрам, e-mail — к строчным буквам без пробелов, и только потом СЧЁТЕСЛИ. Первая запись клиента — «оставить», остальные — «дубль», а перед удалением данные дубля переносят в основную запись.