ЗАСОБИ ОПТИМІЗАЦІЇ В EXCEL.
Створення оптимізаційної моделі
Припустимо, що на деякий процес можна впливати, змінюючи кілька параметрів управління. Якщо деяку характеристику цього процесу можна описати як функцію від параметрів управління, то природно шукати такі значення параметрів {оптимальний план), при яких функція набуватиме оптимального в певному розумінні значення (як правило, максимального або мінімального). При цьому оптимальний план має задовольняти обмеження, що накладаються на параметри управління.
Для розв’язування таких завдань в Excel існує спеціальний засіб Поиск решения. Але перед тим як використовувати його, потрібно ввести вихідні дані.
Розрізняють такі дані оптимізаційної задачі: параметри управління, цільова функція і обмеження.
Для параметрів управління потрібно відвести область комірок, де вони будуть записуватись. Потім в цю область слід ввести довільні значення параметрів (наприклад, усі нулі). Під час роботи Поиск решения підбиратиме значення цих параметрів доти, поки не отримає оптимальний план.
Цільову функцію будують, використовуючи посилання на комірки з початковими значеннями параметрів управління. Комірку, де міститься формула цільової функції, називають цільовою.
Кожне обмеження задачі в математичному записі має такий вигляд: h {параметри управління) R b, де h – деяка функція; R – одне з відношень =, > або <; b – дійсне число. Формулу функції h {ліва частина обмеження) та значення b {права частина) потрібно ввести у дві комірки, а запис обмеження відбувається безпосередньо у Поиск решения.
Інсталяція засобу Поиск решения
Для інсталяції засобу Поиск решения виконайте команду вкладка Разработчик,група команд Надстройки, кнопка Надстройки. Потім у групі Доступные надстройки виберіть пункт Поиск решения. Після цього натисніть кнопку ОК.
Засіб Поиск решения
Для запуску засобу Поиск решения виконайте команду вкладка Данные, група команд Анализ , кнопка Поиск решения.
Діалогове вікно Параметры поиска решения містить три групи полів, які потрібно заповнити. Це опції для цільової комірки, адреси комірок із змінними параметрами управління та поле обмежень.
У групі опцій цільової комірки зазначте адресу комірки (у полі Оптимизировать целевую функцию) і тип оптимізаційної задачі. У підгрупі До (тип задачі) є такі пункти:
максимум (задача максимізації цільової функції);
минимум (задача мінімізації цільової функції);
значения (задача рівності цільової функції конкретному числу).
За замовчуванням цільовою вважається комірка, де розміщується курсор.
Адреси змінних параметрів управління задачі задайте в полі Изменяя ячейки переменных.
Обмеження задачі задаються у групі В соответствии с ограничениями. їх вводять натисканням кнопки Добавить. Ця кнопка викликає діалогове вікно Добавление ограничения, де потрібно заповнити три поля. У лівому полі Ссылка на ячейку зазначають адресу лівої частини обмеження, у правому полі Ограничение – адресу правої частини обмеження або число, з яким порівнюється ліва частина, і в центральному полі вибирають тип обмеження: <=, =, >=, «цел» або «двоич». Останні два типи вказують, що ліва частина набуває лише цілих значень або відповідно значення 0 i l. Обмеження додають до списку обмежень, використовуючи кнопку Добавить або ОК. При цьому:
кнопка Добавить дає змогу записати наступне обмеження;
кнопка ОК закриває вікно Добавление ограничения.
Редагувати обмеження можна за допомогою кнопки Изменить.
Параметри керування засобом Поиск решения задають у діалоговому вікні Параметры поиска решения, що викликається натисканням кнопки Параметры.
Для розв’язання задачі натисніть кнопку Найти решение.
Розглянемо параметри керування (їх значення за замовчуванням наведено в дужках).
Максимальное время (100 с) – максимальний час, відведений на розв’язування задачі. Якщо за цей час Поиск решения не знайде оптимального розв’язку, він повідомить результати останньої ітерації.
Предельное количество итераций (100) – обмеження на час роботи Поиск решения у термінах максимальної кількості ітерацій алгоритму.
Относительная погрешность (0,000001) – відносна точність, з якою шукається оптимальне значення цільової комірки.
Допустимое отклонение (5 %) – допустиме відхилення значення цільової комірки від оптимального, якщо в задачі є параметри, область зміни яких обмежена цілими числами.
Параметр сходимость (0,0001). Якщо відносна зміна у п’яти останніх ітераціях менша від цього параметра, оптимізаційна задача вважається розв’язаною. Цей параметр можна застосувати тільки для нелінійних задач.
Параметр линейная модель використовує методи лінійного програмування.
Параметр Значения не отрицательны означає, що всі змінні параметри невід’ємні.
Параметр автоматическое масштабирование використовують тоді, коли значення змінних параметрів та оптимальне значення цільової комірки істотно різняться.
Параметр показывать результаты итераций виводить проміжні результати після кожної ітерації.
У нелінійних задачах у групі Оценки доцільніше вибрати опцію квадратичная.
Параметри разности і Метод поиска. Доцільніше залишити значення параметрів цих груп, що є за замовчуванням.
Аналіз результатів
Після визначення розв’язку оптимізаційної задачі підпрограма Поиск решения відкриває діалогове вікно Результаты поиска решения, звідки вибирають бажані типи звітів про розв’язок.
Для простого відображення розв’язку в робочому аркуші виберіть опцію Сохранить найденное решение.
Для відмови від отриманого розв’язку виберіть опцію Восстановить исходные значения.
Для відображення результатів на окремому аркуші виберіть тип звіту Результаты. При цьому на новому аркуші буде наведено інформацію про оптимальний план та оптимальне значення параметрів, а також про зв’язаність (рівність лівої та правої частин обмеження) чи незв’язаність обмежень.
Для отримання звіту про стійкість розв’язку щодо малих змін у цільовій функції та обмеженнях виберіть тип звіту Устойчивость. Зауважимо, що найважливішим результатом звіту про стійкість є множники Лагранжа (тіньові ціни). Множник Лагранжа для кожного обмеження вказує на миттєве покращення значення цільової функції, якщо збільшити (за умови відношення «<«) праву частину обмеження на 1.
Для аналізу допустимих змін кожного параметра за умови, що значення інших параметрів є фіксованими і такими, як в оптимальному плані, використовують тип звіту Пределы.
За допомогою миші можна вибрати кілька типів звітів одночасно.
Моделі оптимізації
Якщо на одному аркуші потрібно розв’язати кілька різних оптимізаційних задач, з кожною задачею пов’язують модель оптимізації. Модель оптимізації містить інформацію про цільову комірку, тип задачі, її змінні параметри, обмеження та параметри алгоритму. Для даних кожної моделі в аркуші потрібно відвести місце. Автоматично Excel запам’ятовує лише одну (першу) модель у кожному аркуші.
Для збереження поточної моделі (що міститься в даний момент у вікні Поиск решения) потрібно послідовно натиснути кнопки Параметры, Сохранить модель і зазначити область, де зберігатиметься модель.
Для завантаження іншої моделі (що була записана раніше) слід послідовно натиснути кнопки Параметры, Загрузить модель і зазначити адресу моделі.
У комірку D1 введіть заголовок таблиці «Вартість перевезення одиниці товару» і відцентруйте його відносно стовпчиків D:F за допомогою кнопки Объединить и поместить в центре панелі форматування. У комірки D2:F2 введіть текст відповідно «Львів», «Київ», «Харків».
У комірку С3 введіть текст «Магазин 1» і, використовуючи засіб автозаповнення, введіть у комірки С4:С8 відповідно «Магазин 2» – «Магазин 6».
В області D3:F8 таблиці введіть вартості перевезень.
Встановіть для комірок D3:F8 грошовий формат. Для цього Виділіть ці комірки і з їх контекстного меню виберіть команду Формат ячеек, вкладку Число, числовий формат Денежный. Встановіть кількість десяткових знаків «0» і виберіть позначення «грн.». В результаті таблиця «Вартість перевезення одиниці товару» розташується в області C1:F8.
У комірку С10 введіть текст «Потреби товару» так, щоб він розмістився у двох рядках. Для цього з контекстного меню комірки С10 виберіть команду Формат ячеек, перейдіть до вкладки Выравнивание і вмістіть опцію Переносить по словам.
Так само введіть у комірку G10 текст «Доставлено товарів».
Об’єднайте комірки D10:F10 і вирівняйте вміст комірки по центру як по вертикалі, так і по горизонталі. Введіть у комірку D10 текст «Кількість перевезеного товару».
Скопіюйте вміст комірок D2:F2 і вставте його в комірки D11:F11. Аналогічно скопіюйте вміст комірок СЗ:С8 і вставте його в комірки В12:В17. У комірку В18 введіть текст «Разом».
У комірках С12:С 17 побудуйте таблицю потреб у товарах для кожного магазину.
Значення комірок D12:F17 змінюватимуться в результаті виконання процедури пошуку розв’язку. Встановіть в усіх цих комірках початкове значення 25. Для цього Виділіть комірки D12:F17, введіть у рядок формул число 25 і, утримуючи клавішу <Ctrl>, натисніть клавішу <Enter>.
У комірку G12 введіть формулу =CУMM(D12:F12), за якою розраховується кількість доставлених товарів до магазину 1. Використовуючи засіб автозаповнення, введіть цю формулу в комірки G13:G17 (тобто для магазинів 2-6).
У комірці С18 розрахуйте сумарну кількість необхідних товарів за формулою =СУММ(С12:С17) і, використовуючи засіб автозаповнення, введіть її в комірки D18:G18. В результаті виконання завдань 5-12 отримаємо таблицю
/
Об’єднайте комірки В20, С20 і введіть в комірку В20 текст «Запаси товарів на складі». У комірки D20:F20 введіть дані з таблиці «Запаси товарів на складах».
У комірки D21 введіть формулу =D20-D18, за якою обчислюється кількість товарів, що залишилася на складі у Львові. Заповніть комірки Е21 та F21 аналогічними формулами, використовуючи засіб автозаповнення.
У комірку D24 введіть формулу =CУMMПPOИЗB(D3:D8;D12:D17), за якою розраховується вартість перевезень товарів зі складу у Львові до всіх магазинів. За допомогою засобу автозаповнення введіть аналогічні формули в комірки Е24 та F24. У комірку G24 введіть формулу =CУMM(D24:F24), за якою обчислюється сумарна вартість перевезень.
Запустіть процедуру Поиск решения.
Заповніть параметри Поиск решения: цільова комірка $G$24; задача мінімізації; змінюючи комірки $D$12:$F$17. Створіть такі обмеження: усі потреби магазинів у товарах повинні бути виконані, тобто кількість потрібного товару для кожного магазину (комірки С12:С17) повинна дорівнювати кількості перевезеного товару (комірки G12:G17); не можна перевозити від’ємну кількість товарів, тобто значення в комірках, що змінюються (D12:F17), не можуть бути від’ємними; кількість запасів товарів на кожному складі не може бути від’ємною (комірки D21:F21).
$C$12:$C$17=$G$12$G$17
$D$12:$F$17>=0
$D$21:$F$21>=0
Розв’яжіть наведену транспортну задачу, натиснувши кнопку Найти решение у діалоговому вікні Поиск решения.
Розрахуйте вартість перевезень за старим планом перевезень. Для цього в комірках C26:F33 побудуйте таблицю «Попередній план перевезень». При цьому числові дані таблиці розмістіть в області D28:F33. Для обчислення вартості перевезень за попереднім планом у комірку G35 введіть формулу =СУММПРОИЗВ(D28:FЗЗ; D3:F8).
Порівняйте два розглянутих плани перевезень і визначте економічний ефект від впровадження нового плану в комірці G37 за формулою =G35-G24.