В excel знак содержит: Проверьте, содержит ли ячейка текст (с чувствительностью к делу)

Содержание

Формула Эксель ЕСЛИ ячейка содержит текст то значение равно (условия с текстом)


Автор Амина С. На чтение 5 мин Опубликовано

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

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

  1. Условие. Может выступать арифметическим знаком или логической функцией. В любом случае, главное требование к условию – оно должно возвращать значение ИСТИНА или ЛОЖЬ.
  2. Значение, если ИСТИНА. Это текст, который выводится или функция, которая будет выполняться в случае истинности значения. Допустимо использование нескольких вложенных функций ЕСЛИ для того, чтобы выполнять многоуровневые проверки.
  3. Значение, если ЛОЖЬ. Это текст, функция или значение любого другого типа, которое будет показываться или выполняться в случае, если описанный в первом аргументе критерий не соответствует действительности.

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

Содержание

  1. Проверка условия для полного совпадения текста (ЕСЛИ + СОВПАД)
  2. Функция ЕСЛИ с частичным совпадением текста
  3. ЕСЛИ + ПОИСК
  4. ЕСЛИ + НАЙТИ

Проверка условия для полного совпадения текста (ЕСЛИ + СОВПАД)

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

В целом, для проверки соответствия переменной текстовой строке необходимо использовать логический оператор =. Общая функция выглядит следующим образом: =ЕСЛИ(G2=»выполнено»,ИСТИНА,ЛОЖЬ).

В этом случае строки могут быть записаны в разном регистре, и этот фактор не будет учитываться в функции ЕСЛИ. А что делать, если нужно проверить именно на разность регистров букв? Для этого вместо оператора = нужно использовать функцию СОВПАД, которая проверяет, являются ли текстовые строки идентичными.

Не стоит забывать заключать все текстовые значения в кавычки.

Сама функция работает следующим образом: она смотрит на текстовую строку 1, текстовую строку 2. Если они полностью совпадают, то она передает в ячейку или в аргумент функции значение ИСТИНА. Если же в результате анализа строк обнаруживается хотя бы одно различие, функция возвращает значение ЛОЖЬ.

Чтобы передать функцию СОВПАД функции ЕСЛИ, необходимо её использовать в качестве первого аргумента. Например, формула может иметь следующий вид: =ЕСЛИ(СОВПАД(G2,»Выполнено»),»Да»,»Нет»). Как мы видим в этом примере, эта формула понимает, что строки «ВЫПОЛНЕНО» и «выполнено» не являются идентичными.

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

Функция ЕСЛИ с частичным совпадением текста

Ранее мы разобрались, как сравниваются строки на предмет полного соответствия. Но в некоторых ситуациях достаточно лишь частичного совпадения строки с эталоном. Например, нам нужен город, но какой именно – не столь важно. Самый простой метод, как это сделать – использовать операторы ? и *. Но это хорошая идея далеко не во всех ситуациях.

ЕСЛИ + ПОИСК

Один из самых надежных способов добиться поставленной задачи – использование функции ПОИСК в качестве аргумента функции ЕСЛИ. С её помощью пользователь может определить порядковый номер знака, который начинает отсчёт строки. В общем виде эта функция выглядит следующим образом: =ПОИСК(что_ищем, где_ищем, начиная_с_какого_символа_ищем).

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

Эта функция должна использоваться совместно с ещё одним оператором. Называется он ЕЧИСЛО. Его задача – проверка, является ли определённая переменная числом. Если да, ячейке или функции передаётся значение ИСТИНА. Она может использоваться, чтобы проверить, является ли конкретная переменная текстовой. Вот, как ее использовать для наших целей: =ЕСЛИ(ЕЧИСЛО(ПОИСК(«город»,B2)),»Город»,»»).

Эта функция работает по следующему алгоритму: она проверяет, является ли значение, возвращенное функцией поиска, числовым. Если да, то возвращаемое значение – «Город». Логика очень простая. Дело в том, что эта функция также фильтрует любые другие типы данных, кроме текстовых. Поскольку функция «Поиск» может возвращать и ошибку в случае ненахождения соответствующего числа, то функция ЕЧИСЛО дает возможность его отфильтровать. Если оказывается, что функция ЕЧИСЛО говорит, что была выдана ошибка, то в качестве аргумента передается пустое значение.

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

ЕСЛИ + НАЙТИ

Если же пользователю нужно сравнивать строки и по регистру, то тогда используется другая функция – НАЙТИ. Она работает абсолютно аналогичным образом, только только учитывает большие и малые буквы. Ее синтаксис включает следующие аргументы:

  1. Что ищем.
  2. Где осуществляется поиск.
  3. С какой позиции ищется нужный текст.

Следовательно, и использование формулы будет абсолютно аналогичным. =ЕСЛИ(ЕЧИСЛО(ПОИСК(«город»,B2)),»Город»,»»). Как видим, функция ЕСЛИ дает возможность осуществлять самые разнообразные операции. Это один из главных операторов Excel, который позволяет автоматизировать почти все, что только можно.

Оцените качество статьи. Нам важно ваше мнение:

Использование операторов строк и подстановочных знаков

В формулах можно использовать оператор строки (&), чтобы сцепить или объединить две строки или несколько строк либо содержимое ячеек, на которые указывают ссылки. Подстановочные знаки (*, ?, ~) можно использовать в условиях для замены одного или нескольких символов.

Сцепление строк или содержимого ячеек

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

Ниже приведено несколько примеров использования оператора сцепления.

  • «Абв»&»Где» возвращает «АбвГде».

  • «Абв»&A1 возвращает «Абв2», если ячейка A1 содержит 2.

  • A1&A2 возвращает «12», если ячейка A1 содержит 1, а ячейка A2 содержит 2.

  • B2&»; «&E2 возвращает «Последний; Первый», если ячейка B2 содержит «Последний», а ячейка E2 содержит «Первый».

Использование подстановочных знаков для подстанвоки одиночных символов

Символ «?» используется для подстановки одиночного символа в выражении, допускающем применение условий.

Ниже приведено несколько примеров использования символа «?» в различных вариантах подстановки.

  • «Ка?» представляет любую строку, которая начинается с «Ка» и содержит ровно один дополнительный символ, например «Ка2» или «Каж».

  • «Эт??» представляет любую строку, которая начинается с «Эт» и содержит ровно два дополнительных символа, например «Этих» или «Этим».

  • СЧЕТЕСЛИ(B2:E7;»?ол») возвращает количество ячеек в диапазоне B2:E7, содержащих значение, которое начинается с одиночного символа, после которого следуют символы «ол», например «кол» и «вол». Такие слова, как «скол» или «ствол», не соответствуют этому условию.

Использование подстановочных знаков для подстановки любого количества символов

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

Ниже приведено несколько примеров использования подстановочного знака «*» в различных вариантах подстановки.

  • «*ег» представляет строку любой длины, оканчивающуюся на «ег», например «бег» или «снег».

  • СЧЕТЕСЛИ(B2:E7;»*ек») возвращает количество ячеек в диапазоне B2:E7, содержащих значение, которое оканчивается на «ек», например «человек» или «век». Слово «веко» не соответствует этому условию.

Полное соответствие подстановочному символу

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

Ниже приведено несколько примеров использования символа «~» в различных вариантах подстановки.

  • «~?» представляет вопросительный знак, а не подстановочный символ, используемый для подстановки любого одиночного символа.

  • СЧЕТЕСЛИ(E;»~*») возвращает количество ячеек в столбце E, содержащих символ звездочки.

  • ПОИСК(«~?»;B2) возвращает 11, если ячейка B2 содержит «Это вопрос? Да!», поскольку вопросительный знак является 11‑м символом в строке.

Использование нескольких подстановочных символов в условии

Подстановочные символы (?, *, ~) могут использоваться вместе в выражениях, допускающих применение условий. Ниже приведено несколько примеров.

  • «*а?» представляет любое выражение, содержащее символ «а», за которым следует любой другой одиночный символ, например «смак», «шар» и «кочан».

  • СЧЕТЕСЛИ(B2:E7;»*од?») возвращает количество ячеек в диапазоне B2:E7, содержащих значение, которое начинается с любого набора символов (в том числе пустого), после которого следуют символы «од», а затем одиночный символ. Этому сочетанию символов соответствуют слова «вода», «колода», «одр» и «вроде». При этом слова «лодка» или «провод» не соответствуют данному условию, так как в первом случае после сочетания «од» следуют два символа, а во втором случае после сочетания «од» нет ни одного символа.

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

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

Как использовать формулу Excel: «Если ячейка содержит»

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

Формула Excel: если ячейка содержит

Общая формула 

 =ЕСЛИ(ЧИСЛО(ПОИСК("abc",A1)),A1,"") 

Резюме 

Чтобы проверить наличие ячеек, которые содержат определенный текст, вы можете использовать формулу, которая использует функцию ЕСЛИ вместе с функциями ПОИСК и ЕЧИСЛО. В показанном примере формула в C5 выглядит следующим образом:

 =ЕСЛИ(ЧИСЛО(ПОИСК("abc",B5)),B5,"") 

Если вы хотите проверить, содержит ли ячейка A1 текст «Пример», вы можете запустить формулу, которая будет выводить «Да» или «Нет» в ячейке B1 . Есть несколько способов использовать эти формулы. На момент написания Excel может возвращать следующие варианты:

  • Если ячейка содержит любое значение
  • Если ячейка содержит текст
  • Если ячейка содержит число
  • Если ячейка содержит определенный текст
  • Если ячейка содержит определенную текстовую строку
  • Если ячейка содержит одну из множества текстовых строк
  • Если ячейка содержит несколько строк

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

Объяснение. Если ячейка содержит

Одним из ограничений функции ЕСЛИ является то, что она не поддерживает подстановочные знаки Excel, такие как «?» а также «*». Это просто означает, что вы не можете использовать IF сам по себе для проверки текста, который может появиться в любом месте ячейки.

Одним из решений является формула, использующая функцию ЕСЛИ вместе с функциями ПОИСК и ЕСЛИЧИСЛО. Например, если у вас есть список адресов электронной почты и вы хотите извлечь те, которые содержат «ABC», используйте следующую формулу:

 =ЕСЛИ(ЧИСЛО(ПОИСК("abc",B5)),B5," "). Предполагая, что клетки доходят до B5 

Если где-либо в ячейке B5 встречается «abc», функция ЕСЛИ возвращает это значение. В противном случае ЕСЛИ вернет пустую строку («»). Логическим тестом этой формулы является следующий бит:

 ISNUMBER(SEARCH("abc",B5)) 

Прочитать статью: Эффективность Excel: 11 формул Excel для повышения производительности

Использование формул «если ячейка содержит» в Excel

Приведенные ниже руководства были написаны с использованием последней версии Microsoft Excel 2019 для Windows 10. Некоторые шаги могут отличаться, если вы используете другую версию или платформу. Свяжитесь с нашими специалистами, если вам нужна дополнительная помощь.

1. Если ячейка содержит какое-либо значение, вернуть значение

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

  1. Выберите выходную ячейку и используйте следующую формулу: =ЕСЛИ(ячейка<>«», значение_для_возврата, «») .
  2. В нашем примере мы хотим проверить ячейку с номером 9.0017 A2 , а возвращаемое значение будет No . В этом сценарии вы должны изменить формулу на =IF(A2<>«», «No», «») .
  3. Поскольку ячейка A2 не пуста, формула вернет « No » в выходной ячейке. Если проверяемая ячейка пуста, выходная ячейка также останется пустой.

2. Если ячейка содержит текст/число, верните значение

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

Проверка текста
  1. Чтобы проверить, содержит ли ячейка текст, выберите выходную ячейку и используйте следующую формулу: =ЕСЛИ(ISTEXT(ячейка), возвращаемое_значение, «») .
  2. В нашем примере мы хотим проверить ячейку A2 , и возвращаемое значение будет Да . В этом сценарии вы должны изменить формулу на =ЕСЛИ(ИСТЕКСТ(A2), «Да», «») .
  3. Поскольку ячейка A2 содержит текст, а не число или дату, формула вернет « Да ” в выходную ячейку.
Проверка числа или даты
  1. Чтобы проверить, содержит ли ячейка число или дату, выберите выходную ячейку и используйте следующую формулу: =ЕСЛИ(ЧИСЛО(ячейка), возвращаемое_значение, «») .
  2. В нашем примере мы хотим проверить ячейку D2 , и возвращаемое значение будет Да . В этом сценарии вы должны изменить формулу на =ЕСЛИ(ЧИСЛО(D2), «Да», «») .
  3. Поскольку ячейка D2 содержит число, а не текст, формула вернет « Да » в выходную ячейку.

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

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

  1. Выберите выходную ячейку и используйте следующую формулу: =IF(cell=»text», value_to_return, «») .
  2. В нашем примере мы хотим проверить ячейку с номером 9.0017 A2 , текст, который мы ищем, — « пример », а возвращаемое значение будет Да . В этом сценарии вы должны изменить формулу на =IF(A2=»example», «Yes», «») .
  3. Поскольку ячейка A2 содержит текст « пример », формула вернет « Да » в выходную ячейку.

4. Если ячейка содержит определенный текст, верните значение (с учетом регистра)

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

  1. Выберите выходную ячейку и используйте следующую формулу: =ЕСЛИ(ИСЧИСЛ(ячейка,»текст_с учетом регистра»), «значение_для_возврата», «») .
  2. В нашем примере ячейка, которую мы хотим проверить, — это A2 , текст, который мы ищем, — « EXAMPLE », а возвращаемое значение будет Да . В этом сценарии вы должны изменить формулу на =ЕСЛИ(ИСПОЛНИТЬ(A2,»ПРИМЕР»), «Да», «») .
  3. Поскольку ячейка A2 содержит текст « EXAMPLE » с соответствующим регистром формула вернет « Да » в выходную ячейку.

