Формула для расчета премии в 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).

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

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

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

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

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

Пример 1.

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

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

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

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

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

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

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

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

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

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

    Пример 3

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

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

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

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

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

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

    Copyright © 2008-2019
    Ющик Е.В. e-mail: veta@comp5.ru

    Читайте также:  Интернет банк хоум кредит банки ру

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

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

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

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

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

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

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

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

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

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

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

    Читайте также:  Где взять кредит на ремонт квартиры

    Таблица KPI в Excel:

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

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

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

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

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

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

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

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

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