Для розв’язування цієї задачі відведемо під змінні k і b комірки D2 і E2, відповідно, а в комірку F4 уведемо мінімізуючу функцію

Лабораторна робота №1

“Використання засобу ПОИСК РЕШЕНИЯ для розв’язування економічних задач”

Мета роботи. У даній роботі студенти знайомляться з новим класом задач, які властиві для економіки. Оптимізаційні задачі відіграють у роботі економіста особливу роль, бо у своїй діяльності він повсякчас стикається з проблемою вибору рішення, і не просто рішення, а того рішення, яке буде оптимальним. При розв’зуванні таких задач в Excel використовується потужний засіб ПОИСК РЕШЕНИЯ. При виконанні цієї роботи стане зрозумілою різниця між ПОДБОРОМ ПАРАМЕТРА і ПОИСКОМ РЕШЕНИЯ і умови правильного їх застосування. На формуванні дослідницьких навичок велику роль відіграють задачі на розв’язууваннясистеми нелінійних рівнянь і побудови рівняння регресії.

Зразок виконання завдань до лабораторної роботи №3.

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

1. Моделі, яка складається з множини допустимих емпіричних співвідношень між множиною змінних.
2. Точно визначеної підмножини змінних, значення яких повинні вибиратися фірмою або другою особою чи колективом.
3. Цільової функції змінних, побудованої таким чином, що більшому її значенню відповідає найкраща ситуація з точки зору данної фірми.
4. Методів аналізу впливу на значення цільової функції значень змінних, що вибираються на основі рішення.

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

1. Лінійна оптимізаційна задача[1]. Завод випускає вироби чотирьох моделей – А, Б, В і Г. Усі вироби мають необмежений збут і підприємство само має можливість планувати асортимент і величину випуска. Здержуючим фактором є три групи устаткування, плановий фонд роботи яких задано і не може бути перевищено. Відомі норми часу на обробку кожного виду виробів на устаткуванні кожної групи, а також величину прибутку, одержаної за одиницю окремих виробів. Скласти план виробництва, який забезпечить максимальний прибуток заводу.

Групи устаткування	Час у хвилинах на одиницю виробу	Місячний фонд часу (хвилин)
А	Б	В	Г
Токарна
Фрезерна
Сверлильна
Прибуток за одиницю виробу(гр.)	0,4	0,2	0,5	0,8

Розв’язування.

У нашому випадку заводу потрібно так спланувати об’єми виробництва, щоб одержати максимальний прибуток. Математична модель цієї задачі матиме вид:

максимізувати 0,4x + 0,2y + 0,5z + 0,8t

при обмеженнях x + 2y + 4z + 8t ≤ 2400;

3x + 5y + z ≤ 12000;

6x + 3y + t ≤ 30000,

де x, y, z, t – відповідно випуск виробів А, Б, В, Г.

На рисунку видно зразок розв’язування цієї задачі.

Далі виділяємо комірку А8 і виконуємо команду Сервис – Поиск решения. У полі: Установить целевую ячейку буде знаходитись А8; Равной: вибіраємо кнопку масимальному значению;в поле изменяя ячейкизаносимо

діапазон комірокА5:D5; у поле Ограничения додаємо потрібні обмеження, для цього натискуємо кнопку Добавить.На робочому аркуші з’являється вікно Добавление ограничения, яке заповнюємо і натискуємо кнопку Добавить.Після вводу останього обмеження натискуємо кнопку ОК. Тепер все готово для того, щобми могли натиснути кнопку Выполнить у вікні Поиск решения.

Результати розрахунку нашої задачі видно на наступному рисунку. Як видно з рисунка маємо слідуючий план виробництва: виробу А потрібно запланувати до випуску 4000 одиниць, виробу Г – 2500 одиниць. Вироби Б і В потрібно не випускати. Прибуток при такому плані буде дорівнювати 3600 гр.

