Задача 1.
Определить план производства (найти такое количество товаров каждого вида) таким образом, чтобы суммарная прибыль была максимальной и выполнялись ограничения: общее количество товаров и суммарные расходы не должны превышать предельных значений.
Порядок выполнения:
1.На рабочем листе создать таблицу с данными о товарах, согласно варианту, и провести расчет по формулам ВР=В*К П=ПП*ВР
2. С помощью контекстного меню скопировать таблицу на новый лист2. Листу1 присвоить имя «исходная таблица».
3.На Листе 2 для исходной таблицы вызвать команду «Поиск решений » из меню Сервис. В открывшемся окне процедуры «Поиск решения » указать параметры процедуры «Поиск решения »:
целевая ячейка - суммарная прибыль (максимальное значение) – $F$8
изменяя ячейки – кол-во товаров каждого вида; нужно указать диапазон пустых ячеек, в которых после выполнения процедуры «Поиск решения » будет получено значения оптимальных выпусков – $C$2:$C$7
ограничения – кол-во товаров целые и положительные, суммарные расходы и общее кол-во товаров не должны превышать предельных значений
например $C$8<$C$9; $D$8<$D$9.
После заполнения полей диалогового окна выполнить процедуру «Поиск решения».
В отчете указать:
· таблицу с данными о товарах (вариант задания – см. пример)
· провести расчет по формулам
· указать параметры процедуры «Поиск решения» с адресами ячеек
· таблицу с результатами выполненной процедуры «Поиск решения»
Варианты индивидуальных заданий
Таблица 1
| Вариант | Товары | Предельные значения | |
| Общее кол-во | Суммарные затраты | ||
| 1,2,3,4,5,6 | |||
| 7,8,9,10,11,12 | |||
| 13,14,15,16,17,18 | |||
| 1,3,5,7,9,11 | |||
| 2,4,6,8,10,12 | |||
| 6,8,10,12,14,16 | |||
| 5,7,9,11,13,15 | |||
| 7,9,11,13,15,17 | |||
| 6,8,10,12,14,16 | |||
| 3,8,10,12,14,16 | |||
| 1,2,3,16,17,18 | |||
| 4,5,6,13,14,15 | |||
| 7,8,9,10,11,12 | |||
| 1,2,5,6,9,10,13 | |||
| 1,4,8,11,12,16 | |||
| 5,13,14,15,17,18 | |||
| 1,8,,11,12,14,18 | |||
| 2,4,6,13,14,15 | |||
| 5,6,7,11,15,17 | |||
| 1,2,3,16,17,18 | |||
| 6,7,8,16,17,18 | |||
| 1,2,5,6,9,10,13 |
Таблица2. Информация о товарах.
| Наименование | Расходы, гр/шт. (Р) | Кол-во, шт. (К) | Процент прибыли (ПП) |
| Товар 1 | 5% | ||
| Товар 2 | 7% | ||
| Товар 3 | 13% | ||
| Товар 4 | 10% | ||
| Товар 5 | 9% | ||
| Товар 6 | 10% | ||
| Товар 7 | 9% | ||
| Товар 8 | 12% | ||
| Товар 9 | 22% | ||
| Товар 10 | 17% | ||
| Товар 11 | 15% | ||
| Товар 12 | 17% | ||
| Товар 13 | 13% | ||
| Товар 14 | 18% | ||
| Товар 15 | 33% | ||
| Товар 16 | 25% | ||
| Товар 17 | 23% | ||
| Товар 18 | 25% |
Задача 2.
1. Решение ЗЛП с помощью инструмента “Поиск решения”
При решении ЗЛП с использованием встроенного инструмента “Поиск решения” изначально необходимо представить исходную математическую модель в удобном для заполнения таблиц EXCEL виде. Рассмотрим использование данной процедуры на примере решения “Задачи об использовании сырья”.
Задача об использовании сырья
Небольшая фабрика изготовляет два вида красок: для наружных (Е) и внутренних (I) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн соответственно. Расходы продуктов А и В на 1 тонну соответствующих красок приведены в таблице:
| Исходный продукт | Расход исходных продуктов (в тоннах) на 1 тонну краски | Максимально возможный запас продукта, тонн | |
| краска Е | краска I | ||
| А | |||
| В |
Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает величину спроса на краску Е более чем на 1 тонну. Кроме того, установлено, что спрос на краску I никогда не превышает 2 тонн в сутки. Оптовые цены одной тонны красок равны: 3 тыс. грн. для краски Е, 2 тыс. грн. для краски I.
Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?
Решение:
Пусть Х1, Х2 – планируемый к производству суточный объём производства краски Е и I соответственно (в тоннах). Тогда целевая функция математической модели будет выражать суммарную прибыль от реализации краски обоих видов, а система ограничений – производственные и маркетинговые ограничения, накладываемые на переменные модели.
Таким образом, математическая модель данной задачи будет иметь вид:
Подготовим лист EXCEL к использованию процедуры “Поиск решения”:
1) в ячейках C2:D2 записываются наименования переменных модели (в общем случае количество ячеек в данном диапазоне равно количеству переменных в соответствующей математической модели);
2) ячейки C3:D3 резервируются для значений переменных модели, которые будут найдены после выполнения процедуры “Поиск решения”;
3) в ячейках C4:D4 записывают коэффициенты при переменных модели в целевой функции модели F(X1, Х2);
4) в ячейки C6:D9 (число строк диапазона равно количеству ограничений в системе ограничений математической модели, число столбцов – числу переменных) заносим матрицу коэффициентов при переменных X1 и Х2 в системе ограничений модели;
5) в ячейках G6:G9 записаны правые части системы ограничений модели;
6) ячейка Е4 (целевая ячейка) резервируется для вычисления оптимального значения целевой функции модели.
Для рассматриваемого примера лист EXCEL будет иметь вид (рис. 1):
После занесения исходных данных на лист EXCEL в целевую ячейку Е4 записывают формулу: СУММПРОИЗВ($C$3:$D$3;C4:D4), которую затем копируют с модификацией в ячейки Е6:Е9 (результат представлен на рис. 2):
 |
