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

Содержание

Функция ЕСЛИ в Excel с примерами

Сегодня мы рассмотрим функцию ЕСЛИ.

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

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

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

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

Синтаксис функции ЕСЛИ очень простой:

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

лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.

В этой части необходимо проверить на соответствие выражения.

Например:

=ЕСЛИ(А1=10; [значение_если_истина]; [значение_если_ложь]) — если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ

Другой пример

=ЕСЛИ(А1>30; [значение_если_истина]; [значение_если_ложь]) — если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ

Еще пример

=ЕСЛИ(С1=”Да”; [значение_если_истина]; [значение_если_ложь]) — если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ

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

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

значение_если_истина, значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения: ИСТИНА и ЛОЖЬ

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

Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы функции ВПР. Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)

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

=ЕСЛИ(A3="Ведро";D3;"-")

и нажмем Enter

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

Итак, первый аргумент (лог выражения) A3=»Ведро» проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь), в нашем случае это «-» ( т.е будет написано тире).

Таким образом, в ячейки E3 появится значение D3, т.е число 240.

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

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

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

Спасибо за внимание.

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

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

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

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

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

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

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

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

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

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

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

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

Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.

Далее нажимаем ОК, выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

В Значение_если_истина вводим «Реализация», а в Значение_если_ложь – можно ввести прочерк « — »

Далее протягиваем формулу до конца таблицы и подключаем сводную.

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

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

Назад
Вперёд

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

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

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

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

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

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

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

Оператор ЕСЛИ в Excel с несколькими условиями (логика И)

Общая формула ЕСЛИ в Excel с двумя или более условиями:

В переводе на человеческий язык формула гласит: Если условие 1 истинно И условие 2 истинно, вернуть значение_если_истина ; иначе вернуть значение_если_ложь .

Предположим, у вас есть таблица, в которой перечислены результаты двух тестов в столбцах B и C. Чтобы сдать выпускной экзамен, учащийся должен иметь оба балла выше 50.

Для логического теста вы используете следующий оператор AND: AND (B2>50, C2>50)

Если оба условия верны, формула вернет «Пройдено»; если какое-либо условие ложно — «Fail».

=ЕСЛИ(И(B2>50, B2>50), "Пройдено", "Не пройдено")

Легко, не правда ли? Скриншот ниже доказывает, что наша формула Excel IF/AND работает правильно:

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

Например, чтобы вывести «Хорошо», если и B2, и C2 больше 50, и «Плохо» в противном случае, формула выглядит следующим образом: «Хорошо!», «Плохо»)

Важное примечание! Функция И проверяет все условия , даже если уже проверенные условия оценены как ЛОЖЬ. Такое поведение немного необычно, поскольку в большинстве языков программирования последующие условия не проверяются, если какой-либо из предыдущих тестов вернул FALSE.

На практике кажущийся правильным оператор IF может привести к ошибке из-за этой специфики. Например, приведенная ниже формула вернет #DIV/0! (ошибка «делить на ноль»), если ячейка A2 равна 0:

=ЕСЛИ(И(A2<>0, (1/A2)>0,5),"Хорошо","Плохо")

во избежание этого следует использовать вложенную функцию ЕСЛИ:

=ЕСЛИ(A2<>0, ЕСЛИ((1/A2)>0,5, "Хорошо", "Плохо"), "Плохо")

Подробнее информацию см. в формуле ЕСЛИ И в Excel.

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

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

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

Отличие от рассмотренной выше формулы ЕСЛИ/И состоит в том, что Excel возвращает ИСТИНА, если любое из указанных условий истинно.

Итак, если в предыдущей формуле использовать ИЛИ вместо И:

=ЕСЛИ(ИЛИ(B2>50, B2>50), "Зачет", "Незачет")

