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

🧠 Excel: Пошук розв’язання — суперсила для складних задач!

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

🚀 Що це за функція?

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

Наприклад: хочеш дізнатися, скільки товару треба продати, щоб отримати максимальний прибуток — Solver допоможе!

⚙️ Як увімкнути Пошук розв’язання?

Цей інструмент не з’являється автоматично — його треба включити вручну:

1. Перейдіть у вкладку Файл (або кнопку Office у версії 2007).

2. Оберіть Параметри.

3. Відкрийте розділ Надбудови.

4. Внизу, у полі Керування, виберіть Надбудови Excel і натисніть Перейти.

5. У списку поставте галочку біля Пошук розв’язання і натисніть ОК.

Тепер інструмент з’явиться у вкладці Дані — і ви готові до дій.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

🧠 Розв’язувач — прихований скарб Excel

Як ми вже переконались, Розв’язувач — це не просто інструмент, а справжній помічник у складних обчисленнях. Він може:

• 🔍 Знаходити найкраще рішення серед багатьох варіантів

• 📈 Оптимізувати витрати, прибутки, ресурси

• 🧩 Розв’язувати задачі з обмеженнями, які вручну було б складно порахувати

🧩 Що можна робити з Solver?

• Знаходити максимальні або мінімальні значення (наприклад, прибуток, витрати).

• Розв’язувати системи рівнянь.

• Оптимізувати розподіл ресурсів.

• Враховувати обмеження (наприклад, бюджет, кількість товару, час).

🤷‍♀️ Чому ним користуються не всі?

На жаль, багато користувачів навіть не знають, що такий інструмент існує. А ті, хто знає — не завжди вміють правильно його налаштувати. І дарма! Бо він може зекономити купу часу, особливо коли мова йде про складні задачі.

💡 Висновок

Розв’язувач — це як суперсила в Excel. Вона не завжди помітна, але якщо навчитися її використовувати — можна вирішувати задачі, які здавались надто складними.

🏠 Домашнє завдання: Excel + оптимізація = 💡

🎯 Завдання

Уявіть, що ви — керівник виробництва. У вас є три види сировини і можливість виготовити чотири види продукції. Кожен продукт приносить прибуток, але для його виготовлення потрібна сировина, якої обмежена кількість.

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

📊 Що потрібно зробити?

1. Створіть таблицю в Excel:

   • Вкажіть запаси кожного виду сировини.

   • Вкажіть норму витрат сировини на кожен вид продукції.

   • Вкажіть прибуток від кожного виду продукції.

   • Додайте змінні — скільки одиниць кожного продукту плануєте виготовити.

2. Складіть математичну модель:

   • Цільова функція: максимум прибутку.

   • Обмеження: не перевищити запаси сировини.

3. Увімкніть інструмент Пошук розв’язання (Solver):

   • Перейдіть у Файл → Параметри → Надбудови → Керування → Надбудови Excel → Перейти → Пошук розв’язання → ОК.

4. Налаштуйте Solver:

   • Цільова клітинка — загальний прибуток.

   • Змінювані клітинки — кількість продукції.

   • Обмеження — витрати сировини ≤ запаси.

5. Запустіть Пошук розв’язання — Excel знайде найкраще рішення!

📂 Завершення

• Збережіть файл на Google Диску.

• Додайте посилання на нього у вашому завданні на платформі Google ClassRoom.