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

Обновлено: 20.11.2024

Пытаетесь создать оператор IF с подстановочным символом, но каждый раз терпит неудачу? Проблема не в вашей формуле, а в самой функции - Excel IF не поддерживает подстановочные знаки. Однако есть способ заставить его работать для частичного совпадения текста, и это руководство научит вас, как это сделать.

Всякий раз, когда вы хотите выполнить частичное или нечеткое сопоставление в Excel, наиболее очевидным решением будет использование подстановочных знаков. Но что, если конкретная функция, которую вам нужно использовать, не поддерживает подстановочные знаки? К сожалению, Excel IF является одной из таких функций. Это особенно разочаровывает, учитывая, что другие "условные" функции, такие как СЧЁТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИМН, отлично работают с подстановочными знаками.

К счастью, это не препятствие, которое может остановить творческого пользователя Excel :) Комбинируя ЕСЛИ с другими функциями, вы можете заставить его оценивать частичное совпадение и получить хорошую альтернативу подстановочной формуле ЕСЛИ в Excel.

Почему функция ЕСЛИ в Excel не работает с подстановочным знаком

В приведенном ниже образце таблицы предположим, что вы хотите проверить, содержат ли идентификаторы в первом столбце букву "A". Если найдено - вывести "Да" в столбец B, если нет - вывести "Нет".

Кажется, включение подстановочного текста в логический тест было бы простым решением:

Но, к сожалению, это не работает. Формула возвращает «Нет» для всех ячеек, даже для тех, которые содержат «А»:

Почему оператор IF с подстановочным знаком не работает? Судя по всему, Excel не распознает подстановочные знаки, используемые со знаком равенства или другими логическими операторами. Присмотревшись к списку функций, поддерживающих подстановочные знаки, вы заметите, что их синтаксис предполагает, что подстановочный текст появляется непосредственно в аргументе, подобном этому:

Инструкция Excel IF с подстановочным знаком

Теперь, когда вы знаете, почему формула ЕСЛИ с подстановочным знаком не работает, давайте попробуем выяснить, как заставить ее работать. Для этого мы просто встроим функцию, которая принимает подстановочные знаки в логическом тесте ЕСЛИ, а именно функцию СЧЁТЕСЛИ:

При таком подходе ЕСЛИ не имеет проблем с пониманием подстановочных знаков и безошибочно идентифицирует ячейки, содержащие либо «А», либо «а» (поскольку СЧЁТЕСЛИ не чувствителен к регистру):

Эта формула помещается в ячейку B2 или любую другую ячейку в строке 2, а затем вы можете перетащить ее вниз на любое количество ячеек:

Это решение также можно использовать для поиска строк определенного шаблона. Предполагая, что допустимы только идентификаторы, состоящие из 2 групп по 2 символа, разделенных дефисом, вы можете использовать «??-??» подстановочная строка для их идентификации:

Как работает эта формула:

Для логической проверки ЕСЛИ мы используем функцию СЧЁТЕСЛИ, которая подсчитывает количество ячеек, соответствующих указанной строке с подстановочными знаками. Поскольку диапазон критериев представляет собой одну ячейку (A2), результат всегда равен 1 (совпадение найдено) или 0 (совпадение не найдено). Учитывая, что 1 соответствует TRUE, а 0 соответствует FALSE, формула возвращает "Действительно" (value_if_true), когда счетчик равен 1, и пустую строку (value_if_false), когда счетчик равен 0.

Формула IF ISNUMBER SEARCH для частичных совпадений

Еще один способ заставить Excel IF работать с частичным совпадением текста — включить в логическую проверку функцию НАЙТИ или ПОИСК. Разница в том, что ПОИСК чувствителен к регистру, а ПОИСК - нет.

Итак, в зависимости от того, хотите ли вы считать строчные и прописные буквы одними и теми же или разными символами, подойдет одна из следующих формул:

Формула без учета регистра для частичного совпадения:

Формула с учетом регистра для частичного совпадения:

Поскольку обе функции предназначены для выполнения типа "ячейка содержит", подстановочные знаки в этом случае на самом деле не нужны.

Например, для обнаружения идентификаторов, содержащих "A" или "a", используется следующая формула:

=IF(ISNUMBER(ПОИСК("A", A2)), "Да", "Нет")

Чтобы искать только заглавную букву "А" и игнорировать "а", используйте следующую формулу:

=IF(ISNUMBER(НАЙТИ("A", A2)), "Да", "Нет")