Тогда любой, кто наберет более 50 баллов на любом из экзаменов, получит «Зачет» в столбце D. С такими условиях наши ученики имеют больше шансов сдать выпускной экзамен (особенно не повезло Иветте, которая провалилась всего на 1 балл 🙂

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

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

=ЕСЛИ(ИЛИ(B2={"доставлено", "оплачено"}) , "Закрыто", "")

Дополнительные примеры формул можно найти в функции Excel ЕСЛИ ИЛИ.

ЕСЛИ с несколькими операторами И и ИЛИ

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

Предположим, что в нашей примерной таблице у вас есть следующие критерии для проверки результатов экзамена:

Условие 1: экзамен1>50 и экзамен2>50
Условие 2: экзамен1>40 и экзамен2>60

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

На первый взгляд формула кажется немного хитрой, но на самом деле это не так! Вы просто выражаете каждое из вышеперечисленных условий как оператор И и вкладываете их в функцию ИЛИ (поскольку нет необходимости выполнять оба условия, достаточно любого из них):

ИЛИ(И(B2>50, C2>50), И(В2>40, С2>60)

Затем используйте функцию ИЛИ для логической проверки ЕСЛИ и укажите нужные значения value_if_true и value_if_false . В результате вы получите следующую формулу ЕСЛИ с несколькими условиями И/ИЛИ:

=ЕСЛИ(ИЛИ(И(B2>50, C2>50), AND(B2>40, C2>60), "Pass" , "Fail")

Скриншот ниже показывает, что мы сделали формулу правильно:

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

В Excel 2007 и выше у вас не более 255 аргументов, а общая длина формулы ЕСЛИ не превышает 8 192 символов.
В Excel 2003 и более ранних версиях не более 30 аргументов, а общая длина формулы ЕСЛИ не превышает 1024 символов.

Вложенный оператор IF для проверки нескольких логических тестов

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

Вот типичный пример: предположим, вы хотите квалифицировать достижения учащихся как « Хорошо », « Удовлетворительно » и « Плохо » на основе следующих баллов:

Хорошо: 60 или более (> =60)
Удовлетворительно: от 40 до 60 (>40 и <60)
Плохо: 40 или меньше (<=40)

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

В нашем случае функции расположены от большего к меньшему:

=ЕСЛИ(В2>=60, "Хорошо", ЕСЛИ(В2>40, "Удовлетворительно", "Плохо"))

Естественно, при необходимости вы можете вложить больше функций (до 64 в современных версиях).

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

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

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

Чтобы оценить условия с помощью логики И, используйте звездочку:

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

Чтобы проверить условия с помощью логики ИЛИ, используйте знак плюс:

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

Чтобы правильно заполнить формулу массива, нажмите одновременно клавиши Ctrl + Shift + Enter. В Excel 365 и Excel 2021 это также работает как обычная формула благодаря поддержке динамических массивов.

Например, чтобы получить «Пройдено», если и B2, и C2 больше 50, используйте следующую формулу:

=ЕСЛИ((B2>50) * (C2>50), "Пройдено", "Не пройдено")

В моем Excel 365 обычная формула работает нормально (как вы можете видеть на скриншотах выше). В Эксель 2019и ниже, не забудьте сделать его формулой массива, используя сочетание клавиш Ctrl + Shift + Enter.

Для оценки нескольких условий с помощью логики ИЛИ используется следующая формула:

=ЕСЛИ((B2>50) + (C2>50), «Удачно», «Не пройдено»)

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

В этом разделе объясняется, как использовать ЕСЛИ в сочетании с другими функциями Excel и какие преимущества это дает.

Пример 1. Если ошибка #Н/Д в ВПР

Когда функция ВПР или другая функция поиска не могут что-либо найти, она возвращает ошибку #Н/Д. Чтобы ваши таблицы выглядели лучше, вы можете вернуть нулевой, пустой или конкретный текст, если #N/A. Для этого используйте эту общую формулу:

IF(ISNA(VLOOKUP(…)), value_if_na , VLOOKUP(…))

Например:

Если #N/A возвращает 0:

Если искомое значение в E1 не найдено, формула возвращает ноль.

=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ)), 0, ВПР(E1, A2:B10, 2, ЛОЖЬ))

Если #Н/Д, вернуть пустое значение:

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

=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ )), "", ВПР(E1, A2:B10, 2, ЛОЖЬ))

Если #Н/Д вернуть определенный текст:

Если искомое значение не найдено, формула возвращает определенный текст.

=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ )), "Не найдено", ВПР(E1, A2:B10, 2, ЛОЖЬ))

Дополнительные примеры формул см. в разделе ВПР с оператором ЕСЛИ в Excel.

Пример 2. ЕСЛИ с функциями СУММ, СРЗНАЧ, МИН и МАКС

Для суммирования значений ячеек на основе определенных критериев в Excel предусмотрены функции СУММЕСЛИ и СУММЕСЛИМН.

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

=ЕСЛИ(СУММ(B2:C2)>130, "Хорошо", ЕСЛИ(СУММ(B2:C2) )>110, "Удовлетворительно", "Плохо"))

Если сумма больше 130, то результат «хорошо»; если больше 110 – «удовлетворительно», если 110 и ниже – «плохо».

Аналогичным образом вы можете встроить функцию СРЗНАЧ в логическую проверку ЕСЛИ и возвращать разные метки на основе среднего балла:

=ЕСЛИ(СРЕДНИЙ(B2:C2)>65, "Хорошо", ЕСЛИ(СРЕДНИЙ(B2:C2)>55, "Удовлетворительно", "Плохо"))

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

=IF(D2=MAX($D$2:$D$10), "Лучший результат", "")

