Goal Seek, частина інструментарію Excel для аналізу «що-якщо», є потужною функцією, яка використовується для зворотних обчислень. По суті, якщо ви знаєте бажаний результат формули, але не впевнені, яке вхідне значення дасть цей результат, ви можете скористатися Goal Seek, щоб знайти його. Незалежно від того, чи ви фінансовий аналітик, студент чи власник бізнесу, розуміння того, як використовувати Goal Seek, може значно спростити ваш процес вирішення проблем. У цьому посібнику ми розглянемо, що таке Goal Seek, як до нього отримати доступ, і продемонструємо його практичне застосування на різних прикладах, починаючи від коригування планів виплати кредиту до розрахунку мінімальних балів за іспити тощо.

• Приклад 1: Коригування плану погашення кредиту
• Приклад 2: Визначення мінімального балу для складання іспиту
• Приклад 3: Підрахунок необхідної кількості голосів для перемоги на виборах
• Приклад 4: Досягнення цільового прибутку в бізнесі

Що таке Goal Seek в Excel?

Мета пошуку є невід’ємною функцією Microsoft Excel, частиною набору інструментів аналізу «що, якщо». Це дає змогу користувачам виконувати зворотні обчислення – замість обчислення результату заданих вхідних даних ви вказуєте бажаний результат, а Excel коригує вхідне значення для досягнення цієї мети. Ця функція особливо корисна в сценаріях, коли вам потрібно знайти вхідне значення, яке дасть певний результат.

• Регулювання однієї змінної:
  Goal Seek може змінювати лише одне значення комірки введення за раз.
• Потрібна формула:
  Під час застосування функції пошуку цілі цільова комірка має містити формулу.
• Зберігайте цілісність формули:
  Goal Seek не змінює формулу в цільовій клітинці; він змінює значення у клітинці введення, від якого залежить формула.

Доступ до Goal Seek в Excel

Щоб отримати доступ до функції пошуку цілі, перейдіть до дані вкладка, клацніть Що-якщо аналіз > Мета пошуку. Дивіться знімок екрана:

Тоді Мета пошуку з’явиться діалогове вікно. Ось пояснення трьох параметрів у діалоговому вікні пошуку мети Excel:

• Встановити комірку: цільова клітинка, яка містить формулу, яку ви хочете отримати. Ця клітинка має містити формулу, і Goal Seek відкоригує значення в іншій пов’язаній клітинці, щоб значення цієї клітинки досягло вашої встановленої цілі.
• Оцінювати: цільове значення "Встановити комірку" досягати.
• Змінивши комірку: комірка введення, яку потрібно налаштувати в Goal Seek. Значення в цій клітинці буде змінено, щоб забезпечити значення в "Встановити комірку"зустрічає"Оцінювати" ціль.
  
  

У наступному розділі ми розглянемо, як використовувати функцію пошуку цілі в Excel на докладних прикладах.

Використання Goal Seek із прикладами

У цьому розділі ми розглянемо чотири поширені приклади, які демонструють корисність Goal Seek. Ці приклади варіюються від коригування особистих фінансів до стратегічних бізнес-рішень, пропонуючи зрозуміти, як цей інструмент можна застосувати в різних сценаріях реального світу. Кожен приклад створено, щоб дати вам більш чітке розуміння того, як ефективно використовувати Goal Seek для вирішення різних типів проблем.

Приклад 1: коригування плану виплат за кредитом

сценарій: особа планує взяти кредит у розмірі 20,000 5 доларів США та має на меті погасити його протягом 5 років за річною ставкою 377.42%. Необхідний щомісячний платіж становить $500. Пізніше він розуміє, що може збільшити свою щомісячну виплату на XNUMX доларів. Враховуючи, що загальна сума кредиту та річна відсоткова ставка залишаються незмінними, скільки років тепер потрібно для погашення кредиту?

Як показано в наступній таблиці:

• B1 містить суму позики $20000.
• B2 містить термін позики 5 (років).
• B3 містить річну процентну ставку 5%.
• B5 містить щомісячний платіж, який розраховується за формулою =PMT(B3/12,B2*12,B1).
  
  

Тут я покажу вам, як використовувати функцію пошуку цілі для виконання цього завдання.

Крок 1. Активуйте функцію пошуку цілі

Перейти до дані вкладка, клацніть Що-якщо аналіз > Мета пошуку.

Крок 2: Налаштуйте параметри Goal Seek

1. У Встановити комірку виберіть формулу B5.
2. У Оцінювати введіть щомісячну заплановану суму погашення -500 (від'ємне число означає платіж).
3. У Змінюючи комірку виберіть клітинку B2, яку потрібно налаштувати.
4. Натисніть OK.
   
   

Результат

Команда Статус пошуку мети з’явиться діалогове вікно, яке показує, що рішення знайдено. У той же час значення в клітинці B2, яке ви вказали в полі «Змінюючи клітинку», буде замінено на нове значення, а клітинка формули отримає цільове значення на основі зміни змінної. Натисніть OK застосувати зміни

Тепер на погашення кредиту йому знадобиться близько 3.7 років.

Приклад 2: Визначення мінімального балу для складання іспиту

сценарій: Студент повинен скласти 5 іспитів, кожен з яких має однакову вагу. Щоб скласти, студент повинен набрати середній бал 70% за всіма іспитами. Склавши перші 4 іспити та знаючи свої бали, студент тепер має розрахувати мінімальний бал, необхідний на п’ятому іспиті, щоб переконатися, що загальний середній бал сягає або перевищує 70%.

Як показано на знімку екрана нижче:

• B1 містить оцінку першого іспиту.
• B2 містить оцінку другого іспиту.
• B3 містить оцінку третього іспиту.
• B4 містить оцінку четвертого іспиту.
• B5 міститиме оцінку п’ятого іспиту.
• B7 — прохідний середній бал, який розраховується за формулою =(B1+B2+B3+B4+B5)/5.
  
  

Щоб досягти цієї мети, ви можете застосувати функцію Goal Seek таким чином:

Крок 1. Активуйте функцію пошуку цілі

Перейти до дані вкладка, клацніть Що-якщо аналіз > Мета пошуку.

Крок 2: Налаштуйте параметри Goal Seek

1. У Встановити комірку виберіть клітинку формули B7.
2. У Оцінювати в поле введіть прохідний середній бал 70%.
3. У Змінюючи комірку виберіть клітинку (B5), яку потрібно налаштувати.
4. Натисніть OK.
   
   

Результат

Команда Статус пошуку мети з’явиться діалогове вікно, яке показує, що рішення знайдено. У той же час клітинка B7, яку ви вказали у полі «Змінюючи клітинку», буде автоматично введена з відповідним значенням, а клітинка формули отримає цільове значення на основі зміни змінної. Натисніть OK щоб прийняти рішення.

Отже, щоб досягти необхідного загального середнього рівня та скласти всі іспити, студент повинен набрати не менше 71% останнього іспиту.

Приклад 3: Підрахунок необхідних голосів для перемоги на виборах

сценарій: Під час виборів кандидат повинен підрахувати мінімальну кількість голосів, необхідну для отримання більшості. З відомою загальною кількістю поданих голосів, яку мінімальну кількість голосів має набрати кандидат, щоб набрати понад 50% і перемогти?

Як показано на знімку екрана нижче:

• Комірка B2 містить загальну кількість голосів, поданих на виборах.
• Комірка B3 показує поточну кількість голосів, отриманих кандидатом.
• Комірка B4 містить бажану більшість (%) голосів, яку має отримати кандидат, яка обчислюється за формулою =B2/B1.
  
  

Щоб досягти цієї мети, ви можете застосувати функцію Goal Seek таким чином:

Крок 1. Активуйте функцію пошуку цілі

Перейти до дані вкладка, клацніть Що-якщо аналіз > Мета пошуку.

Крок 2: Налаштуйте параметри Goal Seek

1. У Встановити комірку виберіть клітинку формули B4.
2. У Оцінювати введіть бажаний відсоток більшості голосів. У цьому випадку для перемоги на виборах кандидат повинен набрати більше половини (тобто понад 50%) від загальної кількості дійсних голосів, тому я вводжу 51%.
3. У Змінюючи комірку виберіть клітинку (B2), яку потрібно налаштувати.
4. Натисніть OK.
   
   

Результат

Команда Статус пошуку мети з’явиться діалогове вікно, яке показує, що рішення знайдено. У той же час клітинка B2, яку ви вказали в полі «Змінюючи клітинку», буде автоматично замінена новим значенням, а клітинка формули отримає цільове значення на основі зміни змінної. Натисніть OK застосувати зміни.

Отже, для перемоги на виборах кандидату потрібно не менше 5100 голосів.

Приклад 4: Досягнення цільового прибутку в бізнесі

сценарій: компанія прагне досягти цільового прибутку в 150,000 XNUMX доларів США на поточний рік. Враховуючи як постійні, так і змінні витрати, їм необхідно визначити необхідний дохід від продажів. Скільки одиниць продажів потрібно для досягнення цієї мети прибутку?

Як показано на знімку екрана нижче:

• B1 містить постійні витрати.
• B2 містить змінні витрати на одиницю.
• B3 містить ціну за одиницю.
• B4 містить продані одиниці.
• B6 – загальний дохід, який розраховується за формулою =B3*B4.
• B7 – загальна вартість, яка розраховується за формулою =B1+(B2*B4).
• B9 — поточний прибуток від продажів, який розраховується за формулою =B6-B7.
  
  

Щоб отримати загальну кількість одиниць продажів на основі цільового прибутку, ви можете застосувати функцію пошуку цілі таким чином.

Крок 1. Активуйте функцію пошуку цілі

Перейти до дані вкладка, клацніть Що-якщо аналіз > Мета пошуку.

Крок 2: Налаштуйте параметри Goal Seek

1. У Встановити комірку виберіть клітинку формули, яка повертає прибуток. Ось клітинка B9.
2. У Оцінювати введіть цільовий прибуток 150000.
3. У Змінюючи комірку виберіть клітинку (B4), яку потрібно налаштувати.
4. Натисніть OK.
   
   

Результат

Після цього з’явиться діалогове вікно «Статус пошуку цілі», яке покаже, що рішення знайдено. У той же час клітинка B4, яку ви вказали в полі «Змінюючи клітинку», буде автоматично замінена новим значенням, а клітинка формули отримає цільове значення на основі зміни змінної. Натисніть OK прийняти зміни.

У результаті компанії потрібно продати принаймні 6666 одиниць, щоб отримати цільовий прибуток у 150,000 XNUMX доларів США.

Поширені проблеми та рішення для Goal Seek

У цьому розділі наведено деякі поширені проблеми та відповідні рішення для Goal Seek.

1. Пошук цілі не може знайти рішення

• Причина: зазвичай це трапляється, коли цільове значення неможливо досягти за допомогою будь-якого можливого вхідного значення або якщо початкове припущення надто далеке від будь-якого потенційного рішення.
• рішення: Налаштуйте цільове значення, щоб переконатися, що воно знаходиться в допустимому діапазоні. Крім того, спробуйте різні початкові припущення, ближчі до очікуваного рішення. Переконайтеся, що формула в «Set Cell» є правильною та може логічно створити бажане «To Value».

2. Низька швидкість обчислень

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

3. Проблеми з точністю

• Причина: під час використання пошуку цілі ви можете помітити, що іноді Excel скаже, що знайшов рішення, але це не зовсім те, що ви хочете – близько, але недостатньо близько. Goal Seek може не завжди надати дуже точний результат через точність обчислень і округлення Excel.
  
  Наприклад, щоб обчислити конкретне піднесення числа до степеня та використовувати пошук цілі, щоб знайти основу, яка досягає бажаного результату. Тут я зміню вхідне значення в комірці A1 (основа), щоб клітинка A3 (результат піднесення до степеня) відповідала цільовому результату 25.
  
  Як ви можете бачити на знімку екрана нижче, Goal Seek надає приблизне значення, а не точний корінь.
  
• рішення: для більшої точності збільште кількість десяткових знаків, які відображаються в параметрах обчислення Excel, а потім повторно запустіть функцію пошуку цілі. Будь ласка, зробіть наступне.
  
  1. Натисніть філе > Опції відкрити Параметри Excel вікна.
  2. Select Формули в лівій панелі та в Варіанти розрахунку розділі, збільшити кількість знаків після коми в Максимальна зміна коробка. Ось я зміню 0.001 до 0.000000001 і натисніть кнопку OK.
     
  3. Повторно запустіть Goal Seek, і ви отримаєте точний корінь, як показано на знімку екрана нижче.
     

4. Обмеження та альтернативні методи

• Причина: Goal Seek може коригувати лише одне вхідне значення і може бути непридатним для рівнянь, які потребують одночасного коригування кількох змінних.
• рішення: Для сценаріїв, які потребують коригування кількох змінних, використовуйте розв’язувач Excel, який є потужнішим і дозволяє встановлювати обмеження.

Goal Seek — це потужний інструмент для виконання зворотних обчислень, що позбавляє вас від виснажливого завдання вручну перевіряти різні значення для наближення вашої цілі. Отримавши інформацію з цієї статті, тепер ви можете впевнено застосовувати Goal Seek, підвищуючи аналіз даних і ефективність на нові висоти. Для тих, хто прагне глибше заглибитися в можливості Excel, наш веб-сайт може похвалитися великою кількістю посібників. Дізнайтеся більше порад і підказок щодо Excel тут.