Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Что осваивается и изучается?
Решение задачи определения оптимального плана и транспортной задачи при помощи надстройки «Поиск решения».
Задание 1. Задача об оптимальном ассортименте. Предприятие выпускает 2 вида продукции. Цена единицы 1-го вида продукции - 25 000, 2-го вида продукции – 50000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.
|
Продукция |
Запасы сырья |
|
|
1-й вид продукции |
2-й вид продукции |
|
|
1,2 |
1,9 |
37 |
|
2,3 |
1,8 |
57,6 |
|
0,1 |
0,7 |
7 |
Требуется определить плановое количество выпускаемой продукции таким образом, чтобы стоимость произведенной продукции была максимальной
Выполнение.
1. Такие задачи решаются при помощи инструмента Excel «Поиск решения». Для установки этого инструмента необходимо :
Главное меню: Сервис / Надстройки / Установить флажок «Поиск решения» / OK.
После загрузки инструмента «Поиск решения» в меню Сервис появляется команда «Поиск решения». Выполнение этой команды начинается с вывода диалогового окна, в котором вводятся исходные данные задачи.
2. Математическая модель задачи.
Пусть продукция производится в количестве:
1-й вид – x1 единиц, 2-й вид – x2 единиц.
Тогда стоимость произведенной продукции выражается целевой функцией
f(x1,x2)=25000 x1+50000x2,
для которой необходимо найти максимум.
При этом следует учесть ограничения по запасам сырья:
1,2 x1+1,9 x2 37,
2,3 x1+1,8 x2 57,6,
0,1 x1+0,7 x2 7
и по смыслу задачи x1, x2 должны быть неотрицательными и целыми:
x10, x2 0.
3. Ввод исходных данных в компьютер.
3.1. Введем целевую функцию и ограничения.
Для переменных x1,x2 определим соответственно ячейки С2:D2 и зададим им начальные значения, равные нулю. Затем коэффициенты целевой функции и нормы расхода сырья расположим под неизвестными в ячейках С3:D3 и С6:D8 соответственно. Запасы сырья расположим справа от матрицы норм расхода в ячейках G6:G8. В ячейке F2 вычислим значение целевой функции, а в ячейках F6:F8 реальный расход сырья.
|
Ячейка |
Формула |
|
F2 |
= СУММПРОИЗВ(C2:D2;C3:D3) |
|
F6 |
= СУММПРОИЗВ($C$2:$D$2;C6:D6) |
|
F7 |
= СУММПРОИЗВ($C$2:$D$2;C7:D7) |
|
F8 |
= СУММПРОИЗВ($C$2:$D$2;C8:D8) |
3.2. Задание параметров для диалогового окна «Поиск решения».
Выполнить команду Сервис / Поиск решения.
В диалоговом окне «Поиск решения» нужно указать:
адрес ячейки, в которой находится формула, вычисляющая значение целевой функция;
цель вычислений (задать критерий для нахождения экстремального значение целевой функции);
адреса ячеек, в которых находятся значения изменяемых переменных х1, х2;
матрицу ограничений, для чего нажимается кнопка «Добавить»;
параметры решения задачи, для чего нажимается кнопка «Параметры».
Диалоговое окно «Поиск решения» и схема расположения исходных данных приведены ниже. Информация в этом окне соответствует решаемой задаче.
После ввода всех данных и задания параметров нажать кнопку «Выполнить».
Ответ: 825000
2. Сетевая транспортная задача
Задание 2.1.
Три поставщика одного и того же продукта располагают в планируемый период следующими запасами этого продукта: первый- 120 условных единиц, второй- 100 и третий 80 единиц. Этот продукт должен быть перевезен к трем потребителям, спросы которых соответственно равны 90, 90 и 120 условных единиц. Приведенная ниже таблица содержит показатели затрат, связанных с перевозкой продукта из i-го пункта отправления в j-й пункт потребления.
Требуется перевезти продукт с минимальными затратами.
|
Поставщики |
Потребители и их спрос |
Запасы |
||
|
|
А |
Б |
В |
|
|
I |
7 |
6 |
4 |
120 |
|
II |
3 |
8 |
5 |
100 |
|
III |
2 |
3 |
7 |
80 |
|
Спрос |
90 |
90 |
120 |
|
Математическая модель задачи выглядит следующим образом.
Целевая функция имеет вид:
F(x)=7× x11+6× x12+4× x13+3× x21+8× x22+5× x23+2× x31+3× x32+7× x33® min,
Ограничения имеют вид:
x11+x12+x13=120, x21+x22+x23=100, x31+x32+x33=80, x11+x21+x31=90,
x12+x22+x32=90, x13+x23+x33=120,
xij³ 0, i, j=.
Искомые значения xij находятся в блоке ячеек B4:D6. Адрес данного блока входит в поле ввода Изменяя ячейки в окне “Поиск решения” . Требования к ограничениям по спросу и запасам представлены соответственно в ячейках B7:D7 и E4:E6. Коэффициенты ЦФ, означающие затраты на доставку расположены в блоке ячеек B12:D14.
Формулы целевой функции и ограничений находятся соответственно в ячейке F8 и ячейках B8:D8 (ограничения по спросу), F4:F6 (ограничения по запасам) . Вид электронной таблицы в режиме отображения формул представлен на рис.
Первая запись в группе Ограничения представляет ограничения по нижней границе xij. Вторая и третья записи выражают ограничения по уровню спроса и запасов соответственно.
Окончательный вид электронной таблицы Excel, созданной для решения задачи.
Задание 2.2.
На складах имеется груз, количество которого определяется в следующей таблице:
|
Склады |
Склад 1 |
Склад 2 |
Склад 3 |
|
Наличие груза на складе |
18 |
75 |
31 |
Этот груз необходимо перевезти в пункты назначения в соответствии с таблицей:
|
Пункты Назначения |
Пункт 1 |
Пункт 2 |
|
Потребность груза |
45 |
79 |
Стоимость перевозок определяется таблицей:
|
Пункт 1 |
Пункт 2 |
|
|
Склад 1 |
17 |
6 |
|
Склад 2 |
12 |
13 |
|
Склад 3 |
9 |
8 |
Необходимо составить план перевозок так, чтобы стоимость перевозок была минимальной.
Ответ: 1286.
Задание 3. Задача о смесях. Фирма «Корма» имеет возможность покупать 4 различных вида зерна (компонентов смеси) и изготавливать различные виды кормов. Разные зерновые культуры содержат разное количество питательных ингредиентов. Произведенный комбикорм должен удовлетворять некоторым минимальным требованиям с точки зрения питательности. Требуется определить, какая из возможных смесей является самой дешевой. Исходные данные приведены в следующей таблице
|
Единица веса |
Минимальные потребности на планируемый период |
||||
|
зерна 1 |
зерна 2 |
зерна 3 |
зерна 4 |
||
|
Ингредиент A |
2 |
3 |
7 |
1 |
1250 |
|
Ингредиент B |
1 |
0,7 |
0 |
2,3 |
450 |
|
Ингредиент C |
5 |
2 |
0,2 |
1 |
900 |
|
Ингредиент D |
0,6 |
0,7 |
0,5 |
1 |
350 |
|
Ингредиент E |
1,2 |
0,8 |
0,3 |
0 |
600 |
|
Затраты в расчете на ед. веса (цена) |
41 |
35 |
48 |
42 |
Минимизировать |
Ответ: 21778.
Задание 4. Балансовые модели. Имеется трехотраслевая балансовая модель экономики с матрицей ai,j коэффициентов затрат:
Производственные мощности отраслей ограничивают возможности ее валового выпуска числами Mi = {300, 200. 500}. Определить оптимальный валовой выпуск всех отраслей Xi, максимизирующий стоимость суммарного конечного продукта Yi, если задан вектор цен Ci на конечный продукт (2, 5, 1).
Конечный продукт определяется формулой
Yi = Xi - i=1,2,3
Целевая функция F(x1,x2,x3) = max
Ограничения валового выпуска xi mi
Ответ: 909
Задание 4а. Решить эту же задачу, если накладываются следующие ограничения на валовой выпуск продукции и конечный продукт отраслей:
валовый выпуск : X1 : X3 = 2 : 1 , конечный продукт: Y2 <=100
Ответ: 907,5
Задание 4б. К данным задачи 4 заданы коэффициенты прямых затрат труда на выпуск продукции каждой отрасли. Определить максимально возможный выпуск конечного продукта в стоимостном выражении, если суммарные затраты труда не должны превышать заданного числа единиц.
|
Коэффициенты прямых затрат труда на выпуск продукции отраслей |
Суммарные затраты труда |
||
|
1-я отрасль |
2-я отрасль |
3-я отрасль |
|
|
0,2 |
0,3 |
0,15 |
<= 70 |
Ответ: 789