Jak utworzyć tabelę danych do analizy „co, jeśli” w programie Excel?

Microsoft Excel oferuje kilka funkcji do analizy scenariuszy „co, jeśli”. Jednym z takich narzędzi jest arkusz danych. Dzięki niemu możesz eksperymentować z różnymi wartościami, aby zobaczyć różne wyniki.

Jest to przydatny sposób przeglądania rzeczy takich jak pożyczki, inwestycje, narzuty produktów itp. z różnymi zmiennymi. Na przykład możesz wprowadzić kilka różnych stóp procentowych pożyczki, aby zobaczyć, jaka będzie Twoja płatność przy każdej stopie, wszystko w jednym miejscu.

Utwórz tabelę danych z jedną zmienną

Tabele danych w programie Excel mogą zawierać jedno lub dwa różne dane wejściowe. Zacznijmy więc od tabeli danych z jedną zmienną. Obliczyliśmy przyszłą wartość naszej inwestycji za pomocą funkcji FV i stałych dla rocznej stopy procentowej, liczby płatności i kwoty płatności.

Tutaj zobaczymy, jaka będzie przyszła wartość naszych inwestycji przy różnych stopach procentowych.

Możesz wprowadzić swoje wartości w kolumnie lub wierszu, ale rozmieszczenie wpływa na to, gdzie powinieneś umieścić swoją formułę. Jeśli Twoje wartości są w kolumnie, umieść formułę o jedną komórkę powyżej i po prawej stronie. Jeśli twoje wartości są w rzędzie, umieść formułę o jedną komórkę w dół i po lewej stronie.

W naszym przykładzie używamy tabeli danych zorientowanej kolumnowo. Jak widać na poniższym zrzucie ekranu, przenieśliśmy naszą formułę do kolumny D i wymieniliśmy nasze stopy procentowe w kolumnie C.

Zaznacz komórki zawierające formułę, wartości i oczekiwane wyniki. Dla nas to od C2 do D6.

Kliknij kartę Dane, kliknij strzałkę listy rozwijanej Analiza warunkowa i wybierz opcję Tabela danych.

W otwartym oknie Tabela danych wprowadź odwołanie do komórki dla zmiennej, która ma zostać zmodyfikowana zgodnie z ustawieniami. W naszym przykładzie wprowadzamy odwołanie do komórki B3 dla zmieniającej się stopy procentowej w polu Column Entry Cell. Ponownie używamy tabeli danych opartej na kolumnach.

Kliknij OK, a zobaczysz wyniki. Mamy teraz przyszłą wartość naszej inwestycji z każdą wprowadzoną stopą procentową.

Utwórz tabelę danych z dwiema zmiennymi

Możesz dodać dwie zmienne do tabeli danych, aby uzyskać bardziej szczegółową analizę. Korzystając z tego samego przykładu, nie tylko chcemy uzyskać przyszłą wartość naszych inwestycji o różnych stopach procentowych, ale także chcemy zobaczyć różne przyszłe wartości poprzez zmianę liczby płatności.

Ponieważ używasz dwóch zmiennych, będziesz używać tabeli danych zorientowanej zarówno na kolumny, jak i na wiersze. Dlatego umieść formułę bezpośrednio nad kolumną wartości i po lewej stronie wiersza wartości, jak pokazano poniżej.

Teraz zaznacz wszystkie komórki zawierające formułę, wartości i oczekiwane wyniki. Dla nas to od C2 do F6.

Kliknij kartę Dane i wybierz opcję Analiza warunkowa > Tabela danych. Gdy pojawi się okno, wprowadź odwołania do komórek wejściowych wierszy i kolumn.

Korzystając z naszego przykładu, wprowadzamy B3 dla komórki wpisu kolumny, która dotyczy naszej stopy procentowej, oraz B4 dla komórki wpisu wiersza, która dotyczy naszej kwoty płatności.

Kliknij OK i powinieneś zobaczyć wszystkie dane wyjściowe twoich zmiennych. Tutaj mamy przyszłą wartość naszej inwestycji przy różnych stopach procentowych i różnej liczbie płatności, a wszystko to przedstawione w ładnej i zgrabnej tabeli.

Następnym razem, gdy chcesz przeanalizować dane w arkuszu z różnymi liczbami lub kwotami, rozważ użycie arkusza danych. Nie tylko obliczenia są wykonywane za Ciebie, ale wynik daje ustrukturyzowaną tabelę ułatwiającą analizę.