В B6 на снимке экрана ниже вы можете наблюдать разницу в результате:

Как работает эта формула:

В основе формулы лежит комбинация ISNUMBER и ПОИСК (или НАЙТИ):

Функция ЕЧИСЛО преобразует число в ИСТИНА, а любое другое значение, включая ошибку, в ЛОЖЬ. Логическое значение поступает непосредственно на логическую проверку IF. В нашем случае A2 содержит «A», поэтому ISNUMBER возвращает TRUE:

В результате ЕСЛИ возвращает значение, установленное для аргумента value_if_true, то есть "Да".

Excel оператор ЕСЛИ ИЛИ с подстановочными знаками

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

Например, чтобы найти "aa" ИЛИ "bb" в A2 без учета регистра букв и вернуть "Да", если они будут найдены, используйте одну из следующих формул:

=ЕСЛИ(ИЛИ(ЧИСЛО(ПОИСК("aa", A2)), ЧИСЛО(ПОИСК("bb", A2))), "Да", "")

=ЕСЛИ(ИЛИ(СЧЁТЕСЛИ(A2, "*aa*"), СЧЁТЕСЛИ(A2, "*bb*")), "Да", "")

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

=ЕСЛИ(СЧЁТЕСЛИ(A3, "*aa*") + СЧЁТЕСЛИ(A3, "*bb*"), "Да", "")

Вместо жесткого кодирования строк с подстановочными знаками в формуле их можно вводить в отдельные ячейки, скажем, D2 и F2, как показано на снимке экрана ниже. Обратите внимание, что эти ссылки на ячейки заблокированы знаком $, чтобы формула правильно копировалась в следующие ячейки:

Приведенные выше формулы хорошо работают для 2 частичных совпадений, но если вы ищете 3 или более совпадений, они станут слишком длинными. В этом случае имеет смысл подойти к задаче иначе:

Подать несколько подстрок в функцию ПОИСК в константе массива, подсчитать возвращенные числа и проверить, больше ли результат нуля (что будет означать, что найдена хотя бы одна из подстрок):

Таким образом, вы получите точно такой же результат с более компактной формулой:

Формула Excel ЕСЛИ И с подстановочными знаками

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

Предположим, вы хотите найти ячейки в столбце A, которые содержат как "b", так и "2". Для этого используйте "*b*" и "*2*" для критериев COUNTIFS и A2 для диапазона критериев:

Еще один способ – использовать формулу ЕСЛИ И вместе с ПОИСКОМ ПО НОМЕРУ:

=ЕСЛИ(И(ЧИСЛО(ПОИСК("b", A2)), ЧИСЛО(ПОИСК("2", A2))), "Да", "")

Хотя в этой формуле не используются подстановочные знаки, она работает так же, как поиск двух строк с подстановочными знаками ("*b*" и "*2*") в одной ячейке.

Конечно, ничто не мешает вам ввести значения поиска в предопределенные ячейки, в нашем случае D2 и F2, и предоставить ссылки на ячейки для формулы:

=ЕСЛИ(И(ЧИСЛО(ПОИСК($D$2, A2)), ЧИСЛО(ПОИСК($F$2, A2))), "Да", "")

Если вы предпочитаете использовать более компактные формулы, где это возможно, вам может больше понравиться подход с константами массива. Формула IF COUNT SEARCH очень похожа на предыдущий пример, но поскольку на этот раз обе подстроки должны появиться в A2, мы проверяем, равно ли количество 2:

Это основные методы использования подстановочных знаков в операторе IF в Excel. Если вы знаете какие-либо другие решения, другие пользователи обязательно оценят, если вы поделитесь своим опытом в комментариях. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Всякий раз, когда вы работаете с широким диапазоном данных на листе, частичное или нечеткое совпадение — эффективный способ быстро найти совпадение. Если вы хотите выполнить частичную совпадающую строку, самое простое решение — использовать подстановочные знаки. В Excel есть много опций, таких как ВПР, XПР, ИНДЕКС с ПОИСКПОЗОМ, которые объединяют ЕСЛИ с другими функциями для выполнения этой задачи. Сегодня мы узнаем, как выполнить частичное совпадение строки в Excel.

Загрузить практическую рабочую тетрадь

Загрузите этот практический лист, чтобы практиковаться в выполнении задачи, пока вы читаете эту статью.

Частичное соответствие строки в Excel: 5 методов

