КУРСОВА РОБОТА НА ТЕМУ: «Розв’язання задач оптимізацій нелінійних моделей в MS Excel»

ЗМІСТ

ВСТУП    3

1. НАДБУДОВА EXCEL ПОШУК РОЗВ’ЯЗКУ    4

1.1.    Надбудова Excel Пошук розв’язку    4

1.2.    Параметри надбудови Пошук розв’язку    7

2.    НЕЛІНІЙНІ МОДЕЛІ    10

2.1.    Характеристика нелінійних моделей    10

2.2.    План виробництва    12

2.3.    Управління запасами    14

2.4.    Розміщення центру обслуговування    17

2.5    Співвідношення двох процесів    22

ВИСНОВКИ    25

СПИСОК ВИКОРИСТАНИХ ДЖЕРЕЛ    26

ВСТУП

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

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

Розглянуті моделі розв’язувались в середовищі програми Excel за допомогою надбудови “Поиск решения”, яка призначена для розв’язання певних систем рівнянь, лінійних та нелінійних задач оптимізації. Вона дозволяє знайти такий розв’язок задачі, при якому в заданій комірці робочого листа (в цільовій комірці) досягається максимум або мінімум або ж це значення дорівнює деякому числу.

Метою даної курсової роботи є аналіз можливостей середовища Excel при розв’язуванні задач оптимізації нелінійних моделей.

Для досягнення даної мети необхідно вирішити такі завдання: проаналізувати надбудову Пошуку розв’язку; обгрунтувати параметри засобу Пошук рішення; проаналізувати нелінійні моделі; обгрунтувати план виробництва, управління запасами,розміщення центру обслуговування, та співвідношення двох процесів.

1.НАДБУДОВА EXCEL ПОШУК РОЗВ’ЯЗКУ

  1. Надбудова Excel Пошук розв’язку.

Могутнім засобом аналізу даних Excel
є надбудова Solver (Пошук рішення). З її допомогою можна визначити, при яких значеннях вказаних комірок, формула в цільовій комірці приймає потрібне значення (мінімальне, максимальне або рівне якій-небудь величині). Для надбудови пошуку рішення можна задати обмеження, причому не обов’язково, щоб при цьому використовувалися ті ж комірки. Для розрахунку заданого значення застосовуються різні математичні методи пошуку. Можна встановити режим, в якому отримані значення змінних автоматично заносяться в таблицю. Крім того, результати роботи програми можуть бути оформлені у вигляді звіту. Програма Поиск решения (в оригіналі Excel Solver) – додаткова надбудова табличного процесора MS Excel, яка призначена для розв’язання певних систем рівнянь, лінійних та нелінійних задач оптимізації, використовується з 1991 року. Розмір задачі, яку можна розв’язати за допомогою базової версії цієї програми, обмежується такими граничними показниками:

·    кількість невідомих (decision variable) – 200;

·    кількість формульних обмежень (explicit constraint) на невідомі – 100;

·    кількість граничних умов (simple constraint) на невідомі – 400.

Розробник програми Solver компанія Frontline System вже давно спеціалізується на розробці могутніх і зручних способів оптимізації, вбудованих в середовище популярних табличних процесорів різноманітних фірм-виробників (MS Excel Solver, Adobe Quattro Pro, Lotus). Висока ефективність їх застосування пояснюється інтеграцією програми оптимізації і табличного бізнес-документу. Завдяки світовій популярності табличного процесора MS Excel, вбудована в його середовище програма Solver є найбільш поширеним інструментом для пошуку оптимальних рішень у сфері сучасного бізнесу.

За замовчанням в Excel надбудова “Поиск решения” відключена. Щоб активізувати її в Excel 2007, потрібно клацнути значок Кнопка
Microsoft Office, клацнути Параметры Excel, а потім вибрати категорію Надстройки. У полі Управление вибрати значення Надстройки Excel і натиснути кнопку Перейти. У полі Доступные надстройки встановити прапорець поряд з пунктом Поиск
решения і натиснути кнопку ОК.

У Excel 2003 і нижче потрібно вибрати команду Сервис/Надстройки, в діалоговому вікні Надстройки, що з’явилося, встановити прапорець Поиск решения і клацнути на кнопці ОК. Якщо вслід за цим на екрані з’явиться діалогове вікно з пропозицією підтвердити ці наміри, клацнути на кнопці Да. (Можливо, для цього знадобиться встановлюючий компакт-диск Office).


1. Створити таблицю з формулами, які встановлюють зв’язки між комірками.

2. Виділити цільову комірку, яка повинна прийняти необхідне значення, і вибрати команду:

– В Excel 2007 Данные/Анализ/Поиск решения;

– В Excel 2003 і нижче Tools > Solver (Сервис > Поиск решения). Поле Set
Target Cell (Установить целевую ячейку) діалогового вікна надбудови Solver (Поиск решения), що відкрилося, міститиме адресу цільової комірки.

3. Встановити перемикачі Equal To (Равной), що задають значення цільової комірки, − Мах (Максимальному значению), Min (Минимальному значению) або Value of (значению). У останньому випадку ввести значення в полі справа.

4. Вказати в полі By Changing Cells (Изменяя ячейки), в яких комірках програма повинна змінювати значення у пошуках оптимального результату.

5. Створити обмеження в списку Subject to the Constraints (Ограничения). Для цього клацнути на кнопці Add (Добавить) і в діалоговому вікні Add Constraint (Додавание ограничения) визначити обмеження.


6. Клацнути на кнопці Options (Параметры), і у вікні, що з’явилося, встановити перемикач Неотрицательные значения (якщо змінні повинні бути додатними числами), Ленейная модель (якщо завдання відноситься до лінійних моделей).

7. Клацнувши на кнопці Solver (Выполнить), запустити процес пошуку рішення.

8. Коли з’явиться діалогове вікно Solver Results (Результаты поиска решения), вибрати перемикач Keep Solve Solution (Сохранить найденное решение) або Restore Original Values (Востановить исходные значения).

9. Клацнути на кнопці ОК.

  1. Параметри надбудови Пошук рішення

Максимальний час − служить для обмеження часу, відпущеного на пошук рішення задачі. У цьому полі можна ввести час в секундах, що не перевищує 32767 (приблизно дев’ять годин); значення 100, що використовується за замовчанням, цілком прийнятно для вирішення більшості простих завдань.

Граничне число ітерацій − керує часом рішення задачі шляхом обмеження числа обчислювальних циклів (ітерацій).

Відносна похибка − визначає точність обчислень. Чим менше значення цього параметра, тим вища точність обчислень.

Допустима похибка − призначена для задання допустимої похибки відносно оптимального рішення, якщо множина значень комірки обмежена множиною цілих чисел. Чим більше значення допустимої похибки, тим менше часу потрібно на пошук рішення.

Збіжність − застосовується тільки до нелінійних завдань. Коли відносна зміна значення в цільовій комірці за останні п’ять ітерацій стає меншою за число, вказане в полі Сходимость, пошук припиняється.

Лінійна модель − служить для прискорення пошуку рішення шляхом застосування до задачі оптимізації лінійної моделі. Нелінійні моделі припускають використання нелінійних функцій.

Невід’ємні значення − дозволяє встановити нульову нижню межу для тих комірок, для яких не було задано відповідне обмеження в діалоговому вікні Добавить ограничение.

Автоматичне масштабування − використовується, коли числа в змінних комірках і в цільовій комірці істотно відрізняються.

Показувати результати ітерацій − припиняє пошук рішення для перегляду результатів окремих ітерацій.

Завантажити модель − після клацання на цій кнопці відривається однойменне діалогове вікно, в якому можна ввести посилання на діапазон комірок, що містять модель оптимізації.

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

Оцінка лінійна − виберіть цей перемикач для роботи з лінійною моделлю.

Оцінка квадратична − виберіть цей перемикач для роботи з нелінійною моделлю.

Прямі різниці − використовується в більшості завдань, де швидкість зміни обмежень відносно невисока. Збільшує швидкість роботи засобу Поик решения.

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

Метод пошуку Ньютона − вимагає більше пам’яті, але виконує менше ітерацій, ніж в методі зв’заних градієнтів.

Метод пошуку зв’язаних градієнтів − реалізує метод зв’язаних градієнтів, для якого потрібно менше пам’яті, але виконується більше ітерацій, ніж в методі Ньютона. Даний метод слід використовувати, якщо завдання достатньо велике і необхідно економити пам’ять або якщо ітерації дають дуже малу відмінність в послідовних наближеннях.

 

ЗАВАНТАЖИТИ

Для скачування файлів необхідно або Зареєструватись

Розвязування задач оптимізації нелінійних моделей в MS Excel (946.9 KiB, Завантажень: 5)

Сторінка: 1 2 3
завантаження...
WordPress: 23.11MB | MySQL:26 | 0,543sec