Решение задач средствами Microsoft Exsel

Задача №3.

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

 

Ресурсы	Нормы затрат ресурсов на одно изделие		Общее количество ресурсов
	стол	шкаф
Древесина(м3):
1 вида	0,2	0,1	40
2 вида	0,1	0,3	60
Трудоемкость (человеко-часов)	1,2	1,5	371,4
Прибыль от реализации одного изделия (руб.)	8	6

 

Определить, сколько столов и шкафов фабрике следует изготовлять, чтобы прибыль от их реализации была максимальной.

Решение задачи:

В данном случае мебельной фабрике необходимо спланировать объем производства столов и шкафов так, чтобы максимизировать прибыль. Поэтому переменными являются: Х₁ - количество столов, Х₂ - количество шкафов

Суммарная прибыль от производства столов и  шкафов равна Z=8* Х₁+6* Х₂. Целью фабрики является определение среди всех допустимых значений Х₁ и Х₂ таких, которые максимизируют суммарную прибыль, т.е. целевую функцию Z

Ограничения, которые  налагаются на Х₁ и Х₂:

• объем производства шкафов и столов не может быть отрицательным, следовательно: Х₁³0, Х₂ ³ 0;
• нормы затрат древесины на столы и шкафы не может превосходить максимально возможный запас данного исходного продукта, следовательно:

0,2*Х₁+ 0,1*Х₂ £40

0,1*Х₁ +0,3*Х₂ £60

Кроме того, ограничение  на трудоемкость не превышает количества затрачиваемых ресурсов

1,2*Х₁+ 1,5*Х₂ £ 371,4

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

Максимизировать

Z = 8*Х₁ + 6*Х₂

при следующих ограничениях:

0,2*Х₁+ 0,1*Х₂ £40

0,1*Х₁ +0,3*Х₂ £60

1,2*Х₁+ 1,5*Х₂ £ 371,4

Данная  модель является линейной, т.к. целевая  функция и ограничения линейно  зависят от переменных.

Далее решим задачу с помощью MS Excel

Отведем ячейки A3 и ВЗ под значения переменных Х₁ и Х₂ (рис. 1).

Рис. 1. Диапазоны, отведенные под переменные, целевую функцию  и ограничения

 

В ячейку С4 ведем  функцию цели: =8*АЗ+6*ВЗ, в ячейки А7:А9 введем левые части ограничений:

 =0,2*А3+0,1*ВЗ

 =0,1*А3+0,3*ВЗ

 = 1,2*АЗ+1,5*ВЗ,

а в ячейки В7:В9 - правые части ограничений. (рис.1.)

Выберем команды Сервис/Поиск решения и заполним открывшееся диалоговое окно Поиск решения как показано на рис 2.

Для ввода ограничений нажмем кнопку Добавить.

 

 

 

 

 

 

 

 

 

Рис. 2. Диалоговое окно Поиск решения задачи о максимизации прибыли на фабрике

 

В диалоговом окне Параметры поиска решения установим флажок Линейная модель (Рис.3.).

Рис 3. Параметры поиска решения

После нажатия кнопки Выполнить открывается окно Результаты поиска решения, которое сообщает, что решение найдено (рис. 4).

Рис. 4. Результаты поиска решения

 

Результаты  расчета задачи представлены на рис. 5,  из которого видно, что оптимальным является производство 127 столов и 146 шкафов. Этот объем производства принесет фабрике 1892 руб. прибыли.

Рис. 5. Результаты расчета

Задача №7

Кондитерская  фабрика для производства трех видов карамели использует три вида сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья каждого вида на производство 1т карамели данного вида приведены в таблице. В ней же указано общее количество сырья каждого вида, которое может быть использовано фабрикой, а также приведена прибыль от реализации 1т карамели данного вида.

Вид сырья	Нормы расхода  сырья (т) на 1т карамели			Общее кол-во сырья
	А	В	С
Сахарный песок	0,8	0,5	0,6	800
Патока	0,4	0,4	0,3	600
Фруктовое пюро	-	0,1	0,1	120
Прибыль от реализации 1т продукции (руб.)	108	112	126

Найти план производства карамели, обеспечивающий максимальную прибыль от ее реализации.

Решение задачи:

Пусть Х₁ – карамель вида А (т.)

 Х₂ – карамель вида В (т.)

 Х₃ – карамель вида С (т.).

РА, РВ, РС –  прибыль от реализации

 Тогда система  ограничений и целевая функция  запишутся следующим образом:

РА*Х₁+РВ*Х₂+РС*Х₃ =>mах (целевая функция);

Х1*0,8+ Х2*0,5+ Х3*0,6<=800     ограничения на запасы сырья

Х1*0,4+ Х2*0,4+ Х3*0,3<=600     сахарный песок, патока,

Х2*0,1+ Х3*0,1<=120                    фруктовое пюре  

Х1>=0; Х2>=0; Х3>=0;

Х1, Х2, Х3- целые числа.

Далее решим задачу с помощью MS Excel.

Отведем ячейки С3, С4 и С5 под значения переменных Х₁, Х₂, и Х₃(рис. 1).

Рис. 1. Диапазоны, отведенные под переменные, целевую функцию  и ограничения

В ячейку С6 ведем функцию цели: =СУММ(С3:С5), в ячейки А10 : С10 введем левые части ограничений:

=0,8*В3+0,5*В4+0,6*В5

=0,4*В3+0,4*В4+0,3*В5

=0,1*В4+0,1*В5,

а в  ячейки С3, С4 и С5 введем левые части  ограничений (рис.1.):

=108*В3

=112*В4

=126*В5

Выберем команды Сервис/Поиск решения и заполним открывшееся диалоговое окно Поиск решения как показано на рис 2.

Для ввода ограничений нажмем кнопку Добавить.

 

 

 

 

 

Рис. 2. Диалоговое окно Поиск решения задачи о максимизации прибыли на фабрике

В диалоговом окне Параметры поиска решения установим флажок Линейная модель (Рис.3.).

Рис 3. Параметры  поиска решения

После нажатия кнопки Выполнить открывается окно Результаты поиска решения, которое сообщает, что решение найдено (рис. 4).

Рис. 4. Результаты поиска решения

Результаты  расчета задачи представлены на рис. 5,  из которого видно, что максимальную прибыль в количестве 162000 руб. можно получить производя сырья А в количестве 100 т и сырья С в количестве 1200 т и не произведя при этом сырья В.

Рис. 5. Результаты расчета

Задача №9