excelpogodzinach-logo
Tabele danych - Excel Po Godzinach

Jak używać tabel danych?

Zastanawiasz się nad kupnem wymarzonego mieszkania lub domu i poszukujesz najtańszego kredytu hipotecznego? A może chcesz znaleźć najkorzystniejszą lokatę bankową? Co robisz? Włączasz Excela, wprowadzasz zgromadzone informacje i tworzysz formułę, aby przeanalizować wszystkie dostępne scenariusze i wybrać ten właściwy. Problem w tym, że formuła prezentuje tylko jeden wynik na raz, a Ty potrzebujesz porównać rezultaty przykładowo dla pięciu różnych poziomów oprocentowania. W takim przypadku nie pozostaje Ci nic innego jak użyć tabeli danych, która zdecydowanie ułatwi Ci to zadanie.

Tabela danych to najprościej rzecz ujmując tabela umożliwiająca wyświetlenie wyników dla wielu danych wejściowych jednocześnie.

Dla przykładu możesz porównać jaki kapitał wraz z odsetkami zgromadzisz wpłacając konkretną sumę pieniędzy na roczną lokatę w zależności od wysokości oprocentowania zaoferowanego przez banki.

Pracując w Excelu możesz tworzyć tabele danych z jedną zmienną lub z dwiema zmiennymi.

W przypadku tej pierwszej analizujesz wynik działania formuły w oparciu o zmianę jednego elementu np. wysokości oprocentowania, natomiast w drugim przypadku zmianie ulegają aż dwie wartości np. wysokość oprocentowania i okres na jaki lokata została zawarta.

Tworzenie tabel danych z jedną lub dwiema zmiennymi jest dziecinnie proste, o czym za chwilę sam się przekonasz. Aby jeszcze bardziej zobrazować zasady ich definiowania posłużę się dwoma przykładami, po jednym dla każdej z tabel.

Tabela danych z jedną zmienną

Dla tabeli z jedną zmienną obliczymy jaką wartość kapitału wraz z odsetkami możesz uzyskać wpłacając 5000 zł na trzymiesięczną lokatę bankową w zależności od wysokości oferowanego oprocentowania.

W tym przypadku zmienną, która będzie ulegała zmianie jest oczywiście wysokość oprocentowania lokaty.

Aby stworzyć tabelę danych z jedną zmienną należy postępować według poniższej procedury:
KROK 1: Wprowadź do arkusza odwołanie do formuły (tutaj komórka B9) oraz wartości, których chcesz użyć jako zmienna (zakres A10:A19).
 
Jak używać tabel danych? - Excel Po Godzinach
KROK 2: Zaznacz zakres komórek obejmujący wprowadzone wartości oraz odwołanie do formuły (A9:B19).
 
Jak używać tabel danych? - Excel Po Godzinach
KROK 3: Na karcie Dane, w grupie Prognoza kliknij rozwijaną listę Analiza warunkowa, a następnie wybierz polecenie Tabela danych.
 
Jak używać tabel danych? - Excel Po Godzinach
Jeśli korzystasz z Excela 2007, to na karcie Dane znajduje się grupa Narzędzia danych, gdzie znajdziesz rozwijaną listę Analiza symulacji. Kliknij ją, a następnie wybierz polecenie Tabela danych.
KROK 4: W oknie dialogowym Tabela danych podaj adres komórki źródłowej, dla której wartość będzie ulegała zmianie. Jeżeli wprowadziłeś serię danych w kolumnie, to uzupełnij pole Kolumnowa komórka wejściowa (tutaj $B$3). W przeciwnym razie adres komórki źródłowej należy podać w polu Wierszowa komórka wejściowa.
 
Jak używać tabel danych? - Excel Po Godzinach
KROK 5: Kliknij przycisk OK.

Excel pobiera każdą wartość z zakresu A10:A19 i zastępuje nią komórkę wejściową ($B$3), a następnie wyświetla wynik formuły w tabeli danych. Po zakończeniu obliczeń, Excel zwraca oryginalną wartość kolumnowej i/lub wierszowej komórki wejściowej.
 
Jak używać tabel danych? - Excel Po Godzinach
Tym samym uzyskujesz gotową odpowiedź na temat tego jaką wartość kapitału wraz z odsetkami otrzymasz z ulokowania kwoty 5000 zł na 3 miesiące przy różnym oprocentowaniu.

Mając raz utworzoną tabelę danych możesz zmieniać poszczególne wartości komórek arkusza, z których korzysta formuła. Po każdej modyfikacji, Excel przeliczy wszystkie formuły w tabeli.

Miej jednak na uwadze, że w przypadku naprawdę obszernych tabel danych przeliczenie formuł może Excelowi zająć sporo czasu.

Jeśli chcesz się ustrzec przed taką sytuacją, to możesz wyłączyć obliczanie tabel danych. Jak to zrobić? Otóż zadanie jest bardzo proste.

