Obliczanie wielu wyników za pomocą tabeli danych

W programie Microsoft Excel tabele danych są częścią zestawu poleceń znanych jako narzędzia analizy warunkowej. Tworząc i analizując tabele danych, przeprowadzasz analizę warunkową.

Analiza warunkowa to proces zmiany wartości komórek w celu sprawdzenia, jak te zmiany wpłyną na wyniki formuł w arkuszu. Możesz na przykład użyć tabeli danych, aby zmieniać oprocentowanie i okres spłaty kredytu — w celu oszacowania potencjalnych kwot miesięcznych rat.

Typy analizy warunkowej    

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

Podobnie jak scenariusze, tabele danych pomagają zbadać zestaw 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 analizowanie zakresu 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.

Tabela danych nie może uwzględniać więcej niż dwóch zmiennych. Jeśli chcesz analizować więcej niż dwie zmienne, zamiast tego należy użyć scenariuszy. Chociaż jest ona ograniczona tylko do jednej lub dwóch zmiennych (jedna dla komórki wprowadzania wiersza i jedna dla komórki wprowadzania kolumny), tabela danych 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.

Dowiedz się więcej z artykułu Wprowadzenie do analizy warunkowej.

Twórz tabele danych z jedną lub dwiema zmiennymi, w zależności od liczby zmiennych i formuł, które chcesz 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żesz na przykład użyć tabeli danych z jedną zmienną, aby zobaczyć, jak różne stopy procentowe wpływają na miesięczną ratę kredytu hipotecznego, używając funkcji PMT. Wartości zmiennych wprowadza się 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 wprowadzania B3.

Tabele danych z dwiema zmiennymi    

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ć, jak różne kombinacje stóp procentowych i okresów kredytowania wpłyną na miesięczną ratę kredytu hipotecznego.

Na poniższej ilustracji komórka C2 zawiera formułę płatności =PMT(B3/12,B4,-B5), która używa dwóch komórek wprowadzania, B3 i B4.

 

Obliczenia tabeli danych    

Za każdym razem, gdy arkusz jest ponownie obliczany, wszelkie tabele danych również zostaną ponownie obliczone — nawet jeśli nie było żadnych zmian w danych. Aby przyspieszyć obliczanie arkusza zawierającego tabelę danych, możesz zmienić opcje Obliczania, aby automatycznie ponownie obliczać 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 albo w jednej kolumnie (zorientowana kolumnowo), albo w jednym wierszu (zorientowana wierszowo). 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 podstawić w komórce wprowadzania — w dół jednej kolumny lub w poprzek jednego wiersza. Zostaw kilka pustych wierszy i kolumn po obu stronach wartości.

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

   • Jeśli tabela danych jest zorientowana kolumnowo (wartości zmiennych znajdują się w kolumnie), wpisz formułę w komórce jeden wiersz powyżej i jedną komórkę na prawo od kolumny wartości. Ta tabela danych z jedną zmienną jest zorientowana kolumnowo, 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 wierszowo (wartości zmiennych znajdują się w wierszu), wpisz formułę w komórce znajdującej się o jedną kolumnę na lewo od 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 wybierz pozycję Analiza warunkowa > Tabela danych (w grupie Narzędzia danych lub Prognoza z Excel 2016).

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

   • Jeśli tabela danych jest zorientowana kolumnowo, wprowadź odwołanie do komórki do komórki wprowadzania w polu Komórka wprowadzania kolumny. Na powyższej ilustracji komórka wprowadzania to B3.

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

     Uwaga: Po utworzeniu tabeli danych możesz chcieć zmienić format komórek wynikowych. Na ilustracji 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 wprowadzania.

Oto odpowiednia procedura

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

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

   • Jeśli tabela danych jest zorientowana wierszowo, 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 wybierz pozycję Analiza warunkowa > Tabela danych (w grupie Narzędzia danych lub Prognoza z Excel 2016).

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

   • Jeśli tabela danych jest zorientowana kolumnowo, wprowadź odwołanie do komórki wprowadzania w polu Komórka wprowadzania kolumny.

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

Tabela danych z dwiema zmiennymi używa formuły zawierającej dwie listy wartości wejściowych. Formuła musi odwoływać się do dwóch różnych komórek wprowadzania.

Wykonaj następujące czynności:

1. W komórce arkusza wprowadź formułę, która odwołuje się do dwóch komórek wprowadzania.

   W poniższym przykładzie — w którym początkowe wartości formuły są wprowadzane w komórkach B3, B4 i B5, wpisz 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 okresy kredytowania (w miesiącach) w komórkach D2 i E2.

4. Zaznacz zakres komórek zawierający formułę (C2), zarówno wiersz, jak i kolumnę wartości (C3:C5 i D2:E2), oraz komórki, w których mają się znaleźć obliczone wartości (D3:E5).

   W tym przypadku zaznacz zakres C2:E5.

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

6. W polu Komórka wprowadzania wiersza wprowadź odwołanie do komórki wprowadzania 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 wprowadzania dla wartości wejściowych w kolumnie.
   Wpisz B3 w polu Komórka wprowadzania kolumny.

8. Wybierz przycisk OK.

Przykład tabeli danych z dwiema zmiennymi

Tabela danych z dwiema zmiennymi może pokazać, jak różne kombinacje stóp procentowych i okresów kredytowania wpłyną na miesięczną ratę kredytu hipotecznego. Na przedstawionej tu ilustracji komórka C2 zawiera formułę płatności, =PMT(B3/12,B4,-B5), która używa dwóch komórek wprowadzania, B3 i B4.

Po ustawieniu tej opcji obliczania, obliczenia tabeli danych nie są wykonywane, gdy ponowne obliczanie jest przeprowadzane dla całego skoroszytu. Aby ręcznie ponownie obliczyć tabelę danych, zaznacz jej formuły, a następnie naciśnij klawisz F9.

Wykonaj poniższe kroki, aby poprawić wydajność obliczeń:

1. Wybierz kolejno pozycje Plik > Opcje > Formuły.

2. W sekcji Opcje obliczania wybierz pozycję Automatycznie.

   Porada: Opcjonalnie na karcie Formuły wybierz strzałkę w sekcji Opcje obliczania, a następnie wybierz pozycję Automatycznie.

Możesz użyć kilku innych narzędzi programu Excel do przeprowadzenia analizy warunkowej, jeśli masz określone cele lub większe zbiory danych zmiennych.

Szukanie wyniku

Jeśli znasz oczekiwany wynik formuły, ale nie wiesz dokładnie, jakiej wartości wejściowej potrzebuje formuła, aby uzyskać ten wynik, użyj funkcji szukania wyniku. Zobacz artykuł Używanie funkcji szukania wyniku, aby znaleźć żądany wynik, dostosowując wartość wejściową.

Solver programu Excel

Możesz użyć dodatku Solver programu Excel, aby znaleźć optymalną wartość dla zestawu zmiennych wejściowych. Solver pracuje 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 ograniczeń. 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 za pomocą dodatku Solver.