Analiza warunkowa w Excelu: Cel wyniku, Menedżer scenariuszy i Tabele danych

Podajesz mu trzy rzeczy:

• Ustaw komórkę: komórka z twoją formułą (wynik)
• Na wartość: docelowy wynik, który chcesz uzyskać
• Zmieniana komórka: komórka wejściowa, którą Excel może korygować

Zobaczmy to w praktyce. 

Prowadzisz mały biznes sprzedający notesy. Twoje obecne przychody to $25,000, ale celem jest $50,000. Cena jest stała — $25, więc musisz ustalić, ile sztuk trzeba sprzedać. 

Jak to zrobić:

1. Przejdź do Dane > Analiza warunkowa > Cel wyniku

2. W polu Ustaw komórkę kliknij B4 (komórka z formułą przychodu)

3. W polu Na wartość wpisz 50000

4. W polu Zmieniana komórka wybierz B2 (sprzedane sztuki)

5. Kliknij OK

Cel wyniku zmienia B2 z 1,000 na 2,000 sztuk — dokładnie tyle, ile potrzeba, by przy cenie $25 za sztukę osiągnąć $50,000 przychodu.

Używanie Celu wyniku do osiągnięcia konkretnego wyniku. Obraz: autor.

Jak używać Menedżera scenariuszy w Excelu

Menedżer scenariuszy to narzędzie Analizy warunkowej, które pozwala zapisywać i przełączać się między wieloma zestawami wartości wejściowych w tym samym arkuszu. Każda zapisana wersja to scenariusz. 

Możesz utworzyć ich dowolnie wiele i przełączać się między nimi jednym kliknięciem, aby porównywać wyniki. 

Przydaje się, gdy chcesz zaplanować wariant optymistyczny, pesymistyczny i bazowy, bez utrzymywania trzech osobnych plików. 

Podajesz mu trzy rzeczy:

• Nazwa scenariusza: etykieta dla każdego zestawu założeń
• Zmieniane komórki: komórki wejściowe różniące się między scenariuszami
• Wartości: konkretne liczby dla danego scenariusza

Utwórz scenariusz

Załóżmy, że planujesz kwartalną sprzedaż w biznesie z notesami.

Przyjmijmy, że bieżący zysk to $15,000. Chcesz zamodelować trzy scenariusze: najlepszy, oczekiwany i najgorszy. 

Jak utworzyć scenariusz:

1. Przejdź do Dane > Analiza-warunkowa > Menedżer scenariuszy

2. W oknie Menedżer scenariuszy kliknij Dodaj

3. W polu Nazwa scenariusza wpisz Best Case.

4. W polu Zmieniane komórki wybierz B2:B4.

5. Pojawi się okno dialogowe Wartości scenariusza. Wprowadź w odpowiednich polach następujące wartości:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Kliknij OK. 

Powtórz te kroki dla pozostałych dwóch scenariuszy, używając wartości poniżej:

Utwórz scenariusz z trzema przypadkami. Obraz: autor.

Aby przełączać scenariusze, wybierz jeden z listy i kliknij Pokaż. Excel natychmiast podmieni wartości, a twój zysk się zaktualizuje.

Kliknij Pokaż, aby zobaczyć wartości każdego scenariusza. Obraz: autor.

Edytuj scenariusze

Wybierz dowolny scenariusz z listy i kliknij Edytuj. Możesz zmienić jego nazwę, zakres komórek lub wartości. W arkuszu nic się nie zmieni, dopóki nie klikniesz Pokaż

Generuj raporty scenariuszy

Menedżer scenariuszy potrafi też utworzyć raport porównujący wszystkie zapisane scenariusze obok siebie. 

Aby wygenerować raport scenariuszy:

1. W Menedżerze scenariuszy kliknij Podsumowanie

2. W oknie Podsumowanie scenariusza wybierz Podsumowanie scenariusza

3. W polu Komórki wyników wybierz B7 (twoja komórka zysku)

4. Kliknij OK

Excel generuje tabelę podsumowującą na nowym arkuszu, pokazującą wszystkie trzy scenariusze obok siebie

Wygeneruj raport podsumowania scenariuszy. Obraz: autor.

Jak używać Tabel danych w Excelu

Tabele danych automatyzują powtarzalne obliczenia dla zakresu wartości wejściowych. Zamiast ręcznie zmieniać dane wejściowe, zapisywać wynik i powtarzać, definiujesz zakres raz, a Excel uzupełnia wszystkie wyniki jednocześnie.

Podajesz trzy rzeczy:

• Zakres wejść: wartości, które chcesz przetestować
• Komórka wejściowa w wierszu/kolumnie: komórka w twojej formule, którą te wartości zastąpią
• Komórka z formułą: wynik, wokół którego budujesz tabelę

Jednowymiarowe Tabele danych

Jednowymiarowa Tabela danych sprawdza, jak zmiana jednego wejścia wpływa na wynik formuły.

