Тема 15. Пошук рішень в Excel

Пошук рішень в Excel

Розглянемо задачу про використання сировини:

Нехай деяке підприємство виробляє два види продукції Р1 і Р2. Для випуску цих видів продукції необхідно використати три види сировини С1, С2 і С3. Відомо, яка кількість кожної сировини витрачається для виробництва продукції Р1 і Р2 відповідно. Також відома інформація про наявність усіх видів сировини на складі.

Види сировини	Запаси сировини	Кількість одиниць сировини для виготовлення одиниці продукції
		Р1	Р2
С1 С2 С3	20 40 30	2 8 5	5 5 6

Прибуток від реалізації одиниці продукції Р1 становить 50 грн., а продукції Р2 - 40 грн.

Шукатимемо розв'язок такої задачі: скільки потрібно виробити продукції Р1 і Р2 для отримання максимального при­бутку.

Створимо математичну модель даної задачі. Позначимо х1 - кількість одиниць продукції Р1, а х2 - кількість одиниць про­дукції Р2. Тоді, враховуючи кількість одиниць сировини, що витрачається на виготовлення одиниці продукції, а також за­паси сировини, одержимо систему нерівностей, яка одночасно є системою обмежень для розв'язку поставленої задачі:

2* х1 + 5* х2 <= 20

8* х1 + 5* х2 <= 40

5* х1 + 6* х2 <= 30

По цих обмеженнях видно, що кількість сировини не мо­же перевищувати її запасів на складі підприємства.

За умовою задачі прибуток підприємства складається з при­бутку від реалізації х1 одиниць продукції Р1 (50 грн. за кожну) та х2 одиниць продукції Р2 (40 грн. за кожну). Сумарний прибу­ток розраховуватиметься за формулою:

L = 50*х1 + 40*х2

Потрібно знайти такі невід'ємні значення х1 і х2, при яких функція L набуде максимального значення (щоб отримати найбільший прибуток).

Задамо всі дані в Excel:

У комірках А12, А13, А14 і D12 будуть відображатись 0, бо у комірках C9 i D9 записані 0.

Задамо команду Сервіс/Пошук рішень (якщо такої команди немає, то у  Сервіс/Настройки встановити відповідний прапорець).

·         Виберемо функцію у комірці D12, шукатимемо її максимум,

·         шукані величини - це діапазон C9:D9,

·         задамо обмеження : діапазон C9:D9 >= 0, діапазон C9:D9 - цілі числа, діапазон A12:A14 <= B12:B14.

Нові значення ми отримаємо в комірках C9 i D9 - це кількості одиниць продукції, в D12 отримаємо максимальний прибуток, в А12, А13, А14 - отримаємо кількості витраченої сировини на продукції Р1 та Р2 (порівняємо їх із запасами сировини - В12, В13, В14).

Відео-урок для розв'язання задачі лінійного програмування:

                  https://www.youtube.com/watch?v=Xmo-M6a4wWI