Поэтапное решение.
После того как введена исходная матрица ограничений, в соответствующие клетки вводятся расчетные формулы целевой функции и ограничений (рисунок 3)
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
|
|
|
Переменные |
|
|
|
|
2 |
имя |
прод1 |
прод2 |
прод3 |
прод4 |
|
|
|
3 |
значение |
|
|
|
|
|
|
|
4 |
нижн.гр |
|
|
|
|
|
|
|
5 |
верх.гр |
|
|
|
|
|
|
|
6 |
коэф.в ЦФ |
60 |
70 |
120 |
130 |
=СУММПРОИЗВ(В$3:E$3;B6:E6) |
макс |
|
7 |
|
|
|
Ограни-чения |
|
|
|
|
8 |
вид |
|
|
|
|
левая часть |
знак |
правая часть |
9 |
трудовые |
1 |
1 |
1 |
1 |
=СУММПРОИЗВ(В$3:E$3;B9:E9) |
<= |
16 |
10 |
сырье |
6 |
5 |
4 |
3 |
=СУММПРОИЗВ(В$3:E$3;B10:E10) |
<= |
110 |
11 |
финансы |
4 |
6 |
10 |
13 |
=СУММПРОИЗВ(В$3:E$3;B11:E11) |
<= |
100 |
Рисунок 3
Для решения задачи запускаем Поиск решения по маршруту: Сервис-Поиск решения. На экране появится диалоговое окно Поиск решения (рисунок 4).
Рисунок 4
В поле установить целевую ячейку необходимо указать адрес ячейки, значение которой будет использоваться как критерий оптимизации. С помощью переключателей выбора устанавливается значение критерия оптимальности, равное максимальному значению. В поле Изменяя ячейки необходимо указать диапазон ячеек, которые программа должна изменить для оптимального значения. Чтобы задать ограничения нужно нажать кнопку Добавить (рис.5).
Рисунок 5
В окне Поиск решений нажать на кнопку Параметры. Здесь устанавливается линейность модели и неотрицательные значения (рисунок 6).
Рисунок 6
После того, как установлены параметры поиска решения, нужно нажать кнопку выполнить. На экране появится диалоговое окно Результаты поиска решения. Решение найдено (рис. 7) и результат оптимального решения задачи приведены в таблице (рис. 8).
Рисунок 7
|
А |
В |
С |
D |
Е |
F |
G |
H |
1 |
|
|
|
Переменные |
|
|
|
|
2 |
имя |
прод1 |
прод2 |
прод3 |
прод4 |
|
|
|
3 |
значение |
10 |
0 |
6 |
0 |
|
|
|
4 |
нижн.гр. |
|
|
|
|
|
|
|
5 |
верхн.гр. |
|
|
|
|
|
|
|
6 |
коэф. в ЦФ |
60 |
70 |
120 |
130 |
1320 |
макс |
|
7 |
|
|
|
Ограничения |
|
|
|
|
8 |
вид |
|
|
|
|
левая часть |
знак |
правая часть |
9 |
трудовые |
1 |
1 |
1 |
1 |
16 |
<= |
16 |
10 |
сырье |
6 |
5 |
4 |
3 |
84 |
<= |
110 |
11 |
финансы |
4 |
6 |
10 |
13 |
100 |
<= |
100 |
Рисунок 8
На рисунке 8 видно, что для получения максимальной прибыли равной 1320 руб., предприятию необходимо выпустить 10 единиц продукции 1 вида и 6 единиц продукции 3 вида.
ВЫВОДЫ
Множество задач планирования и управления решаются методами математического программирования. Наиболее развитыми в области решения оптимизационных задач являются методы линейного программирования. Excel тот пакет, который позволяет решать такие задачи с помощью программы Поиск решения.
Чтобы решить задачу с помощью Поиска решения следует сначала составить ее математическую модель (формализовать задачу). Основным способом решения задач оптимизации является симплекс-метод.
Математическую модель необходимо адаптировать под программное обеспечение Excel. Для этого составляется матрица ограничений. После того как введена исходная матрица ограничений, в соответствующие клетки вводятся расчетные формулы целевой функции и ограничений.
Для решения задачи запускают Сервис – Поиск решения. При нажатии кнопки Параметры, устанавливается линейность модели.