Na przykład masz formułę raty kredytu i chcesz zobaczyć, jak miesięczna rata zmienia się przy oprocentowaniu od 5% do 10%.

Aby utworzyć Jednowymiarową Tabelę danych:

1. Przygotuj zestaw danych. Uwaga: w moim zestawie, w komórce E1, bezpośrednio nad pierwszą stopą, nie obok nagłówka kolumny, wpisuję =B5. Jeśli E1 zostanie puste albo referencja formuły trafi w inne miejsce, tabela danych się wykona, ale zwróci same zera zamiast wartości rat.

Utwórz Jednowymiarową Tabelę danych. Obraz: autor.

2. Następnie zaznacz pełny zakres D1:E9 (wraz z E1)

3. Przejdź do Dane > Analiza warunkowa > Tabela danych

4. Pole Wejście w wierszu pozostaw puste

5. W polu Wejście w kolumnie wybierz B4 (oprocentowanie)

6. Kliknij OK.

Excel wypełnia tabelę obliczonymi wynikami dla każdej wartości wejściowej. Zauważ, że rata nie rośnie równomiernie. Skok z 3% do 4% to $49, ale z 9% do 10% to już $61. Zmiany stóp procentowych uderzają mocniej, im wyżej idą.

Obliczone wyniki dla każdej wartości wejściowej. Obraz: autor.

Dwuwymiarowe Tabele danych

Dwuwymiarowa tabela danych sprawdza, jak jednoczesna zmiana dwóch wejść wpływa na wynik formuły.

Na przykład model cenowy może zależeć zarówno od ceny produktu, jak i liczby sprzedanych sztuk. Dwuwymiarowa tabela danych pokaże zysk dla różnych cen i wolumenów sprzedaży w jednym widoku.

Najpierw przygotuj zestaw danych. 

Spójrz na zestaw, którego używam:

• W komórce D1 wpisz =B5. To referencja formuły i musi znaleźć się w lewym górnym rogu siatki, nie obok etykiety

• W wierszu 1, zaczynając od E1, wypisz wartości sprzedanych sztuk, które chcesz przetestować: 500, 1000, 1500, 2000

• W kolumnie D, zaczynając od D2, wypisz testowane ceny: $20, $25, $30, $35

Przygotuj zestaw danych do Dwuwymiarowej Tabeli danych. Obraz: autor.

Aby utworzyć Dwuwymiarową Tabelę danych:

1. Zaznacz pełny zakres D1:H5.

2. Przejdź do Dane > Analiza warunkowa > Tabela danych

3. W polu Wejście w wierszu wybierz B3 (sprzedane sztuki)

4. W polu Wejście w kolumnie wybierz B2 (cena za sztukę)

5. Kliknij OK.

Excel wypełnia zysk dla każdej kombinacji ceny i sprzedanych sztuk

Obliczone wyniki dla wartości wejściowych. Obraz: autor.

Dwuwymiarowe Tabele danych są szczególnie pomocne, gdy jedno założenie nie daje pełnego obrazu. Cena może wyglądać dobrze sama w sobie, ale zysk zależy też od tego, ile sztuk faktycznie kupią klienci. Ta druga zmienna sprawia, że model staje się użyteczniejszy.

Cel wyniku vs. Menedżer scenariuszy vs. Tabele danych

Cel wyniku, Menedżer scenariuszy i Tabele danych to elementy zestawu Analizy warunkowej w Excelu, ale rozwiązują różne problemy. 

Oto szybkie porównanie między nimi 

Analiza warunkowa vs. Solver

Analiza warunkowa i Solver pomagają badać różne wyniki w Excelu, ale rozwiązują różne typy problemów.

• Analiza warunkowa testuje założenia. Zmieniasz jedno lub więcej wejść i widzisz, jak wpływają na wynik. Celem jest zrozumienie możliwych rezultatów, a nie znalezienie najlepszego.
• Solver działa inaczej. Podajesz cel, np. maksymalizację zysku lub minimalizację kosztu, a on szuka kombinacji wejść, która najlepiej go spełnia. Nie testuje jednego założenia — rozwiązuje je optymalnie. 

Na przykład wnioskujący o kredyt może użyć Celu wyniku, by ustalić kwotę kredytu dającą ratę $500 miesięcznie. Firma produkcyjna może użyć Solver, by ustalić miks produkcji generujący najwyższy zysk przy zachowaniu ograniczeń pracy i materiałów. 

Uwaga: Solver nie jest częścią standardowych narzędzi Analizy warunkowej. To osobny dodatek. W większości wersji Excela trzeba go ręcznie włączyć w Opcjach programu Excel, żeby pojawił się na karcie Dane.

Najlepsze praktyki Analizy warunkowej

Oto kilka dobrych praktyk podczas pracy z Analizą warunkową: 

1. Oddzielaj założenia od formuł

Przechowuj wartości wejściowe w dedykowanych komórkach i odwołuj się do nich w formułach. Unikaj wpisywania liczb na sztywno bezpośrednio w obliczenia.

Na przykład ta formuła jest trudna do aktualizacji:

=(50*1000)-10000

A ta wersja jest znacznie łatwiejsza w utrzymaniu:

=(B2*B3)-B4

Gdy założenia się zmieniają, aktualizujesz tylko komórki wejściowe zamiast edytować formuły w całym skoroszycie.

2. Jasno opisuj scenariusze

Nazwy scenariuszy powinny od razu mówić, co reprezentują. „Scenariusz 1”, „Scenariusz 2” i „Scenariusz 3” za pół roku nic ci nie powiedzą. Nazwij je zgodnie z treścią, np. „Best Case: 15% wzrostu”. 

3. Waliduj dane wejściowe

Analiza warunkowa jest tak wiarygodna, jak testowane dane wejściowe. Zanim ją uruchomisz, sprawdź, czy:

• Procenty są wpisane poprawnie
• Daty mają spójny format
• Jednostki miary są konsekwentne
• Wartości wejściowe mieszczą się w realistycznych zakresach

Prognoza przychodów oparta na 500% wzroście sprzedaży może być technicznie poprawna, ale bezużyteczna decyzyjnie.

4. Dokumentuj założenia

Model, który dziś ma sens, za trzy miesiące niekoniecznie będzie zrozumiały dla ciebie lub kogoś innego. 

Krótka notatka przy każdym założeniu, choćby „na podstawie danych za Q3”, oszczędza później wielu domysłów. Dokumentowanie pomaga innym zrozumieć pochodzenie liczb i ułatwia przyszłe aktualizacje.

Typowe problemy z Analizą warunkową

Większość problemów z Analizą warunkową wynika z ustawienia arkusza, a nie z samych narzędzi. Jeśli Cel wyniku, Menedżer scenariuszy lub Tabele danych dają nieoczekiwane rezultaty, sprawdź kilka typowych obszarów — zwykle to rozwiązuje problem.

1. Formuły się nie aktualizują 

Zmieniasz wejście i nic się nie dzieje. Zwykle oznacza to, że tryb obliczeń Excela został po drodze przestawiony na ręczny. 

Aby to naprawić: przejdź do Formuły > Opcje obliczeń i upewnij się, że ustawiono Automatyczne. Jeśli już jest, naciśnij F9, aby wymusić przeliczenie.

2. Nieprawidłowe odwołania do komórek

Narzędzia Analizy warunkowej zależą od poprawnych odwołań do komórek. Jeśli wybrano złe komórki, wyniki będą błędne, mimo że Excel poprawnie zakończy obliczenia. 

Na przykład Cel wyniku może zmieniać niewłaściwą komórkę wejściową. Tabela danych może odwoływać się do niewłaściwego wejścia w wierszu lub kolumnie. Menedżer scenariuszy może zapisać wartości dla nie tych komórek.

Aby to naprawić: Przejrzyj komórki wejściowe przed uruchomieniem analizy, dokładnie sprawdź zaznaczenia w oknach dialogowych narzędzi i używaj opisowych etykiet, by było jasne, co reprezentuje każda komórka. 

3. Niezrozumienie ograniczeń Celu wyniku

Wielu użytkowników oczekuje, że Cel wyniku rozwiąże problemy z wieloma zmiennymi. Nie potrafi. Cel wyniku zmienia tylko jedną komórkę wejściową naraz, by osiągnąć cel. 

Na przykład może ustalić wolumen sprzedaży potrzebny do wygenerowania zysku $15,000. Nie jest w stanie jednocześnie korygować wolumenu, ceny i kosztów, by osiągnąć ten cel.

Aby to naprawić: Używaj Celu wyniku, gdy zmienia się tylko jedna zmienna. Używaj Menedżera scenariuszy lub Solver, gdy zmiennych jest więcej. 

4. Zbyt złożone modele scenariuszy

Menedżer scenariuszy technicznie pozwala na maks. 32 zmieniane komórki na scenariusz, ale to, że można, nie znaczy, że warto. 

Powyżej czterech–pięciu zmiennych scenariusze trudno porównać, a jeszcze trudniej wytłumaczyć komuś innemu. Jeśli model potrzebuje tylu ruchomych elementów, to prawdopodobnie czas go przebudować, zamiast wszystko upychać w jednym scenariuszu.

Na koniec 

Każdy arkusz ma gdzieś w sobie założenia. To może być stopa wzrostu, cena albo oprocentowanie.

Analiza warunkowa nie usuwa tych założeń. Pokazuje, co się stanie, jeśli okażą się błędne — a na tym w dużej mierze polega dobre podejmowanie decyzji.

Jeśli dopiero zaczynasz, nie startuj od modelu z pięcioma zmiennymi i trzema zagnieżdżonymi scenariuszami. Najpierw zbuduj coś małego: jedno wejście, jeden wynik, jeden Cel wyniku i poznaj, jak wejście i rezultat poruszają się razem. Złożone rzeczy przyjdą później, gdy podstawa będzie jasna.