Scenariusz to zestaw wartości, które program Excel zapisuje i może automatycznie podstawić w arkuszu. Można tworzyć i zapisywać różne grupy wartości jako scenariusze, a następnie przełączać się między tymi scenariuszami, aby wyświetlać różne wyniki.
Jeśli określone informacje mają zostać użyte w scenariuszach dla kilku osób, możesz zebrać te informacje w oddzielnych skoroszytach, a następnie scalić scenariusze z różnych skoroszytów w jeden.
Po przygotowaniu wszystkich scenariuszy można utworzyć raport podsumowujący scenariusze, zawierający informacje ze wszystkich scenariuszy.
Scenariuszami zarządza się za pomocą Menedżera scenariuszy z poziomu widoku warunkowego w grupie Prognoza na karcie Dane .
Rodzaje analizy What-If
Program Excel zawiera trzy rodzaje narzędzi do analizy What-If: Menedżer scenariuszy, Tabela danych i Szukanie wyniku. Scenariusze i tabela danych przyjmują zestawy wartości wejściowych i projektują do przodu w celu określenia możliwych wyników. Szukanie wyniku różni się od scenariuszy i tabeli danych tym, że przyjmuje wynik i rzutuje wstecz w celu określenia możliwych wartości wejściowych, które dają ten wynik.
Każdy scenariusz obsługuje maksymalnie 32 wartości zmiennych. Jeśli chcesz przeanalizować więcej niż 32 wartości, które reprezentują tylko jedną lub dwie zmienne, możesz użyć tabel danych. Chociaż tabela danych jest ograniczona tylko do jednej lub dwóch zmiennych (jednej dla komórki wprowadzania wiersza i jednej dla komórki wprowadzania kolumny), może zawierać dowolną liczbę różnych wartości zmiennych. Scenariusz może mieć maksymalnie 32 różne wartości, ale możesz utworzyć dowolną liczbę scenariuszy.
Oprócz tych trzech narzędzi można zainstalować 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.
Tworzenie scenariuszy
Załóżmy, że chcesz utworzyć budżet, ale nie masz pewności co do swoich przychodów. Za pomocą scenariuszy można definiować różne możliwe wartości przychodu, a następnie przełączać się między scenariuszami w celu przeprowadzenia analizy warunkowej.
Załóżmy na przykład, że najgorszy scenariusz budżetu to przychód brutto w wysokości 50 000 zł i koszty sprzedanych towarów w wysokości 13 200 zł, co daje zysk brutto w wysokości 36 800 zł. Aby zdefiniować ten zestaw wartości jako scenariusz, najpierw wprowadź wartości w arkuszu, jak pokazano na poniższej ilustracji:
Komórki Zmienianie zawierają wpisane wartości, natomiast komórka Wynik zawiera formułę opartą na komórkach Zmiana (na tej ilustracji komórka B4 zawiera formułę =B2-B3).
Następnie w oknie dialogowym Menedżer scenariuszy można zapisać te wartości jako scenariusz. Przejdź do karty Dane , wybierz pozycję Analiza warunkowa, wybierz pozycję Menedżer scenariuszy, a następnie wybierz pozycję Dodaj.
W oknie dialogowym Nazwa scenariusza nadaj scenariuszowi nazwę Najgorszy przypadek i określ, że komórki B2 i B3 są wartościami, które zmieniają się między scenariuszami. Jeśli przed dodaniem scenariusza zaznaczysz komórki Zmienianie w arkuszu, Menedżer scenariuszy automatycznie wstawi komórki. W przeciwnym razie możesz wpisać je ręcznie lub użyć okna dialogowego zaznaczania komórek po prawej stronie okna dialogowego Zmienianie komórek.
Uwaga
Mimo że ten przykład zawiera tylko dwie komórki z zamianą (B2 i B3), scenariusz może zawierać maksymalnie 32 komórki.
Ochrona — możesz również chronić swoje scenariusze. W sekcji Ochrona zaznacz odpowiednie opcje lub usuń ich zaznaczenie, jeśli nie chcesz żadnej ochrony.
- Wybierz pozycję Zapobiegaj zmianom , aby uniemożliwić edytowanie scenariusza, gdy arkusz jest chroniony.
- Wybierz pozycję Ukryty , aby zapobiec wyświetlaniu scenariusza, gdy arkusz jest chroniony.
Uwaga
Te opcje mają zastosowanie tylko do arkuszy chronionych. Aby uzyskać więcej informacji o chronionych arkuszach, zobacz Chronienie arkusza.
Teraz załóżmy, że najlepszym scenariuszem budżetu jest przychód brutto w wysokości 150 000 zł i koszty sprzedanych towarów w wysokości 26 000 zł, co daje 124 000 zł zysku brutto. Aby zdefiniować taki zestaw wartości jako scenariusz, należy utworzyć inny scenariusz, nadać mu nazwę Najlepszy przypadek i podać inne wartości dla komórek B2 (150 000) i B3 (26 000). Ponieważ zysk brutto (komórka B4) jest formułą — różnicą między przychodami (B2) i kosztami (B3), nie należy zmieniać komórki B4 w przypadku scenariusza najlepszego przypadku.
Po zapisaniu scenariusza staje się on dostępny na liście scenariuszy, których można użyć w analizach warunkowych. Biorąc pod uwagę wartości na poprzedniej ilustracji, jeśli zdecydujesz się wyświetlić scenariusz Najlepszy przypadek, wartości w arkuszu zmienią się tak, aby przypominały poniższą ilustrację:
Scalanie scenariuszy
Wszystkie informacje potrzebne do utworzenia scenariuszy można wziąć pod uwagę w jednym arkuszu lub skoroszycie. Możesz jednak zebrać informacje scenariusza z innych źródeł. Załóżmy na przykład, że próbujesz utworzyć budżet firmy. Możesz zebrać scenariusze z różnych działów, takich jak Sprzedaż, Płace, Produkcja, Marketing i Prawne, ponieważ każde z tych źródeł zawiera inne informacje do wykorzystania podczas tworzenia budżetu.
Takie scenariusze można zebrać w jednym arkuszu przy użyciu polecenia Scal . Każde źródło może dostarczyć dowolną liczbę zmienianych wartości komórek. Na przykład możesz chcieć, aby poszczególne działy dostarczały prognozy wydatków, ale potrzebujesz prognoz przychodów tylko z kilku działów.
Po wybraniu scalenia Menedżer scenariuszy ładuje okno dialogowe Scenariusze scalania zawierające listę wszystkich arkuszy w aktywnym skoroszycie oraz wszystkie inne skoroszyty, które są w tym czasie otwarte. Kreator poda liczbę scenariuszy w każdym wybranym arkuszu źródłowym.
Podczas zbierania różnych scenariuszy z różnych źródeł należy używać tej samej struktury komórek w każdym ze skoroszytów. Na przykład pozycję Dochód, zawsze umieść w komórce B2, a wartość Wydatki zawsze w komórce B3. Jeśli używasz różnych struktur scenariuszy z różnych źródeł, scalanie wyników może być trudne.
Porada
Rozważ samodzielne utworzenie scenariusza, a następnie wysłanie współpracownikom kopii skoroszytu zawierającego ten scenariusz. Takie podejście ułatwia zapewnienie, że struktura wszystkich scenariuszy jest taka sama.
Raporty podsumowania scenariuszy
Aby porównać kilka scenariuszy, utwórz raport, który podsumowuje je na tej samej stronie. Raport może wyświetlać scenariusze obok siebie lub przedstawiać je w formie raportu w formie tabeli przestawnej.
Raport podsumowania scenariuszy oparty na poprzednich dwóch przykładowych scenariuszach może wyglądać następująco:
Program Excel automatycznie dodaje poziomy grupowania, które umożliwiają rozwijanie i zwijanie widoku podczas wybierania różnych opcji.
Na końcu raportu podsumowującego jest wyświetlana uwaga z wyjaśnieniem, że kolumna Bieżące wartości odzwierciedla wartości zmienianych komórek podczas tworzenia raportu podsumowania scenariuszy. Komórki, które uległy zmianie w poszczególnych scenariuszach, zostały wyróżnione na szaro.
Uwaga
- Domyślnie w raporcie podsumowującym są używane odwołania do komórek w celu identyfikowania zmienianych komórek i komórek wynikowych. Jeśli przed uruchomieniem raportu podsumowującego utworzysz nazwane zakresy komórek, raport będzie zawierał te nazwy zamiast odwołań do komórek.
- Raporty scenariuszy nie są obliczane automatycznie. Jeśli zmienisz wartości scenariusza, te zmiany nie będą widoczne w istniejącym raporcie podsumowującym. Pojawią się one po utworzeniu nowego raportu podsumowującego.
- Komórki wynikowe nie są potrzebne do wygenerowania raportu podsumowania scenariusza, ale są potrzebne do raportu w formie tabeli przestawnej scenariusza.
Potrzebujesz dalszej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społecznościach.
Zobacz również
Obliczanie wielu wyników za pomocą tabeli danych
Wprowadzenie do analizy What-If
Definiowanie i rozwiązywanie problemów za pomocą dodatku Solver
Omówienie formuł w programie Excel
Jak unikać niepoprawnych formuł w programie Excel
Wykrywanie błędów formuł w programie Excel
Skróty klawiaturowe w programie Excel