ІНСТРУКЦІЯ ПО ВИКОРИСТАННЮ Microsoft Excel ДЛЯ РОЗВ’ЯЗАННЯ ЗАДАЧ ЛП

ІНСТРУКЦІЯ ПО ВИКОРИСТАННЮ Microsoft Excel ДЛЯ РОЗВ’ЯЗАННЯ ЗАДАЧ ЛП

Для того щоб розв’язати задачу ЛП в табличному редакторі Microsoft Excel, необхідно виконати наступні дії.

1. Ввести умову задачі:

a) створити екранну форму для введення умови задачі:

РОЗВ’ЯЗАННЯ ОДНОІНДЕКСНИХ ЗАДАЧ

Розглянемо приклад знаходження рішення для наступної одноіндексної задачі ЛП:

(1.1)

Введення початкових даних

А)-b) Створення екранної форми і введення в неї умови задачі

Екранна форма для введення умов задачі (1.1) разом з введеними в неї початковими даними представлена на мал. 1.1.

МАЛ. 1.1. Екранна форма задачі (1.1) (курсор в осередку F6)

У екранній формі на мал. 1.1 кожній змінній і кожному коефіцієнту задачі поставлений у відповідність конкретний осередок в Excel. Ім'я осередку складається з букви, яка вказує стовпець, і цифри, яка вказує рядок, на перетині яких знаходиться об'єкт задачі ЛП. Так, наприклад, змінним задачі (1.1) відповідають осередки B3 (), C3 (), D3 (), E3 (), коефіцієнтам ЦФ відповідаютьосередки B6 (130,5), C6 (20), D6 (56), E6 (87,8), правим частинам обмежень відповідають осередки H10 (756), H11 (450), H12 (89) і т.д.

Формули, що описують обмеження моделі (1.1)

Ліва частина обмеження	Формула Excel
або	=СУММПРОИЗВ(B$3:E$3;B10:E10)
або	=СУММПРОИЗВ(B$3:E$3;B11:E11)
або	=СУММПРОИЗВ(B$3:E$3;B12:E12)

Як видно з табл. 1.1, формули, задаючі ліві частини обмежень задачі (1.1), відрізняються одна від одної і від формули (1.4) в цільовому осередку F6 тільки номером рядка у другому масиві. Цей номер визначається тим рядком, в якому обмеження записане в екранній формі. Тому для завдання залежності для лівих частин обмежень досить скопіювати формулу з цільового осередку в осередки лівих частин обмежень. Для цього необхідно:

1. вмістити курсор в полі цільового осередку F6 і скопіювати в буфер вміст осередку F6 ( клавішами " Ctrl-Insert");

2. вміщувати курсор почергово в поля лівої частини кожного з обмежень, тобто в F10, F11 і F12, і вставляти в ці поля вміст буфера (клавішами " Shift-Insert") (при цьому номер осередків у другому масиві формули буде мінятися на номер того рядка, в який була зроблена вставка з буфера);

3. на екрані в полях F10, F11 і F12 з'явиться 0 (нульове значення) (див. мал. 1.2).

Перевірка правильності введення формул

Для перевірки правильності введених формул проводьте почергово подвійне натиснення лівої клавіші миші на осередки з формулами. При цьому на екрані рамкою будуть виділятися осередки, що використовуються в формулі (мал. 1.4 і 1.5).

МАЛ. 1.4. Перевірка правильності введення формули в цільовий осередок F6

МАЛ. 1.5. Перевірка правильності введення формули в осередок F12

для лівої частини обмеження 3

d) Завдання ЦФ

Подальші дії проводяться у вікні "Пошук рішення", яке викликається з меню "Сервіс" (мал. 1.6):

· поставте курсор в полі "Встановити цільовий осередок";

· введіть адресу цільового осередку $F$6 або зробіть одне натиснення лівої клавіші миші на цільовий осередок в екранній формі (це буде рівносильно введенню адреси з клавіатури;

· введіть напрям оптимізації ЦФ, клацнувши один раз лівою клавішею миші по селекторній кнопці "максимальному значенню".

МАЛ. 1.6. Вікно "Пошук рішення" задачі (1.1)

Рішення задачі

а) Установка параметрів рішення задачі

Задача запускається на рішення у вікні "Пошук рішення". Але заздалегідь для встановлення конкретних параметрів рішення задач оптимізації певного класу необхідно натиснути кнопку "Параметри" і заповнити деякі поля вікна "Параметри пошуку рішення" (мал. 1.8).

МАЛ. 1.8. Параметри пошуку рішення, відповідні для більшості задач ЛП

Параметр "Максимальний час" служить для призначення часу (в секундах), що виділяється на рішення задачі. У полі можна ввести час, що не перевищує 32 767 секунд (більш 9 годин).

Параметр "Граничне число ітерацій" служить для управління часом рішення задачі шляхом обмеження числа проміжних обчислень. У полі можна ввести кількість ітерацій, що не перевищує 32 767.

Параметр "Відносна погрішність" служить для завдання точності, з якою визначається відповідність осередку цільовому значенню або наближення до вказаних кордонів. Поле повинно містити число з інтервалу від 0 до 1. Чем менше кількість десятеричних знаків у введеному числі, тим нижче точність. Висока точність збільшить час, який потрібно для того, щоб зійшовся процес оптимізації.

Параметр "Допустиме відхилення" служить для завдання допуску на відхилення від оптимального рішення в цілочисельних задачах. При вказівці більшого допуску пошук рішення закінчується швидше.

Параметр "Збіжність" застосовується тільки при рішенні нелінійних задач.

Установка прапорця "Лінійна модель" забезпечує прискорення пошуку рішення лінійної задачі за рахунок застосування симплекс-методу.

Підтвердіть встановлені параметри натисненням кнопки "OK".

B) Запуск задачі на рішення

Запуск задачі на рішення проводиться з вікна "Пошук рішення" шляхом натиснення кнопки "Виконати".

Після запуску на рішення задачі ЛП на екрані з'являється вікно "Результати пошуку рішення" з одним з повідомлень, представлених на мал. 1.9, 1.10 і 1.11.

МАЛ. 1.9. Повідомлення про успішне рішення задачі

МАЛ. 1.10. Повідомлення при неспільній системі обмежень задачі

МАЛ. 1.11. Повідомлення при необмеженості ЦФ в необхідному напрямі

Іноді повідомлення, представлені на мал. 1.10 і 1.11, свідчать не про характер оптимального рішення задачі, а про те, що при введенні умов задачі в Excel були допущені помилки, що не дозволяють Excel знайти оптимальне рішення, яке насправді існує (див. нижче за подразд.1.3.5).

Якщо при заповненні полів вікна "Пошук рішення" були допущені помилки, що не дозволяють Excel застосувати симплекс-метод для рішення задачі або довести її рішення до кінця, то після запуску задачі на рішення на екран буде видане відповідне повідомлення з вказівкою причини, по якій рішення не знайдене. Іноді дуже мале значення параметра "Відносна погрішність" не дозволяє знайти оптимальне рішення. Для виправлення цієї ситуації збільшуйте погрішність поразрядно, наприклад від 0,000001 до 0,00001 і т.д.

У вікні "Результати пошуку рішення" представлені назви трьох типів звітів: "Результати", "Стійкість", "Межі". Вони необхідні при аналізі отриманого рішення на чутливість (див. нижче лаб. роб. № 3). Для отримання ж відповіді (значень змінних, ЦФ і лівих частин обмежень) прямо в екранній формі просто натисніть кнопку "OK". Після цього в екранній формі з'являється оптимальне рішення задачі (мал. 1.12).

МАЛ. 1.12. Екранна форма задачі (1.1) після отримання рішення

Питання для самоконтролю

1. Які основні етапи рішення задач ЛП в MS Excel?

2. Який вигляд і способи завдання формул для цільового осередку і осередків лівих частин обмежень?

3. У чому значення використання символа $ в формулах MS Excel?

4. У чому відмінність використання в формулах MS Excel символів “;” і “:”?

5. Чому при введенні формул в осередки ЦФ і лівих частин обмежень в них відображаються нульові значення?

6. Яким чином в MS Excel задається напрям оптимізації ЦФ?

7. Які осередки екранної форми виконують ілюстративну функцію, а які необхідні для рішення задачі?

8. Як наочно відобразити в екранній формі осередки, що використовуються в конкретній формулі, з метою перевірки її правильності?

9. Поясніть загальний порядок роботи з вікном "Пошук рішення".

10. Яким чином можна змінювати, додавати, видаляти обмеження у вікні "Пошук рішення"?

11. Які повідомлення видаються в MS Excel у випадках: успішного рішення задачі ЛП; несовместности системи обмежень задачі; необмеженість ЦФ?

12. Поясніть значення параметрів, що задаються у вікні "Параметри пошуку рішення".

13. Які особливості рішення в MS Excel цілочисельних задач ЛП?

14. Які особливості рішення в MS Excel двухиндексных задач ЛП?

15. Які особливості рішення в MS Excel задач ЛП з булевыми змінними?

ІНСТРУКЦІЯ ПО ВИКОРИСТАННЮ Microsoft Excel ДЛЯ РОЗВ’ЯЗАННЯ ЗАДАЧ ЛП

Для того щоб розв’язати задачу ЛП в табличному редакторі Microsoft Excel, необхідно виконати наступні дії.

1. Ввести умову задачі:

a) створити екранну форму для введення умови задачі:

Система охраняемых территорий в США Изучение особо охраняемых природных территорий(ООПТ) США представляет особый интерес по многим причинам...

Что делать, если нет взаимности? А теперь спустимся с небес на землю. Приземлились? Продолжаем разговор...

Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...

Что делает отдел по эксплуатации и сопровождению ИС? Отвечает за сохранность данных (расписания копирования, копирование и пр.)...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: