Оператор Excel IF для частичного совпадения текста (подстановочный знак)

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

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

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

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

Предположим, что в приведенном ниже примере таблицы вы хотите проверить, содержат ли идентификаторы в первом столбце букву «A». Если найдено – вывести “Да” в столбец B, если нет – вывести “Нет”.

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

=ЕСЛИ(A2=”*a*”,”Да”,”Нет”)

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

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

=СЧЁТЕСЛИ(A2:A10, “*a*”)

Excel ЕСЛИ содержит частичный текст

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

ЕСЛИ(СЧЁТЕСЛИ(клетка“*текст*”), значение_если_истина, значение_если_ложь)

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

= ЕСЛИ (СЧЁТЕСЛИ (A2, “*a*”), “Да”, “Нет”)

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

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

=ЕСЛИ(СЧЁТЕСЛИ(A2, “??-??”), “Действительно”, “”)

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

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

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

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

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

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

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

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

ЕСЛИ(ЧИСЛО(НАЙТИ(“текст“, клетка)) значение_если_истина, значение_если_ложь)

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

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

=ЕСЛИ(ЧИСЛО(ПОИСК(“A”, A2)), “Да”, “Нет”)

Чтобы искать только заглавную букву «А» и игнорировать «а», формула такова:

=ЕСЛИ(ЧИСЛО(НАЙТИ(“A”, A2)), “Да”, “Нет”)

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

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

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

IНОМЕР(ПОИСК(“A”, A2))

Функция ПОИСК ищет указанный текст (в данном примере «А») и возвращает его позицию в строке в формате А2. Если текст не найден, возвращается ошибка #ЗНАЧ. Поскольку и ПОИСК, и НАЙТИ предназначены для выполнения типа соответствия «ячейка содержит», в этом случае подстановочные знаки на самом деле не нужны.

Функция ISNUMBER преобразует число в TRUE, а любое другое значение, включая ошибку, в FALSE. Логическое значение поступает непосредственно на логическую проверку IF. В нашем случае A2 содержит «A», поэтому ISNUMBER возвращает TRUE:

ЕСЛИ(ИСТИНА, “Да”, “Нет”)

В результате ЕСЛИ возвращает значение, установленное для значение_если_истина аргумент, который является «Да».

Оператор Excel IF OR с подстановочными знаками

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

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

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

или же

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

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

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

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

=ЕСЛИ(ИЛИ(СЧЁТЕСЛИ(A2, “*”&$D$2&”*”), СЧЁТЕСЛИ(A2, “*”&$F$2&”*”)), “Да”, “”)

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

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

=ЕСЛИ(СЧЁТ(ПОИСК({“aa”,”bb”}, A2))>0, “Да”, “”)

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

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

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

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

=ЕСЛИ(СЧЁТЕСЛИМН(A2, “*b*”, A2, “*2*”), “Да”, “”)

Другой способ — использовать формулу ЕСЛИ И вместе с ПОИСКОМ ПО НОМЕРУ:

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

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

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

=ЕСЛИ(И(ЧИСЛО(ПОИСК($D$2, A2)), НОМЕР(ПОИСК($F$2, A2))), “Да”, “”)

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

=ЕСЛИ(СЧЁТ(ПОИСК({“b”,”2″}, A2))=2, “Да”, “”)

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