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

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

Інструмент Поиск решения є надбудовою Excel. Якщо в меню Сервис відсутня команда Поиск решения, треба завантажити відповідну надбудову, виконавши команду Сервис/Надстройки і в діалоговому вікні Надстройки встановити прапорець Поиск решения.

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

Засіб пошуку розв'язання, як правило, використовують для задач, що задовольняють таким умовам:

1) значення в клітинці результату залежить від значень в інших комірках або формул;

2) значення в змінних комірках належать певному діапазону або задовольняють деяким обмеженням;

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

Приклад 1

Знайти розв’язання системи нелінійних рівнянь:

у = х+4

у = x2 - 3x

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

Для пошуку розв’язків системи рівнянь методом пошуку розв’язку, слід виконати такі дії.

Спочатку потрібно скласти таблицю даних та використати статистичну функцію СРОТКЛ(число1, число2, число3, …), яка дозволяє знайти середнє абсолютних значень відхилень точок даних від середнього. У точках перетину графіків функцій середнє абсолютних значень відхилень повинно бути рівним 0.

1. В комірки А1, А2 і A3 ввести написи Рівняння системи, Середнє абсолютних значень відхилень та Корінь.

2. В комірку В1 і С1 ввести наступні формули:

=В3 + 4

=В3^2 – 3

3. В комірку В2 ввести формулу обчислення середнього абсолютних значень відхилень значень з комірок В1 і С1 від середнього:

=СРОТКЛ(В1; С1)

4. В комірку В3 ввести довільне початкове значення кореня, наприклад 15.

5. Вибрати команду Сервис/Поиск решения, в результаті чого з'явиться діалогове вікно Поиск решения.

6. В поле Установить целевую ячейку ввести посилання на комірку В2.

7. В полі Равной встановити перемикач значению, а в полі вводу залишити задане за замовчуванням значення 0.

8. В полі Изменяя ячейки задати змінну комірку (у цьому прикладі В3).

9. Клацнути на кнопці Выполнить, щоб активізувати процедуру пошуку розв'язання.

Якщо розв'язання знайдене, з'явиться діалогове вікно Результаты поиска решения, а в таблиці відобразиться обчислене значення кореня 3, 19. Встановивши відповідні перемикачі в цьому вікні, можна замінити вихідні значення в комірках новими або відновити вихідні значення в змінних комірках.

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

Приклад 2

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

Розв'язання цієї задачі допомагає з'ясувати, чи варто вкладати додаткові засоби в рекламу, щоб збільшити прибуток.

Вихідні дані задачі складаються з 2 блоків: Заплановані показники та Дані про продукцію.

У І кварталі заплановані такі показники реалізації:

• Сезонний чинник (комірка В2) - 0, 9. Сезонний чинник відображає коливання попиту на товар в залежності від пори року (наприклад, взимку частіше купують теплі речі);

• Витрати на заробітну плату персоналу (комірка В8) - 8 тис. грн.

• Витрати на рекламу (комірка В9) –10 тис. грн.

Дані про продукцію:

• Ціна реалізації (комірка В17) – 40 грн.

• Собівартість (комірка В18) – 25 грн.

Обчислення показників виконують так:

• Обсяг збуту продукції (комірка В3) залежить від сезонного чинника і витрат на рекламу:

= 35*В3*(В10+3000)^0, 5.

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

= В4*В18.

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

В6=В4*В19.

• Валовий прибуток визначається так:

=В5 – В6.

• Припустимо, “накладні витрати” фірми нараховуються в обсязі 15% прибутку з обігу, тобто в комірку В5 введемо формулу:

=0, 15 * В5.

• “валові витрати” обчислимо як суму витрат на заробітну плату персоналу, рекламу та накладні витрати, тобто в комірку В12 введемо формулу:

=СУММ(В9: В11).

• прибуток від реалізованої продукції визначимо як валовий прибуток мінус валові витрати, тобто в комірку В14 запишемо формулу:

В14 = В8 – В12.

• рентабельність:

В15 = В14/В5.

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

Активізуємо Сервис/Поиск решения та опишемо умови задачі:

• задати комірку В14 як таку, що містить цільову функцію;

• встановити мету оптимізації Максимальное значение;

• вказати комірку, значення якої змінюватиметься під час пошуку розв'язання – В10;

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

Після натиснення кнопки Выполнить отримається результат оптимізації.

Висновок. Як видно з отриманого результату, при витратах на рекламу в розмірі 17 093 грн. прибуток від реалізації товару буде максимальним. Однак слід зазначити, що максимізація прибутку не обов'язково відповідає найвищому значенню рентабельності, її значення зменшилося до 8%.

Приклад 3

Для виготовлення виробів х, у, z використовують три види сировини: І, IІ, III. У таблиці задано норми витрат сировині на один виріб кожного виду, ціна одного виробу, а також кількості сировини кожного виду, які можна використати. Скільки виробів кожного виду потрібно виготовити, щоб прибуток був максимальний?

Математична модель задачі. Позначимо через х, у, z шукані кількості виробів трьох видів. Потрібно визначити цілі значення x, y, z, для яких досягається максимум функції прибутку f = 9х + 10у + 16z за таких обмежень:

18х + 15 у + 12 z < = 360

6х + 4у + 8z < = 192

5х + 3у + 3z < = 180

x, у, z > = 0; x, у, z – цілі.

Розв'язування. Для розв'язування потрібно виконати таку послідовність дій:

1. Коміркам А1, В1, С1 присвоїти імена х, у, z, виконавши команду Вставка/Имя/Присвоить/ … ввести відповідні імена.

2. У комірку D1 ввести формулу: = 9*х + 10* у + 16* z;

3. Виконати команду Сервис/Поиск решения.

4. Задати адресу цільової комірки D1 і зазначити дію Достижение максимума фун кции.

5. Задати комірки, де має міститися розв'язок: х; у; z;

6. За допомогою кнопки Добавить додати обмеження у вигляді дев’яти умов:

х < = (360 – 15*y – 12*z) 18

у < = (192 – 6*x – 8*z)/4

z < = (180 – 5*x – 3 *y)/3

x, у, z > = 0; x, у, z – цілі.

7. Натиснути на кнопку Параметры, зазначити, що модель лінійна.

8. Натиснути кнопку Выполнить для отримання розв’язку.

(Відповідь: x = 0, у = 8, z = 20, f = 400)