Функции Excel ISTEXT и ISNONTEXT с примерами формул

В учебнике показано, как использовать функции ISTEXT и ISNONTEXT в Excel, чтобы проверить, содержит ли ячейка текстовое значение или нет.

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

Функция Excel ИСТЕКСТ

Функция ISTEXT в Excel проверяет, является ли заданное значение текстом или нет. Если значение является текстовым, функция возвращает значение TRUE. Для всех других типов данных (таких как числа, даты, пустые ячейки, ошибки и т. д.) возвращается ЛОЖЬ.

Синтаксис следующий:

ИСТЕКСТ(значение)

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

Например, чтобы узнать, является ли значение в A2 текстом или нет, используйте эту простую формулу:

=ИСТЕКСТ(A2)

Функция Excel НЕТТЕКСТ

Функция ISNONTEXT возвращает TRUE для любого нетекстового значения, включая числа, даты и время, пробелы и другие формулы, которые возвращают нетекстовые результаты или ошибки. Для текстовых значений возвращается ЛОЖЬ.

Синтаксис такой же, как у функции ISTEXT:

ИСТЕКСТ(значение)

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

=НЕТЕКСТ(A2)

Как показано на снимке экрана ниже, формулы ISTEXT и ISNONTEXT возвращают противоположные результаты:

Функции ISTEXT и ISNONTEXT в Excel — примечания по использованию

ISTEXT и ISNONTEXT — очень простые и простые в использовании функции, и вы вряд ли столкнетесь с какими-либо трудностями при их использовании. Тем не менее, есть несколько ключевых моментов, на которые следует обратить внимание:

• Обе функции являются частью группы функций IS, которые возвращают логические (логические) значения TRUE или FALSE.
• В конкретном случае, когда числа хранятся в виде текста, ИСТЕКСТ возвращает ИСТИНА, а ИСНОНТЕКСТ возвращает ЛОЖЬ.
• Обе функции доступны во всех версиях Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.

Использование ISTEXT и ISNONTEXT в Excel — примеры формул

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

Проверить, является ли значение текстом

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

формула ИСТЕКСТ:

Возвращает TRUE для любого значения, которое Excel считает текстом.

=ИСТЕКСТ(B2)

формула НЕТЕКСТ:

Возвращает TRUE для любого значения, которое Excel считает нетекстовым.

=НЕТЕКСТ(B2)

ISTEXT для проверки данных: разрешить только текст

В некоторых ситуациях может потребоваться разрешить пользователям вводить в определенные ячейки только текстовые значения. Для этого создайте правило проверки данных на основе формулы ISTEXT. Вот как:

1. Выберите одну или несколько ячеек, которые вы хотите проверить.
2. На Данные вкладка, в Инструменты данных группу, нажмите кнопку «Проверка данных».
3. На Настройки вкладка Проверка данных диалоговом окне выберите «Пользовательский» для критериев проверки и введите формулу ISTEXT в соответствующее поле.
4. Нажмите OK, чтобы сохранить правило.

В этом примере мы проверяем ответы на вопросы анкеты в ячейках с B2 по B4 с помощью этой формулы:

=ИСТЕКСТ(B2:B4)

Кроме того, вы можете настроить свои собственные Предупреждение об ошибке сообщение, чтобы объяснить вашим пользователям, какие данные принимаются:

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

Дополнительные сведения см. в разделе Использование проверки данных в Excel.

Формула Excel ЕСЛИ ИСТЕКСТ

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

Формула 1. Если текст, то

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

=ЕСЛИ(ИСТЕКСТ(A2), “Да”, “Нет”)

Формула 2. Проверка ввода ячейки

В одном из предыдущих примеров мы обсуждали, как обеспечить допустимый ввод данных пользователем с помощью проверки данных. Это также можно сделать в более «мягкой» форме с помощью формулы Excel ЕСЛИ ИСТЕКСТ.

Предположим, в анкете вы хотите определить, какие ответы действительны (текстовые), а какие нет (нетекстовые). Для этого используйте вложенные операторы IF со следующей логикой:

• Если проверяемая ячейка пуста, ничего не вернуть, т.е. пустую строку (“”).
• Если ячейка является текстом, верните «Действительный ответ».
• Если ни одно из вышеперечисленных, вернуть “Неверный ответ – пожалуйста, введите текст”.

Сложив все это вместе, мы получим следующую формулу, где B2 — это проверяемая ячейка:

=IF(B2=””, “”, IF(ISTEXT(B2), “Действительный ответ”, “Неверный ответ – введите текст.”))

Проверить, содержит ли диапазон какой-либо текст

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

Чтобы проверить весь диапазон, объедините функцию ISTEXT с СУММПРОИЗВ следующим образом:

СУММПРОИЗВ(ИСТЕКСТ(диапазон)*1)>0

СУММПРОИЗВ(–ИСТЕКСТ(диапазон))>0

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

=СУММПРОИЗВ(ИСТЕКСТ(A2:C2)*1)>0

=СУММПРОИЗВ(–ИСТЕКСТ(A2:C2))>0

Одна из приведенных выше формул помещается в ячейку D2, а затем вы перетаскиваете ее вниз через ячейку D5.

Итак, теперь у вас есть четкое представление о том, какие строки содержат одну или несколько текстовых строк (ИСТИНА), а какие содержат только числа (ЛОЖЬ).

Если вы хотите вернуть разные результаты, скажите «Да» или «Нет», а не ИСТИНА и ЛОЖЬ, заключите приведенную выше формулу в оператор IF:

=ЕСЛИ(СУММПРОИЗВ(–ИСТЕКСТ(A2:C2))>0, “Да”, “Нет”)

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

Формула основана на способности СУММПРОИЗВ изначально обрабатывать массивы. Работая изнутри наружу, вот что он делает:

• Функция ISTEXT возвращает массив значений TRUE и FALSE. Для A2:C2 мы получаем этот массив:
  {ИСТИНА, ИСТИНА, ЛОЖЬ}
• Затем мы умножаем каждый элемент приведенного выше массива на 1, чтобы преобразовать логические значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. Для той же цели можно использовать двойной унарный оператор (–). После преобразования формула принимает следующий вид:

  СУММПРОИЗВ({1,1,0})>0

• Функция СУММПРОИЗВ складывает 1 и 0, и вы проверяете, больше ли результат нуля. Если это так, диапазон содержит по крайней мере одно текстовое значение, и формула возвращает TRUE, если не FALSE.

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

Функция Excel ISTEXT может только определить, содержит ли ячейка текст, то есть абсолютно любой текст. Чтобы узнать, содержит ли ячейка определенную текстовую строку, используйте формулу ПОИСК ЕСЛИЧИСЛО или СЧЁТЕСЛИ с подстановочными знаками.

Например, чтобы увидеть, содержит ли идентификатор элемента в A2 текстовую строку, введенную в ячейку D2, используйте приведенную ниже формулу (обратите внимание на абсолютную ссылку $D$2, которая предотвращает изменение адреса ячейки при копировании формулы в другие ячейки):

=ISNUMBER(ПОИСК($D$2, A2))

Для удобства обернем его в функцию ЕСЛИ:

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

И получить следующие результаты:

Тот же результат может быть достигнут с COUNTIF:

=ЕСЛИ(СЧЁТЕСЛИ(A2, “*”&$D$2&”*”)>0, “Да”, “Нет”)

Дополнительные примеры см. в разделе Excel. Если ячейка содержит формулы.

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

Функцию ISTEXT также можно использовать с условным форматированием Excel для выделения ячеек, содержащих текстовые значения. Вот как:

1. Выберите все ячейки, которые вы хотите проверить, и выделите (в этом примере A2: C5).
2. На Дом вкладка, в Стили группа, нажмите Новое правило > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
3. в Форматировать значения, где эта формула верна введите следующую формулу:

   =ИСТЕКСТ(A2)

   Где A2 — самая левая ячейка выбранного диапазона.

4. Нажмите на Формат кнопку и выберите нужное форматирование.
5. Дважды щелкните OK, чтобы закрыть оба диалоговых окна и сохранить правило.

Более подробное объяснение каждого шага см. в разделе Использование формул для условного форматирования Excel.

В результате Excel выделяет все ячейки с любыми текстовыми строками:

Вот как можно использовать функции ISTEXT и ISNONTEXT в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Доступные загрузки

Примеры формул Excel ISTEXT и ISNONTEXT

Вас также могут заинтересовать