Hoci je dlhý zoznam funkcií Excelu jednou z najlákavejších funkcií tabuľkového procesora od Microsoftu, existuje niekoľko málo využívaných skvostov, ktoré tieto funkcie vylepšujú. Jedným z často prehliadaných nástrojov je analýza What-If.

Nástroj What-If Analysis^{(What-If Analysis)} v Exceli je rozdelený do troch hlavných komponentov. Časť, o ktorej sa tu diskutuje, je výkonná funkcia hľadania cieľa^{(Goal Seek)} , ktorá vám umožňuje pracovať spätne od funkcie a určiť vstupy potrebné na získanie požadovaného výstupu zo vzorca v bunke. Čítajte ďalej a zistite, ako používať nástroj What-If Analysis Goal Search v Exceli.^{(What-If Analysis Goal Seek)}

Príklad nástroja Excel na vyhľadávanie cieľov

Predpokladajme, že si chcete vziať hypotekárny úver na kúpu domu a zaujíma vás, ako úroková sadzba úveru ovplyvní ročné splátky. Výška hypotéky je 100 000 $ a úver budete splácať v priebehu 30 rokov.

Pomocou funkcie PMT v Exceli môžete ľahko zistiť, aké by boli ročné platby, ak by úroková sadzba bola 0 %. Tabuľka by mohla vyzerať asi takto:

Bunka pri A2 predstavuje ročnú úrokovú sadzbu, bunka pri B2 je dĺžka úveru v rokoch a bunka pri C2 je výška hypotekárneho úveru. Vzorec v D2 je:

=PMT(A2,B2,C2)

a predstavuje ročné splátky 30-ročnej hypotéky vo výške 100 000 USD s 0 % úrokom. Všimnite si^(Notice) , že číslo v D2 je záporné, pretože Excel predpokladá, že platby sú záporným peňažným tokom z vašej finančnej pozície.

Žiaľ, žiadny hypotekárny veriteľ vám nepožičia 100 000 USD s 0% úrokom. Predpokladajme^(Suppose) , že sa trochu zamyslíte a zistíte, že si môžete dovoliť splácať 6 000 dolárov ročne na splátkach hypotéky. Teraz sa pýtate, aká je najvyššia úroková sadzba, ktorú si môžete za pôžičku vziať, aby ste sa uistili, že nakoniec nezaplatíte viac ako 6 000 dolárov ročne.

Mnoho ľudí v tejto situácii jednoducho začne písať čísla do bunky A2, kým číslo v D2 nedosiahne približne 6 000 USD. Môžete však prinútiť Excel , aby to urobil za vás, pomocou nástroja What-If Analysis Goal Search^{(Analysis Goal Seek)} . V podstate prinútite Excel pracovať spätne od výsledku v D4, kým nedosiahne úrokovú sadzbu, ktorá uspokojí vašu maximálnu výplatu 6 000 USD.

Začnite kliknutím na kartu Údaje na ^(Data)páse^(Ribbon) s nástrojmi a vyhľadaním tlačidla Analýza^{(What-If Analysis)} čohokoľvek v časti Nástroje údajov^{(Data Tools)} . Kliknite na tlačidlo What-If Analysis a z ponuky vyberte položku Hľadanie cieľa^{(Goal Seek)} .

Excel otvorí malé okno a požiada vás, aby ste zadali iba tri premenné. Premenná Set Cell musí byť bunka, ktorá obsahuje vzorec. V našom príklade je to D2 . Premenná To Value je množstvo, ktoré má byť bunka v D2 na konci analýzy.

U nás je to -6000 . Pamätajte, že Excel vníma platby ako negatívny peňažný tok. Premenná By Changeing Cell^{(By Changing Cell)} je úroková sadzba, ktorú chcete, aby vám Excel našiel, takže hypotéka v hodnote 100 000 USD vás bude stáť iba 6 000 USD ročne. Takže použite bunku A2 .

Kliknite na tlačidlo OK^(OK) a môžete si všimnúť, že Excel v príslušných bunkách bliká veľa čísel, až kým sa iterácie nakoniec nezblížia ku konečnému číslu. V našom prípade by bunka na A2 mala teraz ukazovať približne 4,31 %.

Táto analýza nám hovorí, že ak nechcete minúť viac ako 6 000 USD ročne na 30-ročnú hypotéku v hodnote 100 000 USD, musíte si zabezpečiť úver maximálne na 4,31 %. Ak chcete pokračovať v analýzach typu „čo keby“, môžete vyskúšať rôzne kombinácie čísel a premenných, aby ste preskúmali možnosti, ktoré máte, keď sa snažíte zabezpečiť dobrú úrokovú sadzbu na hypotéku.

Nástroj What-If Analysis Goal Seek^{(What-If Analysis Goal Seek)} v Exceli je výkonným doplnkom rôznych funkcií a vzorcov, ktoré sa nachádzajú v typickom tabuľkovom procesore. Keď budete pracovať spätne od výsledkov vzorca v bunke, môžete jasnejšie preskúmať rôzne premenné vo svojich výpočtoch.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long list of functions is one of the most enticіng features of Microsoft’s spreadsheet application, there a few underutilized gems that enhance theѕe funсtions. One often-overlooked tool is the What-If Anаlysis.

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

• Ako používať analýzu What-If v Exceli

• Ako používať funkciu Speak Cells v Exceli

• 2 spôsoby použitia funkcie Transponovať v Exceli

• Ako porozumieť analýze What-If v programe Microsoft Excel

• Ako odstrániť prázdne riadky v Exceli

• Zoskupte riadky a stĺpce v pracovnom hárku programu Excel

• Pri kopírovaní vzorca v Exceli zachovajte odkazy na bunky

• Ako presúvať stĺpce v Exceli

• Tabuľky Google vs Microsoft Excel – Aké sú rozdiely?

• Ako používať funkciu PMT v Exceli

• Ako vytvoriť viacero prepojených rozbaľovacích zoznamov v Exceli

• Ako prispôsobiť funkciu prilepenia programu Word

• Ako odstrániť duplicitné riadky v Exceli

• Ako otvoriť Word a Excel v núdzovom režime

• 5 spôsobov, ako previesť text na čísla v Exceli

• Ako vytvoriť jednoduchú kontingenčnú tabuľku v Exceli

• Ako vytvoriť koláčový graf v Exceli

• Ako vytvoriť makro alebo skript VBA v programe Excel

• Pripojenie Excelu k MySQL

• Ako odstrániť mriežku v Exceli