Tabela danych to zakres komórek, w którym można zmieniać wartości w niektórych komórkach i powiedzieć na różne odpowiedzi na problem. Dobrym przykładem tabeli danych jest funkcja PMT z różnymi kwotami pożyczki i stopami procentowymi, aby obliczyć przystępną cenowo kwotę domowego kredytu hipotecznego. Eksperymentowanie z różnymi wartościami w celu obserwowania odpowiednich zmian wyników to typowe zadanie podczas analizy danych.

W Microsoft Excel tabele danych są częścią pakietu poleceń nazywanych narzędziami analizy What-If analiz. Konstruowanie i analizowanie tabel danych jest analizą co do sytuacji.

Analiza jeżeli jest procesem zmiany wartości w komórkach w celu zobaczenia, jak te zmiany wpłyną na wynik formuł w arkuszu. Na przykład za pomocą tabeli danych można zróżnić stopę procentową i długość okresu pożyczki — aby ocenić potencjalne kwoty płatności miesięcznych.

Uwaga: Za pomocą tabel danych i Visual Basic for Applications (VBA) można wykonywać szybsze obliczenia. Aby uzyskać więcej informacji, zobacz Excel What-If Danych: Szybsze obliczenia przy użyciu VBA.

Typy analizy what-if    

Istnieją trzy typy narzędzi do analizy what-if w programie Excel: scenariusze,tabele danych i szukanie celu. Scenariusze i tabele danych używają zestawów wartości wejściowych do obliczania możliwych wyników. Szukanie wyniku jest odrębnie różne, używa pojedynczego wyniku i oblicza możliwe wartości wejściowe, które dają ten wynik.

Tabele danych, podobnie jak scenariusze, ułatwiają zbiór możliwych wyników. W przeciwieństwie do scenariuszy tabele danych pokazują wszystkie wyniki w jednej tabeli w jednym arkuszu. Tabele danych ułatwiają szybkie przeanalizowanie 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 może uwzględniać więcej niż dwóch zmiennych. Aby przeanalizować więcej niż dwie zmienne, należy użyć scenariuszy. Chociaż jest ona ograniczona tylko do jednej lub dwóch zmiennych (jedna dla komórki wejściowej wiersza i jedna dla komórki wejściowej kolumny), tabela danych może zawierać tyle różnych zmiennych, ile chcesz. Scenariusz może mieć maksymalnie 32 różne wartości, ale można utworzyć tyle scenariuszy, ile tylko chcesz.

Więcej informacji zawiera artykuł Wprowadzenie do analizy What-If danych.

Utwórz tabele danych z jedną zmienną lub z dwiema zmiennymi w zależności od liczby zmiennych i formuł do przetestowania.

Tabele 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ł, użyj tabeli danych z jedną zmienną. Można na przykład użyć tabeli danych z jedną zmienną, aby zobaczyć, jak różne stopy procentowe wpływają na comiesięczne spłaty kredytu hipotecznego przy użyciu funkcji PMT. Wartości zmiennych wprowadza się w jednej kolumnie lub wierszu, a wyniki są wyświetlane w przylegającej 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ą

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żna na przykład użyć tabeli danych z dwiema zmiennymi, aby zobaczyć, jaki wpływ będą mieć różne kombinacje stopy procentowej i warunków kredytu 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.

Data table with two variables
 

Obliczenia tabeli danych    

Zawsze, gdy arkusz jest ponownie obliczanie, wszelkie tabele danych również będą ponownie obliczanie — nawet jeśli dane nie zostały zmieniane. Aby przyspieszyć obliczanie arkusza zawierającego tabelę danych, można zmienić opcje obliczania w celu automatycznego ponownego obliczania arkusza, ale nie tabel 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 (zorientowanych 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 wejściowej — w jednej kolumnie 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ę (wartości zmiennych znajdują się w kolumnie), wpisz formułę w komórce jeden wiersz powyżej i jedną komórkę po prawej stronie kolumny wartości. Ta tabela danych z jedną zmienną jest zorientowana na kolumnę i formuła jest zawarta w komórce D2.


      Tabela danych z jedną zmienną
      Jeśli chcesz zbadać efekty różnych wartości w innych formułach, wprowadź dodatkowe formuły w komórkach po prawej stronie pierwszej formuły.

    • Jeśli tabela danych jest zorientowana na wiersz (wartości zmiennych znajdują się w wierszu), wpisz formułę w komórce o jedną kolumnę po lewej stronie pierwszej wartości i jedną komórkę poniżej wiersza wartości.

      Jeśli chcesz zbadać 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 zastąpić. Na powyższej ilustracji ten zakres to C2:D5.

  4. Na karcie Dane kliknij pozycję Analiza >danych (w grupie Narzędzia danych lub Prognoza w grupie 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 komórki wejściowej w polu Komórka wprowadzania kolumny. Na powyższej ilustracji komórka wejściowa to B3.

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

      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 tych:

    • Jeśli tabela danych jest zorientowana na kolumnę, 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 na wiersz, 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> (w grupie Narzędzia danych lub Prognoza w Excel 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 wprowadzania kolumny.

    • Jeśli tabela danych jest zorientowana na wiersz, 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łę, która odwołuje 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, 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 takim 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 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 tym przypadku zaznacz zakres C2:E5.

  5. Na karcie Dane w grupie Narzędzia danych lub Prognoza (w programie Excel 2016 ) kliknij pozycję Analiza >analiza >dane (w grupie Narzędzia danych lub grupa Prognoza w 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órki wejściowej Kolumna wprowadź odwołanie do komórki wejściowej dla wartości wejściowych w kolumnie.
    Wpisz B3 w polu Komórka wprowadzania danych w kolumnie.

  8. Kliknij przycisk OK.

Przykład tabeli danych z dwiema zmiennymi

W tabeli danych z dwiema zmiennymi można zobaczyć, jak różne kombinacje stopy procentowej i warunków pożyczki wpływają na comiesięczne spłaty kredytu hipotecznego. Na rysunku poniżej 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.

Data table with two variables

Ustawienie tej opcji obliczania nie powoduje, że obliczenia w tabeli danych nie są wykonywane po ponownej obliczeniach w całym skoroszycie. Aby ręcznie ponownie obliczyć tabelę danych, zaznacz jej formuły, a następnie naciśnij klawisz F9.

Wykonaj poniższe czynności, aby zwiększyć wydajność obliczeń:

  1. Kliknij pozycję > opcje >formuł.

  2. W sekcji Opcje obliczania w obszarze Obliczaniekliknij 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 (wgrupie Obliczanie).

Jeśli masz określone cele lub większe zestawy danych zmiennych, możesz użyć kilku innych narzędzi do analizy Excel analizy what-if.

Szukanie wyniku

Jeśli znasz wynik formuły, której można oczekiwać, ale nie wiesz dokładnie, jakie wartości wejściowe formuły są potrzebne do jego uzyskania, użyj funkcji Goal-Seek danych. Zobacz artykuł Szukanie wyniku w celu znalezienia odpowiedniego wyniku przez dostosowanie wartości wejściowej.

Excel Solver

Za pomocą dodatku Excel Solver można znaleźć optymalną wartość dla zestawu zmiennych wejściowych. Solver pracuje z grupą komórek (zwanych zmiennymi decyzyjnymi lub po prostu komórkami zmiennych) używanych do obliczania formuł w komórkach celu i komórek 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 z tego artykułu: Definiowanie i rozwiązywanie problemu za pomocą do solver.

Podłączenie różnych liczb do komórki pozwala szybko rozwiązać różne problemy. Doskonałym przykładem jest zastosowanie funkcji PMT z różnymi stopami oprocentowania i okresami pożyczki (w miesiącach), aby określić, jaką część pożyczki możesz zasiądąć na samochód lub dom. Liczby wprowadza się w zakresie komórek nazywanym tabelą danych.

Tabela danych to zakres komórek B2:D8. Wartość w kolumnie B4, kwoty pożyczki i miesięcznych spłat w kolumnie D można zmienić automatycznie. Przy oprocentowaniu 3,75% funkcja D2 zwraca miesięczną spłatę w wysokości 1 042,01 zł przy użyciu tej formuły: =PMT(C2/12$B$3$B$4).

Ten zakres komórek (B2:D8) to tabela danych

Możesz użyć 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żna na przykład zmienić stopę procentową comiesięcznej spłaty kredytu hipotecznego przy użyciu funkcji PMT. Wartości zmiennych (stopy procentowe) wprowadza się w jednej kolumnie lub wierszu, a wyniki są wyświetlane w sąsiedniej kolumnie lub wierszu.

W tym skoroszycie na żywo komórka D2 zawiera formułę płatności =PMT(C2/12;$B$3;$B$4). Komórka B3 to komórka zmienna, w której można podłączyć inną długość okresu (liczba miesięcznych okresów płatności). W komórce D2 funkcja PMT podłącza stopę procentową 3,75%/12, 360 miesięcy i pożyczkę na kwotę 225 000 zł i oblicza kwotę miesięcznej spłaty 1 042,01 zł.

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

W tym skoroszycie na żywo komórka C3 zawiera formułę płatności : =PMT($B$3/12;$B$2;B4), która korzysta z dwóch komórek zmiennych: B2 i B3. W komórce C2 funkcja PMT podłącza stopę procentową 3,875%/12, 360 miesięcy i pożyczkę na kwotę 225 000 zł i oblicza kwotę miesięcznej spłaty 1 058,03 zł.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społeczności witryny Answers.

Potrzebna dalsza pomoc?

Rozwijaj swoje umiejętności
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów Microsoft Office

Czy te informacje były pomocne?

Jaka jest jakość języka?
Co wpłynęło na Twoje wrażenia?

Dziękujemy za opinię!

×