Задача 3.2. Расчет зарплаты бригады

Предметная область: расчет заработанного дохода рабочими бригады. Элементы предметной области: фамилии рабочих (10 фамилий), коэффициенты качества труда (ККТ) (действительные числа в диапазоне от 0,5 до 2), фактическое время работы каждого рабочего (целые числа в диапазоне от 0 до 12); значения ежедневных фондов оплаты труда (2800 грн; 2500 грн; 3000 грн; 3200 грн; 3500 грн); процентная ставка подоходного налога (13%).

Отношения:

• ежедневно заработанный доход рабочего бригады рассчитывается по формуле:

= фонд оплаты труда * К _i / К ;

где К_i = ККТ _{рабочего} * число отработанных им часов;

К =  К_i

^{по всем рабочим}

• подоходный налог взимается с дохода, заработанного за неделю по формуле:

= процентная ставка налога * заработанный доход.

Требования:

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

• при вводе ККТ и фактически отработанного времени разрешить ввод значений, находящихся в заданных пределах (средство Проверка данных);

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

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

Методические указания к решению

1. Выполнить вставку новых рабочих листов (Вставка  Лист).

2. Присвоить листам имена: День1, День2, День3, День4, День5.

3. Выполнить группировку всех листов, чтобы одновременно во всей группе ввести заголовки документов и заголовок таблицы.

• Инструкция по группировке листов

Щелкнуть по ярлычку первого листа группы и, удерживая клавишу Shift, щелкнуть по ярлычку последнего листа группы.

4. В сгруппированные рабочие листы ввести фрагмент табличной модели (строки 1-5) в соответствии с рис. 21, а также формулы в столбец «Заработано».

	A	B	C	D	Е	?
1	Ежедневный учет заработной платы
2	Фонд			K
3
4	№	Фамилия	ККТ	Отработано часов	К	Заработано
5

Рис. 21. Фрагмент таблицы для одновременного ввода на всех листах

5. Разгруппировать листы (навести указатель на выделенный лист и нажать правую кнопку мыши, выбрать в контекстном меню команду Разгруппировать листы).

6. Фамилии рабочих бригады целесообразно ввести только на первом листе, а на остальных «вытащить» с помощью двумерных ссылок. Этот прием позволит минимизировать ошибки, допущенные при вводе: при изменении фамилий на первом листе произойдет обновление значений на остальных листах. Сгруппировав листы День 2, День 3, День 4, День 5, в ячейку В5 ввести формулу:

=День1!B5

и скопировать ее вниз.

7. Объединить в группу листы День 1:День 5 и выполнить числовое и стилевое форматирование их данных.

8. Разгруппировать листы.

9. На листе День 1 ввести фамилии и убедиться в том, что они отобразились на остальных листах.

10. Для ввода значений ККТ и фактического времени работы членов бригады на каждом листе разрешить ввод данных, находящихся в заданных пределах.

• Инструкция по разрешению ввода данных, находящихся в заданных пределах

1. выделить диапазон, который требуется проверять при вводе;

2. исполнить команды Данные  Проверка и активизировать вкладку Параметры;

3. в списке Тип данных выбрать вариант Целое число или Действительное;

4. в списке Значение выбрать требуемое ограничение. Например, чтобы установить нижнюю и верхнюю границы, выбрать значение между;

5. ввести минимальное, максимальное или определенное разрешенное значение;

6. активизировать вкладку Сообщение для ввода и ввести заголовок и сообщение;

7. активизировать вкладку Сообщение об ошибке, выбрать вид сообщения и ввести заголовок.

11. Сохранить активную рабочую книгу под именем Бригада.

12. Создать новую рабочую книгу и переименовать Лист1 в Итоги.

13. На листе Итоги ввести текст и значения в соответствии с рис.22.

	A	B	C	D
1	Учет заработной платы за неделю
2	Фонд
3	ПС налога
4
5	Фамилия	Заработанный доход	Налог	К выдаче
6

Рис. 22. Макет таблицы для расчета заработанного за неделю дохода

14. «Вытащить» фамилии с первого листа книги Бригада, создав формулу с внешней двумерной ссылкой, и скопировать ее вниз.

• Инструкция по созданию внешней двумерной ссылки

1. выбрать зависимую ячейку;

2. ввести знак равенства;

3. исполнить команду меню Окно и выбрать имя рабочей книги-источника;

4. щелкнуть по ярлычку листа, в котором находится влияющая ячейка-источник, и выделить ее;

5. нажатием клавиши F4 убрать абсолютную ссылку;

6. завершить ввод формулы нажатием клавиши Enter.

15. Рассчитать недельный заработанный доход каждого рабочего, создав формулу с внешней трехмерной ссылкой вида:

= СУММ ([Бригада.xls] День1:День5!Е5).

• Инструкция по созданию формулы с внешней трехмерной ссылкой

1. в зависимой ячейке с помощью кнопки Автосуммирование вызвать функцию СУММ или другую стандартную функцию;

2. исполнить команду меню Окно и выбрать имя рабочей книги-источника;

3. сгруппировать листы, данные которых необходимо связать (щелкнуть по ярлычку первого листа группы, удерживая Shift, щелкнуть по ярлычку последнего листа группы);

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

16. Найти сумму дохода, заработанного всеми рабочими бригады.

17. С помощью внешней трехмерной ссылки вычислить фонд оплаты труда за неделю.

18. Сравнить полученные значения (их несовпадение свидетельствует о наличии ошибки).

19. Ввести формулы для расчета налогов и выплаченного заработанного дохода.

20. Выполнить копирование формул для всех рабочих.

21. Выполнить числовое и стилевое форматирование данных.

22. Сделать графическую интерпретацию данных итогового листа на основе круговой диаграммы.