Bár az Excel hosszú funkciólistája a Microsoft táblázatkezelő alkalmazásának egyik legcsábítóbb funkciója, van néhány kihasználatlan gyöngyszem, amely javítja ezeket a funkciókat. Az egyik gyakran figyelmen kívül hagyott eszköz a mi lenne, ha elemzés.

Az Excel Mi van, ha elemző^{(What-If Analysis)} eszköze három fő összetevőre oszlik. Az itt tárgyalt rész a hatékony Goal Seek szolgáltatás, amely lehetővé teszi, hogy egy függvénytől visszafelé dolgozzon, és meghatározza azokat a bemeneteket, amelyek szükségesek ahhoz, hogy egy cellában lévő képletből megkapja a kívánt kimenetet. Olvasson tovább, ha megtudja, hogyan kell használni az Excel Mi a helyzet elemzési célkereső^{(What-If Analysis Goal Seek)} eszközét.

Példa az Excel célkereső eszközére

Tegyük fel, hogy jelzáloghitelt szeretne felvenni lakásvásárláshoz, és aggódik amiatt, hogy a kölcsön kamata hogyan befolyásolja az éves törlesztőrészleteket. A jelzáloghitel összege 100 000 USD, és a kölcsönt 30 év alatt fizeti vissza.

Az Excel PMT funkciójával könnyen kitalálhatja, mennyi lenne az éves fizetés, ha a kamatláb 0% lenne. A táblázat valószínűleg így nézne ki:

Az A2 cella az éves kamatlábat, a B2 cella a kölcsön hosszát években, a C2 cella pedig a jelzáloghitel összegét jelöli. A D2 képlete a következő:

=PMT(A2,B2,C2)

és egy 30 éves futamidejű, 100 000 dolláros jelzáloghitel éves törlesztését jelenti 0%-os kamattal. Figyelje^(Notice) meg, hogy a D2 érték negatív, mivel az Excel azt feltételezi, hogy a kifizetések negatív pénzáramlást jelentenek az Ön pénzügyi helyzetéből.

Sajnos egyetlen jelzáloghitelező sem ad 100 000 dollárt 0%-os kamattal. Tegyük fel^(Suppose) , hogy kitalál valamit, és rájön, hogy megengedheti magának, hogy évi 6000 dollárt visszafizetjen jelzáloghitel-törlesztésként. Most azon töpreng, hogy mi a legmagasabb kamat, amelyet felvehet a kölcsönért, hogy biztosan ne fizessen többet évi 6000 dollárnál.

Sokan ebben a helyzetben egyszerűen elkezdenének számokat beírni az A2 cellába, amíg a D2-ben lévő szám eléri a körülbelül 6000 dollárt. A „Mi lenne, ha” elemzési célkereső^{(Analysis Goal Seek)} eszköz használatával azonban az Excel elvégezheti a munkát . Lényegében az Excel visszafelé működik a D4-es eredménytől egészen addig, amíg el nem éri azt a kamatlábat, amely kielégíti a 6000 dolláros maximális kifizetést.

Először kattintson az Adatok^(Data) fülre a szalagon^(Ribbon) , és keresse meg a Mi van, ha elemzés^{(What-If Analysis)} gombot az Adateszközök^{(Data Tools)} részben. Kattintson a Mi lenne, ha elemzés^{(What-If Analysis)} gombra, és válassza a Célkeresés^{(Goal Seek)} lehetőséget a menüből.

Az Excel megnyit egy kis ablakot, és csak három változó bevitelét kéri. A Set Cell változónak képletet tartalmazó cellának kell lennie. A mi példánkban ez D2 . A To Value változó az az összeg, amelyet a D2 -nél lévő cellának az elemzés végén meg kell adni.

Nálunk ez -6000 . Ne feledje, hogy az Excel a kifizetéseket negatív pénzáramlásnak tekinti. A By Changing Cell változó az a kamatláb, amelyet az Excelnek meg kell találnia, így a 100 000 dolláros jelzáloghitel évente mindössze 6 000 dollárba kerül. Tehát használja az A2 cellát .

Kattintson az OK gombra, és észreveheti, hogy az Excel egy csomó számot villog a megfelelő cellákban, amíg az iterációk végül egy végső számhoz nem konvergálnak. A mi esetünkben az A2-es cellának körülbelül 4,31%-ot kellene olvasnia.

Ez az elemzés azt mutatja, hogy ahhoz, hogy ne költsön el évente 6000 dollárnál többet egy 30 éves futamidejű, 100 000 dolláros jelzálogkölcsönre, a hitelt legfeljebb 4,31%-ra kell biztosítani. Ha folytatni szeretné a „mi lenne, ha” elemzéseket, kipróbálhatja a számok és változók különböző kombinációit, hogy feltárja, milyen lehetőségek állnak rendelkezésre a jelzáloghitel jó kamatának biztosítására.

Az Excel Mi van, ha elemzési célkereső^{(What-If Analysis Goal Seek)} eszköze hatékonyan kiegészíti a tipikus táblázatokban található különféle függvényeket és képleteket. Ha egy cellában lévő képlet eredményeiből visszafelé dolgozunk, akkor a számításaiban szereplő különböző változókat jobban áttekinthetjük.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excеl’s long list of functions is оne of the most enticing features of Miсrosoft’s spreadѕheet application, there a few underutilіzed gems that enhance these functions. One often-overlooked tool іs 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

• Az Excel „Mi lenne, ha” elemzésének használata

• Az Excel Speak Cells funkciójának használata

• 2 módszer az Excel transzponálási funkciójának használatára

• Hogyan lehet megérteni a „Mi lenne, ha” elemzést a Microsoft Excelben

• Az üres sorok törlése az Excelben

• Excel-munkalap beszúrása Word-dokumentumba

• Dátum szerinti rendezés az Excelben

• Hogyan távolítsuk el a rácsvonalakat az Excelben

• Hogyan keressünk Excelben

• Használja a billentyűzetet a sormagasság és az oszlopszélesség módosításához az Excelben

• 4 módszer az Excel konvertálására Google Táblázatokká

• Miért érdemes elnevezett tartományokat használni az Excelben?

• Az If és a Beágyazott If utasítások használata Excelben

• Hogyan javítsunk ki egy sort az Excelben

• Google Táblázatok vs Microsoft Excel – Mi a különbség?

• A #N/A hibák javítása az Excel-képletekben, például a VLOOKUP-ban

• Excel-fájl megosztása az egyszerű együttműködés érdekében

• Sorok és oszlopok csoportosítása egy Excel munkalapon

• Alapvető egyoszlopos és többoszlopos adatrendezés Excel-táblázatokban

• A PMT függvény használata Excelben