CC BY
8
Вестник КРАУНЦ. Физ.-мат. науки. 2017. № 1(17). C. 68-81. ISSN 2079-6641
DOI: 10.18454/2079-6641-2017-17-1-68-81 УЧЕБНО-МЕТОДИЧЕСКИЕ МАТЕРИАЛЫ
УДК 517.9
ПРИМЕНЕНИЕ ТАБЛИЧНОГО ПРОЦЕССОРА ПРИ РЕШЕНИИ ЗАДАЧ ОПТИМИЗАЦИИ
О. В. Кудринская
Камчатский государственный университет имени Витуса Беринга, 683032, г. Петропавловск-Камчатский, ул. Пограничная, 4 E-mail: prohlada@inbox.ru
В статье рассматриваются методы решения задач оптимизации с применением инструментов табличного процессора - «Поиск решения» и «Диспетчер сценариев». Представлены решения нескольких примеров линейной и целочисленной оптимизации. Задачи оптимизации широко применяются в науке, экономике, технике и используются при необходимости получения наилучших результатов при определенных условиях.
Ключевые слова: оптимизация, линейная оптимизация, целочисленная оптимизация, поиск решения
© Кудринская О. В., 2017 EDUCATIONAL-METHODICAL MATERIALS
MSC 34A08
APPLICATION OF THE TABLECTIVE PROCESSOR WHILE SOLVING THE OPTIMIZATION PROBLEMS
O. B. Kudrinskaya
Vitus Bering Kamchatka State University, 683032, Petropavlovsk-Kamchatsky, Pogranichnaya st., 4, Russia E-mail: prohlada@inbox.ru
The article discusses methods for solving optimization problems with the use of table processor tools - "Solution search"and "Script manager". Solutions of several examples of linear and integer optimization are presented. The tasks of optimization are widely used in science, economics, engineering and are used when it is necessary to obtain the best results under certain conditions.
Key words: optimization, linear optimization, integer optimization, solution search.
©Kudrinskaya O. B., 2017
Введение
Программа Microsot Excel позволяет решать финансовые, экономические, математические и оптимизационные задачи и находить эффективные решения в разных областях.
Оптимизационные методы используются в экономической и технической области. Их основная функция - это подбор сбалансированного решения, оптимального в определенных условиях (например, нахождение количества продаж для получения максимальной выручки, определение минимального числа рейсов или поездок и т.п.).
В табличном процессоре Microsot Excel для решения задач оптимизации используются следующие инструменты: подбор параметров - определяет значения, которые обеспечат необходимый результат; поиск решения - находит оптимальную величину при учете переменных и определенных условий (ограничений); диспетчер сценариев
- проводит анализ нескольких вариантов исходных значений, создает и оценивает наборы сценариев [3].
Рассмотрим линейную и целочисленную оптимизацию в примерах.
Линейная оптимизация
Пример 1. Определение оптимального количества продукции (значения не целые). Компания производит два типа (A и B) изделий. Для изделия типа A требуется сырье
- 9 литров, а для изделия типа B требуется сырье - 12 литров. Количество сырья, которое получает компания от поставщиков в неделю - до 5100 литров. Для каждой продукции типа A требуется 12 мин. машинного времени, а для продукции типа B
- 42 мин. В неделю можно использовать 160 часов машинного времени. Сколько продукции каждого типа необходимо выпускать компании в неделю, если каждая продукция типа A приносит 200 рублей прибыли, а каждая продукция модели В приносит 300 рублей прибыли? [2].
Таблица 1
Модель продукции Расход сырья на единицу продукции (литров) Затраченное машинное время единицу продукции (мин) Прибыль (руб)
A 9 12 200
B 12 42 300
Решение. Составление математической модели.
Обозначим через х - количество продукции модели А, созданной в течение недели, у - количество продукции модели В, созданной в течение недели.
Общая прибыль от продукции двух моделей равна 200х + 300у рублей. Эту прибыль необходимо максимизировать.
Функция, для которой ищется экстремум (максимум или минимум) - это целевая функция. На увеличение количества продукции влияют ограничения.
В данном примере ограниченно количество сырья в неделю, поступающее от поставщиков, поэтому получаем неравенство 9х + 12у <= 5100.
Ограниченно машинное время на изготовление продукции, так например, на продукцию модели А тратится - 0,2 часа, модели В тратится - 0,7 часа. Получаем неравенство 0,2x+0,7y<=160. Количество продукции - неотрицательное число.
Формально данная задача оптимизации записывается таким образом: [2]
{ 200x + 300y ^ max 9x + 12y < 5100 0,2x + 0,7y < 160
x > 0, y > 0
Оформим решение задачи в Excel. Введем данные на рабочий лист так, как показано на рис. 1.
А в с D
1 Переменные
г Продукция(тип А) 0,00 X
г Продукция (тип 0) огоо V
4
s Целевая функций
б Прибыль 0 =200*к+300жу
7
S Ограничения
3 Сырье о <=5100
10 Время изготовления 0 -0,2"к+0,7*у <=160
Рис. 1. Оформление примера
Искомые значения переменных х,у будут располагаться в ячейках В2 и В3, целевая функция - в ячейке В6. В ячейках В6, В9,В10 - формулы.
Запускаем инструмент Поиск решения и устанавливаем целевую и изменяемые ячейки, а также вводим ограничения (рис. 2).
Рис. 2. Поиск решения
Щелкните кнопку «Параметры» и установите два параметра (рис. 3): Линейная модель (так как ограничения и целевая функция являются линейными по переменным х и у). Неотрицательные значения (для переменных х и у).
Параметры поиска решения
Мачсямэпьнюе ереня;
100
секунд
Предельное число итераций: 1 ОС
Отммнтвльийя погрешность; QjCöMül Допусттое отклонение: 5 Сходимость: 0,0001
%
Ш
CK
Отмена
Загрузить п&дмь.м
Сохранить иолель-
Справка
0 Линейная модель Ц] Автоматическое насштабирование
0 ^Неотрицательное значения! П Гкжаеьвать цезультаты итераций Оценки Разности Метод поиска
0 лнн^ная Ф прямые 0 [¿ьютона
О О ^еитрчльи^е О СОАрйжеимых грйдиеиттОЧ
Рис. 3. Параметры поиска решения
Далее щелкните по кнопке «ОК» и окажитесь в исходном окне, нажмите кнопку «Выполнить». Появится окно «Результаты поиска решения». В нем сообщение «Решение найдено. Все ограничения и условия оптимальности выполнены» (рис. 4).
Результаты поиска решений
Рис. 4. Настройка результатов поиска решения
На выбор предлагаются 2 варианта: «Сохранить найденное решение» или «Восстановить исходные значения». Выбираем первый вариант. В данном окне также можно выбрать тип отчета: по результатам, по устойчивости, по пределам. Выделите все типы отчетов и нажмите кнопку «ОК». В результате значения в таблице поменяются. В ячейках В2 и В3 появятся оптимальные значения (рис. 5).
А в С О
1 Переменные
1 Продукция (тип Л) 423,« 1
з Продукция (тип В) 107,69 V
л
5 Целевая функция
6 Прибыль 116923,1 =200жя+300*у
7
8 Ограничения
Э Сырье 5100 =Э*м+1Гу <■=5100
10 аремй изготовления 160 =0,2*к+0,7*у <=160
i
Рис. 5. Оптимальные значения
Также появляются три новых листа с отчетами: отчет по результатам (рис. 6), отчет по устойчивости (рис. 7), отчет по пределам (рис. 8).
ab с D Е f G
i 2 i 4 Miífowfi Excel IÍ.OOTHCI пор^улылзм Рабочий лнл:|Кннгэ1.1Ьх]/1нсИ отчет имдан: 03,<м,2017 }Д7;12
5 6 целевая ячейка (максимум)
7 яи^йил имя Им одно* »ич<енне
3 0 116523,07169
9 10 11 шмандемые ячейки
12 Лцрйкл Имя Исходное значение № хул ыл
13 продутая (тип 0.00 423,05
Ш Продукция (тип в) п,м 107,69
15 IS 17 Ограничения
13 Ячейку Ими Эм^ч-снни Формула Стагуе Рашнца
19 сырье 5100 3fl39<=51qo связанное 0
20 Бремя изготовления leo SaSlO*=lW [дяиииое 0
21 №2 Продукция (тип А) *l¿i,03 ЗВ52>=0 нечвямн. moa
22 продутая (тип в) Ю7гбэ saS3>=o 107,и
ЗЯ
Рис. 6. Отчет по результатам
AB С 0 £ F й н
L 2 3 4 Microsoft Excel 12.0 СИ чет по устойчивости Рабочий лип; |Книгз1,а1ы|Лнгт: Огчет созданг03,04.М17 1;07;12
6 изменяемые ячейки
Т f Peiyл»т. Ячейка Имя значение Ниднмкр. стоимость Целеюн Коэффициент дбг[)(111но( УГТРЛичеми^ Дипуиимое Уменьшение
9 5051 Продукции [тап А) 42Ш АЛО 200 2S ШДВ7143
10 SBS3 Продукций [Тип 8) 107,69 0,00 ¿00 цязззш
IL Ii Ограничения
13 1Д Регулкт. Ячейка Имя значение Цена Ограничение Дапустимое Правая часть Увеличение Допустимое Уменьшение
15 $В$9 Сырае 5100 2 ¡¡.51282051 5100 2100
1« SeilO Вреиа иэг&то»лени» 160 1« 137.5 «,6№56667
IT
Рис. 7. Отчет по устойчивости
А В с D Е F G Н i J
I Microsoft Ексе! 12.0 Отчет по пределаи
2 Рабочий лист: |кинта1*1',х10гчрт го продолам i
3 5 Отчет пндан: 03.04.2017 3:07:12
6 Целевое
3 Ячейка Имя Значение
В $ess Прибыло 116923,0769
9 10
11 Изменяемое Нижний Целевой Верхний Целевой
1? Ячейка Имя Значенне предел pciyoun предел результат
13 $В$2 продукция(тип А] 423,0В 0,00 32307,69 423,CS 115923,03
14 5в$з Продукция { тип В) 0,00 Sdol5r3S 107,69 115923,0В
Рис. 8. Отчет по пределам
Пример 2. Определение наибольшего и наименьшего содержания вещества в смеси. Имеются три смеси. Содержание веществ в каждой смеси представлено в табл. 2.
Таблица 2
Смеси Вещество 1 Вещество 2 Вещество 3
Смесь 1 70% 30%
Смесь 2 80% 20%
Смесь 3 50% 10% 40%
Их данных трех смесей необходимо создать новую смесь, в которой вещества 215%. Какое наибольшее и наименьшее процентное содержание вещества 1 должно быть в новой смеси? [2].
Решение. Пусть а - количество первой смеси, Ь - количество второй смеси, с-количество третьей смеси, взятые для изготовления новой смеси. Так как в смеси
должно быть 15% вещества 2, получаем уравнение.
0.3a + 0b + 0.1c
a + b + c
Количество вещества 1 в новой смеси:
0.7 a + 0.8 b + 0.5c a + b + c
= 0.15.
= 0.15
Для данной функции трех неотрицательных переменных необходимо найти наибольшее и наименьшее значения. Перейдем к новым переменным.
abc
X =-;-, У =-;-, Z =
a + b + c a + b + c a + b + c Ограничения:
0.3x + 0.1z - 0.15 = 0,
и x + y + z — 1 = 0, при это переменные x,y,z - неотрицательные [2].
Оформим решение задачи в Excel. Введем данные на рабочий лист так, как показано на рис. 9.
До А В «-я-1 с
О о 1 К 0
2 V О
J Ï 1 0
4
5
ГЬч fi 1
7
Khi 8 ОЙ
№
№м Iii
Рис. 9. Оформление примера
Ячейка В8 имеет процентный формат. В ячейках В5, В6, В8 - формулы.
1 этап решения: Целевая ячейка - В8, максимальное значение. Искомые значения переменных х,у,г будут располагаться в ячейках В1, В2 и В3. Запускаем инструмент Поиск решения и устанавливаем целевую и изменяемые ячейки, а также вводим ограничения (рис. 10).
Щелкните кнопку «Параметры» и установите два параметра (Рисунок 11): Линейная модель (так как ограничения и целевая функция являются линейными по переменным х и у). Неотрицательные значения (для переменных х и у).
Далее щелкните по кнопке «ОК» и окажитесь в исходном окне, нажмите кнопку «Выполнить». Появится окно «Результаты поиска решения». В нем сообщение «Решение найдено. Все ограничения и условия оптимальности выполнены» (рис.12).
Нажимаем на кнопку «Сохранить сценарий» и вводим название сценария «Максимум» и нажимаем кнопку «ОК» (рис. 13).
Рис. 10. Поиск решения
Ларзметры поиска решения
ш
Максимальное еремя:
1С'0
оекунд
CK
Предельное число итераций: 100
Отмена
Относительная погрешность; OjGOODO]
Долусттое отклонена: [5_
Снодимость:
Загрузить (Ойшад
%
Сохранить подель-
O.OOOJ
Справка
0 Линейная модель Ц] Автоматическое наоштлбирование
0|Неотрицательи]ще зиаченнв! Ц Показывать цезультаты нтеращ* Оценки Разности Метод поиска
© личная © орчмые ® |^ьютона
О >1еьд|М(тичм^й О щиральныв О сопряжтьи градиеит»
Рис. 11. Параметры поиска решения
Рис. 12. Настройка результатов поиска решения
Появится окно «Результаты поиска решения». На выбор предлагаются 2 варианта: «Сохранить найденное решение» или «Восстановить исходные значения». Выбираем
Рис. 13. Окно «Сохранение сценария» второй вариант - «Восстановить исходные значения» и закрываем данное окно (рис.
14).
Рис. 14. Окно «Результаты поиска решения»
2 этап решения: Целевая ячейка - В8, минимальное значение.
Запускаем инструмент Поиск решения, параметры остаются те же, только устанавливаем для целевой ячейки - минимальное значение, нажимаем кнопку «Выполнить», далее кнопку «Сохранить сценарий» и вводим название сценария «Минимум» и нажимаем кнопку «ОК». (Рисунок 15)
Рис. 15. Окно «Сохранение сценария»
Далее перейдем в диспетчер сценариев (Данные - Работа с данными - Анализ «что-если»- Диспетчер сценариев) (рис. 16).
В окне «Диспетчер сценариев» перечислены сценарии текущего рабочего листа (максимум, минимум). Внизу окна указаны адреса изменяемых ячеек. Выделим в списке сценариев первый сценарий - максимум и нажмем кнопку «Вывести».
На листе изменились значения в таблице. Максимальное значение - 75%. Далее выберем сценарий-минимум и получим следующие результаты:
На листе изменились значения в таблице. Минимальное значение - 55%. Сценарий - это набор значений для изменяемых ячеек. У набора есть имя. Благодаря сценариям пользователь таблицы может хранить несколько вариантов расчетов и обращаться к ним когда необходимо через инструмент «Диспетчер сценариев».
Рис. 16. Окно «Диспетчер сценариев»
А В С
1 к 0,50
г V 0,50
3 г 0
4
5 ■ 1,4999 IE-13 =0,i*x+0,l"z-0,lS
б А, 5510 iE-12
7
3 75% =0,7-х+0г3'у+ф,5-г
Рис. 17. Результат сценария «Максимум»
А В С
1 к 0,25
2 У о,ос
з 1 0,7&
4
5 •6.3699Е14
б -4г801Д5Е-гг =xty+i-l
7
г 55%
9
Рис. 18. Результат сценария «Минимум»
Для того чтобы увидеть все сценарии одновременно можно создать отчет. Для этого необходимо зайти в диспетчер сценариев и нажать кнопку «Отчет». Появится окно «Отчет по сценарию». Выберем тип отчета «Структура».
Рис. 19. Окно «Отчет по сценарию» Появится новый рабочий лист «Структура сценария»:
М17 Ы
-— - ■
1 г ■ ■ tl
1|г А в с 0 £ F G н
1
2 Структура сценарии
+ Tirtvtunt значения: максимум
- 5 Изменяемые:
■ 6 SBS1 0.25 0,50 0,25
■ 7 №2 огоо 0,50 о.оо
■ Б 0r7S 0 0,7Ъ
- - 9 Результат:
- 10 SBS5 -1,49991 Е-11 -6,3SSSE-14
■ 11 SB56 АЭОЮ5Е-12 4,55шыг Ч801105Е-12
■ 12 ieis 55% 7Ь% 55«
13 Примечания: столбец "текущие значения" представляет анзчення изменяемых ячеек е
14 момент создании (Утчетз по Сценарию. Изменяемые ячейки ДЛЯ каждого
1Б сценария выделены серым цветом.
1Ь
—L.
Рис. 20. Отчет «Структура сценария»
Построим отчет по типу «Сводная таблица»:
А в С D
1 SBS1:$B$3 на (В«}
2 T
3 Ячейки результата
4 Названия строк $В$6
5 максимум -1,49991Е-13 4,5510ЭЕ-12 0,75
б минимум -S,3639E-14 -4,80105t-12 0.55
7
Рис. 21. Отчет «Сводная таблица»
Для того чтобы добавить в отчет по типу «Сводная таблица» значения ячеек В1:В3, при создании отчета по сценарию в ячейки результата необходимо добавить ячейки В1:В3. (рис. 22)
Рис. 22. Окно «Отчет по сценарию»
Сценарий «Сводная таблица» будет иметь следующий вид:
А В С D £ F G H
1 SB$1:$B$3 чз \ Все)
2
г 4 Ячейки результата Названия строк * ШМ Ш2 SB Я SBS5 sess SBS8
6 максимум минимум 0,25 0,5 0 0 0 ль -1Г4ЭЭЭ1Е-13 0,75 0.55
7
Я
Рис. 23. Отчет «Сводная таблица»
Целочисленная оптимизация
На практике часто встречаются изделия и продукция, которые нельзя реализовать частями. (например, шкаф, персонал, здание и др.). В таких случаях целесообразно применять целочисленную оптимизацию.
В качестве примера рассмотрим задачу из примера 1 с измененными параметрами. Введем ограничение, что х и у - это целые числа.
В результате оптимальные значения стали целыми, но эти значения не являются результатом округления в большую сторону значений х и у, полученных без ограничения целочисленности в примере 1. Линейная оптимизация.
Рис. 24. Настройка поиска решения с новым ограничением
А в С D
1 Переменные
2 Продукция (тип А) 424 X
3 Продукция (тип 8} 107 У
4
5 Целевая функция
6 Прибыль 116900 =200 * je+ЗОО * у
7
8 Ограничения
9 Сырье 5100 -9ж*+12'у <=5100
10 Бремя изготовления 15ЭГ7 =Q,2*x+0,7*y <=160
4 ■-.
Рис. 25. Оптимальные значения.
Заключение
Освоение инструментов «Поиск решений», «Подбор параметров», «Диспетчер сценариев» дает преимущество в решении экономических, финансовых и математических задач: минимизация расходов при формировании состава сырья, оптимизация количества продукции, минимизация расходов при формировании штатного расписания, оптимизация расходов на изготовление при выборе типа и количества продукции, максимизация прибыли при формировании инвестиционной программы и другие
Список литературы
[1] Задачи оптимизации. [Zadachi optimizacii http://citforum.ru/pp/excel72.shtml ].
[2] Лавренов С.М., Excel: Сборник примеров и задач, М., Финансы и статистика, 2006, 336 с. [Lavrenov S. M., Ehcel: Sbornik primerov i zadach, M., Finansy i statistika, 2006, 336 ].
[3] Поиск решения задач в Excel с примерами. [Poisk reshenija zadach v Excel s primerami http://exceltable.com/ vozmojnosti-excel/ poisk-resheniya-v-excel ].
[4] Решаем задачи оптимизации в Excel. [Reshaem zadachi optimizacii v Excel http://www.profiz.ru/peo/11_20l2/zadachi_optimizacii/ ].
[5] Целочисленная оптимизация http://www.math.mrsu.ru/text/courses/invest/2/2_2_32.html. [Celochislennaja optimizacija ].
Список литературы (ГОСТ)
[1] Задачи оптимизации. [Электронный ресурс]. - URL: http://citforum.ru/ pp/excel72.shtml
[2] Лавренов С.М. Excel: Сборник примеров и задач. - М.:Финансы и статистика, 2006, 336 с.
[3] Поиск решения задач в Excel с примерами. [Электронный ресурс]. - URL: http://exceltable.com/ vozmojnosti-excel/ poisk-resheniya-v-excel
[4] Решаем задачи оптимизации в Excel. [Электронный ресурс]. -URL:http://www.profiz.ru/peo/11_2012/zadachi_optimizacii/
[5] Целочисленная оптимизация. [Электронный ресурс]. - URL: http://www.math.mrsu.ru/text/courses/invest/2/2_2_32.html
Для цитирования: Кудринская О. В. Применение табличного процессора при решении задач оптимизации // Вестник КРАУНЦ. Физ.-мат. науки. 2017. № 1(17). C. 68-81. DOI: 10.18454/2079-6641-2017-17-1-68-81
For citation: Kudrinskaya O. B. Application of the tablective processor while solving the optimization problems, Vestnik KRAUNC. Fiz.-mat. nauki. 2017, 17: 1, 68-81. DOI: 10.18454/20796641-2017-17-1-68-81
Поступила в редакцию / Original article submitted: 04.04.2017
