Привет, друзья! Часто ли вам приходится делать выбор? Например, вы хотите купить новый телефон, но у вас нет полной суммы денег. Взять ссуду или сэкономить? Итак, сегодня мы разберемся, как сделать выбор в таблицах Excel. Это делается с помощью условной функции IF().
Условная функция ЕСЛИ()
Эта функция проверяет истинность одного или нескольких логических выражений и выбирает следующее действие. В логических выражениях используются операционные признаки связи между сравниваемыми значениями.
Реляционные операции в логических выражениях
Простое условие
Для чего нужна функция 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. Знак доллара фиксирует часть адреса, перед которой он устанавливается при копировании формулы. Запрещается менять номер строки в адресе ячейки.
Составное условие
Составное условие состоит из простых логических операций, связанных И () и ИЛИ().
И () — логическая операция, требующая одновременного выполнения всех связанных с ней условий.
ИЛИ () — это логическая операция, которая требует выполнения любого из связанных с ней перечисленных условий.
Логическая операция И()
Например: рассмотрим таблицу записей вступительного экзамена. Чтобы поступить в университет, кандидат должен сдать проходной балл, а по математике оценка должна быть выше 70 баллов.
Внимательно посмотрите на изображение ниже.
В этом примере функция IF () использует составное условие, связанное с логической операцией AND (). Примечание: кандидат Петрова не зачислен, хотя сумма его баллов равна сумме пройденных.
Почему так случилось? Давайте внимательнее рассмотрим условие в нашей формуле = ЕСЛИ (И (E6> = D2; B6> 70); «аккредитовано»; «незарегистрировано»). Для логической операции И () необходимо выполнение всех условий, но у нас есть только одно. Второе условие B6> 70 не выполняется, поэтому составное условие становится ложным. И на экране отображается сообщение «не начислено» (запомните схему — ветка «нет»).
Упражнение:
Перед новым годом в торговой компании организована праздничная распродажа. Рассчитайте сумму продаж с учетом скидки, назначенной при продаже.1. Подсчитайте общую стоимость ваших продаж. Итого = Стоимость * Количество.
2. Определите скидку (процент) с помощью функции ЕСЛИ (). Если дата продажи попадает в период праздничных распродаж, предоставляется скидка, в противном случае скидка равна нулю. При указании условий используйте функцию логического И().
3. Определите сумму продажи с учетом скидки. Сумма продажи со скидкой = Итого * (1- Скидка%).
Решение:
Для проведения расчетов необходимо ввести следующие формулы:
- В ячейке E7: = B7 * C7
- В ячейке F7: = ЕСЛИ (И (D7> = D $ 4; D7
- В ячейке G7: = E7 * (1-F7)
и скопируйте соответствующие столбцы до строки 16 включительно.
Логическая операция ИЛИ()
Давайте посмотрим на пример с логической операцией OR () в той же таблице. Немного изменим состояние задачи. Для зачисления кандидата достаточно иметь более 60 баллов по математике или общий балл не меньше, чем у проходящего. Рисунок ниже.
Кандидат Сидоров был зачислен, но не набрал достаточного количества голосов. Вот формула = SE (OR (B7> 60; E7> D2;); «аккредитовано»; «не аккредитовано»). Здесь используется операция OR (), поэтому достаточно хотя бы одного условия. Так и случилось, первое условие B7> 60 выполняется. Это привело к выводу сообщения о зачислении абитуриента.
Упражнение:
Торговая компания установила дни продажи: последние дни месяца. Рассчитайте сумму продаж с учетом скидки, назначенной в дни продажи.1. Подсчитайте общую стоимость ваших продаж. Итого = Стоимость * Количество.
2. Определите скидку (процент) с помощью функции ЕСЛИ (). Если дата продажи совпадает с датами продажи, назначается скидка, в противном случае скидка равна нулю. При указании условий используйте функцию логического ИЛИ().
3. Определите сумму продажи с учетом скидки. Сумма продажи со скидкой = Итого * (1- Скидка%)
Решение:
Для проведения расчетов необходимо ввести следующие формулы:
- В ячейке E7: = B7 * C7
- В ячейке F7: = ЕСЛИ (ИЛИ (D7 = D $ 4; D7 = E $ 4; D7 = F $ 4), B $ 4,0)
- В ячейке G7: = E7 * (1-F7)
и скопируйте соответствующие столбцы до строки 15 включительно.
Вложенные условия
Если использование составных условий недостаточно для решения проблемы, используется более сложная структура вложенных условий. См. Схему.
В этом примере вторая функция IF () вложена и записана вместо действия, которое вызывается, когда условие первой функции не выполняется. В последних версиях Excel разрешено до 64 вложений.
Упражнение:
Чтобы снизить текучесть кадров, администрация решила платить надбавку за непрерывный опыт работы в своей компании. Рассчитайте бонус за непрерывную работу в соответствии с таблицей бонусов.1. Определите стаж работы сотрудника. Опыт работы = 2018 — Год работы.
2. Используя вложенные функции If, рассчитайте вознаграждение сотрудника. Надбавка (руб.) = Надбавка (%) * Заработная плата
Решение:
Для проведения расчетов необходимо ввести следующие формулы:
- В ячейке D9: = 2018-B9
- В ячейке E9: = IF (D9> = B $ 6; C9 * C $ 6; IF (D9> = B $ 5; C9 * C $ 5,0))
и скопируйте соответствующие столбцы до строки 19 включительно.
В этой статье я попытался максимально подробно и понятно объяснить использование условной функции в Excel. Конечно, в Excel есть и другие условные функции, но о них мы поговорим в следующей статье. Друзья, если эта информация была вам интересна и полезна, не забудьте поделиться ею в социальных сетях. Я также буду рад прочитать ваши комментарии.
PS: удивительные факты
