Как найти дубли в Excel, если одна позиция записана по-разному

Дубли в Excel, которые совпадают символ в символ, находятся за минуту: условным форматированием, командой «Удалить дубликаты» или формулой СЧЁТЕСЛИ. Одну позицию, записанную по-разному, эти способы не видят. Для Excel «Подш. 180205» и «Подшипник 6205-2RS» разные строки, хотя на складе это один подшипник. Ниже разберём штатные способы, подготовку списка, которая добавляет находок, и место, где Excel перестаёт помогать.

Точные дубли: три штатных способа

Все три способа сравнивают ячейки целиком. Названия пунктов приводим по русской версии Excel для Windows, в вашей версии они могут немного отличаться.

Подсветить повторы условным форматированием

Выделите колонку с наименованиями. На вкладке «Главная» откройте «Условное форматирование», затем «Правила выделения ячеек» и «Повторяющиеся значения». Excel закрасит все строки, у которых есть точная пара. Ничего не удаляется, поэтому способ безопасен для первого взгляда на список.

Удалить дубликаты

На вкладке «Данные» есть команда «Удалить дубликаты». Она оставляет первую строку из каждой группы повторов и удаляет остальные. Перед запуском сделайте копию листа: удаление сразу меняет данные, и код удалённой строки пропадёт вместе с ней. Для справочника номенклатуры это неудобно, ведь каждой строке соответствует карточка в учётной системе.

Посчитать повторы формулой

В соседней колонке напишите формулу вида =СЧЁТЕСЛИ($B:$B;B2), где B колонка с наименованиями. Число больше единицы означает, что у строки есть точная пара. Потом отфильтруйте колонку и смотрите группы. Строки остаются на месте вместе с кодами.

Дубли в двух столбцах

Если нужно сравнить два списка, например ваш справочник и прайс поставщика, выделите оба столбца и включите то же правило условного форматирования. Формулой это делается так: =СЧЁТЕСЛИ(Лист2!$B:$B;B2) покажет, сколько раз строка встречается во втором списке.

Что Excel считает совпадением

Все три способа не различают регистр: «ПОДШИПНИК» и «подшипник» для них одинаковы. Зато лишний пробел, другой знак или латинская буква делают строки разными.

• Пробелы. «Болт М12х40» и «Болт М12х40» с двумя пробелами не совпадут. Пробел в конце строки вы не увидите вовсе.
• Латиница вместо кириллицы. Наименование копируют из счёта поставщика, и в «ВВГнг(А)» оказывается латинская «A». Внешне строка та же, для Excel другая.
• Знаки в размерах. «3х2,5», «3x2.5» и «3*2,5» означают одно и то же сечение. Для сравнения это три разных значения.

Подготовка списка: что снимает очистка

Перед поиском приведите наименования к одному виду в отдельной колонке. Исходную колонку не трогайте, она понадобится для сверки.

1. Уберите лишние пробелы. Функция СЖПРОБЕЛЫ удаляет пробелы по краям и сводит двойные к одинарным.
2. Сведите знаки к одному. Функцией ПОДСТАВИТЬ замените «x» и «*» в размерах на «х», точку в десятичных дробях на запятую.
3. Замените латиницу на кириллицу. Похожих пар немного: A, B, C, E, H, K, M, O, P, T, X. Каждую можно заменить вложенным ПОДСТАВИТЬ. Проверьте результат на строках с настоящей латиницей, например «2RS» или «LS»: их менять нельзя.

После очистки точное сравнение найдёт больше пар. Строки, которые отличаются словами, сокращениями или обозначением по другому стандарту, так и останутся разными.

Ручной поиск: сортировка и фильтр по обозначению

Когда точные совпадения закончились, специалисты обычно переходят к ручному поиску. Два приёма заметно его ускоряют.

• Сортировка по очищенной колонке. Строки, которые начинаются одинаково, встанут рядом. «Подшипник 6205-2RS» и «Подшипник 6205 2RS» окажутся соседями, и пару видно глазами. «Подш. 180205» при этом уедет в другое место списка.
• Текстовый фильтр «содержит». Отфильтруйте колонку по ключевому обозначению, например «6205». Вы увидите все строки с этим размером подшипника и разберёте их за один раз. Старое обозначение 180205 в такой фильтр не попадёт, его придётся искать отдельно.

