Як використовувати пошук цілі в Excel (з реальними прикладами)

Якщо ви знаєте результат формули, але не знаєте вхідного значення, яке генеруватиме результат, Microsoft Excel допоможе вам. Просто навчіться використовувати Goal Seek в Excel і виконувати моделювання формул і великих числових даних без зміни фактичних даних.

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

Якщо ви новачок у програмі Excel або маєте певний досвід її використання, але не є експертом, читайте далі, щоб дізнатися про основи пошуку цілі в Excel і про те, як використовувати функцію пошуку цілі в Excel.

Що таке пошук цілі в Excel?

Goal Seek — це вбудований інструмент аналізу "Що, якщо" Excel. Він показує, як остаточне значення у формулі впливає на вхідне значення. Простими словами, це вказує вхідне значення для формули, щоб отримати бажаний результат з тієї самої формули.

Читайте також:  Увімкнення/вимкнення відображення формул у клітинках у Excel

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

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

Ви можете використовувати цей інструмент майже в будь-якому випадку, але наведені нижче є основними.

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

Де Goal Seek в Excel?

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

Де знаходиться пошук цілі в Excel

Розташування інструмента те саме в програмі Excel для Mac. На момент написання цієї статті функція доступна в таких версіях Excel:

• Excel для Microsoft 365 (Windows і Mac)
• Excel 2021 (Windows і Mac)
• Також доступний для Windows і Mac з Excel 2010 і новіших версій

Вимоги до використання функції пошуку цілі в Excel

Excel Goal Seek виконає всі обчислення у серверній частині. Все, що вам потрібно зробити, це визначити наступні три параметри з вашої математичної моделі або таблиці даних:

Компоненти та параметри для Excel Goal Seek

• Введіть посилання на клітинку в поле «Установити клітинку» . Ця довідкова комірка повинна містити формулу. Excel Goal Seek використовує цю формулу для отримання вхідного значення.
• Вам потрібно ввести результат або мету, яку потрібно досягти, у полі Значення .
• Комірка «Змінюючи» — це посилання на клітинку, у якій потрібно ввести дані для математичної моделі, яку ви моделюєте.

Як використовувати пошук цілі в Excel

Нижче наведено декілька прикладів сценаріїв, у яких ви можете використовувати пошук цілі в Excel для прогнозування вхідних даних для бажаного виходу з математичної формули. Кроки однакові для пристроїв Windows 11 і macOS.

1. Цільовий пошук, щоб передбачити оцінку іспиту

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

• Створіть заголовки стовпців із назвою « Семестр» і «Оцінка» на аркуші Excel.
• Введіть семестр I - семестр VIII у колонці семестр .
• Введіть бали всіх семисеместрових іспитів, крім VIII семестру .
• Створіть середній бал за семестр за такою формулою:

=СЕРЕДНЄ(B2:B9)

• Налаштуйте посилання на клітинки відповідно до власного аркуша Excel.
• Тепер викличте функцію Goal Seek .
• Комірка Set має бути клітинкою формули, у якій ви обчислили середнє значення.
• У комірці «Значення» має бути ваш бажаний середній бал за семестр, який становить 85%.
• Введіть посилання на порожню комірку для оцінки іспиту за семестр VIII у вікні Шляхом зміни комірки .

Дізнайтеся, як використовувати пошук цілі для прогнозування результатів іспиту

• Натисніть OK , щоб отримати бал за семестр VIII, який ви повинні забезпечити, щоб отримати середній бал 85%.

Вам потрібно набрати 98% в останньому семестрі, щоб отримати цільовий випускний бал.

Читайте також:  Microsoft Excel: як легко керувати аркушами

2. Передбачте суму позики за допомогою Goal Seek

Припустімо, вам потрібно позичити капітал на 40 років під фіксований відсоток (8%) із фіксованою щомісячною виплатою для вашого бізнесу. Тепер ви знаєте, що можете платити 1800 доларів США на місяць із прибутку від бізнесу. Однак ви не знаєте, яку суму ви повинні позичити під зазначені вище відсотки на вказаний вище період. Ви можете легко розрахувати суму позики, виконавши такі дії:

• Створіть рядки для таких компонентів: Річна відсоткова ставка , Роки до виплати , Сума позики та Щомісячні внески .
• Введіть відповідні значення для наведених вище компонентів у відповідні комірки, за винятком суми позики , яку потрібно обчислити за допомогою Goal Seek.
• Тепер замініть значення щомісячних платежів такою формулою PMT у Excel:

=PMT(B1/12;B2*12;B3;0)

• Викличте функцію Goal Seek у меню Data > What-If Analysis > Goal Seek .
• Набір клітинок має посилатися на клітинку формули PMT.
• Вартість має становити 1500 доларів США.
• Змінивши комірку, має бути значення суми позики .

Навчіться передбачати суму кредиту за допомогою Goal Seek

• Натисніть «ОК» , щоб змоделювати суму позики за допомогою ітераційних обчислень.

Згідно з функцією Goal Seek, сума позики має складати 215 731 долар, якщо ви бажаєте позичити її на 40 років під 8% річних і сплачувати щомісячний внесок у 1500 доларів.

3. Знайдіть ціль продажів за допомогою Goal Seek

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

Рекомендована роздрібна ціна продукту становить 50 доларів США, і ви також пропонуєте оптовикам знижку 10%. Однак ви хотіли б збільшити цільовий дохід вашої компанії від продажу продукту до 75 000 доларів США без зміни MSRP і ставки дисконту. Тоді скільки продуктів ви повинні продати? Давайте дізнаємось за допомогою Goal Seek нижче:

• Створіть рядки для таких відомих компонентів, як Продані одиниці , MSRP і Знижка .
• Розрахуйте дохід за такою формулою:

=B2*(B3*(1-B4))

Навчіться знаходити ціль продажів за допомогою Goal Seek

• Тепер відкрийте Goal Seek і встановіть наступні параметри:
  • Установіть клітинку : $B$5
  • Вартість : $75 000
  • Змінюючи клітинку : $B$2

Переконайтеся, що ви налаштували посилання на клітинки відповідно до ваших власних даних і аркуша Excel. Натисніть OK , і ви отримаєте цільовий обсяг продажів продуктів, який становить 1667 одиниць за тієї самої MSRP і ставки дисконту.

Пошук цілі в Excel: поширені запитання

Як використовувати пошук цілі в Excel для кількох клітинок?

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

Який ярлик для пошуку мети?

Наразі в Excel немає спеціального ярлика для функції пошуку цілі. Ви можете використовувати клавішу Alt в Excel, а потім натискати кілька клавіш на клавіатурі, щоб викликати інструмент пошуку цілі без використання миші. Кроки описані тут:

Press Alt on Excel > without clicking anywhere on the app press A > press W > press G to open Goal Seek

Як я можу зробити свою мету точнішою?

Іноді Goal Seek може отримати вхідне значення в десяткових дробах. Якщо вам потрібна округлена цифра автоматично, ви можете збільшити її точність. Ось як ви можете це зробити:

• Перейдіть на вкладку «Файл» і виберіть «Параметри» на бічній панелі.
• У вікні «Параметри Excel» натисніть опцію «Формули» .
• Позначте опцію Увімкнути функцію ітераційного обчислення .

Як оптимізувати Excel Goal Seek

• Збільште поле «Максимальна кількість ітерацій» до 150 або більше, щоб дозволити Excel тестувати більш прийнятні рішення.
• Також збільште значення максимальної зміни з 0,001 за замовчуванням до 0,00001.

Чи можу я виконати пошук цілі в кількох клітинках?

Ви не можете виконати пошук цілі в Excel для кількох клітинок.

Які переваги пошуку мети?

Ось переваги Goal Seek у Excel:

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

Які обмеження пошуку цілі в Excel?

Goal Seek в Excel має такі недоліки:

• Goal Seek не працюватиме, якщо є  циклічні посилання
• Ви не можете вводити жодні змінні під час моделювання пошуку цілі
• Це не працює на кількох клітинках
• Функція Excel Goal Seek недоступна в Excel Web App або Excel для веб-програм

Excel Goal Seek: Заключні слова

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

Залиште свій коментар нижче щодо свого досвіду роботи з наведеним вище покроковим посібником Excel Goal Seek. Якщо ви знаєте будь-які інші методи аналізу «що-якщо» в Excel, дайте мені знати.

Далі,  як порівняти текст в Excel .