Методическая разработка элективного курса (стр. 15 )

- активизируйте команду Подбор параметра из меню Сервис;

Сохраните таблицу в личном каталоге под именем hospital. xls.

Анализ задачи показывает, что с помощью Excel можно ре­шать линейные уравнения. Конечно, такое уравнение может ре­шить любой школьник. Однако благодаря этому простому примеру стало очевидно, что поиск значения параметра формулы, удовле­творяющего ее конкретному значению, - это не что иное, как чис­ленное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.

Задачи оптимизации

В задаче 118 мы рассмотрели задачу поиска значения пара­метра, позволяющего достичь конкретной цели.

Задачи могут быть и более сложными. Например, поиск не­скольких параметров, обеспечивающих некоторый наперед задан­ный результат.

Кроме того, иногда интересует не конкретный результат, а минимально или максимально возможный. Например, как миними­зировать затраты на содержание персонала или максимизировать прибыли от реализации продукции?

Такие задачи в Excel также решаются с помощью Поиска решения.

Если математическая модель исследуемого процесса и огра­ничения на значения ее параметров линейны, то задача достиже­ния цели является задачей линейного программирования.

Познакомимся с решением этих задач на следующем примере.

2. Составление штатного расписания.

Усложним рассмотренную задачу. Пусть известно, что для нормальной работы больницы необходимо 5-7 санитарок, 8—10 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующий хозяйством и заведующий боль­ницей. Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников больницы, при условии что оклад санитарки не должен быть мень­ше прожиточного минимума - 80 у. е.

В качестве модели решения этой задачи возьмем, как и раньше, линейную. Запишем ее так:

N1 • А1 • С + N2 • (А2 • С + В2)+ … N8 • (А8 • С +В8)=Минимум

В этом уравнении нам не известно число санитарок (N1), медсестер (N2), врачей (N3) и оклад санитарки (С).

Используя Поиск решения, найдем их. Откройте созданный в предыдущей задаче файл hospital. xls. В меню Сервис активизируйте команду Поиск решения. В окне Установить целевую ячейку укажите ячейку F10, содержащую модель.

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

Используя кнопку Добавить, опишите ограничения задачи.

$Е$2<=7

$Е$2>=5

$Е$3<=10

$Е$3>=8

$G$2>=80

Щелкните кнопкой ОК, затем - Выполнить.

Решение приведено ниже:

	А	B	C	D	E	F	G
1	Должность	Коэф. А	Коэф. В	Зарплата сотрудни-ка	Количест- во сотрудни-ков	Суммар-ная зарплата	Зарплата санитарки
2	Санитарка	1	0	150	6	900	150
3	Медсестра	1,5	0	225	8	1800
4	Врач	3	0	450	10	4500
5	Зав. отделением	3	30	480	3	1440
6	Зав. аптекой	2	0	300	1	300
7	Завхоз	1,5	40	265	1	265
8	Главврач	4	0	600	1	600
9	Зав. больницей	4	20	620	1	620
10	Итого					10425

Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.

Эта задача специально приведена здесь для того, чтобы учащимся было легче освоить новый материал. Для закрепления прой­денного материала решим следующую задачу.

3. План выгодного производства.

Предположим, что мы решили производить несколько видов конфет. Назовем их условно «А», «В», «С». Известно, что реализа­ция 10 килограммов конфет «А» дает прибыль 9 у. е., «В» - 10 у. е., «С» - 16 у. е.

Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограммов необходимо произвести, чтобы общая прибыль от реализации была максимальной.

Нормы расхода сырья на производство 10 кг конфет каждого вида приведены ниже.

Сырье	Нормы расхода сырья	Запас сырья
А	В	С
Какао	18	15	12	360
Сахар	6	4	8	192
Наполнитель	5	3	3	180
Прибыль	9	10	16

Технология работы:

- Запустите табличный процессор Excel.

- Заполните таблицу в соответствии с образцом:

	А	В	С
1	Наименование	Количество	Прибыль
2	А	0	=9*В3
3	В	0	=10*В4
4	С	0	=16*В5
5	Стоимость продукции		=СУММ(С3:С5)
6		Расход сырья
7	Какао	Сахар	Наполнитель
8	=18*В3+15*В4+12*В5	=6*В3+4*В4+8*В5	=5*В3+3*В4+3В5

В меню Сервис активизируйте команду Поиск решения и опишите его ограничения, как указано ниже:

$А$10<=360

$В$10<=192

$В$3>=0

$В$4>=0

$В$5>=0

$С$10<=180.

Не забудьте указать, что изменяются ячейки $В$3:$В$5 и в Параметрах на Линейность модели. Запустите Поиск решения. Если вы сделали все верно, то решение будет таким, как указано ниже:

	А	В	С
1	Наименование	Количество	Прибыль
2	А	0	0
3	В	8	80
4	С	30	320
5	Стоимость продукции		400
6		Расход сырья
7	Какао	Сахар	Наполнитель
8	360	192	84

Вывод: из решения видно, что оптимальный план выпуска преду­сматривает изготовление 80 кг конфет «В» и 20 кг конфет «С». Конфеты «А» производить не стоит. Полученная вами прибыль составит 400 у. е.