Реалізація оптимізаційних моделей

Оптимізаційні моделі дають змогу вирішувати широке коло питань планування економічних процесів, знаходити найкращі (оптимальні) рішення, що відповідають певним обмеженням. Методи вибору з низки альтернативних рішень найсприятливішого (з найменшими витратами, максимальним прибутком та ін. за однакових інших умов) використовуються для вирішення багатьох маркетингових проблем: пошуку найвигіднішого асортименту за обмежених ресурсів; розрахунку оп­­тимальної величини товарних запасів; планування маршрутів руху агентів зі збуту та ін. Саме тому програма Пошук рішення, яка є в Excel, може стати потужним допоміжним інструментом у маркетинговій діяльності. Користувач може задати режим, за якого отримані значення змінних автоматично заноситимуться у таблицю і, крім того, представити результати роботи програми у вигляді звітів.

Етапи роботи з прийняття оптимальних рішень

Розв’язання задачі за допомогою програми Пошук рішення складається з таких етапів:

• постановка задачі і створення математичної моделі;

• запис задачі у табличній формі, придатній для введення даних;

• уведення даних у комп’ютер і розв’язання задачі;

• економіко-математичний аналіз отриманого рішення.

За моделювання реальної задачі робота з моделлю на цьому не закінчується. Економіко-математична модель лише з деяким наближенням відбиває реальні обставини. Тому після аналізу отриманого рішення ці обставини уточнюються, постановка задачі і модель належним чином коригуються, введені дані змінюються й програма знову запускається на виконання. Цей ітераційний процес триває, допоки не буде отримано модель, найбільш адекватну для вирішення поставленої проблеми.

Задача, розв’язувана програмою Пошук рішення, у найзагальнішому вигляді формулюється так:

знайти вектор x = (x₁, x₂, ..., x_n), який

мінімізує (або максимізує) функцію j (x) (1)

за обмежень

X_{k min} £ x _k ³ X_{k max}, k = 1, ..., n; (2)

F_{i min} £ g _i (x) £ F_{i max}, i = 1, ..., m; (3)

h _i (x) = F_j , j = 1, ..., p. (4)

Функція j (x) називається цільовою функцією, умови (2) – прямими, а умови (3) та (4) – функціональними обмеженнями.

Якщо функції j, g_j, h_j лінійні, то задача називається задачею лінійного програмування, якщо хоча б одна з них нелінійна – задачею нелінійного програмування. Моделі, які використовують тільки складання, віднімання або функцію СУММ, є лінійними. Якщо використовується множення, ділення або така функція, як експоненційна, модель є нелінійною. Приклади нелінійних задач:

• розсилання рекламних листівок звичайно веде до збільшення обсягів продажу, але у разі значного зростання їх настає насичення, за якого процентне співвідношення відповідей на відправлені листівки зменшуватиметься;

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

Якщо поряд з обмеженнями (2), (3), (4) накладається умова, щоб x_k були цілими числами, то задача називається задачею цілочисельного програмування; якщо накладається умова, щоб x_k дорівнювали 1 або 0 – то задачею двоїстого програмування.

Для застосування програми Пошук рішення необхідно побудувати модель аналізованого об’єкта чи процесу у вигляді таблиці з формулами, які відбивають задані обмеження і цільову функцію. Від форми подання цих даних і структури табличної моделі значною мірою залежать трудомісткість і безпомилковість уведення даних у програму, а також придатність моделі для обґрунтування рішень, що приймаються на підставі отриманого результату. З огляду на це часто (але не завжди) є сенс подавати модель у вигляді, принаймні, трьох секцій: секції керованих змінних; секції цільової функції; секції функціональних обмежень (якщо вони є). Орієнтовний вигляд цих секцій наведено на рис. 36 .

A	B	C	D	E
Цільова функція
Назва	Поточне значення
Змінні
Назва	Поточні значення	Межі
		макс.	мін.
Функціональні обмеження
Назва	Поточні значення	Межі
		макс.	мін.	=

Рис. 36. Основні секції моделі

Клітина колонки В секції цільової функції називається цільовою клітиною. Це клітина, в якій відбивається значення оптимізованого показника. Цільова клітина має містити формулу (або адресу клітини з формулою), результат якої змінюється залежно від значення клітин колонки В секції змінних. Програма Пошук рішення змінює значення клітин секції змінних, допоки у цільовій клітині не з’явиться результат, який потрібно отримати (якщо, звичайно, його можна отримати). У клітини колонок С i D секції змінних вносяться допустимі граничні значення змінюваних клітин (прямі обмеження).

У клітини колонки В секцїї функціональних обмежень уводяться формули для обчислення значення функцій g_i та h_j або адреси їх розташування. У колонки С, D та E цієї секції даних уводяться відповідно значення F_{i max}, F_{i min} та F_j.

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

Окрім цих секцій, у моделі можуть бути (а у більшості задач обов’язково мають бути) й інші блоки, кількість яких залежить від складності модельованих процесів і вміння розробника моделі подати їх у наочному вигляді.

Для запуску програми Пошук рішення виконується команда Сервіс/Пошук рішення. Після цього з’явиться вікно діалогу Пошук рішення. У його полі Змінюючи комірки необхідно вказати діапазон, що містить клітини, значення яких програма повинна змінити для отримання оптимального значення (клітини колонки В секції Змінні). Після активізації цього поля за допомогою миші треба виділити ці клітини або з клавіатури ввести адресу діапазону. За натискання кнопки Припустити програма виділяє клітини, на які прямо або опосередковано посилається цільова клітина.

Для того, щоб задати обмеження (як прямі, так і функціональні), треба натиснути на кнопку Додати. В результаті з’явиться наступне діалогове вікно – Додавання обмежень.

У лівому полі Посилання на клітину вказується адреса клітини або діапазону клітин, вміст яких має відповідати одному типу обмежень.

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

По закінченні введення всіх параметрів натискається кнопка ОК.

Для внесення змін та вилучення обмежень використовуються відповідно кнопки Змінити та Вилучити.

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

У разі натискання у вікні Пошук рішення кнопки Параметри з’явиться вікно Параметри пошуку рішення, в якому можна задати додаткові параметри.

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

Запуск програми на виконання обчислень здійснюється натисканням кнопки Виконати. Залежно від складності задачі і швидкості процесора пошук рішення може відібрати певний час. Окремі кроки цього процесу відображаються у нижній частині вікна Excel у рядку стану. Якщо оптимальне рішення буде знайдено, відповідні значення вставляться в таблицю і на екрані з’явиться діалогове вікно Резуль­тати пошуку рішення з інформацією про закінчення цього процесу.

Однак не завжди задача має рішення. У такому разі в діалоговому вікні замість «Рішення знайдено. Всі обмеження та умова оптимальності виконані» буде повідомлення «Пошук не може знайти слушне рішення».

Якщо рішення знайдено, то користувач повинен вибрати один із режимів (збереження отриманого рішення або поновлення початкових значень) і матиме змогу задати видачу звітів, потрібних для проведення аналізу оптимального рішення. Такий аналіз – дуже важливий етап використання Програми пошуку. Передусім це пояснюється тим, що значна частина маркетингових даних є неточною – на практиці здебільшого використовуються наближені значення. Саме тому користувач повинен з’ясувати, як зміни у даних позначаться на оптимальному рішенні. Загалом можна задати видачу звітів трьох типів: Результати, Стійкість, Межі.