Пример 16.

В табл.6.24 приведены характеристики топлива. Необходимо определить минимальную цену на 1 тонну смеси сортов топлива при соблюдении качества через ограничение содержания примесей: P1 не более 0, 03% и P2 не более 3, 25%.

Таблица 6.24

Исходные данные к примеру 16

Целевая функция примера имеет вид (минимизация затрат на приобретение 1 тонны топлива):

300* A +300* B +450* C = min,

Ограничения можно записать в следующем виде (по качеству топлива):

0, 06* A +0, 04* B +0, 02 C ≤ 0, 03,

2* A +4* B +3* C ≤ 3, 25,

A + B + C =1,

A, B, C ≥ 0.

Сформулируем теперь эти условия в ячейках Excel. Часть такой информации приведена в табл.6.25.

Таблица 6.25

Формирование информации в ячейках Excel для выполнения расчетов по примеру 16

В ячейки D11, D12, D13 заносится количество топлива каждого сорта. Можно начать с любой цифры, например, 0 или занести в эти ячейки исходную цифру 1.

В ячейку D14 вносится формула = D11+D12+D13.

В ячейку D8 вносится формула стоимости 1 тонны топлива = 300*D11+300*D12+450*D13.

В ячейку D9 вносится формула ограничения содержания примеси P1 = 0, 06*D11+0, 04*D12+0, 02D13.

В ячейку D10 вносится формула ограничения содержания примеси P2 = 2*D11+4*D12+3*D13.

После внесения всех формул выполняется действие «Сервис/Поиск решения…». В открывшемся окне заполняются окна ввода информации, как показано на рис.6.15.

Рис.6.15. Заполнение информацией окна «Поиск решения» для примера 16

После заполнения информацией окна «Поиск решения» щелкнуть по кнопке «Параметры» и в открывшемся окне установить переключатель «Неотрицательные значения». Тем самым обеспечим последнее ограничение на искомые параметры (вес топлива не может быть отрицательным).

После щелчка по кнопке ОК выполняется вычисление, результат которого приведен в табл.6.26.

Таблица 6.26

Результаты расчетов для примера 16

После анализа полученного результата можно принимать решение по закупке топлива разного качества в заданном объеме. В данном случае от общего объема закупок 8, 3% должно составлять топливо А, 33, 3% - топливо В и 58, 3% - топливо С. При этом стоимость одной тонны смеси топлива составит 387, 5 р.

По желанию пользователя может быть организован отчет по результатам расчета. Для этого после того, как будет выполнен расчет, в появившемся окне в разделе «Тип отчета» нужно выделить слово «Результаты» и щелкнуть ОК (рис.6.16).

Рис.6.16. Организация отчета по результату поиска решения

Ниже приводятся другие примеры из разных областей деятельности для самостоятельной реализации.