Решение оптимизационных задач в среде Excel-7.0
Автор: Кондратьев Юрий Николаевич
Журнал: Ученые записки Петрозаводского государственного университета @uchzap-petrsu
Рубрика: Технические науки
Статья в выпуске: 7 (101), 2009 года.
Бесплатный доступ
Оптимизация, среда excel-7
Короткий адрес: https://sciup.org/14749610
IDR: 14749610
Текст статьи Решение оптимизационных задач в среде Excel-7.0
В настоящее время решение оптимизационных задач является актуальной проблемой. При этом около 85 % всех оптимизационных задач решается симплекс-методом линейного программирования.
Основная задача линейного программирования заключается в нахождении неотрицательных значений переменных, которые удовлетворяли бы условиям и приводили бы целевую функцию к максимуму или минимуму. В настоящее время подобные задачи решаются при помощи программирования на различных алгоритмических языках. В то же время решение этих задач можно осуществлять более простыми методами в среде Excel-7.0 [1], [2].
Решение оптимизационной задачи рассмотрим на конкретном примере нахождения оптимального плана производства изделий мебели посредством решения данной задачи линейного программирования симплекс-методом на ПЭВМ с использованием пакета программ Excel-7 в среде Windows со следующими условиями задачи: 1. На мебельной фабрике производят три вида столов: вид 1, вид 2 и вид 3.
-
2. Для изготовления каждого вида стола используют пиломатериалы двух пород древесины: сосновые и березовые.
-
3. Объемы пиломатериалов обозначим А(I, J), где I = 1, 2 – сосновые пиломатериалы, а J = 1, 2, 3 – березовые пиломатериалы, в единицах измерения м3/1000, причем поставки количества пиломатериалов В(I) ограничены возможностями поставщиков.
-
4. Каждый вид стола имеет свою стоимость С(J) в условных единицах.
-
5. Исходные данные приведены в табл. 1.
Требуется определить план производства столов Х(J) с целью получения максимальной прибыли Z = max. Для данных условий составим математическую модель (1) и запишем целевую функцию (2):
X(1) +3 *X(2)+2 * X(3) = B(1), B(1) = 3000
-
6 * X(1) + 5 * X(2) + 2 * X(3) = B(2), B(2) = 3320
Х(1) ≥ 0 (1)
Х(2) ≥ 0
Х(3) ≥ 0
Z=18*X(1)+27*X(2) + 17 * X(3) → max (2)
Для решения задачи в электронную таблицу вводятся исходные данные из табл. 1, записыва- ются условия из табл. 2 и задаются параметры в окне диалога «Поиск решения».
Стоимость по видам столов в условных единицах записываем в блок ячеек B13:D13 (табл. 3). Коэффициенты при неизвестных (нормы затрат пиломатериалов по видам столов) заносим в ячейки B19:D20. В ячейки H17 и H18 записываем максимальное количество поставляемых сосновых и березовых пиломатериалов. В ячейку F7 записываем условия целевой функции:
СУММПРОИЗВ(В7:D7;B13:D13).
Для выполнения расчетов выбирается команда: Сервис / Поиск решения / Выполнить .
В результате поиска решения найден оптимальный план (табл. 3) производства столов, который составил: столов первого вида – 64, второго вида – 0, и третьего вида – 1468 шт. при максимальной целевой функции: Z = 26108 у. е.
Таблица 1
Условия задачи
|
Пиломатериалы |
Вид стола |
Поставка пиломатериалов, В(I), м 3 / 1000 |
||
|
1 |
2 |
3 |
||
|
Нормы затрат пиломатериалов А(I, J), м 3 / 1000 |
||||
|
Сосновые |
1 |
3 |
2 |
3000 |
|
Березовые |
6 |
5 |
2 |
3320 |
|
Цена стола C(J), у. е. |
18 |
27 |
17 |
– |
Таблица 2
Условия задачи в электронной таблице
|
Запись условий в ячейку |
Запись ограничений в окно Поиск решения |
|
|
адрес ячейки |
условия |
|
|
F17 |
СУММПРОИЗВ(В7:D7;В19:D19) |
$F$17<=$H$17 |
|
F18 |
СУММПРОИЗВ(В7:D7;В20:D20) |
$F$18<=$H$18 |
|
$B$7>=0 |
||
|
$C$7>=0 |
||
|
$D$7>=0 |
||
Таблица 3
Электронная таблица решения оптимизационной задачи
|
A |
B |
C |
D |
E |
F |
G |
H |
|
|
1 |
||||||||
|
2 |
Оптимальный расчет программы производства столов |
|||||||
|
3 |
||||||||
|
4 |
||||||||
|
5 |
Количество столов, шт |
|||||||
|
6 |
X 1 |
X 2 |
X 3 |
Целевая функция Z |
max |
|||
|
7 |
64 |
0 |
1468 |
26108 |
у. е. |
|||
|
8 |
||||||||
|
9 |
||||||||
|
10 |
||||||||
|
11 |
Цена стола, у. е. |
|||||||
|
12 |
С 1 |
С 2 |
С 3 |
|||||
|
13 |
18 |
27 |
17 |
|||||
|
14 |
||||||||
|
15 |
||||||||
|
16 |
||||||||
|
17 |
Нормы затрат п/м |
3000 |
<= |
3000 |
||||
|
18 |
А i,1 |
А i,2 |
А i,3 |
3320 |
<= |
3320 |
||
|
19 |
1 |
3 |
2 |
|||||
|
20 |
6 |
5 |
2 |
|||||
Список литературы Решение оптимизационных задач в среде Excel-7.0
- Кондратьев Ю. Н. Оптимизация транспортных перевозок в среде Excel-7.0: Тезисы докладов междунар. конф. «Новые технологии и устойчивое управление в лесах Северной Европы». Петрозаводск: Изд-во ПетрГУ, 2001. С. 66.
- Кондратьев Ю. Н. Решение транспортной задачи в среде Excel-7.0//Труды лесоинженерного факультета ПетрГУ. Вып. 3. Петрозаводск: Изд-во ПетрГУ, 2001. С. 55-57.
