48488 (Работа с финансовыми функциями Excel), страница 3

Решение:

1. Формализация задачи.

Обозначим:

количество корма 1-го вида через x₁;

количество корма 2-го вида через x_2;

Тогда целевая функция - затраты на корм - равна:

z=50x₁+60x₂

Соотношение количества вещества А в дневном рационе не должно быть меньше 10 д. е., т.е.

2x₁+1x₂≥10

Соответственно для вещества В и вещества С

2x₁+3x₂≥12

0x₁+2x₂≥4

Так как x₁ и x₂ - количество продукта, то справедливо

x₁≥0

x₂≥0

Полученная математическая модель задачи о смесях:

z

{

=50x₁+60x₂ (min)

2x₁+1x₂≥10

2x₁+3x₂≥12

0x₁+2x₂≥4

x₁≥0

x₂≥0

2. Точное (алгебраическое) решение формализованной задачи.

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

Система уравнений I:

{	2x1+1x2≥10 [1]
	0x1+2x2≥4 [2]

из [2] x₂=2; тогда из [1] x₁=4,Система уравнений II:

{	2x1+3x2≥12 [3]
	0x1+2x2≥4 [4]

из [4] x₂=2; тогда из [3] x₁=3,Принимаем x₁=4, x₂=2, поскольку значение x₁=3 не удовлетворяет неравенство 2x₁+1x₂≥10

3. Графическое решение формализованной задачи.

Строим область, являющуюся пересечением всех плоскостей математической модели полученной при формализации задачи (см. черт.1).

Находим градиент функции z: grad z = {50; 60}. Строим вектор с началом в т. (0; 0) и концом в точке (50; 60). Определяем зону допустимых решений. Для этого строим линии ограничений, приравнивая между собой левые и правые части уравнений и определяя значения точек пересечения линий ограничения с осями Х1 и Х2, присваивая значения равные 0:

2x₁+1x₂=10; x₁=0, x₂=10/x₁=5, x₂=0, 2x₁+3x₂=12; x₁=0, x₂=4/x₁=6, x₂=0

0x₁+2x₂=4; x₂=2, x₁=0, x₂₌0

Строим прямую, перпендикулярную вектору градиента. Передвигаем эту прямую в направлении, указанном вектором. Самая последняя точка, которую пересекает прямая, и есть точка максимума.

Рисунок 1 - Графическое решение формализованной задачи

4. Решение задачи с помощью пакета Excel.

Для решения данной задачи линейного программирования в пакете Excel воспользуемся помощью пункта меню Сервис, пункт Поиск решения.

Прежде, чем воспользоваться этой программой, введем исходные данные:

1. В ячейки C3 и D3 вводим значения точки максимума соответственно.

2. Вводим коэффициенты целевой функции 50 и 60 в ячейки C6 и D6 соответственно.

3. В ячейку F6 вводим формулу для вычисления целевой функции. Для этого вызываем Вставка функции - "Математические" - СУММПРОИЗВ и вводим ячейки C$3: D$3 и C6: D6. Формат функции; =СУММПРОИЗВ (С$3: 0$3; С6: D6).

4. В ячейки C4: D4 вводим нижние границы равные 0. Нижняя граница показывает, что переменные не отрицательные.

5. Вводим коэффициенты системы ограничений в ячейки C10: D12.

6. Вводим правые части системы ограничений в ячейки Н10: Н12.

7. В ячейку F10 вводим формулу расчета выполнения ограничений =СУММПРОИЗВ (С$3: D$3; C10: DО). Копируем эту формулу в ячейки F11, F12.

8. В ячейку I10 вводим формулу расчета неиспользованных ресурсов =H10-F10. Копиру ем эту формулу в ячейки I11, I12

После ввода исходных данных вызываем программу Поиск решения из пункта меню Сервис.

В окно Поиска решения вводим значения в ячейках:

1. Вводим $F$6 в окно "Установить целевую ячейку", выставляем ее "Равной минимальному значению".

2. В окошко "Изменяя ячейки" вводим $C$3: $D$3.

3. В окошке "Ограничения" выбираем пункт "Добавить"

"Ссылка на ячейку" - СЗ, знак - >=, "Ограничение" - С4. Появляется ограничение:

$С$3>=$С$4. Аналогично вводим:

$D$3>=$D$4;

$F$10>=$H$10;

$F$11>=$H$11;

$F$12>=$H$12

4. После этого нажимаем "Выполнить", далее Тип отчета - "Результаты".

Получаем решение в ячейках СЗ и D3 - значения переменных, в ячейках F6 - значение целевой функции, в ячейках F10: F12 - значения ограничений к в ячейках I10: I12 - разницу между исходными ресурсами и использованными.

	A	B	C		D	E		F		G		H		I
1			Переменные
2			X1	X2
3		Значения	4	2
4		Ниж. граница	0	0
5		Верхн. граница
6		F	50	60					320		max
7		Коэффициенты целевой ф-ции
8
9			Коэф-ты						Значение				Факт. ресурсы	Неисп. ресурсы
10	Сис-ма ограничений		2	1					10		>=		10	0
11			2	3					14		>=		12	-2
12			0	2					4		>=		4	0

	A	B	C	D	E	F	G	H	I
1			Переменные
2			X1	X2
3		Значения	4	2
4		Ниж. граница	0	0
5		Верхн. граница
6		F	50	60		=СУММПРОИЗВ (C3: D3; C6: D6)	max
7		Коэффициенты целевой ф-ции
8
9			Коэф-ты			Значение		Факт. ресурсы	Неисп. ресурсы
10	Сис-ма огранич		2	1		=СУММПРОИЗВ (C3: D3; C10: D10)	>=	10	=H10-F10
11			2	3		=СУММПРОИЗВ (C3: D3; C11: D11)	>=	12	=H11-F11
12			0	2		=СУММПРОИЗВ (C3: D3; C12: D12)	>=	4	=H12-F12

Экономический вывод

Для минимизации затрат при ежедневном расходе необходимо включат в рацион 4 кг первого вида и 2 кг второго вида кормов. при этом в рацион необходимо вносить:

Вещества А - 10 ед. при фактическом 10 ед.

Вещества В - 14 ед. при фактическом 12 ед.

Вещества С - 4 ед. при фактическом 4 ед.

Вещество В является недостаточным