Ввод в таблицу вложенных функций

19. Заполните таблицу 3 «Ведомость начислений» расчетными данными, используя кнопку «Мастер функций» и функции «ВПР»; «ЕСЛИ»; «СЕГОДНЯ».

Технология ввода вложенной функции:

Вычислите «Оклад» в столбце «В36»» таблицы «Ведомость начислений». Для этого выполним следующие действия:

· Установите курсор в ячейку «В36».

· Введите знак равенство «=».

· С помощью функции «ВПР» и исходных данных таблицы «Лицевой счет» - разряд» выполните следующие действия (Рис.50).

Рис. 50. Просмотр разряда в таблице «Лицевой счет» с помощью функции ВПР

· Щелкните «ОК».

· Функция «ВПР» просмотрела и вывела в таблицу «Ведомость начислений» «13» разряд табельного номера «1001».

· Далее с помощью таблицы «Справочник разрядов и окладов» просмотрим все оклады для соответствующих разрядов. Для этого скопируйте функцию «ВПР» в начало формулы (Рис.51).

Рис. 51

· Выделите диапазон ячеек «$B$14: C$32$» таблицы «Справочник разрядов и окладов» с указанием 2 столбца «Оклада» (Рис.52).

· Щелкните «ОК».

Рис. 52 Просмотр окладов функцией «ВПР»

 

· Функцией «ВПР» по условию задачи мы просмотрели «разряды» и соответствующие «оклады» (Рис.53).

 

 

Рис. 53 «Технология просмотра с помощью функции «ВПР» разряда и оклада

 

· Далее по формуле оклад умножим «*» на фактически отработанное время и разделим «/» на «Количество рабочих дней в месяце» ( Рис. 54).

Разряд

Кол-во раб дней

Оклад

 

Фактически отработанное время

 

Рис.54

 

= ВПР(ВПР(A36;$A$3:$I$9;3;ложь);$B$14:$C$32;2; ложь)*

ВПР(A36;$A$3:$I$9;5;ложь) /$M$3

 

20. Скопируйте формулу начисления ЗП по окладу в диапазон «В37:В42».

21. В ячейку «С36» введите формулу расчета премии (стр.47). Размер премии зависит от выслуги лет, определяемой как разность между текущей датой и датой поступления на работу.

Для расчета премии выполним следующие действия:

· Введем в таблицу 1 «Лицевой счет» дополнительный столбец «К3» -с наименованием столбца «Дата поступления на работу».

· Заполним столбец «К3» на основании данных приведенных в таблице 1 «Лицевой счет» (Рис. 55).

Рис.55 «Дата поступления на работу»

 

22.Технология ввода формулы для расчета премии представлена на (Рис. 56):

 

=ЕСЛИ((СЕГОДНЯ()-ВПР(A36;$A$3:$К$9;11;ложь))/365<5;В36*$N$4/100;

ЕСЛИ((СЕГОДНЯ()-ВПР(A36;$A$3:$К$9;11;ложь))/365<10;В36*$O$4/100; В36*$P$4/100))

Рис. 56Технология расчета премии

23. Скопируйте формулу в ячейки «С37:С42».

24. В ячейку «D36» введите формулу расчета начисленной ЗП «Всего»: = B36 + C36

25. Скопируйте формулу в ячейки «D37:D42».

26. Введем на основе формул (стр. 47-48) расчеты для таблицы 4 «Ведомость удержаний» применив соответствующие функции «ВПР»; «ЕСЛИ»; «ЕНД» и таблицу «Справочник работников» рабочего листа «Справочник работающих в организации». Для этого:

27.Установите курсор в клетку «G36» с помощью «Мастера функции» введите функцию «ВПР» применив формулу подоходного налога (Рис.57):

=(ВПР(F36;$A$3:$К$9;8;ложь)-$L$3*ВПР(F36;Справочник работающих в организации’!$A$3:$D$9;2;ложь))*0,12

Рис. 57 «Технология расчета подоходного налога»

28.Скопируйте формулу в ячейки «G37:G42».

29.В ячейку «H36» введите формулу расчета пенсионного налога (Рис. 58):

=ВПР(F36;$A$3:$K$9;8;ложь)*0,01

Рис. 58 «Технология расчета пенсионного налога»

 

30.Скопируйте формулу в ячейки «H37:H42».

31.В ячейку «I36» введите формулу расчета удержания по исполнительным листам (Рис.59):

 

=ЕСЛИ(ЕНД(ВПР(F36;$E$13:$F$15;2;ложь));0;(ВПР(F36;

$A$3:$К$9;8;ложь)-G36)*ВПР(F36;$E$13:$F$15;2;ложь)/100)

Рис.59 «Технология расчета по исполнительным листам»

32.Скопируйте формулу в ячейки «I37:I42».

33. В ячейку «J36» «Всего» введите формулу расчета общей суммы удержания: =G36+H36+I36

34. Скопируйте формулу в ячейки «J37:J42».

Стилевое оформление таблиц

35. Самостоятельно установите любой цвет фона и выберите узор для дополнительных таблиц 3,4,6.

Если выбранное оформление не понравилось, отмените его. Для этого нажмите кнопку пиктографического меню «Цвет заливки» и выберите «нет заливки».

36. Выполните условное форматирование для диапазона ячеек «J3:J9» таблицы 1 «Лицевой счет». Если значение суммы З/П к выдаче меньше прожиточного минимума, то необходимо значение вывести «Светло-красная заливка и темно-красный текст», иначе выводиться «Зеленая заливка и темно-зеленный текст».

Для этого необходимо:

* выделить диапазон «J3:J9»;

* выбрать вкладку «Главная/Стили/Условное форматирование»;

* в диалоговом окне команды Условное форматирование для формирования условия форматирования выбрать в группе «Правила выделения ячеек» – операцию «меньше», и ввести ссылку на ячейку «=$Q$4»; а в списке Цвет выбрать «Светло-красная заливка и темно-красный текст»;

* щелкнуть по кнопке «OK».

· для формирования следующего условия в диалоговом окне команды «Условное форматирование» выбрать в группе «Правила выделения ячеек» – операцию «больше», и ввести ссылку на ячейку «=$Q$4»; а в списке Цвет выбрать «Зеленая заливка и темно-зеленный текст».

· щелкнуть по кнопке «OK».

37. Для диапазона ячеек «E3:E9» примените «Пользовательский формат»: если работник проработал целый месяц вывести результат как «Пользовательский формат», если проработал меньше месяца вывести результат красным цветом.

 

Интеграция приложений