CC BY
227
Интернет-журнал «Науковедение» ISSN 2223-5167 http ://naukovedenie.ru/ Том 7, №4 (2015) http ://naukovedenie. ru/index.php?p=vol7-4 URL статьи: http://naukovedenie.ru/PDF/60EVN415.pdf DOI: 10.15862/60EVN415 (http://dx.doi.org/10.15862/60EVN415)
УДК 330.45
Барышев Александр Владимирович
НОУ ВПО «Российский новый университет»
Россия, Москва1 Доцент
Кандидат технических наук E-mail: awb49@yandex.ru
Поиск оптимальных альтернативных решений с помощью Excel в задачах целочисленного программирования
1 124460, Москва, Филаретовская ул., корп. 1134, кв. 165
Аннотация. Существует насущная необходимость принятия эффективных управленческих решений.
В условиях полной определенности широко используются задачи линейного программирования, где требуется найти оптимальное решение. В некоторых задачах переменные, входящие в целевую функцию должны быть выражены целыми числами.
При этом оптимальность принимаемого решения во многом зависит от количества существующих альтернатив и их научно-технической обоснованности. В некоторых случаях существует несколько целочисленных альтернативных оптимальных решений одной задачи. Их поиск в задачах целочисленного программирования процесс трудоёмкий.
Обычно для решения задач целочисленного программирования используется надстройка Excel «поиск решения». Однако не все предлагаемые ею методы равнозначны при поиске целочисленных альтернативных оптимальных решений. Кроме того, эффективность поиска зависит от начального приближения целевой функции. Поэтому целью настоящей работы являются выбор наиболее эффективного метода поиска альтернативных оптимальных решений задач целочисленного программирования с помощью надстройки «поиск решения» Excel (версии 2007-2013) и исследование возможности и особенности применения способа «перестановки ограничений» для задания начального приближения целевой функции.
В работе проведено сравнение эффективности симплексного метода и метода обобщенного приведенного градиента надстройки Excel «поиск решения» при нахождении альтернативных оптимальных решений задач целочисленного программирования и исследована возможность применения способа «перестановки ограничений» для задания начального приближения целевой функции.
Ключевые слова: надстройка Excel «поиск решения»; задачи целочисленного программирования; альтернативные оптимальные решения; симплексный метод; метод обобщенного приведенного градиента; целевая функция; ограничения; начальное приближение целевой функции.
Ссылка для цитирования этой статьи:
Барышев А.В. Поиск оптимальных альтернативных решений с помощью Excel в задачах целочисленного программирования // Интернет-журнал «НАУКОВЕДЕНИЕ» Том 7, №4 (2015) http://naukovedenie.ru/PDF/60EVN415.pdf (доступ свободный). Загл. с экрана. Яз. рус., англ. DOI: 10.15862/60E VN415
Ранее мы рассмотрели вопрос использования надстройки поиск решения Excel (версии 2007-2013) для поиска альтернативных оптимальных решений в задачах линейного программирования [1]. В этой работе был предложен эвристический способ поиска альтернативных оптимальных решений, позволяющий выявить наибольшее число альтернатив и сократить время их поиска, при этом полученный результат может быть выражен дробными числами. Однако в некоторых задачах переменные, входящие в целевую функцию должны быть выражены целыми числами. Если произвести округление дробных чисел до ближайших целых значений, то полученное решение может стать неоптимальным или же выйти из области допустимых значений. В связи с этим в настоящей работе исследована возможность применения ранее предложенного нами способа «перестановки ограничений» [1] для поиска целочисленных оптимальных решений, поскольку наличие альтернатив является важным моментом для принятия управленческих решений. Однако, в литературе, например [2-10], описываются либо теоретические аспекты поиска целочисленных оптимальных решений либо поиск безальтернативного целочисленного оптимального решения. Нахождение способа поиска альтернативных целочисленных оптимальных решений с помощью excel обусловливает актуальность и практическую ценность настоящей работы.
Как указывалось ранее, при использовании симплексного метода надстройки «поиск решения» excel в задачах линейного программирования в качестве признаков, указывающих на существование оптимальных альтернативных решений, является наличие в отчете по устойчивости нулей «в таблице «Изменяемые ячейки» в столбцах «Допустимое увеличение» и «Допустимое уменьшение»...» [2]. Можно предположить, что, если задача линейного программирования имеет альтернативные решения, то и задача целочисленного программирования может иметь альтернативные оптимальные решения. С этой целью исследуем вопрос применения способа «перестановки ограничений» для поиска целочисленных альтернативных оптимальных решений. Кроме того, для выявления особенностей решения рассмотрим примеры, представленные в статье [1].
Пример 1. Найти максимум целевой функции
= Xi + Х2 + Хз
(1)
при ограничениях
Xi + 3X2 + 2х3 < 6л 2х1 + 4х2 + 2х3 < 8 3х1 + 2х2 — х3 < 4 "
(2).
Xi, Х2, Х3 ^ 0
х1(х2,х3, целые }
Введем данные в таблицу Excel из примера 1 [1].
Д1
А В с О Е F G
1 перем лев часть знак прав часть
2 наименование XI Х2 ХЗ
3 коэф е цел.функции 1 1 1
4
5 коэф в 1 огранич -1 3 2 =СУММПРОИЗВ(В5:05;810:01 < 6
6 к о эф' в 2 огранич 2 4 2 =CyMMflPOM3B(B6:D6;81CI;Dl < S
7 коэф- Б 3 огранич 3 2 -1 =СУММЛРОИЗВ{В7:07;В1СШ1 < 4
8
9 Xl" х2' ХЗ' F(x)
10 оптим значение 0 0 0 =CyMMnPOH3B(B3:D3;B10: max
Рисунок 1. Фрагмент листа Excel, заполненный данными из формул (1) и (2) [1] Переходим к использованию «надстройки поиск решения».
Через меню «данные» и «поиск решения» входим в окно «параметры поиска решения». Задаем абсолютный адрес ячейки целевой функции, вид оптимума, диапазон изменяемых ячеек (ограничиваем целыми значениями), задаем ограничения и метод решения задачи (см. рис. 2).
Рисунок 2. Окно «параметры поиска решения» (получено автором)
В целях проведения исследования будем использовать как симплексный метод, так и метод ОПТ. Далее с помощью кнопки «параметры» переходим в одноименное окно (см. рис.
3).
Здесь мы имеем возможность задать точность ограничения, использовать автоматическое масштабирование, показывать результаты итераций, максимальное число решений, число итераций и др.
Например, установим параметры поиска:
1. Точность ограничения - 0.001.
2. Максимальное время (в секундах) - 100.
3. Число итераций - 100.
4. Максимальное число допустимых решений (установим, например) - 4.
В процессе исследования решения будем варьировать методами решения задачи и использованием автоматического масштабирования.
Поиск решения осуществляется после задания начальных значений нажатием кнопки «найти решение» окна «параметры поиска решения».
Исследуем возможности надстройки «поиск решения» для получения целочисленных оптимальных альтернативных решений. С этой целью будем задавать начальное приближение целевой функции способом «перестановки ограничений», а также будем изменять сам метод поиска. Результаты поместим в таблицы 1 и 2.
Рисунок 3. Окно параметры поиска решения MS Excel (получено автором)
Таблица 1
Поиск альтернативных решений целевой функции (1) симплексным методом
(составлено автором)
Метод решения Способ задание начального приближения Начальное приближение Значение целевой функции Значение переменных
Симплексный метод решения Способ перестановки ограничений с масштабированием и без него Х1,Х2,Х3= 0 4.00 xi= 2.00; х2=0.00; хз= 2.00
Х1 = 6; х2 = 8; хз= 4 4.00 xi= 2.00; х2=0.00; хз= 2.00
xi = 4; х2 = 6; хз= 8 4.00 xi= 2.00; х2=0.00; хз= 2.00
xi = 8; х2 = 4; хз= 6 4.00 xi= 2.00; х2=0.00; хз= 2.00
Как следует из данных таблицы 1, сочетание симплексного метода решения и предлагаемого способа задания начальных приближений не обеспечивает поиск альтернативных целочисленных оптимальных решений, а применение масштабирования не влияет на конечный результат.
Изменим симплексный метод решения на метод обобщенного приведенного градиента (ОПТ). Результаты поместим в таблицу 2.
Интернет-журнал «НАУКОВЕДЕНИЕ» Том 7, №4 (июль - август 2015)
http://naukovedenie.ru publishing@naukovedenie.ru
Таблица 2
Поиск целочисленных альтернативных решений целевой функции (1) методом ОПГ
(составлено автором)
Метод решения Способ задания начального приближения Начальное приближение Значение целевой функции Значение переменных
Метод решения нелинейных задач методом ОПГ «перестановки ограничений» (без масштабирования) Х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
xi = 4; х2 = 6; хз= 8 4.00 Х1= 1.00; х2= 0.00; хз= 3.00
xi = 8; х2 = 4; хз= 6 4.00 Х1= 1.00; х2= 0.00; хз= 3.00
«перестановки ограничений» (с масштабированием) Х1,х2,хз= 0 4.00 Х1= 2.00; х2=0.00; хз= 2.00
xi = 6; х2 = 8; хз= 4 4.00 Х1= 1.00; х2= 0.00; хз= 3.00
Х1 = 4; х2 = 6; хз= 8 4.00 Х1= 2.00; х2=0.00; х3= 2.00
Х1 = 8; х2 = 4; хз= 6 4.00 Х1= 1.00; х2= 0.00; х3= 3.00
Как следует из данных таблицы 2, предлагаемый способ задания начальных приближений обеспечил поиск 2 целочисленных оптимальных альтернативных решений, независимо от использования автоматического масштабирования.
Пример 2. Найти максимум целевой функции [1]:
Fx = х1 + 2х2 + 3х3 (3)
Ограничения:
Xi + 2x2 + 3х3 < 10л х1 + х2 < 5
х1 < 1 I (4)
Xi, Х2, ^ 0
х1(х2,хз, целые >
Подобно примеру 1 вводим данные выражений (3) и (4) в таблицу Excel (рис. 4).
А1 * Л
А В с 0 Е Р С
1 перем* лее часть знак прав часть
2 наименование XI х2 ХЗ
3 коэф в цел,функции 1 2 3
4
5 коэф в 1 ог ранич 1 2 з =СУММПРОИЗВ{В5:05;В10:01 < 10
6 коэф в 2 огранич 1 2 0 =СУММПРОИЗВ{В6:06;В10:Ш < 5
7 коэф в 3 огранич 1 0 0 =СУММПРОИЗВ{В7:07;В10:[>1 < 1
3
9 х1,х2,хЗ хГ х2' хЗ' Р(х) > 0
10 оптим значение 0 0 0 =СУММПРОИ36(ВЗ:03;В10: шах
Рисунок 4. Фрагмент листа Ехсе1, заполненный данными (3) и (4) [1]
Произведем поиск целочисленных альтернативных оптимальных решений, используя метод решения нелинейных задач ОПГ и предлагаемый способ задания начальных приближений, при этом будем варьировать использованием автоматического масштабирования. Результаты поиска поместим в таблицу 3.
Таблица 3
Поиск целочисленных альтернативных решений целевой функции (3) методом ОПГ
(составлено автором)
Метод решения Способ задания начального приближения Начальное приближение Значение целевой функции Значение переменных
Метод решения нелинейных задач методом ОПГ «перестановки ограничений» (без масштабирования) Х1,Х2,Х3= 0 10.00 Х1= 0.00; х2= 2.00; хз= 2.00
Х1 = 10; Х2 = 5; хз = 1 10.00 Х1= 0.00; х2= 2.00; хз= 2.00
Х1 = 1; Х2 = 10; хз = 5 10.00 Х1= 0.00; х2= 2.00; хз= 2.00
Х1 = 5; Х2 = 1; хз= 10 10.00 Х1= 0.00; х2= 2.00; хз= 2.00
«перестановки ограничений» (с масштабированием) Х1,Х2,Хз= 0 10.00 Х1= 0.00; х2= 2.00; хз= 2.00
Х1 = 10; Х2 = 5; хз = 1 10.00 Х1= 0.00; х2= 2.00; хз= 2.00
Х1 = 1; Х2 = 10; хз = 5 10.00 Х1= 0.00; х2= 2.00; хз= 2.00
Х1 = 5; х2 = 1; хз= 10 10.00 Х1= 1.00; х2= 0.00; хз= з.00
Как видим из данных таблицы 3, альтернативные оптимальные решения найдены только при использовании автоматического масштабирования, при этом мы избежали случайного поиска начальных значений.
На основании изложенного материала можно сделать следующие выводы: 8
1. Начинать поиск альтернативных целочисленных решений необходимо с решения задачи линейного программирования с помощью симплексного метода надстройки Excel «поиск решения», получив при этом отчет об устойчивости. Если в отчете по устойчивости «в таблице «Изменяемые ячейки» в столбцах «Допустимое увеличение» и «Допустимое уменьшение»...» есть нули, то необходимо искать целочисленные альтернативные оптимальные решения.
2. Поиск целочисленных альтернативных оптимальных решений можно производить методом обобщенного приведенного градиента (ОПГ) с помощью способа «перестановки ограничений». Это позволит не использовать случайный поиск.
3. Наиболее надежен поиск целочисленных альтернативных оптимальных решений с использованием автоматического масштабирования.
ЛИТЕРАТУРА
1. Барышев А.В., Федотова Е.Л. К вопросу использования надстройки Excel «поиск решения» в задачах линейного программирования // Интернет-журнал «НАУКОВЕДЕНИЕ» Том 7, №2 (2015) http://naukovedenie.ru/PDF/54TVN315.pdf (доступ свободный). Загл. с экрана. Яз. рус., англ. DOI: 10.15862/54TVN315.
2. Минько А.А. Принятие решений с помощью Excel. Просто как дважды два. -М.: Эксмо, 2007. - 240 с.
3. Агальцов В.П. Математические методы в программировании: учебник. - М.: ИД «ФОРУМ», 2010. - 240 с.
4. Исследование операций в экономике: учебник для академического бакалавриата / Н.Ш. Кремер, Б.А. Путко, И.М. Тришин, М.Н. Фридман; под. ред. Н.Ш. Кремера. - М.: Издательство Юрайт. 2014. - 438 с.
5. Экономико-математические методы и модели: учебник для бакалавров/п, В.Н. Сотников; под. ред. проф. А.М. Попова. - М.: Издательство Юрайт. 2011. - 479 с.
6. Созонов С.В. Разработка моделей оптимизации производственной программы промышленного предприятия на основе формулирования целевых функций / Экономические науки. 2010. Т. 67. №6. С. 231-235.
7. Колоколов А.А., Заозерская Л.А. Построение и анализ оценок числа итераций для алгоритмов целочисленного программирования с использованием метода регулярных разбиений / Известия вузов. Математика. 2014, №1, с. 41-54.
8. Логинов В.Н. Управленческие решения: модели и методы: Учебное пособие. -М.: Издательство «Альфа-Пресс», 2011. - 184 с.
9. Методы оптимизации: практикум / Б.В. Соболь, Б.Ч. Месхи, Г.И. Каныгин. -Ростов н/Д: Феникс, 2009. - 380 с.
10. Леоненков А.В. Решение задач оптимизации в среде MS Excel. - СПб.: БХВ -Петербург, 2005. - 704 с.
Рецензент: Ольховиков Леонид Александрович, доцент, кандидат военных наук, НОУ «Московский финансово-юридический университет».
Barushev Alexsandr Vladimirowich
NOU VPO «Russian new University» Russia, Moscow E-mail: awb49@yandex.ru
Finding optimal solutions for linear programming decision problems in whole numbers 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. Some of decision making problems variables in the objective function which must be expressed in whole numbers.
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 in whole numbers 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 in whole numbers. However not all of its techniques are equal when finding an optimal solution for a decision problem in whole. 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 in whole numbers using Microsoft Excel (versions 2007-2013) Solver Add-in and to evaluate the ability of using «constraints swapping» methods for initializing the 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 in whole number and evaluates the ability of using «constraints swapping» for initializing the objective function.
Keywords: Microsoft Excel Solver; linear programming problems; alternative optimal solutions; simplex method; generalized reduced gradient method; objective function.
REFERENCES
1. Baryshev A.V., Fedotova E.L. K voprosu ispol'zovaniya nadstroyki Excel «poisk resheniya» v zadachakh lineynogo programmirovaniya // Internet-zhurnal «NAUKOVEDENIE» Tom 7, №2 (2015) http://naukovedenie.ru/PDF/54TVN315.pdf (dostup svobodnyy). Zagl. s ekrana. Yaz. rus., angl. DOI: 10.15862/54TVN315.
2. Min'ko A.A. Prinyatie resheniy s pomoshch'yu Excel. Prosto kak dvazhdy dva. - M.: Eksmo, 2007. - 240 s.
3. Agal'tsov V.P. Matematicheskie metody v programmirovanii: uchebnik. - M.: ID «FORUM», 2010. - 240 s.
4. Issledovanie operatsiy v ekonomike: uchebnik dlya akademicheskogo bakalavriata / N.Sh. Kremer, B.A. Putko, I.M. Trishin, M.N. Fridman; pod. red. N.Sh. Kremera. -M.: Izdatel'stvo Yurayt. 2014. - 438 s.
5. Ekonomiko-matematicheskie metody i modeli: uchebnik dlya bakalavrov/p, V.N. Sotnikov; pod. red. prof. A.M. Popova. - M.: Izdatel'stvo Yurayt. 2011. - 479 s.
6. Sozonov S.V. Razrabotka modeley optimizatsii proizvodstvennoy programmy promyshlennogo predpriyatiya na osnove formulirovaniya tselevykh funktsiy / Ekonomicheskie nauki. 2010. T. 67. №6. S. 231-235.
7. Kolokolov A.A., Zaozerskaya L.A. Postroenie i analiz otsenok chisla iteratsiy dlya algoritmov tselochislennogo programmirovaniya s ispol'zovaniem metoda regulyarnykh razbieniy / Izvestiya vuzov. Matematika. 2014, №1, c. 41-54.
8. Loginov V.N. Upravlencheskie resheniya: modeli i metody: Uchebnoe posobie. - M.: Izdatel'stvo «Alfa-Press», 2011. - 184 s.
9. Metody optimizatsii: praktikum / B.V. Sobol', B.Ch. Meskhi, G.I. Kanygin. - Rostov n/D: Feniks, 2009. - 380 s.
10. Leonenkov A.V. Reshenie zadach optimizatsii v srede MS Excel. - SPb.: BKhV -Peterburg, 2005. - 704 s.
