Как да използвате търсенето на цели в Microsoft Excel за сценарии „какво-ако“

Анализът на What-If на Excel ви позволява да видите как промяната на клетка може да повлияе на изхода на формулата. Можете да използвате инструментите на Excel за тази цел, за да изчислите ефекта от промяната на стойността на клетка във формула.

Excel има три типа инструменти за анализ „какво-ако“: Scenario Manager, Goal Seek и Data Table. С Goal Seek можете да определите какъв вход е необходим, за да преобразувате формулата обратно в определен изход.

Функцията за търсене на цел в Excel е проба и грешка, така че ако не произвежда това, което искате, Excel работи за подобряване на тази стойност, докато не го направи.

Какви са формулите за търсене на цели на Excel?

Търсенето на цел в Excel по същество се разделя на три основни компонента:

Задаване на клетка : Клетката, която искате да използвате като цел.
Да оценявам : Стойността, която искате като своя цел.
Чрез смяна на клетката : Клетката, която искате да промените, за да достигнете стойността на целта си.

С тези три настройки Excel ще се опита да подобри стойността в клетката, която сте задали Чрез смяна на клетката до клетката, която сте поставили Задаване на клетка достига стойността, в която сте определили Да оценявам .

Ако всичко това звучи объркващо, примерите по -долу ще ви дадат добра представа за това как работи търсенето на цел.

Пример за търсене на цел 1

Например, да предположим, че имате две клетки (A & B) и трета клетка, която изчислява средната стойност на тези две.

Да предположим, че имате статична стойност за A и искате да повишите средното ниво, като промените стойността за B. С помощта на Търсене на цел можете да изчислите каква стойност на B ще произведе средната стойност, която искате.

Свързани: Съвети за спестяване на време в Microsoft Excel

как да направите стартиращ диск от iso

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

В Excel щракнете върху клетка C1 .
В лентата с формули въведете следния код: | _+_ | Тази формула ще накара Excel да изчисли средната стойност на стойностите в клетките А1 и В1 и извежда това в клетката C1 . Можете да се уверите, че формулата ви работи, като въведете числа в A1 и B1. C1 трябва да показва средната им стойност.
За този пример променете стойността на А1 клетка към 16 .
Изберете клетка C1 , а след това от лентата отидете на Данни раздел.
Под Раздел данни , изберете Какво-ако анализ и тогава Търсене на цел . Това ще отвори прозореца Търсене на цел.
В прозореца Търсене на цел задайте Задаване на клетка да се C1 . Това ще бъде вашата цел клетка. След като маркирате C1 преди да отворите прозореца за търсене на цел, той автоматично ще го включи Задаване на клетка .
В Да оценявам , въведете желаната стойност на целта. За този пример искаме средната ни стойност да бъде 26, така че стойността на целта също ще бъде 26.
Накрая, в Чрез смяна на клетката , изберете клетката, която искате да промените, за да постигнете целта. В този пример това ще бъде клетка В2 .
Щракнете Добре за да има Goal Seek да работи своята магия.

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

Стойността на клетка A1 ще се промени към решението и това ще замени предишните данни. Добра идея е да стартирате „Търсене на цел“ върху копие на листа с данни, за да избегнете загуба на първоначалните си данни.

Свързани: Луди формули на Excel, които правят невероятни неща

Пример за търсене на цел 2

Търсенето на цел може да бъде полезно, когато го използвате в реални сценарии. Въпреки това, за да използвате пълния потенциал на „Търсене на цел“, трябва да имате подходяща формула, за да започнете.

За този пример нека вземем малък банков случай. Да предположим, че имате банкова сметка, която ви дава 4% годишна лихва върху парите, които имате в сметката си.

Използвайки анализ „Какво-ако“ и търсене на цел, можете да изчислите колко пари трябва да имате в сметката си, за да получите определена сума месечно плащане на лихва.

За този пример, да речем, че искаме да получаваме 350 долара всеки месец от лихвени плащания. Ето как можете да изчислите това:

как да изкореня разпалвам огън 1 -во поколение

В клетката А1 Тип Баланс .
В клетката А2 Тип Годишна ставка .
В клетката А4 Тип Месечна печалба .
В клетката В2 Тип 4% .
Изберете клетка В4 и в лентата с формули въведете формулата по -долу: | _+_ | Месечната печалба ще бъде равна на салдото по сметката, умножено по годишния процент и след това разделено на броя на месеците в една година, 12 месеца.
Отидете на Данни раздел, щракнете върху Какво-ако анализ, и след това изберете Търсене на цел .
В Търсене на цел прозорец, тип B4 в Задаване на клетка .
Въведете 350 в Да оценявам клетка. (Това е месечната печалба, която искате да получите)
Тип В1 в Чрез смяна на клетката . (Това ще промени салдото на стойност, която ще донесе $ 350 месечно)
Щракнете Добре . The Състояние на търсенето на цел ще се появи диалог.

В диалоговия прозорец Състояние на търсене на цел ще видите, че Търсенето на цел е намерило решение в клетка В4. В клетка В1 можете да видите решението, което трябва да бъде 105 000.

Изисквания за търсене на цел

С Търсене на цел можете да видите, че тя може да промени само една клетка, за да достигне стойността на целта. Това означава, че търсенето на цел не може да реши и да достигне до решение, ако има повече от една променлива.

В Excel все още можете да решите за повече от една променлива, но ще трябва да използвате друг инструмент, наречен Solver. Можете да прочетете повече за Solver на Excel, като прочетете нашата статия на Как да използвате Solver на Excel .

Ограничения за търсене на цели

Формулата за търсене на цел използва процеса на изпробване и подобряване, за да достигне оптималната стойност, което може да създаде някои проблеми, когато формулата произвежда неопределена стойност. Ето как можете да го тествате сами:

В клетката А1 , Тип 3 .
Изберете клетка C1 , след това в лентата с формули въведете формулата по -долу: | _+_ | Тази формула ще раздели едно на A1-5 и ако A1-5 е 0, резултатът ще бъде неопределена стойност.
Отидете на Данни раздела, щракнете Какво-ако анализ и след това изберете Търсене на цел .
В Задаване на клетка , Тип C1 .
В Да оценявам , Тип 1 .
Накрая, в Чрез смяна на клетката Тип А1 . (Това ще промени стойността на A1 до 1 в C1)
Щракнете Добре .

Диалоговият прозорец Goal Seek Status ще ви каже, че може да не е намерил решение и можете да проверите отново дали стойността A1 е далеч.

Сега решението на това търсене на цел би било просто да има 6 в A1. Това ще даде 1/1, което е равно на 1. Но в един момент по време на процеса на изпробване и подобрение, Excel опитва 5 в A1, което дава 1/0, което е неопределено, убивайки процеса.

Един начин да заобиколите този проблем с търсенето на цел е да имате различна начална стойност, такава, която може да избегне неопределеното в процеса на изпробване и подобряване.

Като пример, ако промените A1 на произволно число по -голямо от 5 и след това повторите същите стъпки към Търсене на цел за 1, трябва да получите правилния резултат.

прехвърляне на песен от ipod на компютър

Направете изчисленията по -ефективни с търсене на цел

Какво-ако анализът и търсенето на цел могат да ускорят изчисленията ви с вашите формули до голяма степен. Изучаването на правилните формули е друга важна стъпка към това да станете майстор на Excel.

Дял Дял Туит електронна поща 15 формули на Excel, които ще ви помогнат да решите проблеми от реалния живот

Excel не е само за бизнес. Ето няколко формули на Microsoft Excel, които ще ви помогнат да решавате сложни ежедневни проблеми.

Прочетете Напред Свързани теми

Производителност
Електронна таблица
Microsoft Excel
Съвети за Microsoft Office
Анализ на данни

За автора Амир М. Интелигентност(39 статии са публикувани)

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

Още от Amir M. Bohlooli

Абонирайте се за нашия бюлетин

Присъединете се към нашия бюлетин за технически съвети, рецензии, безплатни електронни книги и изключителни оферти!

Щракнете тук, за да се абонирате