Частичное совпадение текста в Excel
Обновлено: 31.10.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.
Читайте также: