Читайте также:
|
Цель работы: научиться использовать табличный процессор Excel для решения задач оптимизации.
Содержание работы:
1.Создание формы для ввода условий задачи, ввод в неё исходных
данных и зависимостей согласно математической модели задачи.
2.Ввод данных из формы в окно Excel «Поиск решения» из меню «Сервис».
3.Задание параметров поиска и решение задачи.
Задача линейного программирования (ЗЛП) в общем случае формулируется следующим образом:
Определить максимум (минимум) целевой функции F max(min) при заданной системе ограничений (2) и граничных условий (3):
Fmax(min) =A1*X1+A2*X2+...+An*Xn (1)
{B11*X1+B12*X2+...+B1n*Xn≤C1
{B21*X1+B22*X2+...+B2n*Xn≤C2
.............................................................. (2)
{Bn1*X1+Bn2*X2+...+Bnn*Xn≤Cn
Xi>=0, i=1...n (3)
Рассмотрим применение процессора Excel для решения ЗЛП на примере.
Задача. Малое предприятия выпускает товары Х1, Х2, Х3, Х4, получая от реализации единицы каждого прибыль в 60,70,120,130 руб. соответственно. Затраты на производство приведены в таблице. Определить:
1) Максимум прибыли в зависимости от оптимального распределения затрат.
2) Минимум ресурсов, необходимых для получения максимальной прибыли.
| Затраты | X1 X2 X3 X4 | Всего |
| Трудовые | 1 1 1 1 | 16 |
| Сырьевые | 6 5 4 1 | 110 |
| Финансы | 4 6 10 13 | 100 |
Составим математическую модель процесса по описанию задачи:
60Х1+70Х2+120Х3+130Х4 = Fmax – целевая функция прибыли.
{Х1+Х2+Х3+Х4 ≤ 16
{6Х1+5Х2+4Х3+Х4 ≤ 110 – ограничения модели
{4Х1+6Х2+10Х3+13Х4 ≤ 100
Хj ≥0 – граничные условия модели
Решение задачи средствами Excel состоит из 3 этапов:
1.Создание формы для ввода условий задачи, ввод в неё исходных данных и зависимостей из математической модели.
2.Ввод данных из формы в окно Excel Поиск решения из меню Сервис.
3.Задание параметров поиска и решение задачи.
1 этап. Создание формы
а) Составление формы в виде:
А B C D E F G H
1 Переменная Х1 Х2 Х3 Х4 Формула Знак Св. член
2 Значение
3 Нули
4 Коэф. ЦФ 60 70 120 130 СП(В2:Е2)(В4:Е4) max
5 Трудовые 1 1 1 1 СП(В2:Е2)(В5:Е5) 16
6 Сырьевые 6 5 4 1 СП(В2:Е2)(В6:Е6) 110
7 Финансы 4 6 10 13 СП(В2:Е2)(В7:Е7) 100
б) Запись в ячейки В4:Е4 коэффициентов целевой функции F (1), в В5:Е7 коэффициентов из системы ограничений (2) и в ячейки Н5:Н7 - свободных членов из системы (2).
в) Ввод формул с помощью процедуры |f| - Мастер функций.
Для целевой функции: щелкнуть левой клавишей мыши по ячейке F4, за
тем по значку Мастера функций |f| на панели инструментов, в появившемся окне "Мастер функций, Шаг 1" в левой части выбрать категорию "Математические", в правой части – функцию СУММПРОИЗВ, нажать клавишу Далее, в окне "Мастер функций, Шаг 2" в поле Массив 1 ввести с клавиатуры В2:Е2 (ячейки, в которых будут варьироваться Х1..Х4), в поле Массив 2 ввести В4:Е4 (коэффициенты целевой функции ЦФ).
Примечание. Можно вводить В2:Е2 не с клавиатуры, а поставить курсор в окно Массив 1, а затем протащить курсор при нажатой левой клавише мыши
по ячейкам В2:Е2, имена ячеек сами запишутся в окно. Аналогично поступитьс полем Массив 2.
Нажать клавишу Готово, в ячейку F4 запишется формула 60*Х1+70*Х2+120*Х3+ 130*Х4 в виде СУММПРОИЗВ(В2:Е2)(В4:Е4)
Для левых частей ограничений аналогично:
- в ячейку F5 вносим СУММПРОИЗВ(В2:Е2)(В5:Е5),
- в ячейку F6 вносим СУММПРОИЗВ(В2:Е2)(В6:Е6),
- в ячейку F7 вносим СУММПРОИЗВ(В2:Е2)(В7:Е7).
Примечание. Чтобы каждый раз для новой ячейки F5..F7 не вызывать Мастер функций |f|, можно скопировать в буфер команду из F4 СУММПРОИЗВ(B$2:E$2)(B4:E4) кнопкой на панели инструментов Копировать в буфер или командой из пункта меню Правка, затем вставить в выделенную ячейку F5..F7 эту команду с помощью кнопки Вставить из буфера или соответствующей команды из пункта меню Правка, при этом ячейки B$2:E$2 не изменятся, а В4:Е4 поменяются на В5:Е5, В6:Е6 и В7:Е7, т.к. символ абсолютной адресации строк $ в них не введён.
2 этап. Заполнение окна Поиск решения
Выбрать в пункте меню Сервис команду Поиск решения, поставить курсор в поле целевой функции, выделить ячейку F4 в форме(или ввести F4 с клавиатуры), поставить точку в кружок строки "Максимальному значению".
В поле "Изменяя ячейки" ввести В2:Е2 (с клавиатуры или протащив мышью).
Нажать клавишу "Добавить", в окне "добавление ограничения в поле "Ссылка на ячейку" ввести F5, выбрать через "стрелка вниз" знак "<=", в поле справа ввести Н5.
Аналогично через "Добавить" ввести F6<=H6, F7<=H7 для системы ограничений B2>=B3,C2>=C3,D2>=D3 и Е2>=E3 для граничных условий Хi>=0.
После ввода последнего граничного условия вместо "Добавить" нажать клавишу ОК, появится окно "Поиск решения".
Для изменения или удаления ограничений и граничных условий используются клавиши Изменить.., Удалить.
3 этап. Параметры поиска
В окне "Поиск решения" нажать клавишу "Параметры", выбрать по умолчанию Максимальное время - 100 с.(может быть до 2^15=32767 c.> 4 час.), число итераций- 100(для большинства задач это количество просчётов подходит с большим запасом), установить флажок "птичка" в строке "Линейная модель", нажать ОК, в появившемся окне Поиск Решения нажать Выполнить, появится окно:
Результаты поиска решения с таблицей результатов:
А В С D E F G H
1 Переменная Х1 Х2 Х3 Х4 Формула Знак Св. член
2 Значение 10 0 6 0
3 Нули
4 Коэф. ЦФ 60 70 120 130 1320 max
5 Трудовые 1 1 1 1 16 <= 16
6 Сырьевые 6 5 4 1 84 <= 110
7 Финансы 4 6 10 13 100 <= 100
т.е. оптимальный план Х(Х1,Х2,Х3,Х4)=(10,0,6,0)
при минимальном использовании ресурсов
-Трудовые - 16 (У1)
-Сырьевые - 84 (У2)
-Финансы - 100 (У3)
даёт максимум прибыли F в 1320 руб.
Вывод: Максимальная прибыль F в 1320 руб. получается при выпуске
только товаров Х1 и Х3 в количестве 10 и 6 штук соответственно, товары Х3 и Х4 выпускать не нужно (это приведёт к снижению прибыли).Трудовые (У1) и финансовые (У3) ресурсы используются полностью, по сырьевым ресурсам (У2) есть запас в 110-84=26 ед.
Кроме того, это означает, что изменение трудовых (У1) и финансовых (У3) ресурсов приведёт к изменению прибыли F, а изменение сырьевых ресурсов (У2) - нет.
Разности между плановыми ресурсами и использованными являются двойственными переменными У1,У2 и У3 сопряжённой задачи линейного программирования. В данном случае У1=У3=0, а У2=26 ед. Таким образом, ресурс У2 можно уменьшить на 26 ед., тогда план по сырью тоже будет оптимальным.
Контрольные вопросы
1.Сформулировать основную задачу линейного программирования. Записать математическую модель ЗЛП.
2.Основные этапы решения ЗЛП с помощью процессора Excel.
3.Способы ввода формул математической модели ЗЛП в форму
4.Ввод ограничений и граничных условий математической модели в форму
5.Ввод параметров поиска решения в процессор Excel.
Задание
1. Составить собственную математическую модель задачи линейного программирования, прибавив к каждому коэффициенту рассмотренного примера свой номер № из списка группы в журнале преподавателя, т.е.
Аi=Ai+№, Вij=Bij+№, Ci=Ci+№.
2. Рассчитать максимальную прибыль и оптимальный план выпуска товаров для её достижения. Определить минимально необходимое количество ресурсов.
3. Выполнить то же, увеличив все ресурсы в 2 раза, сравнить результаты пунктов 2 и 3.
4. Для производства двух видов продукции А и В можно использовать сырье трех видов. При этом на изготовление единицы продукции вида А расходуется а1 кг сырья первого вида, а2 кг сырья второго вида и а3 кг сырья третьего вида. На изготовление единицы продукции вида В расходуется в1 кг сырья первого вида, в2 кг сырья второго вида и в3 кг сырья третьего вида.
На складе имеется всего сырья первого вида с1 кг, сырья второго вида с2 кг и третьего вида с3 кг. От реализации единицы готовой продукции вида А предприятие имеет прибыль x тыс.руб., от реализации единицы готовой продукции вида В прибыль составляет j тыс.руб. Определить максимальную прибыль от реализации всей продукции видов А и В.
Исходные данные для решения задачи 4:
1. a1 =20, a2=15, a3=14; b1=28, b2=9, b3=1;
c1=758, c2=526, c3=541; x=10, j=2.
2. a1=15, a2=15, a3=9; b1=33, b2=25, b3=3;
c1=571, c2=577, c3=445; x=8, j=6;
3. a1=11, a2=13, a3=13; b1=21, b2=15, b3=3
c1=741,c2=741,c3=822; x=5,j=3
4. a1=14,a2=12,a3=8;b1=8,b2=4,b3=2
c1=624,c2=541,c3=376,x=7, j=3
5. a1=19,a2=16,a3=19;b1=26,b2=17,b3=8
c1=868,c2=635,c3=853;x=5, j=4
6. a1=14,a2=15,a3=20;b1=40,b2=27,b3=4
c1=1200, c2=993,c3=1094;x=5, j=5
7. a1=9,a2=15,a3=15;b1=27,b2=15,b3=3
c1=606,c2=802,c3=840;x=11, j=6
8. a1=13,a2=13,a3=11;b1=23,b2=11,b3=1
c1=608,c2=614,c3=575;x=5, j=7
9. a1=8,a2=14,a3=14;b1=7,b2=8, b3=1
c1=417, c2=580, c3=591; x=5, j=5
10. a1=19, a2=16, a3=19; b1=31, b2=9, b3=1
c1=1121, c2=706, c3=1066; x=16, j=19.
Дата добавления: 2015-08-03; просмотров: 126 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Задача 25. | | | ФОРМА И РАЗМЕРЫ ЗЕМЛИ |