KROK 1: Wybierz kartę Plik, a następnie kliknij Opcje.
KROK 2: W oknie dialogowym Opcje programu Excel wybierz kategorię Formuły i przejdź do sekcji Opcje obliczania.
KROK 3: Zaznacz opcję Obliczanie skoroszytu: Automatycznie, z wyjątkiem tabel danych.
KROK 4: Kliknij przycisk OK.

Oczywiście ten sam efekt można uzyskać szybciej klikając na wstążce Excela kartę Formuły, a następnie w grupie Obliczanie rozwijając listę Opcje obliczania i wybierając polecenie Automatyczne z wyjątkiem tabel przestawnych. :)

Od teraz za każdym razem Excel przeliczy cały arkusz pomijając istniejące tabele danych.

Aby przeliczyć tabele danych wystarczy, że użyjesz klawisza funkcyjnego F9 (oblicza cały skoroszyt) lub kombinacji Shift + F9 (oblicza aktywny arkusz).

Tabela danych z dwoma zmiennymi

Przykład z użyciem tabeli z dwiema zmiennymi będzie dotyczył obliczenia wysokości miesięcznej raty kredytu hipotecznego na kwotę 275000 zł. Analizy dokonamy dla różnych wartości oprocentowania oraz długości okresu spłaty kredytu.

Wstawienie tabeli danych z dwiema zmiennymi wymaga wykonania następujących czynności:
KROK 1: Wprowadź do arkusza odwołanie do formuły (tutaj komórka B8).
KROK 2: Wprowadź wartości pierwszej zmiennej umieszczając je pod komórką zawierającą odwołanie do formuły (zakres B9:B15) oraz wartości drugiej zmiennej na prawo od wspomnianej komórki (zakres C8:H8).
 
Jak używać tabel danych? - Excel Po Godzinach
KROK 3: Zaznacz zakres komórek obejmujący wprowadzone wartości oraz odwołanie do formuły (tutaj B8:H15).
KROK 4: Na karcie Dane, w grupie Prognoza kliknij rozwijaną listę Analiza warunkowa, a następnie wybierz polecenie Tabela danych.
KROK 5: W oknie dialogowym Tabela danych podaj adresy komórek źródłowych, dla których wartość będzie ulegała zmianie. Uzupełnij pole Wierszowa komórka wejściowa wskazując na adres komórki zawierającej ten typ danych – tutaj Czas trwania (w miesiącach), czyli komórka B2. Następnie w polu Kolumnowa komórka wejściowa również podaj adres właściwej komórki zawierającej informację o oprocentowaniu, tutaj komórka B3.
 
Jak używać tabel danych? - Excel Po Godzinach
KROK 6: Kliknij przycisk OK.

Excel wstawił nową tabelę danych zawierającą różne wartości miesięcznej raty kredytu, która uwarunkowana jest dwoma zmiennymi – stopą procentową oraz okresem kredytowania.
 
Jak używać tabel danych? - Excel Po Godzinach
W ten oto prosty sposób wygenerowałeś zestaw danych, które teraz możesz porównać i wybrać to rozwiązanie, które najbardziej odpowiada Twoim oczekiwaniom i/lub możliwościom finansowym.

Tabela danych – edycja, usuwanie i kopiowanie

Modyfikacja tabeli danych jest możliwa tylko częściowo. Mianowicie możesz edytować wartości zmiennych oraz odwołanie do formuły lub samą formułę (jeśli ta była bezpośrednio użyta). Jednakże Excel nie pozwoli Ci na zmianę dowolnej komórki zawierającej wyniki działania tabeli danych. Dlaczego?

Otóż korzystając z polecenia Tabela danych Excel posiłkuje się wewnętrzną formułą tablicową TABELA(). Nie znajdziesz jej ani na karcie Formuły w grupie Biblioteka funkcji, ani w oknie dialogowym Wstawianie funkcji (wywoływanym skrótem klawiaturowym Shift + F3), ani też wpisując ją bezpośrednio w komórce arkusza.

Dlatego, jeśli chcesz zmienić otrzymane wyniki, musisz zaznaczyć całą tabelę i ponownie uruchomić polecenie Tabela danych.

Możesz również usunąć całość formuły tablicowej zaznaczając stosowny zakres (w drugim przykładzie jest to zakres C9:H15), a następnie naciskając klawisz Delete. Jeżeli będziesz próbował usunąć pojedynczą komórkę wynikową tabeli danych, to Excel wyświetli stosowny komunikat informujący, że zmiana części tabeli nie jest możliwa.
 
Jak używać tabel danych? - Excel Po Godzinach

I na koniec pozostało nam kopiowanie. Wykonując tą operację należy mieć na uwadze, że wklejając tabelę danych w inne miejsce Excel zamienia wszystkie wartości formuły tablicowej na stałe.

Podobał Ci się ten artykuł?

