Tabela danych w programie Microsoft Excel

Często konieczne jest obliczenie końcowego wyniku dla różnych kombinacji danych wejściowych. Dzięki temu użytkownik będzie mógł ocenić wszystkie możliwe opcje działań, wybrać te, których wyniki interakcji spełniają, a na koniec wybrać najbardziej optymalną opcję. W Excelu do wykonania tego zadania dostępne jest specjalne narzędzie - "Tabela danych" ( "Tabela podstawień" ). Dowiedzmy się, jak z niego skorzystać, aby wykonać powyższe scenariusze.

Czytaj także: Wybór parametrów w Excelu

Używanie tabeli danych

Narzędzie "Tabela danych" służy do obliczania wyniku dla różnych odmian jednej lub dwóch zdefiniowanych zmiennych. Po obliczeniu wszystkie możliwe warianty pojawią się w formie tabeli, która nazywana jest macierzą analizy czynnikowej. "Tabela danych" odnosi się do grupy narzędzi analizy "Co jeśli" , która znajduje się na wstążce w zakładce "Dane" w bloku "Praca z danymi" . Przed programem Excel 2007 narzędzie to nosiło nazwę "Tabela podstawiania" , co jeszcze bardziej dokładnie odzwierciedlało jego istotę niż obecna nazwa.

Tabelę substytucji można wykorzystać w wielu przypadkach. Na przykład typową opcją jest obliczenie kwoty miesięcznej spłaty kredytu dla różnych zmian okresu kredytowania i kwoty pożyczki, okresu kredytowania i stopy procentowej. Ponadto narzędzie to może być wykorzystywane w analizie modeli projektów inwestycyjnych.

Ale powinieneś także wiedzieć, że nadmierne użycie tego narzędzia może prowadzić do hamowania systemu, ponieważ dane są ciągle przeliczane. Dlatego zaleca się, aby w małych tabelarycznych tablicach rozwiązywać podobne zadania, nie używać tego narzędzia, ale używać kopiowania formuł przy użyciu znacznika wypełnienia.

Uzasadnione zastosowanie "Tabeli danych" dotyczy tylko dużych zakresów tabel, gdy kopiowanie formuł może zająć dużo czasu, a podczas procedury zwiększa prawdopodobieństwo popełnienia błędów. Ale w tym przypadku zaleca się również wyłączenie automatycznego przeliczania formuł w zakresie tabeli podstawień, aby uniknąć niepotrzebnego obciążenia systemu.

Główną różnicą między różnymi zastosowaniami tabeli danych jest liczba zmiennych uwzględnionych w obliczeniach: jedna lub dwie zmienne.

Metoda 1: Zastosuj narzędzie z jedną zmienną

Natychmiast rozważmy opcję, gdy tabela danych jest używana z jedną wartością zmiennej. Weźmy najbardziej typowy przykład pożyczek.

Obecnie oferujemy następujące warunki kredytowania:

• Okres kredytowania - 3 lata (36 miesięcy);
• Kwota pożyczki wynosi 900 000 rubli;
• Oprocentowanie wynosi 12,5% rocznie.

Płatności następują pod koniec okresu płatności (miesiąc) w ramach programu rentowego, to znaczy w równych częściach. Jednocześnie na początku całego okresu spłaty znaczną część płatności stanowią płatności odsetkowe, ale wraz ze spadkiem kwoty wypłacanych odsetek spadają kwoty spłaty samego organu. Całkowita kwota płatności, jak już wspomniano powyżej, pozostaje niezmieniona.

Konieczne jest obliczenie, jaka będzie kwota miesięcznej płatności, która obejmuje spłatę pożyczki i odsetki. Aby to zrobić, istnieje operator PLT w programie Excel.

PLT należy do grupy funkcji finansowych, a jego zadaniem jest obliczanie miesięcznej spłaty pożyczki typu rentowego w oparciu o kwotę jednostki kredytowej, okres kredytowania i stopę procentową. Składnia tej funkcji przedstawiona jest w tym formularzu

=ПЛТ(ставка;кпер;пс;бс;тип)

"Stawka" jest argumentem, który określa oprocentowanie płatności kredytowych. Wskaźnik jest ustawiony na okres. Mamy okres wypłaty wynoszący jeden miesiąc. Dlatego roczna stawka 12,5% powinna zostać rozbita na liczbę miesięcy w roku, czyli 12.

