Работа с финансовыми функциями Excel (стр. 3 из 3)
Находим градиент функции z: grad z = {50; 60}. Строим вектор с началом в т. (0; 0) и концом в точке (50; 60). Определяем зону допустимых решений. Для этого строим линии ограничений, приравнивая между собой левые и правые части уравнений и определяя значения точек пересечения линий ограничения с осями Х1 и Х2, присваивая значения равные 0:
2x1+1x2=10; x1=0, x2=10/x1=5, x2=0, 2x1+3x2=12; x1=0, x2=4/x1=6, x2=0
0x1+2x2=4; x2=2, x1=0, x2=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 ед.
Вещество В является недостаточным