Как сравнить два столбца в Excel
Два списка клиентов, две выгрузки, старая и новая база — и нужно понять, кто есть в обеих, а кого нет. Разбираем пять способов сравнить два столбца и две таблицы в Excel: от формулы в одну строку до сверки баз по телефону, который в одной таблице записан с «8», а в другой — с «+7». В конце — файл со всеми формулами.
Что именно сравниваем
Для каждой задачи — свой способ. Ниже — пять, от простого к сложному. Названия функций — как в русском Excel; разделитель аргументов — точка с запятой.
Способ 1. Построчно
В соседнем столбце напишите =A2=B2 — получите ИСТИНА или ЛОЖЬ. Чтобы читалось проще: =ЕСЛИ(A2=B2;"совпадает";"разные"). Протяните формулу вниз на все строки.
Способ подходит, только если строки стоят в одном порядке. Если списки отсортированы по-разному, построчное сравнение покажет «разные» даже там, где значения есть в обоих списках.
Способ 2. Есть ли значение в другом списке: СЧЁТЕСЛИ
Формула =ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$100;A2)>0;"есть";"нет") считает, сколько раз значение из A2 встречается во втором списке. Знаки $ закрепляют диапазон, чтобы он не «съехал» при протягивании.
В примере из файла — два списка по 10 фамилий: построчно совпадают 5, а из первого списка во втором есть 6 — одна фамилия стоит на другой строке. Ещё 4 есть только в первом списке.
Способ 3. На какой строке: ПОИСКПОЗ
=ЕСЛИОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$100;0);"—") возвращает номер строки во втором списке, где нашлось значение, или прочерк, если не нашлось. Последний аргумент 0 — точное совпадение. Номер пригодится, чтобы подтянуть соседние данные функцией ИНДЕКС или ВПР.
Способ 4. Без формул: подсветить повторы
Выделите оба столбца → «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения» и выберите цвет. Значения, которые есть в обоих столбцах, подсветятся.
Удалить повторы можно через «Данные» → «Удалить дубликаты», но учтите: удаление необратимо. Сначала сохраните копию данных на другом листе — так советует и справка Microsoft.
Способ 5. Сверка двух баз клиентов по телефону
Две базы клиентов почти никогда не совпадают по формату телефона: «8 (900) 123-45-67» в одной, «+79001234567» в другой. Сравнивать их напрямую бесполезно — сначала приведите номер к одному виду.
В примере — 8 клиентов в первой базе и 7 во второй: 5 общих, 3 только в первой, 2 только во второй. Без приведения номеров формула не нашла бы ни одного общего клиента.
Постоянным клиентам ушла акция «для новых». Базу рассылки сверили с базой покупателей по телефону, но в одной номера были с «8», в другой — с «+7». Сравнение не нашло ни одного совпадения, и все получили приветственное предложение.
Частые ошибки
• Лишние пробелы. «Иванов » и «Иванов» — разные строки. Уберите пробелы функцией СЖПРОБЕЛЫ.
• Числа как текст. Номер договора 1024 в одной таблице — число, в другой — текст; формула их не сравнит.
• Диапазон без $. При протягивании он сдвигается, и последние строки сравниваются не со всем списком.
• Телефоны без приведения. «8…» и «+7…» не совпадут, пока не сравните последние 10 цифр.
• Удаление без копии. «Удалить дубликаты» не отменить, если файл уже сохранён.
Файл со всеми формулами
Два листа: «Два столбца» — построчное сравнение, «есть / нет» и номер строки, с подсветкой; «Две базы» — сверка двух баз клиентов по телефону с приведением номеров, суммой из второй базы и итогом: сколько общих, сколько только в одной базе. В русском Excel функции откроются с русскими названиями.
Скачать файл с формулами · xlsx, 16 КБ
Когда базы уже в CRM
Сверять таблицы приходится, пока клиенты живут в файлах. Когда они в amoCRM, одного клиента в разных карточках находит виджет «Дубли контактов и компаний»: точное совпадение по телефону или e-mail, нечёткое — по похожему имени или названию, свои поля — ИНН, ОГРН, номер договора. Перед объединением — сверка каждого поля, и телефоны, сделки, задачи переходят в основную запись. 8 994 ₽ за 6 месяцев; пробный период — 50 объединений.
Построчно — =A2=B2, наличие в другом списке — СЧЁТЕСЛИ, номер строки — ПОИСКПОЗ, без формул — условное форматирование. Две базы клиентов сравнивайте по ключу, приведённому к одному виду: последние 10 цифр телефона. Перед удалением дубликатов сохраняйте копию.
Частые вопросы
Converssion CRM — официальный партнёр amoCRM с 2017 года. Внедряем и сопровождаем amoCRM, делаем собственные виджеты для амоМаркета, чат-ботов, ИИ-агентов, сайты и рекламу. Услуги · Кейсы клиентов · Виджеты