Примечание: для вызова встроенной функции СУММПРОИЗВ необходимо выполнить последовательность действий:
1) установить курсор в нужную ячейку (в нашем примере – в ячейку Е4);
2) вызвать “Мастер функций” (кнопка fx), далее “Математические” и выбрать “СУММПРОИЗВ”;
3) в появившейся экранной форме (см. рис. 3) установить курсор в “Массив 1” и выделить на листе EXCEL диапазон зарезервированных для значений переменных ячеек, поставив им абсолютные адреса ($C$3:$D$3) нажатием функциональной клавиши F4; перевести курсор в “Массив 2” и выделить диапазон ячеек, в которых записаны коэффициенты при переменных в целевой функции (C4:D4), после чего нажать “ОК”.
 |
Таким образом, после завершения всех подготовительных операций выбираем в “Сервис” процедуру “Поиск решения” (см. рис. 4). В появившейся экранной форме (см. рис 5) устанавливаем целевую ячейку - $Е$4, затем отмечаем флажком тип оптимизации (исходя из условий задачи) – максимизация; переводим курсор в “Изменяя ячейки” и выделяем на листе EXCEL диапазон зарезервированных для значений переменных ячеек ($C$3:$D$3); после чего, установив курсор в “Ограничения”, нажимаем “Добавить” и в появившейся экранной форме (см. рис. 6) отмечаем диапазон ячеек:
a) “Ссылка на ячейку” - $Е$6:$Е$9 (здесь записаны результаты суммирования левых частей неравенств в системе ограничений);
b) знак выбирается согласно построенной математической модели, причем, если не все ограничения имеют одинаковые знаки, то каждая группа таких ограничений программируется отдельно, для чего удобно ограничения с одинаковыми знаками располагать изначально рядом друг с другом;
c) “Ограничение:” - $G$6:$G$9 (здесь записаны правые части неравенств в системе ограничений модели).
По нажатию “ОК” возвращаемся к экранной форме “Поиск решения”. Выбираем пункт “Параметры”, где отмечаем флажком “Линейная модель” и ”Неотрицательные значения” (см. рис. 7), затем по нажатию “ОК” возвращаемся к экранной форме “Поиск решения”.
 |
После выбора опции “Выполнить” EXCEL проводит расчеты и результаты вычислений заносятся в ячейки C3:D3 и Е4, которые были зарезервированы для значений искомых переменных и оптимального значения целевой функции.
Окончательно лист EXCEL будет иметь вид (см. рис. 8):
