Частичное совпадение текста в Excel представляет собой метод, позволяющий пользователю находить и обрабатывать данные, которые соответствуют только определённой части целого текста. Этот инструмент особенно полезен, когда необходимо выполнить поиск по огромным массивам данных, и когда точное совпадение не всегда возможно или удобно. Учитывая многообразие форматов и содержание используемых данных, частичное совпадение в Excel становится незаменимым помощником в повседневной работе с таблицами.
Ситуации, когда требуется частичное совпадение текста, могут возникать при анализе списков, где нужно находить элемент, содержащий определённые ключевые слова, но не обязательно в точной формулировке.
Например, при работе с клиентскими базами или списками товаров в интернет-магазинах пользователи могут захотеть извлекать записи, в которых фигурирует только часть искомого наименования или описания.
Это позволяет значительно сократить время на поиск и обработку информации, особенно если в таблице содержится большое количество данных.
- Подстановочные знаки Excel
- Как определить частичное совпадение при помощи подстановочных знаков
- Определить частичное совпадение при помощи ПОИСК и НАЙТИ
- ВПР по частичному совпадению текста
- ПОИСКПОЗ Частичное совпадение с подстановочными знаками
- ИНДЕКС ПОИСКПОЗ по частичному совпадению
- Как определить есть ли частичное совпадение текста в ячейках
Подстановочные знаки Excel
В Microsoft Excel подстановочный знак — это особый вид символа, который может заменить любой другой символ. Другими словами, когда вы не знаете точный символ, вы можете использовать подстановочный знак в этом месте.
Два распространенных подстановочных знака, которые распознает Excel, — это звездочка (*) и вопросительный знак (?). Тильда (~) заставляет Excel обрабатывать эти символы как обычные, а не подстановочные знаки.
Подстановочные знаки пригодятся в любой ситуации, когда вам нужно определить частичное совпадение текстовых значений. Вы можете использовать их в качестве критериев сравнения для фильтрации данных, для поиска записей, имеющих некоторую общую часть, или для выполнения нечеткого соответствия в формулах.
Звездочка как подстановочный знак
Звездочка (*) — это наиболее общий подстановочный знак, который может представлять любое количество символов. Например:
- к*- соответствует любому слову, которое начинается с «к», например кот, капуста, кит, и т. д.
- *к- заменяет любую текстовую строку, заканчивающуюся на «к», например лук, маяк, совок, и т. д.
- *к*- представляет любое слово, содержащее «к» в любой позиции, например кот, лук, арка, и т. д.
Вопросительный знак как подстановочный знак
Вопросительный знак (?) представляет любой отдельный символ . Он может помочь вам получить более точные данные при поиске частичного совпадения. Например:
- ? - соответствует любой записи, содержащей один символ, например, «a», «1», «-» и т. д.
- ?? - заменяет любые два символа, например, «ко», «11», «a6» и т. д.
- ???-??? - представляет собой любой текст, содержащий 2 группы по 3 символа, разделенных дефисом, например ABC-DEF, АБВ-123, 111-222, и т. д.
Тильда как подстановочный знак-аннулировщик
Тильда (~), помещенная перед подстановочным знаком, отменяет действие подстановочного знака и превращает его в буквальную звездочку (~*), буквальный вопросительный знак (~?) или буквальную тильду (~~). Например:
- *~?- находит любую запись, заканчивающуюся вопросительным знаком, например Что?,Сколько стоит?, и т. д.
- *~** - находит любые данные, содержащие звездочку, например*1, *11*, 1-мар-2024* и т. д. В этом случае первая и третья звездочки являются подстановочными знаками, а вторая обозначает буквальный символ звездочки.
Как определить частичное совпадение при помощи подстановочных знаков
Почему не работает формула ЕСЛИ
Всякий раз, когда вы хотите определить частичное или нечеткое сопоставление в Excel, наиболее очевидным решением является использование подстановочных знаков.
Пытаетесь построить условие ЕСЛИ с подстановочным текстом, но каждый раз терпите неудачу? Проблема не в вашей формуле, а в самой функции. ЕСЛИ не поддерживает подстановочные символы.
Это особенно разочаровывает, учитывая, что другие «условные» функции, такие как СЧËТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ, прекрасно работают с подстановочными знаками.
В примере таблицы ниже, предположим, вы хотите проверить, содержат ли идентификаторы в первом столбце букву «A». Если найдено — вывести «Да» в столбце B, если нет — показать «Нет».
Кажется, включение подстановочного текста в логический тест было бы простым решением:
=ЕСЛИ(A2= "*a*";"Да"; "Нет")
Но, к сожалению, это не работает. Формула возвращает "Нет" для всех ячеек, даже тех, которые содержат "A":
По всей видимости, Excel не распознает подстановочные знаки, используемые со знаком равенства или другими логическими операторами.
К счастью, это не препятствие, которое может остановить креативного пользователя Excel :) Объединив функцию ЕСЛИ с другими функциями, вы можете заставить ее оценивать частичное совпадение.
Формула ЕСЛИ для частичного совпадения
Теперь, когда вы знаете причину, по которой формула ЕСЛИ с подстановочными знаками не работает, давайте попробуем выяснить, как заставить ее работать. Для этого мы просто встроим функцию, которая принимает подстановочные знаки, в логическую проверку ЕСЛИ, например функцию СЧËТЕСЛИ:
ЕСЛИ(СЧЁТЕСЛИ(ячейка; "*текст*"); значение_если_истина; значение_если_ложь)
Здесь функция СЧЁТЕСЛИ выясняет, сколько раз определенный текст встречается в ячейке. Чтобы подсчитать частичные совпадения, вы помещаете подстановочный знак (*) с обеих сторон текста. Если полученное количество больше нуля, то ЕСЛИ возвращает значение_если_истина, в противном случае - значение_если_ложь.
Предположим, у вас есть список заказов в столбце A, и вы хотите определить заказы с буквой «A». Задачу можно выполнить с помощью этой формулы:
=ЕСЛИ(СЧЁТЕСЛИ(A2; "*a*");"Да"; "Нет")
При таком подходе функция ЕСЛИ без проблем распознает подстановочные знаки и безупречно определяет ячейки, содержащие либо «A», либо «a» (поскольку функция СЧËТЕСЛИ не чувствительна к регистру):
Вместо того чтобы жестко кодировать условие в формуле, вы можете записать его в отдельную ячейку (E1) и сослаться на эту ячейку в вашей формуле:
=ЕСЛИ(СЧЁТЕСЛИ(A2; "*"&$E$1&"*");"Да"; "Нет")
Для корректной работы формулы обязательно зафиксируйте адрес ячейки, содержащей строку, знаком $ ( абсолютная ссылка на ячейку ).
Это решение также можно использовать для поиска по определенному шаблону. Предполагая, что действительны только коды заказа, состоящие из 2 групп по 2 символа, разделенные дефисом, вы можете использовать подстановочную строку "??-??" для их идентификации:
=ЕСЛИ(СЧЁТЕСЛИ(A2; "??-??"); "Верно"; "")
Определить частичное совпадение при помощи ПОИСК и НАЙТИ
Другой способ определить частичное совпадение текста — включить функции ПОИСК или НАЙТИ в логический тест.
Функция ПОИСК используется для определения позиции первого символа подстроки в строке, тогда как функция НАЙТИ аналогична, но при этом учитывает регистр символов.
Итак, разница в том, что НАЙТИ чувствительна к регистру, а ПОИСК — нет.
Если нужный текст найден в ячейке, то эти функции возвращают позицию первого символа частичного совпадения текстовых строк. Если совпадение не найдено, возвращается ошибка #ЗНАЧ!.
Итак, в зависимости от того, хотите ли вы рассматривать строчные и прописные буквы как одинаковые или как разные символы, одна из этих формул подойдет идеально:
Формула для частичного совпадения без учета регистра :
ЕСЛИ(ЕЧИСЛО(ПОИСК("текст";ячейка)); значение_если_истина; значение_если_ложь)
Формула для частичного совпадения с учетом регистра :
ЕСЛИ(ЕЧИСЛО(НАЙТИ("текст"; ячейка)); значение_если_истина; значение_если_ложь)
Поскольку обе функции предназначены для выполнения сопоставления типа «ячейка содержит», подстановочные знаки в данном случае не нужны.
Например, для определения кода заказа, содержащего «A» или «a», формула выглядит следующим образом:
=ЕСЛИ(ЕЧИСЛО(ПОИСК("A"; A2)); "Да"; "")
Чтобы искать только заглавную букву «А» и игнорировать «а», используйте следующую формулу:
=ЕСЛИ(ЕЧИСЛО(НАЙТИ("A"; A2)); "Да"; "")
На скриншоте ниже вы можете наблюдать разницу в результате:
Как работает эта формула:
В основе формулы лежит комбинация функций ЕЧИСЛО и ПОИСК (или НАЙТИ):
ЕЧИСЛО(ПОИСК("A"; A2))
Разберем пошагово:
- Функция ПОИСК ищет текстовую строку и, если строка найдена, возвращает позицию первого символа, в противном случае — ошибку #ЗНАЧ!.
- Функция ЕЧИСЛО проверяет, был ли поиск успешным или нет. Если ПОИСК вернул какое-либо число, ЕЧИСЛО возвращает ИСТИНА. Если поиск приводит к ошибке, ЕЧИСЛО возвращает ЛОЖЬ.
- Наконец, функция ЕСЛИ возвращает указанное значение «Да» для ячеек, имеющих значение ИСТИНА в логической проверке, в противном случае — пустую строку ("").
Вы можете указать текстовую строку не в самой формуле, а в отдельной ячейке. Вот как это будет выглядеть:
=ЕСЛИ(ЕЧИСЛО(ПОИСК($E$1; A2)); "Да"; "")
ВПР по частичному совпадению текста
Теперь рассмотрим, как можно производить поиск значений по частичному совпадению.
Как и многие другие формулы, функция ВПР Excel может использовать следующие подстановочные знаки:
- Знак вопроса(?) для соответствия любому отдельному символу.
- Звездочка(*) соответствует любой последовательности символов.
Подстановочные знаки оказываются действительно полезными во многих ситуациях:
- Когда вы не помните точный текст, который ищете.
- Когда вы ищете текстовую строку, являющуюся частью содержимого ячейки.
- Когда столбец поиска содержит лишние начальные или конечные пробелы. В этом случае вы можете поломать голову, пытаясь понять, почему обычная формула ВПР не работает.
Предположим, вы хотите найти определенного клиента в приведенной ниже базе данных. Вы не помните точно фамилию, но уверены, что она начинается с «ack».
Чтобы вернуть фамилию из столбца A, используйте следующую подстановочную формулу ВПР:
=ВПР("ков*"; $A$2:$B$10; 1; ЛОЖЬ)
Из предыдущих примеров вы уже знаете, что можно объединить амперсанд (&) и ссылку на ячейку, чтобы создать строку поиска.
Для нашего примера вы можете выполнить ВПР по частичному совпадению текста следующим образом:
=ВПР(E1&"*"; $A$2:$B$10; 1; ЛОЖЬ)
Искомый фрагмент текста записан в E1
Чтобы получить соответствующий табельный номер из столбца B, используйте это (разница только в номере индекса столбца):
=ВПР(E1&"*"; $A$2:$B$10; 2; ЛОЖЬ)
На рисунке ниже показаны результаты:
Чтобы найти значение, содержащее заданный символ(ы) в любой позиции, поставьте амперсанд до и после ссылки на ячейку.
=ВПР("*"&E1&"*"; $A$2:$B$10; 1; ЛОЖЬ)
Ниже приведено еще несколько формул ВПР с подстановочными знаками.
Найдите фамилию, заканчивающуюся на «ин»:
=ВПР("*ин"; $A$2:$B$10; 1; ЛОЖЬ)
Получите имя, которое начинается с «Си» и заканчивается на «ов»:
=ВПР("си*ов"; $A$2:$B$10; 1; ЛОЖЬ)
Найдите фамилию из 5 символов:
=ВПР("?????"; $A$2:$B$10; 1; ЛОЖЬ)
Примечания:
- Для корректной работы формулы ВПР для частичного совпадения с подстановочными знаками, укажите в аргументах формулы, что нужно использовать точное совпадение (последний аргумент — ЛОЖЬ).
- Если найдено более одного совпадения, возвращается только первое из них .
ПОИСКПОЗ Частичное совпадение с подстановочными знаками
Также вы можете искать частичное совпадение при помощи функции ПОИСКПОЗ.
Как и многие другие функции, ПОИСКПОЗ понимает подстановочные знаки :
- Вопросительный знак (?) — заменяет любой отдельный символ
- Звездочка (*) — заменяет любую последовательность символов.
Примечание. Подстановочные знаки можно использовать только в формулах ПОИСКПОЗ саргументом тип_соответствия равным 0.
Формула ПОИСКПОЗ с подстановочными знаками полезна в ситуациях, когда вы хотите сопоставить не всю текстовую строку, а только некоторые символы или часть строки. Чтобы проиллюстрировать это, вернемся к данным предыдущего примера.
Найдем позицию в списке, в которой есть частичное совпадение искомого текста и табельного номера.
=ПОИСКПОЗ("*"&E1&"*"; $A$2:$A$10;0)
В поиске частичного совпадения функцией ПОИСКПОЗ с использованием подстановочных знаков вы можете использовать все те же правила и советы, которые мы давали выше в предыдущих разделах этой статьи.
Также для приблизительного поиска формулой ПОИСКПОЗ вы можете использовать не подстановочные знаки, а функции НАЙТИ и ПОИСК.
В нашем случае это может выглядеть так:
=ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(НАЙТИ(E1;$A$2:$A$10));0)
В результате мы нашли ту же 6-ю позицию на основе частичного совпадения.
В итоге здесь самым важным и существенным является то, что, зная номер позиции, в которой обнаружено частичное совпадение, мы можем извлечь любое нужное нам значение из ячеек в этой строке.
Для этого используется комбинация функций ИНДЕКС+ПОИСКПОЗ. О преимуществах этой формулы мы говорили ранее в статье ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР.
ИНДЕКС ПОИСКПОЗ по частичному совпадению
Используя те же условные данные, мы реализуем поиск частичного совпадения в формуле ИНДЕКС ПОИСКПОЗ.
Чтобы получить табельный номер, в котором встречается текст «1у», используем эту формулу:
=ИНДЕКС(B2:B10;ПОИСКПОЗ("*"&E1&"*";$B$2:$B$10;0))
Также мы можем извлечь из таблицы соответствующие этой позиции другие данные: например, фамилию.
=ИНДЕКС(A2:A10;ПОИСКПОЗ("*"&E1&"*";$B$2:$B$10;0))
Обратите внимание, что мы извлекаем данные слева от столбца поиска, что невозможно при помощи формулы ВПР. Перед вами – очередной пример так называемого «левого ВПР», о котором мы подробно рассказали здесь: 4 способа, как сделать левый ВПР в Excel.
Результаты вы видите на скриншоте ниже.
Как определить есть ли частичное совпадение текста в ячейках
Ранее мы рассматривали случаи, когда было точно известно, какой текст мы ищем в частичном совпадении. Однако бывают случаи, когда нужно просто сравнить ячейки в двух столбцах и определить, если ли в них хотя бы частично совпадающий текст.
Как определить есть ли в двух текстовых ячейках одна и та же подстрока?
Например в тексте разных ячеек указан номер телефона. Как определить, что обе ячейки содержат один и тот же телефон или разные номера?
В одних случаях это может быть один символ, а в некоторых – много символов, поэтому в формуле придётся указывать какое количество общих символов идущих подряд в одном и том же порядке нужно найти в текстах двух ячеек.
Если сравниваемые текстовые строки находятся в ячейках A2 и B2, можно предложить такие формулы:
=СУММ(ЕСЛИОШИБКА(НАЙТИ(ПСТР(A2; СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A2)-C2+1)); C2); B2); 0))>0
Эта формула чувствительна к регистру символов и различает строчные и прописные буквы, так как в ней использована функция НАЙТИ.
Вот другой вариант, в котором регистр не важен:
=СЧЁТ(ПОИСК(ПСТР(A2;СТРОКА(A$1:ИНДЕКС(A:A;ДЛСТР(A2)-C2+1));C2);B2))>0
В столбце С указываем, какое количество совпадающих символов мы ищем.
Чтобы было легче понять результат частичного совпадения, мы можем определить, начиная с какой ячейки в Текст2 символы совпадают. Для этого можно использовать
=ОБЪЕДИНИТЬ(",";ИСТИНА;ЕСЛИОШИБКА(ПОИСК(ПСТР(A2;СТРОКА(A$1:ИНДЕКС(A:A;ДЛСТР(A2)-C2+1));C2);B2);""))
Также обратите внимание, что в строке 4 мы имеем разные результаты, так как первая буква в слове «шел» различается по регистру.
Важное замечание! Эти формулы являются формулами массива, поэтому в Excel2019 и более ранних версиях нужно использовать Ctrl+Shift+Enter при вводе.
Таким образом, используя рекомендации и формулы, приведенные в этой статье, можно углубить понимание текстовых данных и эффективно находить частичные совпадения в ячейках Excel.
