Задача 1. Для приготовления смеси необходимо использовать набор из трех компонентов (A, B, C), ресурсы которых ограничены соответственно 20, 25 и 10 кг в расчете на одну загрузку. При этом смесь должна содержать необходимое количество химических добавок: D (не менее 20 гр.), E (не менее 2000 гр.), F (не менее 100 гр.). Себестоимость смеси должна оказаться наиболее выгодной, т.е. минимальной.
Следующая таблица характеризует содержание химических добавок D, E, F в 1 кг. каждого компонента и себестоимость компонентов A, B, C соответственно.
| Виды компонентов | Содержание химических добавок, гр. | Себестоимость 1 кг компонента, тыс.руб. | ||
| D | E | F | ||
| A | 0,5 | |||
| B | 0,2 | |||
| C | 1,0 |
Для составления математической модели обозначим через х1, х2, х3 соответственно количество килограммов компонента A, компонента B и компонента C, которые составляют искомую химическую смесь. Тогда на основании условий задачи данные переменные должны удовлетворять следующим ограничительным условиям:
Введем ограничения по ресурсам согласно условию:
Кроме того, переменные должны быть неотрицательными, т.е.
Очевидно, существует множество решений системы неравенств. Необходимо выбрать тот состав смеси, который обладает наименьшей себестоимостью, т.е. целевая функция выглядит так: 
Решите задачу с помощью надстройки «Поиск решения» (инструмент для поиска решения уравнений и задач оптимизации). При отсутствии надстройки «Поиск решения», ее необходимо подключить при помощи соответствующего пункта меню.
Порядок работы.
Подготовьте в Microsoft Excel на первом листе таблицу исходных данных, переменных и ограничений.
Внесите в ячейку С12 формулу для расчета целевой функции: =A10*A3+B10*B3+C10*C3. В ячейке С12 должен отразиться «0».
|
|
В ячейке Е11 находится формула: =A6*A3+B6*B3+C6*C3; в ячейке Е12 находится формула: =A7*A3+B7*B3+C7*C3; в ячейке Е13 находится формула: =A8*A3+B8*B3+C8*C3.
Запустите надстройку «Поиск решения» (находится в пункте меню «Данные»).
Установите целевую ячейку, равной минимальному значению. Укажите диапазон изменяемых переменных (ячеек) и добавьте ограничения задачи.
Кнопкой «Выполнить» запустите «Поиск решения», затем нажимаем «Ок».
Если вы сделали все верно, то получите результат решения как на рисунке
Задача 2.Составление плана выгодного (оптимального) производства.
Фирма производит несколько видов продукции (А, В, С) из одного и того же сырья. Реализация продукции А дает прибыль 10 руб., В — 15 руб. и С — 20 руб. на единицу изделия.
Продукцию можно производить в любых количествах, поскольку известно, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.
Нормы расхода сырья на производство продукции каждого вида приведены в таблице.
| Сырье | Нормы расхода сырья | Запас сырья | ||
| А | В | С | ||
| Хлопковое волокно 1 сорта | ||||
| Хлопковое волокно 2 сорта | ||||
| Хлопковое волокно 3 сорта | ||||
| Прибыль на единицу изделия, руб. |
Порядок работы.
1. Создайте на втором листе Microsoft Excel расчетную таблицу с исходными данными.
2. Расчетные формулы расхода сырья имеют такой вид:
|
|
В ячейке F5 находится формула: =B5*$B$9 + C5*$C$9 + D5* $D$9; в ячейке F6 находится формула: =B6*$B$9 + C6*$C$9 + D6* $D$9; в ячейке F7 находится формула: =B7*$B$9 + C7*$C$9 + D7* $D$9.
Обратите внимание, что значения количества продукции каждого вида пока не известны и будут подобраны в процессе решения задания (диапазон ячеек B9:D9 пока пустой).
Общая прибыль по А (формула в ячейке В10): =B8*B9
Общая прибыль по B (формула в ячейке C10): =C8*C9
Общая прибыль по C (формула в ячейке D10): =D8*D9
Итоговая общая прибыль (формула в ячейке E10):=СУММ(B10:D10)
3. Запустите надстройку «Поиск решения». Установите целевую ячейку, равной максимальному значению. Укажите параметры поиска, как указано на рисунке.
В качестве целевой ячейки (равной максимальному значению) укажите ячейку Е10, в качестве изменяемых ячеек — диапазон ячеек количества сырья — B9:D9. Не забудьте указать ограничения на запас сырья. Установите параметры поиска решения. Для этого кнопкой «Параметры» откройте диалоговое окно «Параметры поиска решения» установите параметры по образцу, задайте линейную модель расчета.
4. Кнопкой «Выполнить» запустите «Поиск решения». Если вы сделали все верно, то решение будет как на рисунке.
5. Сохраните созданный документ под именем «Оптимизация в Microsoft Excel».
Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг. продукции В и 22,22 кг. продукции С. Продукцию А производить не стоит. Полученная прибыль при этом составит 527,78 руб.
Задача 3. Минимизация фонда заработной платы фирмы.
Пусть известно, что для нормальной работы фирмы требуется 5...7 курьеров, 8... 10 младших менеджеров, десять менеджеров, трое заведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы. Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 руб.
|
|
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Ai * х +Вi, где х - оклад курьера; Аi и Вi - коэффициенты, показывающие:
Ai – во сколько раз превышается значение х; Bi – на сколько превышается значение х.
В качестве модели решения этой задачи возьмем линейную модель. Тогда условие задачи имеет вид: N1 * А1 * х + N2 * (А2 * х + В2) +... + N8 * (А8 * х + В8) = Минимум,
где N, — количество работников данной специальности; х — зарплата курьера; А и В, — коэффициенты заработной платы сотрудников фирмы.
Порядок работы.
1. Создайте на третьем листе Microsoft Excel расчетную таблицу с исходными данными.
2. Запустите надстройку «Поиск решения». Установите целевую ячейку F13 (суммарный фонд заработной платы), равной минимальному значению. В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера — $E$6:$E$7:$D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]). Укажите параметры поиска, как указано на рисунке. Активизировав кнопку Параметры, введите параметры поиска.
Запустите процесс поиска решения нажатием кнопки «Выполнить». В открывшемся диалоговом окне «Результаты поиска решения» задайте опцию «Сохранить найденное решение».
Если вы сделали все верно, то решение будет как на рисунке.
 |
Задачи для самостоятельного решения
Решить задачу на четвертом листе согласно указанному преподавателем варианту.
| 
|
| 
|
| 
|
| 
|
| 
|