Wprowadzenie do analizy warunkowej

Analiza warunkowa to proces zmiany wartości komórek w celu sprawdzenia, jak te zmiany wpłyną na wyniki formuł w arkuszu.

Program Excel udostępnia trzy rodzaje narzędzi do analizy warunkowej: scenariusze, szukanie wyniku i tabele danych. Scenariusze i tabele danych przyjmują zestaw wartości wejściowych i określają możliwe wyniki. Tabela danych działa tylko z jedną lub dwiema zmiennymi, ale akceptuje wiele różnych wartości tych zmiennych. Scenariusz może mieć wiele zmiennych, ale obsługuje maksymalnie 32 wartości. Szukanie wyniku działa inaczej niż scenariusze i tabele danych — przyjmuje wynik i ustala możliwe wartości wejściowe, które dają taki wynik.

Oprócz tych trzech narzędzi można zainstalować dodatki ułatwiające analizę warunkową, 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.

Scenariusz to zestaw wartości, które są zapisywane przez program Excel i mogą być automatycznie podstawiane do komórek arkusza. Można utworzyć i zapisać różne grupy wartości w arkuszu, a następnie przełączać się do dowolnego z tych nowych scenariuszy, aby wyświetlać różne wyniki.

Załóżmy na przykład, że istnieją dwa scenariusze budżetu: najgorszy i najlepszy przypadek. Za pomocą Menedżera scenariuszy można utworzyć oba scenariusze w tym samym arkuszu, a następnie przełączać się między nimi. Dla każdego scenariusza określa się zmieniane komórki oraz wartości używane w danym scenariuszu. Podczas przełączania się między scenariuszami komórka wyniku zmienia się w celu odzwierciedlenia różnych wartości zmienianych komórek.

1. Zmieniane komórki

2. Komórka wynikowa

1. Zmieniane komórki

2. Komórka wynikowa

Jeśli kilka osób ma określone informacje w osobnych skoroszytach, których chcesz użyć w scenariuszach, możesz zebrać te skoroszyty i scalić scenariusze.

Po utworzeniu lub zgromadzeniu wszystkich potrzebnych scenariuszy możesz utworzyć raport podsumowania scenariuszy, obejmujący informacje z tych scenariuszy. W raporcie scenariuszy są wyświetlane wszystkie informacje scenariuszy w jednej tabeli w nowym arkuszu.

Jeśli znasz żądany wynik formuły, ale nie masz pewności, jakiej wartości wejściowej formuła wymaga do uzyskania tego wyniku, możesz użyć funkcji Szukanie wyniku . Załóżmy na przykład, że chcesz zaciągnąć pożyczkę. Wiesz, ile pieniędzy potrzebujesz, jak długo ma trwać spłacanie pożyczki oraz jaką kwotę możesz spłacać każdego miesiąca. Funkcja szukania wyniku pozwala określić stopę procentową, jaką należy przyjąć, aby spełnić założenia pożyczki.

Komórki B1, B2 i B3 to wartości kwoty pożyczki, długości okresu i stopy procentowej.

Komórka B4 wyświetla wynik formuły =PMT(B3/12;B2;B1).

Jeśli masz formułę używającą jednej lub dwóch zmiennych albo wiele formuł używających jednej wspólnej zmiennej, możesz użyć funkcji Tabela danych, aby wyświetlić wszystkie wyniki w jednym miejscu. Tabele danych pozwalają łatwo sprawdzać zakres możliwości jednym rzutem oka. Ponieważ można skupić się tylko na jednej lub dwóch zmiennych, wyniki są łatwe do odczytania i udostępnienia w postaci tabelarycznej. Jeśli skoroszyt ma włączony tryb automatycznego ponownego obliczania, dane w tabelach danych są natychmiast obliczane ponownie, co zapewnia, że dane są zawsze aktualne.

Komórka B3 zawiera wartość wejściową. 
Komórki C3, C4 i C5 to wartości podstawiane przez program Excel na podstawie wartości wprowadzonej w komórce B3.

Tabela danych nie obsługuje więcej niż dwóch zmiennych. Aby przeanalizować więcej niż dwie zmienne, można użyć scenariuszy. Chociaż tabela danych jest ograniczona tylko do jednej lub dwóch zmiennych, pozwala używać dowolnej liczby różnych wartości zmiennych. Scenariusz może mieć maksymalnie 32 różne wartości, ale można utworzyć dowolną liczbę scenariuszy.

Aby przygotować prognozy, można w programie Excel automatycznie wygenerować przyszłe wartości oparte na istniejących danych albo automatycznie wygenerować ekstrapolowane wartości oparte na obliczeniach trendu liniowego lub trendu wzrostu.

Korzystając z uchwytu wypełniania lub polecenia Serie, można wypełnić zakres serią wartości przebiegających zgodnie z prostym trendem liniowym lub wykładniczym trendem wzrostu. Aby poszerzyć złożone i nieliniowe dane, można użyć funkcji arkusza lub narzędzia do analizy metodą regresji dostępnego w dodatku Analysis ToolPak.

Funkcja szukania wyniku obsługuje tylko jedną zmienną, ale za pomocą dodatku Solver można ustalać więcej zmiennych wejściowych. Korzystając z dodatku Solver, można znajdować optymalną wartość dla formuły w jednej komórce — nazywanej komórką docelową — w arkuszu.

Dodatek Solver działa z grupą komórek powiązanych z formułą w komórce docelowej. Dodatek Solver dostosowuje wartości w określonych przez Ciebie komórkach zmieniających się — nazywanych komórkami dostosowywanymi — w celu uzyskania określonego wyniku z formuły komórki docelowej. Ograniczenia można stosować w celu ograniczenia wartości, których dodatek Solver może używać w modelu, a ograniczenia mogą odwoływać się do innych komórek wpływających na formułę komórki docelowej.