2. Транспортна задача. Є n пунктів виробництва і m пунктів розподілу продукції. Вартість перевозки одиниці продукції з і-го пункту виробництва в j-й центр розподілу Сij наведена в таблиці, де під рядком мається пункт виробництва, а під стовпчиком – пункт розподілу. Крім цього, у цій таблиці в і-му рядку вказан об’єм виробництва в і-му пункті виробництва, а у j-му стовпчику вказан попит у j-му центрі розподілу. Потрібно скласти план перевезень по доставці потрібної продукції у пункти розподілу, який є мінімальним по сумі транспортних витрат.

Розв’язування.

У транспортній задачі потрібно зробити мінімільною вартість перевозок товарів з одних пунктів у інші[2]. Нехай є m пунктів відправлення і n пунктів призначення. Нехай q_ij– кількість вантажу, що потрібно перевезти з пункту відправлення i в пункт призначення j (i = 1, 2, …, m; j = 1, 2, …, n). Припустимо, що вартість перевозки одиниці вантажу з пункту i в пункт j дорівнює c_IJ, тоді

G =

представляє собою загальну вартість перевезень, яку потрібно мінімізувати, вибираючи m*n невід’ємних значень змінних q_ij . Об’єм вантажу, що є в і-м пункті відправлення, дорівнює а_і, а кількість товарів, яку потрібно доставити у пункт призначення j, дорівнює r_j. Таким чином, загальна кількість вантажу, що відправлена з пункту i, не перевищує а_і, тобто

, і = 1, 2, ..., m,

а загальна кількість вантажу , що завозиться у пункт j повинна бути не менше ніж r_j, тобто

, j = 1, 2, ..., n,

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

.

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

Модель транспортної задачі не збалансована, якщо сумарні об’єми виробленої продукції і потреби в них не рівні. У цьому випадку модель потрібно збалансувати, для цього існує два випадки:

· У випадку перевиробництва вводимо фіктивний пункт розподілення, вартість перевозки одиниці, який робимо рівній вартості складуванню, а об’єм перевозок об’ємом складування зайвої продукції пункту виробництва.[3]

· У випадку дефіциту вводимо фіктивний пункт виробництва, вартість перевозок дорівнює вартості штрафу, за недопоставку продукцію, а об’ємом перевозок недопоставок продукції в пункти розподілення.

У першому випадку вибираємо найменшу ціну, а у другому – штраф вибираємо по вартості найбільшої.

Основна ідея даної задачі – мінімізація перевезень між вказаними пунктами. Отже, спочатку у комірки А1:Е5 записуємо умову задачі. Дана модель являється не збалансованою, оскільки об’єм випуску не дорівнює об’єму спроса. Тому вводимо фіктивного виробника. У комірках А7:Е12 знаходиться вже збалансована задача. Під результат відводимо діапазон комірок А15:D19. У комірки A20:D20, E15:E19 заносимо відповідні формули, у комірку Е20 формулу цільової функції. Далі виділяємо комірку Е20 і виконуємо команду Поиск решения. У вікні Поиск решения робимо установки, так як це показано на рисунку, потім натискуємо

кнопку Выполнить. У комірці Е20 одержуємо результат 620, потім, віднявши від цього результату вартість віртуальної перевозки, яка дорівнює 220, у комірку F21 заносимо 400.

3. Задача про призначення. Є n робочих і m видів робіт. Вартість С_ij виконання і-м робітником j-ї роботи наведена у таблиці, де робочому відповідає рядок, а роботі стовпчик. Потрібно скласти план робіт так, щоб усі роботи були виконані, кожний робітник був зайнятий лише на одній роботі, а сумарна вартість виконання робіт була мінімальною.

Розв’язування.

Зауважимо, що дана задача є несбалансованою, оскільки число робітників більше, ніж число робіт. Перед початком розв’язування задачі потрібно її сбалансувати, тому вводимо рядок фіктивних робіт. Для вирішення цієї задачі вводимо діапазон комірок А17:Е21. Отже, в комірці F22 записуємо цільову функцію: =СУММПРОИЗВ(A9:E13;A17:E21). Решту комірок заповнюємо так, як це зроблено на малюнку. Далі виконуємо обчислення, використовуючи команду Сервис/Поиск решения. При цьому виділяємо комірку F22. Цільову комірку встановлюємо рівною мінімальному значенню. Змінюючи комірки A17:E21, вводимо обмеження, як на малюнку, натискуючи при цьому кнопку Добавить.Далі натискуємо кнопку Выполнить і отримуємо слідуючий результат: вартість виконання робіт дорівнює 24. Але цей результат враховує і віртуальну роботу. Вартість віртуальної роботи,яка дорівнює 15 вираховуємо з загальної вартості: 24 – 15 =9. Отже при даному розподіленні робіт вартість виконання даних робіт дорівнюватиме 9.

4.Система нелінійних рівнянь. Знайти всі розв’язки системи нелінійних рівнянь.

3х² + 5у² = 3

2х + 9у = 3

Розв’язування.

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

У першому рівнянні, яке є рівнянням еліпса, при y=0, х буде змінюватися від –1 до +1, при х=0 у буде змінюватись приблизно від –0,8 до +0,8.

Нагадаємо, що пара (х, у) є розв’язком системи тоді і лише тоді, коли вона є розв’язком слідуючого рівняння з двома невідомими:

(3х² + 5у² - 3)² + (2х + 9у - 3)² = 0.

У діапазонах А2:А12 і B2:H6 протабулюємо функцію відповідно по змінним х та у, як показано на рисунку. У комірку В2 заносимо таку формулу:

=(3*$A2^2+5*B$1^2-3)^2+(2*$A2+9*B$1-3)^2,

яка обчислює праву частину рівняння при значеннях х та у з комірок А2:А12 і B2:H6 у діапазоні B2:H12. Для знаходження першого кореня виберемо найменше значення по одержаній таблиці. Це буде число 0,68. Відповідні йому значення х=1 і у=0,2. Саме число 0,68 показує дійсну величину ухилу шуканої точки від одержаних значень х та у. Отже в А14 занесемо 1, а в В14 – 0,2. Слідуюче найменше значення в іншому околі буде число1,0784. УА15 занесемо -0,8, а в В15 – 0,4.

Далі у комірки С14 і С15 заносимо формули вказані на рисунку, запускаємо програму Поиск решенияі діючи анологічно, як у минулих випадках знаходимо майже точні кординати перетину прямої з еліпсом у комірках А14:В14 і А15:В15.

5. Рівняння регресії. Побудувати лінійну модель для двох величин.

Тиждень
Кількість машин

Розв’язування.

Розв’яжемо задачу нелінійної оптимізації за допомогою засобу Поиск решенияна прикладі побудови лінійного рівняння регресії. Є дві спостережувані величини х і у, де х – звітний тиждень, а у – об’єм реалізації машин за цей тиждень. Потрібно побудувати лінійну модель y=kx+b, яка найкращим чином описує спостережувані значення. Звичайно k і b підбирають так, щоб мінімизувати суму квадратів різниць між спостережуваними і теоретичними значеннями залежної змінної у, тобто мінімізувати

z= (y_i-kx_i-b)²,

де n – число спостережень (у даному випадку n=8).

Для розв’язування цієї задачі відведемо під змінні k і b комірки D2 і E2, відповідно, а в комірку F4 уведемо мінімізуючу функцію

{=СУММКВРАЗН(B2:B9;E2+D2*A2:A9)}

Дана функція обчислює суму квадратів різниць для елементів вказаних масивів. Тепер виберемо команду Сервис-Поиск решения і заповнимо вікно так як на рисунку

Відмітимо, що на змінні обмеження не накладаються. В результаті одержимо k=6,01 і b=7,07

Рівняння регресії дозволяє зробити прогноз на 9-й тиждень, для цього у рівняння у=6,01х+7,07 замість х підставляємо число 9. одержимо у=61,16, або, заокруглив у сторону збільшення, маємо у=62.