Analiza warunkowa z tabelami danych
Dzięki tabeli danych w programie Excel można łatwo zmieniać jedno lub dwa dane wejściowe i przeprowadzać analizę typu „co, jeśli”. Tabela danych to zakres komórek, w których można zmienić wartości w niektórych komórkach i znaleźć różne odpowiedzi na problem.
Istnieją dwa typy tabel danych -
- Tabele danych z jedną zmienną
- Tabele danych z dwiema zmiennymi
Jeśli masz więcej niż dwie zmienne w swoim problemie analizy, musisz użyć narzędzia Menedżer scenariuszy programu Excel. Aby uzyskać szczegółowe informacje, zapoznaj się z rozdziałem - Analiza warunkowa z Menedżerem scenariuszy w tym samouczku.
Tabele danych z jedną zmienną
Tabelę danych z jedną zmienną można wykorzystać, jeśli chcesz zobaczyć, jak różne wartości jednej zmiennej w jednej lub kilku formułach zmienią wyniki tych formuł. Innymi słowy, za pomocą tabeli danych z jedną zmienną można określić, jak zmiana jednego wejścia zmienia dowolną liczbę wyjść. Zrozumiesz to na przykładzie.
Example
Jest pożyczka w wysokości 5 000 000 na okres 30 lat. Chcesz poznać miesięczne płatności (EMI) dla różnych stóp procentowych. Możesz również być zainteresowany poznaniem kwoty odsetek i kwoty głównej spłacanej w drugim roku.
Analiza z tabelą danych z jedną zmienną
Analizę z tabelą danych z jedną zmienną należy przeprowadzić w trzech krokach -
Step 1 - Ustaw wymagane tło.
Step 2 - Utwórz tabelę danych.
Step 3 - Wykonaj analizę.
Rozumiemy szczegółowo te kroki -
Krok 1: Ustaw wymagane tło
Załóżmy, że stopa procentowa wynosi 12%.
Wypisz wszystkie wymagane wartości.
Nazwij komórki zawierające wartości, aby formuły miały nazwy zamiast odwołań do komórek.
Ustaw obliczenia dla EMI, Skumulowanych odsetek i Łącznej kwoty głównej za pomocą funkcji programu Excel - odpowiednio PMT, CUMIPMT i CUMPRINC.
Twój arkusz powinien wyglądać następująco -
Możesz zobaczyć, że komórki w kolumnie C mają nazwy podane w odpowiednich komórkach w kolumnie D.
Krok 2: Utwórz tabelę danych
Wpisz listę wartości, tj. Stopy procentowe, które chcesz podstawić w komórce wejściowej w kolumnie E w następujący sposób -
Wpisz pierwszą funkcję (PMT) w komórce jeden wiersz powyżej i jedną komórkę po prawej stronie kolumny wartości. Wpisz pozostałe funkcje (CUMIPMT and CUMPRINC) w komórkach po prawej stronie pierwszej funkcji.
Teraz dwa wiersze powyżej wartości stopy procentowej wyglądają następująco -
Jak zauważyłeś, nad wartościami stopy procentowej znajduje się pusty wiersz. Ten wiersz dotyczy formuł, których chcesz użyć.
Tabela danych wygląda jak podano poniżej -
Krok 3: Przeprowadź analizę za pomocą narzędzia tabeli danych analizy warunkowej
Wybierz zakres komórek, które zawierają formuły i wartości, które chcesz podstawić, czyli wybierz zakres - E2: H13.
Kliknij kartę DANE na Wstążce.
Kliknij opcję Analiza warunkowa w grupie Narzędzia danych.
Wybierz opcję Tabela danych z listy rozwijanej.
Data Table pojawi się okno dialogowe.
- Kliknij ikonę w polu Kolumnowa komórka wejściowa.
- Kliknij komórkę Interest_Rateczyli C2.
Jak widać, komórka wejściowa kolumny jest przyjmowana jako $ C $ 2. Kliknij OK.
Tabela danych jest wypełniona obliczonymi wynikami dla każdej z wartości wejściowych, jak pokazano poniżej -
Jeśli możesz zapłacić EMI w wysokości 54000, możesz zauważyć, że stopa procentowa w wysokości 12,6% jest dla Ciebie odpowiednia.
Tabele danych z dwiema zmiennymi
Aby zobaczyć, jak różne wartości dwóch zmiennych w formule zmienią wyniki tej formuły, można użyć tabeli danych z dwiema zmiennymi. Innymi słowy, za pomocą dwóch zmiennych tabeli danych można określić, w jaki sposób zmiana dwóch wejść zmienia jedno wyjście. Zrozumiesz to na przykładzie.
Example
Jest pożyczka w wysokości 50 000 000. Chcesz wiedzieć, jak różne kombinacje stóp procentowych i okresu trwania pożyczki wpłyną na miesięczną płatność (EMI).
Analiza z tabelą danych z dwiema zmiennymi
Analizę z tabelą danych z dwiema zmiennymi należy przeprowadzić w trzech krokach -
Step 1 - Ustaw wymagane tło.
Step 2 - Utwórz tabelę danych.
Step 3 - Wykonaj analizę.
Krok 1: Ustaw wymagane tło
Załóżmy, że stopa procentowa wynosi 12%.
Wypisz wszystkie wymagane wartości.
Nazwij komórki zawierające wartości, tak aby formuła miała nazwy zamiast odwołań do komórek.
Ustaw obliczenia dla EMI za pomocą funkcji Excel - PMT.
Twój arkusz powinien wyglądać następująco -
Możesz zobaczyć, że komórki w kolumnie C mają nazwy podane w odpowiednich komórkach w kolumnie D.
Krok 2: Utwórz tabelę danych
Rodzaj =EMI w komórce F2.
Wpisz pierwszą listę wartości wejściowych, tj. Stopy procentowe, w kolumnie F, zaczynając od komórki poniżej wzoru, czyli F3.
Wpisz drugą listę wartości wejściowych, tj. Liczbę płatności w drugim wierszu, zaczynając od komórki po prawej stronie wzoru, czyli G2.
Tabela danych wygląda następująco -
Wykonaj analizę, korzystając z tabeli danych narzędzia analitycznego What-If Analysis Tool
Wybierz zakres komórek, które zawierają formułę i dwa zestawy wartości, które chcesz podstawić, czyli wybierz zakres - F2: L13.
Kliknij kartę DANE na Wstążce.
Kliknij opcję Analiza warunkowa w grupie Narzędzia danych.
Wybierz tabelę danych z listy rozwijanej.
Pojawi się okno dialogowe Tabela danych.
- Kliknij ikonę w polu Wiersz komórki wejściowej.
- Kliknij komórkę NPERczyli C3.
- Ponownie kliknij ikonę w polu Wiersz komórki wejściowej.
- Następnie kliknij ikonę w polu Kolumnowa komórka wejściowa.
- Kliknij komórkę Interest_Rate, czyli C2.
- Ponownie kliknij ikonę w polu Kolumnowa komórka wejściowa.
Zobaczysz, że komórka wejściowa Row jest przyjmowana jako $ C $ 3, a komórka wejściowa Column jest przyjmowana jako $ C $ 2. Kliknij OK.
Tabela danych zostanie wypełniona obliczonymi wynikami dla każdej kombinacji dwóch wartości wejściowych -
Jeśli możesz zapłacić EMI w wysokości 54 000, oprocentowanie 12,2% i 288 EMI są dla Ciebie odpowiednie. Oznacza to, że okres pożyczki wynosiłby 24 lata.
Obliczenia tabeli danych
Tabele danych są obliczane ponownie za każdym razem, gdy arkusz roboczy je zawierający jest obliczany ponownie, nawet jeśli nie uległy zmianie. Aby przyspieszyć obliczenia w arkuszu zawierającym tabelę danych, należy zmienić opcje obliczeń naAutomatically Recalculate arkusz roboczy, ale nie tabele danych, jak podano w następnej sekcji.
Przyspieszenie obliczeń w arkuszu
Obliczenia w arkuszu zawierającym tabele danych można przyspieszyć na dwa sposoby -
- Z opcji programu Excel.
- Ze Wstążki.
Z opcji programu Excel
- Kliknij kartę PLIK na Wstążce.
- Wybierz Opcje z listy w lewym okienku.
Pojawi się okno dialogowe Opcje programu Excel.
W lewym okienku wybierz Formulas.
Wybierz opcję Automatic except for data tables pod Workbook Calculationw sekcji Opcje obliczeniowe. Kliknij OK.
Ze Wstążki
Kliknij kartę FORMUŁY na Wstążce.
Kliknij Calculation Options w grupie Obliczenia.
Wybierz Automatic Except for Data Tables na liście rozwijanej.
