Решение задач ЛП средствами MS Excel.

⇐ ПредыдущаяСтр 5 из 5

Введем немного другую формулировку нашей задачи. Предположим что для изготовления модели и модели нужно сырье разных видов. Например, нам понадобится несколько видов сплавов: Сплав 1, Сплав 2, Сплав 3, и к примеру два вида крепежных механизмов. Тогда задачей ЛП станет определить количество заказываемых видов сырья на предприятии с механическим цехом, обеспечивающую максимальную выручку на основе заданных объемов ресурсов и нормативов затрат на первично обработанное сырье (требует дополнительную обработку) и вторично обработанное сырье (полуфабрикат), представленных в таблице 1. Условной выручкой будем считать процентное содержание сырья из общей стоимости модели, то есть в зависимости от содержания определенного вида сырья в детали зависит общая стоимость детали.

(таблица 1.)

Ресурсы	Плановый фонд ресурсов	Нормативные затраты ресурсов на 100 деталей
Первичное сырье	Вторичное сырье
Сплав 1	Сплав 2	Крепеж вида 2	Вторые прочие
Сплав 1, м²		4, 0	8, 0	-	-	3, 8
Сплав 2, м²		2, 5	-		-	-
Сплав 3, м²		3, 2	2, 0	3, 0	-	4, 6
Крепеж вида 1, шт		2, 1	2, 6	2, 3	2, 2	-
Крепеж вида 2, шт.		6, 5	-	-		-
Выручка, у.е.		1, 3	2, 0	1, 5	0, 3	1, 7

1. Выполним построение математической модели.

Обозначим, х₂, х₃, х₄, х₅ – количество видов сырья, соответствующего вида (100шт.): х₁ - первичное сырье; х₂ – вторичное сырье Сплав 1; х₃ - Сплав 2; х₄ - Крепеж вида 2; х₅ - вторичное прочее сырье.

Запишем целевую функцию, максимизирующая выручку:

F(X) = 1, 3х₁ + 2х₂ + 1, 5х₃ + 0, 3х₄ + 1, 7х₅ ® max

Запишем ограничения:

4х₁ + 8х₂ + 0х₃ + 0х₄ + 3, 8х₅ £ 40000 (ограничение по ресурсу «Сплав 1»);

2, 5х₁ + 0х₂ + 10х₃ + 0х₄ + 0х₅ £ 25000 (ограничение по ресурсу «Сплав 2»);

3, 2х₁ + 2х₂ + 3х₃ + 0х₄ + 4, 6х₅ £ 27000 (ограничение по ресурсу «Сплав 3»);

2, 1х₁ + 2, 6х₂ + 2, 3х₃ + 2, 2х₄ + 0х₅ £ 20000 (ограничение по ресурсу «Крепеж вида 1, шт.»);

6, 5х₁ + 0х₂ + 0х₃ + 21х₄ + 0х₅ £ 45000 (ограничение по ресурсу «Крепеж вида 2, шт.»);

х_{1, 2, 3, 4, 5} £ 0 (условие не отрицательности количества сырья)

В результате получаем следующую экономико-математическую модель:

F(X) = 1, 3х₁ + 2х₂ + 1, 5х₃ + 0, 3х₄ + 1, 7х₅ ® max

4х₁ + 8х₂ + 0х₃ + 0х₄ + 3, 8х₅ £ 40000

2, 5х₁ + 0х₂ + 10х₃ + 0х₄ + 0х₅ £ 25000

3, 2х₁ + 2х₂ + 3х₃ + 0х₄ + 4, 6х₅ £ 27000

2, 1х₁ + 2, 6х₂ + 2, 3х₃ + 2, 2х₄ + 0х₅ £ 20000

6, 5х₁ + 0х₂ + 0х₃ + 21х₄ + 0х₅ £ 45000

х_{1, 2, 3, 4, 5} ³ 0

2.Создаем документ Microsoft Excel и вводим исходные данные задачи, как показано на рисунке 2.1.

Рисунок 2.1. – Исходные данные задачи линейного программирования

3. Вводим зависимость для целевой функции

Для этого ставим курсор в ячейку G4, затем нажимаем на кнопку «Мастер функций», которая находится на панели инструментов. Окно мастера функций показано на рисунке 2.2.Выбираем категорию «Математические», Функцию «СУММПРОИЗВ» и нажимаем OK. Окно для ввода аргументов функции показано на рисунке 2.3.

Рисунок 2.2. – Окно «Мастера функций»

В появившемся окне «Аргументы функции», в строку «Массив 1» вводим B$3: F$3, а в строку «Массив 2» вводим B4: F4 и нажимаем ОK. В ячейку G4 введена функция.

Рисунок 2.3. – Окно для ввода аргументов функции

4. Вводим зависимости для ограничений. Для этого повторяем процесс, описанный в п.3 для каждого из ограничений. В результате в ячейке G7 должна появиться формула: =СУММПРОИЗВ(B$3: F$3, B7: F7). В ячейке G8 должна появиться формула: =СУММПРОИЗВ(B$3: F$3, B8: F8), в ячейке G9 должна появиться формула: =СУММПРОИЗВ(B$3: F$3, B9: F9), в ячейке G10 должна появиться формула: =СУММПРОИЗВ(B$3: F$3, B10: F10), а в ячейке G11 должна появиться формула: =СУММПРОИЗВ(B$3: F$3, B11: F11)

Далее в строке Меню выбираем Сервисà Поиск решения. В появившемся окне «Поиск решения» назначаем целевую функцию. Для этого ставим курсор в строку «Установить целевую ячейку»à вводим адрес ячейки $G$4, равной «Максимальному значению»à курсор в строку «Изменяя ячейки»à вводим адреса искомых переменных $B$3: $F$3. Окно «Поиск решения» показано на рисунке 2.3.

Рисунок 2.3 – Окно «Поиск решения»

Нажимаем на кнопку «Добавить», появляется окно «Добавление ограничения», представленного на рисунке 2.4.

Рисунок 2.4 – Окно «Добавление ограничения»

В строке «Ссылка на ячейку» вводим адрес $G$7à вводим знак ограниченияà в строке «Ограничение» вводим адрес $I$7à нажимаем на кнопку «Добавить». Вводим остальные ограничения по этому же алгоритму. После введения последнего ограничения нажимаем кнопку OK. На экране появляется окно «Поиск решения» с введенными условиями, как показано на рисунке 2.5.

Рисунок 2.5 – Окно «Поиск решения» с введенными ограничениями

5. Вводим параметры для решения ЗЛП.

Для этого в окне «Поиск решения» нажимаем на кнопку «Параметры». Появляется окно «Параметры поиска решения». Устанавливаем в окнах «Линейная модель» (это обеспечивает применение симплекс-метода) и «Неотрицательные значения», как показано на рисунке 2.6.

Рисунок 2.6 – Окно «Параметров поиска решения»

Далее нажимаем кнопку OK и на экране появляется окно «Поиск решения». Нажимаем кнопку «Выполнить». Появляется окно «Результаты поиска решения» и исходная таблица с заполненными ячейками B3: F3 и ячейка G4 с максимальным значением целевой функции, как показано на рисунке 2.6.

Рисунок 2.7 – Окно «Результаты поиска решения»

Нажимаем кнопку OK.

Полученное решение означает, что для максимизации выручки необходимо:

Х₁ = 0; Х₂ = 3763, 70; Х₃ = 2500; Х₄ = 2029, 27; Х₅ = 2602, 74;

Максимальная выручка составляет: 16310, 83 у.е.

⇐ Предыдущая 1 2 3 45