Obliczanie wielu wyników za pomocą tabeli danych

Tabela danych jest zakresem komórek, w którym można zmieniać wartości w kilku komórkach i przynosić różne odpowiedzi na problem. Dobrą przykładem tabeli danych jest stosowanie funkcji PMT z różnymi kwotami pożyczki, a stopy procentowe w celu obliczania przystępnej kwoty pożyczki hipotecznej. Eksperymentowanie z różnymi wartościami w celu obserwowania odpowiednich zmian w wynikach to typowe zadanie w ramach analizy danych.

W programie Microsoft Excel tabele danych są częścią zestawu poleceń znanych jako narzędzia do analizy warunkowej. Podczas konstruowania i analizowania tabel danych przeprowadzana jest analiza warunkowa.

Analiza warunkowa polega na zmianie wartości w komórkach, aby zobaczyć, jak te zmiany wpłyną na wyniki formuł w arkuszu. Można na przykład użyć tabeli danych w celu zmiany stopy procentowej i okresu pożyczki, aby oszacować potencjalne miesięczne kwoty płatności.

Uwaga: Możesz wykonywać szybsze obliczenia za pomocą tabel danych i języka Visual Basic for Applications (VBA). Aby uzyskać więcej informacji, zobacz tabele danych warunkowe programu Excel: szybsze Obliczanie w języku VBA.

Typy analizy warunkowej    

W programie Excel występują trzy typy narzędzi do analizy warunkowej: scenariusze, tabele danychi cel — wyszukiwanie. Scenariusze i tabele danych używają zestawów wartości wejściowych w celu obliczania możliwych wyników. Cel — szukanie w różny sposób różni się od siebie, dlatego używa pojedynczego wyniku i oblicza możliwe wartości wejściowe, które spowodują powstanie wyniku.

Podobnie jak scenariusze, tabele danych ułatwiają poznawanie 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 może uwzględniać więcej niż dwóch zmiennych. Jeśli chcesz analizować więcej niż dwie zmienne, zamiast tego użyj scenariuszy. Chociaż jest ograniczona tylko do jednej lub dwóch zmiennych (po jednej dla wiersza wejściowej komórki i jednej dla komórki wejściowej kolumny), tabela danych może zawierać dowolną liczbę różnych wartości zmiennych. Scenariusz może zawierać maksymalnie 32 różnych wartości, ale można utworzyć dowolną liczbę scenariuszy.

Więcej informacji znajduje się w artykule wprowadzenie do analizy warunkowej.

W zależności od liczby zmiennych i formuł, które mają zostać przetestowane, należy utworzyć tabele danych z jedną zmienną lub dwoma zmiennymi.

Tabele danych z pojedynczą zmienną    

