Нечёткий поиск в Excel это поиск значений, которые похожи на искомое, но не совпадают с ним буква в букву. Встроенной функции «найти похожее» в Excel нет, поэтому задачу решают тремя способами: подстановочными знаками * и ? для поиска по части слова, расстоянием Левенштейна для поиска с опечатками и надстройкой Fuzzy Lookup или нечётким слиянием в Power Query для сопоставления двух таблиц. Какой способ выбрать, зависит от задачи, и ниже они разобраны по порядку, начиная с самого простого, подстановочных знаков, а для списков запросов и названий есть готовые процедуры надстройки.
Какой способ нечёткого поиска выбрать
| Задача | Способ |
|---|---|
| Найти ячейки, где слово встречается внутри текста | подстановочные знаки в СЧЁТЕСЛИ, ВПР, ПОИСК, ПРОСМОТРX |
| Найти значение при неизвестной части написания | знак ? или * в шаблоне |
| Исправить опечатку по справочнику | расстояние Левенштейна |
| Сопоставить два списка с разным написанием названий | Fuzzy Lookup или нечёткое слияние Power Query |
| Найти дубли с другим порядком слов | процедура «Неявные дубли» в !SEMTools |
| Найти опечатки в поисковых запросах | процедура «Работа с опечатками» в !SEMTools Pro |
Подстановочные знаки: звёздочка, вопрос и тильда
Подстановочные знаки это шаблонные символы, которые заменяют часть текста. Их понимают СЧЁТЕСЛИ и другие функции с условием, ВПР с точным совпадением, ПОИСК и ПОИСКПОЗ. Три знака:
- * заменяет любое количество символов, в том числе ни одного: «кофе*» найдёт «кофемашина» и «кофе молотый».
- ? заменяет ровно один символ: «ко?» найдёт «кот» и «код», но не «колос».
- ~ снимает особый смысл со следующего знака: «~*» ищет саму звёздочку, а «~?» сам знак вопроса.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Товар | Цена | Ищем | Цена | Сколько с «кофе» | |
| 2 | Кофе молотый | 450 | чай | 200=ВПР("*"&D2&"*";A2:B6;2;ЛОЖЬ) | 3=СЧЁТЕСЛИ(A2:A6;"*кофе*") | |
| 3 | Кофемашина | 12000 | ||||
| 4 | Чай чёрный | 200 | ||||
| 5 | Зерновой кофе | 900 | ||||
| 6 | Какао | 300 |
Звёздочки склеены с ячейкой через &, поэтому достаточно менять слово в D2. ВПР вернёт цену первого найденного товара, где встретилось «чай». Регистр знаки не различают.
В английском Excel: =VLOOKUP("*"&D2&"*",A2:B6,2,FALSE)
Знаки работают только в функциях, которые сравнивают текст по шаблону. В простом сравнении =A2="*кофе*", в функциях НАЙТИ и СОВПАД они воспринимаются как обычные символы.
Поиск по части слова: ПОИСК, СЧЁТЕСЛИ, ВПР, ПРОСМОТРX
- Есть ли слово в ячейке: =ЕЧИСЛО(ПОИСК("кофе";A2)). ПОИСК не различает регистр и возвращает позицию слова, а для отсутствия ошибку, которую превращает в ИСТИНА и ЛОЖЬ функция ЕЧИСЛО.
- Сколько ячеек содержат слово: =СЧЁТЕСЛИ(A2:A100;"*кофе*"), подробнее в статье СЧЁТЕСЛИ.
- Подтянуть данные по части названия: =ВПР("*"&D2&"*";A2:B100;2;ЛОЖЬ), см. ВПР. Последний аргумент ЛОЖЬ обязателен, иначе подстановочные знаки не сработают.
- То же в Excel 365: =ПРОСМОТРX("*"&D2&"*";A2:A100;B2:B100;;2). Режим 2 в пятом аргументе включает подстановочные знаки, см. ПРОСМОТРX.
Если слов для поиска много, формулы с шаблонами становятся громоздкими. Тогда проще воспользоваться поиском по списку слов: поиск в Excel по словам.
Почему ВПР с приближённым совпадением не ищет похожие слова
Название аргумента «Интервальный просмотр» в ВПР сбивает с толку. Значение ИСТИНА (или 1) включает не поиск похожего слова, а поиск ближайшего меньшего значения в отсортированном столбце. Для чисел и дат это полезно, для текста бесполезно: слова будут сравниваться по алфавиту, и вместо «Москва» формула с опечаткой «Масква» может вернуть произвольную соседнюю строку. Для опечаток нужны методы ниже.
Поиск с опечатками: расстояние Левенштейна
Когда искомое написано с ошибкой, подстановочные знаки бессильны: непонятно, где поставить звёздочку. Здесь помогает расстояние Левенштейна, число правок, которое отделяет одну строку от другой. Для «Масква» и «Москва» оно равно 1. Посчитайте расстояние от искомого слова до каждого значения справочника функцией LEVENSTEIN, возьмите минимум и найдите по нему строку через ИНДЕКС и ПОИСКПОЗ. Пошаговый пример с формулами разобран в статье расстояние Левенштейна в Excel.
Перед сравнением приведите регистр к одному виду и уберите лишние пробелы через СЖПРОБЕЛЫ: иначе «Москва » и «москва» окажутся не такими близкими, как вы думаете.
Надстройка Fuzzy Lookup от Microsoft
Fuzzy Lookup это бесплатная надстройка Excel от Microsoft, которая сопоставляет две таблицы по похожему тексту. В описании Microsoft она сравнивает строки по сходству, а не по точному совпадению, поэтому находит «ООО Ромашка» там, где в другом списке «Ромашка ООО». Надстройку скачивают с сайта Microsoft и устанавливают отдельно. Для каждой строки первой таблицы она подбирает самую похожую строку второй и сообщает степень сходства. Результат нужно проверять глазами: порог сходства подбирается опытным путём.
Нечёткое слияние в Power Query
В Excel 365 и новых версиях Excel нечёткое сопоставление встроено в Power Query. Загрузите обе таблицы в редактор запросов (вкладка «Данные» → «Из таблицы или диапазона»), затем выберите «Объединить запросы» и отметьте столбцы, по которым сравниваем. В окне объединения включите параметр нечёткого сопоставления (в английском интерфейсе «Use fuzzy matching to perform the merge») и задайте порог сходства от 0 до 1: чем ближе к 1, тем строже сравнение. По умолчанию стоит 0,8. Результат загружается обратно на лист и обновляется кнопкой «Обновить всё». Способ подходит для списков в сотни и тысячи строк, но требует аккуратной проверки совпадений.
Нечёткий поиск дублей и опечаток в !SEMTools
Для списков запросов и названий в !SEMTools есть две процедуры, которые решают соседние задачи.
- «Неявные дубли». Находят и удаляют ячейки, совпадающие с другой ячейкой с точностью до порядка слов: «купить стол кухонный» и «кухонный стол купить». Опечатки они не ловят. Меню «ОБНАРУЖИТЬ» → «Дубли» (подсветка) и «УДАЛИТЬ» → «Ячейки» → «Дубли» (удаление), есть вариант с учётом словоформ. Подробнее: неявные дубли в Excel.
- «Работа с опечатками» (Pro). Группа «Web (Парсинг, SEO, PPC)» → «Семант.анализ» → «Работа с опечатками». Пункт «Является ли опечаткой?» проверяет фразу, пункт «Наиболее вероятное исправление» возвращает исправленный вариант. Процедура работает через поисковые подсказки, поэтому нужен интернет, лучше всего она справляется с фразами из 1–3 слов, за один раз обрабатывается до 500 фраз.
- Функции LEVENSTEIN и WORDDIFF для сравнения пар строк формулой входят и в бесплатный набор, список на странице возможности !SEMTools, доступные бесплатно.
Простые точные повторы ищутся без всего этого, см. повторяющиеся значения в Excel.
Почему поиск по шаблону ничего не находит
- В ВПР стоит ИСТИНА или пропущен последний аргумент: поставьте ЛОЖЬ или 0.
- Шаблон набран в простом сравнении «=» или в НАЙТИ: там подстановочные знаки не работают.
- В данных лишние пробелы или неразрывные пробелы: «кофе » не равно «кофе». Почистите данные через СЖПРОБЕЛЫ.
- Искомая звёздочка или вопрос сами оказались в тексте: поставьте перед ними тильду.
- Слово написано в другой форме: «кофемашины» не содержит «кофемашина». Сокращайте шаблон до основы: «кофемаш*».
Нечёткий поиск в Excel: частые вопросы
Есть ли в Excel нечёткий поиск?
Встроенной функции нет. Для поиска по части слова используют подстановочные знаки * и ?, для опечаток расстояние Левенштейна, для сопоставления таблиц Power Query или надстройку Fuzzy Lookup.
Как в Excel найти слово по части написания?
Поставьте звёздочки по краям слова в шаблоне: =СЧЁТЕСЛИ(A2:A100;"*кофе*") или =ВПР("*"&D2&"*";A2:B100;2;ЛОЖЬ).
Как найти похожие названия с опечатками?
Посчитайте расстояние Левенштейна до каждого значения справочника функцией LEVENSTEIN и возьмите значение с наименьшим расстоянием.
Как сопоставить два списка с разным написанием названий?
Используйте нечёткое слияние в Power Query или надстройку Fuzzy Lookup, затем проверьте совпадения вручную.
Что значит ? в формуле поиска Excel?
Вопросительный знак заменяет ровно один символ: шаблон «ко?» найдёт «кот» и «код».
Найти неявные дубли, свести похожие запросы и исправить опечатки в списках на тысячи строк можно процедурами !SEMTools, без ручной настройки формул.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: нечёткий поиск в Excel
Для поиска по части слова хватит подстановочных знаков, для опечаток нужно расстояние Левенштейна, а для двух больших списков Power Query или Fuzzy Lookup. Не забывайте, что приближённый ВПР ищет число, а не похожее слово. Другие вопросы для начинающих собраны в разделе частые вопросы по Excel.