Формула для расчета премии в excel

На данном этапе расчет производится с использованием логических функций ЕСЛИ. Первая формула в ячейке АА2 (рис. 5.25) создается по принципу: если служащий проработал менее года (значение ячейки Z2 сравнивается со значением ячейки U4), то премия равна произведению значения оклада, указанного в ячейке Е2, на коэффициент, внесенный в ячейку V4. Таким образом, в ячейке АА2 используется формула, приведенная ниже:

Так как коэффициент в ячейке V4 отсутствует, то есть равен нулю, то и размер премии равен нулю.

Аналогичные формулы введены в ячейки АВ2, АС2 и AD2:

Но формула в ячейке АЕ2 несколько от них отличается:

Если количество проработанных лет больше либо равно количеству лет, указанному в ячейке U8, то размер оклада умножается на коэффициент, указанный в ячейке V8.

Вложение формул

Выполним операцию вложения формул из ячеек Х2 и Y2 в формулу, которая находится в ячейке Z2:

Процесс вложения формул с функциями ЕСЛИ начнем с ячейки АЕ2 – мы вкладываем ее в ячейку AD2, а из ячейки AD2 – в ячейку АС2 и т. д. В ячейку АА2 введем формулу:

Формулу из ячейки Z2 вложим вместо ссылок на эту ячейку в формулу ячейки АА2:

Теперь в расчетах участвуют только формулы, расположенные в ячейках S2 и АА2. В ячейке AF2 создадим формулу, основанную на логической функции ЕСЛИ:

Данная формула определяет, какое логическое значение находится в ячейке S2. Если это значение ИСТИНА, формула возвращает сумму премии. Если значение ЛОЖЬ, то возвращается сообщение Ошибка!.

Обратите внимание, что третьим аргументом формулы является текст. Это означает, что в функцию ЕСЛИ можно вводить любой текст (в частности, разного рода сообщения).

В формулу из ячейки AF2 вместо ссылок на адреса ячеек мы введем находящиеся в них формулы. В результате получим такую формулу:

Теперь рассмотрим лист Оклады целиком, со всеми используемыми для расчета таблицами (рис. 5.26).

Лист состоит из следующих элементов:

список с окладами (диапазон А1:Е11);
фрагмент базы данных сотрудников (G1:L11);
таблица с условиями начисления премии (U1:V8);
таблица с информацией о количестве полных проработанных на предприятии лет(Z1:Z11);
область вложенных формул (AF2:AF11).

KPI – показатель эффективности, позволяющий объективно оценить результативность выполняемых действий. Данная система применяется для оценки различных показателей (деятельности всей компании, отдельных структур, конкретных специалистов). Она выполняет не только функции контроля, но и стимулирует трудовую активность. Часто на основе KPI строится система оплаты труда. Это методика формирования переменной части зарплаты.

KPI ключевые показатели эффективности: примеры в Excel

Стимулирующий фактор в системе мотивации KPI – денежное вознаграждение. Получить его может тот работник, который выполнил поставленную перед ним задачу. Сумма премии / бонуса зависит от результата конкретного сотрудника в отчетном периоде. Объем вознаграждения может быть фиксированным или выражаться в процентах к окладу.

Каждое предприятие определяет ключевые показатели эффективности и вес каждого индивидуально. Данные зависят от задач компании. Например:

Цель – обеспечить план продаж продукта в размере 500 000 рублей ежемесячно. Ключевой показатель – план продаж. Система измерения: фактическая сумма продаж / плановая сумма продаж.
Цель – повысить сумму отгрузки в периоде на 20%. Ключевой показатель – средняя сумма отгрузки. Система измерения: фактическая средняя величина отгрузки / плановая средняя величина отгрузки.
Задача – увеличить число клиентов на 15% в определенном регионе. Ключевой показатель – число клиентов в базе данных предприятия. Система измерения: фактическое число клиентов / плановое число клиентов.

Разброс коэффициента (весы) предприятие также определяет самостоятельно. Например:

Выполнение плана менее 80% – недопустимо.
Выполнение плана 100% – коэффициент 0,45.
Выполнение плана 100-115% – коэффициент 0,005 за каждые 5%.
Отсутствие ошибок – коэффициент 0,15.
В отчетном периоде не было замечаний – коэффициент 0,15.

Это лишь возможный вариант определения мотивационных коэффициентов.

