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 XNUMX.
• B2 съдържа срок на заема 5 (години).
• B3 съдържа годишен лихвен процент 5%.
• B5 съдържа месечното плащане, което се изчислява по формулата =PMT(B3/12,B2*12,B1).
  
  

Тук ще ви покажа как да използвате функцията Goal Seek, за да изпълните тази задача.

Стъпка 1: Активирайте функцията Goal Seek

Отидете в Дата кликнете върху Какво-ако анализ > Търсене на цел.

Стъпка 2: Конфигурирайте настройките за търсене на цел

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: Активирайте функцията Goal Seek

Отидете в Дата кликнете върху Какво-ако анализ > Търсене на цел.

Стъпка 2: Конфигурирайте настройките за търсене на цел

1. в Задайте клетка изберете клетката с формула B7.
2. в Да ценят въведете средния резултат за преминаване 70%.
3. в Чрез смяна на клетка изберете клетката (B5), която искате да коригирате.
4. Кликнете OK.
   
   

Резултат

- Състояние на търсене на цел след това ще се появи диалогов прозорец, показващ, че е намерено решение. В същото време клетката B7, която сте посочили в полето „Чрез промяна на клетка“, ще бъде автоматично въведена със съответстваща стойност и клетката с формула ще получи целевата стойност въз основа на промяната на променливата. Кликнете OK да приеме решението.

Така че, за да постигне необходимата обща средна стойност и да издържи всички изпити, студентът трябва да постигне най-малко 71% на последния изпит.

Пример 3: Изчисляване на необходимите гласове за изборна победа

Сценарий: При избори кандидатът трябва да изчисли минималния брой гласове, необходим за осигуряване на мнозинство. При известен общия брой подадени гласове, какъв е минималният брой гласове, които кандидатът трябва да получи, за да постигне повече от 50% и да спечели?

Както е показано на екранната снимка по-долу:

• Клетка B2 съдържа общия брой гласове, подадени на изборите.
• Клетка B3 показва текущия брой гласове, получени от кандидата.
• Клетка B4 съдържа желаното мнозинство (%) от гласовете, които кандидатът цели да постигне, което се изчислява по формула =B2/B1.
  
  

За да постигнете тази цел, можете да приложите функцията Goal Seek, както следва:

Стъпка 1: Активирайте функцията Goal Seek

Отидете в Дата кликнете върху Какво-ако анализ > Търсене на цел.

Стъпка 2: Конфигурирайте настройките за търсене на цел

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: Активирайте функцията Goal Seek

Отидете в Дата кликнете върху Какво-ако анализ > Търсене на цел.

Стъпка 2: Конфигурирайте настройките за търсене на цел

1. в Задайте клетка изберете клетката с формула, която връща печалбата. Ето клетка B9.
2. в Да ценят въведете целевата печалба от 150000 XNUMX.
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.
  
  Например, за да изчислите конкретно степенуване на число и да използвате Goal Seek, за да намерите основата, която постига желания резултат. Тук ще променя входната стойност в клетка A1 (основа), така че клетка A3 (резултат от степенуване) да съответства на целевия резултат от 25.
  
  Както можете да видите на екранната снимка по-долу, Goal Seek предоставя приблизителна стойност, а не точния корен.
  
• Решение: Увеличете броя на десетичните знаци, показани в опциите за изчисление на Excel за по-голяма точност, след което стартирайте отново функцията Търсене на цел. Моля, направете следното.
  
  1. Кликнете досие > Настроики за да отворите Опции на Excel прозорец.
  2. Изберете формули в левия прозорец и в Опции за изчисление раздел, увеличете броя на десетичните знаци в Максимална промяна кутия. Тук се променям 0.001 да се 0.000000001 и кликнете OK.
     
  3. Стартирайте отново Goal Seek и ще получите точния корен, както е показано на екранната снимка по-долу.
     

4. Ограничения и алтернативни методи

• Причина: Търсенето на цел може да коригира само една входна стойност и може да не е подходящо за уравнения, изискващи едновременни настройки на множество променливи.
• Решение: За сценарии, изискващи корекции на множество променливи, използвайте Solver на Excel, който е по-мощен и позволява настройка на ограничения.

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