Формулы эксель если в эксель: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Содержание

ЕСЛИ (функция ЕСЛИ) — Служба поддержки Майкрософт

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

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

Например, функция =ЕСЛИ(C2=»Да»;1;2) означает следующее: ЕСЛИ(С2=»Да», то вернуть 1, в противном случае вернуть 2).

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

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

Например:

Имя аргумента	Описание
лог_выражение (обязательно)	Условие, которое нужно проверить.
значение_если_истина (обязательно)	Значение, которое должно возвращаться, если *лог_выражение* имеет значение ИСТИНА.
значение_если_ложь (необязательно)	Значение, которое должно возвращаться, если *лог_выражение* имеет значение ЛОЖЬ.

Имя аргумента

Описание

лог_выражение (обязательно)

Условие, которое нужно проверить.

значение_если_истина (обязательно)

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

значение_если_ложь (необязательно)

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Простые примеры функции ЕСЛИ

=ЕСЛИ(C2=»Да»;1;2)

В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)

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

В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст «Превышение бюджета», в противном случае вернуть текст «В пределах бюджета»)

На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение «Фактические» больше значения «Плановые», то вычесть сумму «Плановые» из суммы «Фактические», в противном случае ничего не возвращать).

В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = «Да», то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)

Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: «Текст»). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.

Распространенные неполадки

Проблема	Возможная причина
0 (ноль) в ячейке	Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.
#ИМЯ? в ячейке	Как правило, это указывает на ошибку в формуле.

Проблема

Возможная причина

0 (ноль) в ячейке

Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.

#ИМЯ? в ячейке

Как правило, это указывает на ошибку в формуле.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Функция ЕСЛИ — вложенные формулы и типовые ошибки

Функция УСЛОВИЯ

Использование ЕСЛИ с функциями И, ИЛИ и НЕ

СЧЁТЕСЛИ

Рекомендации, позволяющие избежать появления неработающих формул

Общие сведения о формулах в Excel

Функция ЕСЛИ в Excel. Примеры (с несколькими условиями)

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

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

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

Содержание

Функция ЕСЛИ в Excel (общие сведения)
Синтаксис функции ЕСЛИ с одним условием
Синтаксис функции ЕСЛИ с несколькими условиями
Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”
Функция ЕСЛИ с условием «И»
Функция ЕСЛИ с условием «ИЛИ»
Сравнение данных в двух таблицах
Функция СУММЕСЛИ в Excel
Функция СУММЕСЛИМН в Excel
Функция СЧЁТЕСЛИ в Excel
Функция СЧЕТЕСЛИМН в Excel

Функция ЕСЛИ в Excel (общие сведения)

Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:

Проверить всю колонку А на предмет наличия четных чисел.
Если четное число обнаружено, сложить такие-то значения.
Если четное число не обнаружено, то выдать надпись «не обнаружено».
Проверить получившееся число, является ли оно четным.
Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

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

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

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

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

Давайте рассмотрим синтаксис более подробно:

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

Вот пример для наглядности.

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

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

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

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

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

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

Чтобы было более наглядно, вот синтаксис.

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

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

Вот пример.

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

Как видим, здесь было добавлено еще одно условие, но принцип не изменился. Так можно сразу несколько критериев проверять.

Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”

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

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

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

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

Функция ИЛИ работает аналогичным образом, но в этом случае достаточно истинности только одного из условий. Максимально так можно осуществить проверку до 30 условий.

Вот варианты, как можно применять функции И и ИЛИ как аргумент функции ЕСЛИ.

Сравнение данных в двух таблицах

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

Для сравнения двух таблиц используется функция СЧЕТЕСЛИ. Давайте рассмотрим ее более подробно.

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

Наша таблица выглядит так.

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

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

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

Нужно убедиться, что адреса абсолютные (со значком доллара перед названием строки и колонки). После формулы нужно добавить =0, чтобы Excel искал точные значения.

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

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

Функция СУММЕСЛИ в Excel

А теперь перейдем к разновидностям функции ЕСЛИ, которые помогут выполнить заменить сразу два пункта алгоритма. Первая из них – СУММЕСЛИ, которая складывает два числа, которые соответствуют определенному условию. Например, перед нами стоит задача определить, сколько денег нужно платить в месяц всем продавцам. Для этого необходимо.

Добавить ряд с общими доходами всех продавцов и нажать по ячейке, в которой будет находиться результат после ввода формулы.
Находим кнопку fx, которая находится рядом со строчкой для формул. Далее появится окошко, где посредством поиска можно отыскать необходимую функцию. После выбора оператора нужно нажать кнопку «ОК». Но всегда возможен ручной ввод.
11
Далее появится окно ввода аргументов функции. Все значения можно указать в соответствующих полях, а диапазон ввести через кнопку рядом с ними.
12
Первый аргумент – диапазон. Сюда вводятся ячейки, которые нужно проверять на предмет соответствия критериям. Если говорить про нас – это должности работников. Вводим диапазон D4:D18. Или же просто осуществить выделение интересующих ячеек.
В поле «Критерий» следует ввести должность. В случае с нами – «продавец». Как диапазон суммирования, указываем те ячейки, где перечислены заработные платы сотрудников (это делается как вручную, так и выделить их с помощью мыши). Нажимаем «ОК», и получаем готовую посчитанную заработную плату всех сотрудников, являющихся продавцами.

Согласитесь, что это очень удобно. Не так ли?

Функция СУММЕСЛИМН в Excel

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

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

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

Рассмотрим главные аргументы более подробно:

Диапазон суммирования. Ячейки, которые будут суммироваться.
Диапазон условия 1 – диапазон, который будет проверяться на предмет соответствия определенному критерию.
Условие 1 – непосредственно условие.
Диапазон условия 2 – второй диапазон, который будет проверяться на предмет соответствия критерию.
Условие 2 – второе условие.

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

Функция СЧЁТЕСЛИ в Excel

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

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

Функция СЧЕТЕСЛИМН в Excel

Аналогично формуле СУММЕСЛИМН, эта формула считает количество ячеек, которые подпадают под несколько условий. Синтаксис похож, но немного отличается от формулы СУММЕСЛИМН:

Диапазон условия 1. Это диапазон, который будет проверяться на соответствие первому критерию.
Условие 1. Непосредственно первый критерий.
Диапазон условия 2. Это диапазон, который будет проверяться на соответствие второму критерию.
Условие 2.
Диапазон условия 3.

И так далее.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ЕСЛИ + ПОИСК

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

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

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

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

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

ЕСЛИ + НАЙТИ

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

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

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

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

ЕСЛИ И в Excel — voxt

ЕСЛИ И в формуле Excel

Если And в excel — это две самые разные функции, которые обычно вложены вместе, в простой функции If мы можем проверить только один критерий, но если мы используем функцию логического И, мы можем протестировать более одного критерия и дать результат на основе критериев , они используются следующим образом = ЕСЛИ (Условие 1 И Условие 2, Значение, если истинно, Значение, если Ложь).

