Функція "Підбір параметра" в "Ексель". Аналіз "що якщо"

Можливості програми Excel такі, що вони дозволяють вирішувати завдання, які виконати вручну неможливо або занадто складно. Це можуть бути трудомісткі завдання з перебором величезної кількості варіантів або обчислення великих чисел. Знаючи можливості Excel, вирішити їх не складе труднощів.

Функція підбору параметра

Прикладом такої трудомісткої і практично нерозв'язної вручну завдання є наступна. Вам відомо, який результат обчислень необхідно отримати, які обчислення до нього призводять, але невідома відправна точка – значення вихідних даних. Іноді можна провести зворотні дії, а іноді така задача може поставити в глухий кут. Excel пропонує метод вирішення такої проблеми, який носить назву підбору параметра. Виклик функції знаходиться на вкладці «Дані» панелі інструментів «Робота з даними». У версіях, починаючи з MS Excel 2007 - «Аналіз "що якщо"», пункт меню «Підбір параметра».


Для функції задають атрибути: вказують клітинки, в яких потрібно змінювати для досягнення необхідного результату, і поля, в яких знаходиться результат підбору. Ще один атрибут функції – значення, яке потрібно отримати. Як видно на картинці, формула дозволяє вводити для зміни і підстановки тільки один аргумент. Це не завжди задовольняє користувача. У випадку, якщо потрібно підбирати кілька значень для отримання потрібного результату, підбір параметрів «Ексель» не підходить. У цій ситуації застосовується спеціальна надбудова, на якій ми зупинимося нижче.

Розрахунок суми позики

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


У «Ексель» 2007 є відповідна функція для розрахунку щомісячних платежів по позиці з відомими відсотками і терміном. Вона називається СПЛ. Синтаксис команди: PMT(ставка; кпер; пс;[бс];[тип]), де: Ставка – проценти за позикою. Кпер – число оплат (для річного кредиту у разі щомісячної оплати це 12 разів). ПС – початкова сума. БС – майбутня вартість (якщо ви маєте намір виплатити не всю суму, а лише її частина, тут вказується, який борг повинен залишитися). Це необов'язковий аргумент, за замовчуванням він дорівнює 0. Тип – коли виробляється оплата – на початку місяця або в кінці. Цей параметр не обов'язково вказувати, якщо він не заповнений, приймається рівним 0 що означає оплату в кінці місяця. Слід враховувати, що в атрибуті «Ставка» вказується не річний відсоток, а щомісячний, тому відому нам відсоткову ставку необхідно розділити на кількість платежів на рік – 12. Внесемо на лист «Ексель» 2007 потрібні дані. В якості початкової суми пропишемо поки умовні 100000 рублів і займемося знаходженням реальної суми. Викликаємо діалогове вікно підбір параметра. Відправною точкою для знаходження є сума щомісячного платежу. Формула СПЛ повертає негативні дані, так що ми вводимо число зі знаком «мінус»: - 7000 рублів в полі «Значення». Цю суму ми повинні отримати в комірці з платежем, змінюючи інформацію в поле з позикою.
Прописуємо все це у вікні і запускаємо підбір параметра «Ексель». У результаті функція розрахувала, який позика ми можемо собі дозволити - 7962156 руб.

Визначення процентної ставки

Розглянемо тепер зворотну задачу. Банк видає позику в 100 тис. рублів на 2 роки і хоче отримати дохід у 10 тис. рублів. Яку мінімальну процентну ставку потрібно встановити для отримання прибутку? До вже наявної на аркуші інформації додаємо рядок »Прибуток». Вона розраховується за формулою: Прибуток = |Платіж|*Термін-Сума Встановлюємо термін 24 місяці. Зверніть увагу на поле «Ставка». Числове значення має виражатися у відсотках. Для цього виберіть числовий формат «Відсоток» в Excel: вкладка «Головна» - панель інструментів «Число» - кнопка з зображенням відсотка. Викликаємо функцію підбору і ставимо її аргументи. Очікуваний результат буде записаний в полі «Прибуток» зміною значення у клітинці «Ставка» і складати 10000 рублів. Після запуску програма показує необхідний відсоток, рівний 932354423334073 %.

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

Як вже було сказано вище, функція підбору параметра в «Ексель» не завжди задовольняє вимогам задачі. Якщо потрібно підібрати кілька аргументів, використовується надбудова під назвою «Пошук рішення». З її допомогою можна отримати оптимальний результат методом підбору декількох параметрів.
Перевірте, чи доступна вона для використання: вкладка «Дані», панель інструментів «Аналіз». Якщо в програмі немає такої панелі або на ній відсутня потрібна команда, активуйте її. Зайдіть в настройки Excel (кнопка Microsoft Office Excel 2007 меню «Файл» у версіях 2010 і вище) і знайдіть пункт «Надбудови». Перейдіть до керування надбудовами і встановіть прапорець на елементі «Пошук рішення». Тепер функція активована.

Транспортна задача

Класичне застосування методу – рішення транспортної задачі. Припустимо, компанія зберігає товари на декількох складах і доставляє їх в декілька магазинів. Стоїть питання, з яких складів доставляти продукцію в які магазини, щоб витрати на транспортування були мінімальні. Задамо вхідні дані. Для цього побудуємо таблиці з інформацією про витрати на доставку, обсязі товарів на кожному зі складів і необхідну кількість товарів для кожної торгової точки. Будемо підбирати такі значення поставок, щоб дотримувалися умови: 1) Повні витрати були мінімальні. 2) Сумарні поставки товарів в торгові точки задовольняли вимогам. 3) Сумарний вивезення продукції зі складів не перевищував наявні запаси. 4) Кількість одиниць продукції має бути цілим і невід'ємним. Результат пошуку рішення.

Інші способи аналізу даних

Крім перерахованих вище варіантів, є й інші методи аналізу даних. Вони перебувають у пункті меню «Аналіз "що якщо"». Це «Диспетчер сценаріїв» і «Таблиця даних».

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