Korzystając z narzędzi do analizy warunkowej w programie Excel, możesz używać kilku różnych zestawów wartości w jednej lub kilku formułach, aby zbadać wszystkie wyniki.
Na przykład możesz przeprowadzić analizę warunkową w celu utworzenia dwóch budżetów, zakładając w każdym z nich określony poziom przychodów. Możesz też określić żądany wynik formuły, a następnie ustalić, jakie zestawy wartości pozwalają uzyskać ten wynik. Program Excel udostępnia kilka różnych narzędzi ułatwiających przeprowadzanie analiz odpowiedniego typu.
Pamiętaj, że jest to tylko omówienie tych narzędzi. Dostępne są linki do tematów Pomocy dotyczących każdego z nich.
Omówienie
Analiza warunkowa to proces zmiany wartości komórek w celu sprawdzenia, jak te zmiany wpłyną na wyniki formuł w arkuszu.
W programie Excel są dostępne trzy rodzaje narzędzi do analizy What-If: scenariusze, wyszukiwanie celów 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 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żesz użyć dodatku Analysis ToolPak.
Uwzględnianie wielu różnych zmiennych przy użyciu scenariuszy
Scenariusz to zestaw wartości, które program Excel zapisuje i może automatycznie podstawiać w komórkach 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. Zmienianie komórek
2. Komórka wynikowa
1. Zmienianie komórek
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.
Uwaga
Raporty scenariuszy nie są automatycznie obliczane ponownie. Jeśli zmienisz wartości scenariusza, te zmiany nie będą widoczne w istniejącym raporcie podsumowującym. Zamiast tego należy utworzyć nowy raport podsumowujący.
Używanie funkcji szukania wyniku w celu ustalenia, jak uzyskać odpowiedni wynik
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).
Uwaga
Funkcja szukania wyniku działa tylko z jedną zmienną wartością wejściową. Jeśli chcesz określić więcej niż jedną wartość wejściową, na przykład kwotę pożyczki i kwotę miesięcznych spłat pożyczki, użyj dodatku Solver. Aby uzyskać więcej informacji o dodatku Solver, zobacz sekcję Przygotowywanie prognoz i zaawansowanych modeli biznesowych oraz skorzystaj z linków w sekcji Zobacz też.
Sprawdzanie efektów jednej lub dwóch zmiennych w formule przy użyciu tabel danych
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ć tabeli 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.
Przygotowywanie prognoz i zaawansowanych modeli biznesowych
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.
Za pomocą uchwytu wypełniania lub polecenia Serie można wypełnić serię wartości, które pasują do prostego trendu liniowego lub wykładniczego trendu wzrostu. Aby rozszerzyć złożone i nieliniowe dane, możesz użyć funkcji arkusza lub narzędzia do analizy regresji w dodatku Analysis ToolPak.
Funkcja szukania wyniku obsługuje tylko jedną zmienną, ale przy użyciu dodatku Solver można wyświetlać więcej zmiennych do tyłu. 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.
Potrzebujesz dodatkowej 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ż
Używanie dodatku Solver do budżetowania kapitałowego
Używanie dodatku Solver do określania optymalnej mieszanki produktów
Definiowanie i rozwiązywanie problemów za pomocą dodatku Solver
Omówienie formuł w programie Excel
Jak unikać niepoprawnych formuł
Skróty klawiaturowe w programie Excel