Бонус по ступенчатой шкале
Практический разбор: рассчитать ставку бонуса по выполнению плана. Таблица под задачу, контрольный результат, ошибки конкретной формулы, самопроверка и самостоятельное задание.
=ЕСЛИМН(B2>=120%;10%;B2>=100%;5%;B2>=90%;2%;ИСТИНА;0%)=IFS(B2>=120%,10%,B2>=100%,5%,B2>=90%,2%,TRUE,0%)В русской локализации Excel аргументы чаще разделяются точкой с запятой, а в английской — запятой. При копировании формул учитывайте региональные настройки.
Короткий ответ
В этой статье разберем, как в Excel рассчитать ставку бонуса по выполнению плана. Сначала — готовая формула, затем подробная логика, рабочий пример, проверка результата, типовые ошибки и варианты применения в реальной таблице.
Зачем использовать этот прием
Практическая задача этой статьи — рассчитать ставку бонуса по выполнению плана. Именно поэтому пример ниже использует поля «Менеджер» и «Выполнение плана», а не универсальную таблицу с абстрактными «Значение 1 / Значение 2».
Контрольный результат для примера: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%. Сначала получите его вручную по данным таблицы, затем используйте формулу. Так вы проверяете не только синтаксис Excel, но и саму бизнес-логику расчёта.
Пример исходных данных
| Менеджер | Выполнение плана | Бонус |
|---|---|---|
| Анна | 88% | 0% |
| Борис | 90% | 2% |
| Вера | 100% | 5% |
| Глеб | 119% | 5% |
| Дина | 125% | 10% |
Контроль: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
Таблица для «Бонус по ступенчатой шкале» подобрана так, чтобы результат можно было проверить вручную. При переносе в свой файл сохраняйте смысл ключевых полей, даже если рабочие названия столбцов отличаются.
Подготовка данных
- Определение kpi и когорты.
- Период расчёта.
- Правила возвратов/отмен и нулевой базы.
- Отдельно проверьте контрольную строку: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
- Порядок условий — часть алгоритма: сначала размещайте более узкие/высокие пороги, если широкое условие иначе перехватит строку.
Разбираем формулу пошагово
1. Сформулируйте задачу словами
Запишите правило без Excel:
> «Для каждой строки нужно рассчитать ставку бонуса по выполнению плана.»
Для «Бонус по ступенчатой шкале» сначала сформулируйте правило однозначно и получите ожидаемый ответ словами. Формулу стоит вводить только после того, как бизнес-логика не допускает двух разных трактовок.
2. Определите входные данные
Отметьте:
- откуда берется исходное значение;
- какой диапазон участвует;
- какие критерии обязательны;
- какой результат ожидается;
- что делать при пустых данных;
- что делать при ошибке.
3. Введите формулу
Русская версия:
=ЕСЛИМН(B2>=120%;10%;B2>=100%;5%;B2>=90%;2%;ИСТИНА;0%)
Английская версия:
=IFS(B2>=120%,10%,B2>=100%,5%,B2>=90%,2%,TRUE,0%)
Формулу для «Бонус по ступенчатой шкале» сначала проверьте на одной понятной строке; только после совпадения с ручным ответом переносите её на весь диапазон.
4. Проверьте вручную
Для контрольной строки «Бонус по ступенчатой шкале» получите ожидаемый результат без Excel: поисковый ответ найдите в таблице глазами, а расчётный пересчитайте отдельно. Затем сравните ручной ответ с формулой.
5. Проверьте ссылки
В Excel:
A2— относительная ссылка;$A$2— полностью абсолютная;$A2— фиксирован столбец;A$2— фиксирована строка.
В «Бонус по ступенчатой шкале» неверно закреплённая ссылка может не дать ошибки Excel, но сместить диапазон после копирования. Поэтому сравните адреса ссылок в первой и последней формуле.
---
Ключевой нюанс именно этой задачи
Если шкала часто меняется, лучше вынести пороги и ставки в отдельный справочник, а не зашивать их в формулу.
До формулы зафиксируйте определение метрики, период, источник данных и правила учета возвратов/исключений.
---
Как читать формулу, а не заучивать ее
Для «Бонус по ступенчатой шкале» читайте формулу через задачу рассчитать ставку бонуса по выполнению плана, а не по названиям функций. Сначала найдите в выражении входное значение или ключ, затем диапазон/порог, после этого — возвращаемый или вычисляемый результат. Контрольная точка для текущего примера: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
Порядок условий — часть алгоритма: сначала размещайте более узкие/высокие пороги, если широкое условие иначе перехватит строку. Ключевой смысл из исходного материала: Если шкала часто меняется, лучше вынести пороги и ставки в отдельный справочник, а не зашивать их в формулу.
Рабочий сценарий №1: ежедневная выгрузка
Используйте приём в процессе, где действительно нужно рассчитать ставку бонуса по выполнению плана. Не начинайте с массового копирования формулы: возьмите одну понятную строку из примера и подтвердите результат: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%. После этого перенесите логику на 5–10 реальных строк с разными исходными значениями.
Особенность именно этой темы: Если шкала часто меняется, лучше вынести пороги и ставки в отдельный справочник, а не зашивать их в формулу.
Рабочий сценарий №2: контроль качества
Сделайте рядом с основным результатом контроль качества. Для статьи «Бонус по ступенчатой шкале» минимум один контроль должен проверять следующую вещь: числитель и знаменатель относятся к разным периодам. Второй контроль должен показывать строки, где исходные данные не позволяют уверенно применить правило.
Проверочный сценарий: Проверьте значения ровно на каждом пороге и между порогами.
Рабочий сценарий №3: подготовка к сводной таблице
После расчёта не прячьте результат внутри исходного листа. Выведите его в отчёт в том разрезе, в котором принимается решение. Для задачи «рассчитать ставку бонуса по выполнению плана» рядом полезно показывать исходные значения, из которых получился результат, а также число строк/объектов в базе расчёта.
Сводная особенно полезна для KPI по менеджерам, каналам и периодам. Всегда показывайте базовые объёмы рядом с процентными показателями.
Частые ошибки
Ошибка 1. Числитель и знаменатель относятся к разным периодам
В этой теме это особенно опасно, потому что результат может выглядеть правдоподобно. Проверьте значения ровно на каждом пороге и между порогами.
Ошибка 2. Нулевая база превращена в осмысленный процент
Сверьте результат с контрольной точкой: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
Ошибка 3. Процент показывают без абсолютных объёмов
Если структура данных изменилась, заново сопоставьте аргументы формулы со столбцами, а не просто протягивайте старое выражение.
Ошибка 4. Игнорируется нюанс конкретной функции
Если шкала часто меняется, лучше вынести пороги и ставки в отдельный справочник, а не зашивать их в формулу.
Профессиональная проверка формулы
Для «Бонус по ступенчатой шкале» одной обычной строки недостаточно. Прогоните отдельные тесты на корректный пример, изменение входа, качество данных и пограничное значение:
- Контрольный пример: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
- Изменение входа: Проверьте значения ровно на каждом пороге и между порогами.
- Качество данных: специально создайте проблему «числитель и знаменатель относятся к разным периодам» и убедитесь, что вы её замечаете.
- Граница правила: проверьте значение непосредственно на пороге/границе, если она есть в формуле.
- Массовое копирование: сравните первую, вторую и последнюю формулу после протягивания.
- Независимый итог: пересчитайте хотя бы одну строку вручную или альтернативным способом.
Чек-лист аудита
Советы для больших таблиц
Для статьи «Бонус по ступенчатой шкале» при росте файла особенно важно сохранить проверяемость задачи «рассчитать ставку бонуса по выполнению плана». Для KPI сначала создайте единое определение метрики, затем автоматизацию. На больших объёмах переносите повторяющиеся меры в сводную/Power Pivot, сохраняя контрольные примеры. Порядок условий — часть алгоритма: сначала размещайте более узкие/высокие пороги, если широкое условие иначе перехватит строку. Контроль качества не должен исчезать после оптимизации: оставьте хотя бы одну независимую строку, где ожидается результат «88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.».
Когда лучше использовать Power Query
Для задачи «рассчитать ставку бонуса по выполнению плана» Power Query становится предпочтительнее, когда обработка повторяется с каждой новой выгрузкой. Power Query нужен для подготовки фактов продаж: статусы, возвраты, источники, периоды. Сам KPI затем проще считать в сводной или модели данных. В случае «Бонус по ступенчатой шкале» формулу имеет смысл оставить на листе, если пользователь должен менять параметры и сразу видеть пересчёт.
Когда лучше сводная таблица
В контексте «Бонус по ступенчатой шкале» сводная нужна не вместо формулы, а после подготовки корректного поля/показателя. Сводная особенно полезна для KPI по менеджерам, каналам и периодам. Всегда показывайте базовые объёмы рядом с процентными показателями. Для проверки всегда возвращайтесь к исходным строкам, на которых подтверждается: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
Практическое задание
Создайте таблицу минимум из 20 строк и примените:
=ЕСЛИМН(B2>=120%;10%;B2>=100%;5%;B2>=90%;2%;ИСТИНА;0%)
После этого специально добавьте:
- пустое значение в тесте «Бонус по ступенчатой шкале»;
- дубликат ключа или строки;
- нулевое значение там, где оно допустимо;
- граничный сценарий правила;
- строку с неправильным типом данных.
Запишите, как меняется результат и почему.
---
Задание повышенной сложности
Возьмите практическое задание именно по теме «Бонус по ступенчатой шкале» и добавьте исключение «числитель и знаменатель относятся к разным периодам». Сделайте так, чтобы пользователь либо получил корректный результат, либо увидел понятный контроль качества, а не правдоподобное неверное число.
Затем выполните тест: Проверьте значения ровно на каждом пороге и между порогами. Финальная проверка должна по-прежнему объяснять контрольный результат: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
Что запомнить
- Что решаем: рассчитать ставку бонуса по выполнению плана.
- Контрольный результат: 88%→0%; 90%→2%; 100%→5%; 119%→5%; 125%→10%.
- Ключевой нюанс: Если шкала часто меняется, лучше вынести пороги и ставки в отдельный справочник, а не зашивать их в формулу.
- Главный риск: числитель и знаменатель относятся к разным периодам.
- Практический принцип: Порядок условий — часть алгоритма: сначала размещайте более узкие/высокие пороги, если широкое условие иначе перехватит строку.
- Следующий шаг: примените формулу на собственных данных и сохраните независимую контрольную строку.
Что изучить дальше
После темы «Бонус по ступенчатой шкале» сравните два способа решить близкую задачу: формулой на листе и преобразованием данных. Для сценария «рассчитать ставку бонуса по выполнению плана» ориентир по Power Query такой: Power Query нужен для подготовки фактов продаж: статусы, возвраты, источники, периоды. Сам KPI затем проще считать в сводной или модели данных. Если же нужен групповой отчёт по периодам/сегментам, используйте следующий ориентир: Сводная особенно полезна для KPI по менеджерам, каналам и периодам. Всегда показывайте базовые объёмы рядом с процентными показателями.
Не знаете, что учить дальше?
Проверьте свой уровень Excel за 7–10 минут
18 рабочих ситуаций покажут сильные стороны и темы, которые дадут вам самый заметный прирост.
Практика по системе
Закрепите формулы на рабочих задачах
В курсах Tablicus теория связана с практикумами, таблицами и автоматической проверкой результата.