Кейс 4.
Логические функции и расчеты
Часто встречаются ситуации, когда способы расчета зависят от различных условий. В ситуациях подобного рода облегчают решения логические функции.
Кейс из этого урока объединяет знания из всех предыдущих.
Удачи!
Логические функции, которые облегчают решения, когда способы расчета зависят от различных условий.
Необходимо посчитать доход менеджеров. Доход состоит из оклада и бонусов, зависящих от продаж.
В файле с исходными данными представлена таблица с продажами по каждому менеджеру за несколько месяцев. В ситуациях, когда в расчете используется несколько условий, удобно применять логическую функцию "ЕСЛИ".
Ежемесячный оклад составляет 20 000 ₽
Размер бонуса зависит от суммы месячных продаж менеджера:
- если продаж больше чем на 200 000 руб. - 15% от суммы продаж
- если продажи больше 150 000, но меньше 200 000 руб. - 10% от суммы продаж
- если продажи больше 100 000, но меньше 150 000 руб. - 5% от суммы продаж
Вид функции: = ЕСЛИ(Логическое выражение;Значение если истина;Значение если ложь)
Аргументы
Описание
Логическое выражение
Значение ячейки или формула, по которой проверяем на совпадение
Значение если истина
Значение или формула при совпадении лог. выражения
Значение если ложь
Значение или формула при совпадении лог. выражения
Аргументы
Описание
Логическое выражение
Значение ячейки или формула, по которой проверяем на совпадение
Значение если истина
Значение или формула при совпадении лог. выражения
Значение если ложь
Значение или формула при совпадении лог. выражения
Функция сравнивает заданное в ней значение, ссылку или формулу и возвращает заданные значения, ссылку или формулу в случае совпадения
и, аналогично, в случае не совпадения.
Возможно использование функции в связке с другими функциями, например: =ЕСЛИ(ВПР), ЕСЛИ(ЕСЛИ), ЕСЛИ(И) и т.д.
Давайте разберем небольшой пример:
Есть таблица, в которой содержится информация о доходах и расходах. Для каждой статьи проставлен тип "доход" или "расход".
Проставим перед каждой суммой расхода знак "-"
- Для поля "Логическое выражение: сравниваем его значение со словом "Расход", то есть: если ячейка В" равна "Расход", то... . (текстовые значения всегда нужно заключать в кавычки).
- Значение если истина: ставим знак минус и указываем на ячейку с суммой "С2"
- Значение если ложь: в данном случае всего два варианта и, если наше значение не совпало, то значит это доход. Соответственно, указываем на ячейку "С2"
Вид формулы для расчета дохода: оклад + продажи * (%)
В нашем случае нужно использовать функцию из нескольких условий.
В нашем случае нужно использовать функцию из нескольких условий.
Схема "ЕСЛИ" для нескольких условий
Начинаем заполнять таблицу "ФОТ"
Наша функция будет состоять из нескольких функций.
В таком случае нужно вводить формулу в строке формул.
Встаем на ячейку "В10" и вводим: =если(
Отображается подсказка с аргументами функций.
3.1. Вводим первое логическое выражение.
При вводе формулы вручную, ее аргументы нужно отделять точкой с запятой ";"
3.2. Указываем "значение если истина":
3.3. Аргументом для "Значение если ложь" будет еще одна функция с условием:
Поскольку остальные два условия представляют собой сравнение с двумя числами, мы будем использовать функцию "И"
Вид функции: = И(Логическое выражение1;Логическое выражение2; Логическое выражение3;...)
Функция проверяет совпадение всех логических выражений, входящих в состав ее аргументов.
Функция проверяет совпадение всех логических выражений, входящих в состав ее аргументов.
4.1. Аргумент логического выражения следующей функции будет выглядеть следующим образом:
4.2. Указываем "Значение если истина"
4.3. И в точности так же указываем наше последнее условие
4.4. Прописываем "Значение если истина"
Прописываем "Значение если ложь". В последнем условии это уже "0" и закрываем условия скобками. Их должно быть ровно столько, сколько и открытых скобок
Далее нажимаем "ENTER"
5.1. Таким образом мы вывели формулу для бонуса, но нам нужно получить расчет дохода. Поэтому добавляем оклад в формулу:
5.2. Можно было добавить значение оклада в каждое из условий в формуле, но из-за этого формула станет объемной и сложночитаемой.
Далее растягиваем формулу на всю таблицу и получаем результат.
Хочешь закрепить материал?
Реши задачу самостоятельно!
Имеется таблица, в которой содержится информация о количестве отработанных часов сотрудниками:
И есть таблица с тарифами;
Пользуясь функцией "ЕСЛИ", посчитать доходы сотрудников.
Не забывайте про абсолютную и относительные адресации ячеек!
КЕЙС 3
Функция "СУММЕСЛИМН"
Функция, которую следует использовать при суммировании значения столбца таблицы по нескольким условиям.
КЕЙС 5
Сортировка данных в таблицах
Обзор правильного упорядочивания информации в таблицах с помощью сортировки.