Объяснил

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Синтаксис

Как использовать формулу ЕСЛИ И И в Excel? (с примерами)

Вы можете скачать этот шаблон Excel IF AND Formula здесь — IF AND Formula Excel Template

Пример # 1

Рассмотрим следующую таблицу, чтобы понять работу функции ЕСЛИ И.

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

Функция, используемая для выполнения проверки:

= ЕСЛИ (И (B2

Ниже приведен снимок экрана с условием, примененным к таблице для выполнения оценки.

Нажмите Enter, чтобы получить ответ.

Перетащите формулу, чтобы найти ответ для всех квартир.

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

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

Пример # 2

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

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

= ЕСЛИ (И (B2> = 200, C2 = «A»), D2 * 10%, D2 * 5%)

Нажмите Enter и посмотрите окончательный результат приведенного выше примера.

Перетащите формулу, чтобы найти бонус для всех сотрудников.

Объяснение: — Критерии для расчета бонуса выполняются, если условие для количества полученных заказов больше, чем равное 300, и производительность равна «A». На основании результатов функция ЕСЛИ выполняет следующие действия:

Если оба условия выполнены, функция И истинна, тогда полученный бонус рассчитывается как заработная плата, умноженная на 10%.
Если одно или оба условия оказываются ЛОЖНЫ по функции И, то бонус рассчитывается как заработная плата, умноженная на 5%.

Пример # 3

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

Ссылаясь на таблицу данных, у нас есть пять акций с соответствующими финансовыми коэффициентами, такими как ROCE, ROE, Долг к собственному капиталу и отношение PE. Используя эти параметры, мы будем анализировать акции, которые будут иметь лучший инвестиционный горизонт для лучшего роста. Итак, давайте применим условие, чтобы получить результат.

Как видно из приведенной выше таблицы, у нас есть акции и их соответствующие данные о параметрах, которые мы будем использовать для проверки условия инвестирования в подходящие акции.

Синтаксис, используемый в таблице ниже: —

= ЕСЛИ (И (B2> 18%, C2> 20%, D2

Нажмите Enter и посмотрите окончательный результат приведенного выше примера.

Перетащите формулу, чтобы найти Критерии инвестирования.

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

Если все четыре критерия, упомянутые в функции AND, проверены и удовлетворены, функция IF вернет текстовую строку «Invest».
Если одно или несколько из четырех условий или все четыре условия не удовлетворяют функции AND, функция IF будет возвращать пустые строки.

То, что нужно запомнить

Комбинация функций ЕСЛИ И И не делает различий между нечувствительными к регистру текстами, состоящими из прописных и строчных букв.
Функцию И можно использовать для вычисления до 255 условий ИСТИНА или ЛОЖЬ, а общая длина формулы не должна превышать 8192 символа.
Текстовые значения или пустые ячейки предоставляются в качестве аргумента для проверки условий в, функция И будет проигнорирована.
Функция И вернет «# ЗНАЧ!» если при оценке условий логический выход не обнаружен.

—

MS Office 2007: Microsoft Excel

Сравнение числа

Введем в ячейку А1 формулу =7>5. Она вернет значение ИСТИНА. Скопируем содержимое А1 в А2 и исправим в А2 формулу: =3>5. Эта формула вернет значение ЛОЖЬ. Правые части обеих формул представляют собой высказывания, т.е. утверждения, относительно которых можно заключить, верны они или нет.

Рассмотрим другой пример. Введем в ячейку А4 число 2, а в ячейку В4 формулу =А4>3. Формула возвращает значение ЛОЖЬ. Введем в А4 число 6. Формула возвращает значение ИСТИНА. В В4 записан предикат, т.е. высказывание с переменными (в данном случае переменная одна). В зависимости от значения переменных предикат может принимать значения ИСТИНА и ЛОЖЬ. В этом примере формула как бы дает ответ на вопрос: «Число (или результат вычислений по формуле), хранящееся в ячейке А4, превышает 3?» В зависимости от значения А4 ответ будет ДА (ИСТИНА) или НЕТ (ЛОЖЬ).

В формуле =А4>3 ее составные части (А4 и 3) можно считать арифметическими выражениями, только очень простыми. Более сложный пример: =(А4 А2-1)>(2 *А4+1). В этом выражении скобки можно опустить, потому что арифметические операции имеют более высокий приоритет, чем операции сравнения, но скобки придают формуле наглядность.

Операции сравнения можно свести в таблицу (Таблица 4).

Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками: > и =. Причина в том, что на клавиатуре нет знака≥.

Высказывание и предикат имеют общее название — логическое выражение. Имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции (НЕ, И, ИЛИ).

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

Задача 5.1.

В ячейке А1 (с именем z) запишите любое число. Выясните, принадлежит ли оно отрезку [2, 5].

Решение задачи

Присвоим ячейке А1 имя z (ФормулыПрисвоить Имя). Введем в А1 число 3. Для того чтобы z принадлежал отрезку [2, 5], нужно, чтобы одновременно были истинны два предиката: z≥2 и z≤5.B ячейке В1 разместим формулу =И(z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В ячейке В1 получим значение ИСТИНА.

Следует предостеречь от неверного ввода формулы: =2<=/<=5. Введите эту формулу в С1 и убедитесь, что возвращается ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности.

Задача 5.2.

В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (∞,2) или (5,∞).

Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z > 5. В ячейке D1 поместите формулу =ИЛИ (z<2; z>5). А1 содержит число 3, поэтому формула возвращает ЛОЖЬ.

Задачу можно было решить иначе с учетом того обстоятельства, что на рабочем листе есть формула проверки принадлежности числа z отрезку [2, 5]. Упомянутые два луча составляют на числовой оси дополнение к этому отрезку. Введем в ячейку Е1 формулу =НЕ(В1). Убедитесь, вводя в ячейку А1 различные числа, что формулы в ячейках D1 и Е1 дают идентичные результаты.

На практике «в чистом виде» логические выражения, как правило, не используются. Логическое выражение используется в функции ЕСЛИ:

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

При вызове встроенной функции ЕСЛИ открывается диалоговое окно (рис. 88), имеющее три аргумента. В окне Лог_выражение записывается логическое выражение. Во втором аргументе Значение_если _истина записывается выражение, которое будет вычислено, если лог_выражение возвращает значение ИСТИНА, а в третьем аргументе — выражение, вычисляемое, если лог_выражение возвращает ЛОЖЬ.

Задача 5.3.

1. Введем в ячейку А2 формулу, которая возвращает z+1, если z >1, и z в противном случае: =ЕСЛИ (z>l; z + 1; z). (В Мастере функций ЕСЛИ находится в категории «Логические», также как функции И, ИЛИ, НЕ).

2. Если z>60, то в ячейке В2 выводить сообщение «Превышено пороговое значение», в противном случае выводить z:

=ЕСЛИ(z>60;»Превышено пороговое значение»;z)

Обратите внимание, что текст в формулах вводится в кавычках.

3. Если z е [10,25], то возвращать z, если z < 10, то возвращать

если z > 25, то возвращать 25. Выражение для этого условия будет выглядеть примерно следующим образом (запишем формулу в С2):

=ЕСЛИ(z<10;10;ЕСЛИ(z<=2 5;z;25))

Теперь попробуйте менять значение z в ячейке А1, следя за тем, как меняются значения в ячейках с формулами.

Задача 5.4.

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

Исходные данные

Таблица зависимости высоты (hm) и дальности полета (Sm) от времени ™.

Решение задачи

На рис.88 представлена расчетная таблица в режиме отображения формул, а на рис.89 представлен график зависимости высоты (hm) от дальности полета (Sm) и таблица в режиме отображения данных, по которым построен график.

В диапазон G3:G24 вводятся значения времени ™ от 0 до 4,2, в колонку н — формулы для вычисления дальности полета (Sm), в колонку

I- формулы для вычисления высоты полета.

Выделив элементы этого ряда, с помощью диалогового окна Формат ряда данных, вызванного с помощью контекстного меню, можно изменить тип, цвет и размер маркера данного ряда.

Формулы в ячейках J3:J24 возвращают максимальное значение столбца I либо значение #Н/Д (нет данных). Полученные данные используются для того, чтобы отметить на графике максимальную высоту полета.

После выделения построенной диаграммы в нее добавляется новый ряд с помощью команды меню Конструктор — Данные- Выбрать данные — Добавить ряд, значения которого содержатся в ячейках J3: J24.

Excel 9. Формулы – Эффективная работа в MS Office

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

Список вкладок ленты «Формула» я дала на уроке «Excel 3. Введение в Excel».

По окончании этого урока вы сможете:

Перечислить и показать арифметические операторы для создания формулы
Вводить вручную формулы
Использовать встроенные функции
Выбирать нужные функции из контекстного меню

1. Арифметические операторы

Все эти символы вводятся с клавиатуры. Рассмотрим на примере.

Шаг 1. Щелкаем по ячейке А1, куда будет введена формула.

Шаг 2. Вводим формулу «=75+32+44»:

Обратите внимание, что в строке формул записывается вводимая нами формула.

Шаг 3. Нажимаем Enter. Активной станет ячейка А2.

Шаг 4. Делаем активной ячейку А1.

Понять и запомнить!

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

Алгоритм ввода формулы при помощи арифметических операторов:

Щелкаем на ячейке, в которой должны появиться результаты формулы.
Вводим сначала знак равенства (=), а потом − первое значение.
Вводим арифметический оператор, а затем − второе значение.
После того, как вся формула введена, нажимаем Enter, чтобы завершить ввод.

2. Формула с использованием данных, которые находятся в ячейках

При расчете плотности вязания мне часто приходится вычислять пропорцию:

Размер	Количество петель
10 см образца	18 петель
50 см размер изделия	Сколько нужно набрать петель?

То есть формула будет выглядеть так: «=18*50/10». Это в том случае, если мы решим вводить её вручную.

Шаг 1. Щелкаем на ячейке, в которой должны появиться результаты формулы:

Шаг 2. Вводим сначала знак равенства «=», а затем щелкаем на ячейке B2, которая содержит первое значение 18:

Шаг 3. Вводим арифметический оператор «*», а потом щелкаем на ячейке A3, которая содержит второе значение 50:

Шаг 4. Вводим арифметический оператор «/», а потом щелкаем на ячейке А2, которая содержит третье значение 10:

Шаг 5. Завершаем ввод, нажав клавишу Enter.

Вы можете возразить: да с калькулятором проще! Но у меня давно изготовлен шаблон таблицы Excel, в котором прописаны все формулы для расчета вязаного изделия (а формул много!).

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

Используем пример с петлями (если хотите, можете подсчитать во что обойдется килограмм икры, если её покупать в баночках по 130 грамм при стоимости баночки 740 р.).

Шаг 1. Отмечаем ячейку B3 и открываем диалоговое окно «Аргументы функции» (лента Формулы → группа команд Библиотека функций → функция ПРОИЗВЕД из списка выпадающего меню):

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

Знакомимся с диалоговым окном «Аргументы функции»:

Поля, в которые вносятся адреса ячеек. По умолчанию Число 1 имеет адрес ячейки, которая находится как раз над ячейкой с будущей формулой. Полей всего два.
Значение произведения. В данном случае значение ячейки B2
Пояснение к формуле. Советую на первых порах читать внимательно. Слово «Возвращает» всегда немного напрягало. Все-таки по-русски правильно было сказать: результат вычислений. А дальше пояснение, что перемножать можно до 255 чисел. Полей, напомню, всего два.
Значение произведения. Зачем дублировать – загадка.

Шаг 2. Не выходя из диалогового окна «Аргументы функции», отмечаем ячейку B2 (на всякий случай) и щелкаем ЛМ в поле «Число 2»

Шаг 3. Не выходя из диалогового окна «Аргументы функции», отмечаем ячейку A2:

Вот и появилось поле «Число 3». Контролируем полученное значение. Уже 900.

Дальше надо решить проблему с делением. Но функции ДЕЛЕНИЕ нет! Кстати, и функции ВЫЧИТАНИЕ тоже нет. На помощь придёт знание арифметики. Деление – это умножение на дробь «1/а».

Шаг 4. Вводим с клавиатуры в поле «Число 3» − ««1/» и, не выходя из диалогового окна «Аргументы функции», отмечаем ячейку A1:

Все время контролируем полученное значение. Уже 90. А заодно внимательно посмотрим на строку формул. Формула записалась с соблюдением синтаксиса Excel.

Нажимаем ОК:

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

Изменяем значение в любой ячейке, которая участвует в формуле, например в ячейке B2:

Формула вычислила новое значение

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

Понять и запомнить!

1. При делении умножаем на дробь «1/адрес ячейки»
2. При вычитании прибавляем отрицательное число «–адрес ячейки»

4. Формула, записанная вручную

Удалите в ячейке В3 формулу. Записываем формулу заново.

Шаг 1. Вводим знак «=» с клавиатуры и набираем «про»:

Выскакивает выпадающее меню, где представлен список функций, начинающихся на «про».

Шаг 2. Двойной щелчок ЛМ по названию функции «ПРОИЗВЕД» и дальше повторяем действия по вводу адресов ячеек, не забывая разделять адреса знаком «точка с запятой ;»:

Если вы дружите с английской раскладкой клавиатуры, то можете набрать формулу вручную с клавиатуры. Если вы наберете «А» или «В» в русской раскладке, то Excel вас просто не поймёт, формула не заработает, и вы увидите:

При щелчке по зеленому треугольнику ЛМ появится значок с восклицательным знаком, а уже при щелчке ЛМ по этому значку появится выпадающее меню:

Разбираться с этим меню вы будете весь световой день, но ошибку не найдёте. Так что лучше не делать ошибок в синтаксисе формул. Самое лучшее в этом случае щелкнуть ЛМ по строке формул:

Правильно введённые адреса акцентированы цветом, а ошибочный адрес (русская буква «А») – чёрным цветом. Если вы исправите русскую букву «А» на английскую, то ошибка исчезнет.

5. Суммирование строк и столбцов

Шаг 1. Создаем ряд чисел в столбце и делаем активной ячейку суммирования.

Шаг 2. Повторяем Шаги 1÷2 из пункта 4:

Знак «=» и набираем «сум»
Выбираем функцию «СУММА» из предложенного списка

Шаг 3. Щелкаем по первой ячейке ряда чисел, которые следует просуммировать, и протягиваем курсор вниз, то есть выделяем диапазон ячеек.

Шаг 1. Нажимаем Enter

Обратите внимание, что диапазон ячеек обозначен двоеточием «:» (Excel 5).

Есть более простой способ: воспользоваться кнопкой Автосумма (AutoSum) на ленте Главная (Лента Главная → группа команд Редактирование → команда Автосумма). Эта кнопка продублирована на ленте Формулы.

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

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

Шаг 1. Нажимаем кнопку Автосумма на ленте Главная:

Диапазон ячеек, который суммируется, будет обведен «бегущей» границей, а в строке формул появится формула «=СУММ(С1:С4)».

Шаг 2. Нажимаем Enter

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

Создаем ряд чисел по двум столбцам и делаем активной ячейку суммирования.

Шаг 1. Повторяем Шаги 1÷2 из пункта 4:

Знак «=» и набираем «сум»
Выбираем функцию «СУММА» из предложенного списка

Шаг 7. Выбираем первый диапазон (1), затем второй диапазон (2) простым перетаскиванием ЛМ:

6. Далеко идущие выводы

Формула введенная вручную

2. Формула с использованием данных, которые находятся в ячейках

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

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

Теперь вы можете:

Перечислить и показать арифметические операторы для создания формулы
Вводить вручную формулы
Использовать встроенные функции
Выбирать нужные функции из контекстного меню

На этом занятии вы познакомились с базой, на которой построены все возможности мощного вычислительного аппарата Excel.

функция ЕСЛИ — служба поддержки Майкрософт

Формулы и функции

Справка по Excel и обучение

Формулы и функции

ЕСЛИ функция

Обзор формул в Excel
Статья
XLOOKUP
Статья
ВПР
Статья
Функция СУММ
Статья
СЧЁТЕСЛИ функция
Статья
ЕСЛИ функция
Статья
ИФС
Статья
СУММЕСЛИ
Статья
СУММЕСЛИМН
Статья
СООТВЕТСТВОВАТЬ
Статья

Обычное использование функций

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

Таким образом, оператор IF может иметь два результата. Первый результат — если ваше сравнение истинно, второй — если ваше сравнение ложно.

Например, =ЕСЛИ(C2=”Да”,1,2) говорит ЕСЛИ(C2=Да, то возвращает 1, иначе возвращает 2).

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

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

Например:

Имя аргумента	Описание
логический_тест (обязательно)	Состояние, которое вы хотите проверить.
value_if_true (обязательно)	Значение, которое вы хотите вернуть, если результат *logical_test* ИСТИНА.
value_if_false (необязательно)	Значение, которое вы хотите вернуть, если результат *logical_test* равен FALSE.

Простые примеры IF

= ЕСЛИ (C2 = «Да», 1,2)

В приведенном выше примере в ячейке D2 указано: ЕСЛИ(C2 = Да, затем вернуть 1, иначе вернуть 2)

В этом примере формула в ячейке D2 говорит: ЕСЛИ(C2 = 1, затем вернуть Да, иначе вернуть Нет) Как видите, функцию ЕСЛИ можно использовать для оценки как текста, так и значений. Его также можно использовать для оценки ошибок. Вы не ограничены только проверкой равенства одной вещи другой и возвратом одного результата, вы также можете использовать математические операторы и выполнять дополнительные вычисления в зависимости от ваших критериев. Вы также можете вложить несколько функций ЕСЛИ вместе, чтобы выполнить несколько сравнений.

В приведенном выше примере функция ЕСЛИ в D2 говорит ЕСЛИ (C2 больше, чем B2, затем вернуть «Превышение бюджета», иначе вернуть «В рамках бюджета»)

=ЕСЛИ(С2>В2,С2-В2,0)

На приведенном выше рисунке вместо текстового результата мы собираемся вернуть математический расчет. Таким образом, формула в E2 говорит: ЕСЛИ (фактическое значение больше запланированного, затем вычтите запланированную сумму из фактической суммы, в противном случае ничего не возвращайте).

В этом примере формула в F7 говорит: ЕСЛИ(E7 = «Да», затем рассчитайте общую сумму в F5 * 8,25%, в противном случае налог с продаж не взимается, поэтому верните 0)

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

Общие проблемы

Проблема	Что пошло не так
0 (ноль) в ячейке	Не было аргумента ни для value_if_true , ни для value_if_False аргументов. Чтобы увидеть правильное возвращаемое значение, добавьте текст аргумента к двум аргументам или добавьте к аргументу ИСТИНА или ЛОЖЬ.
#ИМЯ? в ячейке	Обычно это означает, что в формуле написана ошибка.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

Функция ЕСЛИ — вложенные формулы и обход подводных камней

функция ЕСЛИ

Использование ЕСЛИ с функциями И, ИЛИ и НЕ

СЧЁТЕСЛИ функция

Как избежать неработающих формул

Обзор формул в Excel

Функция ЕСЛИ – вложенные формулы и избежание ловушек

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

Операторы

IF невероятно надежны и составляют основу многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться к минимальным условиям, таким как Мужчина/Женщина, Да/Нет/Возможно, и это лишь некоторые из них, но иногда вам может понадобиться оценить более сложные сценарии, требующие вложения* более 3 функций ЕСЛИ вместе.

* «Вложенность» относится к практике объединения нескольких функций в одной формуле.

Синтаксис

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

Например:

Имя аргумента	Описание
логический_тест (обязательно)	Состояние, которое вы хотите проверить.
значение_если_истина (обязательно)	Значение, которое вы хотите вернуть, если результат *logical_test* равен TRUE.
значение_если_ложь (опционально)	Значение, которое вы хотите вернуть, если результат *logical_test* равен FALSE.

Примечания

Хотя Excel позволяет вкладывать до 64 различных функций ЕСЛИ, делать это совсем не рекомендуется. Почему?

org/ListItem»>
Множественные операторы IF требуют тщательного обдумывания, чтобы правильно построить и убедиться, что их логика может правильно вычислять каждое условие до конца. Если вы не вложите свою формулу на 100 % точно, она может работать в 75 % случаев, но возвращать неожиданные результаты в 25 % случаев. К сожалению, шансы на то, что вы поймаете 25%, невелики.
Сопровождение нескольких операторов IF может оказаться невероятно трудным, особенно когда вы возвращаетесь через некоторое время и пытаетесь выяснить, что вы или, что еще хуже, кто-то другой пытался сделать.

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

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

Примеры

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

=ЕСЛИ(D2>89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))
Этот сложный вложенный оператор IF следует простой логике:

Если результат теста (в ячейке D2) больше 89, учащийся получает A
Если результат теста выше 79, учащийся получает B
org/ListItem»>
Если результат теста выше 69, учащийся получает C
Если результат теста больше 59, то студент получает D
В противном случае учащийся получает F

Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно сегментировать оценки между A+, A и A- (и так далее)? Теперь нужно переписать оператор IF с четырьмя условиями, чтобы он содержал 12 условий! Вот как теперь будет выглядеть ваша формула:

=ЕСЛИ(В2>97,»А+»,ЕСЛИ(В2>93,»А»,ЕСЛИ(В2>89,»А-«,ЕСЛИ(В2>87,»В+»,ЕСЛИ(В2>83,» В», ЕСЛИ(В2>79,»В-«, ЕСЛИ(В2>77,»С+»,ЕСЛИ(В2>73,»С»,ЕСЛИ(В2>69,»С-«,ЕСЛИ(В2>57) ,»D+»,ЕСЛИ(B2>53,»D»,ЕСЛИ(B2>49,»D-«,»F»))))))))))))

Он по-прежнему функционально точен и будет работать так, как ожидалось, но на написание и тестирование уходит много времени, чтобы убедиться, что он работает так, как вы хотите. Еще одна вопиющая проблема заключается в том, что вам приходилось вводить баллы и эквивалентные буквенные оценки вручную. Какова вероятность того, что вы случайно сделаете опечатку? А теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но вы действительно хотите подвергать себя таким усилиям и вероятным ошибкам, которые будет очень трудно обнаружить?

Совет: Каждая функция в Excel требует открывающей и закрывающей скобки (). Excel попытается помочь вам понять, что и куда нужно, окрашивая различные части формулы, когда вы ее редактируете. Например, если вы отредактируете приведенную выше формулу, то при перемещении курсора за каждую из закрывающих скобок «)» соответствующая открывающая скобка окрасится в тот же цвет. Это может быть особенно полезно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли у вас совпадающих скобок.

Дополнительные примеры

Ниже приведен очень распространенный пример расчета комиссии с продаж на основе уровня достижения дохода.

=ЕСЛИ(С9>15000,20%,ЕСЛИ(С9>12500,17,5%,ЕСЛИ(С9>10000,15%,ЕСЛИ(С9>7500,12,5%,ЕСЛИ(С9>5000,10%,0)) )))

Эта формула гласит: ЕСЛИ(C9 больше 15 000, то возвращается 20 %, ЕСЛИ(C9 больше 12 500, возвращается 17,5 % и т. д.)

Хотя эта формула очень похожа на предыдущий пример Grades, эта формула является прекрасным примером того, насколько сложно поддерживать большие операторы ЕСЛИ. Что вам нужно будет сделать, если ваша организация решит добавить новые уровни вознаграждения и, возможно, даже изменить существующие? долларовые или процентные значения? У вас будет много работы на ваших руках!

Совет: Вы можете вставлять разрывы строк в строку формул, чтобы упростить чтение длинных формул. Просто нажмите ALT+ENTER перед текстом, который вы хотите перенести на новую строку.

Вот пример сценария комиссии с неисправной логикой:

Вы видите, что не так? Сравните порядок сравнения Revenue с предыдущим примером. В какую сторону идет этот? Правильно, она идет снизу вверх (от 5000 до 15000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это большое дело, потому что формула не может пройти первую оценку для любого значения, превышающего 5000 долларов. Допустим, у вас есть доход в размере 12 500 долларов – оператор IF вернет 10 %, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях эти типы ошибок остаются незамеченными, пока они не окажут негативного влияния. Итак, зная, что сложные вложенные операторы IF сопряжены с некоторыми серьезными ловушками, что вы можете сделать? В большинстве случаев можно использовать функцию ВПР вместо построения сложной формулы с помощью функции ЕСЛИ. Используя функцию ВПР, сначала необходимо создать справочную таблицу:

Эта формула предлагает искать значение в C2 в диапазоне C5:C17. Если значение найдено, вернуть соответствующее значение из той же строки в столбце D.

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

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

VLOOKUP рассматривается здесь более подробно, но это, безусловно, намного проще, чем 12-уровневый сложный вложенный оператор IF! Есть и другие менее очевидные преимущества:

Справочные таблицы
VLOOKUP открыты и легко видны.
org/ListItem»>
Значения таблицы могут быть легко обновлены, и вам никогда не придется прикасаться к формуле, если ваши условия изменятся.
Если вы не хотите, чтобы люди видели вашу справочную таблицу или мешали ей, просто поместите ее на другой рабочий лист.

Знаете ли вы?

Теперь есть функция IFS, которая может заменить несколько вложенных операторов IF одной функцией. Итак, вместо нашего примера с начальными оценками, который имеет 4 вложенные функции ЕСЛИ:

=ЕСЛИ(D2>89,»A»,ЕСЛИ(D2>79,»B»,ЕСЛИ(D2>69,»C»,IF(D2>59,»D»,»F»))))

Это можно сделать намного проще с помощью одной функции IFS:

org/ListItem»>
=ЕСЛИ(D2>89,»A»,D2>79,»B»,D2>69,»C»,D2>59,»D»,ИСТИНА,»F»)

Функция IFS великолепна, потому что вам не нужно беспокоиться обо всех этих операторах IF и скобках.

Примечание. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Купить или попробовать Microsoft 365

Нужна дополнительная помощь?

Связанные темы

Видео: расширенные функции ПЧ

Функция IFS (Microsoft 365, Excel 2016 и более поздние версии)

Функция СЧЁТЕСЛИ будет подсчитывать значения на основе одного критерия.

Функция СЧЁТЕСЛИМН будет подсчитывать значения на основе нескольких критериев.

Функция СУММЕСЛИ будет суммировать значения на основе одного критерия.

Функция СУММЕСЛИМН будет суммировать значения на основе нескольких критериев.

И функция

Функция ИЛИ

Функция ВПР

Обзор формул в Excel

Как избежать неработающих формул

Обнаружение ошибок в формулах

Логические функции

функций Excel (по алфавиту)

функций Excel (по категориям)

Функция ЕСЛИ в Excel — обзор, примеры Как использовать?

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

Например, формулу ЕСЛИ в Excel можно применить следующим образом:

«=ЕСЛИ(условие A,»значение B»,»значение C»)»

Функция ЕСЛИ Excel возвращает «значение B», если условие A выполняется и возвращает «значение C», если условие A не выполняется.

Часто используется для логических интерпретаций, помогающих в принятии решений.

Содержание

Что такое функция ЕСЛИ в Excel?
- Синтаксис функции ЕСЛИ Excel
- Как использовать функцию ЕСЛИ в Excel?
  - Пример № 1
  - Пример № 2
  - Пример № 3
  - Пример № 4
  - Пример № 5
- Руководя

Синтаксис функции ЕСЛИ Excel

Синтаксис функции ЕСЛИ показан на следующем рисунке:

Функция ЕСЛИ Excel принимает следующие аргументы:

Logical_test: Относится к оцениваемому условию. Условие может быть значением или логическим выражением.
Value_if_true: Это значение, возвращаемое в результате, когда условие истинно.
Value_if_false: Это значение, возвращаемое в результате, когда условие «ложно».

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

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

Operator	Meaning
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less
<=	Меньше или равно
<>	Не равно

Как использовать функцию ЕСЛИ в Excel?

Давайте разберемся с работой функции ЕСЛИ с помощью следующих примеров в Excel.

Вы можете скачать этот шаблон Excel функции ЕСЛИ здесь – Шаблон Excel функции ЕСЛИ

Пример #1

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

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

Формула ЕСЛИ выглядит следующим образом:

«=ЕСЛИ(B2=«Да», «Жизнь возможна», «Жизнь невозможна»)

На следующем рисунке показана формула ЕСЛИ, примененная к ячейке C2.

На следующем изображении показано, как формула ЕСЛИ применяется к диапазону ячеек C2:C5.

Перетащите ячейки, чтобы просмотреть вывод всех планет.

Результаты в таблице ниже показывают, что жизнь возможна на планете Земля.

Блок-схема общей функции IF Excel

Блок-схема функции IF для Марса (пример № 1)

Блок-схема функции IF для Юпитера и Венеры такая же, как и для функции IF Марс (Пример №1).

Блок-схема функции IF для Земли

Следовательно, функция IF excel позволяет проводить логическое сравнение между значениями. modus operandi функции ЕСЛИ формулируется следующим образом: если что-то верно, то сделайте что-нибудь; в противном случае сделайте что-нибудь еще.

Пример #2

В следующей таблице показан список лет. Мы хотим узнать, является ли данный год високосным или нет.

В високосном году 366 дней; дополнительный день 29 февраля. Критерии високосного года формулируются следующим образом:

Год будет точно делиться на 4 и не будет делиться на 100 или
Год будет точно делиться на 400.

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

Мы используем функцию ОСТАТ, чтобы найти остаток после деления делимого на делитель.

Функция И Функция ИФункция И в Excel классифицируется как логическая функция; он возвращает ИСТИНА, если указанные условия выполнены, в противном случае он возвращает ЛОЖЬ. Подробнее оценивает оба условия високосных лет на значение «истина». Функция ИЛИФункция ИЛИФункция ИЛИ в Excel используется для проверки различных условий, что позволяет сравнивать два значения или утверждения в Excel. Если хотя бы один из аргументов или условий оценивается как ИСТИНА, он вернет ИСТИНА. Точно так же, если все аргументы или условия имеют значение FALSE, он вернет FASLE. Подробнее оценивает любое из условий для значения «истина».

Применим функцию ОСТАТ к следующим условиям:

Если ОСТАТ(год,4)=0 и ОСТАТ(год,100)<>(не равно) 0, то год високосный.

или

Если MOD(year,400)=0, то год високосный; в противном случае год не является високосным.

Формула ЕСЛИ выглядит следующим образом:

«=ЕСЛИ(ИЛИ(И((MOD(год,4)=0),(MOD(год,100)<>0)),(MOD(год,400 )=0)), «Високосный год», «Не високосный год»)»

Аргумент «год» относится к эталонному значению.

На следующих изображениях показан результат применения формулы ЕСЛИ к диапазону ячеек.

На следующем рисунке показано, как формула ЕСЛИ применяется к диапазону ячеек B2:B18.

Следующая таблица показывает 1960, 2028 и 2148 годы как високосные, а остальные годы как невисокосные.

Результат формулы Excel отображается для диапазона ячеек B2:B18 на следующем изображении.

Пример #3

Следующая таблица показывает список водителей и направления, по которым они добирались до пункта назначения. Ему предшествует изображение перекрестка дорог, объясняющее повороты водителей и их пункты назначения. Поворот направо ведет к городу B, а поворот налево ведет к городу C. Определите пункт назначения водителя в город B и город C.

Изображение пересечения дорог

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

Если водитель поворачивает направо, он/она достигает города B.
Если водитель поворачивает налево, он/она достигает города C.

Мы используем следующую формулу ЕСЛИ для найти пункт назначения:

«=ЕСЛИ(B2=»Левый», «Город C», «Город B»)»

На следующем изображении показан результат применения формулы ЕСЛИ к ячейке C2.

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

На изображении ниже показана формула ЕСЛИ, примененная к диапазону.

Результат формулы ЕСЛИ и адресаты отображаются на следующем изображении.

Результат показывает, что шесть водителей добрались до города C, а остальные четыре — до города B.

Пример #4

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

Давайте перечислим имена элементов в столбце A и количество элементов в столбце B. Список данных, которые необходимо проверить для всего списка элементов, показан в ячейке E2 на изображении ниже.

Мы используем ЕСЛИ Excel вместе с функцией ВПРФункция ВПРФункция ВПР Excel ищет определенное значение и возвращает соответствующее совпадение на основе уникального идентификатора. Уникальный идентификатор однозначно связан со всеми записями базы данных. Например, идентификатор сотрудника, номер списка учащихся, контактный номер клиента, адрес электронной почты продавца и т. д. являются уникальными идентификаторами.
читать далее, чтобы проверить наличие предметов в инвентаре.

Функция ВПР ищет значения, относящиеся к количеству элементов, а функция ЕСЛИ проверит, больше ли номер элемента нуля или нет.

Применим следующую формулу ЕСЛИ в ячейке F2:

«=ЕСЛИ(ВПР(E2,A2:B11,2,0)=0, «Элемент недоступен»,«Элемент доступен»)»

Если значение поиска элемента равно 0, то элемент недоступен; в противном случае товар доступен.

Следующее изображение показывает результат формулы ЕСЛИ в ячейке F2.

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

Пример №5

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

Мы применяем вложенный ЕСЛИ в Excel, поскольку у нас есть несколько критериев для поиска и определения оценки каждого учащегося.

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

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

«=ЕСЛИ (условие1, значение_если_истина1, ЕСЛИ(условие2, значение_если_истина2, значение_если_ложь2 ))»

Следующая таблица представляет диапазон оценок и оценок соответственно.

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

«=ЕСЛИ((B2>=95),«A»,ЕСЛИ(И(B2>=85,B2<=94),«B»,ЕСЛИ(И(B2>=75,B2<=84) ,"C",IF(AND(B2>=61,B2<=74),"D","F"))))"

Функция ЕСЛИ проверяет логическое условие, как показано в формуле ниже:

«=ЕСЛИ(логическая_проверка, [значение_если_истина],[значение_если_ложь])»

Мы разделим вышеупомянутую вложенную формулу и проверим операторы ЕСЛИ, как показано ниже:

Первая логическая проверка: B2>=95

Если формула возвращает,

Значение_если_истина, выполните: «A» (Уровень А) иначе (запятая) введите значение_если_ложь
Значение_если_ложь, затем формула найдет другое условие ЕСЛИ и введите условие ЕСЛИ логическое выражение 1) и B2<=94(логическое выражение 2)
(мы используем функцию И для проверки нескольких логических выражений, поскольку два заданных условия должны быть оценены как «истинные»).
Если формула возвращает значение,
- Значение_если_истина, выполнить: «B» (Уровень B) иначе (запятая) ввести значение_если_ложь
- Значение_если_ложь, затем формула найдет другое условие ЕСЛИ и введет условие ЕСЛИ
Третий логический тест: B2>=75(логическое выражение 1 ) и B2<=84(логическое выражение 2)
(Мы используем функцию И для проверки нескольких логических выражений, поскольку два заданных условия должны быть оценены как «истинные»). , выполнить: «C» (уровень C), иначе (запятая) введите значение_если_ложь
значение_если_ложь, затем формула находит другое условие ЕСЛИ и вводит условие ЕСЛИ

Четвертый логический тест : B2>=61(логическое выражение 1) и B2<=74(логическое выражение 2)

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

Если формула возвращает значение

Значение_если_истина, выполните: «D» (уровень D) иначе (запятая) введите значение_если_ложь

Значение_если_ложь, выполнить: «F» (Уровень F)
Наконец, закрыть скобки.

На изображении ниже показан результат применения формулы ЕСЛИ к диапазону.

На следующем изображении показана вложенная формула ЕСЛИ, примененная к диапазону.

Оценки учащихся перечислены в следующей таблице.

Видео о функции ЕСЛИ в Excel

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

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

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

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

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

Сводка

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

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

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

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

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

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

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

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

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

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

 ISNUMBER(SEARCH("abc",B5))

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

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

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

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

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

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

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

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

Проверка текста

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

Проверка числа или даты

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

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

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

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

909:30 Поскольку ячейка A2 содержит текст « пример », формула вернет « Да » в выходную ячейку.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Прежде чем идти

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

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

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

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

Формулы Excel: использование функции ЕСЛИ

Урок 17: Использование функции ЕСЛИ

/en/excelformulas/interactive-birthday-budget-blues/content/

«Привет! Как вы знаете, мы недавно завершили наш полугодовой сбор средств. $50

, они получат бесплатную большую сумку. Я собирался ввести Бесплатный подарок каждому, кто пожертвовал не менее 50 долларов, но сегодня я очень занят. Как вы думаете, вы могли бы справиться с этим для меня?»

Наша электронная таблица

После того, как вы загрузили нашу электронную таблицу, откройте файл в Excel или другом приложении для работы с электронными таблицами. Как видите, наш коллега Джордан уже добавил массу информации — есть несколько разных столбцов, и каждый ряд содержит контактную информацию одного человека, а также сумму денег, которую этот человек пожертвовал9.0003

Как быстро решить эту проблему?

Итак, если человек пожертвовал не менее 50 долларов, нам нужно написать слова Бесплатный подарок в строке этого дарителя. Мы могли бы просмотреть каждую строку, посмотреть на значение, а затем написать Бесплатный подарок , если значение составляет не менее 50 долларов. Но в электронной таблице много людей, поэтому вручную это может занять очень много времени. Что нам действительно нужно, так это способ заставить нашу электронную таблицу делать это автоматически .

Хотя вы, возможно, никогда не использовали его раньше, IF Функция может упростить решение подобных задач. Функция ЕСЛИ может просмотреть значение в ячейке, чтобы увидеть, соответствует ли оно основному условию (в нашем примере , если значение больше или равно 50 ). Затем он может добавить текст в пустую ячейку. Здесь мы можем использовать функцию ЕСЛИ, чтобы просмотреть пожертвование каждого человека, а затем добавить слова Бесплатный подарок , если они пожертвовали не менее 50 долларов. По сути, функция ЕСЛИ может делать то же самое, что мы планировали, только гораздо быстрее.

Подготовьте электронную таблицу

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

Написание функции

Теперь, когда у нас есть новый столбец, мы начнем строить нашу формулу. Нам понадобится формула в каждой строке, поэтому мы начнем с ввода формулы в ячейку h3. Как и в любой формуле, вы начнете с равно знаку ( = ). Затем введите имя функции, за которым следует открывающая скобка. Пока это должно выглядеть так:

=IF(

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

Первый аргумент устанавливает условие, которое мы хотим проверить. Для этой формулы мы хотим знать , если значение в ячейке G2 больше или равно 50 . Мы будем использовать некоторые математические операторы (> для больше и = для равенства) для записи условия:

=IF(G2>=50

Следующий аргумент сообщит функции, что писать, если условие равно . true (то есть, если значение больше или равно 50). Это означает, что человек получит бесплатный подарок, поэтому мы запишем Free Gift . Поскольку этот аргумент является текстовым, мы нужно поместить его в двойных кавычках («») . И, как и в любой функции, нам нужно будет использовать запятую для разделения каждого аргумента:

=IF(G2>=50, «Бесплатный подарок»

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

=ЕСЛИ(G2>=50, «Бесплатный подарок», «»)

Готово! Когда вы нажмете Enter, в ячейке должны появиться слова Free Gift .

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

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

«Отлично! Это именно то, что я хотел!

Я не знаю, как ты сделал это так быстро, но это действительно избавляет меня от многих проблем. Знаешь, я думаю, что у нас, вероятно, есть запасная сумка, если ты захочешь себе!»

Бонусный раздел

Продолжать

Предыдущий: Интерактив: День рождения Бюджетный блюз

Далее:Использование CONCATENATE для объединения имен

/en/excelformulas/using-concatenate-to-combine-names/content/

Учебное пособие по формуле ЕСЛИ для Excel — все, что вам нужно знать

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

Уровень навыка: Новичок

Посмотреть учебник

Посмотреть на YouTube и подписаться на наш канал

Загрузить файл Excel

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

Объяснение функции IF.xlsx

Освоение функции IF

Функция IF — важная функция, которую необходимо знать. Это может помочь при сравнении данных, выполнении поиска по групповым данным и создании интерактивных отчетов . Если вы не знакомы с IF или хотели бы освежить знания, этот пост поможет.

Вот сигнатура функции ЕСЛИ:

=ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

Функция ЕСЛИ имеет 3 аргумента :

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

Вот пример. Я сравниваю суммы дохода с суммами цели . Если доход на 90 523 больше или равен цели 90 524, я хочу, чтобы в столбце D было написано 90 523 Да , а если нет, то Нет .

Давайте поговорим о самой важной части функции ЕСЛИ: логическом тесте.

Логические тесты

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

Operator	Description
=	Equal To
<>	Not Equal To
>	Greater Than
<	Less Than
>=	Больше или равно
<=	Меньше или равно

Логические тесты возвращают значения ИСТИНА или ЛОЖЬ. Это важно, потому что логическому тестовому аргументу в IF требуется возвращаемое ему значение TRUE или FALSE (логическое). Это также важно, потому что вам может понадобиться , а не , даже функция ЕСЛИ, если значения ИСТИНА/ЛОЖЬ могут быть использованы для получения ответа, который вы ищете.

Также важно отметить, что регистр не имеет значения при сравнении текста. Следующая формула вернет TRUE.

= «Excel Campus» = «excel campus»

Возврат других значений

Хотя для моего примера, вероятно, будет достаточно «ИСТИНА» и «ЛОЖЬ», я предпочитаю, чтобы они говорили «Да» и «Нет» в ответ на вопрос в заголовке столбца: «Попал в цель?».

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

Аргументы value_if_true и value_if_false могут содержать что угодно:

Число
Текст, заключенный в двойные кавычки — «привет»
Ссылка на другую ячейку — A2
Формула с другой функцией ЕСЛИ.
Формула с любой другой комбинацией функций.

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

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

Например, следующая формула вернет ЛОЖЬ, если A2 НЕ равно B2, так как аргумент значение_если_ложь не указан.

=ЕСЛИ(A2=B2, "Сопоставление")

Группировка данных

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

На этом листе я хочу классифицировать транзакции как Крупные или Малые в зависимости от суммы, которую я ввел в ячейку D4. Транзакция менее 60 000 долларов будет помечена как small . Транзакции , превышающие или равные 60 000 долларов США, будут равны 9.0523 большой .

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

символа доллара ($) в формуле делают ссылки абсолютными , поэтому они не изменяются при копировании формулы в ячейки под ней. Когда выбрана ссылка на ячейку в формуле, вы можете нажать F4 на клавиатуре (для Fn + F4 для большинства клавиатур ноутбуков), чтобы вставить символ доллара перед номером строки и буквой столбца . (Повторное нажатие переключает его на одно или другое и обратно на отсутствие или на оба.)

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

Множественные логические тесты

Если мы хотим использовать более одного логического теста , мы можем использовать функции И и ИЛИ .

Функция И

Функция И проверяет, верны ли все аргументы, и возвращает ИСТИНА, если они равны .

В приведенном ниже примере наша формула ищет транзакции, которые являются одновременно «Крупными» и «Продуктом 6».

Функция ИЛИ

Возможно, вы хотите показать положительный результат, если только ОДИН из логических тестов в вашей формуле верен, а не ОБА . В этом случае вы можете использовать функцию ИЛИ .

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

Используя тот же лист, что и выше, формула в нашем примере ниже ищет записи с размером «Большой» ИЛИ , если название продукта «Товар 5».

Если мы хотим вернуть текст или число вместо ИСТИНА или ЛОЖЬ, то мы можем обернуть функции И или ИЛИ в функцию ЕСЛИ. Например, используя приведенную выше функцию ИЛИ в качестве нашего логического теста в формуле ЕСЛИ, мы можем ввести слово Обновление (в скобках) в качестве нашего аргумента Значение, если истинно . Это означает, что когда указанные нами условия верны, в ячейке будет написано Upgrade . Чтобы вернуть пустую ячейку , когда условия не выполняются, у нас просто есть две кавычки без ничего между ними для нашего Значение, если аргумент False .

Вложенные формулы ЕСЛИ

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

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

Например, допустим, у нас есть три категории счетов под названием Small , Большой и Ключ . Малый счет менее 10 000 долларов . Крупный счет — это от 10 000 до 70 000 долларов США . А ключевая учетная запись больше, чем 70 000 долларов .

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

Логика формулы такова.

Если сумма больше или равна 70 000 долларов США, верните значение «Ключ».
Но если это НЕ так, оцените следующую функцию ЕСЛИ.
Если сумма больше или равна 10 000 долларов США, вернуть значение «Большой».
Но если это НЕ, верните значение «Маленький».

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

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

Альтернатива 1: функция IFS

Это относительно новая функция , представленная в Excel 2019. Несмотря на то, что она по-прежнему требует большого выбора и логических тестов, ее несколько легче читать и писать формулу IFS по сравнению с вложенные формулы ЕСЛИ.

Я не буду вдаваться в подробности написания функции IFS, но вы можете поэкспериментировать с ней в файле Excel, прикрепленном вверху этого поста.

Альтернатива 2: Формулы поиска

Можно использовать VLOOKUP, XLOOKUP или Index Match , чтобы вернуть те же результаты, что и вложенные ЕСЛИ. Их намного проще написать. Чтобы узнать, как написать такую формулу ВПР, перейдите к этому руководству:

Как использовать ВПР для поиска ближайшего совпадения — последний аргумент ИСТИНА

Распространенные ошибки с функцией ЕСЛИ

Если вы столкнулись с ошибки при использовании функции ЕСЛИ, это скорее всего связано с типы данных .

Формулы эксель если в эксель: Функция ЕСЛИ — вложенные формулы и типовые ошибки

ЕСЛИ (функция ЕСЛИ) — Служба поддержки Майкрософт

Простые примеры функции ЕСЛИ

Распространенные неполадки

Дополнительные сведения

См. также

Функция ЕСЛИ в Excel. Примеры (с несколькими условиями)

Функция ЕСЛИ в Excel (общие сведения)

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

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

Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”

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

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

Сравнение данных в двух таблицах

Функция СУММЕСЛИ в Excel

Функция СУММЕСЛИМН в Excel

Функция СЧЁТЕСЛИ в Excel

Функция СЧЕТЕСЛИМН в Excel

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

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

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

ЕСЛИ + ПОИСК

ЕСЛИ + НАЙТИ

ЕСЛИ И в Excel — voxt

ЕСЛИ И в формуле Excel

Объяснил

Синтаксис

Как использовать формулу ЕСЛИ И И в Excel? (с примерами)

Пример # 1

Пример # 2

Пример # 3

То, что нужно запомнить

Похожие записи

—

Excel 9. Формулы – Эффективная работа в MS Office

1. Арифметические операторы

2. Формула с использованием данных, которые находятся в ячейках

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

4. Формула, записанная вручную

5. Суммирование строк и столбцов

6. Далеко идущие выводы

функция ЕСЛИ — служба поддержки Майкрософт

Простые примеры IF

Общие проблемы

Нужна дополнительная помощь?

См. также

Функция ЕСЛИ – вложенные формулы и избежание ловушек

Примечания

Примеры

Дополнительные примеры

Знаете ли вы?

Нужна дополнительная помощь?

Связанные темы

Функция ЕСЛИ в Excel — обзор, примеры Как использовать?

Содержание

Как использовать функцию ЕСЛИ в Excel?

Пример #1

Пример #2

Рекомендации для нескольких операторов IF

Видео о функции ЕСЛИ в Excel

Рекомендуемые статьи

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

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

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

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

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

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

Проверка текста

Проверка числа или даты

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

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

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

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

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

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

Прежде чем идти

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

Формулы Excel: использование функции ЕСЛИ

Урок 17: Использование функции ЕСЛИ

«Привет! Как вы знаете, мы недавно завершили наш полугодовой сбор средств. $50