Jeśli tak, to dołącz do grona Czytelników bloga Excel Po Godzinach, którzy każdego tygodnia otrzymują sprawdzone porady, wskazówki oraz rozwiązania nietypowych problemów.

7 myśli na temat “Jak używać tabel danych?

    1. Cześć U Mamusi Muminka,

      Tak to już bywa, że na studiach Excel jest traktowany po macoszemu. A potem okazuje się, że w pracy trzeba uczyć się wszystkiego od nowa.

      Dzięki za miłe słowa i zapraszam do regularnego zaglądania. Napisz też w komentarzu lub przez formularz kontaktowy co sprawia Ci największą trudność w pracy z Excelem.

      Pozdrawiam

  1. Dzień dobry p. Michale co tu można powiedzieć po prostu dobry temat umówiony dot. wątku Tabeli i jak zawsze profesjonalne podejście do zagadnienia. Chciałbym, aby też jeszcze jakieś artykuły na ten temat pojawiły się na pańskim blogu. Ponadto co raz częściej pojawiają się nowe .Jeśli można prosić proszę omówić np. takie tematy Tworzenie tabel i list na potrzeby tabel przestawnych oraz Wydajne przestawianie danych z innego skoroszytu. Ogółem można stwierdzić jeden z lepszych prowadzonych blogów na temat Excelu pomagający zwykłym użytkownikom Excela, który trzyma wysoki poziom nauczania. Może Pan Michał skusi się na wydanie swojej pierwszej książki autorskiej o tematyce Excel Warto zrobić ankietę wśród czytelników ,czy by widzieli taką książkę od Pana oraz , czy jest takie zapotrzebowanie .Ponadto proszę zrobić tzw. licznik odwiedzin strony lub artykułu bardziej bym był przy liczniku odwiedzin danego artykułu. Wtedy wiadomo ile osób wchodzi na dany temat. Trzymam kciuki za dalsze prowadzenie blogu i skąd Pan znajduje czas na prowadzenie jego.
    Pozdrawiam
    Jankesd

    1. Witaj Marku,

      Dzięki za to pytanie. Jak zwykle moi Czytelnicy nie zawodzą mnie swoją dociekliwością. :)

      Co do lokaty bankowej, gdzie obliczamy wartość na koniec okresu wraz z odsetkami, to użyta została funkcja FV, której składnia jest następująca =FV(stopa;liczba_rat;rata;wartość_aktualna;typ). W przykładzie funkcja ta miała podane następujące argumenty stopa (B3/B1), liczba rat (B2), wartość aktualna (B4). Pominąłem parametr rata, ponieważ wpłata była tylko na początku oraz typ. Ten ostatni może przyjmować wartości 0 (płatność odsetek na koniec okresu) lub 1 (płatność odsetek na początek okresu). Jeśli jest pominięty, to tak jakbym wstawił 0.
      Cała formuła wygląda tak: FV(B3/B1;B2;;B4)

      Ratę spłaty pożyczki możesz obliczyć korzystając z funkcji finansowej PMT, której składnia wygląda tak: =PMT(stopa;liczba_rat;wartość_początkowa;wartość_przyszła;typ). W przykładzie przypisałem następujące argumenty: stopa (B3/B1); liczba rat (B2); wartość początkowa (B4); wartość przyszła, czyli po spłacie kredytu (0). Typ pominięty, więc płatność na koniec okresu. Całość wygląda tak: PMT(B3/B1;B2;B4;0)

      Pozdrawiam

  2. Witam ponownie p. Michale chociaż moje pytanie nie jest związane z omawianym tematem, lecz szukam dobrego rozwiązania problemu dot. porównanie trzech plików o takich samych nazwach/używam Excela 2007/, a mianowicie w różnych folderach mam zestawienie danych np. osób z imienia i nazwiska, daty, powiązania, w trzech poszczególnych miesiącach np. osoby w styczniu 2016, osoby w czerwcu 2016 oraz osoby w październiku 2016.Pliki te maja takie same nazwy i nie można zmienić. Chciałbym porównać w jednym oknie obok siebie arkusz z danymi z wymienionymi miesiącami i tu się zaczyna problem w postaci ,że pliki posiadają takie same nazwy i próbując otwarcia drugiego lub trzeciego pliku w programie Excel podaje komunikat o takiej treści dokument o nazwie osoby w styczniu 2016 xls jest już otwarty i nie mogę mieć trzech otwartych dokumentów o tej samej nazwie, które są w dwóch różnych folderach i aby otworzyć drugi lub trzeci dokument albo zamknij dokument, który masz otwarty, albo zmień nazwę jednego dokumentu.Jeśli nacisnę oki. plik nie zostanie otwarty, a więc w tym przypadku nie mam jakiekolwiek możliwości otwarcia interesujących mnie trzech skoroszytów, które posiadają identyczne nazwy w jednym oknie Excela?

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *