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

Урок 13-14. Розв’язування рівнянь, систем рівнянь, оптимізаційних задач. Практична робота «Розв’язування оптимізаційних задач».

Навчальна презентація до теми уроку

(Дії з матрицями)

(Викачати із сервера)

Навчальна презентація за підручником Ривкінд Й.Я.

Тема 2. п. 2.5. Оптимізаційні задачі (із інструкціями для виконання практичних завдань)

Завдання практичної роботи (з інструкціями). Робота з математичним процесором GRAN1

Теоретичний матеріал по роботі з математичним процесором GRAN1 на сторінці уроку 16-17

Застосування функцій роботи з матрицями

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

А * Х = В, де А – матриця коефіцієнтів; Х – матриця-стовпець невідомих; В – матрицястовпець вільних членів:

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

Для виконання цієї роботи Excel пропонує декілька функцій, зібраних у категорії «Математичні», наприклад:

  • МОБР – повертає обернену матрицю;
  • МОПРЕД – обчислює визначник матриці;
  • МУМНОЖ – перемножує матриці;
  • ТРАНСП – повертає транспоновану матрицю.

Примітка. Є дві особливості використання таких функцій. По-перше, перед використанням функції треба виділити на робочому аркуші діапазон саме такого розміру, який відповідає результату обчислень. По-друге, після того як будуть визначені всі аргументи функції, вихід із «Майстра функцій» здійснюється шляхом натиснення комбінації клавіш Ctrl + Shift + Enter, а не кнопки «ОК» у діалоговому вікні. Розглянемо розв’язання задачі з використанням цих функцій.

Приклад. Кондитерська фабрика спеціалізується на виробництві трьох фірмових тортів Т1, Т2, Т3, для чого використовує сировину трьох типів С1, С2 та С3. Витрати сировини на кожен кондитерський виріб та щоденні запаси сировини задані табл. 8 (всі значення подані у гривенному еквіваленті). Знайти щоденний грошовий обсяг виробництва тортів кожного виду.

Нехай х1, х2, х3 – щоденну вартість тортів відповідно першого, другого та третього виду. В такому разі відповідно до норм витрат сировини кожного типу маємо систему лінійних рівнянь:

У матричній формі ця система має вигляд А * Х = В

За допомогою Excel знаходимо обернену матрицю та її добуток на В. Одержуємо результати

Розв’язання задач оптимізації засобами Excel

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

Підбір параметра формули

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

  • обрати пункт меню Сервіс / Підбір параметра (Сервис / Подбор параметра);
  • у діалоговому вікні «Підбір параметра» в полі «Встановити в комірці» («Установить в ячейке») зазначити адресу комірки з формулою, результат якої відомий;
  • у полі «Значення» («Значение») указати цей відомий результат;
  • у полі «Змінюючи значення в комірці» («Изменяя значение в ячейке») вказати адресу комірки, у якій міститься параметр формули, значення якого слід обчислити.

Приклад. Є деякі дані про видачу банком кредитів за ставкою 5%. Визначити, яким має бути відсоток ставки, щоб банк зміг одержати прибуток 20 000 грн, якщо прибуток задається різницею між сумами повернення та виданими сумами.

  1. Насамперед треба переконатися, що значення стовпця повернених сум розраховані за формулою ПОВЕРНЕНО = ВИДАНО (1+ВІДСОТОК), тобто для комірки D3 маємо = С3*(1+С$9).
  2. Слід звернути увагу на те, що комірки зі значеннями прибутку, виданих та повернених сум мають формат Грошовий, а комірка зі значенням відсотка – Відсотковий.
  3. Для інших комірок діапазону D3:D6 використовуємо автозаповнення.
  4. Комірки С7 і D7 містять суми розташованих вище значень.
  5. Розрахунок прибутку в комірці С10 повинен здійснюватися за формулою = D7-С7.

Цю задачу можна розв’язати кількома способами, зокрема, за допомогою «Підбору параметра». Для цього достатньо в полі «Встановити в комірці» зробити посилання на комірку С10, у полі «Значення» записати 20 000, а в полі «Змінюючи значення в комірці» дати посилання на комірку С9. Результат становить 11,92 %.

Пошук розв’язку

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

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

Для пошуку розв’язку необхідно:

  • обрати пункт меню Сервіс / Пошук розв’язку (Сервис / Поиск решения);
  • у полі «Встановити цільову комірку» («Установить целевую ячейку») діалогового вікна «Пошук розв’язку» зазначити адресу комірки з відомим результатом обчислень (або таким, який треба мінімізувати чи максимізувати);
  • відповідно до мети розв’язання задачі встановити перемикач у положення Мінімальному значенню (Минимальному значению), Максимальному значенню (Максимальному значению), Значенню (Значению) (вказати конкретне значення);
  • у полі «Змінюючи комірки» («Изменяя ячейки») вказати діапазон із значеннями, які треба обчислити;
  • у полі «Обмеження» («Ограничения») за допомогою кнопки «Додати» («Добавить») додати обмеження відповідно до змісту задачі;
  • натиснути кнопку «Виконати» («Выполнить»).

Приклад. Підприємство виробляє фарбу для внутрішніх та зовнішніх робіт із сировини двох типів ─ С1 і С2. У табл. наведені дані щодо витрат сировини на виробництво кожного виду фарби, добовий запас сировини та прибуток, який отримає підприємство після реалізації 1 т фарби. Відділ маркетингу обмежив щоденне виробництво фарби для внутрішніх робіт до 2 т через відсутність попиту, а також виявив, що щоденне виробництво фарби для зовнішніх робіт не повинне перевищувати аналогічний показник виробництва фарби для внутрішніх робіт більше ніж на 1 т. Знайти оптимальне співвідношення між видами продукції, яку виробляє підприємство з метою отримання максимального щоденного прибутку.

Для розв’язання задачі створюємо електронну таблицю:

Для комірок стовпця Е застосовуємо формулу, яка для комірки Е3 має вигляд = В3*В$6+С3*С$6.

  1. Загальний прибуток (комірка В7) обчислюється за формулою = В5*В6+С5*С6.
  2. Далі обираємо пункт меню Сервіс / Пошук розв’язку, в полі «Встановити цільову комірку» зазначаємо $В$7, встановлюємо перемикач у положення Максимальному значенню, у полі «Змінюючи комірки» вказуємо діапазон $В$6:$С$6, у полі «Обмеження» за допомогою кнопки «Додати» додаємо такі обмеження: $В$6<=$C$6+1, $В$6:$C$6>=0, $C$6<=2, $E$3:$E$4<=$D$3:$D$4.
  3. Натискаємо кнопку «Виконати», після чого електронна таблиця набуває іншого вигляду:

Це означає, що оптимальним рішенням буде щоденне виробництво 2,667 т фарби для зовнішніх робіт та 1,667 т фарби для внутрішніх робіт зі щоденним прибутком 20 тис. грн.