Тема 19. Електронні таблиці MS Excel: Засіб Пошук розв’язку

 В практиці часто зустрічається задача знаходження таких значень параметрів[1], при яких деяка інша величина набуває найбільшого або найменшого значення. Такими задачами, наприклад, є:

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

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

- задача оптимального розподілу товарів по магазинах з мінімальними транспортними витратами

Цей перелік можна продовжити. Задачі подібного типу та методи їх розв’язку будуть детально розглядатись в курсі Математичні методи в економіці.[2] Тут ми розглянемо розв’язання лише однієї задачі за допомогою засобу Пошук розв’язку в MS Excel, а саме – задачі планування випуску продукції при обмежених ресурсах (задачі лінійного програмування).

Задача лінійного програмування

 Нехай підприємство випускає n видів продукції: W₁, W₂, ..., W_n. При її виготовленні використовується m видів сировини: Х₁, Х₂, ..., Х_m. На одиницю продукції виду W_j витрачається сировини виду Х_i в кількості a_ij. Запаси сировини видів Х₁, Х₂, ..., Х_m на підприємстві відповідно складають b₁,b₂,...,b_m. Прибуток від реалізації одного виробу[3] виду W₁ складає c₁ грн., виробу виду W₂ – складає c₂ грн., ... , виробу виду W_n – складає c_n грн.

 Необхідно скласти план випуску продукції з наявних на підприємстві запасів сировини, щоби прибуток від її реалізації був максимальним.

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

Позначимо через x₁, x₂,..., x_n кількість одиниць продукції виду W₁, W₂, ... ,W_n відповідно. Таким чином, набір являє собою певний план випуску продукції. Прибуток, який підприємство одержить від реалізації такого плану, дорівнює f = c₁x₁+c₂x₂+...+c_nx_n. Зрозуміло, що чим більше одиниць продукції випустить підприємство, тим більше прибуток. Але кількість одиниць продукції, яку може випустити підприємство, обмежується наявними сировинними запасами.

Кількість сировини виду X_i (i=1,...,m), яка буде витрачена на реалізацію такого плану, позначимо через r_i. Тоді можна записати, що a_i1x₁+a_i2x₂+...+a_inx_n= r_i (i=1,...,m). Кількість витраченого і-го матеріалу не повинна перевищувати його наявний запас, тобто r_i £ b_i.

 Таким чином, одержуємо наступну математичну модель:

Знайти x₁, x₂, ..., x_n такі, що

 дорівнює f = c₁x₁+c₂x₂+...+c_nx_n набуває максимального значення, і при цьому, задовольняється система нерівностей

В загальному випадку цю систему нерівностей потрібно доповнити умовою невід’ємності x₁, x₂,..., x_n: . Якщо продукція є поштучною, то на потрібно ще накласти умови цілочисельності. Функція називається цільовою функцією.

 Такого виду математична модель називається задачею лінійного програмування. В задачі лінійного програмування обмеження являють собою систему лінійних рівнянь або нерівностей, а цільова функція є лінійною функцією.[4]

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

 Приклад розв’язку задачі лінійного програмування за допомогою засобу Поиск решения

Нехай підприємство випускає три види виробів А, Б, В. Вважатимемо, що вироби є штучним товаром, тобто одиниця кількості є штука, а не м, кг, л тощо. Прибуток від реалізації одного виробу складає відповідно для виробу А, Б і В - 126 грн., 123 грн., 127 грн. На виготовлення кожного виробу витрачається сировина 5-ти видів: М1, М2, М3, М4, М5. При цьому:

сировини М1 на один виріб А йде 1,4 кг, на виріб Б – 1,6 кг, на виріб В – 1,2 кг;

сировини М2 на один виріб А йде 1,5 кг, на виріб Б – 2,2 кг, на виріб В – 1,6 кг;

сировини М3 на один виріб А йде 3,2 кг, на виріб Б – 3,1 кг, на виріб В – 3,3 кг;

сировини М4 на один виріб А йде 1,9 кг, на виріб Б – 2,1 кг, на виріб В – 1,8 кг;

сировини М5 на один виріб А йде 2,4 кг, на виріб Б – 3,3 кг, на виріб В – 3,5 кг.

