Przełączanie między różnymi zestawami wartości przy użyciu scenariuszy

W programie Excel dostępne są trzy rodzaje narzędzi What-If Analysis: Scenariusze, Tabele danych i Wyszukiwanie celów. Scenariusze i tabele danych biorą zestawy wartości wejściowych i projekt do przodu, aby określić możliwe wyniki. Funkcja szukania wyniku różni się od scenariuszy i tabel danych tym, że w celu określenia możliwych wartości wejściowych dających taki wynik potrzeba uzyskania wyniku i jego przesunięcia do tyłu.

W każdym scenariuszu można zmieścić maksymalnie 32 wartości zmiennych. Jeśli chcesz przeanalizować więcej niż 32 wartości, a wartości reprezentują tylko jedną lub dwie zmienne, możesz użyć tabel danych. Chociaż jest ograniczona tylko do jednej lub dwóch zmiennych (jednej dla komórki wprowadzania wiersza i jednej dla komórki wprowadzania danych w kolumnie), tabela danych może zawierać dowolną liczbę różnych wartości zmiennych. Scenariusz może mieć maksymalnie 32 różne wartości, ale można utworzyć dowolną liczbę scenariuszy.

Oprócz tych trzech narzędzi można instalować dodatki ułatwiające wykonywanie What-If Analizy, takie jak dodatek Solver. Dodatek Solver jest podobny do funkcji szukania wyniku, ale obsługuje więcej zmiennych. Można też tworzyć prognozy za pomocą uchwytu wypełniania i różnych wbudowanych poleceń programu Excel. W przypadku bardziej zaawansowanych modeli można użyć dodatku Analysis ToolPak.

Załóżmy, że chcesz utworzyć budżet, ale nie masz pewności co do przychodów. Za pomocą scenariuszy można zdefiniować różne możliwe wartości przychodu, a następnie przełączać się między scenariuszami w celu przeprowadzenia analiz warunkowych.

Załóżmy na przykład, że najgorszy scenariusz budżetowy to Przychód brutto w wysokości 50 000 zł, a Koszty towarów sprzedanych w wysokości 13 200 zł, pozostawiając 36 800 zł zysku brutto. Aby zdefiniować ten zestaw wartości jako scenariusz, należy najpierw wprowadzić wartości w arkuszu, jak pokazano na poniższej ilustracji:

Komórki Changing (Zmienianie) zawierają wpisane wartości, natomiast komórka Wynik zawiera formułę opartą na komórce Zmienianie (na tej ilustracji komórka B4 zawiera formułę =B2-B3).

Następnie użyj okna dialogowego Menedżer scenariuszy , aby zapisać te wartości jako scenariusz. Goto the Data tab > What-If Analysis > Scenario Manager > Add.

W oknie dialogowym Nazwa scenariusza nadaj scenariuszowi nazwę Najgorszy przypadek i określ, że komórki B2 i B3 to wartości, które zmieniają się między scenariuszami. Jeśli zaznaczysz pozycję Zmienianie komórek w arkuszu przed dodaniem scenariusza, Menedżer scenariuszy automatycznie wstawi komórki, w przeciwnym razie możesz wpisać je ręcznie lub użyć okna dialogowego wyboru komórki po prawej stronie okna dialogowego Zmienianie komórek.

Ochrona — możesz również chronić swoje scenariusze, więc w sekcji Ochrona sprawdź odpowiednie opcje lub wyczyść ich zaznaczenie, jeśli nie chcesz mieć żadnej ochrony.

• Wybierz pozycję Zapobiegaj zmianom , aby zapobiec edytowaniu scenariusza, gdy arkusz jest chroniony.

• Wybierz pozycję Ukryte , aby zapobiec wyświetlaniu scenariusza, gdy arkusz jest chroniony.

Teraz załóżmy, że najlepszym scenariuszem budżetowym jest przychód brutto w wysokości 150.000 dolarów i Koszty towarów sprzedanych 26.000 dolarów, pozostawiając 124.000 dolarów zysku brutto. Aby zdefiniować ten zestaw wartości jako scenariusz, należy utworzyć inny scenariusz, nadać go nazwę Najlepsza sprawa i podać różne wartości dla komórek B2 (150 000) i komórki B3 (26 000). Ponieważ zysk brutto (komórka B4) jest formułą — różnicą między przychodem (B2) a kosztami (B3) — nie zmienia się komórki B4 w scenariuszu Najlepszego przypadku.

Po zapisaniu scenariusza staje się on dostępny na liście scenariuszy, których można używać w analizach warunkowych. Jeśli na powyższej ilustracji zostaną wyświetlone wartości w scenariuszu Najlepsza wielkość liter, wartości w arkuszu zmienią się tak, aby przypominały następującą ilustrację:

Może się zdarzyć, że wszystkie informacje w jednym arkuszu lub skoroszycie będą potrzebne do utworzenia wszystkich scenariuszy, które chcesz rozważyć. Możesz jednak zechcieć zebrać informacje o scenariuszach z innych źródeł. Załóżmy na przykład, że próbujesz utworzyć budżet firmy. Scenariusze mogą być zbierane z różnych działów, takich jak Sprzedaż, Płace, Produkcja, Marketing i Prawne, ponieważ każde z tych źródeł ma inne informacje do użycia podczas tworzenia budżetu.

Te scenariusze można zebrać w jednym arkuszu za pomocą polecenia Scal . Każde źródło może dostarczyć dowolną liczbę lub dowolną liczbę zmieniających się wartości komórek. Na przykład możesz chcieć, aby każdy dział dostarczał prognozy wydatków, ale potrzebujesz tylko prognoz przychodów z kilku.

Gdy zdecydujesz się scalić, Menedżer scenariuszy załaduje kreatora scenariuszy korespondencji seryjnej, który wyświetli listę wszystkich arkuszy w aktywnym skoroszycie, a także wyświetli listę innych skoroszytów, które mogły zostać otwarte w tym czasie. Kreator poda liczbę scenariuszy dostępnych w każdym wybranym arkuszu źródłowym.

Podczas zbierania różnych scenariuszy z różnych źródeł należy użyć tej samej struktury komórek w każdym ze skoroszytów. Na przykład przychód może zawsze znajdować się w komórce B2, a wydatki zawsze mogą znajdować się w komórce B3. W przypadku użycia różnych struktur scenariuszy z różnych źródeł scalanie wyników może być trudne.

Aby porównać kilka scenariuszy, możesz utworzyć raport podsumowujący je na tej samej stronie. Raport może zawierać listę scenariuszy obok siebie lub przedstawiać je w raport w formie tabeli przestawnej.

Raport podsumowania scenariuszy oparty na dwóch poprzednich przykładowych scenariuszach wyglądałby podobnie do następującego:

Zauważysz, że program Excel automatycznie dodał poziomy grupowania , co spowoduje rozwinięcie i zwinięcie widoku po kliknięciu różnych selektorów.

Na końcu raportu podsumowującego zostanie wyświetlona uwaga z wyjaśnieniem, że kolumna Bieżące wartości reprezentuje wartości komórek zmieniających się w momencie tworzenia raportu podsumowania scenariuszy, a komórki zmienione w poszczególnych scenariuszach są wyróżniane na szaro.