Търсенето на цел е един от инструментите за анализ на какво-ако на Excel, който ви помага да намерите правилната входна стойност на формула, за да получите желания резултат. Показва как една стойност във формула влияе на друга. С други думи, ако имате целева стойност, която искате да постигнете, можете да използвате целта, за да намерите правилната входна стойност, за да я получите.
Например, да приемем, че сте получили общо 75 точки по предмет и имате нужда от поне 90, за да получите S оценка по този предмет. За щастие имате един последен тест, който може да ви помогне да повишите средния си резултат. Можете да използвате Търсене на цели, за да разберете колко резултат ви е необходим на този последен тест, за да получите S оценка.
Goal Seek използва метод проба-грешка, за да върне проблема, като въвежда предположения, докато стигне до правилния резултат. Търсенето на цели може да намери най-добрата входна стойност за формулата за броени секунди, която би отнела много време, за да разберете ръчно. В тази статия ще ви покажем как да използвате инструмента за търсене на цели в Excel с някои примери.
Компоненти на функцията за търсене на цел
Функцията за търсене на цел се състои от три параметъра, а именно:
- Задайте клетка – Това е клетката, в която се въвежда формулата. Той посочва клетката, в която искате желания изход.
- Да оценявам – Това е целта / желаната стойност, която искате в резултат на операцията за търсене на цел.
- Чрез смяна на клетка – Това посочва клетката, чиято стойност трябва да се коригира, за да се получи желаният изход.
Как да използвате търсене на цел в Excel: Пример 1
За да демонстрирате как работи в прост пример, представете си, че управлявате щанд с плодове. На екранната снимка по-долу клетка B11 (по-долу) показва колко ви струва да закупите плодове за вашата щанда, а клетка B12 показва общите ви приходи от продажбата на тези плодове. А клетка B13 показва процента на вашата печалба (20%).
Ако искате да увеличите печалбата си до „30%“, но не можете да увеличите инвестицията си, така че трябва да увеличите приходите си, за да спечелите печалба. Но до колко? Търсенето на цел ще ви помогне да я намерите.
Използвате следната формула в клетка B13, за да изчислите процента на печалба:
=(B12-B11)/B12Сега искате да изчислите маржа на печалбата, така че вашият процент на печалба да е 30%. Можете да направите това с Търсене на цел в Excel.
Първото нещо, което трябва да направите, е да изберете клетката, чиято стойност искате да коригирате. Всеки път, когато използвате търсене на цел, трябва да изберете клетка, която съдържа формула или функция. В нашия случай ще изберем клетка B13, защото съдържа формулата за изчисляване на процента.
След това отидете в раздела „Данни“, щракнете върху бутона „Анализ какво ще стане“ в групата „Прогноза“ и изберете „Търсене на цел“.
Ще се появи диалоговият прозорец Търсене на цел с 3 полета:
- Задайте клетка – Въведете препратката към клетката, която съдържа формулата (B13). Това е клетката, която ще има желания от вас изход.
- Да оценявам – Въведете желания резултат, който се опитвате да постигнете (30%).
- Чрез смяна на клетка – Вмъкнете препратката към клетката за входната стойност, която искате да промените (B12), за да стигнете до желания резултат. Просто щракнете върху клетката или въведете ръчно препратката към клетката. Когато изберете клетката, Excel ще добави знака „$“ преди буквата на колоната и номера на реда, за да я направи абсолютна клетка.
Когато приключите, щракнете върху „OK“, за да го тествате.
След това ще се появи диалогов прозорец „Състояние на търсенето на цел“ и ще ви информира дали е намерило някакво решение, както е показано по-долу. Ако е намерено решение, входната стойност в „променящата се клетка (B12)“ ще бъде коригирана на нова стойност. Това е, което искаме. Така че в този пример анализът установи, че за да постигнете целта си от 30% печалба, трябва да постигнете приход от $1635,5 (B12).
Щракнете върху „OK“ и Excel ще промени стойностите на клетките или щракнете върху „Отказ“, за да отхвърлите решението и да възстановите оригиналната стойност.
Входната стойност (1635.5) в клетка B12 е това, което открихме с помощта на Търсене на цел, за да постигнем целта си (30%).
Неща, които трябва да запомните, когато използвате Търсене на цели
- Зададената клетка винаги трябва да съдържа формула, която зависи от клетката „Чрез промяна на клетка“.
- Можете да използвате търсене на цел само за една входна стойност на клетка наведнъж
- „Чрез промяна на клетка“ трябва да съдържа стойност, а не формула.
- Ако Търсенето на цел не може да намери правилното решение, то показва най-близката стойност, която може да произведе, и ви казва, че съобщението „Търсене на цел може да не е намерило решение“.
Какво да направите, когато търсенето на цел в Excel не работи
Въпреки това, ако сте сигурни, че има решение, има няколко неща, които можете да опитате да отстраните този проблем.
Проверете параметрите и стойностите за търсене на цел
Има две неща, които трябва да проверите: първо, проверете дали параметърът „Задаване на клетка“ се отнася до клетката с формула. Второ, уверете се, че клетката с формула (Set cell) зависи пряко или косвено от променящата се клетка.
Коригиране на настройките за итерация
В настройките на Excel можете да промените броя на възможните опити, които Excel може да направи, за да намери правилното решение, както и неговата точност.
За да промените настройките за изчисление на итерациите, щракнете върху „Файл“ от списъка с раздели. След това щракнете върху „Опции“ в долната част.
В прозореца с опции на Excel щракнете върху „Формули“ в левия панел.
В секцията „Опции за изчисление“ променете тези настройки:
- Максимални итерации – Показва броя на възможните решения, които excel ще изчисли; колкото по-голямо е числото, толкова повече итерации може да извърши. Например, ако зададете „Максимални итерации“ на 150, Excel тества 150 възможни решения.
- Максимална промяна – Показва точността на резултатите; по-малкият брой дава по-висока точност. Например, ако стойността на вашата входна клетка е равна на „0“, но търсенето на цел спира да се изчислява на „0,001“, промяната на това на „0,0001“ би трябвало да реши проблема.
Увеличете стойността „Максимални итерации“, ако искате Excel да тества повече възможни решения и намалете стойността „Максимална промяна“, ако искате по-точен резултат.
Екранната снимка по-долу показва стойността по подразбиране:
Без циркулярни препратки
Когато формула препраща обратно към собствената си клетка, тя се нарича кръгова препратка. Ако искате Excel Goal Seek да работи правилно, формулите не трябва да използват кръгова препратка.
Пример 2 за търсене на цел в Excel
Да приемем, че заемате пари от „$25 000“ от някого. Те ви заемат парите при лихва от 7% на месец за период от 20 месеца, което прави изплащането на „$1327“ на месец. Но можете да си позволите да плащате само „1000 долара“ на месец за 20 месеца със 7% лихва. Goal Seek може да ви помогне да намерите сумата на заема, която води до месечно плащане (EMI) от „$1000“.
Въведете трите входни променливи, които ще ви трябват, за да изчислите вашето PMT изчисление, т.е. лихвен процент от 7%, срок от 20 месеца и главница от 25 000 долара.
Въведете следната PMT формула в клетка B5, която ще ви даде сума за EMI от $1,327,97.
Синтаксисът на функцията PMT:
=PMT(лихвен процент/12, срок, главница)формулата:
=PMT(B3/12,B4,-B2)
Изберете клетка B5 (клетка с формула) и отидете на Данни –> Анализ какво ще стане –> Търсене на цел.
Използвайте тези параметри в прозореца „Търсене на цел“:
- Задайте клетка – B5 (клетката, която съдържа формулата, която изчислява EMI)
- Да оценявам – 1000 (формулата резултат/цел, която търсите)
- Чрез смяна на клетка – B2 (това е сумата на заема, която искате да промените, за да постигнете целевата стойност)
След това натиснете „OK“, за да запазите намереното решение, или „Отказ“, за да го изхвърлите.
Анализът ви казва, че максималната сума пари, която можете да заемете, е $18825, ако искате да надхвърлите бюджета си.
Ето как използвате търсенето на цели в Excel.