Jest to drugi artykuł z pięcioczęściowej serii poświęconej analizie danych w programie Excel. W tej sekcji pokażę, jak korzystać z tabeli dwóch zmiennych danych w programie Excel.
Inne artykuły z tej serii:
- Jedna tabela danych zmiennych w programie Excel.
- Menedżer scenariuszy w programie Excel.
- Szukaj celu w programie Excel.
- Solver Excela.
Obejrzyj wideo - Tabela danych z dwiema zmiennymi w programie Excel
Tabela danych z dwiema zmiennymi najlepiej sprawdza się w sytuacjach, gdy chcesz zobaczyć, jak zmienia się wynik końcowy, gdy dwie zmienne wejściowe zmieniają się jednocześnie (w porównaniu z tabelą danych z jedną zmienną, w której zmienia się tylko jedna zmienna wejściowa).
Jeśli chcesz analizować dane, gdy zmienia się więcej niż 2 zmienne, najlepszym rozwiązaniem jest menedżer scenariuszy.
Kiedy używać dwóch zmiennych tabeli danych w programie Excel
Załóżmy, że masz zestaw danych, jak pokazano poniżej:
W powyższym zestawie danych mamy Kwotę Pożyczki, Oprocentowanie oraz Liczbę Płatności Miesięcznych. Na podstawie tych 3 zmiennych wejściowych obliczana jest płatność miesięczna (jest zaznaczona na czerwono, ponieważ jest to wypływ pieniędzy). Do obliczenia miesięcznej raty stosowany jest następujący wzór:
=PMT(B2/12;B3;B1)
Teraz możesz chcieć przeprowadzić analizę, aby zobaczyć, jaka powinna być idealna kombinacja kwoty pożyczki i liczby miesięcznych spłat, aby spełnić Twoje wymagania. Na przykład możesz chcieć utrzymać miesięczną spłatę na poziomie 500 USD lub mniej i przeanalizować, jaka kombinacja kwoty pożyczki i tytułu własności może ci to dać.
W takiej sytuacji należy zastosować tabelę z dwoma zmiennymi danymi.
Konfigurowanie dwóch zmiennych tabeli danych w programie Excel
Oto kroki, aby skonfigurować tabelę dwóch zmiennych danych w programie Excel:
- W kolumnie podaj wszystkie różne wartości, które chcesz przetestować pod kątem liczby miesięcznych płatności. W tym przykładzie testujemy 72, 84, 96… 240. W tym samym czasie miej różne wartości kwoty pożyczki w wierszu tuż nad wartościami kolumn (zaczynając od jednej komórki po prawej), jak pokazano na poniższym rysunku.
- Wpisz =B4 w komórce D1, czyli o jeden wiersz powyżej wartości w kolumnie. Jest to konstrukcja, której należy przestrzegać podczas pracy z dwiema zmiennymi tabelami danych. Upewnij się również, że wartość w komórce D1 jest zależna od obu zmiennych (Liczba miesięcznych spłat i Kwota pożyczki). Nie zadziała, jeśli ręcznie wprowadzisz wartość w komórce D1.
W tym przypadku komórka D1 odwołuje się do komórki B4, która ma wartość obliczoną przy użyciu formuły wykorzystującej komórki B1, B2 i B3.
- Teraz wszystkie dane są ustawione do użycia do obliczenia tabeli danych z dwiema zmiennymi.
- Wybierz dane (D1:J16). Przejdź do zakładki Dane -> Narzędzia danych -> Co jeśli Analiza -> Tabela danych
- W oknie dialogowym tabeli danych użyj następujących odnośników:
- Komórka wejściowa wiersza: $ B $ 1
- Komórka wejściowa kolumny: $B$3
- Kliknij OK. Jak tylko klikniesz OK, natychmiast wypełni wszystkie puste komórki w wybranym zakresie danych. Zapewnia szybki podgląd miesięcznych spłat dla różnych kombinacji kwoty pożyczki i liczby miesięcznych spłat.
Na przykład, jeśli chcesz zidentyfikować kombinacje kwoty pożyczki i liczby miesięcznych spłat, które skutkowałyby wypłatą miesięczną mniejszą niż 500 USD miesięcznie, możesz po prostu użyć tej metody tabeli z 2 zmiennymi danymi.
Notatka:
- Po obliczeniu wartości za pomocą tabeli danych nie można tego cofnąć za pomocą Control + Z. Możesz jednak ręcznie wybrać wszystkie wartości i usunąć je.
- Nie można usunąć/zmodyfikować żadnej komórki w całym zestawie obliczonych wartości. Ponieważ jest to tablica, będziesz musiał usunąć wszystkie wartości.
