Укажите в поле «Значение» - 10000.

Практическая работа № 3

Составление штатного расписания хозрасчетной больницы

Цели работы:

 

¨ научиться использовать электронные таблицы для автома­тизации расчетов;

¨ закрепить приобретенные навыки по заполнению, форма­тированию и печати таблиц.

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

Заведующий хозрасчетной больницей должен составить штатное расписание, т.е. определить, сколько сотрудников, на каких должностях и с каким окладом он должен при­нять на работу. Общий месячный фонд зарплаты составляет $10 000. Построим модель решения этой задачи. Поясним, что является исходными данными. Казалось бы, что ничего не дано, кроме общего фонда заработной платы. Од­нако заведующему больницей известно больше: он знает, что для нормальной работы больницы нужно: 5-7 санитарок; 8-10 медсестер; 10-12 врачей; 1 заведующий аптекой; 3 заведующих отделениями; 1 главный врач; 1 завхоз; 1 заведующий больницей. На некоторых должностях число людей может меняться. Например, зная, что найти санитарок трудно, руководитель может принять решение сократить число санитарок, чтобы увеличить оклад каждой из них. Итак, заведующий принимает для себя следующую модель задачи. За основу берется оклад санитарки, а все остальные вычисляются через него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад яв­ляется линейной функцией от оклада санитарки: А • С + В, где С - оклад санитарки; А и В - коэффициенты, которые для каждой должности определяются решением совета трудового коллектива.

Допустим, совет решил, что

 

Должность	коэффициент А	коэффициент В
санитарка	1	0
медсестра должна получать в 1,5 раза больше санитарки	1,5	0
врач - в 3 раза больше санитарки	3	0
заведующий отделением - на 30 $ больше, чем врач	3	30
заведующий аптекой -в 2 раза больше санитарки	2	0
завхоз - на 40 $ больше медсестры	1,5	40
главный врач - в 4 раза больше санитарки	4	0
заведующий больницей - на 20 $ больше главного врача	4	20

 

Задав количество человек на каждой должности, можно со­ставить уравнение: N1*(А1*C+B1)+N2*(А2*C+B2)+...+N8*(A8*С+В8)=10000. В этом уравнении нам известны А1...А8 и В1...В8, а не из­вестны С и N1...N8. Ясно, что решить такое уравнение известными методами не удается, да и единственно верного решения нет. Остается решать уравнение путем подбора. Взяв первоначально какие-либо приемлемые значения не­известных, подсчитаем сумму. Если эта сумма равна фонду за­работной платы, то вам повезло. Если фонд заработной платы превышен, то можно снизить оклад санитарки либо отказаться от услуг какого-либо работника и т. д. Проделать такую ра­боту вручную трудно. Но вам поможет электронная таблица.

 

Коэф.	Коэф.	Должность	Зарплата	Кол-во	Суммарная		Зарплата
А	В		сотрудника	сотрудников	зарплата		санитарки
		Санитарка					150,00
		Медсестра
		Врач
		Зав. отделением
		Зав. аптекой
		Завхоз
		Главврач
		Зав. больницей
		суммарный месячный фонд

ХОД РАБОТЫ.

ЗАДАНИЕ 1. Заполните таблицу.

1. 1. Отведите для каждой должности одну строку и занесите название должностей в столбец С. В столбцах А и В укажите коэффициенты А и В, соответ­ствующие каждой должности.

1.2. В ячейку НЗ занесите значение заработной платы санитар­ки 150,00 и установите для нее формат 0,00 - два знака после запятой.

ЗАДАНИЕ 2. В столбце D вычислите заработную плату для каждой должности. В постановке задачи было объяснено, что заработная плата вычисляется по формуле А*С+В. В нашей таблице коэффициенты А и В находятся в столбцах А и В, а С - зарплата санитарки указана в ячейке НЗ. Внимание. Столбец D должен заполняться формулами с ис­пользованием абсолютной ссылки на ячейку НЗ. Изменение со­держимого этой ячейки должно приводить к изменению содержимого всего столбца D. Аналогично в работе №2 в задании 3 изменение значения шага во вспомогательной таблице приводи­ло к пересчету в основной. В данной задаче удобно использовать еще один способ абсолютной адресации - именованную ячейку.

Именованная ячейка.

В Excel можно присвоить имя любой ячейке или области.

Первый способ. Что­бы присвоить имя ячейке, ее необходимо выделить и выполнить команду Формулы→Определенные имена→Присвоить имя. На экране появится диалого­вое окно с полем ввода, где необходимо набрать имя и нажать кнопку ОК. Имя должно начинаться с буквы, не содержать пробелов, не совпадать с адресацией. Например, нельзя использовать имя F12, но можно F_12.

Второй способ именования состоит в использовании поля имени, которое располагается слева в строке формул. Для этого необходимо: выделить ячейку или область, перейти в поле имени и щелкнуть левой кнопкой мыши, ввести имя и нажать клавишу <Enter>. При выборе имени из списка имен Excel немедленно перей­дет к этой именованной ячейке или области.

Использовать именованную ячейку удобно в формулах, так как можно заменить адрес ячейки, ничего не говорящий о ее содержании на более выразительное имя. При ссылке в форму­лах на именованную ячейку она будет адресована абсолютно и при копировании формул не возникнут ошибки. Кроме того, рекомендуется именовать все "важные" ячейки, в которых пла­нируется часто изменять данные и которые содержат итоговые результаты.

2.1. Ячейке НЗ присвоите имя С (русская буква).

2.2. В ячейку D3 занесите формулу А3*С+В3. Хотя эта формула равнозначна =АЗ*$H$3+B3, но имя С об­легчает понимание формулы.

