ТЕМА 9.

ВИКОРИСТАННЯ ПАКЕТУ MS EXCEL ДЛЯ ПРИЙНЯТТЯ УПРАВЛІНСЬКИХ РІШЕНЬ ПРИ РОЗВ’ЯЗУВАННІ ОПТИМІЗАЦІЙНИХ ЗАДАЧ.

 

1.     Сутність та зміст задач оптимального планування.

2.     Математична постановка задач оптимізації.

3.     Допустимі розв’язки. Критерій оптимальності. Обмеження.

4.     Класифікація задач оптимізації процесів.

5.     Набір інструментів «Аналіз даних» пакету MS Excel та його можливості.

6.     Використання процедури «Пошук розв’язання» пакету MS Excel для розв’язання оптимізаційних задач.

 

1.       Сутність та зміст задач оптимального планування.

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

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

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

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

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

Економічна теорія змістовно сформулювала основні цілі, які переслідують люди, оцінюючи варіанти стратегій поведінки:

-              для однієї людинидосягнення найбільшого задоволення своїх потреб;

-              для групи організованих людей – прагнення до максимального прибутку підприємства;

-              для нації (держави) – забезпечення максимального добробуту населення.

Слід також враховувати цілі виробника і споживача:

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

-              виробник прагне досягти максимального прибутку при виборі найкращої виробничої програми.

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

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

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

Поведінка виробника чи споживача математично виражається у виборі невідомого вектора (обсягів товарів і послуг) з множини векторів:

х = (х1, х2, … , хn),                                         (9.1)

де хjобсяг jго товару (послуги) для споживача або обсяг виробництва jго виробу для виробника;

nкількість товарів (послуг), які виробляє виробник або купує споживач.

В економіці такі задачі називаються задачами оптимального планування, а в математицізадачами математичного програмування.

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

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

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

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

Оптимальний розв’язок – це найкращий розв’язок за певним критерієм при заданих обмеженнях.

Прикладами задач оптимального планування є:

а) з точки зору виробникаоптимізація виробничої програми: при наявних виробничих ресурсах і заданих нормативах витрат визначити такий план виробництва (виробничу програму), який би забезпечив отримання максимального економічного ефекту;

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

 

2.       Математична постановка задач оптимізації.

В загальному випадку задача оптимізації, яка включає три компоненти (цільову функцію F, обмеження hi і граничні умови), має таку математичну постановку:

;

;

………………………………

;                                                                                                    (9.2)

………………………………

;

; ; ,

 

де  і   нижнє і верхнє гранично допустимі значення .

 

Можна також записати задачу (9.2) в більш загальній компактній формі:

;

;                                                                                                   (9.3)

; ; .

 

3.       Допустимі розв’язки. Критерій оптимальності. Обмеження.

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

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

Критерій оптимальності виражають цільовою функцією F, або функцією цілі. Критерій в загальному випадку може оцінювати якісні властивості об’єкта, причому як бажані для суб’єкта (зазвичай з максимальним рівнем . Наприклад, прибуток, рентабельність, продуктивність, надійність та ін.), так і небажані для нього (або мінімальні . Наприклад, невиробничі витрати, витрати матеріалів, собівартість, час простою обладнання, втрати від браку та ін.).

Якщо при прийнятті рішення потрібно максимізувати деяку властивість, то в результаті розв’язання задачі критерій буде мати найбільше значення з усіх допустимих розв’язків. Якщо ж потрібно мінімізувати критерій, то в результаті розв’язку критерій буде мати найменше значення з усіх допустимих.

Можливі три види визначення цільової функції:

-              максимізація;

-              мінімізація;

-              визначення заданого значення.

Обмеження зазвичай виражають певні залежності між змінними величинами.

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

Наприклад, в економічних, технічних та інших видах розрахунків шукані величини зазвичай є додатні або рівні нулю, тобто накладається умова невід’ємності (). В ряді випадків значення величини  може бути заданим , де  - задане значення. Тоді граничні умови можна записати так:

.                                           (9.4)

 

4.       Класифікація задач оптимізації процесів.

В будь-яких математичних моделях можна виділити такі елементи:

-          вихідні дані (детерміновані чи стохастичні (випадкові));

-          залежності, які описують цільову функцію (неперервні чи дискретні);

-          обмеження (лінійні чи нелінійні).

Залежності між змінними, а також цільова функція і обмеження можуть бути лінійними і нелінійними.

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

Якщо залежності містять змінні у вищих степенях або містять добутки змінних, такі залежності називають нелінійними.

Класифікація задач оптимізації процесів представлена в таблиці 9.1.

Таблиця 9.1

Класифікація задач оптимізації процесів

Область застосування

Управління

Проектування

Розробка технологічних процесів

Прийняття рішень

Виробництво

Освіта

Культура

Бізнес

Економіка

Фінанси

Мистецтво

Сфера побуту

Задачі розподілу ресурсів (матеріальних, фінансових, інвестиційних, трудових)

1. Оптимізація параметрів об’єкта проектування

1.  Оптимізація маршруту виготовлення виробу

