В столбец оклад ввести формулу увеличения февральского оклада относительно января на 50 эксель

Обновлено: 04.07.2024

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

Для применения данной методики в наш рабочий лист нужно внести некоторые изменения. В ячейку G3 введите сумму прежнего оклада начальника отдела реализации (1400), а в ячейку G4 - коэффициент, на который увеличиваются все оклады. Будущий оклад начальника отдела реализации (то есть оклад после повышения) будет вычисляться в ячейке G5 по формуле

Как вы понимаете, здесь прежний оклад умножается на индекс повышения и округляется до рублей.

В столбец Н занесите все коэффициенты, используемые при перерасчете окладов, а в диапазон Е2:Е11 - формулы, по которым рассчитывается размер будущего оклада (на основании алгоритма, представленного на рис. 5.7). Как теперь должна выглядеть таблица с формулами, показано на рис. 5.8.


Рис. 5.8. Расчет окладов с применением коэффициента (с формулами)

Если в ячейку G4 ввести значение коэффициента, равное нулю, то в диапазоне Е2:Е11 отобразятся прежние оклады.

Если на предприятии еще раз будет принято решение о повышении окладов сотрудников, то достаточно внести в ячейку G4 новое значение коэффициента увеличения - и новые ставки в диапазоне Е2:Е11 будут рассчитаны автоматически.

Допустим, что руководство за существенный вклад в развитие предприятия решило увеличить на 10% оклад сотрудникам отдела снабжения. В этом случае при повышении коэффициента, который используется для вычисления оклада начальника данного отдела, с 0,65 до 0,75 автоматически возрастет на 10% зарплата у работников отдела. Взяв данную систему за основу, можно разработать ряд промежуточных или поправочных коэффициентов, посредством которых нетрудно будет регулировать размер заработной платы.


Рис. 5.9. Расчет окладов с применением коэффициента (с числовым примером)

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

В ней увеличенный размер оклада умножается на коэффициент, привязанный к окладу начальника отдела реализации (в данном случае на 1, поскольку речь идет о самом начальнике отдела реализации).

Ячейка Е4 является управляющей для ячеек Е2 и Е9, где производится расчет сумм окладов начальников других отделов, а также для ячеек области Е5:Е8, в которых рассчитываются оклады рядовых сотрудников отдела реализации. Ячейки Е2 и Е9, в свою очередь, являются управляющими для ячеек, в которых производится расчет окладов для рядовых сотрудников отделов контроля и снабжения. В частности, ячейка Е9 управляет расчетами в ячейках ЕЮ и Е11.

В ячейке Е2 находится формула

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

Обратите внимание, что формула содержит абсолютную ссылку на ячейку Е4.

Это оправданно по следующим причинам:

  • При копировании формулы в другие ячейки (в нашем случае - в ячейку Е9), где производится расчет окладов начальников отделов, ссылка на ячейку Е4 не поменяется. А вот ссылка на ячейку с коэффициентом изменится в зависимости от того, в какую ячейку столбца Е будет скопирована формула (если это будет ячейка Е9, то вместо ссылки на ячейку Н2 появится ссылка на ячейку Н9).
  • Если не ввести абсолютную ссылку на ячейку, то в случае изменения местоположения этой ячейки после сортировки списка, мы получим неправильный результат. Если после сортировки адрес ячейки Е4 изменится, то изменятся и ссылки не нес в формулах с абсолютной ссылкой. (О сортировке мы поговорим ниже.)

В ячейке ЕЗ располагается формула для расчета оклада сотрудника из отдела контроля:

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

1. Создайте таблицу расчета заработной платы по образцу Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.


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

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

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

Формула для расчета «Всего начислено» = Оклад + Премия.

При расчете Удержания используется формула = Всего начислено * % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * Е5.

Формула для расчета столбца «К выдаче» = Всего начислено – Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Формулы/Вставить функцию/категория — Статистические функции).

4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и набе­рите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (пр.клавиша мыши по листу/Переместить/Скопировать…или зажмите клавишу CTRL и перетащите лист правее). Не забудьте для копирования поставить галочку в окошке Создавать копию.

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %.

Убедитесь, что программа произвела пересчет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле = Оклад х % Доплаты. Значение доплаты примите равным 5 %.

8. Измените формулу для расчета значений колонки «Всего начислено» = Оклад + Премия + Доплата.

