Урок 4. Розв’язування задач методом «Пошук розв'язання»

Функція Microsoft Excel: Пошук розв'язання (Розв'язувач)

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

Включення функції

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

Для того, щоб зробити активацію Пошук розв'язання в програмі Microsoft Excel, переходимо у вкладку «Файл». Для версії 2007 року, слід натиснути на кнопку Microsoft Office в лівому верхньому кутку вікна. У вікні, переходимо в розділ «Параметри».

У вікні параметрів натискаємо на вкладку Надбудови. Після переходу, в нижній частині вікна параметра Керування вибираємо значення Налаштування Excel, і натискаємо на кнопку Перейти.

Відкривається вікно з налаштуваннями. Ставимо галочку навпроти назви потрібного нам налаштування - Пошук розв'язання. Тиснемо на кнопку OK.

Після цього, кнопка для запуску функції Розв'язувач з'явиться на стрічці Excel у вкладці Дані.

Підготовка таблиці

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

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

Цільова і шукана комірка повинні бути пов'язані один з одним за допомогою формули. У нашому конкретному випадку, формула розташовується в цільовій комірці, і має такий вигляд: «= C8*$H$2», де $H$2 - абсолютна адреса шуканої комірки, а «C8» - загальна сума заробітної плати, від якої проводиться розрахунок премії працівникам підприємства.

Запуск інструменту Розв'язувач

Після того, як таблиця підготовлена, перебуваючи у вкладці Дані, тиснемо на кнопку Розв'язувач, яка розташована на стрічці в блоці інструментів Аналіз.

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

Під вікном з адресою цільової клітинки, потрібно встановити параметри значень, які будуть перебувати в ній. Це може бути максимум, мінімум, або конкретне значення. У нашому випадку, це буде останній варіант. Тому, ставимо перемикач в позицію Значення, і в поле зліва від нього прописуємо число 30000. Як ми пам'ятаємо, саме це число за умовами становить загальну суму премії для всіх працівників підприємства.

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

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

Після цього, відкривається вікно додавання обмеження. В поле Посилання на клітинки прописуємо адресу комірок, щодо яких вводиться обмеження. У нашому випадку, це шукана комірка з коефіцієнтом. Далі проставляємо потрібний знак: «менше або дорівнює», «більше або дорівнює», «дорівнює», «ціле число», «бінарне», і т.д. У нашому випадку, ми виберемо знак «більше або дорівнює», щоб зробити коефіцієнт невід'ємним числом. Відповідно, в поле Обмеження вказуємо число 0. Якщо ми хочемо налаштувати ще одне обмеження, то тиснемо на кнопку Додати. У зворотному випадку, тиснемо на кнопку «OK», щоб зберегти введені обмеження.

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

Додаткові налаштування можна задати у Параметрах.

Тут можна встановити точність обмеження і межі рішення. Коли потрібні дані введені, тисніть на кнопку OK. Але, для нашого випадку, змінювати ці параметри не потрібно.

Після того, як всі налаштування встановлені, тиснемо на кнопку Розв'язати.

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

Якщо з якої-небудь причини результати пошуку рішень вас не задовольняють, або при їх підрахунку програма видає помилку, то, в такому випадку, повертаємося, описаним вище способом, в діалогове вікно параметрів. Переглядаємо всі введені дані, так як можливо десь була допущена помилка. У разі, якщо помилка була знайдена, то переходимо до параметру Виберіть метод вирішення. Тут надається можливість вибору одного з трьох способів розрахунку: За методом зведеного градієнта, За симплекс-методом, Розвиваний розв'язувач. За замовчуванням, використовується перший метод. Пробуємо вирішити поставлене завдання, вибравши будь-який інший метод. У разі невдачі, повторюємо спробу, з використанням останнього методу. Алгоритм дій все той же, який ми описували вище.

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

Домашнє завдання

1. Розв'яжіть наступне завдання засобами програми Microsoft Excel:

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

2. Збережіть створений файл на вашому Google Диску та прикріпіть посилання на нього до Ваших робіт у завданні на цей урок на платформі Google ClassRoom.