Как рассчитать премию от оклада в эксель

Как рассчитать премию в экселе?

Рассчитать премию в программе эксель довольно просто, нужно только понимать какой алгоритм начисления. Давайте рассмотрим один из многочисленных вариантов.

Премия начисляется исходя из отработанного стажа, сотрудники, отработавшие менее пяти лет, должны получить десять процентов от оклада. Сотрудники со стажем от пяти до десяти лет получают премию в размере двадцать пять процентов, а кто больше десяти лет пятьдесят процентов.

Так как для премии действуют разные условия и чтобы сделать быстрый расчет, воспользуемся функцией «Если», она будет состоять из двух частей, сначала напишем первую часть в ячейке «D2»: =ЕСЛИ(B2<5;10%*C2;), данная ветка будет работать только для людей со стажем меньше пяти лет.

Теперь напишем второе условие для промежутка пять и десять лет и выше, формула преобразуется в следующий вид: =ЕСЛИ(B2<5;10%*C2;ЕСЛИ(5

Остается только скопировать формулу из ячейки «D2» на диапазон ячеек «D3» по «D6» и мы сделали несложную формулу расчета премии, в зависимости от отработанного стажа.

Практическая работа № 4

Цель занятия : Применение относительной и абсолютной адресаций для финансовых расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги.

1. Запустите табличный процессор Microsoft Excel.
2. Сохраните в своей папке Работа в Excel на диске D: рабочую книгу под именем Ведомость.xlsx

Создать таблицы ведомости начисления заработной платы за два месяца на различных листах электронной книги, произвести расчеты, условное форматирование, сортировку, установить комментарии к отдельным ячейкам и выполнить защиту данных .

1. Создайте таблицу расчета заработной платы по образцу

1. Произвести расчеты во всех столбцах таблицы.

Формулы для расчета:

• При расчете Премии используется формула: Оклад * %Премии , то есть в ячейке D5 наберите формулу = $D$4*C5, скопируйте формулу
• При расчете Всего начислено используется формула: Оклад + Премия
• При расчете Удержания используется формула:

Всего начислено * %Удержания , для этого в ячейке F5 наберите формулу

• При расчете К выдаче используется формула:

Всего начислено – Удержания.

1. Рассчитайте итоги по столбцам, а также минимальный, максимальный и средний доходы.
2. Переименуйте Лист 1 в – Зарплата октябрь.
3. Скопируйте содержимое листа «Зарплата октябрь» на новый лист из контекстного меню на ярлыке листа.
4. Присвоить скопированному листу имя Зарплата ноябрь.
5. Измените значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул.
6. Между колонками Премия и Всего начислено вставьте новую колонку Доплата.
7. Значение доплаты примите равным 5 %.
8. Рассчитайте значение доплаты для всех сотрудников по формуле: Оклад * % Доплаты.
9. Измените формулу для расчета значений колонки Всего начислено :

Оклад + Премия + Доплата

УСЛОВНОЕ ФОРМАТИРОВАНИЕ ЯЧЕЕК

1. Перейдите на лист – Ведомость за октябрь
2. Зададим условное форматирование для чисел в столбце К выдаче по следующим условиям:

• значений меньше 5000 – выделить красным цветом шрифта
• значения между 5000 и 7000 – выделить белым цветом шрифта на красном фоне
• значения между 7000 и 10000 – зеленым цветом шрифта;
• значения большие или равно 10000 – синим цветом шрифта.

• Выделите числовой диапазон ячеек – К выдаче (G5:G18)
• На странице ленты Главная разверните кнопку Условное форматирование, Правило выделения ячеек, Меньше

• Заполните открывшееся окно как это показано на рисунке и нажмите ОК
• Чтобы задать второе условие дайте команду Условное форматирование, Правило выделения ячеек, Между
• Заполните открывшееся окно как показано на рисунке ниже, в Пользовательском формате задайте цвет шрифта – белый, цвет заливки – красный

• Самостоятельно задайте условное форматирование для оставшихся двух видов значений:

• значения между 7000 и 10000 – зеленым цветом шрифта;
• значения большие или равно 10000 – синим цветом шрифта.

1. Проведите сортировку по табельному номеру в порядке возрастания. Для этого

• Выделите диапазон A5:G18
• На странице ленты Данные нажмите кнопку Сортировка
• Заполните диалоговое окно как на рисунке

1. А теперь выполним сортировку фамилий в алфавитном порядке возрастания. Для этого

• Выделите диапазон A5:G18
• На странице ленты Данные нажмите кнопку Сортировка
• Заполните диалоговое окно как на рисунке

1. Чтобы отсортировать, например значения для табельного номера не меняя остальные строки в таблице надо:

• Выделить диапазон А4:А18 (к сортируемому диапазону добавляется одна ячейка сверху – как шапка столбца)
• На странице ленты Данные нажмите кнопку
• В открывшемся окне установите флажок Сортировать в пределах указанного выделения и нажмите кнопку ОК

КОММЕНТАРИИ К ЯЧЕЙКАМ

1. Для ячейки D4 внесем комментарий «Премия пропорционально окладу». Для этого:

• Сделайте активной ячейку D4,
• Дайте команду Рецензирование, Создать примечание
• В появившемся окне введите текст примечания – Премия пропорционально окладу
• При создании примечания в правом верхнем углу ячейки D3 появилась красная точка, которая свидетельствует о наличии примечания.
• Чтобы скрыть примечание нажмите на ссылку Показать или скрыть примечание
• При наведении указателя мыши а ячейку с красной точкой, примечание появляется как всплывающая подсказка.
• Команда Показать все примечания – скрывает (выводит) тексты всех примечаний

ЗАЩИТА РАБОЧЕГО ЛИСТА

1. Защитим рабочий лист — Зарплата октябрь от изменений. Для этого:

• Дайте команду командой Рецензирование, Защитить лист
• В строке Пароль для отключения защиты введите пароль (например, 12345), нажмите ОК
• Подтвердите пароль – 12345.
• Убедитесь, что лист защищен и невозможно ввести или удалить данные.
• Снимите защиту листа ( Рецензирование, Снять защиту листа ).
• Сохраните созданную вами электронную книгу Ведомость.xlsx

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ:

Выполнить в файле Ведомость.xlsx на рабочем листе Ведомость ноябрь:

1. Выполните сортировку по табельному номеру в порядке убывания
2. Сделать примечание на любые 3 ячейки.
3. Сделать условное форматирование оклада и премии за ноябрь месяц:

• до 2000 р. – желтым цветом заливки, синим цветом шрифта;
• от 2000 до 5000 – зеленым цветом шрифта;
• от 5000 до 6000 – белый цвет шрифта, зеленый цвет заливки;
• от 6000 до 8000 – красный цвет шрифта;
• от 8000 до 10000 – розовый цвет заливки, черный цвет шрифта;
• свыше 10000 – малиновым цветом заливки, белым цветом шрифта.

1. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников за ноябрь месяц.
2. Защитите лист от изменений, установите пароль
3. Проверьте защиту. Убедитесь в неизменяемости данных.
4. Снимите защиту с листа.

Анализ результатов работы и формулировка выводов

В отчете необходимо предоставить: в своей папке файл: Ведомость.xlsx (два рабочих листа)

Как рассчитать премию от оклада в эксель

contact@deynekina.ru
+7-916-571-91-94
DEYNEKINA HR&BA
Как посчитать премию сотруднику по нескольким KPI?

Давайте разберем, как в экселе быстро посчитать премию сотруднику, если премия считается на основе нескольких KPIs
и все они имеют разный вес.

Для решения этой задачи нам нужен средневзвешенный показатель.

Средневзвешенный показатель – это среднее значение, в котором учтена доля каждого из значений, участвующих в расчете среднего значения.

Например, вы хотите посчитать премию по результатам KPIs. Целевая премия сотрудника – 15% от оклада. Сотрудник трудится над 3 целями в месяц. Суммарно всех 3 KPI равны 100%. То есть при выполнении каждого показателя на 100%, сотрудник получает свои 15% от оклада.

Как рассчитать премию, которую заработал сотрудник? С помощью средневзвешенного показателя.

Делаем это с помощью функции эксель СУММПРОИЗВ (SUMPRODUCT). Выделяем оба диапазона (доля в общей премии; факт). Получаем 88,6%. Если мы посчитаем среднее значение, получим 92%. Как видите, показатели отличаются.

Чтобы посчитать фактический процент премии, мы целевой процент премии 15% умножаем на полученный средневзвешенный процент – 88,6%. Рассчитанная премия составит 13,3%.

Как рассчитать премию от оклада в эксель

Цель занятия: Применение относительной и абсолютной адресаций для финансовый расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги.

Задание: Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчёты, форматирование, сортировку и защиту данных. Исходные данные представлены на рис.2.5, результаты работы – на рис. 2.6., 2.7.

Порядок работы:

1. Запустите редактор электронных таблиц MS EXCEL и создайте новую книгу.
2. Создайте таблицу расчета заработной платы по образцу (см. рис. 2.5.). Введите исходные данные – Табельный номер, ФИО и Оклад. % премии = 27%, % Удержания = 13%.

Примечание. Выделите отдельные ячейки для значений % премии (D4) и % Удержания (F4).

Рис. 2.5. Исходные данные для задания
Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула Премия = Оклад х х % Премии, в ячейке D5 наберите формулу =$D$4*C5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчётной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминать, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:
Всего начислено = Оклад + Премия;
При расчете Удержания используется формула:
Удержание = Всего начислено х % Удержания.
Для этого в ячейке F5 наберите формулу = $F$4*E5.
Формула для расчета столбца «К выдаче»:
К выдаче = Всего начислено – Удержания.

1. Рассчитайте итоги по столбцам, а так же максимальный и минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/ категория – Статистические функции).
2. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Результаты работы представлены на рис. 2.6.

Рис. 2.6. Итоговый вид таблицы расчета заработной платы за октябрь

1. Скопируйте содержимое листа «Зарплата октябрь» на новый лист.
2. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы, измените значение премии на 32%. Убедитесь, что программа произвела перерасчет формул.
3. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле:

Доплата = Оклад х % Доплаты. Значение доплаты примите равным 5%.

1. Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата.
2. Проведите условное форматирование значений колонки «К выдаче». Установите формат значений между 7000 и 10000 – зеленым текстом шрифта; меньше 7000 – красным; больше или равно 10000 – синим цветов шрифта (Формат/Условное форматирование).
3. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы – без итогов, выберите меню Данные/Сортировка, сортировать по – Столбец В).
4. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.

Рис. 2.7. Конечный вид зарплаты за ноябрь

1. Защитите лист «Зарплата за ноябрь» от изменений (Сервис/Защита/Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля.

Дополнительные задания:
Задание 1. Сделать примечания к двум-трем ячейкам.
Задание 2. Выполнить условное форматирование оклада и премии за ноябрь месяц:
До 2000 р. – желтым цветом заливки;
От 2000 до 10000 р. – зеленым цветом шрифта;
Свыше 10000 р. — малиновым цветом заливки, белым цветом шрифта.

1. Защитить лист зарплаты за октябрь от изменений. Проверьте защиту
2. Сохраните файл зарплата с произведенными изменениями.