Запас сировини М1 складає 500 кг, сировини М2 – 480 кг, сировини М3 – 640 кг, сировини М4 – 490 кг, сировини М5 – 600 кг.

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

 Складемо математичну модель.

 Позначимо через x₁ кількість виробів виду А, через x₂ – кількість виробів виду Б, а через x₃ – кількість виробів виду В. Тоді цільова функція матиме вигляд f = 126x₁+123x₂+127x₃, а система обмежень запишеться як

 1,4x₁+1,6x₂+1,2x₃£500

1,5x₁+2,2x₂+1,6x₃£480

3,2x₁+3,1x₂+3,3x₃£640

1,9x₁+2,1x₂+1,8x₃£490

2,4x₁+3,3x₂+3,5x₃£600

x₁³0, x₂³0, x₃³0,

де х₁, х₂, х₃ – цілі.

 Потрібно знайти х₁, х₂, х₃ такі, що цільова функція досягає максимуму.

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

 Занесемо в комірки A1, B1, C1 довільні додатні числа або нулі. Це наш початковий план випуску продукції. В комірці А1 буде кількість одиниць виробу А, яку ми збираємось випускати. Аналогічно, в комірці В1 – кількість одиниць виробів виду Б, а в комірці С1 – кількість одиниць виробів В. Для визначеності, занесемо в ці комірки по одиниці.

 В комірки A2, B2, C2 занесемо прибутки від реалізації одиниці виробу виду А, Б, В відповідно, тобто числа 126, 123, 127.

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

=A1*A2+B1*B2+C1*C2

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

 Занесемо в комірки А3, В3, С3 витрати сировини М1 на одиницю продукції виду А, Б, В відповідно. Аналогічно, в комірки А4, В4, С4 занесемо витрати сировини М2, і т.д.

 В комірки D3, D4, D5, D6, D7 занесемо формули для обчислення сумарних кількостей сировини кожного виду, що буде витрачена на виготовлення запланованої кількості продукції. Тобто, в комірку D3 занесемо формулу

 = A3*A1+B3*B1+C3*C1

в комірку D4 – занесемо формулу

 = A4*A1+B4*B1+C4*C1 і т.д.

 Нарешті, в комірки Е3,.., Е7 занесемо величину запасів сировини кожного виду – в комірку Е3 заносимо 500, в комірку Е4 – 480 і т.д.

 В результаті ми одержимо наступну таблицю:

 В режимі відображення формул[5] таблиця (тобто робочий лист) має наступний вигляд:

 Робочий лист в режимі відображення формул наведено вище для наочності, щоби можна було бачити, що в яку комірку ми записали. Переходити в такий режим при розв’язанні задачі немає потреби.

Тепер, щоби знайти розв’язок, тобто план випуску продукції, який забезпечить максимальний прибуток при наявних запасах сировини, застосовуємо засіб Поиск Решения. Для цього з головного меню вибираємо Сервис Þ Поиск решения ... . На екрані відкривається діалогове вікно Поиск решения:

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

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

 Клацаємо мишею в полі Изменяя ячейки, після чого, виділяємо комірки, де знаходиться початковий план випуску продукції, тобто комірки A1, B1, C1. Клацаємо на кнопку Добавить. На екрані відкривається діалогове вікно Добавление ограничения:

 Записуємо обмеження, що накладаються на план випуску продукції обмеженими запасами сировини. В комірках D3:D7 знаходяться витрати сировини виду М1, ..., М5 відповідно, які будуть використані при реалізації плану випуску продукції, записаному в комірках А1:С1. Витрати не повинні перевищувати наявних ресурсів, тобто вмісту комірок Е3:Е7. Для запису цих обмежень, клацаємо мишею на комірці D3[6]. В полі Ссылка на ячейку з’являється абсолютна адреса цієї комірки – $D$3. В наступному полі, якщо там не стоїть знак <= (менше-дорівнює), встановлюємо його, вибравши зі списку. Список розкривається за допомогою кнопки . Далі, клацаємо мишею в полі Ограничение, щоби там з’явився курсор, після чого – на комірці Е3. В полі з’являється формула =$E$3. Таким чином, ми записали обмеження: витрати сировини М1 не повинні перевищувати її наявних запасів. Далі клацаємо на кнопці Добавить, щоби написати обмеження на витрати сировини М2: в полі Ссылка на ячейку вказуємо комірку D4, а в полі Ограничение – комірку Е4. Аналогічно записуємо обмеження на

