Урок 5. Розв'язування рівнянь. Розв'язування  систем рівнянь, оптимізаційних задач.

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

Метод Підбір параметра

Задача 1 (стародавня). Купець придбав 138 аршин чорного та червоного сукна за 540 карбованців (крб). Скільки аршин кожного сукна придбав купець, якщо червоне коштувало 5 крб. за аршин, а чорне — 3 крб.?

Складімо математичну модель розв'язання задачі. Нехай х - кількість аршинів чорного сукна, тоді червоного сукна він придбав 138-х . Складемо рівняння 3х+5(138-х)=540.

Складімо таблицю: у її клітинках подано формули і результати їх обчислення:

Задача 2. Кредит у 1000 у. о. видано на 5 років під 20 % річних за таких умов повернення: наприкінці другого року повернути 200 у. о., наприкінці кожного наступного — по 400 у. о. Визначити, яку суму необхідно внести наприкінці першого року, щоб кредит було погашено вчасно (до кінця п’ятого року).

Кожного року борг із кредитування обчислюється за формулою:  Борг = Залишок_внеску + Залишок_внеску * Річні/100.

Погашення кредиту означає, що борг відсутній: Борг = 0.

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

Отже, якщо х — повернення грошей у перший рік, то:

борг1 = 1000 – х + 0,2(1000 – х);

борг2 = борг1 – 200 + 0,2(борг1 – 200);

борг3 = борг2 – 400 + 0,2(борг2 – 400);

борг4 = борг3 – 400 + 0,2(борг3 – 400);

борг5 = борг4 – 400 + 0,2(борг4 – 400).

Оскільки кредит необхідно сплатити за 5 років, то борг5 = 0, тобто: борг4 – 400 + 0,2(борг4 – 400) = 0.

Складіть таблицюза зразком. Обчисліть Залишок внеску, як різницю боргу та сплаченого, в стовпці С, і остаточний Борг наприкінці року — у стовпці D (формули складіть самостійно).

Метод Пошук рішення

Задача 3. Підприємство виготовляє вироби трьох видів — А, В і С. Денний дохід із кожного виду одиниці виробу — 13, 18 і 22 у. о. Необхідно визначити, за якої кількості виробів підприємство отримає максимальний дохід з урахуванням того, що:

1) загальний обсяг виробництва — усього 300 одиниць виробів на день;

2) підприємству необхідно здійснити виробництво 50 одиниць виробу А для виконання замовлення; 40 одиниць виробу В для виконання планового замовлення; а оскільки збут виробів С доволі невеликий, то слід виготовляти не більше 40 одиниць цього виробу.

Складемо математичну модель. Позначимо кількість виробів кожного типу через а, в і с. Тоді можна скласти рівняння: дохід = 13а + 18в + 22с.

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

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

а + в + с = 300;

a ≥ 50; в ≥ 40; с ≤ 40; а, в, с — цілі додатні значення.

Розв’яжемо задачу в MS Excel за допомогою надбудови Розв’язувач.

1. Скористаємося математичною моделлю для створення таблиці в середовищі Excel. У клітинки С2:С4 вводимо початкові довільні значення для виробів а, в, с. 

2. Для використання надбудови Розв’язувач її необхідно встановити на стрічку Дані за допомогою команд:  меню Файл —Параметри - Надбудови. 

3. У вікні, що відкриється, вибираємо Пакет аналізу й активуємо кнопку Перейти — відкриється вікно Надбудови.

4. Проставляємо галочку біля Розв’язувач. Підтверджуємо — ОК.

На стрічці Дані з’явилася команда Розв’язувач (Пошук рішення).

9. Після завершення введення обмежень натискаємо кнопку Знайти рішення.

10. У вікні, що відкриється, залишаємо команду Зберегти знайдений розв'язок та натискаємо ОК.

11. Дані таблиці будуть змінені: