Использование оператора ЕСЛИ в Microsoft Excel


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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel 2007 для разработчиков Excel 2010 для разработчиков Excel 2013 для разработчиков Excel для Windows Phone 10 Excel Starter 2010 Еще. Меньше

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

  • =ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

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

Технические сведения

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

  • ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
  • ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
  • ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; [значение_если_ложь]))

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

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

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

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

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

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

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

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

  • И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
  • ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
  • НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Примеры

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

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

Ниже приведены формулы с расшифровкой их логики.

Если A2 (25) больше нуля и B2 (75) меньше 100, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба условия имеют значение ИСТИНА, поэтому функция возвращает значение ИСТИНА.

Если A3 («синий») = «красный» и B3 («зеленый») равно «зеленый», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только одно условие имеет значение ИСТИНА, поэтому возвращается значение ЛОЖЬ.

Если A4 (25) больше нуля или B4 (75) меньше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только первое условие имеет значение ИСТИНА, но поскольку для ИЛИ требуется, чтобы только один аргумент был истинным, формула возвращает значение ИСТИНА.

Если значение A5 («синий») равно «красный» или значение B5 («зеленый») равно «зеленый», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае второй аргумент имеет значение ИСТИНА, поэтому формула возвращает значение ИСТИНА.

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

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

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

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

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

Если A3 больше B2 И меньше C2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба аргумента истинны, поэтому формула возвращает значение ИСТИНА.

Если A4 больше B2 ИЛИ меньше B2+60, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае первый аргумент равен ИСТИНА, а второй — ЛОЖЬ. Так как для оператора ИЛИ требуется, чтобы один из аргументов был истинным, формула возвращает значение ИСТИНА. Если вы используете мастер вычисления формул на вкладке «Формулы», вы увидите, как Excel вычисляет формулу.

Новые статьи:  Обрезка фото в PowerPoint

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Использование операторов И, ИЛИ и НЕ с условным форматированием

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

На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.

Вот как будут выглядеть формулы для примеров с датами:

Примеры использования операторов И, ИЛИ и НЕ с условным форматированием

Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий.

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

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

Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.

Примечание: Распространенной ошибкой является ввод формулы в условное форматирование без знака равенства (=). В этом случае вы увидите, что в диалоговом окне Условное форматирование в формулу будут добавлены знак равенства и кавычки : =»OR(A4>B2,A4, поэтому вам потребуется удалить кавычки, прежде чем формула ответит должным образом.

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

См. также

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

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

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

Аргументы функции ЕСЛИ и принцип действия в Excel

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

Теперь разберем её аргументы более подробно:

  1. Логическое выражение – определённые данные, которые мы должны проверить на соответствие с некоторыми имеющимися у нас условиями.
  2. Значение_если_истина – результат проверки, который мы увидим, когда логическое выражение будет справедливо.
  3. Значение_если_ложь – результат при несоответствии условия и наших данных, которые мы проверяем.

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

схема принципа работы ЕСЛИ

Теперь рассмотрим, как нам определить результат проверки с помощью функции ЕСЛИ. Для начала определяем ЧТО нам надо проверять – число 50. Затем указываем условие – «больше 40», «меньше 40», после чего пишем результат проверки – «ИСТИНА», при условии, что 50 действительно больше 40 и «ЛОЖЬ», когда 50 меньше 40. После того как мы определили, как будут выглядеть наши аргументы, начинаем собирать по кусочкам нашу формулу:

сборка формулы

Функция проверила выражение «50 больше 40» и определила, что результат — ИСТИНА (ячейка Е4).

Следующее условие проверяем точно также просто заменив оператор «больше» на «меньше»:

результат примера 1

Поскольку 40 больше 50, проверка определила, что наше выражение – ЛОЖЬ.

Теперь рассмотрим более наглядные примеры применения функции ЕСЛИ. У нас есть список студентов и оценок в баллах, полученных за экзамен. Нам нужно найти студентов отличников, которые получили оценку больше или равно 90. Начинаем составлять функцию, ячейка F17. Значение в ячейке E17 должно быть больше или равно (>=) 90, тогда мы получим результат «ИСТИНА». Когда значение в ячейке Е17 будет меньше 90, возвратится значение «ЛОЖЬ»:

список студентов

И копируем значение ячейки до конца столбца, таким образом мы нашли студентов, которые получили результат больше или равно 90 баллов:

нашли студентов

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

Новые статьи:  Считаем количество символов в документе Microsoft Word

таблица соответствий



Формула функции ЕСЛИ и ВПР

Теперь рассмотрим пример использования вложенных формул и ситуацию, где они могут пригодиться. В предыдущем примере мы определили студентов отличников, но у нас остались ещё незаполненные поля. Нам нужно также определить какую оценку в буквах получат и другие студенты. Указываем, что студенты с баллом больше или равно 90 получат оценку А: =ЕСЛИ(E31>=90; «A»; Затем на месте аргумента «значение_если_ложь» вставляем формулу ЕСЛИ(Е31>=80; «В»; и на месте аргумента значение_если_ложь этой же формулы вставляем ещё одну формулу ЕСЛИ(Е31>=60; «С»; и на месте третьего аргумента уже этой функции пишем последнее условие, не забывая добавить скобки: ЕСЛИ(Е31<60)))):

алгоритм

Копируем формулу до конца столбца и таким образом мы построили вложенную функцию. Однако иногда при написании такой функции нужно учитывать один нюанс – она корректно работает, пока данные для сравнения указываться от большего значения к меньшему (1,2,3,4):

данные для сравнения

Вот что получится, когда мы укажем условия для сравнения наоборот – от меньшего к большему:

наоборот

Логика в том, что функция во время проверки первой ячейки определила, что значение больше 60 и выдала соответствующий результат – «С». Дальше проверка не продолжилась. Есть ситуации, где оценка вместо А будет А+, А, А-, такое разветвление будет по каждой букве и самих буквенных оценок будет больше. Тогда процесс создания вложенной функции будет очень долгим, вложенных формул будет очень много и становиться легко запутаться. В таком случае вместо ЕСЛИ можно использовать ВПР. Для начала видоизменяем нашу меньшую таблицу. Такие изменения обусловлены особенностями работы функции ВПР:

альтернатива

Формула ВПР будет искать приблизительное значение ячейки Е60 в диапазоне В53:С56 во втором столбце и передавать найденные значения в основную таблицу:

ВПР

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

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

синтаксис

Например у нас есть список студентов и данные их оценок по трём предметам. Нам нужно проверить наличие у студента оценки 2 хотя бы по одному предмету и указать, есть ли у студента пересдача:

ЕСЛИ несколько условий

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

логическое выражение

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

Например

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

При условии, что в ячейке Е91 не находится слово «нет» (НЕ(Е91= «нет»)), наш результат – зачёт, в любом инном случае – не зачёт:

При условии

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

У нас есть отчет о чистых прибылях нескольких компаний. Мы хотим видеть только тот показатель, который является максимальным. Для этого мы используем вместе с функцией ЕСЛИ функцию МАКС: ЕСЛИ (МАКС( указываем диапазон в котором будем искать значение )= ячейка, которую функция будет пропускать через диапазон ; значение если истина (проверяемая ячейка) ; значение если ложь (ничего не указывать)). В ячейке Е113 прописываем формулу, не забываем про абсолютные ссылки для диапазона D113:E119, иначе он сместится при копировании, копируем формулу до конца столбца:

ЕСЛИ МАКС

Разнообразим наши результаты работы и создадим график, который будет так же выделять наше максимальное число: выбираем диапазон D111:E119 – Вставка — Рекомендуемые диаграммы – Выбираем первую диаграмму и ОК. Теперь у нас данные выводятся в табличном и графическом виде:

Новые статьи:  Способы вставки таблицы в Microsoft Excel

диаграммы

Теперь максимальное число выделяется не только в таблице, а и на диаграмме. Но сейчас этих величин у нас две – с обеих столбцов. Немного подредактируем нашу диаграмму. На диаграмме выбираем любой из Рядов, открываем меню, клацая правой кнопкой мыши по нём, выбираем Формат ряда данных, указываем Перекрытие рядов на 100% и рассмотрим результат, который у нас получился:

Перекрытие рядов на 100%

Данные из третьего столбца перекрыли данные из второго и мы получили подсвечивание максимального показателя. Теперь, когда нам нужно будет изменить любое число во втором столбце, наша формула заново определит максимальное число из столбца «Чистая прибыль», покажет его в столбце «Максимальный показатель», а потом мы увидим его среди остальных чисел и на диаграмме автоматически. Например, укажем новое число для автопроизводителя Nissan – 1600. Вот какие изменения произошли:

Максимальный показатель

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

  • Создать таблицу
  • Форматирование
  • Функции Excel
  • Формулы и диапазоны
  • Фильтр и сортировка
  • Диаграммы и графики
  • Сводные таблицы
  • Печать документов
  • Базы данных и XML
  • Возможности Excel
  • Настройки параметры
  • Уроки Excel
  • Карта сайта
  • Скачать примеры

ЕСЛИ (функция ЕСЛИ)

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

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

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

Синтаксис

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

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

  • =ЕСЛИ(A2>B2;»Превышение бюджета»;»ОК»)
  • =ЕСЛИ(A2=B2;B4-A4;»»)

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

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

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

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

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

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

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

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

Ячейка D2 содержит формулу =ЕСЛИ(C2=

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

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

Ячейка D2 содержит формулу =ЕСЛИ(C2=1;

  • =ЕСЛИ(C2=1;»Да»;»Нет»)

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

  • =ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)

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

  • =ЕСЛИ(C2>B2;C2-B2;0)

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

Ячейка F7 содержит формулу ЕСЛИ(E7=

  • =ЕСЛИ(E7=»Да»;F5*0,0825;0)

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

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

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

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

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

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

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

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


Оставьте комментарий