Значение, которое вы устанавливаете, когда два условия истинны или ложны.
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверенное и альтернативное), а 3, 4 и более. В этом случае вы также можете использовать функцию IF, но теперь вам придется поместить их одно в другое, указывая все условия по очереди. Рассмотрим следующий пример.
Несколько менеджеров по продажам должны получать премию в зависимости от выполнения плана продаж. Система поощрения выглядит следующим образом. Если план выполнен менее чем на 90%, премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20%, а если план выполнен более чем достаточно — премия 30%. Как вы можете видеть, есть четыре варианта. Чтобы задать их в одной формуле, необходима следующая логическая структура. Если выполняется первое условие, то выполняется первый вариант, иначе, если выполняется второе условие, то выполняется второй вариант, иначе, если … и т.д. Количество условий может быть довольно большим. В конце формулы приводится последний альтернативный вариант, для которого не действует ни одно из вышеперечисленных условий (как третье поле в обычной формуле IF). В результате формула имеет следующий вид.
Комбинация функций IF работает таким образом, что если условие выполнено, то последующие условия больше не проверяются. Поэтому важно изложить их в правильном порядке. Если бы мы начали с B2
При написании формулы легко запутаться, поэтому стоит посмотреть на всплывающую подсказку.
Не забудьте закрыть все круглые скобки в конце, иначе Excel выдаст ошибку
Синтаксис функции ЕСЛИ
Вот синтаксис и аргументы этой функции:
=If(булево выражение, значение, если «да», значение, если «нет»)
Логическое выражение — это (обязательное) условие, которое возвращает значение true или false («да» или «нет»);
Значение «да» — это (обязательное) действие, которое будет выполнено в случае положительного ответа;
Значение «нет» — это (обязательное) действие, предпринимаемое в случае отрицательного ответа;
Давайте рассмотрим эти аргументы вместе.
Первый аргумент — это логический вопрос. И этот ответ может быть только «да» или «нет», «истинным» или «ложным».
Как правильно задать вопрос? Вы можете построить логическое выражение, используя символы «=», «>», «=», «».
Расширение функционала с помощью операторов «И» и «ИЛИ»
Когда необходимо проверить несколько истинных условий, используется функция AND, суть которой заключается в следующем: ЕСЛИ a = 1 И a = 2 ТО значение в ИЛИ равно c.
Функция ИЛИ проверяет, истинно ли условие 1 или условие 2. Если хотя бы одно условие истинно, результат будет истинным. Вывод таков: ЕСЛИ a = 1 ИЛИ a = 2 ТО значение в FATHER равно c.
Функции AND и OR могут проверять до 30 условий.
Пример использования оператора AND:
Пример использования функции ИЛИ:
Простейший пример применения.
Предположим, вы работаете в компании, которая продает шоколадные конфеты в нескольких регионах и обслуживает множество клиентов.
Мы должны отличать продажи, которые происходили в нашем регионе, от тех, которые были совершены за рубежом. Для этого нам нужно добавить в таблицу еще один атрибут для каждой продажи: страну, в которой она произошла. Мы хотим, чтобы этот атрибут создавался автоматически для каждой записи (т.е. строки).
В этом нам поможет функция IF. Добавим в таблицу данных столбец «Страна». Регион «Запад» — это местные продажи («Местные»), а остальные регионы — зарубежные продажи («Экспорт»).
Применение «ЕСЛИ» с несколькими условиями
Мы только что рассмотрели пример использования оператора «IF» с одним логическим выражением. Но вы также можете использовать более одного условия. Сначала будет проверена первая, и если она успешна, то значение будет выведено сразу. Только если первое логическое выражение не сработает, будет проверено второе.
Рассмотрим пример той же таблицы. Но на этот раз давайте усложним задачу. Теперь нам нужно определить скидку на женскую обувь в зависимости от вида спорта.
Первое условие — проверка пола. Если это «мужчина» — сразу же отображается значение 0. Если это «женщина», то начинается вторая проверка условия. Если спорт — бег — 20%, если теннис — 10%.
Давайте напишем формулу для этих условий в нужной нам ячейке.
= IF(B2=»мужчина»;0; IF(C2=»бег»;20%;10%))
Нажимаем Enter и получаем результат в соответствии с заданными условиями.
Затем распространите формулу на все остальные строки таблицы.
Операторы сравнения чисел и строк
Операторы, сравнивающие числа и строки, представлены операторами, состоящими из одного или двух математических знаков равенства и неравенства:
- > — более чем;
- >= — больше или равно;
- = — равно;
- — не равны.
Синтаксис:
| 1 | Результат = Выражение1 Оператор Выражение2 |
- Результат — любая числовая переменная;
- Выражение — это выражение, которое возвращает число или строку;
- Оператор — любой оператор, который сравнивает числа и строки.
Если переменная Result объявлена как Boolean (или Variant), она будет возвращать False и True. Числовые переменные других типов возвращают 0 (False) и -1 (True).
Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом Excel VBA выдает ошибку несоответствия типов:
| 1 2 3 4 5 6 7 8 9 10 | Sub Primer1() On Error GoTo Instr Dim myRes As Boolean ‘Сравнение строки с числом myRes = «пять» > 3 Instr: If Err.Description «» Then MsgBox «Произошла ошибка: » & Err.Description End If End Sub |
Сравнение строк начинается с их первого символа. Если они равны, сравниваются следующие символы. И так далее, пока символы не станут разными, или одна или обе строки не закончатся.
Значения буквенных символов увеличиваются в алфавитном порядке, сначала все заглавные буквы, а затем все строчные. Если вы хотите сравнить длину строк, используйте функцию Len.
| 1 2 3 | myRes = «семь» > «восемь» ‘myRes = True myRes = «семь» > «восемь» ‘myRes = False myRes = Len(«семь») > Len(«восемь») ‘myRes = False |
Одновременное выполнение двух условий
Также возможен вывод данных в Excel при одновременном выполнении двух условий. Таким образом, значение будет считаться ложным, если хотя бы одно из условий не выполняется. Для этого используется оператор «AND».
В качестве примера возьмем нашу таблицу. Теперь скидка в 30% будет предоставляться только в том случае, если это женская обувь, предназначенная для бега. Если эти условия выполняются одновременно, значение ячейки будет равно 30%, в противном случае оно будет равно 0.
Для этого используйте следующую формулу:
= IF(AND(B2=»women’s»;C2=»running»);30%;0)
Нажмите Enter, чтобы отобразить результат в ячейке.
Как и в примерах выше, распространите формулу на оставшиеся строки.
Общее определение и задачи
«ЕСЛИ» — это стандартная функция Microsoft Excel. Его задача — проверить, выполняется ли определенное условие. Когда условие выполняется (true), оно возвращает одно значение в ячейке, где используется функция, а когда условие не выполняется (false), оно возвращает другое значение.
Синтаксис этой функции следующий: «IF(логическое выражение; [функция, если истинно]; [функция, если ложно])».
Как правильно записать?
Поместим курсор в ячейку G2 и введем знак «=». В Excel это означает, что теперь будет введена формула. Поэтому, как только вы нажмете «e», вам будет предложено выбрать функцию, начинающуюся на эту букву. Мы выбираем «ЕСЛИ».
Все наши дальнейшие действия также будут подсказаны.
В качестве первого аргумента введите: C2=»Запад». Вам не нужно набирать адрес ячейки вручную, просто щелкните по ней мышью, как и в других функциях Excel. Затем вставьте «,» и укажите второй аргумент.
Второй аргумент — это значение, которое примет ячейка G2, если будет выполнено условие, которое мы записали. Это будет слово «Местный».
Затем мы снова указываем значение третьего аргумента, разделяя их запятыми. Это значение, которое примет ячейка G2, если условие не будет выполнено: «Экспорт». Не забудьте завершить ввод формулы, закрыв скобки и нажав клавишу «Enter».
Наша функция выглядит следующим образом:
= IF(C2=»Запад», «Местный», «Экспорт»)
Наша ячейка G2 приняла значение «Местный».
Теперь нашу функцию можно скопировать во все остальные ячейки столбца G.
Дополнительная информация
- Функция IF может одновременно тестировать 64 условия;
- Если любой из аргументов функции является массивом — вычисляется каждый элемент массива;
- Если в функции не указано условие для аргумента value_if_false, т.е. после аргумента value_if_false стоит только запятая (точка с запятой), то функция вернет «0», если результат функции FALSE.
В примере ниже формула =IF(A1>20, «Разрешить») или =If(A1>20; «Разрешить»), где value_if_false не указано, но аргумент value_if_true по-прежнему следует через запятую. Функция возвращает «0» всякий раз, когда проверяемое условие не соответствует условию TRUE.
Если в функции не указано условие для аргумента TRUE(value_if_true), т.е. условие указано только для аргумента value_if_false, то формула будет возвращать «0» всякий раз, когда результатом вычисления функции будет TRUE;
- В следующем примере формула имеет вид =IF(A1>20; «Запретить») или =If(A1>20; «Запретить»), где аргумент value_if_true не указан, формула будет возвращать «0» всякий раз, когда условие равно TRUE.
Вложенные условия с математическими выражениями.
Вот еще одна типичная проблема: цена единицы товара варьируется в зависимости от его количества. Ваша цель — написать формулу, которая вычисляет цену для любого количества товара, введенного в данную ячейку. Другими словами, ваша формула должна проверять несколько условий и выполнять различные вычисления в зависимости от того, какой диапазон сумм содержит определенное количество товаров.
Эту задачу также можно выполнить с помощью нескольких вложенных функций ЕСЛИ. Логика такая же, как и в примере выше, за исключением того, что вы умножаете указанное количество на значение, возвращаемое вложенными условиями (т.е. соответствующую цену за единицу).
Если предположить, что количество хранится в B8, то формула будет выглядеть следующим образом:
=B8*IF(B8>=101; 12; IF(B8>=50; 14; IF(B8>=20; 16; IF( B8>=11; 18; IF(B8>=1; 22; «»)))))
И вот результат:
Как вы понимаете, этот пример демонстрирует лишь общий подход, и вы можете легко адаптировать эту вложенную функцию в зависимости от вашей конкретной задачи.
Например, вместо «жесткого» ввода цен в саму формулу можно обратиться к ячейкам, где они указаны (ячейки B2 — B6). Это позволит вам редактировать исходные данные без необходимости обновлять саму формулу:
=B8*IF(B8>=101; B6; IF(B8>=50; B5; IF(B8>=20; B4; IF( B8>=11; B3; IF(B8>=1; B2; «»)))))
Аргументы функции
- logical_test (log_expression) — это условие, которое вы хотите проверить. Этот аргумент функции должен быть логическим и указываться как FALSE или TRUE. Аргументом может быть статическое значение или результат функции, вычисления;
- [value_if_true] ([value_if_true]) — это (необязательное) значение, возвращаемое функцией. Он будет отображаться, если значение, которое мы проверяем, имеет условие TRUE;
- [value_if_false] ([value_if_false]) — (необязательно) — это значение, которое возвращает функция. Он отобразится, если проверяемое условие соответствует условию FALSE.
А если один из параметров не заполнен?
Если вас не интересует, что произойдет, например, если не будет выполнено интересующее вас условие, то вам не нужно приводить второй аргумент. Например, мы предоставляем скидку 10% при заказе более 100 штук. Мы не приводим никаких аргументов для случая, когда условие не выполняется.
=IF(E2>100,F2*0.1)
Каков будет результат этого?
Насколько это приятно и удобно — решать вам. Я считаю, что лучше использовать оба аргумента.
А если второе условие не выполняется, но вам ничего не нужно делать, вставьте в ячейку пустое значение.
=ЕСЛИ(E2>100,F2*0.1,””)
Однако эту конструкцию можно использовать в том случае, если значения «True» или «False» будут использоваться другими функциями Excel как булевы значения.
Обратите также внимание, что результирующие булевы значения в ячейке всегда центрируются. Это также можно увидеть на скриншоте выше.
Также, если вам действительно нужно просто проверить условие и получить «True» или «False» («Да» или «Нет»), вы можете использовать следующую конструкцию -…
=IF(E2>100,TRUE,FALSE)
Обратите внимание, что не обязательно использовать инвертированные запятые. Если вы поместите аргументы в инвертированные запятые, результатом функции IF будет текстовое, а не логическое значение.
Функция ЕПУСТО
Если вы хотите проверить, является ли ячейка пустой, вы можете использовать функцию EPUST (ISBLANK), которая имеет следующий синтаксис:
=EPUST(value)
Аргумент value может быть ссылкой на ячейку или диапазон. Если значение относится к пустой ячейке или диапазону, функция возвращает логическое значение TRUE, иначе FALSE.
Функции ИСТИНА и ЛОЖЬ
Функции TRUE и FALSE обеспечивают альтернативный способ записи логических значений TRUE и FALSE. Эти функции не имеют аргументов и выглядят следующим образом:
=TRUTH()
=FALSE().
Например, ячейка A1 содержит логическое выражение. Тогда следующая функция вернет «Pass», если выражение в ячейке A1 равно TRUE:
= IF(A1=ISTIN(); «Пройти»; «Остановиться»)
В противном случае формула вернет значение «Стоп».
Составное условие
Составное условие состоит из простых логических операций AND() и OR(), объединенных вместе.
AND() — это булева операция, которая требует одновременного выполнения всех связанных с ней условий.
OR() — это логическая операция, которая требует одновременного выполнения любого из связанных с ней условий.
Простое условие
Итак, что делает функция IF()? Посмотрите на диаграмму. Вот простой пример того, как эта функция работает для определения знака a.
Блок-схема простого состояния. Определение отрицательных и неотрицательных чисел
Условие a>=0 определяет две возможности: неотрицательное число (нуль или положительное) и отрицательное число. Ниже приведено схематическое изображение формулы в Excel. За условием, за которым следует точка с запятой, следует список опций. Если условие истинно, в ячейке отобразится «неотрицательно», в противном случае — «отрицательно». Это запись, соответствующая ветви формулы «Да», за которой следует «Нет».
Текстовые данные в формуле заключаются в инвертированные запятые, а формулы и числа пишутся без инвертированных запятых.
Если результатом должен быть расчет, рассмотрите следующий пример. Давайте увеличим неотрицательное число на 10, а отрицательное оставим без изменений.
Простая диаграмма состояния. Расчет данных
На диаграмме видно, что если условие выполняется, то число увеличивается на десять, а вычисленное выражение A1+10 (выделено зеленым цветом) записывается в формулу Excel. В противном случае число не меняется, и вычисляемое выражение здесь состоит только из самого обозначения A1 (выделено красным цветом).
Это было краткое введение для новичков, которые только начинают изучать основы Excel. Теперь давайте рассмотрим более серьезный пример, в котором используется условная функция.
Задание:
Процентная ставка прогрессивного налога зависит от дохода. Если доход компании превышает определенную сумму, ставка налога выше. Используя функцию IF, рассчитайте сумму налога.
Решение:
Решение этой проблемы можно увидеть на рисунке ниже. Однако давайте поясним иллюстрацию. Основные исходные данные для решения этой задачи находятся в колонках A и B. В ячейке A5 показан предел дохода, при котором изменяется ставка налога. Соответствующие ставки указаны в ячейках B5 и B6. Доход компании указан в диапазоне ячеек B9:B14. Формула, которая рассчитывает налог, записана в ячейке C9: =If(B9>A$5;B9*B$6;B9*B$5). Эту формулу следует скопировать в ячейки ниже (выделены желтым цветом).
В формуле расчета адреса ячеек записаны как A$5, B$6, B$5. Знак доллара делает часть адреса, перед которой он ставится, постоянной при копировании формулы. Номер строки в адресе ячейки здесь не может изменяться.
Пример функции с несколькими условиями
В функции «ЕСЛИ» также можно ввести несколько условий. В этой ситуации инструкция «IF» вложена в другую. Если условие истинно, ячейка выводит набор результатов, но если условие не истинно, вывод зависит от второго оператора.
- Как и в прошлый раз, «продлите» формулу вниз.
Пример использования «ЕСЛИ»
Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «IF».
- Затем нажмите клавишу Enter. Теперь, чтобы формула появилась в нижних ячейках, просто наведите курсор на правый нижний угол заполненной ячейки, нажмите левую кнопку мыши и, не отпуская ее, переместите курсор в нижнюю часть таблицы.
- Таким образом, мы получили таблицу со столбцом, заполненным функцией «ЕСЛИ».
Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)
При использовании функции ЕСЛИ в Excel можно применять различные операторы для проверки условия. Ниже приведен список операторов, которые вы можете использовать:
Если сумма баллов больше или равна «35», формула возвращает «Pass», в противном случае возвращает «Fail».
Заключение
Одним из самых популярных и полезных инструментов в Excel является функция IF, которая проверяет данные на соответствие заданным условиям и возвращает результат автоматически, исключая тем самым возможность человеческой ошибки. Поэтому знание и умение пользоваться этим инструментом сэкономит вам время не только на выполнение многих задач, но и на поиск возможных ошибок, возникающих при работе в «ручном» режиме.