Obliczanie wielu wyników za pomocą tabeli danych

Omówienie

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, używa pojedynczego wyniku i oblicza możliwe wartości wejściowe, które dałyby taki 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.

Podstawowe informacje o tabeli danych

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.

Tworzenie tabeli danych z jedną zmienną

Tabela danych z jedną zmienną zawiera wartości wejściowe w jednej 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órki wejściowej.

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ę Tabela danych analizy > warunkowej (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 wejściowej w polu komórki wejściowej Kolumna . 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.

Dodawanie formuły do tabeli danych z jedną zmienną

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ęTabela danychanalizy> warunkowej (w grupie Narzędzia danych lub w grupie Prognoza 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.

Tworzenie tabeli danych z dwiema zmiennymi

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ę Tabela danych analizy > warunkowej (w grupie Narzędzia danych lub w grupie Prognoza 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.

Przyspieszanie obliczeń w arkuszu zawierającym tabele danych

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 pozycję Formułyopcji>pliku>.

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.

Co dalej?

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.

Podłączając różne liczby do komórki, możesz szybko uzyskać różne odpowiedzi na problem. Doskonałym przykładem jest użycie funkcji PMT z różnymi stopami procentowymi i okresami kredytowania (w miesiącach), aby obliczyć, na jak wysoki kredyt na dom lub samochód możesz sobie pozwolić. Wprowadzasz liczby do zakresu komórek nazywanego tabelą danych.

W tym przypadku tabelą danych jest zakres komórek B2:D8. Możesz zmienić wartość w komórce B4 (kwotę pożyczki), a miesięczne płatności w kolumnie D zostaną automatycznie zaktualizowane. Przy stopie procentowej 3,75% komórka D2 zwraca miesięczną płatność w wysokości 1042,01 USD przy użyciu następującej formuły: =PMT(C2/12,$B$3,$B$4).

Możesz używać jednej lub dwóch zmiennych, w zależności od liczby zmiennych i formuł, które chcesz przetestować.

Użyj testu z jedną zmienną, aby zobaczyć, jak różne wartości jednej zmiennej w formule zmienią wyniki. Możesz na przykład zmienić stopę procentową dla miesięcznej raty kredytu hipotecznego za pomocą funkcji PMT. Wprowadzasz wartości zmiennych (stopy procentowe) w jednej kolumnie lub wierszu, a wyniki są wyświetlane w pobliskiej kolumnie lub wierszu.

W tym dynamicznym skoroszycie komórka D2 zawiera formułę płatności =PMT(C2/12;$B$3;$B$4). Komórka B3 jest komórką zmiennej, w której możesz wprowadzić inną długość okresu (liczbę miesięcznych okresów płatności). W komórce D2 funkcja PMT uwzględnia stopę procentową 3,75%/12, 360 miesięcy i pożyczkę w wysokości 225 000 USD, a następnie oblicza miesięczną płatność w wysokości 1042,01 USD.

Użyj testu z dwiema zmiennymi, aby zobaczyć, jak różne wartości dwóch zmiennych w formule zmienią wyniki. Możesz na przykład przetestować różne kombinacje stóp procentowych i liczby miesięcznych okresów płatności w celu obliczenia raty kredytu hipotecznego.

W tym dynamicznym skoroszycie komórka C3 zawiera formułę płatności : =PMT($B$3/12;$B$2;B4), która używa dwóch komórek zmiennych: B2 i B3. W komórce C2 funkcja PMT uwzględnia stopę procentową 3,875%/12, 360 miesięcy i pożyczkę w wysokości 225 000 USD, a następnie oblicza miesięczną płatność w wysokości 1058,03 USD.