Частичное совпадение строки в Excel можно выполнить разными способами, используя одну функцию или несколько функций одновременно. В этой статье мы изучим 5 различных способов сделать это.

1.Операторы IF OR для частичного совпадения строки

Функция «ЕСЛИ» не поддерживает подстановочные знаки. Но комбинация IF с другими функциями может быть использована для частичного совпадения строки. Давайте учиться.

Шаг 1:

В следующем примере у нас есть таблица данных, в которой имена некоторых кандидатов указаны в столбце «Имя». Нам нужно идентифицировать имена, которые содержат одну из текстовых строк, указанных в столбцах 2 и 3. Это означает, что нам нужно найти имена, которые содержат букву «A» или «L».

Шаг 2:

В столбце "Статус" в ячейке "E4" примените формулу ЕСЛИ ИЛИ. Формат этой формулы:

=ЕСЛИ(ИЛИ(ЧИСЛО(ПОИСК(текст,ячейка)),ЧИСЛО(ПОИСК(текст,ячейка))),"значение_если_истина", "значение_если_ложь")

Вставьте значения в формулу. Окончательная формула для частичного совпадения:

Нажмите «Ввод». Формула определила строку частичного соответствия.

Шаг 3:

Теперь примените эту формулу к остальным ячейкам, чтобы получить окончательный результат.

2. IF ISNUMBER SEARCH Формула частичного совпадения строки

Мы можем найти результаты, содержащие частичные совпадения строк, используя комбинацию IF ISNUMBER SEARCH.

Шаг 1:

Рассмотрите набор данных, содержащий столбцы «Имя», «Строка соответствия», «Статус». Нам нужно идентифицировать имена, содержащие строку частичного совпадения, из столбца «Строка совпадения».

Шаг 2:

Примените формулу IF ISNUMBER SEARCH в столбце "Статус" в ячейке D4

=IF(ISNUMBER(ПОИСК("текст", ячейка)), значение_если_истина, значение_если_ложь)

Вставьте значения. Окончательная формула для строки частичного совпадения:

Нажмите «Ввод». Наш результат достигнут.

Шаг 3:

Теперь примените эту формулу ко всем ячейкам в столбце, чтобы узнать все результаты, содержащие строку частичного совпадения.

3. ВПР для частичного совпадения строки

Теперь мы воспользуемся функцией ВПР для частичного совпадения строки.

Шаг 1:

Рассмотрите таблицу, в которой указаны имена некоторых кандидатов и их ранги.

Шаг 2:

Скопируйте заголовки столбцов и вставьте их где-нибудь на листах. Там мы выполним нашу задачу.

Шаг 3:

Примените функцию ВПР в ячейке F4. Формула

Искомое_значение: $E$5&”*”. Мы используем звездочку (*) в качестве подстановочного знака, который соответствует нулю или более текстовым строкам.
Table_array равен $B$4:$C$9.
Col_index_num
[range_lookup]: нам нужно точное совпадение (FALSE)

Нажмите «Ввод». Формула выполнила частичное совпадение строки.

Шаг 4.

Теперь примените одну и ту же формулу 2 или более раз, чтобы освоить эту функцию.

4. XLOOKUP для частичного совпадения строки

XLOOKUP с ISNUMBER также может дополнить строку частичного совпадения

Шаг 1:

В следующем примере даны две таблицы. В первой таблице строки частичного совпадения имеют ранг. Нам нужно идентифицировать имена во второй таблице, содержащей строки с частичным соответствием, а затем вернуть ранг, связанный с этими именами.

Шаг 2.
В ячейке F4 примените формулу. Формат этой формулы:

=XLOOKUP(искомое_значение,ISNUMBER(ПОИСК(текст,ячейка)),return_array)

Вставьте значения в формулу.

Искомое_значение равно TRUE.
Текст: $B$4:$B$9.
Ячейка – E4 (Генри Джонатан). Формула вернет ранг Генри Джонатана
Return_array: $C$4:$C$9.

Нажмите «Ввод». Формула успешно возвращает ранг имени, содержащему строки частичного совпадения.

Шаг 3:

Теперь сделайте то же самое для всех ячеек.

5. INDEX с MATCH для частичного совпадения строки

Используя функцию ИНДЕКС с ПОИСКПОЗОМ, мы можем вернуть текст, содержащий строку частичного совпадения.

Шаг 1:

В следующем примере даны две таблицы. В первой таблице приведены «Имя» и «Звание» некоторых кандидатов. Во второй таблице дана строка частичного совпадения. Нам нужно идентифицировать имена в первой таблице, содержащей строки частичного совпадения.

В столбце F4 примените ИНДЕКС с формулой ПОИСКПОЗ. Формула:

Нажмите «Ввод». У нас есть Имя «Роббен», которое содержит строку частичного совпадения (Роб)

Шаг 3:

Звездочку (*) можно использовать с обеих сторон ячейки, если у вас есть символы с обеих сторон строки частичного совпадения. Предположим, у нас есть строка частичного совпадения «ni». У него есть подстановочные знаки с обеих сторон, теперь мы будем использовать эту звездочку (*) с обеих сторон ячейки. Итак, формула будет,

Нажмите «Ввод». Наш результат здесь.

Быстрые заметки

✅ Функция XLOOKUP доступна только в Excel 365. Эту функцию могут использовать только пользователи Excel 365.

✅Функция ВПР всегда ищет значения поиска в крайнем левом верхнем столбце справа. Эта функция «Никогда» ищет данные слева.

✅Звездочка (*) используется в качестве подстановочного знака.Используйте его с обеих сторон строки частичного соответствия, если вам нужны подстановочные знаки с обеих сторон.

Заключение

Выполнение частичного совпадения строки в Excel обсуждается в этой статье с использованием пяти различных методов. Надеюсь, эта статья будет вам полезна, когда вы столкнетесь с проблемами. Вы можете поделиться своими мыслями, если у вас возникли какие-либо вопросы.

Как мы можем найти приблизительное совпадение, используя подстановочные знаки с функцией ЕСЛИ Excel?

Можно ли использовать подстановочные знаки в части логической проверки функции IF?

Кажется, что это простое "да", но, к сожалению, это не так.

Подстановочные знаки хорошо работают с такими функциями, как СУММЕСЛИМН, СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН; к сожалению, они не работают с функциями ЕСЛИ.

Почему они не работают с функциями ЕСЛИ? Давайте рассмотрим причину и посмотрим, сможем ли мы найти обходной путь.

ПРИМЕЧАНИЕ. Если вам интересно узнать об использовании подстановочных знаков с функцией СУММЕСЛИМН, перейдите по ссылке ниже на сообщение «СУММ EXCEL на основе частичного совпадения текста».

В приведенном ниже наборе данных мы хотим написать формулу в столбце B, которая будет искать текст в столбце A. Наша формула будет искать в тексте столбца A текстовую последовательность «AT», и если она будет найдена, отобразить «AT» в столбце. Б.

Неважно, где в тексте столбца A встречаются буквы «AT», нам нужно видеть «AT» в соседней ячейке столбца B. Если буквы «AT» не встречаются в тексте столбца A , формула в столбце B ничего не должна отображать.

Начнем с выбора ячейки B5 и ввода следующей формулы ЕСЛИ.

Обратите внимание, что формула ничего не возвращает, хотя текст в ячейке A5 содержит последовательность букв «AT».

Причина сбоя в том, что Excel плохо работает при использовании подстановочных знаков сразу после знака равенства в формуле.

Любая функция, использующая знак равенства в логическом тесте, не любит использовать подстановочные знаки таким образом.

«А как насчет функции СУММЕСЛИМН?», — слышу я ваш вопрос.

Если вы исследуете функцию СУММЕСЛИМН, подстановочные знаки не идут сразу после знака равенства; вместо этого они приходят после ссоры. Например:

Подстановочный знак не появляется сразу после знака равенства.

Первое, что нам нужно понять, это синтаксис функции ПОИСК. Синтаксис следующий:

ПОИСК(найти_текст, внутри_текста, [начальный_номер])

find_text — обязательный аргумент, определяющий искомый текст.
within_text — обязательный аргумент, определяющий текст, в котором вы хотите найти значение, указанное в find_text
Начальный_номер — это аргумент опции, определяющий номер/позицию символа в аргументе внутри_текста, с которого вы хотите начать поиск. Если этот параметр опущен, позиция начального символа по умолчанию равна 1 (первый символ слева от текста)

Чтобы проверить, правильно ли работает функция поиска, давайте проведем простой тест со следующей формулой.

Нам возвращается позиция символа, в которой буквы «AT» были обнаружены функцией ПОИСК. Первый ПОИСК нашел буквы «АТ», начинающиеся с позиции 1-го символа текста. Следующее открытие было в позиции 5-го символа, а последнее открытие было в позиции 4-го символа.

Мы можем использовать эту новую информацию, чтобы определить, существует ли текст «AT» в строках сопутствующего текста. Если мы видим какое-либо число в качестве ответа, мы знаем, что «AT» существует в текстовой строке. Если мы получаем ответ об ошибке, мы знаем, что текст «AT» не существует в текстовой строке.

ПРИМЕЧАНИЕ. Функция ПОИСК НЕ учитывает регистр. Поиск букв «AT» найдет «AT», «At», «aT» и «at». Если вы хотите искать текст и различать разные регистры (с учетом регистра), используйте функцию НАЙТИ. Функция НАЙТИ работает так же, как ПОИСК, но с учетом регистра.

Интересной функцией в Excel является функция ISNUMBER. Функция ISNUMBER предназначена для возврата «True», если что-то является числом, и «False», если что-то не является числом. Синтаксис следующий:

ISNUMBER(значение)

значение — обязательный аргумент, определяющий проверяемые данные. Значение может относиться к ячейке, формуле или имени, которое относится к ячейке, формуле или значению.

Давайте добавим функцию ISNUMBER к логике нашей предыдущей функции SEARCH.

Любая ячейка, содержащая числовой ответ, теперь имеет значение «Верно», а любая ячейка, содержащая ошибку, теперь имеет значение «Ложь».

Теперь мы можем использовать функции ISNUMBER/SEARCH в качестве оператора подстановки в исходной функции ЕСЛИ.

Это отличный способ выполнить логические тесты в функциях, не допускающих использование подстановочных знаков.

Предположим, мы хотим найти два разных набора текста («AT» или «DE») и вернуть слово «Europe», если в искомом тексте будет обнаружена любая из этих текстовых строк. Мы объединим исходную формулу с функцией ИЛИ для поиска нескольких текстовых строк.

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

Excel, если ячейка содержит частичный текст

Формула ниже проверит, содержит ли ячейка частичный текст. Он вернет «Да», если он содержит частичный текст, и «Нет», если не найден.

Эта формула проверяет частичный текст в ячейке A2 и возвращает «Да», если он найден, возвращает «Нет», если он не найден. Вы можете вернуть True, False, если требуется, как показано в данной формуле.

Excel, если ячейка содержит подстановочный знак частичного текста

Ниже приведены полезные подстановочные знаки, которые помогают сопоставить частичный текст в формулах Excel и очень полезны для проверки того, содержит ли ячейка частичный текст в Excel.

Звездочка (*)

Вы можете использовать звездочку (*) для соответствия нулю или более символов. Посмотрите примеры ниже:

1. Формула для проверки Excel, если ячейка содержит частичный текст, который должен заканчиваться частичным текстом
=IF(COUNTIF(A2,"*PartialText"),TRUE,FALSE)

Здесь * – это подстановочный знак, предшествующий частичному тексту.

2. Формула для проверки Excel на наличие в ячейке частичного текста, который должен начинаться с частичного текста
=IF(COUNTIF(A2,"PartialText*"),TRUE,FALSE)

Здесь * – это подстановочный знак, следующий за частью текста.

3. Формула для проверки Excel, если ячейка содержит частичный текст, который содержит частичный текст в любой позиции в ячейке
=IF(COUNTIF(A2,"*PartialText*"),TRUE,FALSE)

Здесь * – это подстановочный знак, следующий за неполным текстом и предшествующий ему.

Вопросительный знак (?) – один символ

Вы можете использовать вопросительный знак (?) для соответствия любому символу. Посмотрите примеры ниже:

4. Формула для проверки Excel, если ячейка содержит неполный текст, ячейка должна заканчиваться частичным текстом и только перед любым одним символом.
=ЕСЛИ(СЧЁТЕСЛИ(A2;"?PartialText"),ИСТИНА,ЛОЖЬ)

Поделитесь этой историей, выберите платформу!

Проверить, содержит ли диапазон ячеек определенный текст

Оставить комментарий Отменить ответ

Вы должны войти в систему, чтобы оставить комментарий.

Мы предоставляем бесплатные онлайн-учебники, формулы, шаблоны, информационные панели и макросы, чтобы преуспеть в Microsoft Excel. Мы охватываем базовые и продвинутые концепции и делимся творческими идеями, советами по Excel и ярлыками с помощью связанных файлов примеров. Вы можете ознакомиться с множеством примеров, посвященных новейшим инструментам и методам в приложениях Microsoft Excel.

Читайте также: