Chociaż długa lista funkcji programu Excel jest jedną z najbardziej przyciągających cech aplikacji arkusza kalkulacyjnego firmy Microsoft, istnieje kilka niewykorzystanych klejnotów, które zwiększają te funkcje. Jednym często pomijanym narzędziem jest analiza typu "co by było".
Narzędzie analizy typu "co by było" Excela zostało podzielone na trzy główne komponenty. Omówiona tutaj część stanowi potężną funkcję szukania celu, która umożliwia pracę wstecz z funkcji i określenie danych wejściowych niezbędnych do uzyskania pożądanego wyniku z formuły w komórce. Czytaj dalej, aby dowiedzieć się, jak korzystać z narzędzia do wyszukiwania celu typu "szukanie celu" Excela.
Przykład programu Excel do wyszukiwania celu
Załóżmy, że chcesz wykupić kredyt hipoteczny na zakup domu i obawiasz się, w jaki sposób oprocentowanie pożyczki wpłynie na roczne płatności. Kwota kredytu hipotecznego wynosi 100 000 USD, a spłatę pożyczki spłacisz w ciągu 30 lat.
Korzystając z funkcji PMT programu Excel, możesz łatwo ustalić, jakie będą roczne płatności, jeśli stopa procentowa wynosi 0 %. Arkusz kalkulacyjny prawdopodobnie wyglądałby tak:
Komórka o rozmiarze A2 reprezentuje roczną stopę procentową, komórka o B2 jest długością pożyczki w latach, a komórka w C2 to kwota kredytu hipotecznego. Formuła w D2 to:
= PMT (A2, B2, C2)
i przedstawia roczne płatności z 30-letniego kredytu hipotecznego o wartości 100 000 USD. 0% odsetek. Zauważ, że liczba w D2 jest ujemna, ponieważ program Excel zakłada, że płatności są ujemnym przepływem środków pieniężnych z twojej pozycji finansowej.
Niestety, żaden pożyczkodawca hipoteczny nie pożyczy ci 100 000 USD przy 0% oprocentowaniu. Przypuśćmy, że dokonasz pewnych odkryć i dowiesz się, że możesz pozwolić sobie na spłatę 6.000 USD rocznie na płatnościach hipotecznych. Zastanawiasz się teraz, jaka jest najwyższa stopa procentowa, jaką możesz wziąć na pożyczkę, aby upewnić się, że nie zapłacisz więcej niż 6 000 $ rocznie.
Wiele osób w tej sytuacji po prostu zacznie pisać cyfry w komórce A2, aż liczba w D2 osiągnie około 6000 USD. Możesz jednak sprawić, aby program Excel wykonał pracę za Ciebie, korzystając z narzędzia do wyszukiwania celu typu "What-If". Zasadniczo sprawisz, że Excel będzie działać wstecz od wyniku w D4, dopóki nie osiągnie stopy procentowej, która zapewni maksymalną wypłatę w wysokości 6000 $.
Rozpocznij, klikając kartę DaneWstążka i znajdując przycisk Analiza co-jeśliw sekcji Narzędzia danych. Kliknij przycisk Analiza co-jeślii wybierz z menu Szukanie celu.
Excel otwiera małe okno i prosi o podanie tylko trzech zmiennych. Zmienna Ustaw komórkęmusi być komórką zawierającą formułę. W naszym przykładzie jest to D2. Zmienna Do wartościto kwota, jaką ma mieć komórka w D2na końcu analizy.
Dla nas jest to >-6,000. Pamiętaj, że Excel widzi płatności jako ujemny przepływ gotówki. Zmienna Zmieniająca się komórkato stopa procentowa, którą program Excel ma dla Ciebie znaleźć, aby hipoteki o wartości 100 000 USD kosztowały tylko 6000 USD rocznie. Tak więc użyj komórki A2.
Kliknij przycisk OKi możesz zauważyć, że Excel miga kilka liczb w odpowiednich komórkach, aż iteracje w końcu się zbiegają ostatnia cyfra. W naszym przypadku komórka na A2 powinna teraz przeczytać około 4,31%.
Ta analiza mówi nam, że aby nie wydawać 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ę what-if, możesz wypróbować różne kombinacje liczb i zmiennych, aby zbadać opcje, jakie masz, starając się zabezpieczyć dobrą stopę procentową od kredytu hipotecznego.
Analiza typu "co by było" w Excelu Narzędzie "Szukaj wyniku" to potężne uzupełnienie 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 lepiej poznać różne zmienne w swoich obliczeniach.