Obliczanie wielu wyników za pomocą tabeli danych

W programie Microsoft Excel tabele danych są częścią zestawu poleceń nazywanych narzędziami do analizy What-If. Podczas konstruowania i analizowania tabel danych wykonuje się analizę warunkową.

Analiza warunkowa to proces zmieniania wartości w komórkach, aby zobaczyć, jak te zmiany wpłyną na wynik formuł w arkuszu. Za pomocą tabeli danych można na przykład zmieniać stopę procentową i długość okresu pożyczki w celu oceny potencjalnych miesięcznych kwot płatności.

Typy analizy warunkowej    

W programie Excel istnieją trzy typy narzędzi do analizy warunkowej: scenariusze, tabele danych i wyszukiwanie celów. Scenariusze i tabele danych używają zestawów wartości wejściowych do obliczania możliwych wyników. Szukanie wyniku jest zupełnie inne, używa pojedynczego wyniku i oblicza możliwe wartości wejściowe, które dałyby taki wynik.

Podobnie jak scenariusze, tabele danych ułatwiają eksplorowanie zestawu możliwych wyników. W przeciwieństwie do scenariuszy tabele danych pokazują wszystkie wyniki w jednej tabeli w jednym arkuszu. Korzystanie z tabel danych ułatwia szybkie sprawdzenie zakresu możliwości. Ponieważ można skupić się tylko na jednej lub dwóch zmiennych, wyniki są łatwe do odczytania i udostępnienia w postaci tabelarycznej.

