Решение задач оптимизации в среде Microsoft Excel

Федеральное агентство по образованию 

Государственное образовательное учреждение

Высшего профессионального образования 
 
 
 

Отчет по лабораторной работе 

На  тему: «Решение задач  оптимизации

  в среде Microsoft Excel» 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

2009 г. 
 
 
 
 
 
 
 
 
 

Задача 1 

Исходная постановка задачи: 

Для поддержания нормальной жизнедеятельности человеку ежедневно необходимо потреблять не менее 118 г белков,56 г. жиров, 500 г. Углеводов, 8 г. Минеральных солей. Количество питательных веществ, содержащихся в 1 кг каждого вида потребляемых продуктов, а так же цена за 1 кг каждого из этих продуктов приведены в следующей таблице: 

 

Составить дневной  рацион, содержащий не менее минимальной  суточной нормы потребности человека в необходимых питательных веществах при минимальной общей стоимости  потребляемых продуктов 

Формальная постановка задачи: 

Обозначим через x₁ количество потребляемого за сутки мяса за x₂, х₃, х₄, х₅, х₆, х₇,  соответственно рыбы, молока, масла, сыра, крупы, картофеля. Требуется найти наилучшие значения  x₁-х₇. Наилучшими для данной задачи являются такие значения, которые минимизируют общую стоимость потребляемых продуктов 

S=18 x₁ +10 х₂+2,8 х₃+34 х₄+29 х₅+5 х₆+1 х₇ → min 

Поскольку х_1-7 выражают количество потребляемых продуктов, то они не могут быть отрицательны, т.е. 

Х₁ ≥0, х₂ ≥0,  х₃ ≥0,  х₄≥0,  х₅ ≥0,  х₆≥0,  х₇≥0 

Ограничения на минимальную суточную норму могут  быть записаны следующим образом:

Х₁*180+Х₂*190+ Х₃*30+ Х₄*10+ Х₅*260+ Х₆*130+Х₇*21≥118

Х₁*20+Х₂*3+ Х₃*40+ Х₄*865+ Х₅*310+ Х₆*30+Х₇*2≥56

Х₁*0+Х₂*0+ Х₃*50+ Х₄*6+ Х₅*20+ Х₆*650+Х₇*200≥500

Х₁*9+Х₂*10+ Х₃*7+ Х₄*10+ Х₅*60+ Х₆*20+Х₇*10≥8 
 
 

Таким образом, задача состоит в том, что бы найти  значения Х, удовлетворяющим условиям: 

Х_1-7≥0

Х₁*180+Х₂*190+ Х₃*30+ Х₄*10+ Х₅*260+ Х₆*130+Х₇*21≥118

Х₁*20+Х₂*3+ Х₃*40+ Х₄*865+ Х₅*310+ Х₆*30+Х₇*2≥56

Х₁*0+Х₂*0+ Х₃*50+ Х₄*6+ Х₅*20+ Х₆*650+Х₇*200≥500

Х₁*9+Х₂*10+ Х₃*7+ Х₄*10+ Х₅*60+ Х₆*20+Х₇*10≥8 

И минимизирующие функцию S=18 x₁ +10 х₂+2,8 х₃+34 х₄+29 х₅+5 х₆+1* х₇ 

 

ячейки B19:H22- количество белков, жиров, углеводов, миниральных солей и цена.

 В ячейке  I22 находится функция минимизирующая цену.

В ячейках В15:Н15 изменяемые ячейки, те  значения, которые нужно найти

Таким образом, заполняем диалоговое окно Поиск решения: 

В поле установить целевую  указываем ячейку, содержащую оптимизируемое значение (I23), устанавливаем переключатель Равной минимальному значению.

В поле Изменяя ячейки задаем диапазон подбираемых параметров- В15:Н15

набор ограничений:

1. В15:Н15=целое

2. В15:Н15≥0

3. I19≥B10

4. I20≥B11

5. I21≥B12

6. I22≥B13 

 
 
 
 

Таким образом  наилучшими для данной задачи являются использовать 1 литр молока  и 1 кг крупы. При таком использовании мы будем получать не меньше минимума необходимых питательных веществ и цена при этом будет равна 7,8 рублей. 
 
 
 

Задача 2 

Исходная формулировка задачи 

Мясокомбинат  имеет в своем составе 4 завода, на каждом из которых может изготавливаться  три вида колбасных изделий. Мощности каждого из заводов соответственно равны 320, 280, 270 и 350. Ежедневные потребности в колбасных изделиях каждого вида составляют 450, 370 и 400 соответственно. В таблице приведена стоимость производства каждого вида колбасных изделий н каждом заводе. 

 

Необходимо определить наилучшее распределение выпуска  колбасных изделий между заводами 

Формальная постановка задачи

Константы 

1. обозначим через c_i-j  себестоимость производства колбасных изделий, где I- заводы 1-4, а j- разновидности колбасных изделий 

С=  
 

2. Пусть Z_i –мощности заводов, где i=1,2,3,4 .т.е.Z₁=320, Z₂=280, Z₃=270, Z₄=350

3. b_j- ежедневные потребности, j=1,2,3 т.е.

b₁=450- потребность в колбасе вареной

b₂=370- потребность в колбасе в/к

b₃=400- потребность в ветчине 

Переменные

1.Пусть х_ij- объем изготовления i-тым заводом j-той продукции

2.а_i- расход для i-того завода, где i=1,2,3,4.

3.S – суммарные расходы 

S=∑∑ 

Решение: 

1.Зададим математическую  модель расхода i-го завода 
 
 
 
 

2. 
 
 

3. 
 
 

Ограничения

Неизвестные должны удовлетворять следующим ограничениям:

1. неотрицательный объем производства, то есть х_ij≥0;
2. в силу сбалансированности задачи, вся произведенная продукция должна соответствовать мощностям заводов и потребности потребителей должны быть удовлетворены.

 
 

 

 
 

 

В результате получаем следующее распределение производства колбас между заводами:

 

Анализ  найденного решения: 

     Значение  целевой функции составило 3800 денежных единиц. При этом экономическая интерпретация результатов следующая. Завод 1 производит колбасу вареную и колбасу в/к соответственно 170 и 150 т., Завод 2- только колбасу вареную- 280 т., Завод 3- колбасу в/к и ветчину соответственно 220 и 50 т., Завод 4- ветчину-350 т. При этом, все заводы работают на полную мощность, а затраты на производство будут минимальными и составят 3800 денежных единиц.