Пошук цілі — це один із інструментів Excel для аналізу «що-якщо», який допомагає знайти правильне вхідне значення формули для отримання бажаного результату. Він показує, як одне значення у формулі впливає на інше. Іншими словами, якщо у вас є цільове значення, якого ви хочете досягти, ви можете використовувати ціль пошуку, щоб знайти правильне вхідне значення для його отримання.
Наприклад, припустимо, що ви набрали загалом 75 балів з предмета, і вам потрібно принаймні 90, щоб отримати оцінку S з цього предмета. На щастя, у вас є останній тест, який може допомогти вам підвищити середній бал. Ви можете використовувати Goal Seek, щоб визначити, скільки балів вам потрібно на цьому підсумковому тесті, щоб отримати оцінку S.
Goal Seek використовує метод проб і помилок, щоб відстежити проблему, вводячи припущення, поки не отримає правильний результат. Функція пошуку цілі може знайти найкраще вхідне значення для формули за лічені секунди, для визначення яких вручну знадобиться багато часу. У цій статті ми покажемо вам, як використовувати інструмент пошуку цілі в Excel з деякими прикладами.
Компоненти функції пошуку цілі
Функція пошуку цілі складається з трьох параметрів, а саме:
- Встановити клітинку – Це клітинка, в яку вводиться формула. Він визначає комірку, в якій потрібно отримати потрібний результат.
- Оцінювати – Це цільове/бажане значення, яке ви хочете отримати в результаті операції пошуку цілі.
- Змінюючи клітинку – Це визначає комірку, значення якої потрібно відкоригувати, щоб отримати бажаний результат.
Як використовувати пошук цілі в Excel: приклад 1
Щоб продемонструвати, як це працює на простому прикладі, уявіть, що ви запускаєте фруктовий кіоск. На наведеному нижче знімку екрана клітинка B11 (нижче) відображає, скільки вам коштувала покупка фруктів для вашого ларька, а клітинка B12 показує ваш загальний дохід від продажу цих фруктів. А клітинка B13 показує відсоток вашого прибутку (20%).
Якщо ви хочете збільшити свій прибуток до «30%», але ви не можете збільшити свої інвестиції, ви повинні збільшити свій дохід, щоб отримати прибуток. Але на скільки? Пошук мети допоможе вам її знайти.
Ви використовуєте таку формулу в клітинці B13, щоб обчислити відсоток прибутку:
=(B12-B11)/B12Тепер ви хочете розрахувати маржу прибутку так, щоб ваш відсоток прибутку становив 30%. Ви можете зробити це за допомогою пошуку цілі в Excel.
Перше, що потрібно зробити, це вибрати комірку, значення якої потрібно змінити. Кожного разу, коли ви використовуєте функцію пошуку цілі, ви повинні вибрати клітинку, яка містить формулу або функцію. У нашому випадку ми виберемо клітинку B13, оскільки вона містить формулу для обчислення відсотка.
Потім перейдіть на вкладку «Дані», натисніть кнопку «Що, якщо аналіз» у групі «Прогноз» і виберіть «Пошук цілі».
З’явиться діалогове вікно пошуку цілі з 3 полями:
- Встановити клітинку – Введіть посилання на клітинку, що містить формулу (B13). Це осередок, який буде мати бажаний результат.
- Оцінювати – Введіть бажаний результат, якого ви намагаєтеся досягти (30%).
- Змінюючи клітинку – Вставте посилання на клітинку для вхідного значення, яке ви хочете змінити (B12), щоб отримати бажаний результат. Просто клацніть клітинку або вручну введіть посилання на клітинку. Коли ви виберете клітинку, Excel додасть знак «$» перед літерою стовпця та номером рядка, щоб зробити її абсолютною.
Коли ви закінчите, натисніть «ОК», щоб перевірити це.
Потім з’явиться діалогове вікно «Статус пошуку цілі», яке повідомить вас, чи знайдено якесь рішення, як показано нижче. Якщо рішення було знайдено, введене значення в «комірці, що змінюється (B12)» буде змінено на нове значення. Це те, чого ми хочемо. Отже, у цьому прикладі аналіз визначив, що для досягнення мети 30% прибутку вам потрібно отримати дохід у розмірі 1635,5 доларів США (B12).
Натисніть «ОК», і Excel змінить значення клітинок, або натисніть «Скасувати», щоб скасувати рішення та відновити початкове значення.
Вхідне значення (1635,5) у клітинці B12 – це те, що ми знайшли за допомогою пошуку цілі для досягнення нашої мети (30%).
Що потрібно пам’ятати під час використання Goal Seek
- Комірка Set завжди повинна містити формулу, яка залежить від комірки «Шляхом зміни комірки».
- Ви можете використовувати ціль Seek лише для однієї клітинки введеного значення за раз
- «Шляхом зміни комірки» має містити значення, а не формулу.
- Якщо пошук цілі не може знайти правильне рішення, він показує найближче значення, яке може створити, і повідомляє, що повідомлення «Пошук цілі не знайшов рішення».
Що робити, якщо пошук цілі в Excel не працює
Однак, якщо ви впевнені, що є рішення, ви можете спробувати вирішити цю проблему.
Перевірте параметри та значення пошуку цілі
Є дві речі, які ви повинні перевірити: по-перше, перевірте, чи відноситься параметр «Встановити комірку» до комірки формули. По-друге, переконайтеся, що клітинка формули (комірка Set) залежить, прямо чи опосередковано, від комірки, що змінюється.
Налаштування параметрів ітерації
У налаштуваннях Excel ви можете змінити кількість можливих спроб, які Excel може зробити, щоб знайти правильне рішення, а також його точність.
Щоб змінити параметри обчислення ітерацій, натисніть «Файл» у списку вкладок. Потім натисніть «Параметри» внизу.
У вікні параметрів Excel натисніть «Формули» на панелі ліворуч.
У розділі «Параметри розрахунку» змініть ці налаштування:
- Максимальна кількість ітерацій – вказує кількість можливих рішень, які розрахує Excel; чим більше число, тим більше ітерацій він може виконати. Наприклад, якщо для параметра «Максимальні ітерації» встановлено значення 150, Excel перевірить 150 можливих рішень.
- Максимальна зміна – вказує на точність результатів; менша кількість дає більшу точність. Наприклад, якщо ваше вхідне значення комірки дорівнює «0», але пошук цілі перестає обчислюватися на «0,001», зміна цього значення на «0,0001» має вирішити проблему.
Збільште значення «Максимальні ітерації», якщо ви хочете, щоб Excel тестував більше можливих рішень, і зменште значення «Максимальна зміна», якщо ви хочете отримати більш точний результат.
На скріншоті нижче показано значення за замовчуванням:
Без циркулярних посилань
Коли формула посилається на власну клітинку, вона називається круговим посиланням. Якщо ви хочете, щоб Excel Goal Seek працював належним чином, формули не повинні використовувати кругові посилання.
Приклад пошуку цілі Excel 2
Скажімо, ви позичаєте у когось гроші на суму «25 000 доларів США». Вони позичають вам гроші під відсоткову ставку 7% на місяць на період 20 місяців, що становить «1327 доларів США» на місяць. Але ви можете дозволити собі платити «1000 доларів США» на місяць протягом 20 місяців з відсотковою ставкою 7%. Goal Seek може допомогти вам знайти суму позики, яка спричиняє щомісячний платіж (EMI) у розмірі «1000 доларів США».
Введіть три вхідні змінні, які вам знадобляться для розрахунку вашого PMT, тобто відсоткова ставка 7%, термін 20 місяців і основна сума 25 000 доларів США.
Введіть таку формулу PMT в клітинку B5, яка дасть вам суму EMI у розмірі 1327,97 доларів США.
Синтаксис функції PMT:
=PMT(Процентна ставка/12, Термін, Основна сума)Формула:
=PMT(B3/12,B4,-B2)
Виберіть клітинку B5 (комірка формули) і перейдіть до Дані –> Аналіз що робити –> Пошук цілі.
Використовуйте ці параметри у вікні «Пошук цілі»:
- Встановити клітинку – B5 (комірка, яка містить формулу для обчислення електромагнітних поштовхів)
- Оцінювати – 1000 (формула результат/ціль, яку ви шукаєте)
- Змінюючи клітинку – B2 (це сума позики, яку ви хочете змінити для досягнення цільової вартості)
Потім натисніть «ОК», щоб зберегти знайдене рішення, або «Скасувати», щоб відмінити його.
Аналіз говорить вам, що максимальна сума грошей, яку ви можете позичити, становить 18825 доларів, якщо ви хочете перевищити свій бюджет.
Ось як ви використовуєте пошук цілі в Excel.