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).
KROK 2: Zaznacz zakres komórek obejmujący wprowadzone wartości oraz odwołanie do formuły (A9:B19).
KROK 3: Na karcie Dane, w grupie Prognoza kliknij rozwijaną listę Analiza warunkowa, a następnie wybierz polecenie Tabela danych.
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.
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.
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 danych. 🙂
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).
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.
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.
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.
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.