Użyj tabeli danych z pojedynczą zmienną, jeśli chcesz zobaczyć, jak różne wartości jednej zmiennej w jednej lub większej liczbie formuł zmienią wyniki tych formuł. Na przykład można użyć tabeli danych z pojedynczą zmienną, aby zobaczyć, jak różne stopy procentowe wpływają na miesięczną płatność hipoteczną przy użyciu funkcji PMT. Wartości zmiennych można wprowadzać 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 = rata (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 spowodują zmianę wyników tej formuły. Na przykład można użyć tabeli danych z dwiema zmiennymi, aby zobaczyć, jak różne kombinacje stóp procentowych i terminów pożyczek wpłyną na miesięczną płatność hipoteczną.

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

Data table with two variables
 

Obliczenia tabeli danych    

Po ponownym obliczeniu arkusza wszystkie tabele danych również zostaną ponownie obliczone, nawet jeśli nie wprowadzono żadnych zmian w danych. Aby przyspieszyć obliczenie arkusza zawierającego tabelę danych, możesz zmienić opcje obliczeń , aby automatycznie ponownie obliczyć arkusz, ale nie tabele danych. Aby dowiedzieć się więcej, zobacz przyspieszanie obliczeń w arkuszu zawierającym tabele danych.

Tabela danych z jedną zmienną zawiera wartości wprowadzone w jednej kolumnie (zorientowanej na kolumny) lub w wierszu (zorientowanym na wierszach). Każda formuła w tabeli danych z jedną zmienną musi zawierać tylko jedną komórka wprowadzania.

Wykonaj następujące czynności:

  1. Wpisz listę wartości, które chcesz podstawiać w komórce wejściowej — albo w dół o jedną kolumnę, albo 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 kolumny (wartości zmiennych znajdują się w kolumnie), wpisz formułę w komórce o wiersz powyżej i po prawej stronie kolumny wartości. Tabela danych z pojedynczą zmienną jest zorientowana na kolumny, a formuła jest zawarta w komórce D2.

      Tabela danych z jedną zmienną

      Jeśli chcesz sprawdzić 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 wiersze (wartości zmiennych znajdują się w wierszu), wpisz formułę w komórce o jednej kolumnie z lewej strony pierwszej wartości i o jednej komórce poniżej wiersza wartości.

      Jeśli chcesz sprawdzić efekty różnych wartości w innych formułach, 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 podstawiać. Na powyższej ilustracji zakresem jest C2.

  4. Na karcie dane kliknij pozycję Analiza warunkowa >tabeli 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 kolumny, wprowadź odwołanie do komórki komórki wejściowej w polu Kolumnowa komórka wejściowa . Na powyższej ilustracji komórka wejściowa ma wartość B3.

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

      Uwaga: Po utworzeniu tabeli danych może zajść potrzeba zmiany formatu komórek wynikowych. Na rysunku komórki wynikowe są sformatowane jako waluta.

Formuły używane w tabeli danych z pojedynczą 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 kolumny, wprowadź nową formułę w pustej komórce po prawej stronie istniejącej formuły w pierwszym 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> tabeli 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 kolumny, wprowadź odwołanie do komórki wejściowej w polu Kolumnowa komórka wejściowa .

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

W tabeli danych z dwiema zmiennymi użyto formuły zawierającej 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ącą 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łę = rata (B3/12, B4,-B5) w komórce C2.

  2. Wpisz pojedynczą 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 prawej stronie.

    Wpisz warunki pożyczki (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órek, w których mają być obliczone wartości (D3: E5).

    W tym przypadku wybierz zakres C2: E5.

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

  6. 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 komórkę B4 .

  7. 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 .

  8. Kliknij przycisk OK.

Przykład tabeli danych z dwiema zmiennymi

W tabeli danych z dwiema zmiennymi może być widoczny sposób, w jaki różne kombinacje stóp procentowych i terminów pożyczek wpłyną na miesięczną płatność hipoteczną. W tym miejscu, komórka C2 zawiera formułę płatności, = rata (B3/12; B4;-B5), która używa dwóch komórek wejściowych, B3 i B4.

Data table with two variables

Po ustawieniu tej opcji obliczeń nie występują obliczenia tabeli danych po wykonaniu obliczeń 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. Wykonaj jedną z następujących czynności:

    • W Excel 2007 kliknij przycisk pakietu Microsoft Office , Obraz przycisku pakietu Office kliknij pozycję Opcje programu Excel, a następnie kliknij kategorię formuły .

    • We wszystkich innych wersjach kliknij pozycję opcje > plików > formuły.

  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ę automatyczne z wyjątkiem tabel danych (w grupie Obliczanie ).

Możesz użyć kilku innych narzędzi programu Excel, aby przeprowadzić analizę warunkową, jeśli masz określone cele lub większe zestawy danych zmiennych.

Szukanie wyniku

Jeśli znasz wynik, który ma być oczekiwany na podstawie formuły, ale nie wiesz dokładnie, jaką wartość wejściową musi uzyskać formuła, aby uzyskać ten wynik, użyj funkcji szukania wyniku. Aby znaleźć odpowiedni wynik, należy dostosować wartość wejściową doartykułu.

Dodatek Solver dla programu Excel

Za pomocą dodatku Solver w programie Excel można znaleźć optymalną wartość dla zestawu zmiennych wejściowych. Dodatek Solver współpracuje z grupą komórek (nazywanych zmiennymi decyzji lub po prostu komórkami), które są używane podczas obliczania formuł w komórkach cel i ograniczenie. 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. Więcej informacji znajduje się w tym artykule: Definiowanie i rozwiązywanie problemów za pomocą dodatku Solver.

Podłączając różne numery do komórek, możesz szybko uzyskać różne odpowiedzi na problem. W niezwykle atrakcyjnym przykładzie użyto funkcji PMT z różnymi stawkami odsetek i okresami pożyczki (w miesiącach), aby ustalić, ile pożyczki można przyznać w domu lub w kabinie. Wprowadzasz liczby w zakresie komórek nazywanym tabelą danych.

W tym miejscu tabela danych jest zakresem komórek B2: D8. Możesz zmienić wartość w B4, kwotę pożyczki i miesięczne płatności w kolumnie D automatycznie aktualizowane. Przy użyciu 3,75% stopy procentowej D2 zwraca miesięczną spłatę $1 042,01 przy użyciu następującej formuły: = rata (C2/12, $B $3, $B $4).

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

W zależności od liczby zmiennych i formuł, które mają zostać przetestowane, można użyć jednej lub dwóch zmiennych.

Użyj testu jednozmiennego, aby zobaczyć, jak różne wartości jednej zmiennej w formule spowodują zmianę wyników. Na przykład możesz zmienić stawkę procentową płatności hipotecznej za pośrednictwem funkcji PMT. Wartości zmiennych (stopy procentowe) można wprowadzać w jednej kolumnie lub wierszu, a wyniki są wyświetlane w pobliżu kolumny lub wiersza.

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

Użyj testu dwuzmiennych, aby zobaczyć, jak różne wartości dwóch zmiennych w formule spowodują zmianę wyników. Na przykład możesz przetestować różne kombinacje stóp procentowych i liczby miesięcznych okresów płatności, aby obliczyć płatność hipoteczną.

W tym skoroszycie na żywo komórka C3 zawiera formułę płatności, =rata ($B $3/12, $B $2, B4), która używa dwóch komórek zmiennych, B2 i B3. W komórce C2 wtyki funkcji PMT w stopce oprocentowania 3.875%/12, 360 miesięcy i pożyczki $225 000 oraz obliczają stawkę miesięczną $1 058,03.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Uwaga:  Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Czy możesz dać nam znać, czy te informacje były pomocne? Oto angielskojęzyczny artykuł do wglądu.

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×