Розв’язування систем лінійних рівнянь методом Пошуку розв’язку

Мета роботи: Закріпити знання та вміння по роботі з програмою MS Excel, набуті в попередніх практичних роботах. Навчитись застосовувати метод Пошук розв’язку для розв’язування оптимізаційних задач.

Теоретичний матеріал

Для реалізації оптимізаційних розрахунків в Microsoft Excel використовується надбудова Пошук рішення. Якщо вона не встановлена - тобто в меню Сервіс відсутній рядок Пошук рішення, то за допомогою команди Сервіс Þ Надбудови слід встановити відповідний прапорець в переліку надбудов MS Excel. Крім того, слід правильно підготувати дані оптимізаційної моделі в таблиці MS Excel. Модель оптимізаційного завдання задається в діалоговому вікні Пошук рішення:

Модель використовує цільову функцію, яка записується у вигляді формули в окремому осередку. Для цільової функції указується: максимізація, мінімізація або рівність фіксованому значенню. В процесі пошуку рішення змінюються значення у вказаних осередках, відповідних змінним при дотриманні обмежень. Додаткові настройки оптимізації задаються в діалоговому вікні Параметри пошуку рішення

Для рішення задач за допомогою цієї надбудови потрібно виконати наступні дії

2. Створити форму для введення умов задачі.

3. Вказати адреси комірок, в яких буде розміщений результат рішення (комірки, що змінюються).

4. Ввести вихідні дані.

5. Ввести залежність для цільової функції.

6. Ввести залежність для обмежень.

7. Вказати цільову комірку.

8. Ввести обмеження.

9. Ввести параметри для рішення задачі.

10.

Приклад розв’язування такого типу задач:

Задано: Фабрика має в своєму розпорядженні визначену кількість ресурсів: робочу силу, сировину, обладнання. Робоча сила, сировина і обладнання – є в наявності відповідно 80 (люд./днів), 480 (кг) і 130 (станко/год.). Фабрика може випускати товари чотирьох видів. Інформація про кількість одиниць кожного ресурсу, необхідних для виробництва одного килиму кожного виду, і прибутках, що отримує підприємство від одиниці кожного виду товарів, наведена в таблиці.

 

Ресурси	Норми витрат ресурсів на одиницю виробу	Наявність ресурсів
Виріб №1	Виріб №2	Виріб №3	Виріб №3
Праця
Сировина
Обладнання
Ціна (тис.грн.)

 

Потрібно знайти такий план випуску продукції, при якому буде максимальною загальна вартість продукції.

 

Складемо економіко-математичну модель задачі:

Позначимо через Х₁, Х₂, Х₃, Х₄ кількість виробів кожного типу.

Цільова функція – це вираз, який необхідно максимізувати:

f (x) = 3Х₁ + 4Х₂ + 3Х₃ + 1Х₄, або f (x) = 3Х₁ + 4Х₂ + 3Х₃ + Х₄

Обмеження по ресурсам

7Х₁+2Х₂+2Х₃+6Х₄≤80

5Х₁+8Х₂+4Х₃+3Х₄≤480

2Х₁+4Х₂+Х₃+8Х₄≤130

 

Це є задача оптимального використання ресурсів. Для розв’язку даної задачі використаємо надбудову Excel „Поиск решения”, яка дозволяє вирішувати задачі оптимізації.

Якщо в меню Сервіс відсутня команда Поиск решения, значить необхідно її завантажити. Для цього треба вибрати в меню СЕРВИС - Надстройки і активізуйте надбудову Поиск решения. Якщо такої надбудови в списку немає то її потрібно ін сталювати.

 

Для рішення задач за допомогою цієї надбудови потрібно виконати наступні дії:

1. Створити форму для введення умов задачі.

2. Вказати адреси комірок, в яких буде розміщений результат рішення (комірки, що змінюються).

3. Ввести початкові дані.

4. Ввести залежність для цільової функції.

5. Ввести залежність для обмежень.

6. Вказати цільову комірку.

7. Ввести обмеження.

8. Ввести параметри для рішення задачі.

 

Першим кроком рішення задачі, буде підготовка форми для введення умов.

В нашій задачі оптимальні значення вектора Х=(Х₁, Х₂, Х₃, Х₄) будуть розташовані в комірках В3:Е3, оптимальне значення цільової функції – в комірці – F4.

 

Введемо початкові дані в створену форму. Отримаємо наступні результати:

 

В комірці F4 вставляємо залежність для цільової функції:

користуючись майстром функцій або в рядку формул вводимо наступну формулу: =СУММПРОИЗВ(B$3:E$3;B4:E4)

Введемо залежності для лівих частин обмежень:

послідовно в комірки F7, F8, F9 скопіюємо вміст комірки F4.

На цьому введення даних в форму є завершеним.

 

Наступним кроком є запуск надбудови “Пошук рішення”

Після вибору команд Сервис Þ Поиск решения з’явиться діалогове вікно надбудови Пошук рішення.

В цьому діалоговому вікні є три основних параметри:

– Установить целевую ячейку

– Изменяя ячейки

– Ограничения

Спочатку заповнюємо поле “Установить целевую ячейку”. У всіх задачах для засобу Пошук рішення оптимізується результат в одній з комірок робочого листа. Цільова комірка пов’язана з іншими комірками цього робочого листа за допомогою формул. Засіб Пошук рішення використовує формули, які дають результат в цільовій комірці, для перевірки можливих рішень. Можна вибрати пошук найменшого або найбільшого значення для цільової комірки чи встановити конкретне значення.

В нашому випадку цільовою коміркою являється комірка $F$4. Тому в поле “Установить целевую ячейку” вставляємо значення: $F$4. Вибираємо напрямок цільової функції: Максимальному значению.

Другий важливий параметр засобу – це параметр “Изменяя ячейки”. Комірки що змінюються – це ті комірки, значення в яких будуть змінюватись для того, щоб оптимізувати результат в цільовій комірці. Для пошуку рішення можна вказати до 200 комірок, що змінюються. До комірок, що змінюються, пред’являються дві основні вимоги: вони не повинні містити формул, і зміна їх значень повинна впливати на зміні результату в цільовій комірці. Іншими словами, цільова комірка залежна від комірок, що змінюються.

Отже, згідно умови нашої задачі встановлюємо параметр “Изменяя ячейки” рівним B$3:E$3 – комірки що будуть змінюватись.

Третій параметр, якій потрібно вводити для Пошуку рішення – це Ограничения. Натиснувши кнопку діалогового вікна “Добавить” на екрані з’явиться нове діалогове вікно “Добавление ограничения” Малюнок 2

 

В полі “Ссылка на ячейку” вводимо адресу $F$7, вибираємо знак обмеження <=, а в полі “Ограничение” вводимо адресу комірки $Н$7. Після цього натискаємо кнопку “Добавить” на екрані знову з’явиться діалогове вікно “Добавление ограничения” в якому потрібно ввести наступне обмеження. Після введення останнього обмеження натискаємо кнопку “ОК”.

Наступним кроком, після введення обмежень, є встановлення параметрів для рішення задачі. Для встановлення параметрів рішення задачі використовується діалогове вікно “Параметры поиска решения”, яке викликається за допомогою кнопки “Параметры”.

Для нашої задачі встановлюємо наступні параметри: встановлюємо прапорці: “Линейная модель”, “Неотрицательные значения” і натискаємо кнопку “ОК”.

Введення параметрів для роботи надбудови “Пошук рішення” на цьому закінчено. Тепер для отримання результату натискаємо кнопку “Выполнить” і на екрані з’являється діалогове вікно “Результаты поиска решения”.

Отримане рішення означає, що максимальний прибуток 150 тис. грн. фабрика може отримати при випуску 30 одиниць товару №2 і 10 одиниць товару №3. При цьому ресурси праця і обладнання будуть використані повністю, а із 480 кг пряжі (ресурс сировина) буде використано 280 кг.

 

 

Excel дозволяє представити результати пошуку рішення в формі звіту.

Існує три типи таких звітів:

Результаты. В звіт включаються вихідні і кінцеві значення цільової і комірок, що впливають на неї, додаткові відомості про обмеження

Устойчивость. Звіт, що містить відомості про чутливість рішення до малих змін в комірках, що змінюються або в формулах обмежень

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

 

Питання до захисту лабораторної роботи №9.

1.

Варіанти завдань.

Варіант завдання відповідає номеру студента у списку групи.

Розв’язати систему із кількох рівнянь з кількома невідомими виду

a_iX₊b_iY+c_iZ=d_i (i = 1,2,3,…),

використовуючи команду Сервис-Поиск решения.

Для цього внесіть в таблицю приблизні значення невідомих X, Y, Z, значення коефіцієнтів при цих невідомих a_i, b_i, c_i (i = 1,2,3); перемножте X, Y, Z на відповідні коефіцієнти і просумуйте добутки по строкам. Запустіть Поиск решения. Як цільову комірку візьміть першу суму, задайте установку в ній першого вільного члена d₁; на дві інші суми накладіть обмеження: рівність двом іншим вільним членам d₂ и d₃; нажміть клавішу “ Параметры ” і ознайомтесь з параметрами і методами, які застосовуються при оптимізаційних розрахунках; закрийте вікно “ Параметры ”, натиснувши ОК, і запустіть виконання програми (Выполнить).