Obliczanie wielu wyników za pomocą tabeli danych

Tabela danych jest zakresem komórek, który pokazuje, jak pewne zmiany jednej lub dwóch zmiennych w formułach (formuła: Sekwencja wartości, odwołań do komórek, nazw, funkcji lub operatorów w komórce, które razem dają nową wartość. Formuła zawsze zaczyna się od znaku równości (=).) wpływają na wyniki tych formuł. Tabele danych umożliwiają skrócone obliczanie wielu wyników w jednej operacji oraz oglądanie i porównywanie wyników wszystkich możliwych kombinacji w jednym arkuszu.

W tym artykule


Omówienie

Tabele danych są częścią zestawu poleceń nazywanych narzędziami analizy symulacji. Korzystanie z tabel danych to przeprowadzanie analizy symulacji.

Analiza symulacji polega na zmienianiu wartości w komórkach, aby zobaczyć, jak te zmiany wpłyną na wynik formuł w arkuszu. Tabeli danych można użyć na przykład do zmiany stopy procentowej i długości okresu dla kredytu w celu określenia możliwych kwot rat miesięcznych.

Rodzaje analizy symulacji    W programie Excel są dostępne trzy rodzaje narzędzi do analizy symulacji: scenariusze, tabele danych i funkcja Szukanie wyniku. Scenariusze i tabele danych określają możliwe wyniki na podstawie zestawów wartości wejściowych. Funkcja Szukanie wyniku działa inaczej niż scenariusze i tabele danych, ponieważ na podstawie wyniku określa możliwe wartości wejściowe, które dają ten wynik.

Podobnie jak scenariusze, tabele danych pomagają badać zestaw możliwych wyników. W przeciwieństwie do scenariuszy tabele danych przedstawiają wszystkie wyniki w jednej tabeli w jednym arkuszu. Użycie tabel danych ułatwia szybkie sprawdzenie zakresu możliwości. Wyniki są łatwe do odczytania i udostępnienia w formie tabeli dzięki skupieniu się tylko na jednej lub dwóch zmiennych.

Tabela danych nie może uwzględniać więcej niż dwóch zmiennych. Jeśli mają być analizowane więcej niż dwie zmienne, należy użyć scenariuszy. Chociaż tabela danych jest ograniczona tylko do jednej lub dwóch zmiennych (jednej dla komórki wejściowej wiersza i jednej dla komórki wejściowej kolumny), może ona zawierać dowolną liczbę wartości zmiennych. Scenariusz może mieć maksymalnie 32 różne wartości, ale można utworzyć dowolną liczbę scenariuszy.

Aby uzyskać więcej informacji na temat narzędzi analizy symulacji, prześledź łącza w sekcji Zobacz też.

Podstawy tabel danych

W zależności od liczby zmiennych i formuł, które mają zostać przetestowane, można utworzyć tabelę danych z jedną zmienną lub dwiema zmiennymi.

Tabele danych z jedną zmienną    Tabeli danych z jedną zmienną można użyć, aby zobaczyć, jak różne wartości tej zmiennej w jednej lub kilku formułach wpłyną na wyniki tych formuł. Można na przykład użyć tabeli danych z jedną zmienną do sprawdzenia, jak różne stopy procentowe wpłyną na wysokość miesięcznych rat kredytu hipotecznego, za pomocą funkcji PMT. Wartości zmiennej należy wprowadzić w jednej kolumnie lub wierszu, a wyniki zostaną wyświetlone 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.

Tabela danych z jedną zmienną

Tabela danych z jedną zmienną

Tabele danych z dwoma zmiennymi    Tabeli danych z dwiema zmiennymi można użyć do sprawdzenia, jak różne wartości dwóch zmiennych w jednej formule zmienią wyniki tej formuły. Tabeli danych z dwiema zmiennymi można na przykład użyć do sprawdzenia, jak różne kombinacje stopy procentowej i okresu kredytu wpłyną na wysokość miesięcznych rat kredytu hipotecznego.

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

Tabela danych z dwoma zmiennymi

Tabela danych z dwiema zmiennymi

Obliczanie tabel danych    Tabele danych są obliczane ponownie za każdym razem, gdy arkusz jest obliczany ponownie, nawet jeśli nic się w nich nie zmieniło. Aby przyspieszyć obliczanie arkusza zawierającego tabele danych, można zmienić opcje w obszarze Obliczanie tak, aby było wykonywane automatyczne obliczanie arkusza z wyjątkiem tabel danych. Zobacz sekcję Przyspieszanie obliczeń w arkuszu z tabelami danych.

Początek strony Początek strony

Tworzenie tabeli danych z pojedynczą zmienną

Tabela danych z jedną zmienną ma wartości wejściowe umieszczone w kolumnie (orientacja kolumnowa) lub w wierszu (orientacja wierszowa). Formuły używane w tabeli danych z jedną zmienną muszą odwoływać się tylko do jednej komórki wejściowej (komórka wejściowa: Komórka, w której podstawiana jest każda wartość wejściowa z tabeli danych. Komórką wejściową może być dowolna komórka w arkuszu. Chociaż komórka wejściowa nie musi być częścią tabeli danych, formuły w tabelach danych muszą odwoływać się do komórki wejściowej.).

  1. Wpisz listę wartości, które chcesz podstawić w komórce wejściowej, umieszczając je w pojedynczej kolumnie lub w pojedynczym wierszu. Z każdej strony wartości zostaw kilka pustych wierszy i kolumn.
  2. Wykonaj jedną z następujących czynności:
    • Jeśli tabela danych jest zorientowana kolumnowo (wartości zmiennej są wpisane w kolumnie), wpisz formułę w komórce w wierszu ponad pierwszą wartością i o jedną komórkę w prawo od kolumny zawierającej wartości. Tabela danych z jedną zmienną przedstawiona na ilustracji w sekcji Omówienie jest zorientowana kolumnowo, a formuła znajduje się w komórce D2.