"Kper" - argument, który określa liczbę okresów dla całego okresu pożyczki. W naszym przykładzie okres wynosi jeden miesiąc, a okres kredytowania wynosi 3 lata lub 36 miesięcy. Tak więc liczba okresów będzie wczesna 36.

"PS" jest argumentem, który określa aktualną wartość pożyczki, to znaczy wielkość instytucji kredytowej w momencie jej wydania. W naszym przypadku liczba ta wynosi 900 000 rubli.

"BS" jest argumentem wskazującym kwotę jednostki kredytowej w momencie jej pełnej zapłaty. Oczywiście wskaźnik ten będzie wynosił zero. Ten argument nie jest wymaganym parametrem. Jeśli go pominiesz, przyjmuje się, że jest równy liczbie "0".

"Typ" jest również opcjonalnym argumentem. Zgłasza, kiedy płatność zostanie dokonana: na początku okresu (parametr - "1" ) lub na koniec okresu (parametr - "0" ). Jak pamiętamy, płacimy na koniec miesiąca kalendarzowego, to znaczy, że wartość tego argumentu będzie równa "0" . Ale biorąc pod uwagę, że ten wskaźnik nie jest obowiązkowy i domyślnie, jeśli go nie używasz, wartość i tak jest rozumiane jako "0" , wtedy w podanym przykładzie na ogół nie można go użyć.

1. A więc zacznijmy obliczenia. Wybierz komórkę na arkuszu, gdzie wyświetlona zostanie obliczona wartość. Klikamy przycisk "Wstaw funkcję" .
2. Uruchomi się Kreator funkcji . Dokonujemy przejścia do kategorii "Finanse" , wybierz nazwę "PLT" z listy i kliknij przycisk "OK" .
3. Następnie aktywowane jest okno argumentów powyższej funkcji.

   Kursor umieszczamy w polu "Bet" , a następnie klikamy komórkę na arkuszu z wartością rocznej stopy procentowej. Jak widać, pole natychmiast wyświetla współrzędne. Ale, jak pamiętamy, potrzebujemy miesięcznej stawki, dlatego dzielimy wynik na 12 ( / 12 ).

   W polu "Kper" w taki sam sposób wpisujemy współrzędne komórek okresu kredytowania. W takim przypadku nie musisz niczego dzielić.

   W polu "Ps" należy podać współrzędne komórki zawierającej kwotę jednostki pożyczki. Robimy to. Również umieść znak "-" przed wyświetlanymi współrzędnymi. Faktem jest, że funkcja PLT domyślnie daje wynik końcowy z negatywnym znakiem, słusznie biorąc pod uwagę miesięczną spłatę pożyczki jako stratę. Jednak ze względu na przejrzystość korzystania z tabeli danych liczba ta musi być dodatnia. Dlatego umieszczamy znak minus przed jednym z argumentów funkcji. Jak wiesz, pomnożenie "minus" przez "minus" ostatecznie daje "plus" .

   W polach "Bc" i "Type" nie tworzymy żadnych danych. Kliknij przycisk "OK" .