витрати сировини виду М3, М4, М5, після чого клацаємо на кнопці Добавить.

Тепер потрібно накласти обмеження на складові плану випуску продукції – кількість одиниць продукції кожного виду повинна бути ³ 0 (не від’ємною) і, крім того, виражатись цілим числом. Отже, додатними повинні бути значення, які знаходяться в комірках А1, В1, С1. Задаємо це в діалоговому вікні Добавление ограничения. В полі Ссылка на ячейку вказуємо комірку А1, клацнувши на кнопці розкриття списку, вибираємо >= :

Клацнувши в полі Ограничение, записуємо в ньому 0. Це відповідає умові А1³0. Аналогічно записуємо умови В1³0 і С1³0. Далі, накладемо умову цілочисельності на складові плану, тобто на вміст комірок А1, В1, С1. Для цього, знову в полі Ссылка на ячейку, вкажемо комірку А1, а в списку виберемо цел. В полі Ограничение автоматично буде встановлено значення целое. Аналогічно задаємо цілочисельність В1 і С1. Після вводу умови цілочисельності вмісту комірки С1, замість клацання на кнопці Добавить, клацаємо на кнопці ОК. Вікно Добавление ограничения зникає, а натомість знову з’являється вікно Поиск решения:

Для знаходження плану, який забезпечить максимальний прибуток, достатньо клацнути на кнопці Выполнить. В таблиці, в комірках А1, В1, С1 буде знаходитись оптимальний план випуску продукції (замість початкового). Крім того, на екран буде виведено діалогове вікно Результаты поиска решения:

 

Якщо вибрати опцію Сохранить найденное решение і клацнути на кнопці ОК, то на робочому листі зафіксується знайдений розв’язок. Тобто, робочий лист буде мати вигляд як на малюнку вгорі. Якщо вибрати опцію Восстановить исходные значения, то ми вернемось до того вигляду робочого листа, який він мав до застосування засобу Поиск решения.

 При виконанні 8-го завдання контрольної роботи, потрібно вибрати опцію Сохранить найденное решение. В разі відсутності розв’язку (засіб Поиск решения виводить в такому випадку відповідне повідомлення) потрібно в робочому листі, в вільній комірці, наприклад, А8 вставити текст: "Розв’язок не знайдений".

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

 Прокоментуємо тепер знайдений розв’язок.

 В комірках А1, В1, С1 знаходиться оптимальний план. Згідно цього плану, для досягнення максимального прибутку, потрібно випустити 81 виріб виду А, 122 вироби виду Б, а вироби виду В не випускати взагалі. При реалізації такого плану підприємство одержить прибуток 25212 грн. В комірці D3 будуть знаходитись кількість сировини виду М1, яка піде на реалізацію плану; аналогічно в комірках D4, ..., D7 – витрати сировини відповідного виду.

[1] тобто, незалежних величин, що фігурують у задачі.

[2] Курс може називатись, звичайно, і іншим чином, наприклад, Економіко-математичні методи і т.п.

[3] точніше одиниці продукції, оскільки продукція не обов’язково повинна бути поштучною – її кількість може вимірюватись в кг, м і т.п.

[4] Лінійна функція в загальному випадку має вигляд y=a₁x₁+a₂x₂+...+a_nx_n+a₀, де a₁, a₂, ... , a_n - деякі сталі. Коли говорять про лінійну систему рівнянь чи нерівностей, то це значить, що права і ліва частина кожного рівняння (нерівності) є лінійними функціями.

[5] Режим відображення формул відображає в комірках не значення, обчислені за формулами, а самі формули. Для переходу в цей режим потрібно натиснути комбінацію клавіш Ctrl+`. Для повернення в звичайний потрібно ще раз натиснути цю комбінацію клавіш.

[6] Якщо в полі Ссылка на ячейку немає курсору, потрібно спочатку клацнути мишею на цьому полі.