Прием заявок до 26 марта;pdf

Лабораторная работа № 1
Решение задач линейного программирования графическим методом с
использованием MS Excel
Цель работы – решить задачу линейного программирования
графическим методом, с использованием надстройки поиск решений
средствами MS Excel.
Исходные данные
Целевая функция: x  2 y  max
Ограничения, неравенства:
5 x  3 y  30
x y3
 3x  5 y  15
x  0; y  0
(1)
(2)
(3)
(4)
(5)
Порядок выполнения работы
Для решения необходимо построить область допустимых решений
задачи, ограниченную неравенствами (2), (3), (4).
1. В документе MS Excel в ячейки ввести формулы неравенств,
рассчитать для каждого неравенства значения координат (X Y)
для каждой точки;
2. В ячейку ввести формулу (1), рассчитать координаты точки, для
построения линии градиента;
3. Найти координаты точки пересечения линий неравенств, для
этого необходимо решить систему из уравнений (2) и (3);
4. Рассчитать точку max функции, для этого подставляем в целевую
функцию полученные значения (X Y) из решения системы
уравнений;
5. Построить диаграмму исходя из рассчитанных координат.
Выбрать Мастер диаграмм на панели Excel, выбрать точечную
диаграмму, в окне мастера диаграмм вводим данные для построения прямых
по координатам.
1
Таблица 1 – Координаты точек неравенств
5x+3y=30 x=
6 y=
0 x=
x-3=3
x=
3 y=
0 x=
3x+5y=15 x=
5 y=
3 x=
Таблица 2 – Координаты точек линии градиента
x-2y=0
x=
1 y=
0
6
5
y=
y=
10
3
y=
6
-2
Таблица 3 – Координаты точек пересечения линий неравенств
5x+3y=30 =>
8y=15
y=
1,88
x-y=3
x=3+y
x=
4,88
Таблица 4 – Точка max функции
x-2y=0
Fmax=
1,125
Таблица 5 – Сводная таблица данных (необходима для решения задачи графическим
методом)
координаты необходимые для графического метода
точка 1
точка 2
x
1-ая
прямая
2-ая
прямая
3-я
прямая
точки
линии
градиента
точка max
функции
y
x
y
6
0
0
10
3
0
6
3
0
3
5
6
1
-2
0
0
4,88
1,88
2
12
10
8
6
1-ая прямая
2-ая прямая
4
3-я прямая
точки линии градиента
2
точка max функции
0
-2
0
1
2
3
4
5
6
7
-4
Рисунок 1.1 - Вид диаграммы
3
Решение задачи при помощи встроенной функции Excel
«Поиск решения»
Поиск решения - это надстройка EXCEL, которая позволяет решать
оптимизационные задачи. Ecли, в меню Сервис отсутствует команда Поиск
решения, значит, необходимо загрузить эту надстройку. Выберите команду
Сервис Надстройки и активизируйте надстройку Поиск решения. Если же
этой надстройки нет в диалоговом окне Надстройки, то вам необходимо
обратиться к панели управления Windows, щелкнуть на пиктограмме
Установка и удаление программ и с помощью программы установки Excel
(или Office) установить надстройку Поиск решения.
После выбора команд Сервис  Поиск решения появится диалоговое окно
Поиск решения.
В диалоговом окне Поиск решения есть три основных параметра:
• Установить целевую ячейку
• Изменяя ячейки
• Ограничения
Сначала нужно заполнить поле Установить целевую ячейку. Во всех
задачах для средства Поиск решения оптимизируется результат в одной из
ячеек рабочего листа. Целевая ячейка связана с другими ячейками этого
рабочего листа с помощью формул. Средство Поиск решения использует
формулы, которые дают результат в целевой ячейке, для проверки
возможных решений. Можно выбрать поиск наименьшего или наибольшего
значения для целевой ячейки или же установить конкретное значение.
Второй важный параметр средства Поиск решения — это параметр
Изменяя ячейки. Изменяемые ячейки — это те ячейки, значения в которых
будут изменяться для того, чтобы оптимизировать результат в целевой
ячейке. Для поиска решения можно указать до 200 изменяемых ячеек. К
изменяемым ячейкам предъявляется два основных требования. Они не
должны содержать формул, и изменение их значений должно отражаться на
изменении результата в целевой ячейке. Другими словами, целевая ячейка
зависима от изменяемых ячеек.
Третий параметр, который нужно вводить, для Поиска решения – это
ограничения.
4
Для решения задачи необходимо:
1. Указать адреса ячеек, в которые будет помещен результат решения
(изменяемые ячейки);
2. Ввести исходные данные;
3. Ввести зависимость для целевой функции;
4. Ввести зависимости для ограничений;
Запустить Поиск решений.
5. Назначение целевой функции (установить целевую ячейку);
6. Ввод ограничений;
7. Ввод параметров для решения ЗЛП;
Таблица 6 – Решение задачи методом поиска решения
Переменные
X
значение
коэф при
ЦФ
Y
ЦФ
1
-2
Ограничение
лев
часть
1-ое ур-ие
2-ое ур-ие
3-е ур-ие
5
1
-3
3
-1
5
знак
>=
<=
<=
прав
часть
30
3
15
1) Создать таблицу 6;
2) В ячейку целевой функции вводим формулу СУМПРОИЗВ() для
диапазонов данных: строка значения для (X Y), и для диапазона коэф.
при ЦФ для (X Y) Рисунок 1.2;
3) В формуле СУМПРОИЗВ(), ячейки для диапазона «значения» сделать
абсолютными Рисунок 1.2;
4) Скопировать формулу СУМПРОИЗВ() в ячейки «левая часть» для
каждого уравнения Рисунок 1.2;
5) Выделить ячейку с формулой целевой функции;
6) Выбрать Сервис  Поиск решения;
7) Ввести необходимые данные в окно поиска решений Рисунок 1.3;
5
Рисунок 1.2 – Формула СУПРОИЗВ()
Рисунок 1.3 – Настройка окна «Поиск решений»
6