Чтобы обе метки были в одном столбце, вложите вышеуказанные функции одну в другую:

=ЕСЛИ(D2=МАКС($D$2:$D$10), "Лучший результат", ЕСЛИ(D2=МИН($D$2:$D$10), "Худший результат", ""))

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

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

СЧЁТЕСЛИ — подсчет ячеек, соответствующих условию
COUNTIFS — количество ячеек с несколькими критериями
СУММЕСЛИ — условно суммировать ячейки
СУММЕСЛИМН — сумма ячеек с несколькими критериями

Пример 3. ЕСЛИ с ISNUMBER, ISTEXT и ISBLANK

Для идентификации текста, чисел и пустых ячеек в Microsoft Excel предусмотрены специальные функции, такие как ISTEXT, ISNUMBER и ISBLANK. Поместив их в логические тесты трех вложенных операторов IF, вы можете идентифицировать все различные типы данных за один раз:

=ЕСЛИ(ИСТЕКСТ(A2), "Текст", ЕСЛИ(ЧИСЛО(A2), "Число", ЕСЛИ(ПУСТО(A2), "Пусто", "")))

Пример 4. ЕСЛИ и СЦЕПИТЬ

Чтобы вывести результат ЕСЛИ и некоторый текст в одну ячейку, используйте функции СЦЕПИТЬ или СЦЕПИТЬ (в Excel 2016 — 365) и ЕСЛИ вместе. Например:

=СЦЕПИТЬ("Вы выступили ", ЕСЛИ(B1>100,"фантастика!", ЕСЛИ(B1>50,"ну", "плохо")))

=CONCAT("Вы выполнено", ЕСЛИ(B1>100,"фантастика!", ЕСЛИ(B1>50, "хорошо", "плохо")))

Глядя на скриншот ниже, вам вряд ли понадобятся объяснения того, что делает формула:

ЕСЛИ ОШИБКА / формула ISNA в Excel

В современных версиях Excel есть специальные функции для перехвата ошибок и замены их другим вычислением или предопределенным значением — ЕСЛИОШИБКА (в Excel 2007 и более поздних версиях) и IFNA (в Excel 2013 и более поздних версиях). В более ранних версиях Excel вместо этого можно использовать комбинации ЕСЛИ ОШИБКА и ЕСЛИ ЕСНА.

Разница в том, что ЕСЛИОШИБКА и ЕОШИБКА обрабатывают все возможные ошибки Excel, включая #ЗНАЧ!, #Н/Д, #ИМЯ?, #ССЫЛКА!, #ЧИСЛО!, #ДЕЛ/0! и #NULL!. В то время как IFNA и ISNA специализируются исключительно на ошибках #N/A.

Например, чтобы заменить ошибку «делить на ноль» (#DIV/0!) собственным текстом, можно использовать следующую формулу:

=ЕСЛИ(ЕОШИБКА(A2/B2), «Н/Д ", А2/В2)

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

Практическая рабочая тетрадь для загрузки

Excel IF несколько критериев — примеры (файл .xlsx)

Формула Excel: If else | Эксельджет

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

 =ЕСЛИ(проверка, истинный результат, ложный результат)

Резюме

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

 =IF(D5="S","Small","Large")

Объяснение

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

В показанном примере у нас есть список футболок с указанием цвета и размера. Однако размер обозначается аббревиатурой «S» для маленького и «L» для большого. В данных есть только эти два размера. Допустим, вы хотите написать формулу, чтобы расширить эти сокращения и показать слово «Маленький» или «Большой» в столбце E. Другими словами:

Если ячейка в столбце D содержит «S», верните «Маленький». .
Если ячейка в столбце D содержит «L», вернуть «Large».

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

 = ЕСЛИ (проверка, истинный результат, ложный результат)

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

 =ЕСЛИ(D5="S","Маленький","Большой")

В переводе это означает:

ЕСЛИ ячейка D5 равна (содержит) букве «S», вернуть слово «Маленький», ИНАЧЕ вернуть слово «Большой».

Обратите внимание, что мы тестируем только «S» — нам не нужно тестировать «L». Это потому, что у нас есть только два возможных значения, и часть ELSE формулы (результат FALSE) логически заботится о «L» для нас: если ячейка не содержит «S», она должна быть «L».

Вложение IF для обработки большего количества условий

Это прекрасно работает для двух условий, но что, если у нас есть третье условие? Например, «M» для «Среднего»? В этом случае нам нужно расширить формулу другим оператором IF. Ставим второй оператор IF, заменяя ложный результат. В приведенном ниже примере мы добавили еще один размер (средний). Формула, которую мы используем в E5:

 =ЕСЛИ(D5="S","Малый",IF(D5="M","Средний","Большой"))

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