Задания для самостоятельной работы
Федеральное государственное образовательное бюджетное учреждение
Высшего образования
«ФИНАН
«ФИНАНСОВЫЙ УНИВЕРСИТЕТ ПРИ ПРАВИТЕЛЬСТВЕ
РОССИЙСКОЙ ФЕДЕРАЦИИ»
Департамент анализа данных, принятия решений и финансовых технологий
И.Х.Утакаева
Решение задач линейного программирования (надстройка «Поиск решения» MicrosoftExcel)
Учебное пособие для проведения
семинара по компьютерному практикуму
Для бакалавров направления 38.03.01 «Экономика»
Электронное издание
Москва 2017
Введение
В общем случае задача линейного программирования может быть записана в виде:
(1)
(2)
(3)
Данная запись означает: найти экстремум целевой функции (1) и соответствующие ему переменные 
удовлетворяющие системе ограничений (2) и условиям неотрицательности(3).
Для решения задачи линейного программирования средствами MicrosoftExcel, необходимо выполнить следующие действия:
1. создать экранную форму для ввода условия задачи: переменных, целевой функции (ЦФ), ограничений, граничных условий;
2. ввести исходные данные в экранную форму: коэффициенты ЦФ, коэффициенты при переменных в ограничениях, правые части ограничений;
3. ввести зависимости из математической модели в экранную форму: формулу для расчета ЦФ,формулы для расчета значений левых частей ограничений;задать ЦФ (в окне "Поиск решения"):ячейку с целевой функцией и направление оптимизации ЦФ;
|
|
|
4. ввести ограничения и граничные условия (в окне "Поиск решения"):ячейки со значениями переменных, граничные условия длядопустимых значений переменных, соотношениямежду правыми и левыми частями ограничений;
5. установить параметры решения задачи (в окне "Поиск решения");
6. запустить задачу на решение (в окне "Поиск решения");
7. выбрать формат вывода решения (в окне "Результаты поиска решения").
|
Необходимо создать экранную форму для ввода условия задачи.
Рисунок 1
В представленной формекаждой переменной и каждомукоэффициенту задачи поставленаставится в соответствие ячейкаMicrosoftExcel:
ü переменным задачи 
соответствуют ячейки D14,E14,F14,G14;
ü коэффициентам ЦФ 
соответствуют ячейки D22,E22,F22,G22;
ü коэффициентам системы ограничений задачи соответствуют ячейки D19, E19, F19, G19, D20, E20, F20, G20, D21, E21, F21, G21;
ü правым частям системы ограничений соответствуют ячейки I30,I31, I32.
Далее осуществляется ввод исходных данных в экранную форму: коэффициенты ЦФ, коэффициенты при переменных в ограничениях, правые части ограничений.
|
|
|
Рисунок 2 
В ячейку D26, в которой будет отображаться значение ЦФ, необходимо ввести формулу 
=5 
, по которой это значение будет рассчитано.
Используя обозначения соответствующих ячеек в Excel, формулу для расчета ЦФ можно записать как сумму произведений каждой из ячеек, отведенных для переменных задачи (D14,E14, F14, G14), на соответствующие ячейки, отведенные для коэффициентов ЦФ (D22, E22, F22, G22) следующим образом:
ü поместитькурсор в ячейку D26;
ü нажать кнопку «fх» и вызвать окно «Вставка функции»;
ü выбрать в поле «Категория» категорию «Математические»;
ü в поле «Выберите функцию» выбрать функцию «СУММПРОИЗВ» и нажать кнопку «ОК»;
ü в появившемся окне «СУММПРОИЗВ», в строку «Массив 1» ввести выражение D$14:G$14, а в строку «Массив 2» – выражение D22:G22.
После ввода ячеек в строки «Массив 1» и «Массив» в окне «СУММПРОИЗВ» появятся числовые значения введенных массивов (см. рис.3), а в экранной форме в ячейке D26 появится текущее значение, вычисленное по введенной формуле, то есть 0.
Символ «$» перед номером строки означает, что при копировании этой формулы в другие места листа Excel номер строки 14 не изменится.Символ «:» означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B10:E10 указывает на ячейки B10, C10, D10 и E10). После этого в целевой ячейке появится 0 (нулевое значение).
|
|
|
Рисунок 3
Левые части ограничений задачи представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (D14, E14, F14, G14),на соответствующие ячейки, отведенные для коэффициентов конкретного ограничения:
ü D19, E19, F19, G19 – ограничение 1;
ü B20, C20, D20, E20 – ограничение 2;
ü B21, C22, D23, E24 – ограничение 3.
Формулы, соответствующие левым частям ограничений, представлены в таблице.
Таблица 1
| Левая часть ограничения | Формула Excel |
| 3 х1+2 х2+х3+х4 | =СУММПРОИЗВ(D19:G19;$D$14:$G$14) |
| 4 х1+5 х2+2х3+2х4 | =СУММПРОИЗВ(D20:G20;$D$14:$G$14) |
| 5х1+х2+3х3+2х4 | =СУММПРОИЗВ(D21:G21;$D$14:$G$14) |
Формулы, задающие левые части ограничений задачи, отличаются друг от друга и от формулы в целевой ячейке D26 только номером строки в первом массиве. Номер строки второго массива определяется той строкой, в которой ограничение записано в экранной форме. Поэтому для задания зависимостей для левых частей ограничений достаточно скопировать формулу из целевой ячейки в ячейки левых частей ограничений.
|
|
|
Рисунок 4
Для дальнейших этапов решения задачи необходимо подключитьнадстройкуMicrosoftExcel «Поиск решения», согласно инструкции ниже.
1. В меню «Сервис» выберите «Надстройки».
2. В поле «Доступные надстройки» установите флажок «Поиск решения» и нажмите кнопку «ОК». Если надстройка «Поиск решения» отсутствует в списке поля «Доступные надстройки»нажмите кнопку «Обзор», чтобы найти ее. Если появится сообщение о том, что надстройка «Поиск решения» не установлена на компьютере, нажмите кнопку «Да», чтобы установить ее.
3. После загрузки надстройки «Поиск решения» на вкладке «Данные» становится доступна кнопка «Поиск решения».
После завершения настройкинеобходимо вызвать «Поиск решения» и совершить следующие действия:
ü поставить курсор в поле «Оптимизировать целевую функцию»;
ü ввести адрес целевой ячейки $D$26 или сделать одно нажатие левой клавиши мыши на целевую ячейку в экранной форме – это будет равносильно вводу адреса с клавиатуры;
ü введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке «Максимум».
В окно "Поиск решения" в поле «Изменяя ячейки переменных» впишите адреса $D$14:$G$14. Необходимые адреса можно вносить в поле «Изменяя ячейки» и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.
Рисунок 5
В поле «В соответствие с ограничениями» выбираем кнопку «Добавить»,после чего появится окно «Добавление ограничения».
Рисунок 6
В поле «Ссылка на ячейку» введите адрес ячейки системы ограничений $D$30. Это можно сделать как с клавиатуры, так и путем выделения мышью всех ячеек переменных непосредственно в экранной форме.В поле знака откройте список предлагаемых знаков и выберите знак «<=». В поле «Ограничение» ввести адрес ячейки $I$30.
Рисунок 7
Аналогично следует ввести ограничения $D$31<=$I$31 и $D$32<=$I$32, после чего подтвердите ввод всех перечисленных выше условий нажатием кнопки «OK».
Окно «Поиск решения» после ввода всех параметров задачи представлено ниже. Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, используя кнопки «Изменить» или «Удалить».
Рисунок 8
В условии задачи имеются ограничения 
необходимо в «Параметрах поиска решений» установить флажок «Сделать переменные без ограничения неотрицательными».
После ввода всего условия задачи следует запустить «Поиск решения» на решение нажатием кнопки «Найти решение». В появившемся окне «Результаты поиска решения» следует выбрать «Сохранить найденное решение» и нажать «ОК».
Рисунок 9
После выполнения всех описанных действий в экранной форме появятся оптимальное решение задачи и оптимальное значение целевой функции.
Рисунок 10
Замечание. В случае, когда условие задачи предполагает целочисленность переменных, необходимо нажать кнопку «Добавить» в окне «Поиск решения» и в появившемся окне указать:
ü в поле «Ссылка на ячейку» адрес ячеек переменных задачи, с целочисленным требованием;
ü в поле ввода знака ограничения «целое»;
ü подтвердить ввод ограничения нажатием кнопки "OK".
Рисунок 11
Задание 2. Предприятию требуется выбрать 5 бизнес-партнеров для заключения контракта на поставку товаров на сумму до 5 млн. руб., определив объем сделки с каждым из партнеров и обеспечив максимальную прибыль с учетом того, что ожидаемая сумма рисков от сделок не превысит суммы ожидаемой прибыли. Значения прибылиti, рискаri 
и максимальной суммы сделкиmi с каждым из партнеров приведены в таблице.
Таблица 2
| Бизнес-партнёры | |||||
| Параметр | ООО«Вектор» | ОАО «Луч» | ИП «Семенов» | ООО «Феникс» | ООО «Восток» |
| mi, руб | 1 000 000 | 1 450 000 | 500 000 | 1 320 000 | 1 780 000 |
| ti, % | 15,5 | 12,2 | 10,6 | 16 | 11,1 |
| ri,% | 13,2 | 7,34 | 10,34 | 8,4 | 10,3 |
Построим математическую модель задачи.
Пусть 
– сумма сделки с i-м партнером, S – общая возможная сумма сделки, тогда целевая функция–сумма произведений прибыли для каждого бизнес-партнера прибыль, запишется следующим образом:
.
Ограничения:
Необходимо создать экранную форму для ввода условия задачи:
ü в ячейки В31:F33 введены данные из таблицы из условия задачи;
ü значения ячеек В37:F37 соответствуют переменным , 
;
ü значения ячеек В38:F38 отражают нижнее ограничение из модели задачи 
;
ü в ячейке В42 размещена целевая функция задачи;
ü в ячейке В44 указана общая возможная сумма сделки;
ü ячейки С48:Е48 соответствуют ограничению 
;
ü ячейки С49:Е49 соответствуют ограничению 
Рисунок 12
Формулы, соответствующие левым частям ограничений и целевой функции, представлены в таблице.
Таблица 3
| Ячейка | Формуламатематической модели | Формула Excel |
| В42 | 
| СУММПРОИЗВ(B37:F37;B32:F32)/100 |
| С48 | 
| СУММ(B37:F37) |
| С49 | 
| СУММПРОИЗВ(B33:F33;B37:F37) |
| E49 | 
| СУММПРОИЗВ(B32:F32;B37:F37) |
После заполнения экранной формы, заполняется «Параметры поиска решения».
Рисунок 13
После запуска «Поиска решения» в экранной форме появится оптимальное решение задачи.
Рисунок 14
Задания для самостоятельной работы
1. Для откорма животных используется три вида комбикорма: А, В и С. Каждому животному в сутки требуется не менее 800 г. жиров, 700 г. белков и 900 г. углеводов. Содержание в 1 кг каждого вида комбикорма жиров белков и углеводов (граммы) приведено в таблице:
Таблица 4
| Содержаниев 1 кг. | Комбикорм | ||
| А | В | С | |
| Жиры | 100+10a | 200 | 300 |
| Белки | 170 | 100+10a | 110 |
| Углеводы | 380 | 400 | 100+10a |
| Стоимость 1 кг | 31 | 23 | 20 |
Сколько килограммов каждого вида комбикорма нужно каждому животному, чтобы полученная смесь имела минимальную стоимость? Составить математическую модель ЗЛП и решить ее, провести анализ решения.
2. Молочный завода производит молоко, кефир и сметану, расфасованные в бутылки. На производство 1 т молока, кефира и сметаны требуется соответственно 1000+a, 1000+a и 9400+aкг молока. При этом затраты рабочего времени при разливе 1 т молока и кефира составляют 0,18 и 0,19 машино-часов. На расфасовке 1 т сметаны заняты специальные автоматы в течение 3,25 часов. Всего для производства цельномолочной продукции завод может использовать 136000 кг молока. Основное оборудование может быть занято в течение 21,4 машино-часов, а автоматы по расфасовке сметаны – в течение 16,25 часов. Прибыль от реализации 1 т молока, кефира и сметаны соответственно равна 30, 22+a и 136 руб. Завод должен ежедневно производить не менее 100-a т молока, расфасованного в бутылки. На производство другой продукции не имеется никаких ограничений.
Требуется определить, какую продукцию и в каком количестве следует ежедневно изготовлять заводу, чтобы прибыль от ее реализации была максимальной.
3. На швейной фабрике ткань может быть раскроена несколькими способами для изготовления нужных деталей швейных изделий. Пусть при 1-м варианте раскроя 100 м2 ткани изготовляется 6 деталей 1-го вида, 8 деталей 2-го вида, 16 деталей 3-го вида, а величина отходов при данном варианте раскроя равна 3м2. При 2-м варианте раскроя 100м2 ткани изготовляется 4 деталей 1-го вида, 10 деталей 2-го вида, 8 деталей 3-го вида, а величина отходов при данном варианте раскроя равна 5м2. При 3-м варианте раскроя 100м2 ткани изготовляется 9 деталей 1-го вида, 8 деталей 2-го вида, 6 деталей 3-го вида, а величина отходов при данном варианте раскроя равна 2+а м2. Зная, что деталей 1-го вида следует изготовлять 160+а штук, деталей 2-го вида следует изготовлять 110+а штук, деталей 3-го вида следует изготовлять 180+а штук, требуется раскроить ткань так, чтобы было получено необходимое количество деталей каждого вида при минимальных общих отходах.
Дата добавления: 2018-05-12; просмотров: 771; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!