Определение оптимального плана выпуска продукции при заданных ресурсах.

Цель работы: Использование средств MS EXCEL для решения задач линейного программирования.

Содержание работы:

- ввод исходных данных (матриц коэффициентов),

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

- использование процедуры «Поиск решения« для нахождения экстремума целевой функции при заданных ограничениях.

Постановка задачи . Для производства изделий А, В, С используется четыре вида сырья. Каждый из видов сырья может бать использован в количестве.соответственно не большем 130, 80, 90, 70.

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

Вид сырья	Нормы затрат на единицу лродукции
	А	В	С
I	3	4	1
II	2	5	3
III	4	1	2
IV	5	6	4
Цена единицы продукции	10	8	12

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

Решение

Обозначим :

x_{1- количество изделий вида A ,}

x_{2 - количество изделий вида B ,}

x_{3 - количество изделий вида C .}

Тогда математическая модель данной задачи будет:

F= 10x₁ + 8x₂ + 12x₃→ max

Ограничения на каждое из видов сырья:

3х₁ + 4х₂ + х₃ ≤ 130

2х₁ + 5х₂ +3х₃ ≤ 80

4х₁ + х₂ + 2х₃ ≤ 90

5х₁ + 6х₂ +4 х₃ ≤ 70

Кроме того , должно быть: х_{1 ,} х₂ , х₃ ≥ 0.

Решение производим на компьютере в Excel. Вводим исходные данные, полагая начальные значения неизвестных равными нулю.

Записываем в F4 формулу для левой части первого ограничения, зафиксировав адреса ячеек с начальными значениями искомых переменных .

Вводим данную формулу ( Enter ) , протягиваем ее на ячейки F5-F7 и в ячейку G4 записываем формулу для остатка сырья 1-го вида

Вводим данную формулу ( Enter ) , протягиваем ее на ячейки G5-G7 и в ячейку G13 записываем формулу целевой функции

После введения формулы целевой функции получим

Вызываем процедуру «Поиск решения».Указываем, что целевая функция находится в ячейке G13 и что ее нужно максимизировать ,что изменяемые ячейки B13:D13. Так же указываем на неотрицательность переменных. Записываем ограничения:

F4 ≤ E4 , F5 ≤ E5 , F6 ≤ E6, F7 ≤ E7

(Если процедура «Поиск решения» отсутствует, то ее необходимо активизировать следующим образом: Файл, Параметры, Надстройки, Надстройки Excel, Перейти, выбрать Пакет анализа и Поиск решения, ОК)

Все записи вводятся щелчком на соответствующую ячейку.

Указываем на линейность модели и неотрицательность значений:

После выполнения вычислений получим:

Таким образом, для получения максимальной прибыли в размере 210 д.е. нужно произвести 17,5 изделий вида С, не производя изделий А и В. При этом, сырье 4-го вида используется полностью, т.е. является дефицитным.

Лабораторная работа № 2.