Ключевой момент в измерении KPI – отношение фактического показателя к плановому. Практически всегда заработная плата сотрудника складывается из оклада (постоянной части) и премии (переменной / изменяемой части). Мотивационный коэффициент влияет на формирование переменной.

Предположим, что соотношение постоянной и изменяемой частей в зарплате – 50 × 50. Ключевые показатели эффективности и вес каждого из них:

Примем следующие значения коэффициентов (одинаковые для показателя 1 и показателя 2):

Таблица KPI в Excel:

Оклад – постоянная часть заработной платы зависит от количества отработанных часов. Для удобства расчетов мы предположили, что фиксированная и переменная часть зарплаты равны.
Процент выполнения плана продаж и плана работ рассчитывается как отношение фактических показателей к плановым.
Для расчета премии используются коэффициенты. Формулы в Excel расчета KPI по каждому сотруднику: Мы приняли, что влияние показателя 1 и показателя 2 на сумму премиальных одинаковое. Значения коэффициентов тоже равны. Поэтому для расчета показателя 1 и показателя 2 используются одинаковые формулы (меняются только ссылки на ячейки).
Формула для расчета суммы премии к начислению – =C3*(F3+G3). Плановую премию умножаем на сумму показателя 1 и показателя 2 по каждому сотруднику.
Заработная плата – оклад + премия.

Это примерная таблица KPI в Excel. Каждое предприятие составляет собственную (с учетом особенностей работы и системы премирования).

Матрица KPI и пример в Excel

Для оценки работников по ключевым показателям эффективности составляется матрица, или соглашение о целях. Общая форма выглядит так:

Ключевые показатели – критерии, по которым оценивается работа персонала. Для каждой должности они свои.
Веса – числа в интервале от 0 до 1, общая сумма которых равняется 1. Отражают приоритеты каждого ключевого показателя с учетом задач компании.
База – допустимое минимальное значение показателя. Ниже базового уровня – отсутствие результата.
Норма – плановый уровень. То, что сотрудник должен выполнять обязательно. Ниже – работник не справился со своими обязанностями.
Цель – значение, к которому нужно стремиться. Сверхнормативный показатель, позволяющий улучшить результаты.
Факт – фактические результаты работы.
Индекс KPI показывает уровень результата по отношению к норме.

Формула расчета kpi:

Индекс KPI = ((Факт – База) / (Норма – База)) * 100%.

Пример заполнения матрицы для офис-менеджера:

Коэффициент результативности – сумма произведений индексов и весов. Оценка эффективности сотрудника наглядно показана с помощью условного форматирования.

Функция ЕСЛИ используется в случае, когда результат вычисления зависит от выполнения некоторого условия. Условие записывается в виде логического выражения.

Логическое выражение – это выражение, содержащее константы, формулы и ссылки на ячейки, соединенные знаками отношений:

>= (больше или равно);

> истина (условие выполняется) или ложь (условие не выполняется).

Текстовые константы, входящие в логическое выражение или другие аргументы функции, должны быть заключены в кавычки.

Пример 1.

Задание: вычислить стоимость перевозок (тариф зависит от времени суток).

В ячейке С6 вызвать функцию ЕСЛИ.
Заполнить поля аргументов функции, как показано на рисунке 5.9.

Щелкнуть Ok (или нажать ).

В строке формул отобразится функция:

Скопировать функцию вниз по столбцу, протянув маркер заполнения.

Пример 2

Задание: вычислить премию в размере 10% от превышения плана продаж ( если план не превышен, премия не назначается).

Функция для вычисления премии в ячейке С4 имеет вид:

Поля аргументов заполняются следующим образом:

Вложенные логические функции

Функция ЕСЛИ допускает до 7 уровней вложения в качестве аргументов для конструирования более сложных проверок.

Пример 3

Надбавка за стаж вычисляется по формуле:

Надбавка=
		0, если стаж менее 5 лет;
		10% от оклада, если стаж от 5 до 10 лет;
		20% от оклада, если стаж не менее 10 лет.

Для расчета требуется проверка двух условий, следовательно, необходимо применить вложенную логическую функцию. Такая функция (в ячейке Е3) может выглядеть, например, следующим образом:

=ЕСЛИ(D3 Значение_если_ложь ) и щелкнуть на имени функции в левой части строки формул (там отображается имя функции ЕСЛИ , т.к. эта функция использовалась последней):

Откроется новое окно аргументов функции с пустыми полями для ввода вложенной логической функции. Заполнить поля:

Щелкнуть Ok (или нажать ). В строке формул отобразится вложенная логическая функция