2.3. Скопируйте формулу из ячейки D 3 в D 4: D 10. При копировании адрес ячейки с зарплатой санитарки остал­ся постоянным (абсолютным), а адреса A3 и ВЗ перенастраива­ются (они относительные).

ЗАДАНИЕ 3. В столбце Е укажите количество сотрудников на дол­жностях. Данные в ячейках ЕЗ:Е5 могут изменяться в пределах штат­ного расписания, количество сотрудников на других должно­стях неизменно (см. постановку задачи).

ЗАДАНИЕ 4. В столбце F вычислите заработную плату всех со­трудников данной должности. В ячейку F 3 занесите формулу = D 3* E 3 (зарплата * количе­ство сотрудников). Скопируйте формулу из ячейки F3 в F 4: F 10. Установите для данных в столбцах D и F формат 0,00 - два знака после запятой.

ЗАДАНИЕ 5. Определите суммарный месячный фонд заработной платы. Просуммируйте столбец F, используя кнопку ΣАвтосумма.

5.2. Переместите значение суммы в ячейку F 12 и сделайте к ней подпись "Суммарный месячный фонд заработной платы".

5.3. Составьте штатное расписание. Вносите изменения в зарплату санитарки или меняйте коли­чество сотрудников в ячейках ЕЗ:Е5 до тех пор, пока полученный суммарный месячный фонд заработной платы не будет равен за­данному $10000 (в ячейке F12 необходимо получить значение >= 10000).

ЗАДАНИЕ 6. Сохраните файл.

ЗАДАНИЕ 7. Составьте штатное расписание с использованием функции автоматизации расчетов - подбор параметра. Подбор параметра - удобное средство Excel для анализа «что-если». При этом значения для ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равно заданному.

7. 1. Выберите команду Данные → Работа с данными → Анализ «что-если» → Подбор параметра.

7.2. Укажите в поле «Установить в ячейке» адрес целевой ячейки $F$12.

Укажите в поле «Значение» - 10000.

7.4. Укажите в поле «Изменяя ячейку» адрес ячейки с зарплатой санитарки $Н$3 и нажмите на кнопку <ОК>. Начнется процесс подбора параметра. Если нажать на кнопку <ОК>, значе­ния ячеек в таблице будут изменены в соответствии с найденным решением.

Нажмите на кнопку ОК.

ЗАДАНИЕ 8. Составьте несколько вариантов штатного расписа­ния и оформите их в виде таблицы на рабочем листе 2.

 

Варианты штатного расписания
Суммарная месячная зарплата				10000,00
Варианты	Должность	Кол-во сотрудников	Зарплата санитарки
Вариант 1	Санитарка	5
(миним. кол-во	Медсестра	8
сотрудников)	Врач	10
Вариант 2	Санитарка	7
(макс. кол-во	Медсестра	10
сотрудников)	Врач	12
Вариант 3	Санитарка	6
(ср. кол-во	Медсестра	9
сотрудников)	Врач	11
Вариант 4	Санитарка	6
(ср. кол-во	Медсестра	10
сотрудников)	Врач	10

 

8.1. Измените на листе 1 количество сотрудников на должностях санитар­ки, медсестры и врача согласно варианту 1 в таблице. Подберите зарплату санитарки в новых условиях.

8.3. Запишите получившуюся зарплату санитарки на листе 2 в соответствующей ячейке. Проделайте тоже самое с другими вариантами. В Excel имеется автоматическое средство сохранения всех вариан­тов поиска решения при помощи Диспетчера сценариев, который находится Данные→Работа с данными→Анализ «что-если»→Диспетчер сценариев. Им можно воспользоваться, но разбираться в принципе его работы придется самостоятельно.

ЗАДАНИЕ 9. Сохраните файл.

ЗАДАНИЕ 10. Проанализируйте полученные варианты, выберите и оформите один из них на листе 1 согласно рисунку.

 

Вариант штатного расписания
Зав. больницей Петров И.С.
Должность	Зарплата сотрудника	Кол-во сотрудников	Суммарная зарплата
Санитарка	145,93	5	729,63
Медсестра	218,89	8	1751,11
Врач	437,78	10	4377,78
Зав. отделением	467,78	3	1403,33
Зав. аптекой	291,85	1	291,85
Завхоз	258,89	1	258,89
Главврач	583,70	1	583,70
Зав. больницей	603,70	1	603,70
Суммарный денежный фонд			10000,00

 

Внимание! Удалить столбцы А, В, Н нельзя, так как в табли­це на них есть ссылки, но их можно скрыть. Столбцы А, В, Н необходимо выделить, а затем воспользо­ваться Главная→Ячейки→Формат→Видимость→Скрыть столбец или в контекстно-зависимом меню выбрать команду Скрыть.

10.1. Скройте столбцы А, В, Н.

10.2. Дайте заголовок таблице «Штатное расписание хозрас­четной больницы» и подзаголовок «Зав. больницей Петров И.С.»

10.3. Таблицу оформите, используя автоформатирование. Для этого выделите всю таблицу, включая заголовки. Выберите Главная→Стили → Форматировать как таблицу. Выберите удовлетворяющий вас формат.

ЗАДАНИЕ 11. Подготовьте таблицу к печати. Выберите альбомное расположение. Уберите сетку. Укажите в верхнем колонтитуле фамилию, а в нижнем — дату и время.

Предъявите преподавателю свой файл.

---

Дата добавления: 2020-12-12; просмотров: 91; Мы поможем в написании вашей работы!

Поделиться с друзьями:

---

---

Мы поможем в написании ваших работ!