Tabela danych nie obsługuje więcej niż dwóch zmiennych. Jeśli chcesz przeanalizować więcej niż dwie zmienne, zamiast tego użyj scenariuszy. Chociaż jest ograniczona tylko do jednej lub dwóch zmiennych (jednej dla komórki wprowadzania wiersza i jednej dla komórki wprowadzania 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.

Dowiedz się więcej z artykułu Wprowadzenie do analizy What-If.

Utwórz tabele danych o jednej zmiennej lub dwóch zmiennych, w zależności od liczby zmiennych i formuł, które należy przetestować.

Tabele danych z jedną zmienną    

Użyj tabeli danych z jedną zmienną, jeśli chcesz zobaczyć, jak różne wartości jednej zmiennej w jednej lub kilku formułach zmienią wyniki tych formuł. Można na przykład użyć tabeli danych z jedną zmienną, aby zobaczyć, jak różne stopy procentowe wpływają na miesięczną spłatę kredytu hipotecznego przy użyciu funkcji PMT. Wprowadzasz wartości zmiennych w jednej kolumnie lub wierszu, a wyniki są wyświetlane w sąsiedniej kolumnie lub wierszu.

Na poniższej ilustracji komórka D2 zawiera formułę płatności =PMT(B3/12;B4;-B5),która odwołuje się do komórki wejściowej B3.

Dwie zmienne tabele danych    

Użyj tabeli danych z dwiema zmiennymi, aby zobaczyć, jak różne wartości dwóch zmiennych w jednej formule zmienią wyniki tej formuły. Możesz na przykład użyć tabeli danych z dwiema zmiennymi, aby zobaczyć, w jaki sposób różne kombinacje stóp procentowych i warunków kredytu wpływają na miesięczną spłatę kredytu hipotecznego.

Na poniższej ilustracji komórka C2 zawiera formułę płatności =PMT(B3/12;B4;-B5), która korzysta z dwóch komórek wejściowych: B3 i B4.

 

Obliczenia tabeli danych    

Za każdym razem, gdy arkusz jest obliczany ponownie, wszystkie tabele danych również będą obliczane ponownie — nawet jeśli nie wprowadzono żadnych zmian w danych. Aby przyspieszyć obliczanie arkusza zawierającego tabelę danych, możesz zmienić opcje obliczania , aby automatycznie ponownie obliczyć arkusz, ale nie tabele danych. Aby dowiedzieć się więcej, zobacz sekcję Przyspieszanie obliczeń w arkuszu zawierającym tabele danych.

Tabela danych z jedną zmienną zawiera wartości wejściowe w pojedynczej kolumnie (zorientowanej na kolumnę) lub w wierszu (zorientowanym na wiersz). Każda formuła w tabeli danych z jedną zmienną musi odwoływać się tylko do jednej komórka wprowadzania.

Wykonaj następujące czynności:

1. Wpisz listę wartości, które chcesz zastąpić w komórce wprowadzania — w dół o jedną kolumnę lub w jednym wierszu. Po obu stronach wartości pozostaw kilka pustych wierszy i kolumn.

2. Wykonaj jedną z następujących czynności:

   • Jeśli tabela danych jest zorientowana na kolumnę (zmienne wartości znajdują się w kolumnie), wpisz formułę w komórce o jeden wiersz powyżej i o jedną komórkę po prawej stronie kolumny wartości. Ta tabela danych z jedną zmienną jest zorientowana na kolumnę, a formuła znajduje się w komórce D2.
     
     
     
     Jeśli chcesz sprawdzić wpływ różnych wartości na inne formuły, wprowadź dodatkowe formuły w komórkach po prawej stronie pierwszej formuły.

   • Jeśli tabela danych jest zorientowana na wiersz (zmienne wartości znajdują się w wierszu), wpisz formułę w komórce o jedną kolumnę po lewej stronie pierwszej wartości i o jedną komórkę poniżej wiersza wartości.
     
     Jeśli chcesz sprawdzić wpływ różnych wartości na inne formuły, wprowadź dodatkowe formuły w komórkach poniżej pierwszej formuły.

3. Zaznacz zakres komórek zawierający formuły i wartości, które chcesz podstawić. Na powyższej ilustracji ten zakres to C2:D5.

4. Na karcie Dane kliknij pozycję Analiza warunkowa > tabelę danych (w grupie Narzędzia danych lub w grupie Prognoza Excel 2016 ).

5. Wykonaj jedną z następujących czynności:

   • Jeśli tabela danych jest zorientowana na kolumnę, wprowadź odwołanie do komórki dla komórki wejściowej w polu komórki wprowadzania kolumny . Na powyższej ilustracji komórka wejściowa to B3.

   • Jeśli tabela danych jest zorientowana na wiersze, wprowadź odwołanie do komórki wejściowej w polu komórki wprowadzania wiersza .

     Uwaga: Po utworzeniu tabeli danych możesz zechcieć zmienić format komórek wyników. Na rysunku komórki wynikowe są sformatowane jako waluta.

Formuły używane w tabeli danych z jedną zmienną muszą odwoływać się do tej samej komórki wejściowej.

Oto odpowiednia procedura

1. Wykonaj jedną z następujących czynności:

   • Jeśli tabela danych jest zorientowana na kolumnę, wprowadź nową formułę w pustej komórce z prawej strony istniejącej formuły w górnym wierszu tabeli danych.

   • Jeśli tabela danych jest zorientowana na wiersze, wprowadź nową formułę w pustej komórce poniżej istniejącej formuły w pierwszej kolumnie tabeli danych.

2. Zaznacz zakres komórek zawierający tabelę danych i nową formułę.

3. Na karcie Dane kliknij pozycję Analiza warunkowa > tabelę danych (w grupie Narzędzia danych lub w grupie PrognozaExcel 2016 ).

4. Wykonaj jedną z następujących czynności:

   • Jeśli tabela danych jest zorientowana na kolumnę, wprowadź odwołanie do komórki wejściowej w polu Komórka wejściowa kolumny .

   • Jeśli tabela danych jest zorientowana na wiersze, wprowadź odwołanie do komórki wejściowej w polu Komórka wprowadzania wiersza .

W tabeli danych z dwiema zmiennymi jest używana formuła zawierająca dwie listy wartości wejściowych. Formuła musi odwoływać się do dwóch różnych komórek wejściowych.

Wykonaj następujące czynności:

1. W komórce arkusza wprowadź formułę odwołujące się do dwóch komórek wejściowych.

   W poniższym przykładzie— w którym wartości początkowe formuły są wprowadzane w komórkach B3, B4 i B5, należy wpisać formułę =PMT(B3/12;B4;-B5) w komórce C2.

2. Wpisz jedną listę wartości wejściowych w tej samej kolumnie poniżej formuły.

   W tym przypadku wpisz różne stopy procentowe w komórkach C3, C4 i C5.

3. Wprowadź drugą listę w tym samym wierszu co formuła — po jej prawej stronie.

   Wpisz warunki pożyczki (w miesiącach) w komórkach D2 i E2.

4. Zaznacz zakres komórek zawierający formułę (C2), wiersz i kolumnę wartości (C3:C5 i D2:E2) oraz komórki, w których chcesz obliczyć wartości (D3:E5).

   W takim przypadku zaznacz zakres C2:E5.

5. Na karcie Dane w grupie Narzędzia danych lub w grupie Prognoza (w Excel 2016 ) kliknij pozycję Analiza warunkowa > tabelę danych (w grupie Narzędzia danych lub w grupie Prognoza Excel 2016 ).

6. W polu Komórka wprowadzania wiersza wprowadź odwołanie do komórki wejściowej dla wartości wejściowych w wierszu.
   Wpisz komórkę B4 w polu Komórka wprowadzania wiersza .

7. W polu Komórka wprowadzania kolumny wprowadź odwołanie do komórki wejściowej dla wartości wejściowych w kolumnie.
   Wpisz B3 w polu Komórka wprowadzania kolumny .

8. Kliknij przycisk OK.

Przykład tabeli danych z dwiema zmiennami

Tabela danych z dwiema zmiennymi może pokazać, w jaki sposób różne kombinacje stóp procentowych i warunków kredytu wpływają na miesięczną spłatę kredytu hipotecznego. Na poniższym rysunku komórka C2 zawiera formułę płatności : =PMT(B3/12;B4;-B5), która używa dwóch komórek wejściowych: B3 i B4.

Po ustawieniu tej opcji obliczania obliczenia tabeli danych nie występują po wykonaniu ponownego obliczania w całym skoroszycie. Aby ręcznie ponownie obliczyć tabelę danych, zaznacz jej formuły, a następnie naciśnij klawisz F9.

Aby zwiększyć wydajność obliczeń, wykonaj następujące czynności:

1. Kliknij pozycję Opcje > plików > formuły.

2. W sekcji Opcje obliczania w obszarze Obliczanie kliknij pozycję Automatycznie z wyjątkiem tabel danych.

   Porada: Opcjonalnie na karcie Formuły kliknij strzałkę w obszarze Opcje obliczania, a następnie kliknij pozycję Automatycznie z wyjątkiem tabel danych (w grupie Obliczanie ).

Za pomocą kilku innych narzędzi programu Excel można przeprowadzać analizę warunkową, jeśli masz określone cele lub większe zestawy zmiennych danych.

Szukanie wyniku

Jeśli znasz oczekiwany wynik formuły, ale nie wiesz dokładnie, jakiej wartości wejściowej formuła potrzebuje, aby uzyskać ten wynik, użyj funkcji Goal-Seek. Zobacz artykuł Wyszukiwanie wyniku w celu znalezienia odpowiedniego wyniku przez dostosowanie wartości wejściowej.

Excel Solver

Za pomocą dodatku Solver programu Excel można znaleźć optymalną wartość dla zestawu zmiennych wejściowych. Dodatek Solver działa z grupą komórek (nazywanych zmiennymi decyzyjnymi lub po prostu komórkami zmiennymi), które są używane do obliczania formuł w komórkach celu i ograniczenia. Dodatek Solver dostosowuje wartości w komórkach zmiennych decyzyjnych tak, aby spełnić limity obejmujące komórki ograniczeń i uzyskać pożądany wynik w komórce celu. Dowiedz się więcej w tym artykule: Definiowanie i rozwiązywanie problemu przy użyciu dodatku Solver.