Tworząc wykres w Excelu można dodać do niego linię trendu. Linia trendu pozwala na oszacowanie przyszłych wartości, które mogą zostać osiągnięte przez dane zjawisko. Może nie jest to dobre przybliżenie, ale jest to zawsze jakiś punkt odniesienia.
Dodatkowo na wykresie można pokazać wzór za pomocą, którego została wyznaczona linia trendu. Mając ten wzór możemy już samodzielnie wyznaczać wartości w przyszłości. Pewnym problemem jest fakt, że aby wyznaczyć wzór, należało zrobić wykres. Dodatkowo każdorazowa zmiana danych powoduje zmianę tego wzoru, co wiąże się ze zmianą formuły i ponownym jej wprowadzeniem.
Najlepszym rozwiązaniem byłoby zastosowanie formuły do obliczania tych wartości. Excel domyślnie nie udostępnia funkcji, za pomocą, których można wyznaczyć potrzebne parametry. Z pomocą przychodzi wiedza ze statystyki i prognozowania. Zakładając, że mamy dwa zakresy nazwane xi y możemy napisać następujące wzory:
- trend liniowy:
- wzór: y = m*x + b
- m: =NACHYLENIE(y;x)
- b: =ODCIĘTA(y;x)
- trend logarytmiczny:
- wzór: y = (c * LN(x)) + b
- c: =INDEKS(REGLINP(y; LN(x));1)
- b: =INDEKS(REGLINP(y; LN(x));1;2)
- trend wielomianowy stopnia drugiego:
- wzór: y = c2 * x^2 + c1 * x + b
- c2: =INDEKS(REGLINP(y; x^{1;2});1)
- c1: =INDEKS(REGLINP(y; x^{1;2});1;2)
- b: =INDEKS(REGLINP(y; x^{1;2});1;3)
- trend wielomianowy stopnia trzeciego:
- wzór: y = c3 * x^3 + c2 * x^2 + c1 * x + b
- c3: =INDEKS(REGLINP(y; x^{1;2;3});1)
- c2: =INDEKS(REGLINP(y; x^{1;2;3});1;2)
- c1: =INDEKS(REGLINP(y; x^{1;2;3});1;3)
- b: =INDEKS(REGLINP(y; x^{1;2;3});1;4)
- trend wielomianowy wyższych stopni – równania i parametry wyznacza się przez analogię,
- trend potęgowy:
- wzór: y = c * x ^ b
- c: =EXP(INDEKS(REGLINP(LN(y);LN(x);;);1;2))
- b: =INDEKS(REGLINP(LN(y);LN(x);;);1)
- trend wykładniczy:
- wzór: y = c * e ^ (b * x)
- c: =EXP(INDEKS(REGLINP(LN(y);x);1;2))
- b: =INDEKS(REGLINP(LN(y);x);1)
Teraz wykorzystując te wzory można przystąpić do szacowania wartości przyszłych bez rysowania wykresu.
Polecana książka
Podobne artykuły
Ostatnie komentarze
Michał Jankowski – To są parametry, które określają linię trendu. Można odczytaj je z wykresu, bądż też obliczyć... – Wzory linii trendu
Arek – Mam pytanie odnośnie linii trendu potęgowego: co oznaczają c i b ? wzór: y =... – Wzory linii trendu
and – Jak zmieniać pierwszą liczbę przy autowypełnianiu 1.1 2.1 3.1 4.1 5.1 itd – Opcje autowypełniania
mi – Pomogło, dziękuję
– Sumowanie wartości z błędamiAgnieszka – Super, bardzo pomocne, dziękuję! – Który to tydzień kalendarzowy?
Wojtek
06/12/2013 o 22:21Mam problem z :trend wielomianowy stopnia drugiego, stosując powyższą formułę (c2: =INDEKS(REGLINP( y; x^{1;2});1) itd.) excel wyrzuca błąd #ARG! nie chcą podać parametrów regresji. Program ma jakiś problem z indeksowaniem potęg zmiennej x. Sprawdziłem dane przez moduł analizy danych z opcją regresja i zbiór danych jest wyznaczalny. Czy było to testowane na Excel 2010?
Michał Jankowski
08/12/2013 o 22:21Tak funkcje te działają. Błąd #ARG! wynika ze złego układu danych.
Jestem prawie pewien, że dane są ułożone pionowo:
x y
1 2
3 4
…
Funkcje te wymagają układu poziomego:
x 1 3
y 2 4.
Wtedy na pewno zadziałają.
Jeśli nie chce Pan zmieniać układu swoich danych to należy użyć funkcji TRANSPONUJ.
I może jeszcze jedno. Należy pamiętać, że są to funkcje tablicowe – akceptuje je się naciskając Ctrl+Shift+Enter.
Wojtek
08/12/2013 o 22:23Dziękuję za szybką reakcję, tak rzeczywiście dane były w kolumnach, po wykonaniu transpozycji formuły zadziałały poprawnie.
zen
04/05/2014 o 23:14Witam i dziękuję za poruszenie tego tematu,
Mam pytanie a jednocześnie prośbę …
Czy możliwe jest umieszczenie tu przez Pana przykładowych plików z arkuszami excela . z danymi typowymi dla zobrazowania tematu ??
//bardzo by ucieszyło to mniej gramotnych do których i ja się zaliczam .//
Pozdrawiam
Michał Jankowski
05/05/2014 o 22:41Jasne. Tam gdzie sądzę, że dane są potrzebne to dorzucam pliki z przykładami.
Maciek
23/12/2014 o 12:18Bardzo pomocna informacja, jednak mam problem z zastosowaniem. Chciałem użyć wielomianu drugiego stopnia i niestety parametry c2, c1 i b wykazują błąd #ARG!. Wartości x i y są w wierszach więc pod tym względem jest OK.
Wartości jest po 11 więc jak zmieniłem formuły na c2: =INDEKS(REGLINP(y; x^{1;2;3;4;5;6;7;8;9;10;11});1) to dla c1 i c2 błędu już nie ma ale za to b cały czas zgłasza błąd.
Dodatkowo wartości c2 i c1 niejak się nie zgadzają z tym co jest pokazywane w funkcji na wykresie. Przykładowo c2 mi wyszło 0,699, a na wykresie jest -0,3497.
Przychylam się do sugestii @zen bo taki przykład w pliku znacznie ułatwiłby szukanie błędów we własnych arkuszach.
Maciek
23/12/2014 o 13:04Wszystko działa, tylko dane muszą być jednak w kolumnach, a nie w rzędach.
Karol
04/02/2015 o 15:26może być w kolumnach ale zamiast średnikach w tablicy należy użyć backslash.
Kosek
03/03/2015 o 09:40Jeżeli komuś nie działają backslash to może zrobić tak:
zamiast: =INDEKS(REGLINP(y; x^{1/2/3/4/5/6/7/8/9/10/11});1)
dać to: =INDEKS(REGLINP(y; x^transponuj({1;2;3;4;5;6;7;8;9;10;11}));1)
Wtedy nie trzeba zamieniać kolumny na wiersz.
Arek
31/05/2019 o 13:28Mam pytanie odnośnie linii trendu potęgowego: co oznaczają c i b ?
wzór: y = c * x ^ b
c: =EXP(INDEKS(REGLINP(LN(y);LN(x);;);1;2))
b: =INDEKS(REGLINP(LN(y);LN(x);;);1)
zaproponowany opis niewiele wyjaśnia…
Michał Jankowski
03/06/2019 o 19:19To są parametry, które określają linię trendu. Można odczytaj je z wykresu, bądż też obliczyć na podstawie przedstawionych wzorów.
Wyprowadzenie matematyczne można znaleźć w opracowaniach statystyczych – np. http://keii.ue.wroc.pl/przeglad/Rok%202009/Zeszyt%202/2009_56_2_052-066.pdf