Постановка задачи

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

Таблица 1

Вид продукции	Сырье	Машинное время	Рабочее время
П1	2	4	2
П2	3	3	2
П3	1	2	4

В расчете на один рабочий день имеются следующие ресурсы: сырье – 18 ед., машинное время – 12 ч., рабочее время – 24 ч. Единица продукции П1 стоит 16 ден. ед., продукции П2 – 20 ден. ед., продукции П3 – 18 ден. ед.

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

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

Формирование математической модели.

Обозначим через x₁ планируемый объем выпуска продукции П1,

x₂ ‑ планируемый объем выпуска продукции П2,

x₃ ‑ планируемый объем выпуска продукции П3.

Тогда искомая целевая функция Z, отражающая доход от реализованной продукции, будет иметь вид

Z(x₁, x₂, x₃) = 16 x₁ + 20 x₂ + 18 x₃;

потребность в сырье V –

V(x₁, x₂, x₃) = 2 x₁ + 3 x₂ + 1 x₃;

потребность в машинном времени Р –

P(x₁, x₂, x₃) = 4 x₁ + 3 x₂ + 2 x₃;

потребность в рабочем времени Q –

Q(x₁, x₂, x₃) = 2 x₁ + 2 x₂ + 4 x₃.

Учитывая ограничения по используемым ресурсам, математическую модель можно записать следующим образом:

16 x₁ + 20 x₂ + 18 x₃  max

при заданных ограничениях:

2  x₁ + 3 x₂ + 1 x₃ ≤ 18,

4 x₁ + 3 x₂ + 2 x₃ ≤ 12,

2 x₁ + 2 x₂ + 4 x₃ ≤ 24,

x₁ ≥ 0,

x₂ ≥ 0,

x₃ ≥ 0.

Ввод условий задачи.

Ввод условий задачи состоит из следующих шагов:

1. создание формы для ввода условий задачи;

2. ввод исходных данных;

3. ввод математической модели.

Задание 1.

1. Сформировать таблицу 2.

Таблица 2

2. В процессе расчетов в ячейки Е2:Е4 вводятся вычисляемые (фактические) значения правой части неравенств:

=СУММПРОИЗВ(B$8:D$8;B2:D2),

=СУММПРОИЗВ(B$8:D$8;B3:D3),

=СУММПРОИЗВ(B$8:D$8;B4:D4),

соответственно.

Аналогично, в ячейку Е7 (целевой функции) вводится формула

=СУММПРОИЗВ(B$8:D$8;B7:D7).

Поскольку результаты поиска (в ячейках В8:D8) еще не найдены, то в ячейках E2:E4 и E7 появятся нули (таблица 3).

Таблица 3

Задание 2. Выполнить поиск решения средствами Excel.

1. В случае отсутствия в меню Сервис опции Поиск решения необходимо подключить её, произведя следующую очерёдность действий: нажать меню Сервис → опцию Надстройки → активизировать строку Поиск решения  нажать кнопку OK.

2. Выбрать опцию Поиск решения меню Сервис.

При использовании инструмента Поиск решения необходимо:

‑ задать адрес ячейки, содержащей оптимизируемое значение, и указать ее искомое значение: максимальное, минимальное, либо равное какому-либо числу. Данная ячейка должна содержать формулу с адресами изменяемых ячеек (можно указать до 200 изменяемых ячеек, которые могут содержать как формулы, так и ссылки на блок, либо несмежные ячейки);

‑ указать диапазон изменяемых ячеек (диапазон ячеек, в которых будут выведены искомые значения);

‑ указать ограничения, которые устанавливаются с помощью кнопки Добавить;

‑ после ввода всех параметров нажать кнопку Выполнить.

В результате диалоговое окно «Поиск решения» должно быть заполнено так, как показано на нижеприведённом рисунке.

Тогда появятся выходные значения, приведенные в таблице 4.

Таблица 4

Таким образом, оптимальным планом выпуска будет отказ от выпуска Продукта 1 и Продукта 2 (ячейки В8 и С8 содержат 0), а объём выпуска Продукта 3 должен составлять 6 единиц. В этом случае прибыль достигнет своего максимального значения 108 ден.ед.

Полученные значения также позволяют сравнить предельные и фактические значения ресурсов:

сырье: 6 из 18 (остаток 12);

машинное время: 12 из 12 (израсходовано полностью);

рабочее время: 24 из 24 (израсходовано полностью).

Отчёт по полученным результатам можно просмотреть на листе «Отчет по результатам 1», фрагмент которого приведен в таблице 5.

Таблица 5