2. Оптимізація структури об’єкта проектування

2.  Оптимізація параметрів технологічних процесів

3. Оптимізація функціонування

3.  Вибір режиму роботи, забезпечення якості та ефективності

 

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

Таблиця 9.2

Найбільш поширені задачі оптимального планування

Вихідні дані

Змінні

Залежності

Задачі оптимізації

Детерміновані або постійні

Неперервні

Лінійні

Лінійного програмування

 

Цілочислові

 

Цілочислового програмування

 

Неперервні, цілочислові

Нелінійні

Нелінійного програмування

Стохастичні або випадкові

Неперервні

Лінійні

Стохастичного програмування

 

5. Набір інструментів «Аналіз даних» пакету MS Excel та його можливості.

Програма Excel – це не просто табличний редактор, але ще й потужний інструмент для різних математичних і статистичних обчислень. У додатку є величезна кількість функцій, призначених для цих завдань. Правда, не всі ці можливості за замовчуванням активовані. Саме до таких прихованих функцій відноситься набір інструментів «Аналіз даних».

Щоб скористатися можливостями, які надає функція «Аналіз даних», потрібно активувати групу інструментів «Пакет аналізу», виконавши певні дії в налаштуваннях Microsoft Excel. Алгоритм цих дій практично однаковий для версій програми 2010 2013 і 2016 року, і має лише незначні відмінності у версії 2007 року. На вкладці Файл слід вибрати пункт Параметри, а потім – категорію Надбудови. У полі керування вибрати пункт Надбудови Excel і натиснути кнопку Перейти. У вікні Надбудови встановити прапорець для надбудови Пакет аналізу й натиснути кнопку ОК. Після виконання цих дій зазначена функція буде активована, а її інструментарій доступний на стрічці Excel.

Тепер можна запустити будь-який з інструментів групи «Аналіз даних». У вкладці, на самому правому краю стрічки розташовується блок інструментів «Аналіз». Після натискання на кнопку «Аналіз даних», яка розміщена в ньому, запускається віконце з великим переліком різноманітних інструментів, які пропонує функція «Аналіз даних»:

-              різні види дисперсійного аналізу (однофакторний дисперсійний аналіз, двофакторний дисперсійний аналіз з повтореннями, двофакторний дисперсійний аналіз без повторень) – призначені для виконання різних видів дисперсійного аналізу. Вибір використовуваного засобу залежить від багатьох факторів та кількості вибірок із сукупностей, які потрібно проаналізувати;

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

-              коваріаціяпоказує коваріацію відповідно між кожною парою змінних;

-              описова статистика – використовується для створення одномірного статистичного звіту, який містить інформацію про основну тенденцію та мінливість даних початкового діапазону;

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

-              двовибірковий F-тест для дисперсіїзастосовується для порівняння дисперсій двох сукупностей;

-              аналіз Фур’єзастосовується для розв’язання задач у лінійних системах та аналізу періодичних даних на основі методу швидкого перетворення Фур’є;

-              гістограмавикористовується для обчислення окремих та кумулятивних частот для діапазону даних комірок та елементах даних;

-              ковзна середнявикористовується для розрахунку значень у прогнозованому періоді на основі значення змінної для вказаної кількості попередніх періодів;

-              генерація випадкових чисел – використовується для заповнення діапазону випадковими числами, здобутими з одного або декількох розподілів;

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

-              регресіявиконує лінійний аналіз регресії за допомогою методу «найменших квадратів», щоб провести лінію за набором спостережень;

-              вибіркастворює вибірку з генеральної сукупності шляхом розгляду початкового діапазону як генеральної сукупності;

-              тест Ст’юдента (парний двовибірковий t-тест для середніх, двовибірковий t-тест з однаковими дисперсіями, двовибірковий t-тест з різними дисперсіями) – перевіряє рівність середніх значень загальної сукупності за кожною вибіркою за різних припущень;

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

Вибираємо ту функцію, яку хочемо скористатися і натискуємо кнопку «OK». Робота з кожною функцією має свій власний алгоритм дій.

 

6. Використання процедури «Пошук розв’язання» пакету MS Excel для розв’язання оптимізаційних задач.

«Пошук розв’язання» – це надбудова для Microsoft Excel, яка використовується для аналізу «what-if». За її допомогою можна знайти оптимальне (максимальне або мінімальне) значення для формули, яка міститься в одній клітинці (так званій клітинці цільової функції), з урахуванням обмежень на значення формул в інших клітинках аркуша.

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

Надбудова «Пошук розв’язання» регулює значення у клітинках змінних відповідно до меж у клітинках обмежень і виводить потрібний результат у клітинці цільової функції.

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

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

Розглянемо методи розв’язання, які використовуються в надбудові «Пошук розв’язання» і які можна вибрати в діалоговому вікні Параметри пошуку розв’язання:

-              нелінійний метод узагальненого зведеного градієнта (GRG) – використовується для гладких нелінійних задач;

-              симплексний метод – використовується для лінійних задач;

-              еволюційний метод – використовується для негладких задач.