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

Инструментът за анализ на какво-ако^{(What-If Analysis)} на Excel е разделен на три основни компонента. Частта, която се обсъжда тук, е мощната функция за търсене^{(Goal Seek)} на цели , която ви позволява да работите назад от функция и да определяте входните данни, необходими за получаване на желания изход от формула в клетка. Прочетете, за да научите как да използвате инструмента за търсене на цел на анализ „Какво-ако“^{(What-If Analysis Goal Seek)} на Excel .

Пример за инструмента за търсене на цел в Excel

Да предположим, че искате да изтеглите ипотечен заем за закупуване на къща и сте загрижени за това как лихвеният процент по заема ще повлияе на годишните плащания. Размерът на ипотеката е $100 000 и вие ще изплатите заема в продължение на 30 години.

Използвайки функцията PMT^(PMT) на Excel , можете лесно да разберете какви биха били годишните плащания, ако лихвеният процент беше 0%. Електронната таблица вероятно ще изглежда така:

Клетката в A2 представлява годишния лихвен процент, клетката в B2 е продължителността на заема в години, а клетката в C2 е сумата на ипотечния заем. Формулата в D2 е:

=PMT(A2,B2,C2)

и представлява годишните плащания на 30-годишна ипотека от $100 000 при 0% лихва. Обърнете внимание^(Notice) , че цифрата в D2 е отрицателна, тъй като Excel приема, че плащанията са отрицателен паричен поток от вашето финансово състояние.

За съжаление, нито един ипотечен кредитор няма да ви даде 100 000 долара при 0% лихва. Да предположим^(Suppose) , че се замислите и разберете, че можете да си позволите да изплащате 6000 долара годишно като ипотечни плащания. Сега се чудите какъв е най-високият лихвен процент, който можете да вземете за заема, за да сте сигурни, че няма да плащате повече от 6 000 долара на година.

Много хора в тази ситуация просто биха започнали да въвеждат числа в клетка A2, докато цифрата в D2 достигне приблизително 6000 долара. Можете обаче да накарате Excel да свърши работата вместо вас, като използвате инструмента за ^(Excel)търсене на цел за анализ^{(Analysis Goal Seek)} „Какво-ако “ . По същество ще накарате Excel да работи обратно от резултата в D4, докато достигне лихвен процент, който удовлетворява максималното ви изплащане от 6000 долара.

Започнете, като щракнете върху раздела Данни^(Data) на лентата^(Ribbon) и намерите бутона Анализ какво да стане в секцията ^{(What-If Analysis)}Инструменти за данни^{(Data Tools)} . Щракнете върху бутона Анализ какво ще стане^{(What-If Analysis)} и изберете Търсене на цел^{(Goal Seek)} от менюто.

Excel отваря малък прозорец и ви моли да въведете само три променливи. Променливата Set Cell трябва да бъде клетка, която съдържа формула. В нашия пример тук това е D2 . Променливата To Value е количеството, което искате клетката в D2 да бъде в края на анализа.

За нас е -6000 . Не забравяйте, че Excel разглежда плащанията като отрицателен паричен поток. Променливата By Changing Cell е лихвеният процент, който искате Excel да намери за вас, така че ипотеката от $100 000 ще ви струва само $6 000 на година. Така че, използвайте клетка A2 .

Щракнете върху бутона OK и може да забележите, че Excel мига куп числа в съответните клетки, докато итерациите най-накрая се сближат с окончателно число. В нашия случай клетката в A2 вече трябва да отчита около 4,31%.

Този анализ ни казва, че за да не харчите повече от 6000 долара годишно за 30-годишна ипотека от 100 000 долара, трябва да осигурите заема на не повече от 4,31%. Ако искате да продължите да правите анализи какво-ако, можете да опитате различни комбинации от числа и променливи, за да проучите опциите, които имате, когато се опитвате да осигурите добър лихвен процент по ипотека.

Инструментът за търсене на цел за анализ „Какво-ако“^{(What-If Analysis Goal Seek)} на Excel е мощно допълнение към различните функции и формули, намиращи се в типичната електронна таблица. Като работите назад от резултатите от формула в клетка, можете да изследвате различните променливи във вашите изчисления по-ясно.

Using Excel’s What-If Analysis Goal Seek Tool

Althоugh Excel’s long list of functions is one оf the most entіcing features of Microsoft’s spreadsheet applіcation, therе a few underutilized gems that enhance these functions. One often-ovеrlooked tool is the What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• Как да използвате анализа какво-ако на Excel

• Как да използвате функцията "Говорете клетки" на Excel

• 2 начина за използване на функцията за транспониране на Excel

• Как да разберем анализа какво-ако в Microsoft Excel

• Как да изтриете празни редове в Excel

• Как да премахнете дублиращи се редове в Excel

• Какво е Microsoft Publisher? Ръководство за начинаещи

• Как да използвате абсолютни препратки в Excel

• Използвайте клавиатурата, за да промените височината на реда и ширината на колоната в Excel

• Групирайте редове и колони в работен лист на Excel

• Как да коригирате ред в Excel

• Как да споделяте файл на Excel за лесно сътрудничество

• 3 начина за разделяне на клетка в Excel

• Как да коригирате #N/A грешки във формули на Excel като VLOOKUP

• Как да сортирате по дата в Excel

• Microsoft Outlook няма да се отвори? 10 начина за поправяне

• 4 начина да използвате отметка в Excel

• Как да премахнете решетъчните линии в Excel

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

• Как да напишете IF формула/изявление в Excel