Функция ЕСЛИ в EXCEL
history 11 апреля 2013 г.
- Группы статей
- стандартные функции
Функция ЕСЛИ() относится к наиболее часто используемым функциям. Поэтому рассмотрим ее подробно.
Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Функция имеет 3 аргумента.
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, выражение A1>100. Если в ячейке А1 содержится значение 102, то выражение A1>100 вернет ИСТИНА, т.к. 102>100. Если в ячейке А1 содержится значение 88, то выражение вернет ЛОЖЬ, т.к. неравенство 88>100 неверно. Полностью формула может выглядеть так =ЕСЛИ(A1>100;"Бюджет проекта превышен";"ОК!") Если в ячейке A1 содержится значение большее 100, то формула вернет текстовое значение Бюджет проекта превышен, а если меньше 100 или равное 100, то будет выведено ОК!
Других значений кроме ИСТИНА или ЛОЖЬ логическое выражение вернуть не может. Любое число кроме 0 в первом аргументе будет преобразовано в ИСТИНА, ноль — в ЛОЖЬ. Если ошибочно будет указано текстовое значение, то функция вернет ошибку #ЗНАЧ!.
Примечание : Ошибка #ЗНАЧ! может быть также из-за ошибок в выражениях указанных во втором и третье аргументе.
Второй аргумент — Значение_если_истина и третий аргумент значение_если_ложь
Как было показано выше, если логическое выражение (первый аргумент функции) принимает значение ИСТИНА, то функция возвращает значение из второго аргумента значение_если_истина. Соответственно, если логическое выражение принимает значение ЛОЖЬ, то функция возвращает значение из третьего аргумента значение_если_ложь .
Второй и третий аргумент могут быть любым значением (число или текст) или являться выражением (формулой с функциями). Например формула =ЕСЛИ(A1>100;0;А1*1%) позволяет вычислять премию для проектов, в которых не было превышения бюджета (>100). Если бюджет превышен (в А1 введено значение 200 или любое другое более 100), то формула вернет 0, если в А1 введено значение менее 100, то будет вычислено значение премии А1*1% (т.е. если в А1 число 90, то формула вернет 0,9).
Если в качестве второго и третьего аргументов введено выражение, то оно может содержать и функции, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше или равно 100, то по столбцу С . Не забывайте, что при значении А1 = 100 в данном случае будет срабатывать выражение из аргумента значение_если_ложь.
Аргумент значение_если_ложь можно опустить (не вводить), тогда при значении логического выражения =ЛОЖЬ будет выведено ЛОЖЬ. Например, если в А1 содержится 600, то формула =ЕСЛИ(A1>=1000;"Больше или равно 1000") вернет просто ЛОЖЬ. Понятно, что при значении логического выражения =ИСТИНА будет выведено "Больше или равно 1000", т.е. второй аргумент функции ЕСЛИ() — Значение_если_истина.
Вложенные ЕСЛИ
Для построения более сложных проверок в одной формуле можно использовать несколько функций ЕСЛИ(). Для этого в качестве значений аргументов значение_если_истина и значение_если_ложь введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу
- Если значение из ячейки А8 менее 5, то будет выведено 1
- Если значение из ячейки А8 более 10, то будет выведено 3
- Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2
Примечание : В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.
Примечание : Формулу =ЕСЛИ(A8 10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)
или более сложный вариант =ЕСЛИ(A8
Формула =ЕСЛИ(A8 10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8 =5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.
Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:
1) Определитесь с диапазонами:
1.1) они не должны пересекаться. В примере выше — 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8 10;3;ЕСЛИ(A8 10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.
1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.
Примечание: В перечисленных выше случаях логические выражения вычисляются на основании одной переменной А8. Иногда нужно придумывать формулы с ЕСЛИ(), зависящие от 2-х и более ячеек: =ЕСЛИ(B8>=5;ЕСЛИ(A8>10;3;2);1). Это гораздо сложнее, но, конечно, возможно. Следуйте правилам 1.1)-1.3) и следите за диапазонами. Еще сложнее, если обе переменных являются зависимыми, т.е. например B8 вычисляется на основании А8. Написание таких формул требует предельной аккуратности и напрямую связано с корректностью формулирования самой задачи (возможно вы не продумали до конца как использовать такую сложную зависимость). Советую по возможности разбить вычисления на 2 или более формулы, чтобы проще контролировать результат вычисления.
2) Правильно записывайте логические выражения. Например, условие 5 10;3;2)) в строке формул можно записать как
Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.
4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения — последовательности 1; 2; 3 . Это покажет какой блок вложенных ЕСЛИ() срабатывает.
5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета
=ЕСЛИ(A1>=100;"Очень крупный проект";
ЕСЛИ(A1>=90;"Крупный проект";
ЕСЛИ(A1>=50;"Средний проект";"Малый проект ")))
Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы. Ниже приведено 2 эквивалентные формулы:
Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:
Формула =ПРОСМОТР(A1;<0;50;90;100>;<"Малый проект";"Средний проект";"Крупный проект";"Очень крупный проект">) выглядит необычно, но по сути второй аргумент <0;50;90;100>, как и третий аргумент <"Малый проект";"Средний проект";"Крупный проект";"Очень крупный проект">это запись содержимого 4-х ячеек. Вместо <0;50;90;100>можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.
Еще пример. Пусть требуется вывести разный текст (например " 0") в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():
Примечание : -1E+307 — это самое маленькое отрицательное число в EXCEL, 1E-307 — это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).
Второй массив можно заменить ссылкой на диапазон, например для того чтобы вывести результат вычисления формул:
Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).
Опущен третий аргумент [значение_если_ложь]
Третий аргумент функции не обязателен. Если логическое условие не выполнено, т.е. ЛОЖЬ, а третий аргумент функции не указан, то функция вернет также значение ЛОЖЬ. Например:
Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.
Сложные логические выражения
В аргументе Лог_выражение можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.
Основное правило — логические функции и их комбинации возвращают только одно из 2х логических значений: ЛОЖЬ или ИСТИНА.
Приведем несколько полезных примеров.
Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.
Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.
Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1 10") . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;">10") . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))
Использование ЕСЛИ с функциями И, ИЛИ и НЕ
Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.
=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ ( И), только одно условие должно иметь такое значение ( ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.
Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.
ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; [значение_если_ложь]))
Условие, которое нужно проверить.
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Общие сведения об использовании этих функций по отдельности см. в следующих статьях: И, ИЛИ, НЕ. При сочетании с оператором ЕСЛИ они расшифровываются следующим образом:
И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
Примеры
Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.
Ниже приведены формулы с расшифровкой их логики.
=ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)
Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.
Если значение A7 ("синий") НЕ равно "красный", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.
Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.
Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.
Ниже приведены формулы с расшифровкой их логики.
Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.
=ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)
Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.
Использование операторов И, ИЛИ и НЕ с условным форматированием
Вы также можете использовать операторы И, ИЛИ и НЕ в формулах условного форматирования. При этом вы можете опустить функцию ЕСЛИ.
На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.
"Изменить правило" с параметром "Формула"" xmlns:AntiXSS="urn:AntiXSSExtensions" />
Вот как будут выглядеть формулы для примеров с датами:
Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий.
=И(A3>B2;A3 B2;A4 B2)
Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.
Примечание: Распространенная ошибка — ввод формулы в условное форматирование без знака "равно" (=). При этом вы увидите, что в диалоговом оке Условное форматирование в формулу будут добавлены знаки "равно" и кавычка: ="ИЛИ(A4>B2;A4
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.