9. Поставьте к ячейке D3 комментарии «Премия пропорцио­нальна окладу» (Рецензирование/Создать примечание), при этом в правом верх­нем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рисунке


10. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

11. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников зa ноябрь месяц.

Задание 2.

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

1. Откройте созданный в Занятии 1 файл «Зарплата».

2. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книга. Не забудьте для копирования поставить галочку в окошке Создавать копию.

3. Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.


4. Измените значение Премии на 46%, Доплаты — на 8 %. Убедитесь, что программа произвела пересчет формул.

5. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите, форматирование диаграммы. Конечный вид гистограммы приведен на рисунке.


6. Перед расчетом итоговых данных за квартал проведите сорти­ровку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь—декабрь.

7. Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Не забудьте для ко­пирования поставить галочку в окошке Создавать копию.

8. Присвойте скопированному листу название «Итоги за квар­тал». Измените название таблицы на «Ведомость начисления зара­ботной платы за 4 квартал».

9. Отредактируйте лист «Итоги за квартал». Для этого удалите в основной таблицы колонки Оклада и Премии, а также строку 4 с численными значениями % Премии и % Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку.




10. Вставьте новый столбец «Подразделение» (Главная/Ячейки/Вставить столбец на лист) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу


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

В ячейке D5 для расчета квартальных начислений «Всего начис­лено» формула имеет вид:

= 'Зарплата декабрь'!Р5 + 'Зарплата ноябрь'!Р5 + 'Зарплата октябрь'!Е5.

Аналогично произведите квартальный расчет «Удержания» и «К выдаче».

Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D, Е и F. Ваша электронная таблица примет вид, как на рисунке.


12. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений — по фамилиям.

13. Подведите промежуточные итоги по подразделениям, ис­пользуя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Промежуточные итоги. Задайте параметры подсчета промежуточных итогов:

при каждом изменении в — Подразделение;

операция — Сумма;

добавить итоги по: Всего начислено, Удержания, К выдаче.

Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».


Примерный вид итоговой таблицы представлен на рисунке.


14. Изучите полученную структуру и формулы подведения про­межуточных итогов, устанавливая курсор на разные ячейки табли­цы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

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

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

Исходные данные представлены на рис. 10.1.

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

1. Откройте редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу.

2. Создайте на листе 1 таблицу расчета заработной платы по образцу (см, рис. 10.1). Выделите отдельные ячейки для значений % Пре­мии (D4) и %Удержания(F4). Введите исходные данные — Табель­ный номер, ФИО и Оклад; %Премии =27%, %Удержания = 13%,


Рис. 10.1 Исходные данные для задания 10.1

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

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

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

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете Удержания используйте формулу

Удержания = Всего начислено * %Удержаний,

в ячейке F5 наберите формулу = $F$4 * Е5.

Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено - Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, мини­мальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/категория Статистические).

4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и набери­те новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши.

Краткая справка.

Каждая рабочая книга Excel может содер­жать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того чтобы хранить большие последовательные наборы дан­ных на одном листе.

Краткая справка.

Перемещать и копировать листы можно, пepeтаскивая их ярлыки (для копирования

удерживайте нажатой клавишу [Ctrl]).

6.Присвойте скопированному листу название «Зарплата ноябрь».

Исправьте название месяца в название таблицы. Измените значение премии на 32%. Убедитесь, что программа произвела пересчет формул.

7. Между колонками «Премия» и Всего начислено» вставьте новую колонку — «Доплата» (выделите столбец Е «Всего начислено» и выполните команду Вставка/Столбы). рассчитайте значение доплаты по формуле Доплата = Оклад × %Доплаты. Значение доплаты примите равным 5%.

8. Измените формулу для расчета значений колонки «Всего начислено:

Всего начислено = Оклад + Премия + Доплата.

Скопируйте формулу вниз по столбцу.

9. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 — зеленым цветом шрифта, меньше или равно 7000 — красным цветом шрифта, больше или равно 10 000 — синим цветом (Формат/Условное форматирование).

11. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид таблицы расчета заработной платы за ноябрь приведен на рис.

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

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа).

13. Сохраните созданную электронную книгу под именем «Зар­плата» в своей папке.

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

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

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

  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 (два рабочих листа)

Читайте также: