W tej lekcji przyjrzymy się bardziej zaawansowanym narzędziom Excela, które umożliwiają przeprowadzanie tzw. What-If Analysis. Analiza ta pozwala na symulowanie różnych scenariuszy i ocenę, jak zmiana określonych zmiennych wpłynie na wynik końcowy. Excel oferuje kilka narzędzi do przeprowadzania takich analiz, w tym Data Tables, Scenario Manager oraz Goal Seek.
Co to jest What-If Analysis?
What-If Analysis to technika używana do przewidywania skutków zmiany wartości danych wejściowych na wynik końcowy. Jest to szczególnie przydatne w przypadku budżetowania, prognozowania finansowego, planowania projektów, analizy kosztów i wielu innych dziedzin, gdzie chcesz zobaczyć, jak różne wartości lub scenariusze wpływają na wynik.
Narzędzia do What-If Analysis w Excelu
W Excelu znajdziesz trzy główne narzędzia do przeprowadzania What-If Analysis:
- Data Tables: Pozwalają na szybkie wyliczenie różnych wyników na podstawie zmian w jednej lub dwóch zmiennych.
- Scenario Manager: Umożliwia przechowywanie i analizowanie wielu zestawów wartości wejściowych (tzw. scenariuszy) i porównywanie wyników.
- Goal Seek: Pozwala obliczyć, jaka wartość zmiennej wejściowej musi zostać osiągnięta, aby uzyskać określony wynik końcowy.
Data Tables
Data Tables to jedno z najprostszych narzędzi What-If Analysis w Excelu. Pozwalają one na analizowanie wpływu zmian w jednej lub dwóch zmiennych na wynik formuły.
Data Table z jedną zmienną
Data Table z jedną zmienną pozwala na analizowanie wpływu zmiany jednej wartości na wynik formuły. Przykładowo, możesz użyć tej tabeli, aby sprawdzić, jak zmiana stopy procentowej wpłynie na ratę kredytu.
Tworzenie Data Table z jedną zmienną – krok po kroku:
- Wprowadź dane: Na początek wprowadź do arkusza wszystkie niezbędne dane, w tym formułę, którą chcesz analizować. Przykładowo, możesz obliczyć ratę kredytu przy różnych stopach procentowych, używając funkcji PMT (Payment).Przykład:
- W komórce A1 wpisz „Kwota kredytu” i wprowadź kwotę kredytu w komórce A2, np. 100,000.
- W komórce A3 wpisz „Stopa procentowa” i wprowadź stopę procentową, np. 5%.
- W komórce A4 wpisz „Liczba lat” i wprowadź liczbę lat spłaty kredytu, np. 20.
- W komórce A6 wpisz formułę, która oblicza ratę kredytu:
=PMT(A3/12, A4*12, -A2).
- Utwórz listę zmiennych: Teraz wprowadź listę zmiennych, które chcesz przetestować. Na przykład, jeśli chcesz zobaczyć, jak różne stopy procentowe wpływają na ratę kredytu, wpisz listę różnych wartości stóp procentowych w kolumnie B, począwszy od komórki B6: np. 3%, 4%, 5%, 6%, 7%.
- Utwórz Data Table:
- Zaznacz zakres od B6 do B10 (wartości stóp procentowych) oraz komórkę z formułą (A6).
- Przejdź do zakładki Data na wstążce.
- Wybierz What-If Analysis -> Data Table.
- W oknie dialogowym Data Table wprowadź zmienną dla kolumny: W tym przypadku wskaż komórkę A3 (stopa procentowa).
- Kliknij OK.
Excel automatycznie obliczy wynik dla każdej stopy procentowej, co pozwoli Ci zobaczyć, jak różne stopy procentowe wpływają na miesięczną ratę kredytu.
Data Table z dwiema zmiennymi
Data Table z dwiema zmiennymi pozwala na analizowanie wpływu zmian dwóch różnych zmiennych na wynik formuły. Na przykład, możesz chcieć zobaczyć, jak zarówno stopa procentowa, jak i liczba lat kredytu wpływają na wysokość raty.
Tworzenie Data Table z dwiema zmiennymi – krok po kroku:
- Wprowadź dane: Użyj tych samych danych i formuły, co wcześniej.
- Utwórz siatkę zmiennych: W kolumnie B wpisz różne stopy procentowe, a w wierszu 6 wpisz różne wartości dla liczby lat spłaty kredytu (np. 15, 20, 25, 30 lat).
- Utwórz Data Table:
- Zaznacz zakres, który zawiera zarówno wartości zmiennych, jak i formułę.
- Przejdź do zakładki Data, wybierz What-If Analysis -> Data Table.
- W oknie Data Table wprowadź zmienną dla kolumny (np. A3 – stopa procentowa) oraz zmienną dla wiersza (np. A4 – liczba lat kredytu).
- Kliknij OK.
W wyniku otrzymasz tabelę, która pokazuje wpływ zmiany obu zmiennych na wynik końcowy (wysokość raty kredytu).
Scenario Manager
Scenario Manager pozwala na przechowywanie różnych zestawów wartości i porównywanie wyników w oparciu o te scenariusze. Jest to użyteczne, gdy chcesz przetestować kilka różnych sytuacji, np. różne plany budżetowe lub prognozy sprzedaży.
Tworzenie scenariusza – krok po kroku:
- Przygotuj dane: Wprowadź dane do arkusza, które będą bazą do tworzenia scenariuszy. Na przykład, możesz chcieć porównać różne prognozy sprzedaży dla nadchodzących lat w zależności od trzech różnych scenariuszy (optymistyczny, realistyczny, pesymistyczny).
- Otwórz Scenario Manager:
- Przejdź do zakładki Data.
- Kliknij What-If Analysis -> Scenario Manager.
- W oknie Scenario Manager kliknij przycisk Add.
- Zdefiniuj scenariusze:
- Nadaj nazwę pierwszemu scenariuszowi (np. „Optymistyczny”).
- Wybierz komórki, które chcesz zmieniać (np. wartości prognoz sprzedaży).
- Wprowadź różne wartości dla tego scenariusza.
- Powtórz ten proces dla kolejnych scenariuszy, takich jak „Realistyczny” i „Pesymistyczny”.
- Porównanie scenariuszy:
- Po dodaniu wszystkich scenariuszy możesz w dowolnej chwili przełączać się między nimi, klikając przycisk Show.
- Możesz także wygenerować raport porównujący wyniki dla wszystkich scenariuszy, klikając Summary i wybierając typ raportu.
Goal Seek
Goal Seek to narzędzie, które pozwala na odwrócenie procesu obliczeniowego – zamiast obliczać wynik na podstawie znanych wartości wejściowych, Goal Seek pozwala znaleźć wartość jednej zmiennej, która daje określony wynik.
Przykład użycia Goal Seek:
Załóżmy, że chcesz dowiedzieć się, jaką kwotę kredytu możesz zaciągnąć, aby miesięczna rata nie przekroczyła 1000 zł.
- Wprowadź dane: Użyj tej samej formuły PMT jak wcześniej, ale tym razem przyjmij, że chcesz obliczyć kwotę kredytu, a nie ratę.
- Uruchom Goal Seek:
- Przejdź do zakładki Data.
- Kliknij What-If Analysis -> Goal Seek.
- Wprowadź parametry:
- W oknie dialogowym wprowadź komórkę z formułą (np. A6, gdzie znajduje się wynik obliczanej raty).
- W polu To value wprowadź 1000 (żądana wysokość raty).
- W polu By changing cell wskaż komórkę z kwotą kredytu (np. A2).
- Kliknij OK.
Excel automatycznie obliczy, jaka kwota kredytu odpowiada zadanej wysokości raty.
Podsumowanie
W tej lekcji nauczyłeś się, jak korzystać z trzech głównych narzędzi What-If Analysis w Excelu: Data Tables, Scenario Manager i Goal Seek. Każde z tych narzędzi umożliwia analizowanie różnych scenariuszy, symulowanie zmian i ocenę ich wpływu na wyniki, co jest niezwykle przydatne w planowaniu, prognozowaniu i podejmowaniu decyzji. Dzięki tym narzędziom możesz lepiej zrozumieć, jak różne czynniki wpływają na Twoje dane, a także dostosować swoje plany do zmieniających się warunków. W kolejnych lekcjach przyjrzymy się jeszcze bardziej zaawansowanym technikom analizy danych, takim jak Solver oraz Analiza scenariuszy na jeszcze głębszym poziomie.
Gratulacje! Ukończyłeś lekcję 7.
Przejdź teraz do lekcji 8 >> Solver i zaawansowana optymalizacja w Excelu
Spis Treści - darmowy kurs Excel
Wprowadzenie: Czym jest arkusz kalkulacyjny?
Lekcja 1: Wprowadzanie i formatowanie danych w Excelu
Lekcja 2: Korzystanie z formuł i funkcji w Excelu
Lekcja 3: Tabele i zarządzanie danymi w Excelu
Lekcja 4: Tworzenie wykresów w Excelu
Lekcja 5: Tabele przestawne w Excelu
Lekcja 6: Zaawansowane techniki filtrowania i sortowania danych w Excelu
Lekcja 7: What-If Analysis w Excelu
Lekcja 8: Solver i zaawansowana optymalizacja w Excelu
Lekcja 9: Zaawansowane scenariusze z użyciem Solver w Excel
Lekcja 10: Analysis ToolPak – Narzędzie do zaawansowanej analizy danych w Excelu
Lekcja 11: Power Query w Excelu – zaawansowane techniki pracy z danymi
Lekcja 12: Power Pivot – Analiza dużych zestawów danych w Excelu
Lekcja 13: Power BI – Tworzenie interaktywnych raportów i wizualizacji danych
Lekcja 14: Power Automate – Automatyzacja procesów biznesowych i Power BI
Lekcja 15: Power Apps – Tworzenie aplikacji na podstawie danych z Excela i Power BI
Lekcja 16: Zaawansowane funkcje DAX w Power BI i Power Pivot
Lekcja 17: Tworzenie zaawansowanych raportów w Power BI – Case Study
Lekcja 18: Zarządzanie dużymi bazami danych w Power BI
Lekcja 19: Analiza danych z wykorzystaniem Excela, Power BI i innych narzędzi