Оба приёма работают, пока строк немного и человек знает соответствие обозначений. На каждой группе он тратит время на поиск строк, а само решение занимает малую часть этого времени.

Где способы Excel ломаются

У справочника номенклатуры два вида трудных случаев. Excel не справляется ни с одним.

Одна позиция, записанная по-разному

«Подш. 180205» и «Подшипник шариковый радиальный 6205-2RS» почти не совпадают по буквам. 180205 является старым обозначением, 6205-2RS международным, и это один подшипник с двумя резиновыми уплотнениями. Так же расходятся «Задв. 30с41нж DN100 PN16» и «Задвижка 30С41НЖ Ду100 Ру16»: DN и Ду, PN и Ру обозначают одно и то же. Никакая очистка эти пары не сведёт, нужно знать соответствие обозначений.

Разные позиции с почти одинаковой записью

«Подшипник 6205-2RS» и «Подшипник 6205-2Z» различаются двумя символами. У первого резиновые уплотнения, у второго металлические шайбы. Задвижки Ду100 Ру16 и Ду100 Ру25 различаются давлением, болты класса прочности 8.8 и 10.9 прочностью. Если начать искать похожие строки, например обрезать хвост наименования или сравнивать первые слова, такие пары склеятся. После объединения остатки разных товаров окажутся на одной карточке.

Предполагаю, что в вашем списке подсветка уже показала часть повторов, а глазами вы видите ещё столько же, которые Excel не отметил. Так ли?

Сводная таблица способов

Как проверить результат

Любой способ стоит проверить на контрольной выборке, прежде чем объединять что-то в базе.

1. Возьмите 200 строк из самой запутанной группы, например подшипники или крепёж.
2. Разметьте их вручную: какие строки являются одной позицией.
3. Сравните с тем, что нашёл способ. Считайте две ошибки отдельно: пропущенные дубли и ошибочные склейки.

Ошибочная склейка обходится дороже пропуска. Пропущенный дубль просто остаётся в справочнике. Склеенные разные товары смешивают остатки, и закупка покупает не то. Поэтому способ с меньшим числом находок, но без ложных склеек, часто полезнее.

Когда Excel уже мало

На сотнях строк очистка и ручная проверка групп занимают вечер. На тысячах строк ручной разбор растягивается: подрядчики по НСИ называют норму 5–9 минут на позицию МТР. Расчёт в часах и рублях для справочников разного размера приведён в статье о том, сколько стоит ручной разбор справочника.

Если список выгружен из 1С, штатная обработка поиска дублей тоже сравнивает написание и ошибается в похожих местах. Почему так получается, разобрано в статье о том, почему поиск в 1С пропускает часть повторов. Как потом объединить найденные группы в базе, описано в статье об объединении номенклатуры.

Результат на тестовом справочнике

Мы проверяем каждую строку по смыслу и по ключевым характеристикам: уплотнению, давлению, классу прочности, исполнению кабеля. Цифры на тестовом справочнике МТР приведены на странице о нормализации справочника. Вот примеры пар из него и верное решение по каждой.

Спорные пары мы выносим на лист «Вопросы к вам» с причиной, решение по ним за вами. Чтобы увидеть результат на своих строках, пришлите 5–10 тысяч строк на бесплатную пробу.

Вопросы про дубли в Excel

Как найти дубли в двух столбцах?

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

Почему «Удалить дубликаты» не убрало похожие строки?

Команда сравнивает ячейки целиком. Лишний пробел, латинская буква или сокращение делают строки разными. Регистр при этом не важен.

Можно ли найти дубли с опечатками формулой?

Часть опечаток снимает очистка: СЖПРОБЕЛЫ и замена латиницы через ПОДСТАВИТЬ. Сокращения и разные обозначения одного товара формула не распознает, для этого нужно знать соответствие обозначений.

Как не склеить разные позиции?

Перед объединением смотрите на ключевую характеристику: исполнение подшипника, давление, класс прочности, марку кабеля. Если она различается, позиции разные, даже когда разница в одном символе.