Значение если в excel несколько условий. Как написать формулу в Excel: обучение, самые нужные формулы
Откроется диалоговое окно функции если.
Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 — кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2
Для того, чтобы в дальнейшем применить данную формулу для других сотрудников, протянув ее вниз, нам необходимо закрепить ссылку в ячейке G2, чтобы она стало абсолютной (то есть не менялась, когда мы будет протягивать формулу). Для этого необходимо прописать знаки доллара перед буквой и цифрой или только перед цифрой, так как мы не будет протягивать формулу влево или вправо. Удобнее всего зафиксировать ячейку просто выделив в формуле нужную ячейку и нажав клавишу F4
Должно получиться вот так B3>$G$2 или так B3>G$2
Обратите внимание, Excel автоматически вычисляет выражение и отображает его справа. В нашем случае Excel вычислил, что выражение — ИСТИНА, то есть действительно Алексей сделал продаж в количестве 35 шт, что больше верхней границе плана 20 шт. Обратите внимание, что если бы у Алексея было бы недовыполнение плана, то формулу мы бы все равно писали точно так же — гипотетически рассуждая, как если бы у Алексея был бы выполнен план.
Значение_если_истина . Переведите курс в это поле. Тут нам необходимо рассчитать оплату сотруднику, если он выполнил план. Стандартная оплата 2000 (F4) плюс так как мы рассматриваем ситуацию когда план перевыполнен, нужно прибавить переработку. Для этого вычтем из всего продаж план и получим количество продаж сверх нормы (B3-G2) и умножим их на оплату за перевыполнение 25 рублей (F5). В итоге получаем следующий расчет 2000+(35-20)*25 то есть F4+(B3-G2)*F5
так же не забываем, что данные из таблицы условий нам нужно закрепить, чтобы они сдвигались при протягивании формулы. Для этого выделяем нужные значения и нажимаем F4
Получаем итоговый расчет: $F$4+(B3-$G$2)*$F$5
Переходим к следующему полю.
Значение_если_ложь. Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно — возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.
В этом поле можно написать вложенную функцию ЕСЛИ вручную, но это не очень удобно, поэтому мы снова воспользуемся диалоговыми окнами. Перейдите в ячейку Значение_если_ложь откройте раскрывающийся список последних использованных функции в правом углу и выберите ЕСЛИ
После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Не надо паниковать предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Это сделано для удобство.
Заполняем поле лог_выражение
, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3 Далее поле Значение_если_истина
. То есть если действительно B3 Получаем следующую формулу: $F$4-($F$2-B3)*$F$6 Отлично. Если план перевыполнен, то мы посчитали оплату, если не выполнен, мы проверяем сотрудника на невыполнения плана. Если он не выполнил план, то мы считаем ему оплату. Если же он и не перевыполнил план и не недовыполнил план, то значил от сделал ровно план от 18 до 20 шт. Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку - $F$4 Вот так будет выглядеть вложенное ЕСЛИ Нажимаем «Ок» — формула готова. Вот так выглядит полная формула: ЕСЛИ(B3>$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3<$F$2;$F$4+($F$2-B3)*$F$6;$F$4)) С помощью диалоговых окон очень просто пользоваться функцией ЕСЛИ и при необходимости не запутаться с вложенными ЕСЛИ, главное изначально подумать о последовательности проверки ЕСЛИ. Сегодня мы рассмотрим функцию ЕСЛИ
. Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах. Функция ЕСЛИ
проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет. Синтаксис функции ЕСЛИ
очень простой: ЕСЛИ(лог_выражение
; [значение_если_истина
]; [значение_если_ложь
]) лог_выражение
– это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно. В этой части необходимо проверить на соответствие выражения. Например: ЕСЛИ(А1=10
; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ Другой пример ЕСЛИ(А1>30
; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ Еще пример ЕСЛИ(С1=”Да”
; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да”
вернет ЛОЖЬ ЕСЛИ(лог_выражение
; [значение_если_истина
]; [значение_если_ложь
]) значение_если_истина
, значение_если_ложь
– как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения
: ИСТИНА и ЛОЖЬ Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок) Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ ЕСЛИ(A3="Ведро";D3;"-") Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой. Итак, первый аргумент (лог выражения
) A3="Ведро"
проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина
), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь
), в нашем случае это «-» (т.е будет написано тире). Таким образом, в ячейки E3 появится значение D3, т.е число 240. Функция IF (ЕСЛИ)
в Excel – это отличный инструмент для проверки условий на ИСТИНУ или ЛОЖЬ. Если значения ваших расчетов равны заданным параметрам функции как ИСТИНА, то она возвращает одно значение, если ЛОЖЬ, то другое.
Заданное вами значение при выполнении двух условий ИСТИНА или ЛОЖЬ.
=IF(logical_test, , )
– английская версия
=ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])
– русская версия
При использовании функции IF (ЕСЛИ)
в Excel, вы можете использовать различные операторы для проверки состояния. Вот список операторов, которые вы можете использовать:
Ниже приведен простой пример использования функции при расчете оценок студентов. Если сумма баллов больше или равна “35”, то формула возвращает “Сдал”, иначе возвращается “Не сдал”.
Функция может принимать до 64 условий одновременно. Несмотря на то, что создавать длинные вложенные функции нецелесообразно, то в редких случаях вы можете создать формулу, которая множество условий последовательно.
В приведенном ниже примере мы проверяем два условия.
Функция позволяет выполнять вычисления с числами. Хороший пример использования – расчет комиссии продаж для торгового представителя.
В приведенном ниже примере, торговый представитель по продажам:
=IF(B2<50,0,IF(B2<100,B2*2%,B2*4%))
– английская версия
=ЕСЛИ(B2<50;0;ЕСЛИ(B2<100;B2*2%;B2*4%))
– русская версия
В формуле, использованной в примере выше, вычисление суммы комиссионных выполняется в самой функции ЕСЛИ
. Если объем продаж находится между 50-100K, то формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продажи. Вы можете использовать логические операторы (AND/OR) (И/ИЛИ) внутри функции для одновременного тестирования нескольких условий.
Например, предположим, что вы должны выбрать студентов для стипендий, основываясь на оценках и посещаемости. В приведенном ниже примере учащийся имеет право на участие только в том случае, если он набрал более 80 баллов и имеет посещаемость более 80%.
Вы можете использовать функцию вместе с функцией IF (ЕСЛИ)
, чтобы сначала проверить, выполняются ли оба эти условия или нет. Если условия соблюдены, функция возвращает “Имеет право”, в противном случае она возвращает “Не имеет право”.
Формула для этого расчета:
=IF(AND(B2>80,C2>80%),”Да”,”Нет”)
– английская версия
=ЕСЛИ(И(B2>80;C2>80%);”Да”;”Нет”)
– русская версия
С помощью этой функции вы также можете убирать ячейки содержащие ошибки. Вы можете преобразовать значения ошибок в пробелы или нули или любое другое значение.
Формула для преобразования ошибок в ячейках следующая:
=IF(ISERROR(A1),0,A1)
– английская версия
ЕСЛИ(ЕОШИБКА(A1);0;A1)
– русская версия
Формула возвращает “0”, в случае если в ячейке есть ошибка, иначе она возвращает значение ячейки.
ПРИМЕЧАНИЕ. Если вы используете Excel 2007 или версии после него, вы также можете использовать функцию IFERROR для этого.
Точно так же вы можете обрабатывать пустые ячейки. В случае пустых ячеек используйте функцию ISBLANK, на примере ниже:
=IF(ISBLANK(A1),0,A1)
– английская версия
=ЕСЛИ(ЕПУСТО(A1);0;A1)
– русская версия
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Для этого нужно выполнить следующие действия: Рассмотрим несколько различных логических выражений, чтобы вы поняли, как пользоваться этой функцией. Смысл этого выражения в следующем: если ячейка C1 содержит пустоту. То есть в ней ничего не находится. Именно этот текст будет выводиться в случае выполнения этого условия. Данный текст мы увидим в случае ложного выполнения условия. Как видите, редактор Эксель выдал сообщение, которое мы указывали в случае ложного результата условия. Ниже вы видите список операторов сравнения: А это таблица арифметических операторов: Более подробно об арифметических операторах и их приоритете в Экселе можно почитать в онлайн справке сайта Microsoft. Если хотите, то можете использовать большое количество логических выражений. Всё работает точно так же. Рассмотрим пример более сложного условия. Составлять формулу будем через панель инструментов. Это намного удобнее, чем всё писать в одну строку, поскольку очень легко запутаться. Особенно в самом начале, когда вы только учитесь. Для этого необходимо выполнить следующие действия. Очень внимательно следите за текущей раскладкой клавиатуры. Многие ошибаются и вводят русскую букву С вместо английской C. Визуально вы разницу не увидите, но для редактора это очень важно. В таком случае ничего работать не будет. Все текстовые значения обязательно вводим в кавычках. Мы проверили все три возможных результата. Всё прекрасно работает. Иногда бывает так, что введенное логическое выражение необходимо продублировать на несколько строк. В некоторых случаях дублировать приходится очень много. Такая автоматизация намного удобнее, чем ручная проверка. Рассмотрим пример копирования на таблице премий для сотрудников на праздники. Для этого нужно сделать следующие шаги. Таким образом мы проверяем, является ли данный сотрудник мужчиной. Здесь мы видим, что получилась полная противоположность. Это означает, что всё работает правильно. Кроме арифметических выражений и сравнений, также можно использовать операторы «И» и «ИЛИ». Рассмотрим их более внимательно, поскольку благодаря им возможности функции «ЕСЛИ» значительно расширяются. Первым делом создайте таблицу, в которой будет несколько полей, по которым можно будет сравнивать строки. В нашем случае при помощи поля «Статус сотрудника» мы будем проверять, кому нужно выплатить деньги, а кому – нет. Будем использовать старую функцию, в которой мы проверяли, является ли сотрудник мужчиной. Теперь нужно сделать следующие изменения. Того же самого эффекта можно добиться и при помощи оператора «ИЛИ». Для этого в формулу внесите следующие изменения: Наверняка многие из вас делали расчеты в редакторе Excel. Но бывают случаи, когда в таблице нужно посчитать не все строки, а только лишь некоторые, соответствующие определенному условию. Например, возьмем таблицу, в которой указаны различные товары, их стоимость, количество и статус. Посчитать общую сумму несложно. Но как узнать количество проданных продуктов? Функция ЕСЛИ
одна из самых популярных и часто используемых функций Excel. Используя ее совместно с операторами сравнения и другими логическими функциями Excel, можно решать достаточно сложные задачи. В этом уроке мы попробуем разобрать ее действие на простых примерах, а также научимся использовать сразу несколько функций ЕСЛИ
в одной формуле. Функция ЕСЛИ
имеет всего три аргумента: =ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)
Первый аргумент – это условие, благодаря которому формула может принимать решения. Условие проверяется в самую первую очередь и способно вернуть всего два значения – ИСТИНА или ЛОЖЬ. Если условие истинно, то формула вернет второй аргумент, в противном случае третий. О том, как задавать условия в Excel, читайте статьи: Как задать простое логическое условие в Excel и Используем логические функции Excel для задания сложных условий . Обратимся к примеру, приведенному на рисунках ниже. В данном примере функция ЕСЛИ
в первую очередь проверят условие A1>25
. Если это так, то формула возвратит текстовую строку “больше 25”, в любом другом случае – “меньше или равно 25”. Функция ЕСЛИ
является очень гибкой и ее можно применять в различных ситуациях. Рассмотрим еще один пример. В таблице ниже приведены результаты переаттестации сотрудников фирмы: В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал
или Не сдал
. Те, кто набрал более 45 баллов – сдали экзамен, остальные нет. Функции ЕСЛИ
можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Плохо. Оценка Отлично
ставится при количестве баллов более 60, оценка Хорошо
при более 45 и оценка Плохо
в остальных случаях. Как видите, вместо второго и третьего аргументов функции ЕСЛИ
можно подставлять новые функции ЕСЛИ
, тем самым расширяя число условий, которое формула может обработать. Таким образом, Вы можете создать нужное количество вложений. Правда есть очевидный недостаток такой конструкции, после 3-5 вложений формула станет нечитаемой и громоздкой, и с ней будет невозможно работать. В Excel существуют более благородные инструменты для обработки большого количества условий, например, функция ВПР
или ПРОСМОТР
. Итак, в этом уроке мы рассмотрели логическую функцию ЕСЛИ
во всей ее красе и примерах, а также разобрали простой пример с использованием сразу нескольких функций ЕСЛИ
в одной формуле. Надеюсь, что эта информация была для Вас полезной. Удачи Вам и больших успехов в изучении Microsoft Excel!Пример использования функции ЕСЛИ в Excel
Что возвращает функция
Синтаксис
Аргументы функции
Дополнительная информация
Функция Если в Excel примеры с несколькими условиями
Пример 1. Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)
Пример 2. Использование вложенной функции IF (ЕСЛИ) для проверки условия выражения
Пример 3. Вычисляем сумму комиссии с продаж с помощью функции IF (ЕСЛИ) в Excel
Пример 4. Используем логические операторы (AND/OR) (И/ИЛИ) в функции IF (ЕСЛИ) в Excel
Пример 5. Преобразуем ошибки в значения “0” с помощью функции IF (ЕСЛИ)
Примеры
C1=””
”Ячейка C1 пустая”
”Ячейка C1 не пустая”
Операторы сравнения
Арифметические операторы
Несколько условий
ЕСЛИ(C1>1000;
Копирование функции в таблицах
C6=”М”
Использование дополнительных операторов
Оператор И
=ЕСЛИ(И(C35="М";D35="Основной штат");3000;0)
Оператор Или
Функция СУММЕСЛИ
Коротко о синтаксисе
Пример 1
Пример 2
Функция ЕСЛИ и несколько условий