To pierwszy artykuł z pięcioczęściowej serii poświęconej analizie danych w programie Excel. W tej sekcji pokażę, jak utworzyć i używać tabeli danych jednej zmiennej w programie Excel.
Inne artykuły z serii Analiza danych:
- Dwie zmienne tabele danych w programie Excel.
- Menedżer scenariuszy w programie Excel.
- Szukaj celu w programie Excel.
- Solver Excela.
Obejrzyj wideo - tabela danych z jedną zmienną w programie Excel
Tabela danych z jedną zmienną w programie Excel jest najbardziej odpowiednia w sytuacjach, gdy chcesz zobaczyć, jak zmienia się wynik końcowy po zmianie jednej ze zmiennych wejściowych. Jeśli chcesz zmienić dwie zmienne, użyj tabeli danych z dwiema zmiennymi lub Menedżera scenariuszy.
Kiedy używać jednej zmiennej 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.
Wartość Miesięcznej Płatności jest obliczana na podstawie tych trzech zmiennych (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 liczba miesięcznych płatności odpowiada Twojemu stanowi i jak miesięczna płatność będzie się różnić w zależności od tego. Na przykład możesz chcieć zachować okres kredytowania (liczbę miesięcznych spłat), aby kwota miesięcznej płatności nie przekraczała 1000 USD.
Szybkim sposobem jest zmiana danych w tej tabeli, a formuła zostanie automatycznie zaktualizowana. Jeśli jednak chcesz sprawdzić, czy dla wielu różnych miesięcznych płatności, najlepszym rozwiązaniem będzie tabela danych z jedną zmienną w programie Excel.
Konfigurowanie tabeli danych jednej zmiennej w programie Excel
Oto kroki, aby skonfigurować tabelę danych z jedną zmienną w programie Excel:
- W kolumnie podaj wszystkie różne wartości liczby miesięcznych płatności, które chcesz przetestować. W tym przykładzie testujemy 72, 84, 96… 240.
- Wpisz =B4 w komórce E1, czyli o jeden wiersz powyżej wartości w sąsiedniej kolumnie po prawej stronie. Jest to konstrukcja, której należy przestrzegać podczas pracy z jedną tabelą zmiennych danych w programie Excel. Upewnij się również, że wartość w komórce E1 zależy od liczby miesięcznych płatności. Nie zadziała, jeśli ręcznie wprowadzisz wartość w komórce E1.
W tym przypadku komórka E1 odnosi 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 w tabeli danych jednej zmiennej.
- Wybierz dane (D1:E16). Przejdź do zakładki Dane -> Narzędzia danych -> Co jeśli Analiza -> Tabela danych.
- W oknie dialogowym Tabela danych odnieś się do komórki B3 w polu Komórka wejściowa kolumny. Ponieważ używamy tabeli danych z jedną zmienną, pozostaw komórkę wejściową Wiersz pustą. Ponadto wybraliśmy opcję Kolumnowa komórka wejściowa, ponieważ dane są uporządkowane w kolumnie.
- Kliknij OK. Jak tylko klikniesz OK, automatycznie wypełni komórki sąsiadujące ze zmiennymi punktami danych (liczba miesięcznych płatności).
Na przykład, jeśli chcesz obliczyć, jakiej liczby miesięcznych płatności należy użyć, aby miesięczna płatność była mniejsza niż 1000 USD. Możesz łatwo wybrać 144 miesiące (12 lat).
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.
- Możesz wykonać te same obliczenia, układając dane poziomo. Jedyną różnicą jest to, że będziesz musiał użyć komórki wejściowej Wiersz w oknie dialogowym tabeli danych (zamiast komórki wejściowej kolumny).
