Використовуємо пошук рішень в Excel 2010 для вирішення складних завдань

Значна частина завдань, які вирішуються за допомогою електронних таблиць, припускають, що для виявлення потрібного результату у користувача вже є хоч якісь вихідні дані. Однак Exсel 2010 має в своєму розпорядженні необхідними інструментами, за допомогою яких можна вирішити цю задачу навпаки - підібрати потрібні дані, щоб отримати необхідний результат.

«Пошук рішення» і є одним з таких інструментів, максимально зручних для «завдань оптимізації». І якщо раніше вам ще не доводилося його використовувати, то зараз саме час виправити це.

Отже - починаємо з установки даної надбудови (оскільки самостійно вона не з'явиться). На щастя зараз зробити це можна досить просто і швидко - відкриваємо меню «Сервис», а вже в ньому «компонентами»

Залишиться тільки в графі «Управління» вказати «Надбудови Excel», а після натиснути кнопочку «Перейти».

Після цього нескладного дії кнопка активації «Пошуку рішення» буде відображатися в «Даних». Як і показано на зображенні

Давайте розглянемо, як правильно використовується пошук рішень в Excel 2010, на кількох простих прикладах.

Приклад перший.

Припустимо, що ви займаєте посаду начальника великого відділу виробництва та необхідно правильно розподілити премії співробітникам. Припустимо, загальна сума премій становить 100 000 рублів, і необхідно, щоб премії були пропорційні окладів.

Тобто, зараз нам необхідно підібрати правильний коефіцієнт пропорційності, щоб визначити розмір премії щодо окладу.

В першу чергу необхідно швидко скласти (якщо її ще немає) таблицю, де будуть зберігається вихідні формули і дані, згідно з якими і можна буде отримати бажаний результат. Для нас цей результат - сумарна величина премії. А тепер увага - цільова осередок С8 повинна бути з допомогою формул пов'язана з шуканої змінної осередком під адресою Е2. Це критично. У прикладі ми пов'язуємо їх використовуючи проміжні формули, які і відповідають за вираховування премії кожному співробітнику (С2: С7).

Тепер можна активувати «Пошук рішень». Відкриється нове вікно, в якому нам необхідно вказати необхідні параметри.

Під «1» позначена наша цільова осередок. Вона може бути тільки одна.

«2» - це можливі варіанти оптимізації. Всього можна вибрати «Максимальна», «Мінімальна» або «Конкретне» можливі значення. І якщо вам необхідно саме конкретне значення, то його потрібно вказати у відповідній графі.

«3» - змінюваних осередків може бути кілька (цілий діапазон або ж окремо зазначені адреси). Адже саме з ними і буде працювати Excel, перебираючи варіанти так, щоб вийшло значення, задане в цільовій комірці.

«4» - Якщо знадобитися задати обмеження, то варто скористатися кнопкою «Додати», але ми це розглянемо трохи пізніше.

«5» - кнопка переходу до інтерактивних обчислень на основі заданої нами програми.

Але тепер повернемося до можливості змінювати наше завдання, скориставшись кнопкою «Додати». Даний етап є досить відповідальним (не менше ніж побудова формул), оскільки саме обмеження дозволяють отримати правильний результат на виході. Тут все зроблено максимально зручно, так що поставити їх ви зможете не тільки для всього діапазону відразу, але і для певних осередків.

Для цього можна використовувати ряд певних (і знайомих усім користувачам Excel 2010) знаків «=», «> =», «<=», а також варіанти «цілий» (від «ціле»), «бін» ( «бінарне» або ж «двоичное»), «раз» ( «всі різні»).

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

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

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

Правильне рішення задачі прикладу повинно вийти ось таким

Дуже важливо - щоб отримати правильний результат навіть при найменшій зміні вихідних даних необхідно перезапустити «Пошук рішень».

Щоб більш детально поглянути на те, як діє ця програма, давайте розберемо ще один приклад.

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

Модель «А» вимагає 3 м3 дощок, а модель «В» - на 1 м3 більше (тобто - 4). Від своїх постачальників ви за тиждень отримуєте максимум 1700 м3 дощок. При цьому модель «А» створюється за 12 хвилин роботи верстата, а «В» - за 30 хвилин. Всього в тиждень верстат може працювати не більше 160 годин.

Питання - скільки всього виробів (і якої моделі), повинна випускати фірма за тиждень, щоб отримати максимально можливий прибуток, якщо поличка «А» дає 60 рублів прибутку, а «В» - 120?

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

Будь-яким зручним способом запускаємо наш «Пошук рішень», вводимо дані, виробляємо настройку.

Отже, розглянемо те, що ми маємо. У цільовій комірці F7 міститься формула, яка і розрахує прибуток. Параметр оптимізації встановлюємо на максимум. Серед змінних осередків у нас значиться «F3: G3». Обмеження - всі виявлені значення повинні бути цілими числами, невід'ємними, загальна кількість витраченого машинного часу не перевищує позначку 160 (наша осередок D9), кількість сировини не перевищує 1700 (осередок D8).

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

Активуємо програму, і вона готує рішення.

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

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

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

Так що будьте гранично уважні в майбутньому.

Третій і, мабуть, останній приклад. Спробуємо мінімізувати витрати транспортної компанії використовуючи пошук рішень в Excel 2010.

Отже, будівельна компанія дає замовлення на перевезення піску, який береться від 3 постачальників (кар'єрів). Його необхідно доставити 5 різним споживачам (якими виступають будівельні майданчики). Вартість доставки вантажу включена в собівартість об'єкта, так що наше завдання забезпечити доставку вантажу на будмайданчики з мінімальними витратами.

Ми маємо - запас піску в кар'єрі, потреба будмайданчиків в піску, витрату на транспортування «постачальник-споживач».

Необхідно знайти схему оптимальної перевезення вантажу (куди і звідки), при якій загальна витрата на перевезення була б мінімальною.

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

Після цього приступаємо до пошуку вирішення цього завдання

Втім, не будемо забувати, що досить часто транспортні завдання можуть бути ускладнені деякими додатковими обмежувачами. Припустимо, виникло ускладнення на дорозі і тепер з кар'єру 2 просто технічно неможливо доставити вантаж на будмайданчик 3. Щоб врахувати це, необхідно просто дописати додаткове обмеження «$ D $ 13 = 0». І якщо тепер запустити програму, то результат буде іншим

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

Ось і все з даного питання.

Ми виконали пошук рішень в Excel 2010 - для вирішення складних завдань

Дивіться також:

Вам сподобався матеріал?
Поделітeсь:

Повернутися в початок статті Використовуємо пошук рішень в Excel 2010 для вирішення складних завдань