Что возвращает функция
Значение, указанное, когда два условия истинны или ложны.
Формула ЕСЛИ в Excel – примеры нескольких условий
Чаще всего количество возможных состояний не 2 (контролируемых и чередующихся), а 3, 4 и более. В этом случае вы также можете использовать функцию ЕСЛИ, но теперь вы должны вложить их друг в друга, указав все условия по очереди. Рассмотрим следующий пример.
Разным менеджерам по продажам должны начисляться бонусы в зависимости от выполнения плана продаж. Система мотивации следующая. Если план удовлетворен менее чем на 90%, премия не подлежит уплате, если от 90% до 95% — 10% премии, от 95% до 100% — 20% премии, а если план превышен, то 30%. Как видите, здесь есть 4 варианта. Чтобы указать их в формуле, требуется следующая логическая структура. Если первое условие выполняется, то выполняется первый вариант, в противном случае, если выполняется второе условие, возникает второй вариант, иначе, если .. и т.д. Количество условий может быть довольно большим. В конце формулы указывается последняя альтернатива, для которой не выполняется ни одно из перечисленных выше условий (например, третье поле в нормальной формуле ЕСЛИ). Следовательно, формула выглядит так.
Комбинация функций SE работает таким образом, что при выполнении одного из указанных условий следующие больше не совпадают. Поэтому важно указывать их в правильной последовательности. Если бы мы начали проверку с B2 при написании формулы легко запутаться, поэтому рекомендуется посмотреть подсказку.
В конце убедитесь, что вы закрыли все скобки, иначе Excel выдаст ошибку
Синтаксис функции ЕСЛИ
Вот как выглядит синтаксис этой функции и ее аргументов:
= ЕСЛИ (логическое выражение, значение, если да, значение, если нет»)
Логическое выражение — это (обязательное) условие, которое возвращает истину или ложь (да или нет»);
Значение, если «да» — действие (обязательное), которое выполняется в случае положительного ответа;
Значение, если «нет» — действие (обязательное), которое выполняется в случае отрицательного ответа;
Давайте вместе рассмотрим эти темы подробнее.
Первый аргумент — логичный вопрос. И этот ответ может быть только «да» или «нет», «правда» или «ложь».
Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “=”, “”.
Расширение функционала с помощью операторов «И» и «ИЛИ»
Когда необходимо проверить несколько истинных условий, используется функция И. Суть заключается в следующем: ЕСЛИ a = 1 И a = 2, ТО значение в ELSE является значением c.
Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие выполняется, результат будет истинным. Нижняя строка: ЕСЛИ a = 1 ИЛИ a = 2, ТО значение в ELSE значение c.
С помощью функций И и ИЛИ можно проверить до 30 условий.
Пример использования оператора И:
Пример использования функции ИЛИ:
Простейший пример применения.
Допустим, вы работаете в компании, которая продает шоколад в разных регионах и работает с большим количеством клиентов.
Отдельно стоит выделить продажи, которые происходили в нашем регионе и за рубежом. Для этого вам нужно добавить в таблицу для каждой продажи еще одну характеристику — страну, в которой она произошла. Мы хотим, чтобы этот тег автоматически генерировался для каждой записи (то есть строки).
В этом нам поможет функция SE. Добавим в таблицу данных столбец «Страна». Западный регион — это местные продажи («Местные»), а остальные регионы — зарубежные продажи («Экспорт»).
Применение «ЕСЛИ» с несколькими условиями
Мы только что видели пример использования оператора IF с одним логическим выражением. Но в программе также есть возможность установить более одного условия. В этом случае первая проверка будет выполнена на первой, и в этом случае указанное значение будет отображаться сразу. И только если первое логическое выражение не выполнено, управление вторым будет действовать.
Давайте посмотрим на примере той же таблицы. Но на этот раз усложняем задачу. Теперь нужно делать скидку на женскую обувь в зависимости от вида спорта.
Первое условие — проверка пола. Если это «мужской», сразу отображается значение 0. Если «женский», проверка начинается на основе второго условия. Если спорт беговой — 20%, если теннис — 10%.
Пишем формулу этих условий в нужную нам ячейку.
= SE (B2 = «мужской»; 0; SE (C2 = «ход»; 20%; 10%))
Нажмите Enter и получите результат согласно заданным условиям.
Затем мы распространяем формулу на все оставшиеся строки таблицы.
Операторы сравнения чисел и строк
Операторы сравнения чисел и строк представлены операторами, состоящими из одного или двух равенств и математических неравенств:
- > — другое;
- > = — больше или равно;
- = — равно;
- — не то же самое.
Синтаксис:
| 1 | Результат = Выражение1 Оператор Выражение2 |
- Результат — любая числовая переменная;
- Выражение — это выражение, возвращающее число или строку;
- Оператор — любой оператор сравнения чисел и строк.
Если переменная Result объявлена как Boolean (или Variant), она вернет False и True.Числовые переменные других типов вернут значения 0 (False) и -1 (True).
Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом VBA Excel выдаст ошибку несоответствия типа):
| 1 2 3 4 5 6 7 8 9 10 | Sub Primer1 () При ошибке GoTo Instr Dim myRes As Boolean ‘Сравните строку с числом myRes = «five»> 3 Instr: If Err.Description «» Then MsgBox «Произошла ошибка:» & Err.Description End If Конец подзаголовка |
Сравнение строк начинается с их первых символов. Если они совпадают, сравниваются следующие символы. И так до тех пор, пока символы не станут другими или не закончится одна или обе строки.
Значения буквенных символов увеличиваются в алфавитном порядке, сначала все прописные (прописные) буквы, затем строчные. Если вам нужно сравнить длины строк, используйте функцию Len.
| 1 2 3 | myRes = «семь»> «восемь» ‘myRes = True myRes = «семь»> «восемь»‘ myRes = False myRes = Len («семь»)> Len («восемь») ‘myRes = False |
Одновременное выполнение двух условий
Также в Excel можно просмотреть данные об одновременном выполнении двух условий. В этом случае значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этого действия используется оператор И».
Давайте посмотрим на нашу таблицу в качестве примера. Отныне скидка 30% будет применяться только в том случае, если это женская обувь, предназначенная для бега. При соблюдении этих условий значение ячейки будет 30%, в противном случае — 0.
Для этого воспользуемся следующей формулой:
= ЕСЛИ (И (B2 = «женский»; C2 = «ход»); 30%; 0)
Нажмите клавишу Enter, чтобы просмотреть результат в ячейке.
Как и в приведенных выше примерах, растяните формулу до остальных строк.
Общее определение и задачи
«SE» — это стандартная функция Microsoft Excel. В его обязанности входит контроль соблюдения определенного условия. Когда условие выполняется (истина), значение возвращается в ячейку, в которой используется эта функция, а если оно не выполняется (ложь), возвращается другое значение.
Синтаксис этой функции следующий: «ЕСЛИ (логическое выражение; [функция, если истина]; [функция, если ложь])».
Как правильно записать?
Установите курсор в ячейку G2 и введите знак «=». Для Excel это означает, что формула будет вставлена. Поэтому, как только буква «е» будет нажата дальше, мы получим предложение выбрать функцию, которая начинается с этой буквы. Выбираем «СЭ”.
Кроме того, все наши действия также будут сопровождаться предложениями.
Пишем в качестве первого аргумента: C2 = «Запад». Как и в других функциях Excel, здесь нет необходимости вручную вводить адрес ячейки, достаточно просто щелкнуть по ней мышью. Поэтому мы ставим «» и указываем второй аргумент.
Второй аргумент — это значение, которое примет ячейка G2, если условие, которое мы отметили, выполнено. Это будет слово «Местный”.
Далее мы снова указываем значение третьего аргумента через запятую. Это значение будет принято ячейкой G2, если условие не выполнено: «Экспорт». Не забудьте завершить ввод формулы, закрыв круглые скобки и нажав «Enter”.
Наша функция выглядит так:
= SE (C2 = Запад, Местный, Экспорт”)
Наша ячейка G2 настроена на Local».
Теперь нашу функцию можно скопировать во все остальные ячейки в столбце G.
Дополнительная информация
- В функции ЕСЛИ можно проверить 64 условия одновременно;
- Если один из аргументов функции является массивом, оценивается каждый элемент массива;
- Если вы не укажете условие для аргумента FALSE if_value (value_if_false) в функции, например, после аргумента value_if_true (value_if_true) стоит только запятая (точка с запятой), функция вернет значение «0», если результат оценки функции ЛОЖЬ).
В следующем примере формула = IF (A1> 20, «Разрешить») или = IF (A1> 20; «Разрешить»), где значение_если_если_значение (значение_если_false) не указано, но аргумент value_if_true (value_if_true) по-прежнему является разделенные запятой. Функция вернет «0» всякий раз, когда проверяемое условие не соответствует условиям ИСТИНА).
- Если вы не укажете условие аргумента ИСТИНА (значение_если_ истинное (значение_если_ истинное значение)) в функции, например, условие указано только для аргумента, если_значение (значение_если_ ложь), то формула вернет значение «0», если результат расчет функции ИСТИНА);
В приведенном ниже примере формула = ЕСЛИ (A1> 20; Отклонить) или = ЕСЛИ (A1> 20; Отклонить), где значение_если_ истинное (значение_Если_ истинное значение) не указано, формула будет возвращать «0» всякий раз, когда условие выполняется НАСТОЯЩЕЕ).
Вложенные условия с математическими выражениями.
Вот еще одна типичная проблема: цена за единицу продукта меняется в зависимости от количества. Ваша цель — написать формулу, которая вычисляет цену для любого количества предметов, введенных в определенную ячейку. Другими словами, ваша формула должна проверять разные условия и выполнять разные вычисления в зависимости от диапазона суммы, к которой относится указанное количество товаров.
Эту задачу также можно выполнить с помощью нескольких вложенных функций ЕСЛИ. Логика такая же, как в предыдущем примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными условиями (то есть на соответствующую цену за единицу).
Предполагая, что количество находится в B8, формула будет выглядеть так:
= L8 * SE (La8> = 101; 12; SE (La8> = 50; 14; SE (La8> = 20; 16; SE (La8> = 11; 18; SE (La8> = 1; 22; “”)))))
И вот результат:
Как вы понимаете, этот пример демонстрирует только общий подход, и вы можете легко настроить эту вложенную функцию на основе конкретного действия.
Например, вместо того, чтобы «кодировать» цены в самой формуле, вы можете ссылаться на ячейки, в которых они появляются (ячейки с B2 по B6). Это позволит вам изменить исходные данные без обновления самой формулы:
= L8 * SE (L8> = 101; L6; SE (L8> = 50; L5; SE (L8> = 20; L4; SE (L8> = 11; L3; SE (L8> = 1; L2; “”)))))
Аргументы функции
- логический_тест (logical_test) — это условие, которое вы хотите проверить. Этот аргумент функции должен быть логическим и иметь значение FALSE или TRUE. Аргумент может быть статическим значением или результатом функции или вычисления;
- [value_if_true] ([value_if_true]) — (необязательно) — значение, возвращаемое функцией. Он будет отображаться, если проверяемое значение соответствует условию ИСТИНА;
- [if_value] ([if_false_value]) — (необязательно) — значение, возвращаемое функцией. Он покажет, соответствует ли проверяемое вами условие условию FALSE.
А если один из параметров не заполнен?
Если вас не интересует, что произойдет, например, если интересующее вас условие не выполняется, вы можете опустить второй аргумент. Например, мы предоставляем скидку 10% при заказе более 100 наименований. Мы не указываем никаких аргументов в случае невыполнения условия.
= ЕСЛИ (MI2> 100; F2 * 0,1)
Что будет в результате?
Насколько красиво и удобно — судить вам. Я думаю, что в любом случае лучше использовать оба аргумента.
А если второе условие не выполняется, но ничего делать не нужно, введите в ячейку пустое значение.
= ЕСЛИ (MI2> 100; FA2 * 0,1,””)
Однако эту конструкцию можно использовать в случае, если значение «Истина» или «Ложь» используется другими функциями Excel в качестве логических значений.
Также обратите внимание, что результирующие логические значения в ячейке всегда центрируются. Это видно на скриншоте выше.
Кроме того, если вам действительно нужно проверить некоторые условия и получить «Истина» или «Ложь» («Да» или «Нет»), вы можете использовать следующую конструкцию –
= ЕСЛИ (MI2> 100; ИСТИНА; ЛОЖЬ)
Учтите, что здесь кавычки использовать не нужно. Если вы заключите аргументы в кавычки, функция ЕСЛИ вернет текстовые значения, а не логические значения.
Функция ЕПУСТО
Если вам нужно определить, пуста ли ячейка, вы можете использовать функцию ISBLANK, которая имеет следующий синтаксис:
= БАЛАНС (значение)
Аргумент значения может быть ячейкой или ссылкой на диапазон. Если значение относится к пустой ячейке или диапазону, функция возвращает логическое значение ИСТИНА, в противном случае — ЛОЖЬ.
Функции ИСТИНА и ЛОЖЬ
Функции ИСТИНА и ЛОЖЬ предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят так:
= ИСТИНА()
= ЛОЖЬ()
Например, ячейка A1 содержит логическое выражение. Затем следующая функция вернет значение «Пройдено», если выражение в ячейке A1 ИСТИНА:
= ЕСЛИ (A1 = ИСТИНА (), «Пройден», «Стоп”)
В противном случае формула вернет «Стоп”.
Составное условие
Составное условие состоит из простых логических операций, связанных И () и ИЛИ().
И () — логическая операция, требующая одновременного выполнения всех связанных с ней условий.
ИЛИ () — это логическая операция, которая требует выполнения любого из связанных с ней перечисленных условий.
Простое условие
Для чего нужна функция SE ()? Взгляните на схему. Вот простой пример того, как работает функция при определении знака числа a.
Блок-схема «Простое состояние». Определение отрицательных и неотрицательных чисел
Условие a> = 0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное число. Ниже представлена схема написания формулы в Excel. Параметры перечислены после условия и разделены точкой с запятой. Если условие истинно, в ячейке будет отображаться «неотрицательный» текст, в противном случае — «отрицательный». То есть запись, соответствующая ветви схемы, — «Да», за которой следует «Нет».
Текстовые данные в формуле заключаются в кавычки, а формулы и числа пишутся без них.
Если результатом должны были быть данные, полученные в результате вычислений, см. Следующий пример. Увеличиваем неотрицательное число на 10, а отрицательное оставляем без изменений.
Блок-схема «Простое состояние». Расчет данных
На диаграмме показано, что при выполнении условия число увеличивается на десять и вычисленное выражение A1 + 10 (выделено зеленым цветом) записывается в формулу Excel. В противном случае число не меняется и здесь вычисляемое выражение состоит только в обозначении самого числа А1 (выделено красным).
Это было краткое введение для новичков, только начинающих знакомиться с основами Excel. Теперь давайте посмотрим на более серьезный пример с использованием условной функции.
Упражнение:
Прогрессивная ставка зависит от дохода. Если доход компании превышает определенную сумму, ставка налога выше. Используя функцию SE, рассчитайте сумму налога.
Решение:
Решение этой проблемы можно увидеть на следующем рисунке. Но давайте все же попробуем прояснить эту иллюстрацию. Основные исходные данные для решения этой проблемы можно найти в столбцах 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 в другой. Когда условие выполнено, указанный результат отображается в ячейке, но если условие не выполняется, отображаемый результат зависит от второго оператора.
- Например, возьмем тот же стол с выплатами премий до 8 марта. Но на этот раз по условиям размер бонуса зависит от категории сотрудника. Женщины со статусом ключевого персонала получают премию в размере 1000 рублей, вспомогательный персонал — только 500 рублей. Конечно, мужчины абсолютно не имеют права на этот вид оплаты, независимо от категории.
- Первое условие — если наемный работник — мужчина, размер полученного бонуса равен нулю. Если это значение ложно и сотрудник не мужчина (т.е женщина), проверяется второе условие. Если женщина принадлежит к основному персоналу, в ячейке будет отображаться значение «1000», а в противном случае — «500». В виде формулы это будет выглядеть так: «= SE (B6 =« мужской »;« 0 »; SE (C6 =« Главный штаб »;« 1000 »;« 500″))».
- Вставьте это выражение в верхнюю ячейку столбца «Бонус к 8 марта».
- Как и в прошлый раз, «растягиваем» формулу вниз.
Пример использования «ЕСЛИ»
Теперь рассмотрим конкретные примеры использования формулы ЕСЛИ».
- У нас есть таблица выплат. К 8 марта все женщины имеют право на бонус в размере 1000 рублей. В таблице есть столбец с указанием пола сотрудников. Следовательно, мы должны вычислить женщин из предоставленного списка и в соответствующие строки столбца «Бонус до 8 марта» ввести «1000». При этом, если пол не совпадает с женским, то значение таких строк должно соответствовать «0». Функция будет выглядеть так: «ЕСЛИ (B6 =« женский »;« 1000 »;« 0 »)». То есть при результате проверки «истина» (если выясняется, что строка данных занята женщиной с параметром «женский») будет выполнено первое условие — «1000», а если «ложь»), то, соответственно, последний равен «0».
- Мы вводим это выражение в верхнюю ячейку, где должен отображаться результат. Поставьте знак «=» перед выражением «=».
- Затем нажмите клавишу Enter. Теперь, чтобы эта формула появилась в нижних ячейках, просто переместите указатель в правый нижний угол заполненной ячейки, нажмите левую кнопку мыши и, не отпуская ее, переместите курсор в нижнюю часть таблицы.
- Итак, мы получили таблицу с одним столбцом, заполненным функцией «ЕСЛИ».
Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)
При использовании функции ЕСЛИ в Excel вы можете использовать различные операторы для проверки статуса. Вот список операторов, которые вы можете использовать:
Если сумма баллов больше или равна «35», то формула возвращает «Пройден», в противном случае — «Неудачно”.
Заключение
Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ, которая проверяет данные на соответствие условиям, которые мы установили, и автоматически выдает результат, исключая возможность ошибок из-за человеческой ошибки. Поэтому знания и умение пользоваться этим инструментом сэкономят время не только на многих задачах, но и на поиске возможных ошибок за счет «ручного режима работы.