Занятие 8, 9 (4 ч., лабораторное)

    Тема: Составление штатного расписания хозрасчетного пансионата с помощью Excel.

 

Постановка задачи

    Необходимо составить штатное расписание хозрасчетного пансионата, т. е. определить, сколько сотрудников, на каких должностях и с каким окладом следует принять на работу. Общий месячный фонд заработной платы составляет $10000. При этом для нормального функционирования пансионата нужно:

1) 5-7 санитарок;

2) 8-10 медсестер;

3) 10-12 врачей;

4) 1 заведующий аптекой;

5) 3 заведующих отделениями;

6) 1 главный врач;

7) 1 завхоз;

8) 1 заведующий больницей.

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

 

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

 

    A×C+B,

 

где C – оклад санитарки;

A и B – коэффициенты, которые для каждой должности определяются советом трудового коллектива.

    Допустим, совет трудового коллектива решил, что:

медсестра должна получать в 1,5 раза больше санитарки A = 1,5 B = 0;

врач – в 3 раза больше санитарки                                      A = 3 B = 0;

заведующий отделением – на $30 больше, чем врач       A = 3 B = 30;

заведующий аптекой – в 2 раза больше санитарки          A = 2 B = 0;

завхоз – на $40 больше медсестры                                      A = 1,5 B = 40;

главный врач – в 4 раза больше санитарки                       A = 4 B = 0;

заведующий больницей – на $20 больше главного врача A = 4 B = 20.

    Задав количество человек на каждой должности, можно получить следующую модель:

 

    N1×(A1×C+B1) + N2×(A2×C+B2) + … + N8×(A8×C+B8) = 10000,

 

где N1 – количество санитарок;

N2 – количество медсестер и т. д.;

A1…A8 и B1…B8 – коэффициенты для каждой должности.

    В этом уравнении неизвестными являются C и N1…N8. Решить это уравнение можно путем подбора. В Excel это сделать достаточно просто.

 

Ход работы

1. Заполните следующую таблицу на Листе1 новой рабочей книги Excel:

 

	A	B	C	D	E	F	G	H
1	Коэф.	Коэф.	Должность	Зарплата	Кол-во	Суммарн.		Зарплата
2	A	B		сотруд.	сотруд.	зарплата		Санитарки
3	1	0	Санитарка		7
4	1,5	0	Медсестра		10
5	3	0	Врач		12
6	3	30	Зав. отделением		3
7	2	0	Зав. аптекой		1
8	1,5	40	Завхоз		1
9	4	0	Глав. Врач		1
10	4	20	Зав. больницей		1
11
12		Суммарный месячный фонд зарплаты

 

2. В ячейку H3 занесите в качестве первого приближения значение заработной платы санитарки 150 и установите для нее формат 0,00 – два знака после запятой: Фор м ат è Я чейки… è вкладка Число.

3. Ячейке H3 присвойте имя ОкладСанитарки:

3.1. выделите ячейку H3;

3.2. перейдите в поле имени, которое располагается слева в строке формул, и щелкните левой кнопкой мыши (или Вст а вка è И мя è П рисвоить);

3.3. введите имя и нажмите клавишу Enter.

4. В столбце D вычислите зарплату для каждой должности:

4.1. в ячейку D3 занесите формулу = A3*ОкладСанитарки+B3 (использование имени ячейки равнозначно использованию абсолютной ссылки на нее);

4.2. скопируйте формулу из ячейки D3 в ячейки D4:D10;

4.3. установите для данных столбца D формат 0,00 – два знака после запятой.

5. В столбце F вычислите зарплату всех сотрудников определенных должностей:

5.1. в ячейку F3 занесите формулу = D3*E3;

5.2. скопируйте формулу из ячейки F3 в ячейки F4:F10;

5.3. установите для данных столбца F формат 0,00 – два знака после запятой.

6. В ячейку F12, для вычисления суммарного месячного фонда заработной платы, занесите формулу = СУММ(F3:F10).

7. Штатное расписание можно составить вручную, изменяя либо зарплату санитарки, либо количество сотрудников в ячейках E3:E5 до тех пор, пока в ячейке F12 не получим»10000.

8. Штатное расписание можно составить с помощью функции Подбор параметра:

8.1. задайте точное число сотрудников в ячейках E3:E5;

8.2. выберите команду С е рвис è П одбор параметра…;

8.3. укажите в поле Установить в ячейке: адрес целевой ячейки $F$12;

8.4. укажите в поле Значение: 10000;

8.5. укажите в поле Изменяя значение ячейки: адрес ячейки с зарплатой санитарки $H$3 и нажмите на кнопку OK.

9. Составьте несколько вариантов штатного расписания с использованием функции Подбор параметра:

9.1. измените количество сотрудников на должностях санитарки, медсестры и врача;

9.2. подберите зарплату санитарки в новых условиях;

9.3 составьте таблицу нескольких (3-4) вариантов штатного расписания на Листе2 текущей рабочей книги Excel:

 

Варианты штатного расписания

 

№ варианта	Должность	Кол-во сотрудников	Зарплата санитарки
…	Санитарка Медсестра Врач	… … …	…

 

10. Сохраните текущую рабочую книгу под именем lesson 5. xls: Ф айл è Сохранить к ак…

11. Проанализируйте полученные варианты штатного расписания, выберите и оформите в виде таблицы в Word один из них и сохраните документ под именем lesson 5. doc:

 

Штатное расписание хозрасчетного пансионата

 

Должность	Кол-во сотрудников	Зарплата сотрудника	Суммарная зарплата
…	…	…	…
Суммарный месячный фонд зарплаты			…

 

12. В файле lesson 5. xls на Листе1 постройте Круговую диаграмму, показывающую распределение месячного фонда заработной платы по должностям для выбранного в пункте 11 варианта штатного расписания: Вст а вка è Диа г рамма…

13. Скопируйте полученную в пункте 12 диаграмму из файла lesson 5. xls и вставьте ее в файл lesson 5. doc с помощью операций копирования и вставки.

    Для программ, работающих под управлением ОС Windows, доступны операции копирования (copy) и вставки (paste), в которых применяется буфер обмена (clipboard) Windows.

    Самый простой метод переноса информации (объекта) – копирование ее из одной программы и вставка в другую. При этом объекты не являются связанными. Объекты могут быть встроенными, а могут и не быть встроенными.

    При переносе информации всегда есть исходная программа и программа-адресат.

    Когда файл программы-адресата воспринимает и хранит копию объекта, говорят, что объект встроен в этот файл.

    Если дважды щелкнуть на встроенном объекте, хранящемся в файле-адресате, запускается программа, в которой создан этот объект, однако при этом отображается копия встроенного объекта, а не сам оригинал. Любые изменения встроенного объекта происходят только в той копии объекта, которая хранится в файле-адресате. Например, объект, представляющий собой диаграмму Excel, можно встроить в документ Word.

    Если объект хранится в файле-адресате в виде рисунка, он не является встроенным. Например, если диаграмму Excel вставить в документ Word в виде рисунка, она перестает быть объектом Excel, и двойной щелчок на ней не приведет к запуску программы Excel.

    Если объект продолжает храниться в файле исходной программы, а файл программы-адресата располагает лишь сведениями о его месторасположении, говорят, что объект связан с этим файлом.

    Если дважды щелкнуть на связанном объекте в файле-адресате, открывается исходный файл, в котором создан этот объект, а также используемая программа. Любые изменения в связанном объекте отражаются и в исходном файле, и в файле адресате. Точно также, любые изменения в исходном файле, приводят к изменению объекта в том файле, с которым он связан. Например, если объект, представляющий диаграмму Excel, связан с документом Word, любые изменения в файле данных Excel приведут к обновлению диаграммы как в файле Excel, так и в файле Word.

    При использовании операций копирования (copy) и вставки (paste) мы встраиваем диаграмму Excel в файл Word.

14. С помощью команды Word П равка è Сп е циальная вставка…:

14.1. вставьте полученную в пункте 12 диаграмму из файла lesson 5. xls в файл lesson 5. doc в виде рисунка;

14.2. свяжите полученную в пункте 12 диаграмму из файла lesson 5. xls с файлом lesson 5. doc.

 

Варианты заданий

1. Составьте штатное расписание кафе.

2. Составьте штатное расписание туристического агенства.