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

Обновлено: 31.10.2024

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

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

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

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

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

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

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

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

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

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

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

Excel IF оператор с подстановочный знак

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

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

Формула подстановочного знака IF для идентификации строк определенного шаблона

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

Для логической проверки ЕСЛИ мы используем функцию СЧЁТЕСЛИ, которая подсчитывает количество ячеек, соответствующих указанной строке с подстановочными знаками. Поскольку диапазон критериев представляет собой одну ячейку (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)), "Да", "Нет")

Excel IF формула для частичное совпадение

В 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, как показано на снимке экрана ниже. Обратите внимание, что эти ссылки на ячейки заблокированы знаком $, чтобы формула правильно копировалась в следующие ячейки:

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

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

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

Более компактная альтернатива в Excel формула подстановочного знака ЕСЛИ ИЛИ

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

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

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

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

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

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

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

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

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

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

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

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

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

Как выполнить частичное совпадение строки в Excel (5 методов)

Всякий раз, когда вы работаете с широким диапазоном данных на листе, частичное или нечеткое совпадение — эффективный способ быстро найти совпадение. Если вы хотите выполнить частичную совпадающую строку, самое простое решение — использовать подстановочные знаки. В 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(ПОИСК("текст", ячейка)), значение_если_истина, значение_если_ложь)

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

применение формулы

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

result

Шаг 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.

применение формулы

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

result

Шаг 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 для проверки наличия в ячейке частичного текста. Вы можете использовать эту формулу, чтобы определить, что ячейка содержит частичный текст, а также подсчитать, суммировать и выполнить дальнейшую обработку.

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.

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