Функція Microsoft Excel: пошук рішення
Однією з найбільш цікавих функцій в програмі Microsoft Excel є Пошук рішення. Разом з тим, слід зазначити, що даний інструмент не можна віднести до найпопулярнішим серед користувачів в даному додатку. А даремно. Адже ця функція, використовуючи вихідні дані, шляхом перебору, знаходить найбільш оптимальне рішення з усіх наявних. Давайте з'ясуємо, як використовувати функцію Пошук рішення в програмі Microsoft Excel.
включення функції
Можна довго шукати на стрічці, де знаходиться Пошук рішення, але так і не знайти цей інструмент. Просто, для активації даної функції, потрібно її включити в настройках програми.
Для того, щоб зробити активацію Пошуку рішень в програмі Microsoft Excel 2010 року, і більш пізніх версій, переходимо у вкладку «Файл». Для версії 2007 року, слід натиснути на кнопку Microsoft Office в лівому верхньому кутку вікна. У вікні, переходимо в розділ «Параметри».
У вікні параметрів натискаємо по пункту «Надбудови». Після переходу, в нижній частині вікна, навпаки параметра «Управління» вибираємо значення «Надбудови Excel», і натискаємо на кнопку «Перейти».
Відкривається вікно з надбудовами. Ставимо галочку навпроти назви потрібної нам надбудови - «Пошук рішення». Тиснемо на кнопку «OK».
Після цього, кнопка для запуску функції Пошуку рішень з'явиться на стрічці Excel у вкладці «Дані».
підготовка таблиці
Тепер, після того, як ми активували функцію, давайте розберемося, як вона працює. Найлегше це уявити на конкретному прикладі. Отже, у нас є таблиця заробітної плати працівників підприємства. Нам слід розрахувати премію кожного працівника, яка є твором заробітної плати, зазначеної в окремому стовпці, на певний коефіцієнт. При цьому, загальна сума грошових коштів, що виділяються на премію, дорівнює 30000 рублів. Осередок, в якій знаходиться дана сума, має назву цільової, так як наша мета підібрати дані саме під це число.
Коефіцієнт, який застосовується для розрахунку суми премії, нам належить обчислити за допомогою функції Пошуку рішень. Осередок, в якій він розташовується, називається шуканої.
Цільова і шукана осередок повинні бути пов'язані один з одним за допомогою формули. У нашому конкретному випадку, формула розташовується в цільовій комірці, і має такий вигляд: «= C10 * $ G $ 3», де $ G $ 3 - абсолютний адресу шуканої осередки, а «C10» - загальна сума заробітної плати, від якої проводиться розрахунок премії працівникам підприємства.
Запуск інструменту Пошук рішення
Після того, як таблиця підготовлена, перебуваючи у вкладці «Дані», тиснемо на кнопку «Пошук рішення», яка розташована на стрічці в блоці інструментів «Аналіз».
Відкривається вікно параметрів, в яке потрібно внести дані. В поле «Оптимізувати цільову функцію» потрібно ввести адресу цільової комірки, де буде розташовуватися загальна сума премії для всіх працівників. Це можна зробити або надрукувати координати вручну, або клікнувши на кнопку, розташовану зліва від поля введення даних.
Після цього, вікно параметрів згорнеться, а ви зможете виділити потрібну комірку таблиці. Потім, потрібно знову натиснути по тій же кнопці зліва від форми з введеними даними, щоб розгорнути вікно параметрів знову.
Під вікном з адресою цільової осередки, потрібно встановити параметри значень, які будуть перебувати в ній. Це може бути максимум, мінімум, або конкретне значення. У нашому випадку, це буде останній варіант. Тому, ставимо перемикач в позицію «Значення», і в поле зліва від нього прописуємо число 30000. Як ми пам'ятаємо, саме це число за умовами становить загальну суму премії для всіх працівників підприємства.
Нижче розташовано поле «Змінюючи осередки змінних». Тут потрібно вказати адресу шуканої осередки, де, як ми пам'ятаємо, знаходиться коефіцієнт, множенням на який основної заробітної плати буде розрахована величина премії. Адреса можна прописати тими ж способами, як ми це робили для цільової осередки.
В поле «Згідно з обмеженнями» можна виставити певні обмеження для даних, наприклад, зробити значення цілими або невід'ємними. Для цього, тиснемо на кнопку «Додати».
Після цього, відкривається вікно додавання обмеження. В поле «Посилання на осередки» прописуємо адресу осередків, щодо яких вводиться обмеження. У нашому випадку, це шукана осередок з коефіцієнтом. Далі проставляем потрібний знак: «менше або дорівнює», «більше або дорівнює», «дорівнює», «ціле число», «бінарне», і т.д. У нашому випадку, ми виберемо знак «більше або дорівнює», щоб зробити коефіцієнт позитивним числом. Відповідно, в поле «Обмеження» вказуємо число 0. Якщо ми хочемо налаштувати ще одне обмеження, то тиснемо на кнопку «Додати». У зворотному випадку, тиснемо на кнопку «OK», щоб зберегти введені обмеження.
Як бачимо, після цього, обмеження з'являється у відповідному полі вікна параметрів пошуку рішення. Також, зробити змінні невід'ємними, можна встановивши галочку біля відповідного параметра трохи нижче. Бажано, щоб встановлений тут параметр не суперечив тим, які ви прописали в обмеженнях, інакше, може виникнути конфлікт.
Додаткові настройки можна задати, якшо по кнопці «Параметри».
Тут можна встановити точність обмеження і межі рішення. Коли потрібні дані введені, тисніть на кнопку «OK». Але, для нашого випадку, змінювати ці параметри не потрібно.
Після того, як всі налаштування встановлені, тиснемо на кнопку «Знайти рішення».
Далі, програма Ексель в осередках виконує необхідні розрахунки. Одночасно з видачею результатів, відкривається вікно, в якому ви можете або зберегти знайдене рішення, або відновити вихідні значення, переставивши перемикач в відповідну позицію. Незалежно від обраного варіанту, встановивши галочку «Повернутись в діалогове вікно параметрів», ви можете знову перейти до налаштувань пошуку рішення. Після того, як виставлені галочки і перемикачі, тиснемо на кнопку «OK».
Якщо з якої-небудь причини результати пошуку рішень вас не задовольняють, або при їх підрахунку програма видає помилку, то, в такому випадку, повертаємося, описаним вище способом, в діалогове вікно параметрів. Переглядаємо всі введені дані, так як можливо десь була допущена помилка. У разі, якщо помилка знайдена була, то переходимо до параметру «Виберіть метод вирішення». Тут надається можливість вибору одного з трьох способів розрахунку: «Пошук рішення нелінійних задач методом ОПГ», «Пошук рішення лінійних задач симплекс-методом», і «Еволюційний пошук рішення». За замовчуванням, використовується перший метод. Пробуємо вирішити поставлене завдання, вибравши будь-який інший метод. У разі невдачі, повторюємо спробу, з використанням останнього методу. Алгоритм дій все той же, який ми описували вище.
Як бачимо, функція Пошук рішення являє собою досить цікавий інструмент, який, при правильному використанні, може значно заощадити час користувача на різних підрахунках. На жаль, далеко не кожен користувач знає про його існування, не кажучи про те, щоб правильно вміти працювати з цією надбудовою. У чомусь цей інструмент нагадує функцію «Підбір параметра ...» , Але в той же час, має і суттєві відмінності з ним.