Подготовка и применение электронных таблиц при решении задач оптимизации (на примере функции «Поиск решения» MS

Практическая работа №2 Тема: Подготовка и применение электронных таблиц при решении задач оптимизации (на примере функции «Поиск решения» MS Excel Цель занятия Изучение технологии поиска решения для задач оптимизации  (минимизация, максимизации). Задание12.1.Используя режим подбора параметра (Сервис /Подбор параметра),  определить штатное расписание фирмы. Известно, что в штате фирмы состоят: 6  курьеров, 8 младших менеджеров, 10 менеджеров, 3 заведующих отделом, 1  главный бухгалтер, 1 программист, 1 системный аналитик, 1 генеральный  директор. Общий месячный фонд зарплаты составляет 100 000р. Определить,  какими должны быть оклады сотрудников. Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Ai*x + Bi, где х ­ оклад курьера; Ai и Вi ­ коэффициенты, показывающие:  Ai ­ во сколько раз превышается значение х; Bi ­ на сколько превышается значение  х. Порядок работы 1.  Запустите редактор электронных таблиц Microsoft Excel. 2.  Создайте   таблицу   штатного   расписания   фирмы   Введите исходные данные  в рабочий лист электронной книги. Штатное расписание фирмы Зарплата курьера Коэф  А Коэф  В Зарплата  сотрудника Количество  сотрудников Суммарная  зарплата 1 1,5 3 3 5 1,5 4 5 0 0 0 1000 0 1500 0 2000 6 8 10 3 1 1 1 1 Фонд заработной платы Должность Курьер Младший менеджер Менеджер Зав. отделом Главный бухгалтер Программист Системный аналитик Ген. директор  3.  Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все  расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное  число. 4.  В столбце D введите формулу для расчета заработной платы по каждой  должности.  = B6*$D$3 + С6 Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием. В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. =D6*E6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием. В ячейке  F14 автосуммированием вычислите суммарный фонд заработной платы  фирмы. 5.  Произведите подбор зарплат сотрудников фирмы для суммарной заработной  платы, 100 000 р. Для этого в меню Сервис активизируйте команду Подбор  параметра. В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы; в поле Значение наберите  искомый результат 100 000; в поле Изменяя значение ячейки введите ссылку на  изменяемую ячейку D3,в  которой находится значение зарплаты курьера, и  щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по  заданному условию при фонде зарплаты, равном 100000 р.  6. созданную книгу под именем «Штатное расписание» в своей папке. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните  Задание 2. Используя режим подбора параметра и таблицу расчета штатного  расписания , определить заработные платы сотрудников фирмы для ряда заданных  значений фонда заработной платы.  Порядок работы Запустите редактор электронных таблиц Microsoft Excel и откройте ранее  созданный файл   «Штатное  расписание».    Методом подбора параметров последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100 000, 150 000,  200 000, 250000, Результаты подбора значений зарплат скопируйте в таблицу в  виде специальной вставки. Выделить копируемые данные Правка/Копировать,  установить курсор  в ячейку таблицы ответов Правка/Специальная вставка/  вставить значения. Штатное расписание фирмы 100000 Зарплата 150000 Зарплата 200000 250000 Зарплата Зарплата Должность Курьер Младший курьер Менеджер Зав. отделом Главный бухгалтер Программист Системный аналитик Ген. директор  Задание 3. Задачи оптимизации (поиск решения) Минимизация фонда заработной платы фирмы. Пусть известно, что для нормальной работы фирмы требуется 5...7 курьеров, 8...  10 младших менеджеров,   10  менеджеров,   3   заведующих  отделами,   главный    бухгалтер, Программист, системный аналитик, генеральный директор фирмы. Общий месячный фонд зарплаты должен быть минимален. Необходимо  определить, какими должны быть оклады сотрудников фирмы, при условии, что  оклад курьера не должен меньше 1400 р. В качестве модели решения этой задачи возьмем линейную модель. Тогда условие  задачи  имеет вид N1 * А1* х + N2 * (А2 * х + В2) + ... + N8 * (Ag * х + Bg) = Минимум, Где N ­ количество работников данной специальности; х ­ зарплата курьера; Ai и Bi ­ коэффициенты заработной платы сотрудников фирмы.  Порядок работы 1.    Запустите редактор электронных таблиц Microsoft Excel и откройте ранее  созданный файл   «Штатное  расписание».   Скопируйте  содержимое  листа  «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное  расписание 2».  2.   В меню Сервис/надстройка активизируйте команду Поиск решения  3. В окне Установить целевую ячейку укажите ячейку F14, содержащую модель  суммарный фонд заработной платы. Поскольку необходимо минимизировать общий месячный фонд зарплаты,  активизируйте кнопку равный ­ Минимальному значению. В окне Изменяя ячейки  укажите адреса ячеек, в которых будет отражено количество курьеров и младших  менеджеров, а также зарплата курьера ­ $E$6:$E$7:$D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]). Используя кнопку Добавить в окнах Поиск решения и Добавление ограничений,  опишите все ограничения задачи: количество курьеров изменяется от 5 до 7,  младших менеджеров от 8 до 10, а зарплата курьера > 1400 Ограничения наберите  в виде $D$3 > = 1400  $Е$6 > = 5  $Е$6< = 7  $Е$7 > = 8   $Е$7<=10.  Активизировав кнопку Параметры, введите параметры поиска Рис.12.1 Задание условий для минимизации фонда заработной платы Рис.12.2 Добавление ограничений для минимизации фонда заработной платы Используя кнопку Добавить в окнах Поиск решения и Добавление  ограничений ,опишите все ограничения  задачи: Количество курьеров изменяется  от 5 до 7 ,младших менеджеров  от 8 до 10, а зарплата курьера > 1400  (рис.12.2).Ограничения  наберите в виде  $D$3>=1400                                                                         $Е$6>=5                                                                         $Е$6<=7                                                                         $Е$7>=8                                                                         $Е$7<=10   Запустите   процесс   поиска                    Активизировав кнопку  Параметры ,  введите параметры поиска, как показано на  рис.12.3.   Окончательный вид окна Поиск решения приведен на рис. 12.1   решения   нажатием   кнопки  Выполнить.  В   открывшемся   диалоговом   окне   Результаты   поиска   решения   задайте   опцию Сохранить найдено решение (рис.12.4).   Решение задачи приведено на рис. 12.5. Оно тривиально: чем меньше сотрудников и чем меньше их оклад ,тем меньше месячный фонд заработной платы. Рис.12.3. Задание параметров поиска решения по минимизации фонда заработной платы Рис.12.4. Сохранение найденного при поиске решения Штатное расписание фирмы Зарплата курьера Должность Курьер Младший менеджер Менеджер Зав. отделом Главный бухгалтер Программист Системный аналитик Ген. директор  Коэф  А Коэф  В Зарплата  сотрудника Количество  сотрудников Суммарная  зарплата 1 1,5 3 3 5 1,5 4 5 0              1400,00 0              2100,00 0              4200,00 1000              5200,00 0              7000,00 1500              3600,00 0              5600,00 2000              9000,00 5             7000,00 8           16800,00 10           42000,00 3           15600,00 1             7000,00 1             3600,00 1             5600,00 1             9000,00 Фонд заработной платы 106600,00 Рис.12.5. Минимизация  фонда заработной платы                                                                                                                                         Таблица 12.1                                                                                                                                          Сырьё Сырье 1 Сырье 2 Сырье 3 Нормы расхода сырья А 18 6 5 В 15 4 3 C 12 8 3 Запас сырья 350 200 100 Прибыль 10 15 20 Задание 12.2. Составление плана выгодного производства. Фирма производит несколько видов продукции из одного и того же сырья –  А,В, С. Реализация продукции А дает прибыть 10 р.., В­ 15 р. и С­ 20 р. на единицу  изделия. Продукцию можно производить в любых количествах , поскольку известно, что  сбыт обеспечен ,но ограничены запасы сырья .Необходимо определить ,какой  продукции  и сколько надо произвести, чтобы общая прибыть от реализации была  максимальной. Нормы расхода сырья на производство продукции каждого вида приведены в табл. 12.1. Порядок  работы 1.Запустите редактор  электронных таблиц  Microsoft Excel  и создайте  новую электронную книгу . 2.Создайте расчетную таблицу как на рис. 12.6 введите исходные данный  И формулу в электронную таблицу. Расчетные формулы имеют такой вид: Расходы сырья 1= (количество сырья 1) *(норма расхода сырья  А)+ (количество сырья 1)*(норма расхода сырья В)+(количество сырья 1)*(норма расхода сырья С) Значит , в ячейку F5 нужно ввести формулу = В5*$B$9+ C5* $C$9 + D5 *$D$9. Обратите внимание ,что значения количества сырья каждого вида пока не  известны и будут подобраны в процессе решения задания(ячейки В9:D9 пока  пустые) (Общая прибыль  по А)=(прибыль на ед.изделий А)*(количество А), Следовательно в ячейку В10 следует ввести формулу =В8*В9 Итоговая общая прибыль =(общая прибыль по А) * (общая прибыль по В) +  (Общая прибыль по С), Значит в ячейку Е10 следует ввести формулу =СУММ(В10:В10). Рис.12.6. Исходные данные для задания 12.2 3.В меня сервис активизируйте команду Поиск решения и введите параметры писка, как указано  на рис.12.7. В качестве целевой ячейки укажите ячейку «Итоговая общая прибыть» (Е10) , в качестве   изменяемых ячеек – ячейки количества сырья – (В9:D9). Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас  сырья: Расход сырья 1 < = 350; расход сырья 2 < = 200; расходы сырья 3 < = 100 , а также  положительные значения количества сырья А, В, С > = 0. Установите параметры поиска решения  (рис.12.8).Для этого кнопкой Параметры  откройте диалоговое окно Параметры поиска Рис.12.7. Задание условий и ограничений для поиска решений  Рис.12.8. Задание параметров поиска решения  Рис.12.9.Найденное решение максимизации прибыли при заданных ограничениях решение , установите параметры по образцу ,задайте линейную  модель  расчета (Линейность модели). 4. Кнопкой Выполнить  запустите Поиск решения .Если вы сделали все  верно ,то решение будет как на рис.12.9. 5.Сохраните созданный документ под именем «План производства» Вывод: Из решения видно ,что оптимальный план  выпуска предусматривает  изготовление 5,56  кг продукции В и 22,22кг продукции А производитель не стоит .Полученная прибыль при этом  состоит 527,78р. Дополнительное задания Используя файл «План производства» (см. задание 12.2),определить план   выгодного производства , т.е. какой продукции и сколько не обходимо  произвести , чтобы общая прибыть от реализации была максимальной. Выберите нормы расхода сырья  на производство продукции каждого вида и  ограничения по запасам сырья из таблицы соответствующего варианта(  5  вариантов ): Вариант 1 Вариант 2 Вариант 3 Вариант 4 Вариант 5