Приклади розрахунків з використанням процедури пошуку рішення
Міністерство Освіти І Науки україни
Київський національний університет
будівництва і архітектури
ЕЛЕКТРОННІ ТАБЛИЦІ
MS EXCEL
Методичні вказівки
до виконання індивідуальних завдань з навчальних дисциплін: “Основи інформатики”, “Практикум із техніки користування ЕОМ”, “Інформатика і системологія” для студентів спеціальностей: 8.050201 “Менеджмент організацій”, 7.070801 “Екологія та охорона навколишнього середовища”, 8.092108 “Теплогазопостачання та вентиляція”, 8.092601 “Водопостачання та водовідведення”, 7.070904 “Землевпорядкування та кадастр, 7.092109 “Споруди і обладнання водопостачання та водовідведення”
Київ 2002
УДК 681.3
ББК 32.973.26-018.2
С 40
Укладачі: Г.Л. Васильєва, канд. фіз.-мат. наук, доцент,
В. О. Недін, асистент
Рецензент М. В. Коханович, старший викладач
Відповідальний за випуск П.П.Лізунов, доктор техн. наук, професор
Затверджено на засіданні кафедри основ інформатики, протокол №6 від 25.03.2002 р.
Видається в авторській редакції.
ЕЛЕКТРОННІ ТАБЛИЦІ MS EXCEL:
С 40 Методичні вказівки до виконання індивідуальних завдань /Уклад.: Г.Л.Васильєва, В.О. Недін. – К.: КНУБА, 2002. – 30 с.
Призначено для студентів спеціальностей: 8.050201 “Менеджмент організацій, 7.070801 “Екологія та охорона навколишнього середовища”, 8.092108 “Теплогазопостачання та вентиляція”, 8.092601 “Водопостачання та водовідведення”, 7.070904 “Землевпорядкування та кадастр”, 7.092109 “Споруди і обладнання водопостачання та водовідведення”
|
|
|
Вирішення задач оптимізації з використанням Microsoft Excel
Умови практичних задач оптимізації включають досягнення певної конкретної мети, наприклад знаходження мінімального рішення за існування певних обмежень вихідних даних. Специфіка таких задач полягає в тому, що створена математична модель досліджуваного процесу, але невідоме значення параметра, за якого можна досягти поставленої мети.
В Microsoft Excel вирішення подібних задач реалізовано як пошук значення параметра функції, що відповідає конкретному значенню функції. Таким чином, за допомогою Excel можна вирішувати будь-які рівняння з одним невідомим. Можливо також знаходити значення кількох параметрів, що забезпечують отримання попередньо заданого результату. В окремих задачах треба знайти не конкретне значення параметра, а максимально чи мінімально можливе. Наприклад, визначення значення у даній ячейці, що відповідає екстремуму у залежній ячейці. Цільова та залежна ячейка повинні бути пов'язані формулою, щоб зміна числового значення в одній з них викликала зміну змісту іншої ячейки. Подібні задачі у Excel вирішуються за допомогою команди Сервис, Поиск решения.
|
|
|
Приклад №1. Знайти мінімальне значення функції
із заданою точністю - відносна похибка e < 1.10-5. Знайти точку мінімуму функції F(x), використовуючи точку (4;-1;2) як початкову.
Для вирішення даної задачі будемо використовувати програму Excel, процедуру Поиск решения. Запишемо умови задачі у робочій книзі Excel. Числові значення та формули у ячейках представлені у таблицях 1 і 2 відповідно.
Таблиця 1. Числові значення у ячейках
| А | В | С | |
| 1 | 4 | 3 | 9 |
| 2 | -1 | -4 | 16 |
| 3 | 2 | 7 | 196 |
| 4 | 221 |
Таблиця 2. Формули у ячейках
| А | В | С | |
| 1 | 4 | =A1-1 | =СТЕПЕНЬ(В1;2) |
| 2 | -1 | =A2-3 | =СТЕПЕНЬ(В2;2) |
| 3 | 2 | =A3+5 | =4*СТЕПЕНЬ(В3;2) |
| 4 | =СУММ(C1:C3) |
Обираємо опцію Сервис, Поиск решения. У вікні Поиск решения,що з'явилося (Рис. 1), встановлюємо мінімальне значення у цільовій ячейці С4 та вказуємо для зміни ячейки А1:А3.
Рис. 1. Вигляд вікна Поиск решения
Натискаємо кнопку Параметры. У вікні, що з’явилося, встановлюємо параметри пошуку рішення (максимальний час пошуку, кількість ітерацій, точність, припустиме відхилення, метод оцінки, метод чисельного диференціювання та алгоритм оптимізації). На рис.2 представлені параметри, встановлені для вирішення даного приклада.
|
|
|
Рис. 2. Вигляд вікна Параметры поиска решения
Натискаємо кнопку ОК та переходимо назад у вікно Поиск решения. Коли всі необхідні для вирішення параметри задані, натискаємо кнопку Выполнить. Програма виконує обчислення. Оскільки ми встановили опцію Показывать результаты итераций, то після виконання першої ітерації з’явиться вікно Текущее состояние поиска решения (рис. 3) та результати обчислень (Таблиця 3). За допомогою натиснення кнопки Сохранить сценарий ми можемо зберегти під різними іменами результати кожної ітерації пошуку і потім звертатися до них та аналізувати. На цьому етапі ми можемо призупинити процес знаходження оптимального значення, продовжити його, а також отримати додаткову інформацію. За умови, що обрано Продолжить, після виконання кожної ітерації на екрані буде з’являтися вікно (Рис. 3) та нові результати.
Рис. 3. Вікно Текущее состояние поиска решения
Таблиця 3. Результати 1-ої ітерації пошуку рішення
| А | В | С | |
| 1 | 4 | 3 | 9 |
| 2 | -1 | -4 | 16 |
| 3 | 2,000002 | 7,000002 | 196,000112 |
| 4 | 221,000112 |
Результати наступних ітерацій наведені у таблицях 4 - 6.
|
|
|
Таблиця 4. Результати 2-ої ітерації пошуку рішення
| А | В | С | |
| 1 | 3,232205 | 2,232205 | 4,982737 |
| 2 | 0,023726 | -2,976374 | 8,858204 |
| 3 | -5,166082 | -0,166082 | 0,110332 |
| 4 | 13,951274 |
Таблиця 5. Результати 3-ої ітерації пошуку рішення
| А | В | С | |
| 1 | 0,999999 | -1,27703Е-06 | 1,6308Е-12 |
| 2 | 2,999999 | -6,74897Е-07 | 4,55487Е-13 |
| 3 | -4,999996 | 3,73271Е-06 | 5,57325Е-11 |
| 4 | 5,78188Е-11 |
Таблиця 6. Результати 4-ої ітерації пошуку рішення
| А | В | С | |
| 1 | 0,999999 | -1,27703Е-06 | 1,6308Е-12 |
| 2 | 2,999999 | -6,74897Е-07 | 4,55487Е-13 |
| 3 | -5,000001 | -1,26729Е-06 | 6,42409Е-12 |
| 4 | 8,51039Е-12 |
Після закінчення процесу пошуку на екрані з’являється вікно Результаты поиска решения (рис. 4). Потрібно обрати, які дані залишити, та тип звіту, а також чи зберігати сценарій отриманого рішення. Встановивши опції, натискаємо ОК та отримаємо звіт вказаного типу (рис. 5-7).
Рис. 4. Вікно Результаты поиска решения
Рис. 5. Приклад звіту по стійкості
Рис. 6. Приклад звіту по результатах
Рис. 7. Приклад звіту по лімітах
Приклад №2. Знайти координати точки мінімуму та мінімальне значення функції
за обмежень:
1. x1>=0;
2. x2>=0;
3. x3>=0;
4. x3<5;
5. x32-x12-x22>=0;
6. x12+x22+x32-4>=0.
Запустивши процедуру пошуку рішення, отримаємо звіт по результатах, де вказані вихідні та кінцеві значення функції і координат (рис. 8).
Рис. 8. Результати пошуку рішення для приклада № 2
Приклади розрахунків з використанням процедури пошуку рішення
У складі Microsoft Excel у папці Examples\Solver знаходиться книга з прикладами (Solvsamp.xls) використання процедури пошуку рішення (Solver.xls). У книзі Solverex.xls можливо знайти інформацію про процедури максимізації або мінімізації цільової функції, а також про накладення та зміну обмежень та збереження моделі оптимізації.
Листи з прикладами розрахунків з книги Solvsamp.xls можна використовувати як основу для постановки задач оптимізації.
Дата добавления: 2018-02-18; просмотров: 601; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!