Aby sprawdzić wpływ różnych wartości na inne formuły, wpisz dodatkowe formuły w komórkach na prawo od pierwszej formuły.

  • Jeśli tabela danych jest zorientowana wierszowo (wartości zmiennej są wpisane w wierszu), wpisz formułę w komórce w kolumnie na lewo od pierwszej wartości i o jedną komórkę poniżej wiersza zawierającego wartości.

Aby sprawdzić wpływ różnych wartości na inne formuły, wpisz dodatkowe formuły w komórkach poniżej pierwszej formuły.

  1. Zaznacz zakres komórek zawierający formuły i wartości, które chcesz podstawiać. Na pierwszej ilustracji w sekcji Omówienie ten zakres to C2:D5.
  2. Na karcie Dane w grupie Narzędzia danych kliknij przycisk Analiza symulacji, a następnie kliknij polecenie Tabela danych.
  1. Wykonaj jedną z następujących czynności:

 Uwaga   Po utworzeniu tabeli danych można zmienić format komórek wynikowych. Na ilustracji komórki wynikowe są sformatowane jako walutowe.

Początek strony Początek strony

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

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

  1. Wykonaj jedną z następujących czynności:
    • Jeśli tabela danych jest zorientowana kolumnowo (wartości zmiennej są wpisane w kolumnie), wpisz 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 (wartości zmiennej są wpisane w wierszu), wpisz 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 w grupie Narzędzia danych kliknij przycisk Analiza symulacji, a następnie kliknij polecenie Tabela danych.
  1. Wykonaj jedną z następujących czynności:
    • Jeśli tabela danych jest zorientowana kolumnowo, wpisz odwołanie do komórki wejściowej w polu Kolumnowa komórka wejściowa.
    • Jeżeli tabela danych jest zorientowana na wiersze, wpisz odwołanie do komórki wejściowej w polu Wierszowa komórka wejściowa.

Początek strony Początek strony

Tworzenie tabeli danych z dwiema zmiennymi

W tabeli danych z dwiema zmiennymi używana jest formuła, która zawiera dwie listy wartości wejściowych. Ta formuła musi odwoływać się do dwóch różnych komórek wejściowych.

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

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

  1. Pierwszą listę argumentów wpisz w tej samej kolumnie, poniżej formuły.

W tym przypadku wpisz różne wartości stóp procentowych w komórkach C3, C4 i C5.

  1. Drugą listę wpisz w tym samym wierszu, w którym znajduje się formuła, po jej prawej stronie.

Wpisz okresy kredytu (w miesiącach) w komórkach D2 i E2.

  1. Zaznacz zakres komórek zawierający formułę (C2) oraz zarówno wiersz, jak i kolumnę wartości (C3:C5 i D2:E2), a także komórki z obliczanymi wartościami (D3:E5).

W tym przypadku zaznacz zakres C2:E5.

  1. Na karcie Dane w grupie Narzędzia danych kliknij przycisk Analiza symulacji, a następnie kliknij polecenie Tabela danych.
  1. W polu Wierszowa komórka wejściowa wprowadź odwołanie do komórki wejściowej dla wartości wejściowych w wierszu.

W polu Wierszowa komórka wejściowa wpisz B4.

  1. W polu Kolumnowa komórka wejściowa wprowadź odwołanie do komórki wejściowej dla wartości wejściowych w kolumnie.

W polu Kolumnowa komórka wejściowa wpisz B3.

  1. Kliknij przycisk OK.

Przykład    Tabela danych z dwiema zmiennymi można pokazywać, jak różne kombinacje stopy procentowej i okresu kredytu wpłyną na wysokość miesięcznych rat kredytu hipotecznego. Na poniższej ilustracji komórka C2 zawiera formułę płatności =PMT(B3/12;B4;-B5), która odwołuje się do dwóch komórek wejściowych: B3 i B4.

Tabela danych z dwoma zmiennymi


Początek strony Początek strony

Przyspieszanie obliczeń w arkuszu z tabelami danych

  1. Kliknij przycisk Microsoft Office Obraz przycisku, kliknij przycisk Opcje programu Excel, a następnie kliknij kategorię Formuły.
  1. W sekcji Opcje obliczania w obszarze Obliczanie kliknij opcję Automatycznie z wyjątkiem tabel danych.

 Porada   Można również na karcie Formuły w grupie Obliczanie kliknąć strzałkę na przycisku Opcje obliczania, a następnie kliknąć polecenie Automatycznie z wyjątkiem tabel danych.

 Uwaga   Po zaznaczeniu tej opcji obliczania tabele danych są pomijane, gdy reszta skoroszytu jest obliczana ponownie. Aby ręcznie ponownie obliczyć tabelę danych, zaznacz jej formuły, a następnie naciśnij klawisz F9.

Początek strony Początek strony

 
 
Dotyczy:
Excel 2007