Chociaż długa lista funkcji Excela jest jedną z najbardziej kuszących funkcji arkusza kalkulacyjnego Microsoftu, istnieje kilka niedostatecznie wykorzystanych klejnotów, które ulepszają te funkcje. Jednym z często pomijanych narzędzi jest analiza warunkowa.

Narzędzie analizy^{(What-If Analysis)} warunkowej programu Excel składa się z trzech głównych elementów. Omawiana tutaj część to zaawansowana funkcja wyszukiwania celu^{(Goal Seek)} , która umożliwia pracę wstecz od funkcji i określanie danych wejściowych niezbędnych do uzyskania żądanego wyniku z formuły w komórce. Czytaj dalej, aby dowiedzieć się, jak korzystać z narzędzia wyszukiwania celu analizy warunkowej programu Excel.^{(What-If Analysis Goal Seek)}

Przykład narzędzia wyszukiwania celu w programie Excel

Załóżmy, że chcesz zaciągnąć kredyt hipoteczny na zakup domu i obawiasz się, jak oprocentowanie kredytu wpłynie na roczne płatności. Kwota kredytu hipotecznego wynosi 100 000 USD, a pożyczkę spłacasz w ciągu 30 lat.

Korzystając z funkcji PMT programu Excel , możesz łatwo określić, jakie byłyby roczne płatności, gdyby stopa procentowa wynosiła 0%. Arkusz kalkulacyjny prawdopodobnie wyglądałby mniej więcej tak:

Komórka w A2 reprezentuje roczną stopę procentową, komórka w B2 to długość kredytu w latach, a komórka w C2 to kwota kredytu hipotecznego. Wzór w D2 to:

=PMT(A2,B2,C2)

i reprezentuje roczne spłaty 30-letniego kredytu hipotecznego w wysokości 100 000 USD z 0% odsetkami. Zauważ^(Notice) , że liczba w D2 jest ujemna, ponieważ program Excel zakłada, że ​​płatności są ujemnym przepływem pieniężnym z Twojej sytuacji finansowej.

Niestety, żaden pożyczkodawca hipoteczny nie pożyczy Ci 100 000 USD z oprocentowaniem 0%. Załóżmy^(Suppose) , że robisz pewne obliczenia i dowiadujesz się, że możesz sobie pozwolić na spłatę 6000 dolarów rocznie w postaci spłat kredytu hipotecznego. Zastanawiasz się teraz, jakie jest najwyższe oprocentowanie, jakie możesz zaciągnąć za pożyczkę, aby upewnić się, że nie zapłacisz więcej niż 6000 USD rocznie.

Wiele osób w takiej sytuacji po prostu zaczęłoby wpisywać liczby w komórce A2, aż liczba w D2 osiągnęłaby około 6000 USD. Możesz jednak sprawić, by program Excel wykonał pracę za Ciebie, korzystając z narzędzia wyszukiwania celu analizy warunkowej . ^{(Analysis Goal Seek)}Zasadniczo sprawisz, że program Excel będzie pracował wstecz od wyniku w D4 aż do uzyskania stopy procentowej, która zaspokoi Twoją maksymalną wypłatę w wysokości 6000 USD.

Rozpocznij od kliknięcia karty Dane^(Data) na Wstążce^(Ribbon) i zlokalizowania przycisku Analiza^{(What-If Analysis)} warunkowa w sekcji Narzędzia danych^{(Data Tools)} . Kliknij przycisk Analiza^{(What-If Analysis)} warunkowa i wybierz z menu opcję Szukanie celu.^{(Goal Seek)}

Excel otwiera małe okno i prosi o wprowadzenie tylko trzech zmiennych. Zmienna Set Cell musi być komórką zawierającą formułę. W naszym przykładzie jest to D2 . Zmienna Do wartości^{(To Value)} to kwota, jaką chcesz, aby komórka w D2 znajdowała się na końcu analizy.

Dla nas jest to -6000 . Pamiętaj, że Excel postrzega płatności jako ujemny przepływ środków pieniężnych. Zmienna By Change Cell^{(By Changing Cell)} to stopa procentowa, którą Excel ma znaleźć dla Ciebie, aby kredyt hipoteczny w wysokości 100 000 USD kosztował Cię tylko 6 000 USD rocznie. Użyj więc komórki A2 .

Kliknij przycisk OK i możesz zauważyć, że Excel miga kilka liczb w odpowiednich komórkach, aż iteracje w końcu zbiegną się w ostateczną liczbę. W naszym przypadku komórka na A2 powinna teraz czytać około 4,31%.

Ta analiza mówi nam, że aby nie wydać więcej niż 6000 USD rocznie na 30-letni kredyt hipoteczny o wartości 100 000 USD, musisz zabezpieczyć pożyczkę na nie więcej niż 4,31%. Jeśli chcesz kontynuować analizę typu „co, jeśli”, możesz wypróbować różne kombinacje liczb i zmiennych, aby zbadać dostępne opcje, gdy próbujesz uzyskać dobre oprocentowanie kredytu hipotecznego.

Narzędzie analizy typu „co, jeśli^{(What-If Analysis Goal Seek)} ” w programie Excel jest potężnym uzupełnieniem różnych funkcji i formuł występujących w typowym arkuszu kalkulacyjnym. Pracując wstecz od wyników formuły w komórce, możesz wyraźniej eksplorować różne zmienne w obliczeniach.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excel’s long list of functions is one of the most enticing features of Microsoft’s spreadsheet application, there a few underutilized gems that enhance these functiоns. One often-overlooked toоl 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

• Jak korzystać z analizy warunkowej programu Excel

• Jak korzystać z funkcji Speak Cells programu Excel

• 2 sposoby korzystania z funkcji transpozycji programu Excel

• Jak zrozumieć analizę warunkową w programie Microsoft Excel

• Jak usunąć puste linie w Excelu?

• Zaawansowany przewodnik VBA dla MS Excel

• Jak utworzyć wiele połączonych list rozwijanych w programie Excel?

• Jak szybko wstawić wiele wierszy w programie Excel

• Jak korzystać z odwołań bezwzględnych w programie Excel

• Jak zrobić wykres kołowy w programie Excel

• Jak korzystać z wypełniania Flash w programie Excel

• Jak scalić dane w wielu plikach Excel

• 4 sposoby użycia znacznika wyboru w programie Excel

• Jak scalić komórki, kolumny i wiersze w programie Excel

• Co to jest tablica VBA w programie Excel i jak ją zaprogramować?

• Co to jest Microsoft Publisher? Przewodnik dla początkujących

• Wyśrodkuj dane arkusza roboczego w programie Excel do drukowania

• Podłączanie Excela do MySQL

• Jak sortować według daty w programie Excel

• 5 sposobów na konwersję tekstu na liczby w programie Excel