CC BY
964
Интернет-журнал «Науковедение» ISSN 2223-5167 http ://naukovedenie.ru/ Том 7, №3 (2015) http ://naukovedenie. ru/index.php?p=vol7-3 URL статьи: http://naukovedenie.ru/PDF/54TVN315.pdf DOI: 10.15862/54TVN315 (http://dx.doi.org/10.15862/54TVN315)
УДК [330.45]
Барышев Александр Владимирович
НОУ ВПО «Российский новый университет»
Россия, Москва1 Кандидат технических наук Доцент
E-mail: awb49@yandex.ru Федотова Елена Леонидовна
ФГБОУ ВПО «Национальный исследовательский университет (МИЭТ)» 2
Москва, Россия Кандидат педагогических наук
Доцент
E-mail: fedotova-e2007@yandex.ru
К вопросу использования надстройки Excel «поиск решения» в задачах линейного программирования
1 105005, г. Москва, ул. Радио, 22
2 124460, г. Москва, Филаретовская ул., корп. 1134, кв. 165 1
Аннотация. Существует насущная необходимость принятия эффективных управленческих решений.
В условиях полной определенности широко используются задачи линейного программирования, где требуется найти оптимальное решение. При этом оптимальность принимаемого решения во многом зависит от количества существующих альтернатив и их научно-технической обоснованности. В некоторых случаях существует несколько альтернативных оптимальных решений одной задачи. Их поиск в задачах линейного программирования процесс весьма трудоёмкий.
Обычно для решения задач линейного программирования используется надстройка Excel «поиск решения». Однако не все предлагаемые ей методы равнозначны при поиске альтернативных оптимальных решений. Кроме того, эффективность поиска зависит от начального приближения целевой функции. Поэтому целью настоящей работы являются выбор наиболее эффективного метода поиска альтернативных оптимальных решений задач линейного программирования с помощью надстройки Excel «поиск решения» и задание наилучших начальных приближений целевой функции.
В работе проведено сравнение эффективности симплексного метода и метода обобщенного приведенного градиента надстройки Excel «поиск решения» при нахождении альтернативных оптимальных решений задач линейного программирования и предложен эвристический способ задания начальных приближений целевой функции, позволяющий сократить время поиска.
Ключевые слова: надстройка Excel «поиск решения»; задачи линейного программирования; альтернативные оптимальные решения; симплексный метод; метод обобщенного приведенного градиента; целевая функция; ограничения; начальное приближение целевой функции.
Ссылка для цитирования этой статьи:
Барышев А.В., Федотова Е.Л. К вопросу использования надстройки Excel «поиск решения» в задачах линейного программирования // Интернет-журнал «НАУКОВЕДЕНИЕ» Том 7, №3 (2015) http://naukovedenie.ru/PDF/54TVN315.pdf (доступ свободный). Загл. с экрана. Яз. рус., англ. DOI: 10.15862/54TVN315
Менеджеру любого ранга повседневно приходится принимать целый ряд решений связанных с экономикой и управлением. При этом, с точки зрения эффективности, принятое решение должно быть оптимальным. Выполнить это требование бывает очень трудно, особенно если математическая модель задачи является негладкой и нелинейной или сама задача плохо структурирована.
Существует много методов принятия управленческих решений [1]. Здесь же мы остановимся на принятии управленческих решений в задачах линейного программирования [2, 3], а именно на использовании надстройки поиск решения Excel в задачах линейного программирования [4].
Как известно, эффективность принимаемого решения во многом зависит от количества существующих альтернатив и их научно-технической обоснованности [5, 6]. При решении задач линейного программирования, в некоторых случаях, найденное оптимальное решение оказывается не единственным [7]. Поэтому, с целью обеспечения большей свободы выбора управленческого воздействия, необходимо выявить все оптимальные альтернативные решения.
В работе предлагается эвристический способ задания начальных приближений целевой функции, позволяющий сократить время поиска.
Этим обусловлена актуальность и практическая ценность настоящей работы.
Существуют различные признаки, указывающие на наличие оптимальных альтернативных решений. При использовании надстройки поиск решения Excel в задачах линейного программирования в качестве таких признаков является наличие в отчете по устойчивости нулей «в таблице «Изменяемые ячейки» в столбцах «Допустимое увеличение» и «Допустимое уменьшение»...» (отчет получают при использовании симплексного метода решения) [8]. Однако, найти эти оптимальные альтернативные решения не так просто, поскольку результат поиска решения зависит от начального приближения и не только. Рассмотрим этот вопрос подробнее. Для этого возьмем несколько конкретных примеров.
Пример 1 [7]. Найти максимум целевой функции
Внесем данные на лист Excel (см. рисунок 1).
В ячейки B2:D2 названия переменных, а в ячейки B3:D3 - коэффициенты при целевой функции F(x).
В ячейки B2:D2 диапазона (B5:D5; B7:D7) запишем коэффициенты при неизвестных в ограничениях.
В ячейки B10:D10, первоначально подставляются начальные условия, например, все единицы или нули.
В ячейках E5,E6,E7 записываются формулы вычисления левой части ограничений, а в ячейки G5,G6,G7 - значения правой части.
В ячейку E10 записывается формула вычисления целевой функции.
Р(х) =Х1+Х2+Хз
(1)
при ограничениях
(2).
AI
A В С D Е F G
1 перем лев часть знак прав часть
2 наименование Xl х2 ¡(3
3 коэф в цел.функции 1 1 1
4
5 коэф в 1 огранич -1 3 2 =СУ М М П РОИ ЗВ[ В5: D5; В10: D1 s &
S коэф в 2 ограним 2 4 2 =СУ М М П РО ШВ( В 6: D6; В10: D1 s а
7 коэф в 3 огранич 3 2 -1 =СУ М М П Р О И ЗВ( В 7: D7; В10: D1 £ 4
S
9 XI1 х2' хЗг т
10 оптим значение 0 0 0 =СУММПРОИЗВ(ВЗ:ОЗ;В10: max
Рисунок 1. Фрагмент листа Excel, заполненный данными из формул (1) и (2)
(произведен авторами)
Теперь можно непосредственно перейти к использованию «надстройки поиск решения».
Через меню «данные» и «поиск решения» входим в таблицу «параметры поиска решения» (см. рис. 2).
Параметры поиска решения I
Оптимизировать целевою функцию:
До: Максимум Минимум
Изменяя ячейки переменных:
шш
Значения:
Ш
Б соответствии с ограничениями:
ÎEÎUO >= 0
$C$U0 >= 0
IDE 10 >= □
ÎE$5 <= ÎG$5
$E$6 <= SGSo
SES7 <= $G$7
"W
Добавить
Изменить
Удалить
Сбросить
Загрузит ь/оохранить
Сделать переменные без ограничений неотрицательными Выберите
Поиск решения линейных задач симплекс-методом
Параметры
метод решения: Метод решения
Для гладких нелинейных задач используйте поиск решения нелинейных задач методом ОПГ, для линейных задач - поиск решения линейных задач симплекс-методом, а для негладких задач - эволюционный поиск решения.
Справка
Найти решение
Закрыть
Рисунок 2. Окно параметры поиска решения MS Excel
В неё вводим указание на ячейку со значением целевой функции ($E$10). Указываем, что ищем максимум целевой функции, и указываем диапазон изменяемых ячеек ($B$10:$D$10). Затем через пункт таблицы «добавить» вводим указания на ограничения, предварительно установив параметры поиска:
1. Точность ограничения - 0.001.
2. Максимальное время (в секундах) - 100.
3. Число итераций - 100.
4. Максимальное число допустимых решений (установим, например) - 4.
При необходимости устанавливаем метки на такие пункты как «Использовать автоматическое масштабирование» и «Показывать результаты итераций» и др.
Минько А.А. рекомендует использовать автоматическое масштабирование во всех случаях [8]. Кроме того, многие авторы, например [4, 8] рекомендуют для решения задач линейного программирования использовать симплексный метод решения. Это же рекомендуется в самом окне «параметры поиска решения» MS Excel. Однако это, также как и использование автоматического масштабирования, как это будет показано ниже, не всегда оказывается целесообразным.
Теперь, нажав кнопку «найти решение» находим искомое решение. Максимум целевой функции будет помещен в ячейку E10. В ячейки В10, С10, D10 будут записаны соответственно Х1, Х2, хз.
В качестве начального приближения используем значения переменных Х1, х2, хз= 0.
После найденного оптимального решения убедимся, что существуют альтернативные оптимальные решения. Для этого создадим «отчет об устойчивости».
На рис. 3. поместим фрагмент отчета об устойчивости.
Ячейки переменных
Окончательное Приведенн. Целевая функция Допустимое Допустимое
Ячейка Имя Значение Стоимость Коэффициент Увеличение Уменьшение
$В$10 оптим значение xl1 2 0 1 1Е+30 0
$С$10 оптим значение х2' 0 -1 1 1 1Е4-30
$D$10 оптим значение хЗ' 2 0 1 0 1
Рисунок 3. Фрагмент отчета об устойчивости (получен авторами)
Как следует из рисунка, в двух крайних столбцах имеются нули. Это указывает на наличие альтернативных оптимальных решений.
Исследуем возможности надстройки «поиск решения» для получения оптимальных альтернативных решений. С этой целью будем различным образом задавать начальное приближение целевой функции (методом случайного поиска и предлагаемым способом), а также будем изменять сам метод поиска (с симплексного на метод обобщенного приведенного градиента (ОПТ) [9]). Результаты поместим в таблицы 1 и 2.
Таблица 1
Поиск альтернативных решений целевой функции (1) симплексным методом
(составлено авторами)
Метод решения Способ задание начального приближения Начальное приближение Значение целевой функции Значение переменных
Симплекс ный метод решения Случайный поиск Х1,Х2,Х3= 0 4.00 Х1= 2.00; Х2=0.00; хз= 2.00
Х1,Х2,Х3= 1 4.00 Х1= 2.00; Х2=0.00; хз= 2.00
Х1,Х2,Х3 = 2 4.00 Х1= 2.00; Х2=0.00; хз= 2.00
Способ перестановки ограничений Х1,Х2,Х3= 0 4.00 Х1= 2.00; Х2=0.00; хз= 2.00
Х1 = 6; Х2 = 8; хз= 4 4.00 Х1= 2.00; Х2=0.00; хз= 2.00
Х1 = 4; Х2 = 6; хз= 8 4.00 Х1= 2.00; Х2=0.00; хз= 2.00
Х1 = 8; Х2 = 4; хз= 6 4.00 Х1= 2.00; Х2=0.00; хз= 2.00
Как следует из данных таблицы 1, при использовании различных способов задания начальных приближений симплексный метод не обеспечивает поиск альтернативных оптимальных решений. Изменим симплексный метод решения на метод обобщенного приведенного градиента (ОПТ). Результаты поместим в таблицу 2.
Как следует из данных таблицы 2, предлагаемый способ задания начальных приближений обеспечил поиск 4 альтернативных решений, если же использовать автоматическое масштабирование, то можно найти только два альтернативных решения.
Таблица 2
Поиск альтернативных решений целевой функции (1) методом ОПГ
(составлено автором)
Метод решения Способ задания начального приближения Начальное приближение Значение целевой функции Значение переменных
Метод решения нелинейных задач методом ОПГ Способ перестановки ограничений (без масштабирова ния) Х1,Х2,Х3= 0 4.00 Х1= 1.90; Х2= 0.00; хз= 2.10
Х1 = 6; Х2 = 8; хз= 4 4.00 Х1= 1,27; Х2= 0.00; хз= 2,73
Х1 = 4; Х2 = 6; хз= 8 4.00 Х1= 0.67; Х2= 0.00; хз= 3.33
Х1 = 8; Х2 = 4; хз= 6 4.00 Х1= 2.00; Х2= 0.00; Х3= 2.00
Пример 2. Найти максимум целевой функции [10]:
Fx = х1 + 2х2 + 3х3 (3)
Ограничения:
!х1 + 2х2 + 3х3 < Ш
х1 + х2 < 5 }, х1, х2, х3 < 0 (4)
х1 < 1 )
Подобно примеру 1 вводим данные выражений (3) и (4) в таблицу Excel (см. рис. 4).
А1 £
А В С D Е F G
1 перем лев масть знак прав часть
2 наименование XI х2 хЗ
3 коэф в цел.функции 1 2 3
4
5 коэф в 1 ограним 1 2 3 =СУММПР0ИЗВ(В5:05;ВЮ:Ш 10
6 коэф в 2 ограним 1 2 0 =СУ М М П РО И ЗВ( В 6: D6; Б10: D1 < 5
7 коэф в 3 ограним 1 0 0 =СУ М М П Р а И ЗВ( В 7: D7; В10: D1 1
а
э х1гх2,хЗ хГ х2' хЗ' F(x) > 0
10 оптим значение 0 0 0 =СУММПРОИЗВ(ВЗ:ОЗ;В10: max t
Рисунок 4. Фрагмент листа Excel, заполненный данными (3) и (4) (произведен авторами)
Произведем поиск альтернативных решений, используя метод решения нелинейных задач ОПГ и предлагаемый способ задания начальных приближений. Результаты поиска поместим в таблицу 3.
Как видим из данных таблицы 3, найдено 3 альтернативных оптимальные решения, при этом мы избежали случайного поиска начальных значений.
Таблица 3
Поиск альтернативных решений целевой функции (3) методом ОПГ
(составлено авторами)
Метод решения Метод задания начального приближения Начальное приближение Значение целевой функции Значение переменных
Метод решения нелинейных задач методом ОПГ Способ перестановки ограничений (без масштабирова ния) Х1,Х2,Х3= 0 10.00 Х1= 0,71; х2= 1,43; хз= 2,14
Х1 = 10; Х2 = 5; хз = 1 4.00 Х1= 1,00; х2= 2,00; хз= 1,67
xi = 5; х2 = 1; хз = 10 4.00 Х1= 0.00; х2= 0.00; хз= 3.33
Х1 = 1; х2 = 10; хз= 5 4.00 Х1= 1,00; х2= 2,00; х3= 1,67
На основании изложенного материала можно сделать следующие выводы:
1. Начинать решение задачи линейного программирования с помощью надстройки Excel «поиск решения» следует с помощью симплексного метода, получив при этом отчет об устойчивости. Если в отчете по устойчивости «в таблице «Изменяемые ячейки» в столбцах «Допустимое увеличение» и «Допустимое уменьшение»...» есть нули, то необходимо искать альтернативные оптимальные решения.
2. Симплексный метод решения не обеспечивает поиск альтернативных оптимальных решений.
3. Поиск альтернативных оптимальных решений следует производить методом обобщенного приведенного градиента (ОПГ) без автоматического масштабирования, используя задание начальных приближений с помощью
предлагаемого способа перестановки ограничений. Это позволит сократить затраченное время.
ЛИТЕРАТУРА
1. Орлов А.И. Организационно-экономическое моделирование: теория принятия решений: учебник. - М.: КНОРУС, 2011. - 568 с.
2. Титов В.В. Оценка эффективности оптимизационного планирования деятельности промышленного предприятия / Регион: экономика и социология. 2007. №1. С. 241-250.
3. Созонов С.В. Разработка моделей оптимизации производственной программы промышленного предприятия на основе формулирования целевых функций / Экономические науки. 2010. Т. 67. №6. С. 231-235.
4. Урубков А.Р., Федотов И.В. Методы и модели оптимизации управленческих решений: учебное пособие. - М.: Издательство «Дело» АНХ, 2009. - 240 с.
5. Баллод Б.А., Елизарова Н.Н. Методы и алгоритмы принятия решений в экономике: учебное пособие. - М.: Финансы и статистика; ИНФРА-М, 2009. -224 с.
6. Барышев А.В., Федотова Е.Л. К вопросу формирования творческих способностей выпускников вузов // Интернет-журнал «НАУКОВЕДЕНИЕ» 2014. №4 http:// http://naukovedenie.ru/PDF/80PVN414.pdf (доступ свободный). Загл. с экрана. Яз. рус.
7. Агальцов В.П. Математические методы в программировании: учебник. - М.: ИД «ФОРУМ», 2010. - 240 с.
8. Минько А.А. Принятие решений с помощью Excel. Просто как дважды два. -М.: Эксмо, 2007. - 240 с.
9. Метод обобщенного приведенного градиента. http://iasa.org.Ua/lections/iso/6/6.7.h tm.
10. Палий И.А. Линейное программирование. Учебное пособие. - М.: Эксмо, 2008. 256 с.
Рецензент: Ключников Анатолий Васильевич, доцент, кандидат технических наук, Национальный исследовательский университет «МИЭТ».
Barushev Alexsandr Vladimirowich
NOU VPO «Russian new University» Russia, Moscow E-mail: awb49@yandex.ru
Vedotova Elena Leonidovha
National Research University of Electronic Technology
Russia, Moscow E-mail: awb49@yandex.ru
Finding optimal solutions for linear programming decision problems using Microsoft Excel Solver Add-in.
Abstract. The vital necessity does exist for making effective management decisions.
Linear programming approach is widely used in case of the full certainty where an optimal decision must be found. The optimality of a found decision mostly depends on the number of existing alternatives and their scientific feasibility. In some cases there can be several alternative optimal solutions for a single decision problem. Finding the best one of these alternatives is a quite difficult and hard-working process.
Often Microsoft Excel Solver Add-in is used to solve linear programming decision problems. However not all of its techniques are equal when finding an optimal solution for a decision problem. Further, the efficiency of finding a solution depends on initial approximation of the objective function. Therefore, the aim of this paper is to choose the most efficient method for finding optimal solutions of linear programming problems using Microsoft Excel Solver Add-in by providing the best input approximation for objective function.
This paper compares the efficiency of simplex and generalized reduced gradient methods in Microsoft Excel for finding alternative optimal solutions of linear programming problems. Also this paper introduces heuristic method of specifying the initial approximations of the objective function which allows to reduce the search time.
Keywords: Microsoft Excel Solver; linear programming problems; alternative optimal solutions; simplex method; generalized reduced gradient method; objective function.
КЕГЕКЕ^Е8
1. Orlov A.I. Organizatsionno-ekonomicheskoe modelirovanie: teoriya prinyatiya resheniy: uchebnik. - M.: KNORUS, 2011. - 568 s.
2. Titov V.V. Otsenka effektivnosti optimizatsionnogo planirovaniya deyatel'nosti promyshlennogo predpriyatiya / Region: ekonomika i sotsiologiya. 2007. №1. S. 241250.
3. Sozonov S.V. Razrabotka modeley optimizatsii proizvodstvennoy programmy promyshlennogo predpriyatiya na osnove formulirovaniya tselevykh funktsiy / Ekonomicheskie nauki. 2010. Г 67. №6. S. 231-235.
4. Urubkov A.R., Fedotov IV Metody i modeli optimizatsii upravlencheskikh resheniy: uchebnoe posobie. - М.: Izdatel'stvo <^е1о» А^ЫК^ 2009. - 240 s.
5. Ballod B.A., Elizarova N.N. Metody i algoritmy prinyatiya resheniy v ekonomike: uchebnoe posobie. - M.: Finansy i statistika; INFRA-M, 2009. - 224 s.
6. Baryshev A.V., Fedotova E.L. K voprosu formirovaniya tvorcheskikh sposobnostey vypusknikov vuzov // ^г^^ита! «NAUKOVEDENIE» 2014. №4 http:// http://naukovedenie.ru/PDF/80PVN414.pdf (dostup svobodnyy). Zagl. s екгапа. Yaz. rus.
7. Agal'tsov У.Р. Matematicheskie metody V programmirovanii: иЛеЬшк. - М.: ID «FORUM», 2010. - 240 s.
8. Мт'ко А.А. Prinyatie resheniy s pomoshch'yu Ехсе1. Prosto как dvazhdy dva. - М.: Eksmo, 2007. - 240 s.
9. Metod obobshchennogo privedennogo gradienta. http://iasa.org.Ua/lections/iso/6/6.7.htm.
10. Paliy 1.А. Lineynoe programmirovanie. Uchebnoe posobie. - М.: Eksmo, 2008. 256 s.
