Пример 7.1

Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В - 4 м². Фирма может получать от своих поставщиков до 1700 м² досок в неделю. Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В - 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели А приносит 2 руб.. прибыли, а каждое изделие модели В - 4 руб.. прибыли?

Составим математическую модель. Обозначим за х - количество изделий модели А, выпускаемых в течение недели, у - количество изделий модели В. Прибыль от этих изделий равна 2х+4у руб. Эту прибыль нужно максимизировать. Беспредельному увеличению количества изделий препятствуют ограничения. Ограничено количество материала для полок, отсюда неравенство 3x+4y ≤ 700.

Ограничено машинное время на изготовление полок. На изделие А уходит 0.2 часа, на изделие В - 0.5 часа, а всего не более 160 ч, поэтому 0.2х + 0.5у ≤ 160. Кроме того, количество изделий - неотрицательное число, поэтому х ≥ 0; у ≥ 0.

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

Теперь решим эту задачу в Excel. Создадим новую рабочую книгу. Введем в ячейки рабочего листа информацию. В ячейках С6, С9 и С10 представлены формулы, приведенные в соответствующих ячейках столбца В (рисунок 38):

Рисунок 38 Создание таблицы для поиска решения

Выделим ячейку, в которой вычисляется целевая функция, и выполним команду Сервис-Поиск решения. В качестве ячейки с целевой функцией установим адрес $В$6 (рисунок 39). Включим переключатель Равной максимальному значению. Перейдем к полю ввода Изменяя ячейки. Достаточно щелкнуть кнопку Предположить и в поле ввода появится адрес блока $В$2: $В$3.

Рисунок 39 Определение параметров в окне Поиск решения

Для ввода ограничений нажмем кнопку Добавить. Установим следующие ограничения: $В$9< =1700, $В$10< =160. Нажмем кнопку Параметры и в диалоговом окне Параметры поиска решения установим два флажка: Линейная модель (так как ограничения и целевая функция являются линейными по переменным х и у) и Неотрицательные значения (для переменных х и у).

После нажатия кнопки Выполнить появляется диалоговое окно Результаты поиска решения. Выбираем в нем вариант Сохранить найденное решение. После этого вид таблицы меняется, в ячейках B2, B3 появятся оптимальные значения. Соответственно пересчитываются все формулы.

Пример 7.2 Предприятие выпускает 2 вида продукции. Цена единицы 1 вида продукции - 25000, 2 вида продукции – 50000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57, 6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице:

Требуется определить плановое количество выпускаемой продукции таким образом, чтобы стоимость произведенной продукции была максимальной

Такие задачи решаются при помощи инструмента Excel Поиск решения. Но для начала создадим математическую модель решения задачи.

Пусть продукция производится в количестве: 1-й вид – x₁ единиц, 2-й вид – x₂ единиц. Тогда стоимость произведенной продукции выражается целевой функцией f(x₁, x₂)=25000 x₁+50000x₂, для которой необходимо найти максимум.

При этом следует учесть ограничения по запасам сырья:

1, 2 x₁ +1, 9 x₂ £ 37,

2, 3 x ₁ +1, 8 x ₂ £ 57, 6,

0, 1 x ₁ +0, 7 x ₂ £ 7

и по смыслу задачи x₁, x₂ должны быть неотрицательными и целыми:

x₁ ³ 0, x₂ ³ 0.

После составления математической модели введем на рабочем листе Excel целевую функцию и ограничения.

Рисунок 40

Для переменных x₁ , x₂ определим соответственно ячейки С2: D2 (рисунок 40), и зададим им начальные значения, равные нулю. Затем коэффициенты целевой функции (цену 1 ед. каждого вида продукции) и нормы расхода сырья для каждого вида расположим под неизвестными в ячейках С3: D3 и С6: D8 соответственно. Запасы сырья расположим справа от матрицы норм расхода в ячейках G6: G8. В ячейке F2 вычислим значение целевой функции по формуле: = СУММПРОИЗВ(C2: D2; C3: D3), а в ячейках F6: F8 ‑ реальный расход сырья по формулам: = СУММПРОИЗВ($C$2: $D$2; C6: D6), = СУММПРОИЗВ($C$2: $D$2; C7: D7), = СУММПРОИЗВ($C$2: $D$2; C8: D8).

Затем определим параметры в окне Поиск решения, где укажем адрес ячейки, в которой находится формула, вычисляющая значение целевой функции – $F$2 и установим для нее переключатель Максимальное значение. В поле Изменяя ячейки установим $C$2: $D$2 (т.е. адреса ячеек, в которых находятся значения изменяемых переменных х₁, х _2). В качестве ограничений зададим условия: C$2$: $D$2=целое, C$2$: $D$2> =0, $F$6: $F$8< =$G$6: $G$8.