Неявные дубли — фразы, которые для человека одинаковы, а для Excel разные: «купить диван» и «Диван купить». Чтобы найти их, каждую фразу приводят к «ключу»: нижний регистр, без лишних пробелов и пунктуации, слова по алфавиту. В Excel 365 ключ строит формула =ОБЪЕДИНИТЬ(" ";ИСТИНА;СОРТ(ТЕКСТРАЗД(СТРОЧН(СЖПРОБЕЛЫ(A2));;" "))), а повторы ключей отмечает =СЧЁТЕСЛИ($B$2:B2;B2)>1. Удалить неявные дубли — в том числе с разными словоформами и по другому столбцу — можно процедурами !SEMTools в 1 клик.
Неявные дубли и их удаление — одна из важнейших задач при работе с текстовым контентом. С подобной задачей часто встречаются SEO и PPC-специалисты, а также вебмастера и контент-менеджеры сайтов.
В семантическом ядре, поисковых запросах и ключевых словах, каталогах товаров, услуг, списках страниц, URL и прочих таблицах со структурированными и не очень текстовыми данными часто встречаются т.н. неявные дубли - ячейки с содержимым, которое по мнению человека повторяет уже имеющееся в таблице, но для компьютера дублем не являющееся из-за минимальных, порой вовсе незаметных различий.
Рассмотрим ситуацию подробнее.
Анализ неявных дублей
Если проанализировать причины, по которым робот видит разницу в содержимом ячеек, можно точно определить несколько таковых. Незначимые для человека, но значимые для компьютера различия — это:
- Изменённый порядок слов в ячейке;
- Лишние пробелы в начале ячейки или между слов
- Не удалённая пунктуация в ячейках;
- Похожие буквы на другом языке;
- Слова и фразы в разных падежах и других видах изменения словоформ;
- В индустрии SEO и PPC — т.н. стоп-слова.
Для каждого типа различий есть и способ устранить их:
- Сортировка слов внутри ячейки решает проблему с разным порядком слов;
- Удаление всех символов, кроме букв и цифр (и пробелов) устранит и возможные различия в них;
- Для исправления различий словоформ используется лемматизация.
После устранения всех различий можно выявить дубликаты внутри столбца и проанализировать, какие фразы неявно дублируют друг друга. Получится три столбца — исходные фразы, нормализованные фразы и статусы их дублирования.
Найти неявные дубли формулой
В Excel 365 и 2024 нормализацию можно сделать одной формулой: СТРОЧН приводит к нижнему регистру, СЖПРОБЕЛЫ убирает лишние пробелы, ТЕКСТРАЗД раскладывает фразу на слова, СОРТ сортирует их, ОБЪЕДИНИТЬ склеивает обратно. Одинаковые ключи — это и есть неявные дубли:
| A | B | C | |
|---|---|---|---|
| 1 | Фраза | Ключ | Статус |
| 2 | купить диван | диван купить=ОБЪЕДИНИТЬ(" ";ИСТИНА;СОРТ(ТЕКСТРАЗД(СТРОЧН(СЖПРОБЕЛЫ(A2));;" "))) | =ЕСЛИ(СЧЁТЕСЛИ($B$2:B2;B2)>1;"дубль";"") |
| 3 | диван купить | диван купить=ОБЪЕДИНИТЬ(" ";ИСТИНА;СОРТ(ТЕКСТРАЗД(СТРОЧН(СЖПРОБЕЛЫ(A3));;" "))) | дубль=ЕСЛИ(СЧЁТЕСЛИ($B$2:B3;B3)>1;"дубль";"") |
| 4 | Купить Диван | диван купить=ОБЪЕДИНИТЬ(" ";ИСТИНА;СОРТ(ТЕКСТРАЗД(СТРОЧН(СЖПРОБЕЛЫ(A4));;" "))) | дубль=ЕСЛИ(СЧЁТЕСЛИ($B$2:B4;B4)>1;"дубль";"") |
| 5 | диваны купить | диваны купить=ОБЪЕДИНИТЬ(" ";ИСТИНА;СОРТ(ТЕКСТРАЗД(СТРОЧН(СЖПРОБЕЛЫ(A5));;" "))) | =ЕСЛИ(СЧЁТЕСЛИ($B$2:B5;B5)>1;"дубль";"") |
Строки 3 и 4 отличаются от первой порядком слов, регистром и двойным пробелом — ключ у них одинаковый, поэтому они помечены как дубли. «диваны купить» формула не поймала: это другая словоформа, для неё нужна лемматизация. Знаки препинания тоже стоит удалить заранее.
В английском Excel: =TEXTJOIN(" ",TRUE,SORT(TEXTSPLIT(LOWER(TRIM(A2)),," ")))
Чтобы удалить дубли, отфильтруйте столбец «Статус» по значению «дубль» и удалите видимые строки — или очистите только ячейки, как при удалении дубликатов без смещения.
Удалить неявные дубли в 1 клик
Как удалить дубликаты фраз, в которых слова различаются лишь их порядком? Подобный инструмент доступен в меню !SEMTools «УДАЛИТЬ» — «Ячейки» — «Дубли внутри диапазона». Ниже наглядная демонстрация работы. Макрос работает аналогично обычному удалению дубликатов без смещения - просто очищает содержимое ячейки, если в ней обнаружен неявный дубль одной из ячеек выше.
Удаление неявных дублей с учётом словоформ
В контекстной рекламе и SEO при проработке семантического ядра бывает важно избавиться в том числе от дублей, в которых различия не только в порядке слов, но и в словоформах. В примере выше видно, что дубликаты такого типа там не удаляются.
Для решения проблемы может помочь такой процесс, как лемматизация. Чтобы не пришлось создавать дополнительный столбец, над которым производить процесс лемматизации, в меню удаления дублей также был добавлен пункт удаления дубликатов слов и фраз вместе с ней. При этом исходные фразы не меняются — лемматизация происходит в фоновом режиме и используется только для сравнения фраз.
Удаление неявных дублей другого столбца
Когда нужно просто сравнить два диапазона между собой и удалить дубли из первого, задача не кажется сложной. Это можно сделать даже с помощью условного форматирования или функции ВПР. Но вот если во втором столбце содержатся неявные дубли, и их тоже хочется удалить, без навыков программирования вам точно не обойтись. Однако, есть и простое решение - соответствующая процедура надстройки !SEMTools.
В пару кликов вы можете сравнить диапазоны между собой и удалить неявные дубли, не прибегая к помощи программистов или поиска готовых решений на форумах по Excel. Демонстрация работы инструмента ниже:
Как мы видим, неявно дублирующиеся ячейки в первом диапазоне были успешно найдены во втором, и были удалены.
Процедура доступна как в Pro, так и Lite версии надстройки !SEMTools.
Почему неявные дубли не находятся
- Фразы отличаются знаками препинания. «диван, купить» и «диван купить» дадут разные ключи. Сначала удалите пунктуацию.
- В одной фразе латинская буква. «Диван» с латинской «а» выглядит так же, но это другое слово. Проверьте фразы на смешанную латиницу и кириллицу.
- Отличаются словоформы. «диван» и «диваны» формула не сравнит — нужна лемматизация или процедура !SEMTools с учётом словоформ.
- Формула даёт #ИМЯ?. ТЕКСТРАЗД и СОРТ есть только в Microsoft 365 и Excel 2024. В других версиях отсортируйте слова в ячейках процедурой сортировки слов.
Неявные дубли — частые вопросы
Что такое неявные дубли?
Фразы, которые совпадают по смыслу, но отличаются порядком слов, регистром, пробелами, пунктуацией или словоформами: «купить диван» и «Диван, купить».
Как найти неявные дубли в Excel?
Привести фразы к ключу формулой =ОБЪЕДИНИТЬ(" ";ИСТИНА;СОРТ(ТЕКСТРАЗД(СТРОЧН(СЖПРОБЕЛЫ(A1));;" "))) и найти повторы ключей через СЧЁТЕСЛИ или «Условное форматирование → Повторяющиеся значения».
Как удалить неявные дубли с разными словоформами?
Лемматизировать фразы перед сравнением. В !SEMTools это делает процедура удаления неявных дублей с учётом словоформ — исходные фразы при этом не меняются.
Как удалить из списка неявные дубли другого списка?
Процедурой !SEMTools «Удалить неявные дубли другого диапазона» — она сравнивает нормализованные фразы обоих диапазонов.
Удалить неявные дубли — с другим порядком слов, регистром, пунктуацией и даже другими словоформами, внутри столбца или по другому диапазону — можно в 1 клик с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить неявные дубли
| Отличие | Как устранить |
|---|---|
| Порядок слов | Сортировка слов в ячейке (СОРТ + ТЕКСТРАЗД) |
| Регистр и пробелы | СТРОЧН + СЖПРОБЕЛЫ |
| Пунктуация | Удалить знаки препинания |
| Словоформы | Лемматизация |
| Всё сразу | Процедура !SEMTools «Дубли внутри диапазона» |
Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: найти дубликаты и удалить повторяющиеся слова.