Задание_№8_Excel

Задание №8. Расчет платежной ведомости

Порядок выполнения расчетов.

1. Оформить таблицу и заполнить данными диапазон ячеек A7:AG11 в соответствии с образцом (рис. 1).

2. Произвести расчеты по учету использования дней месяца сотрудниками.

Действия:

• В ячейку АН7 ввести формулу для расчета количества больничных дней (б/л) сотрудника

=ЕСЛИ(СЧЁТЕСЛИ(С7:АG7;"б/л")=0;"";СЧЁТЕСЛИ(С7:АG7;"б/л"))

• Аналогично в ячейку AI7 ввести формулу для расчета количества дней без сохранения содержания (б/с) сотрудника, количества сверхурочно отработанных (с/у) дней, количества рабочих (Я) дней.

3. Присвоить листу имя Турд (Таблица учета рабочих дней).

Рис. 1 Таблица Турд

4. Перейти на чистый лист книги, оформить таблицу табельной ведомости, заполнив данными все, за исключением диапазона D10:G14.

5. Присвоить диапазонам D10:D14, E10:E14, F10:F14, G10:G14 значения соответствующие расчетным результатам таблицы Турд.

6. Присвоить имя листу Тбв (сокращенная аббревиатура слов Табельная ведомость). Образец таблицы представлен на рис. 2

Рис. 2 Таблица Тбв

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

8. Произвести расчеты по следующему алгоритму:

Сумма начислено = КоличествоРабочихДнейСотрудника*ОплатаОдногоРабочегоДня+КоэффициентПоощренияЗаСверхурочные* ОплатаОдногоРабочегоДня*КолвоСверхурочныхДней+ ОплатаОдногоРабочегоДня*ПроцентНачисленияПоБольничномуЛисту*КолвоДнейПоБольничномуЛисту

Сумма удержано = ОплатаОдногоРабочегоДня*КолвоДнейБезСохранения+НормаПодоходногоНалога*(СуммаНачислено+СуммаПремия)+ПроцентОтчисляемыйПенсионныйФонд*(СуммаНачислено+СуммаПремия)

Сумма премия

Если КоличествоРабочихДнейСотрудника+КолвоСверхурочныхДней > КоличествоРабочихДнейМесяца то Сумма премия = (КоличествоРабочихДнейСотрудника*ОплатаОдногоРабочегоДня+ КолвоСверхурочныхДней* ОплатаОдногоРабочегоДня*КоэффициентПоощренияЗаСверхурочные)*ПремиальныйКоэффициент

9. Переименовать стандартное имя листа на имя Плв (сокращенная аббревиатура слов (Платежная ведомость).

Образец таблицы с результатами расчетов представлен в таблице (рис.3).

Рис. 3 Таблица Плв

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

Использование массива формул

Массив формул необходимо применить в самостоятельной работе для расчетов в таблице платежной ведомости. Создавая массив формул, надо помнить следующее:

• Форма и размер диапазона, в которой помещаются результаты, должны повторять форму и размер диапазонов, содержащих исходные данные.

• Массив формул заключается в фигурные скобки, но не надо их вводить, Excel сделает это автоматически.

• Построение массива формул нельзя завершать нажатием клавиши <Enter>. Следует использовать вместо нее комбинацию клавиш — <Shift+Ctrl+Enter>.

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

Порядок выполнения расчетов:

1. Добавьте новый лист. Дайте имя листу Плв2. Скопируйте таблицу Плв на лист Плв2. В скопированной таблице очистить блок ячеек В4:Е8 от содержимого по схеме (….Очистить…Все).

2. Перейти на лист Тбв. Активизировать окно Присвоение имени. В поле Имя ввести СумНач (Сумма начислено), в поле Формула ввести формулу (в соответствии с алгоритмом), в которой в качестве аргументов используются не адреса ячеек а ДИАПАЗОНЫ ЯЧЕЕК. Ниже представлен фрагмент массива формул с именем СумНач.

=Tбb!$C$10:$C$14*Tбb!$D$10:$D$14+…

3. Нажать комбинацию клавиш <Shift+Ctrl+Enter>, чтобы сообщить Excel о том, что вводится не обычная формула, а массив формул.

В ответ Excel заключит формулу в фигурные скобки {}.

4. Использовать полученное имя для расчетов.

Действия:

• Перейти на лист Плв2 и выделить диапазон ячеек В4:В8.

• Вставить имя СумНач.

• нажать комбинацию клавиш <Shift+Ctrl+Enter>.

В итоге, в диапазоне В4:В8 появятся результаты расчета, а в окне ввода формул будет отображаться имя массива формул в виде {=СумНач}.

Аналогично выполнить остальные расчеты. Имена для массивов формул придумать самостоятельно и, в итоге, Вы должны получить вид таблицы Плв2 совпадающую с видом таблицы Плв.

Самостоятельная работа.

1. На листе Тбв добавить данные: Текущая дата, Районный коэффициент ().

2. В таблицу Турд добавить колонки:

Дата поступления на работу

Количество отработанных лет

Членство в профсоюзе.

КоличествоОтработанныхЛет=ТекущаяДата – ДатаПоступленияНаРаботу

3. Добавить две позиции начислений:

• Районный коэффициент

;

• Выслуга,

где: n_4k - количество отработанных лет; Y_i – виды начислений.

4. Добавить позицию удержаний:

• профсоюзный взнос, если работник состоит в профсоюзной организации,

• где m₄ =1%; Y_i – виды начислений (с учетом дополнительных начислений за выслугу и районный коэффициент)

4