4. Następnie operator oblicza i wyprowadza wynik całkowitej miesięcznej płatności - 30108.26 rubli na wyznaczoną komórkę. Problem polega jednak na tym, że pożyczkobiorca jest w stanie zapłacić maksymalnie 29 000 rubli miesięcznie, to znaczy, że powinien albo znaleźć bank oferujący warunki o niższej stopie procentowej, albo zmniejszyć pożyczkę, albo wydłużyć okres pożyczki. Tabela podstawień pomoże nam obliczyć różne opcje działania.
5. Najpierw używamy tabeli podstawień z jedną zmienną. Zobaczmy, jak wartość obowiązkowej płatności miesięcznej zmienia się w zależności od wahań rocznej stawki, począwszy od 9,5% rocznie, a kończąc na 12,5% rocznie w 0,5% . Wszystkie pozostałe warunki pozostają niezmienione. Rysujemy zakres tabelaryczny, którego nazwy kolumn odpowiadają różnym zmianom w stopie procentowej. W tym samym czasie wiersz "Miesięczne płatności" pozostaje bez zmian. Pierwsza komórka musi zawierać formułę, którą obliczyliśmy wcześniej. Aby uzyskać więcej informacji, możesz dodać linie "Łączna kwota kredytu" i "Całkowita kwota odsetek" . Kolumna, w której znajduje się obliczenie, jest wykonywana bez tytułu.
6. Następnie obliczamy całkowitą kwotę pożyczki w bieżących warunkach. Aby to zrobić, wybierz pierwszą komórkę linii "Łączna kwota kredytu" i pomnóż zawartość komórek "Miesięczna płatność" i "Okres kredytowania" . Następnie kliknij przycisk Enter .
7. Aby obliczyć całkowitą kwotę odsetek w obecnych warunkach, odejmujemy kwotę pożyczki od całkowitej kwoty pożyczki w ten sam sposób. Aby wyświetlić wynik na ekranie, kliknij przycisk Enter . W ten sposób otrzymujemy kwotę, którą przepłacamy przy spłacie pożyczki.
8. Teraz nadszedł czas na zastosowanie narzędzia Tabela danych . Wybierz całą tablicę tabel, z wyjątkiem nazw wierszy. Następnie przejdź do zakładki "Dane" . Klikamy przycisk na wstążce "Analiza co jeśli" , która znajduje się w grupie narzędzi "Praca z danymi" (w programie Excel 2016, grupa narzędzi "Prognoza" ). Następnie otwiera się małe menu. W nim wybierz pozycję "Tabela danych ..." .
9. Otworzy się małe okno, które nazywa się "Tabelą danych" . Jak widać, ma on dwa pola. Ponieważ pracujemy z jedną zmienną, potrzebujemy tylko jednego z nich. Ponieważ zmienimy zmienną za pomocą kolumn, użyjemy pola "Zastąp wartości według kolumn w" . Ustaw tam kursor, a następnie kliknij komórkę w źródłowym zestawie danych, który zawiera bieżącą wartość procentową. Po wyświetleniu współrzędnych komórki w polu kliknij przycisk "OK" .
10. Narzędzie oblicza i wypełnia cały zakres tabeli wartościami, które odpowiadają różnym opcjom dla stopy procentowej. Jeśli umieścisz kursor w dowolnym elemencie danego obszaru tabeli, możesz zobaczyć, że formuła formuły nie wyświetla zwykłej formuły do ​​obliczania płatności, ale specjalna formuła dla tablicy nierozerwalnej. Oznacza to, że teraz nie można zmienić wartości w poszczególnych komórkach. Możesz usunąć wyniki obliczeń tylko razem, a nie osobno.

Ponadto można zauważyć, że wartość miesięcznej płatności w wysokości 12,5% rocznie, uzyskana w wyniku zastosowania tabeli permutacji, odpowiada wartości za taką samą kwotę odsetek, jaką otrzymaliśmy poprzez zastosowanie funkcji PLT . To jeszcze raz potwierdza prawidłowość obliczeń.

Analizując tablicę tablicową, należy stwierdzić, że, jak widzimy, tylko w wysokości 9,5% rocznie jest do przyjęcia poziom miesięcznej płatności (poniżej 29 000 rubli).

Lekcja: Obliczanie wypłaty renty w programie Excel

Metoda 2: użyj narzędzia z dwiema zmiennymi

Oczywiście, bardzo trudno jest znaleźć banki udzielające pożyczek w wysokości 9,5% rocznie. Zobaczmy więc, jakie opcje istnieją, aby zainwestować w akceptowalny poziom miesięcznej płatności za różne kombinacje innych zmiennych: wielkość jednostki kredytowej i okres kredytowania. Jednocześnie utrzymujemy stopę procentową na niezmienionym poziomie (12,5%). W rozwiązaniu tego zadania narzędzie "Tabela danych" pomoże nam użyć dwóch zmiennych.

