Как да използвате търсене на цел в Excel (с примери от реалния живот)

Ако знаете резултата от формула, но не знаете входната стойност, която ще генерира резултата, Microsoft Excel ще ви покрие. Просто научете как да използвате Goal Seek в Excel и извършвайте симулации на формули и големи числени данни, без да променяте действителните данни.

Независимо дали става въпрос за вашите лични финанси, спестявания, инвестиции или бизнес проект, информираното вземане на решения е ключът към успеха. За вземане на информирано решение се нуждаете от симулирани данни от симулация на бази данни въз основа на формула и вашата цел. Excel Goal Seek е инструментът за такава базирана на данни симулация.

Ако не сте запознати с Excel или имате известен опит с използването му, но не сте експерт, прочетете, за да научите основите на търсенето на цел в Excel и как да използвате функцията търсене на цел в Excel.

Какво е търсене на цел в Excel?

Goal Seek е вграденият инструмент за анализ на какво става в Excel. Показва как крайната стойност във формула влияе върху входната стойност. С прости думи, той ви казва входната стойност за формула, за да получите желания резултат от същата формула.

Прочетете също:  Активиране/деактивиране на показването на формули в клетки в Excel

Финансови инвеститори, финансови мениджъри, собственици на бизнес и специалности в областта на мениджмънта използват изобилно този инструмент за анализ на какво става в Excel, за да предскажат входящите данни за желания резултат от всеки математически модел.

Можете също така да наречете Goal Seek в Excel автоматизирана система за симулация, която анализира вашата формула, свързана с произволен размер набори от данни, и ви казва входна стойност. Ако въведете входната стойност във вашия математически модел, ще получите резултата, който търсите.

Можете да използвате този инструмент в почти всеки сценарий, но следните са основните:

• Колко единици продукти трябва да продадете, за да постигнете конкретен приход с добавени отстъпки и комисионни на агенти
• При какъв лихвен процент трябва да вземете заем, за да можете да изплатите заема в рамките на определен период на определени месечни вноски
• Колко работни часа са необходими за завършване на даден проект в рамките на предварително определен срок

Къде е търсенето на цел в 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

Намерете по-долу някои примерни сценарии, при които можете да използвате Goal Seek в Excel, за да предвидите вход за желан изход от математическа формула. Стъпките са сходни както за Windows 11, така и за устройства с macOS.

1. Търсене на цел за прогнозиране на резултат от изпита

Да речем, че трябва да ускорите плановете си за обучение в последния семестър на колежа, за да постигнете 85% среден резултат, когато завършите. Ето как можете да приложите формулата за търсене на цел, за да симулирате необходимия резултат в последния семестър, ако сте получили резултати от останалите седем семестъра:

• Създайте заглавки на колони с имена Семестър и Резултат в работен лист на Excel.
• Въведете семестър I до семестър VIII под колоната Семестър .
• Въведете резултатите от всички седемсеместриални изпити с изключение на семестър VIII .
• Създайте среден резултат за семестър , като използвате следната формула:

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

• Коригирайте препратките към клетки според вашия собствен работен лист в Excel.
• Сега извикайте функцията Goal Seek .
• Клетката Set трябва да е клетката с формула, където сте изчислили средна стойност.
• Клетката To value трябва да бъде желаният от вас среден семестриален резултат, който е 85%.
• Въведете препратката към празната клетка за резултата от изпита от семестър VIII в полето Чрез промяна на клетка .

Научете как да използвате търсенето на цел, за да предвидите резултата от изпита

• Щракнете върху OK , за да получите резултата от семестър VIII, който трябва да осигурите за среден резултат от дипломирането от 85%.

Трябва да постигнете 98% в последния семестър, за да постигнете целевия резултат за дипломиране.

Прочетете също:  Microsoft Excel: Как лесно да управлявате таблиците

2. Прогнозирайте сумата на заема с помощта на Goal Seek

Да предположим, че трябва да заемете капитал за 40 години при фиксирана лихва (8%) с фиксирана месечна отплата за вашия бизнес. Сега знаете, че можете да плащате $1800 на месец от възвръщаемостта на бизнеса. Вие обаче не знаете колко трябва да вземете назаем при горната лихва за горепосочения период. Можете лесно да изчислите сумата, която да вземете назаем, като следвате тези стъпки:

• Създайте редове за следните компоненти: Годишен лихвен процент , Години до изплащане , Сума на заема и Месечни вноски .
• Въведете съответните стойности за горните компоненти в съответните клетки с изключение на сумата на заема , която трябва да изчислите с помощта на търсене на цел.
• Сега заменете стойността на месечните вноски със следната формула PMT в Excel:

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

• Извикайте функцията Goal Seek от Data > What-If Analysis > Goal Seek .
• Зададената клетка трябва да препраща към клетката с формула PMT.
• Стойността трябва да бъде $1500.
• Чрез промяна на клетката трябва да бъде стойността на сумата на заема .

Научете се да прогнозирате сумата на заема с помощта на Goal Seek

• Щракнете върху OK, за да симулирате сумата на заема чрез итеративни изчисления.

Според функцията Goal Seek, сумата на заема трябва да бъде 215 731 долара, ако искате да го заемете за 40 години при 8% годишна лихва и да плащате месечна вноска от 1500 долара.

3. Намерете целта за продажби с помощта на търсене на цел

Ако сте ръководител на продажбите и маркетинга или работите като изпълнителен директор по продажбите и трябва да създадете цел за продажби, за да постигнете конкретна цел за приходи, Goal Seek е идеалният инструмент. Например вашият бизнес в момента продава 1000 единици продукти.

MSRP на продукта е $50 и вие също предлагате отстъпка от 10% за търговците на едро. Въпреки това бихте искали да увеличите целевите приходи на вашата компания от продажби на продукти до $75 000, без да променяте MSRP и дисконтовия процент. Тогава, колко продукта трябва да продадете? Нека разберем с помощта на Goal Seek по-долу:

• Създайте редове за известни компоненти като продадени единици , MSRP и отстъпка .
• Изчислете приходите по следната формула:

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

Научете се да намирате цел за продажби с помощта на търсене на цел

• Сега отворете Goal Seek и задайте следното като негови параметри:
  • Задайте клетка : $B$5
  • На стойност : $75 000
  • Чрез промяна на клетка : $B$2

Уверете се, че коригирате препратките към клетките според вашите собствени данни и работен лист на Excel. Натиснете OK и ще получите целта за продажби на продукти, която е 1667 единици при същата MSRP и процент на отстъпка.

Търсене на цел в Excel: ЧЗВ

Как да използвам търсене на цел в Excel за множество клетки?

Goal Seek в Excel ви позволява да симулирате вход за математическа формула, за да получите предварително определен резултат. За съжаление, той не поддържа никакви променливи или множество клетки, които да бъдат взети под внимание. За множество клетки и базиран на вариации анализ какво-ако можете да използвате добавката Solver на Excel.

Какъв е прекият път за търсене на цел?

Понастоящем няма специален пряк път за функцията Goal Seek в 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, трябва да сте разработили основно до средно ниво на разбиране за това как да правите анализ какво-ако в Excel. Търсенето на цел в Excel е особено полезно при математически задачи, където има ясна формула, входна стойност, няколко променливи, свързани с формулата, и една цел, която трябва да бъде постигната.

Оставете своя коментар по-долу относно опита си с горното ръководство стъпка по стъпка на Excel Goal Seek. Ако знаете други техники за анализ на какво става в Excel, уведомете ме.

След това  как да сравнявате текст в Excel .