Формула в excel если ячейка содержит текст то значение

ЕСЛИ: функция Эксель для дополнительной проверки содержимого

Здравствуйте. Сегодня учимся проверять тип содержимого ячейки и комбинировать полученную информацию с функцией ЕСЛИ . Будем определять, содержится ли в ячейке текст, число или формула и т.п.

Если вы еще не знаете, как работает функция ЕСЛИ – прочтите в этой статье и возвращайтесь сюда!

Проверка в Excel, если содержит текст

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

Получили такие результаты:

Тип проверяемого значения Результат
Число ЛОЖЬ
Дата ЛОЖЬ
Текст ИСТИНА
Число в текстовом формате ИСТИНА
Пустая ячейка ЛОЖЬ
ИСТИНА
Формула Зависит от типа вычисленного значения
Ссылка Зависит от типа значения в ячейке, на которую ссылаемся

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

Обратный результат даст функция =ЕНЕТЕКСТ(ссылка) , которая проверяет, чтобы в ячейке было нетекстовое значение:

Таблица с итогами:

Тип проверяемого значения Результат
Число ИСТИНА
Дата ИСТИНА
Текст ЛОЖЬ
Число в текстовом формате ЛОЖЬ
Пустая ячейка ИСТИНА
Непечатаемый символ ЛОЖЬ
Формула Зависит от типа вычисленного значения
Ссылка Зависит от типа значения в ячейке, на которую ссылаемся

Как будет выглядеть формула в Excel: если ячейка содержит текст, то значение, в противном случае – пустая строка.

Вот так: =ЕСЛИ(ЕТЕКСТ(A1);»Это текст»;»») . Т.е. аргументы:

  • Условие: ЕТЕКСТ(А1)
  • Значение, если это текст: строка «Это текст»
  • Значение, если это не текст: пустая строка (обозначается «»)

Функция Excel ЕЧИСЛО

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

То есть, Excel считает числом цифру, дату, формулу, вернувшую число и ссылку на число.

В Экселе нет функции, проверяющей, что это не число. Чтобы сделать такую операцию, применяем логическую функцию НЕ . Вот так: =НЕ(ЕЧИСЛО(А1)) .

Проверка чётности и нечётности значения

Чтобы определить, чётно число или нет, используем функции:

  • ЕЧЁТН – проверить чётность числа
  • ЕНЕЧЁТ – определить нечётность

Результаты работы функций:

Проверяемое значение Функция Результат
1 ЕЧЁТН(ссылка) ЛОЖЬ
2 ЕЧЁТН(ссылка) ИСТИНА
1 ЕНЕЧЁТ(ссылка) ИСТИНА
2 ЕНЕЧЁТ(ссылка) ЛОЖЬ

Если вы попытаетесь проверить нечисловые значения, функции вернут ошибку #ЗНАЧ!

Для условия, если ячейка четная, функция ЕСЛИ может быть построена так: =ЕСЛИ(ЕЧЁТН(A2);»Это чётное значение»;»») . Получим такую таблицу результатов:

Обратите внимание на особенности:

  1. Функция восприняла дату, как обычное число и выдала правильный результат
  2. Проверка текста и непечатаемого символа дали ошибку #ЗНАЧ!
  3. Число в текстовом формате (ячейка А6) было приведено к числовому и проверено, получен правильный результат
  4. Результат, когда в ячейке формула или ссылка – зависит от результата вычисления или значения в ячейке по ссылке

Проверка в Excel, если ячейка пустая

Для проверки пустоты, есть функция =ЕПУСТО() . Она дает такие результаты:

Видим, что получаем ИСТИНУ лишь тогда, когда в ячейке пустота.

Теперь напишем формулу в Эксель, если пустая ячейка, то выведем какое-то значение: =ЕСЛИ(ЕПУСТО(A2);»Пустая ячейка»;»») .

Похожая проверка в Excel, если не пусто, то формула будет такая: =ЕСЛИ(НЕ(ЕПУСТО(A2));»Ячейка заполнена»;»») .

Проверка на логическое значение

Функция ЕЛОГИЧ проверит, находится ли в ячейке ИСТИНА или ЛОЖЬ. Если это так, вернет ИСТИНУ. В противном случае – ЛОЖЬ

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

Источник: officelegko.com

Формула в excel если ячейка содержит текст то значение

Функция ЕСЛИ СОДЕРЖИТ

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

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

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

У нас есть множество строк с документами Реализации и Возвратов .

Все документы имеют свое наименование за счет уникального номера .

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

Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .

Для того, чтобы это сделать, необходимо:

    Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).