1. Rysowanie nowej tablicy tabel. Teraz nazwy kolumn będą wskazywać okres kredytowania (od 2 do 6 lat w miesiącach w przyrostach jednego roku), aw wierszach - kwotę jednostki kredytowej (od 850000 do 950000 rubli z krokiem 10 000 rubli). Warunkiem obowiązkowym jest to, że komórka, w której znajduje się formuła obliczeniowa (w naszym przypadku PLT ), znajduje się na granicy nazw rzędów i kolumn. Bez tego warunku narzędzie nie będzie działało, gdy będą używane dwie zmienne.
2. Następnie wybierz cały otrzymany zakres tabel, w tym nazwy kolumn, wierszy i komórki z formułą PLT . Przejdź do zakładki "Dane" . Podobnie jak w poprzednim czasie, klikamy przycisk "Analiza co jeśli" w grupie narzędzi "Praca z danymi" . Na otwartej liście wybierz pozycję "Tabela danych ..." .
3. Rozpocznie się okno narzędzia "Tabela danych" . W takim przypadku potrzebujemy obu pól. W polu "Zastąp wartości według kolumn w", podajemy współrzędne komórki zawierającej okres kredytowania w danych pierwotnych. W polu "Wartości zastępcze wg wierszy w" wskazujemy adres komórki parametrów początkowych, zawierający wartość bryły kredytu. Po wprowadzeniu wszystkich danych. Kliknij przycisk "OK" .
4. Program oblicza i wypełnia zakres tabeli danymi. Na przecięciu wierszy i kolumn widać dokładnie, jaka będzie miesięczna płatność, wraz z odpowiednią wartością rocznych odsetek i określonego okresu pożyczki.
5. Jak widać, istnieje wiele wartości. Aby rozwiązać inne problemy, może ich być jeszcze więcej. Dlatego, aby wyniki wyników były bardziej widoczne i od razu określić, które wartości nie spełniają danego warunku, można użyć narzędzi do wizualizacji. W naszym przypadku będzie to formatowanie warunkowe. Wybierz wszystkie wartości w zakresie tabeli, z wyłączeniem nagłówków wierszy i kolumn.
6. Przejście do karty "Strona główna" i kliknięcie ikony "Formatowanie warunkowe" . Znajduje się w polu narzędzi "Style" na wstążce. Z rozwijanego menu wybierz "Reguły wyboru komórki" . Na dodatkowej liście kliknij pozycję "Mniej ..." .
7. Następnie zostanie otwarte okno formatowania warunkowego. W lewym polu określ wartość, która będzie mniejsza od ilości przydzielonych komórek. Jak pamiętamy, jesteśmy zadowoleni z warunku, w którym miesięczna spłata kredytu będzie mniejsza niż 29 000 rubli. Wpisujemy ten numer. W prawym polu możesz wybrać kolor podświetlenia, choć domyślnie możesz go zostawić. Po wprowadzeniu wszystkich wymaganych ustawień kliknij przycisk "OK" .
8. Następnie wszystkie komórki, których wartości odpowiadają powyższemu warunkowi, zostaną podświetlone kolorem.

Analizując tablicę tablicową, można wyciągnąć pewne wnioski. Jak widzimy, przy istniejącym okresie kredytowania (36 miesięcy), aby zainwestować we wskazaną powyżej kwotę miesięcznej płatności, musimy zaciągnąć pożyczkę nieprzekraczającą 860000,00 rubli, czyli o 40 000 mniej niż pierwotnie planowano.

Jeżeli nadal zamierzamy zaciągnąć pożyczkę w wysokości 900 000 rubli, okres kredytowania powinien wynosić 4 lata (48 miesięcy). Tylko w tym przypadku miesięczna płatność nie przekroczy ustalonego limitu w wysokości 29 000 rubli.

Tak więc, korzystając z tej tablicy tablicowej i analizując za i przeciw każdej opcji, kredytobiorca może podjąć konkretną decyzję w sprawie warunków pożyczki, wybierając najbardziej odpowiednią opcję spośród wszystkich możliwych opcji.

Oczywiście tabela przeglądowa może służyć nie tylko do obliczania opcji pożyczek, ale także do rozwiązywania wielu innych problemów.

Lekcja: Formatowanie warunkowe w programie Excel

Ogólnie należy zauważyć, że tablica przeglądowa jest bardzo użytecznym i stosunkowo prostym narzędziem do określania wyniku dla różnych kombinacji zmiennych. Równoczesne stosowanie formatowania warunkowego, dodatkowo można wizualizować otrzymane informacje.

• ← Otwieranie tabel ODS w Microsoft Excel
• Zapytania SQL w Microsoft Excel →