Решение задач математического моделирования в Excel

 

     2.2. Математическая запись модели.

Условные обозначения:

Х_j – переменные, количество выпускаемой продукции j-го вида;

j – номер переменной;

b_i – объем ограничений, количество i-го ресурса;

i – номер ограничения;

а_ij – технико-экономические коэффициенты, норма расхода i – го ресурса для выпуска единицы продукции j – го вида;

с_j – коэффициенты целевой функции, прибыль от производства единицы продукции j-того вида.

Математическая модель

F =   

 

x_j ≥ 0

j =     i =

 

     2.3. Развернутая экономико-математическая модель.

Система переменных:

х₁ – количество продукции №1

х₂ – количество продукции №2

х₃ – количество продукции №3

х₄ – количество продукции №4

 

Система ограничений:

1. по использованию трудовых ресурсов:

          х₁ + х₂ + х₃ + х₄ ≤ 16

2. по использованию сырья:

               6х₁ + 5х₂ + 4х₃ + 3х₄ ≤ 110

3. по использованию финансов:

               4х₁ + 6х₂ + 10х₃ + 13х₄ ≤ 100

      Целевая функция – максимум прибыли:

               F = 60х₁ + 70х₂ + 120х₃ + 130х₄         max

 

     Числовая модель

   F = 60х₁ + 70х₂ + 120х₃ + 130х₄         max

   х₁ + х₂ + х₃ + х₄ ≤ 16

   6х₁ + 5х₂ + 4х₃ + 3х₄ ≤ 110

   4х₁ + 6х₂ + 10х₃ + 13х₄ ≤ 100

   х_j ≥ 0,   j =

 

2.4. Алгоритм решения транспортной задачи.

1. Постановка задачи.

Составить оптимальный план перевозок минеральных удобрений  со складов на поля севооборота, чтобы  транспортные затраты были минимальными. Имеется три склада с удобрениями, где хранится 600 т. минеральных удобрений: 1 – 200 т., 2 – 150 т., 3 – 250 т. Потребность четырех полей севооборота в минеральных удобрениях 475 т.: 1 поле – 100 т., 2 – 150 т., 3 – 150 т., 4 – 75 т. Транспортные затраты на перевозку 1 тонны удобрений с каждого склада на каждое поле приведены в таблице 2.

 

2. Транспортные  издержки, ден. ед. за тонну.

Склады удобрений	Поля севооборота
	первое	второе	третье	четвертое
№1	4	2	3	1
№2	3	6	2	5
№3	6	3	4	2

 

Запасы удобрений больше, чем потребность в них на 125 тонн, модель открытая. Для решения  задачи в Excel не требуется приводить модель к закрытому типу.

2. Решение транспортной  задачи в Excel.

Заполнение  таблицы исходных данных в Excel

1. Заполняется первая часть таблицы, в которой все показатели будут вычисляться в процессе решения задачи (рис.1)

• поля севооборотов являются потребителями;

• склады – поставщики удобрений;
• в ячейках С5:F7 будут высвечиваться результаты перевозок – это изменяемые ячейки;
• в ячейки В5:В7 вводится формула сумма значений ячеек всей строки: в В5 =СУММ(С5:F5); в В6 =СУММ(С6:F6); в В7 =СУММ(С7:F7)
• в ячейки С8:F8 вводится формула – сумма значений ячеек всего столбца: в С8 =СУММ(С5:С7); в D8 =СУММ(D5:D7); в Е8 =СУММ(Е5:Е7); в F8 =СУММ(F5:F7).

Рис.1. Исходные данные в Excel.

 

2. Заполняется вторая часть таблицы (рис.1.)

• в строке 9 (ячейки С9:F9) вводится потребность в удобрениях каждого поля севооборота;

• в столбце В (ячейки В10:В12) вводится наличие удобрений на каждом складе;
• в ячейки С10:F12 – вводятся технико-экономические коэффициенты – транспортные расходы на перевозку удобрений соответствующего склада на соответствующее поле севооборота.

3. Заполняется последняя строка таблицы (рис.1)

в ячейки С13:F13 – вводится функция – сумма произведения массива значений на массив технико-экономических коэффициентов соответствующего столбца;

• в С13 =СУММПРОИЗВ(С5:С7;С10:С12)
• в D13 =СУММПРОИЗВ(D5:D7;D10:D12)
• в E13 =СУММПРОИЗВ(E5:E7;E10:E12)
• в F13 =СУММПРОИЗВ(F5:F7;F10:F12)

     в ячейку  В13 вводится Целевая функция =СУММ(С13:F13)

 

Заполнение  окна «Поиск решения»

• курсор устанавливается в Целевую ячейку (В13)
• В меню выбрать Сервис, Поиск решения
• Заполнить окно «Поиск решения» (рис.2):

• если в поле «Установить целевую ячейку» высвечивается  адрес, который не является адресом Целевой ячейки, то необходимо отодвинуть окно и щелкнуть мышью по целевой ячейке – в поле появится адрес целевой ячейки В13.
• установить переключатель в положение Равной минимальному значению.
• в поле «Изменяя ячейки» мышью ввести интервал ячеек строки значения из таблицы «Переменные» $С$5:$F$7.
• заполнить поле Ограничения
• щелкнуть мышью в поле ограничения
• щелкнуть мышью на кнопке Добавить
• заполнить окно Добавление ограничения:

• Первым всегда вводится Граничное условие – все переменные должны быть больше или равны нулю (рис.3)

• щелкнуть мышью в поле Ссылка на ячейку
• выделить мышью интервал ячеек $С$5:$F$7
• изменить знак, выбрав из списка >=
• щелкнуть мышью в поле Ограничения
• ввести с клавиатуры ноль
• нажать кнопку Добавить

• в новом окне Добавление ограничения ввести следующее ограничение – вывоз удобрений со складов не должен превышать из начилия на складах. (рис.4)

• щелкнуть мышью в поле Ссылка на ячейку
• выделить мышью интервал ячеек $B$5:$B$7
• оставить знак без изменения <=
• щелкнуть мышью в поле Ограничения
• выделить мышью интервал ячеек $B$10:$B$12
• нажать кнопку Добавить

Рис.2. Заполнение окна Поиск решения

 

Рис.3. Ввод условия  не отрицательности переменных

 

Рис.4. Ввод второго  ограничения

 

• В новом окне Добавление ограничение ввести следующее ограничение – потребность полей севооборотов в удобрениях должна быть удовлетворена полностью или с избытком (рис.5)

• щелкнуть мышью в поле Ссылка на ячейку
• выделить мышью интервал ячеек $C$8:$F$8
• изменить знак на противоположный >=
• щелкнуть мышью в поле Ограничения
• выделить мышью интервал ячеек $C$9:$F$9
• нажать кнопку ОК.

4. заполняется окно Параметры

• в окне Поиск решения нажать кнопку параметры
• в окне параметры установить флажку в положения Линейная модель и показывать результаты итераций
• нажать кнопку ОК

Рис.5. Ввод третьего ограничения

 

Получение оптимального решения и отчета по устойчивости

1. в окне Поиск решения нажать кнопку выполнить
2. на экране появиться окно Результаты поиска решения, в котором следует выбрать тип отчета Устойчивость и нажать кнопку ОК
3. на экране останется таблица-модель с оптимальным планом перевозок (рис.6), а перед листом модели будет создан лист Отчет по устойчивости (рис.7)
4. для решения задачи потребовалось 11 итераций

Рис.6. Модель с  оптимальным решением

 

3. Анализ  оптимального решения

По оптимальному плану  необходимо ( рис.6)

• С первого склада перевести сто т. удобрений на одно поле, 25 тонн- на второе поле и 75 т. на четвертое поле
• Со  второго склада – 150 т., т.е. все перевезти на 3 поле;
• С третьего склада-125 т., т.е. все перевезти на второе поле.

При этом на третьем складе останется 125т. удобрений. Потребность всех полей севооборота в удобрениях будет удовлетворена.

При таком плане перевозок  затраты на перевозку удобрений  со складов на поля севооборотов будут  минимальными и составят 1200 ден.ед.

Рис.7. Отчет по устойчивости «Ячейки переменных»

Рис.8. Отчет по устойчивости «Ограничения»

 

4.Анализ  устойчивости оптимального решения

По таблице Изменяемые ячейки ( рис.7)

Нормированная стоимость показывает, что если:

• со второго склада перевести 1т. удобрений  на 2 или 4 поле севооборота, то затраты увеличатся на 5 ден. ед.;
• с третьего перевезти 1т. удобрений на 1 поле севооборота, то затраты увеличатся на 1 ден.ед.

 

Допустимое  увеличение и уменьшение коэффициентов  целевой функции показывают, что оптимальный план не изменится, если затраты на перевозку будут находиться в пределах:

• с первого склада - на первое поле - от 0 до 4; на 2 поле – от 2 до 3; на 3 поле – 3; на 4 поле – 1 ден.ед.;
• со 2 склада – на 1 поле – от 0 и выше; на 2 поле – от 1 и выше; на 3 поле - от 0 до 2; на 4 поле – от 0 и выше ден.ед.;
• с 3 склада – на 1 поле – от 5 и выше; на 2 поле – от 2 до 3; на 3 поле – от 4 и выше; на 4 поле – от 2 и выше ден.ед

По таблице Ограничения (рис.8)

Теневая цена показывает, что:

• если количество удобрений увеличить на 1т.:
• на 1 складе, то затраты снизятся на 1 ден.ед.
• на 2 складе, то затраты снизятся на 2 ден.ед.
• на 3 складе, то затраты не изменятся
• если потребность в удобрениях увеличить на 1т.:
• на 1 поле, то затраты увеличатся на 5 ден.ед.
• на 2 поле, то затраты увеличатся на 3 ден.ед.
• на 3 поле, то затраты увеличатся на 4 ден.ед
• на 4 поле, то затраты увеличатся на 2 ден.ед

 

Допустимые увеличение и уменьшение объемов ограничений показывают, что план перевозок останется оптимальным, если:

• на складах количество удобрений будет находиться в пределах:
• на 1 складе – от 175 до 325 т.
• на 2 складе – от 125 до 150 т.
• на 3 складе – от 125 т. и выше.
• Потребность полей севооборота будет в пределах:
• 1 поля – от 0 до 125 т.
• 2 поля – от 25 до 275 т.
• 3 поля – от 150 до 175 т.
• 4 поля – от 0 до 100 т.