Використання функції "Підбір параметра" для отримання потрібного результату за допомогою коригування вхідного значення

Якщо ви знаєте, який результат має повернути формула, і потрібно визначити, яке саме вхідне значення дає такий результат, скористайтеся функцією "Підбір параметра". Уявімо, що вам потрібно позичити трохи грошей. Ви знаєте, яка сума вам потрібна, як довго ви хотіли б виплачувати її та скільки ви готові віддавати щомісяця. Скористайтеся функцією "Підбір параметра", щоб визначити відсоткову ставку, за якої ви зможете вчасно виконати свої зобов’язання.

Якщо ви знаєте, який результат має повернути формула, і потрібно визначити, яке саме вхідне значення дає такий результат, скористайтеся функцією "Підбір параметра". Уявімо, що вам потрібно позичити трохи грошей. Ви знаєте, яка сума вам потрібна, як довго ви хотіли б виплачувати її та скільки ви готові віддавати щомісяця. Скористайтеся функцією "Підбір параметра", щоб визначити відсоткову ставку, за якої ви зможете вчасно виконати свої зобов’язання.

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

Покрокові інструкції та приклад

Розгляньмо попередній приклад, крок за кроком.

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

Підготовка аркуша

  1. Відкрийте новий чистий аркуш.

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

    1. У клітинку A1 введіть Сума позики.

    2. У клітинку A2 введіть Строк у місяцях.

    3. У клітинку A3 введіть Відсоткова ставка.

    4. У клітинку A4 введіть Платіж.

  3. Потім додайте відомі значення.

    1. У клітинку B1 введіть 100000. Це сума, яку потрібно позичити.

    2. У клітинку B2 введіть 180. Це кількість місяців, протягом яких потрібно повернути гроші.

      Примітка.: Хоча сума платежу вам відома, вводити її не потрібно, адже вона є результатом формули. Натомість, використовуючи функцію "Підбір параметра", пізніше слід ввести формулу та зазначити суму платежу.

  4. Потім додайте формулу, результат якої вам уже відомий. Ми скористаємося функцією PMT.

    1. У клітинку B4 введіть =PMT(B3/12;B2;B1). Формула обчисляє суму платежу. У цьому прикладі ви плануєте сплачувати 900 гривень щомісяця. Не потрібно вводити тут цю суму, тому що ви маєте визначити відсоткову ставку за допомогою функції "Підбір параметра", а для цього потрібно почати з формули.

      Формула містить посилання на клітинки B1 і B2, в яких зберігаються введені раніше значення. Вона також містить посилання на клітинку B3, у якій функція "Підбір параметра" виведе відсоткову ставку. Значення клітинки B3 потрібно розділити на 12, тому що ви вказали щомісячний платіж, а функція PMT припускає щорічну відсоткову ставку.

      Оскільки в клітинці B3 немає значення, у програмі Excel робиться припущення, що відсоткова ставка становить 0 %, і на основі цих значень повертається результат 555,56₴. Наразі цей результат можна ігнорувати.

Визначення відсоткової ставки за допомогою функції "Підбір параметра"

  1. На вкладці Дані у групі Знаряддя даних натисніть кнопку Аналіз "якщо" та виберіть пункт Підбір параметра.

  2. У полі указати клітинку введіть посилання на клітинку, яка містить Формула, яку потрібно вирішити. У цьому прикладі це посилання на клітинку B4.

  3. У поле Значення введіть потрібний результат формули. У нашому прикладі це –900. Зверніть увагу: число від’ємне, адже вказує на виплату.

  4. У поле Змінюючи значення клітинки введіть посилання на клітинку зі значенням, яке потрібно скоригувати. У нашому прикладі це клітинка B3.

    Примітка.:  Формула у клітинці, зазначеній у полі Установити у клітинці, має містити посилання на клітинку, значення якої змінює функція "Підбір параметра".

  5. Натисніть кнопку OK.

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

    Модель із платежем, який залежить від відсоткової ставки

  6. Нарешті, відформатуйте цільову клітинку (B3), щоб представити результат у вигляді відсотків.

    1. На вкладці Основне у групі Число натисніть кнопку Відсотковий формат.

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

Потрібна додаткова довідка?

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

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

×