4.2 Засіб Excel «Поиск решения»

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

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

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

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

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

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

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

Програма Поиск решения в Excel автоматично не встановлюється, тому її спочатку необхідно налаштувати за допомогою меню Сервис→Надстройки та поставити прапорець у полі Поиск решения (рис. 10.23).

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

 

 

У вікні 1 – адреса цільової комірки, 2 – значення цільової комірки, яке необхідно встановити, 3 – діапазон значень, що змінюються, 4 – вікно завдання обмежень, які можна додати за допомогою кнопки Добавить, які можна редагувати за допомогою кнопки Изменить. Кнопка Выполнить активує програму.

Для кращого розуміння даного питання розглянемо приклад.

Приклад 10.4. У таблиці на рис. 4.10 сумарна кількість товарів Тип 1 – Тип 6 становить 1610 шт., дані витрати на кожну одиницю товару різного типу. Необхідно обчислити загальну вартість товарів. Визначити кількість одиниць продукції кожного типу, яку має виготовляти фірма, якщо на даному обладнанні не можна виготовляти більше ніж 2000 одиниць продукції, якщо на виготовлення всієї продукції виділяється 500000 грн.

Розв’язання

Спочатку обчислюються загальні витрати для кожного типу товару за формулою: Загальні витрати= КількістьХВитрати на одиницю

Спочатку створюється економіко-математична модель.

Позначимо кількість одиниць продукції першого типу як , другого типу – як , третього типу –  і т. ін..

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

На значення параметрів  за умовою задачі накладаються обмеження. Оскільки виготовляється продукція, то значення комірок від В2 до В7 повинно бути цілим, оскільки половину від товару не виготовляють, а виготовляють цілу одиницю товару. Також значення цих комірок повинно бути додатним, оскільки одиниці товару не можуть бути від’ємними.

 

 

 

 

 

 

 

Запишемо обмеження математично:

 

Оскільки на даному обладнанні не можна виготовляти більше ніж 2000 одиниць продукції, то на сумарну кількість товару (комірку В8) необхідно накласти обмеження ≤ 2000.

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

Викличемо програму Поиск решения, після чого з’явиться вікно програми (рис. 10.25). У зоні Установить целевую ячейку потрібно вказати адресу цільової комірки, тобто D8, і показати, якої величини повинно бути значення комірки, тобто 500000.

У зоні Изменяя ячейки потрібно вказати адреси змінних комірок, тобто В2:В7, оскільки змінюється кількість одиниць продукції.

 

 

 

 

У зоні Ограничения потрібно занести обмеження, натиснувши на кнопку Добавить. З’явиться вікно для обмежень (рис. 10.26).

У полі Ссылка на ячейку вказується адреса комірок з обмеженнями, посередині вибирається умова (>=, <=, =, цел., двоичн.), а праворуч у полі Ограничение заноситься значення обмеження.

Кожного разу після занесення обмеження натискається кнопка Добавить, доки всі обмеження не будуть вказані, а потім натискається кнопка ОК.

Потім натискається кнопка Выполнить. Через деякий час з’являється повідомлення, що зображене на рис. 10.27.

 

 

Якщо економіко-математична модель створена неправильно (обмеження суперечать одне одному або їх недостатньо для визначення оптимального розв’язання), видається повідомлення, показане на рис. 10.28.

 

На основі знайденого розв’язання можна створити Звіт за результатами обчислень (Тип отчета – Результаты), який міститиме інформацію про попередні значення та отримані значення,  а потім натиснути ОК.

Після виконання пошуку оптимального розв’язання з’явиться таблиця з оптимальним значенням (рис. 10.29).

 

 

Звіт показаний на рис. 10.30