Wykreślanie linii trendu w programie Excel

Excel zapewnia solidny zestaw narzędzi do ilustrowania trendów. Możesz to zrobić, wykreślając linie trendu na wykresach programu Excel, aby przedstawić wizualizację swoich danych . Tutaj dowiesz się, jak wykreślić logarytmiczne linie trendu, linie trendu mocy i wielomianowe linie trendu w programie Excel.

Wykreślanie logarytmicznej linii trendu w programie Excel

Logarytmiczna tendencja jest taka, w której dane wzrasta lub spada bardzo szybko na początku, ale potem zwalnia i poziomy z upływem czasu. Przykładem trendu logarytmicznego jest wzorzec sprzedaży bardzo oczekiwanego nowego produktu, który zazwyczaj sprzedaje się w dużych ilościach przez krótki czas, a następnie spada.

Aby zwizualizować taki trend, możesz wykreślić logarytmiczną linię trendu. Jest to zakrzywiona linia przechodząca przez punkty danych, w której różnice między punktami po jednej stronie linii i po drugiej stronie linii wzajemnie się znoszą.

Oto kroki, które należy wykonać, aby wykreślić logarytmiczną linię trendu w programie Excel:

Kliknij wykres, aby go wybrać.

Jeśli wykres zawiera wiele serii danych, kliknij serię, którą chcesz przeanalizować.

Wybierz Projekt → Dodaj element wykresu → Linia trendu → Więcej opcji linii trendu.
Zostanie wyświetlone okienko Formatuj linię trendu.

Kliknij kartę Opcje linii trendu.

Wybierz przycisk radiowy Logarytmiczny.
Excel kreśli logarytmiczną linię trendu.

(Opcjonalnie) Zaznacz pole wyboru Wyświetl równanie na wykresie.
Jeśli chcesz tylko zobaczyć linię trendu, możesz pominąć kroki 6 i 7.

(Opcjonalnie) Zaznacz pole wyboru Wyświetl wartość R-kwadrat na wykresie.

Kliknij Zamknij.

Excel wyświetla równanie regresji i wartość R2. Ten obraz przedstawia wykres z wykreśloną linią trendu wykładniczego, równaniem regresji i wartością R2.

Wykres z logarytmiczną linią trendu.

Gdy najlepiej dopasowaną linią trendu jest krzywa logarytmiczna, równanie regresji przyjmuje następującą ogólną postać:

y = m * ln(x) + b

y jest zmienną zależną; x jest zmienną niezależną; b i m są stałymi; a ln jest logarytmem naturalnym, dla którego można użyć funkcji Excela LN.

Excel nie ma funkcji, która bezpośrednio oblicza wartości b i m. Możesz jednak użyć funkcji REGLINP, jeśli „wyprostujesz” krzywą logarytmiczną, używając skali logarytmicznej dla niezależnych wartości:

{=REGLINP( znane_ys , LN( znane_xs ), const , stats )}

Wykreślanie linii trendu mocy w programie Excel

W wielu przypadkach analizy regresji najlepszym dopasowaniem jest trend potęgowy, w którym dane stale rosną lub maleją. Taki trend wyraźnie nie jest wykładniczy ani logarytmiczny, co oznacza ekstremalne zachowanie na końcu trendu (w przypadku wykładniczego) lub na początku trendu (w przypadku logarytmicznego). Przykładami trendów energetycznych są przychody, zyski i marże w odnoszących sukcesy firmach, z których wszystkie wykazują stały wzrost tempa wzrostu z roku na rok.

Trend mocy brzmi liniowo, ale wykreślenie linii trendu mocy pokazuje zakrzywioną linię najlepszego dopasowania przechodzącą przez punkty danych. W analizie takich danych zazwyczaj najlepiej jest najpierw wypróbować linię trendu liniowego. Jeśli to nie daje dobrego dopasowania, przejdź do linii trendu mocy.

Wykonaj następujące kroki, aby wykreślić linię trendu mocy w programie Excel:

Kliknij wykres, aby go wybrać.

Jeśli wykres zawiera wiele serii danych, kliknij serię, którą chcesz przeanalizować.

Wybierz Projekt → Dodaj element wykresu → Linia trendu → Więcej opcji linii trendu.
Zostanie wyświetlone okienko Formatuj linię trendu.

Kliknij kartę Opcje linii trendu.

Wybierz przycisk opcji Zasilanie.
Excel kreśli linię trendu mocy.

(Opcjonalnie) Zaznacz pole wyboru Wyświetl równanie na wykresie.
Jeśli chcesz tylko zobaczyć linię trendu, pomiń kroki 6 i 7.

(Opcjonalnie) Zaznacz pole wyboru Wyświetl wartość R-kwadrat na wykresie.

Kliknij Zamknij.

Excel wyświetla równanie regresji i wartość R2 (opisane poniżej). Poniższy obraz przedstawia wykres z wykreśloną linią trendu mocy, równaniem regresji i wartością R2.

Wykres z linią trendu mocy.

Gdy najlepiej dopasowaną linią trendu jest krzywa mocy, równanie regresji przyjmuje następującą ogólną postać:

y = m * xb

y jest zmienną zależną; x jest zmienną niezależną; a b i m są stałymi.

Nie ma dostępnej funkcji arkusza kalkulacyjnego do bezpośredniego obliczania wartości b i m. Możesz jednak użyć funkcji REGLINP, jeśli „wyprostujesz” krzywą mocy, stosując skalę logarytmiczną do wartości zależnych i niezależnych:

{= REGLINP (ln ( known_ys ), LN ( known_xs ) const , Stats )}

Wykreślanie wielomianowej linii trendu w programie Excel

W wielu rzeczywistych scenariuszach związek między zmiennymi zależnymi i niezależnymi nie porusza się w jednym kierunku. To byłoby zbyt łatwe. Na przykład, zamiast stale rosnąć — jednostajnie, jak w trendzie liniowym, ostro, jak w trendzie wykładniczym lub logarytmicznym, lub stale, jak w trendzie potęgowym — dane takie jak sprzedaż jednostkowa, zyski i koszty mogą zmieniać się w górę i w dół .

Aby zwizualizować taki trend, można wykreślić wielomianową linię trendu, która jest najlepiej dopasowaną linią wielu krzywych wyprowadzonych przy użyciu równania wykorzystującego wiele potęg x. Liczba potęg x jest rzędem równania wielomianowego. Ogólnie rzecz biorąc, im wyższy rząd, tym ściślej krzywa pasuje do istniejących danych, ale tym bardziej nieprzewidywalne są prognozowane wartości.

Jeśli masz już wykres, wykonaj następujące kroki, aby dodać wielomianową linię trendu w programie Excel:

Kliknij wykres, aby go wybrać.

Jeśli wykres zawiera wiele serii danych, kliknij serię, którą chcesz przeanalizować.

Wybierz Projekt → Dodaj element wykresu → Linia trendu → Więcej opcji linii trendu.
Zostanie wyświetlone okienko Formatuj linię trendu.

Kliknij kartę Opcje linii trendu.

Wybierz przycisk opcji Wielomian.

Kliknij strzałki przycisku pokrętła Kolejność, aby ustawić żądaną kolejność równania wielomianowego.

Excel kreśli wielomianową linię trendu.

(Opcjonalnie) Zaznacz pole wyboru Wyświetl równanie na wykresie.
Jeśli chcesz tylko zobaczyć linię trendu, omiń kroki 7 i 8.

(Opcjonalnie) Zaznacz pole wyboru Wyświetl wartość R-kwadrat na wykresie.

Kliknij Zamknij.

Excel wyświetla równanie regresji i wartość R2. Poniższy obraz przedstawia wykres z wykreśloną linią trendu wielomianowego, równaniem regresji i wartością R2.

Wykres z wielomianową linią trendu.

Gdy najlepiej dopasowaną linią trendu jest krzywa wielomianowa, równanie regresji przyjmuje następującą postać:

y = m n x n + … + m2x2 + m1x + b

y jest zmienną zależną; x jest zmienną niezależną; a b i m n do m1 są stałymi.

Aby obliczyć wartości b i mn do m1, możesz użyć funkcji REGLINP, jeśli zwiększysz wartości znane_xs do potęg od 1 do n dla wielomianu n -tego rzędu:

{=REGLINP( znane_ys , znane_xs ^ {1,2,…, n }, const , stats )}

Alternatywnie możesz użyć funkcji TREND:

{=TREND( znane_ys , znane_xs ^ {1,2,…, n }, nowe_xs , const )}