5.

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

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

  1. Выберите выходную ячейку и используйте следующую формулу: =ЕСЛИ(ячейка=»текст», «», «значение_для_возврата») .
  2. В нашем примере мы хотим проверить ячейку с номером 9.0017 A2 , искомый текст — « пример », а возвращаемое значение будет No . В этом сценарии вы должны изменить формулу на =IF(A2=»example», «», «No») .
  3. Поскольку ячейка A2 содержит текст « пример », формула вернет пустую ячейку. С другой стороны, другие ячейки возвращают « No » в выходную ячейку.

6. Если ячейка содержит одну из множества текстовых строк, вернуть значение

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

  1. Выберите выходную ячейку и используйте следующую формулу: =ЕСЛИ(ИЛИ(ЧИСЛО(ПОИСК(«строка1», ячейка)), ЧИСЛО(ПОИСК(«строка2», ячейка))), возвращаемое_значение, «») .
  2. В нашем примере мы хотим проверить ячейку A2 . Мы ищем « tshirt » или « hoodie », и возвращаемое значение будет 9.0017 Действительно . В этом сценарии вы должны изменить формулу на =ЕСЛИ(ИЛИ(ЧИСЛО(ПОИСК(«футболка»,A2)),ЧИСЛО(ПОИСК(«толстовка»,A2))),»Действительно «,»») .
  3. Поскольку ячейка A2 содержит одно из искомых текстовых значений, формула вернет « Valid » в выходную ячейку.

Чтобы расширить формулу для большего количества условий поиска, просто измените ее, добавив больше строк, используя ISNUMBER(ПОИСК(«строка», ячейка)) .

7. Если ячейка содержит несколько текстовых строк из множества, верните значение

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

  1. Выберите выходную ячейку и используйте следующую формулу: =ЕСЛИ(И(ЧИСЛО(ПОИСК(«строка1»,ячейка)), ЧИСЛО(ПОИСК(«строка2″,ячейка))), возвращаемое_значение,»») .
  2. В нашем примере мы хотим проверить ячейку A2 . Мы ищем « hoodie » и « black », возвращаемое значение будет Valid . В этом сценарии вы должны изменить формулу на =ЕСЛИ(И(ЧИСЛО(ПОИСК(«толстовка»,A2)),ЧИСЛО(ПОИСК(«черный»,A2))),»Действительный «,»») .
  3. Поскольку ячейка A2 содержит оба искомых текстовых значения, формула вернет « Valid » в выходную ячейку.

Заключительные мысли

Мы надеемся, что эта статья помогла вам научиться использовать формулы «если ячейка содержит» в Microsoft Excel. Теперь вы можете проверить, содержат ли какие-либо ячейки значения, текст, числа и многое другое. Это позволяет вам эффективно перемещаться, манипулировать и анализировать данные.

Мы рады, что вы дочитали статью до этого места 🙂 Спасибо 🙂

Пожалуйста, поделитесь ею в социальных сетях. Кто-то еще выиграет.

Перед уходом

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

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

Вам также может понравиться

» Как использовать функцию КПЕР в Excel
» Как разделить имя и фамилию в Excel
» Как рассчитать анализ безубыточности в Excel

Проверить, содержит ли ячейка текст (без учета регистра) )

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно. .. Меньше

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

Вы также можете использовать фильтр для поиска текста. Дополнительные сведения см. в разделе Фильтрация данных.

Найти ячейки, содержащие текст

Выполните следующие действия, чтобы найти ячейки, содержащие определенный текст:

  1. Выберите диапазон ячеек, которые вы хотите найти.

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

  2. org/ListItem»>

    На вкладке Главная в группе Редактирование щелкните Найти и выбрать , а затем щелкните Найти .

  3. В поле Найти введите текст или числа, которые необходимо найти. Или выберите недавний поиск из раскрывающегося списка Find what .

    Примечание. В критериях поиска можно использовать подстановочные знаки.

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

  5. Щелкните Параметры , чтобы уточнить параметры поиска. Например, вы можете искать все ячейки, содержащие данные одного типа, например формулы.

    В В пределах , вы можете выбрать Лист или Рабочая книга для поиска рабочего листа или всей книги.

  6. Нажмите Найти все или Найти далее .

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

Примечание. Чтобы отменить текущий поиск, нажмите ESC.

Проверить, есть ли в ячейке текст

Для выполнения этой задачи используйте функцию ISTEXT .

Проверить, соответствует ли ячейка определенному тексту

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

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