Excel - analiza za pomocą menedżera scenariuszy

Menedżer scenariusz jest niezwykle przydatnym narzędziem do przeprowadzania analiz. Używamy go w sytuacji, gdy znamy wartości wejściowe i chcemy dla różnych kombinacji tych wartości obliczyć wynik. W poniższym poradniku zajmiemy się prostym przykładem, który powinien dobrze wyjaśnić istotę działania tego narzędzia oraz zalety jakie wynikają z jego użycia.

---

Kurs Excel - zaawansowane techniki

Zaawansowane techniki pracy z Excelem, których znajomość otwiera przed tobą niespotykane możliwości. Dowiedz się więcej

---

Wprowadzenie do przykładu

Rozpoczniemy od omówienia struktury arkusza, który posłuży jako podstawa naszego przykładu.

Arkusz ma reprezentować prosty budżet miesięczny. Kluczowe są tutaj dwie komórki: Suma przychodów miesięcznych (F9) oraz Suma wydatków miesięcznych (G9). W sąsiedniej komórce (H9) wyliczane jest saldo (różnica F9-G9). Dodatkowo w górnej części wyliczany jest na bieżąco procent wydanego przychodu. W takim właśnie arkuszu możemy rozpatrzeć kilka scenariuszy:

• Jakie saldo uzyskamy jeśli wydatki będą niskie a przychody wysokie?
• Jakie saldo uzyskamy jeśli wydatki będą wysokie a przychody niskie?
• A co jeśli zarówno przychody jak wydatki będę niskie?

Takie typowe scenariusze można przeanalizować i trwale zapisać właśnie za pomocą Menedżera scenariuszy.

Pierwszy scenariusz

Na samym początku zwróć uwagą na bieżące wartości w arkuszu: przychody 12000zł, wydatki 7450zł co daje nam ostateczne saldo 4550zł. Załóżmy teraz, że są to takie średnie/przeciętne wartości miesięczne i chcemy je zapisać jako bazowy scenariusz (scenariusz możemy umownie nazwać „Normalny”).
Aby zapisać te bieżące wartości w formie scenariusza należy najpierw przejść do karty DANE. Następnie klikamy na narzędziu Analiza warunkowa (grupa Narzędzia danych) i z listy wybieramy pozycję Menedżer scenariuszy. Powinno się uruchomić takie oto okno:

Na chwilę obecną w oknie widoczny jest komunikat „Nie zdefiniowano żadnych scenariuszy…”. Zgodnie z tym co napisano w dalszej części komunikatu, musimy teraz kliknąć na przycisku Dodaj, aby rozpocząć definiowanie naszego pierwszego scenariusza.
Kliknięcie na przycisk spowoduje otwarcie kolejnego okna, które tym razem w górnej części posiada nagłówek „Dodawanie scenariusza”. W pierwszym polu wprowadzimy nazwę dla naszego scenariusza: nazwiemy go po prostu „Normalny”. Drugie pole, czyli „Komórki zmieniane” jest bardzo ważne, ponieważ to właśnie w nim definiujemy komórki, których wartości będę się zmieniały w poszczególnych scenariuszach. W tym wypadku mówimy tutaj o komórce z wydatkami oraz komórce do której wpisujemy przychody (czyli F9 oraz G9). Adresy tych komórek trzeba po prostu wprowadzić do tego pola.
Tak powinien wyglądać wypełniony formularz scenariusza:

Pozostałe opcje możemy teraz zignorować i po prostu kliknąć na przycisku OK. Po chwili powinno się pojawić takie małe okienko, w których musimy przypisać wartości do dwóch komórek scenariusza (czyli jak zwykle F9 oraz G9). Możemy założyć, że w tym scenariuszu zapisane zostaną bieżące wartości z arkusza, czyli do tych komórek nie wprowadzamy żadnych zmian.

Pozostaje tylko kliknąć na przycisku OK. Zostaniemy dzięki temu przeniesieni do głównego okna menedżera z jednym gotowym scenariusz „normalnym”.

Drugi scenariusz

Scenariusz zdefiniowany przed chwilą jest dość prosty. Składa się z „nazwy” oraz z dwóch komórek przechowujących wartości scenariusza. Prawdziwe zalety tego doskonałego narzędzia Excela ujawniają się dopiero wtedy, gdy zaczniesz definiować większą ilość scenariuszy. Po prostu nawet bardzo skomplikowane scenariusze obejmujące dużą ilość danych wejściowych można wczytać do arkusza dosłownie za pomocą 2-3 kliknięć.

Może więc od razu przystąpimy do pracy nad drugim scenariuszem, który będzie reprezentował następującą sytuację: duże przychody i niskie wydatki.
Generalnie w tym celu należy powtórzyć całą opisaną procedurę dodawanie scenariusza, z tym że teraz oczywiście użyte zostaną inne wartości. Popatrzmy więc na najważniejsze różnice:

Nasz scenariusz nr 2 będzie się nazywał „DużyPrzychódNiskieWydatki”. Zauważ, że komórki zmieniane pozostają bez zmian jeśli chodzi o ich adresy.
Różnica polega na wartościach:

Mamy tutaj wartość wysoką (14800zł) dla komórki z przychodem oraz wartość niską (7000zł) dla komórki z wydatkami. Otrzymujemy w ten sposób saldo 7800zł. Oczywiście te wartości są narzucone z góry po to aby pokazać zasadę działania narzędzia. W każdym razie mamy już dwa scenariusze na tym etapie.

Trzeci scenariusz

Może stwórzmy teraz trzeci scenariusz, który prezentuje odwrotną sytuację niż scenariusz poprzedni czyli niski przychód i duże wydatki. Jak zwykle trzeba powtórzyć całą procedurę dodawania scenariusza. Właściwości scenariusza będą wyglądały tak:

Natomiast wartości wyglądają następująco:

Zarządzanie scenariuszami w programie Excel

Na tym etapie pojawia się oczywiście pytanie co zrobić z tymi trzema scenariuszami. Na chwilę obecną okno menedżera powinno wyglądać tak:

Każdy z tych scenariuszy możemy teraz błyskawicznie załadować do arkusza. Wystarczy zaznaczyć jego nazwę na liście i kliknąć na przycisku Pokaż na dole okna. Dzięki temu, wartości zapisane w danym scenariuszu zostaną wczytane do arkusza we właściwych komórkach.
Tak na przykład wygląda arkusz po wczytaniu scenariusza DużyPrzychódNiskieWydatki:

I tym polega właśnie główna idea tego narzędzia. Możesz zdefiniować różne scenariusze i potem szybko je wczytać. Chociażby po to, aby zaprezentować je odbiorcom twojej pracy.

Co dalej?

Okno menedżera pozwala wczytywać scenariusz względnie szybko ale samo uruchamianie tego narzędzia może być trochę niewygodne. Dobrym pomysłem może być dodanie skrótu do scenariuszy w pasku narzędzi szybki dostęp.
Najpierw trzeba przejść do edycji zawartości paska:

Powinno się otworzyć okno opcji programu Excel. W górnej listy po lewej stronie należy wybrać Wszystkie polecenia. Potem na liście odnajdujemy pozycję Scenariusz i dodajemy ją do paska (trzeba pozycję zaznaczyć i kliknąć na przycisku Dodaj). Teraz wystarczy zatwierdzić zmiany klikając na przycisku OK. Na koniec popatrzmy na to nowe polecenie Scenariusz na pasku szybkiego dostępu.

Warto zauważyć, że po kliknięciu rozwinie się lista dostępnych scenariuszy. Można je więc załadować do arkusza. Funkcjonalność taka sama jak w oknie menedżera z tym, że dostęp do poszczególnych scenariuszy jest znacznie łatwiejszy.