Расчет заработной платы с использованием вложенных функций в табличном процессоре excel

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

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

Порядок действий:

1. Создайте рабочую книгу из 3 листов.

2. Сохраните ее под именемЗарплата.

3. Переименуйте рабочие листы в названия месяцев.

4. Склейте все рабочие листы, выбрав командуВыделить все листы в контекстном меню любого рабочего листа (щелчок правой кнопкой мыши на ярлычке листа).

Все, что вы будете писать на первом листе, как «под копирку» появится на всех последующих листах.

5. В ячейкеА1 напишите полужирным шрифтом:Расчетно-платежная ведомость.

6. В ячейке A3 написать полужирным шрифтом: Ставки подоходного налога.

7. Заголовок в ячейке A3 сделайте по центру колонок А:С

8. В ячейки А4:С7 введите таблицу ставок подоходного налога (рисунок).

9. Оформите внутренние границы диапазона ячеекА4:С7 обычной, а верхнюю границу ячеекА4:С4 двойной линией.

10. Внешние границы диапазонаАЗ:С7оформите двойной линией.

11. В ячейке ЕЗ напишите полужирным шрифтомНеоблагаемый минимум.

12. В ячейкуЕ4 введите значение необлагаемого минимума, равное400.

13. В ячейкиА10:I10 введите заголовки полужирным шрифтом:

14. В ячейкахAll: 116 введите данные для будущей ведомости (№п/п, Фамилия, Начислено, количество минимумов). № п/п введите, пользуясь автозаполнением, значение Начислено задайте таким образом, чтобы при расчете подоходного налога использовались все ставки подоходною налога (рис.).

15. Оформите полученную таблицу границами.

16. Задайте формулы для расчета зарплаты (по пунктам ниже).

Порядок действий:

17. Допустим, что вПФ (пенсионный фонд) отчисляется 1% от начисленной сотруднику суммы. Задайте для этого формулу. Округлите результат до целого числа рублей.

18. Вычислите НОБ (налогооблагаемую базу) за январь. Налогооблагаемая база за январь равна:

Начислено - Отчисление в ПФ - Количество минимумов * Необлагаемый минимум

19. ВычислитеПН нарастающим итогом (∑ПН) На основании ставок - подоходного налога реализуем следующий алгоритм расчета:

Если НОБ<=30000, ∑ ПН = НОБ*12%

Если 30000<НОБ<=60000, то ∑ ПН = 3600+(НОБ-30000)*15%

Если 60000<НОБ<=150000, то ∑ ПН = 8100+(НОБ-60000)*20%

Если НОБ>150000, то ∑ ПН = 26100+(НОБ-150000)*25%

19.1. Выделите ячейку G11.

19.2. Вызовите мастер функций .

19.3. Выберите функциюЕСЛИ() из категорииЛогические.

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

В1-ой строке запишите формулу:F11<=$A$4, это проверка условия, что значениеНОБ не превышает30000 (значение30000 находится в ячейкеА4).

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

Во2-ой строке запишите формулу:F11 *$С$4

∑ ПН=НОБ*12%. 12% - это ячейка С4. Адрес ее тоже абсолютный.

В 3-ей строке: проверка условия, что значение НОБ не превышает 60000. Вновь задаем функцию ЕСЛИ(), выбрав имя функции ЕСЛИ() в поле последней использованной функции (левее строки формул). Это будет уже вложенная функция. Попробуйте задать выражение и формулы для следующей вложенной функции ЕСЛИ() самостоятельно.

19.5 В 1-ой строке диалогового окна вложенной функции ЕСЛИ() необходимо проверить, что значение НОБ не превышает 60000 (значение 60000 находится в ячейке А5). Необходимо, чтобы в дальнейшем этот адрес оставался без изменения при копировании формулы, т.е. был абсолютным.

2-строка: ∑ ПН=3600+(НОБ-30000)*15%. Адреса В5, А4 и С5 - абсолютные.

3-строка: проверка условия, что значение НОБ не превышает 150000. Задайте вновь функцию ЕСЛИ() в начале третьей строки.

19.5. 1 строка: проверьте, что значение НОБ не превышает 150000 (значение 150000 находится в ячейке А6). Адрес А6 абсолютный.

2 строка: ∑ ПН=8100+(НОБ-60000)*20%. Адреса В6, А5 и С6 – абсолютные.

3 строка: ∑ПН=26100+(НОБ-150000)*25%. Адреса В7 А6 и С7 – абсолютные.