В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.



Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .

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

  • Аргумент «Диапазон» – это соответствующая ячейка с наименованием документа.
  • Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

  • В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « – »
  • Далее протягиваем формулу до конца таблицы и подключаем сводную.


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

    Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
    (для перевода по карте нажмите на VISA и далее “перевести”)

    Источник: excelspb.ru

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

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

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

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

    Поиск ячеек, содержащих текст

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

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

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

    На вкладке Главная в группе Редактирование нажмите кнопку найти _амп_и выберите пункт найти.

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

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

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

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

    В поле внутри вы можете выбрать лист или книгу , чтобы выполнить поиск на листе или во всей книге.

    Нажмите кнопку найти все или Найти далее.

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

    Примечание: Чтобы остановить поиск, нажмите клавишу ESC.

    Проверка ячейки на наличие в ней текста

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

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

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

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

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

    Примечание: Функция Поиск не учитывает регистр.

    Источник: support.office.com

    Функция ЕСЛИ() в EXCEL

    Функция ЕСЛИ() относится к наиболее часто используемым функциям.

    Синтаксис функции

    ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])

    Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. =ЕСЛИ(A1>=100;”Бюджет превышен”;”ОК!”) Т.е. если в ячейке A1 содержится значение большее или равное 100, то формула вернет ОК!, а если нет, то Бюджет превышен .

    В качестве аргументов функции, могут участвовать формулы, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше, то по столбцу С .

    Вложенные ЕСЛИ

    В EXCEL 2007 в качестве значений аргументов значение_если_истина и значение_если_ложь можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ() . =ЕСЛИ(A1>=100;”Бюджет превышен”;ЕСЛИ(A1>=90;”Крупный проект”;ЕСЛИ(A1>=50;”Средний проект”;”Малый проект “)))

    Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы:

    Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:

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

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

    =ПРОСМОТР(A24;<-1E+307;0;1E-307>;A27:A29) (см. файл примера )

    Опущен третий аргумент [значение_если_ложь]

    Третий аргумент функции не обязателен, если его опустить, то функция вернет значение ЛОЖЬ (если условие не выполнено). =ЕСЛИ(A1>100;”Бюджет превышен”) Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.

    Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

    Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;”Бюджет превышен”;”ОК!”) или (если в ячейке A1 содержится значение 0) =ЕСЛИ(A1;”Бюджет превышен”;”ОК!”) вернут ОК!

    Если в ячейке A1 находится любое другое число кроме 0, то формула вернет Бюджет превышен . Такой подход удобен, когда проверяется равенство значения нулю.

    Связь функции ЕСЛИ() с другими функциями использующие условия

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

    Функция ЕСЛИ() , как альтернативный вариант, может быть также использована для подсчета и сложения значений с использованием условий. Ниже приведены иллюстрирующие примеры.

    Пусть данные находятся в диапазоне A6:A11 (см. файл примера)

    Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;”>10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )

    Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;”>10″) . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))

    Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))

    Источник: excel2.ru

    Функция ЕСЛИ в Excel с примерами нескольких условий

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

    Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.

    Синтаксис функции ЕСЛИ с одним условием

    Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

    Разберем синтаксис функции:

    Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

    Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

    Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

    Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

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

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

    Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.

    Функция ЕСЛИ в Excel с несколькими условиями

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

    Синтаксис будет выглядеть следующим образом:

    Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

    Примеры несколько условий функции ЕСЛИ в Excel:

    Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

    В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.

    Расширение функционала с помощью операторов «И» и «ИЛИ»

    Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

    Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

    Функции И и ИЛИ могут проверить до 30 условий.

    Пример использования оператора И:

    Пример использования функции ИЛИ:

    Как сравнить данные в двух таблицах

    Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

    Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.

    Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.

    Исходные данные (таблицы, с которыми будем работать):

    Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

    В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

    Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

    Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

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

    Источник: exceltable.com

    Excel: Функция ЕСЛИ

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

    Функция Если проверяет значение логического выражения и возвращает результаты в зависимости от того, истинно оно или ложно.

    Аргументы функции Если

    Если (лог_выражение; [значение_если_истина]; [значение_если_ложь])

    1. Лог_выражение — логическое выражение

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

    Например, «A10=100» — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение Истина, в противном случае — значение Ложь.
    В этом аргументе может использоваться любой оператор сравнения .

    • > больше
    • = больше или равно
    • не равно
    1. Значение_если_истина — значение, которое помещается в ячейку (возвращается), если лог_выражение соответствует значению Истина.

    Значение может быть числом, текстом, формулой или ссылкой на ячейку.

    1. Значение_если_ложь — значение, которое возвращается, если лог_выражение соответствует значению Ложь.

    Значение может быть числом, текстом, формулой или ссылкой на ячейку.

    Обязателен только первый аргумент (логическое выражение). Если опустить второй и третий аргумент, то вместо заданных нами значений будут выводиться значения «Истина» или «Ложь» в зависимости от истинности логического выражения.

    Примеры:

    Если значение в ячейке А1 больше 5, формула возвращает значение 10, в противном случае — 20:

    2. Если (А1>=3 ; «Зачет сдал» ; «Зачет не сдал»)

    Если в ячейке А1 оценка больше или равна 3, то зачет сдан, в противном случае – нет.

    3. =Если (А1=«оплачено»; В1; 0)

    Эта формула возвращает значение из ячейки В1, если ячейка А1 содержит слово «оплачено», и 0, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

    Для создания более сложных проверок в качестве аргументов можно использовать функции Если , вложенные друг в друга.
    В Excel версий до 2003 включительно — до 7 вложений;
    вExcel версии 2007 — до 64 вложений;
    в Excel версии 2010 — до 128 вложений.

    Понравилось? Поделись с друзьями

    Семинары. Вебинары. Конференции

    Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

    Источник: lk.usoft.ru