Wzory linii trendu

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.

Opublikowano: 23 luty 2011, Kategorie: Wykresy , , , , ,
11 Odpowiedzi do Wzory linii trendu
  1. avatar

    Mam 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?

    Odpowiedz
    • avatar

      Tak 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.

    • avatar

      Dziękuję za szybką reakcję, tak rzeczywiście dane były w kolumnach, po wykonaniu transpozycji formuły zadziałały poprawnie.

  2. avatar

    Witam 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

    Odpowiedz
  3. avatar

    Bardzo 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.

    Odpowiedz
  4. avatar

    Wszystko działa, tylko dane muszą być jednak w kolumnach, a nie w rzędach.

    Odpowiedz
  5. avatar

    może być w kolumnach ale zamiast średnikach w tablicy należy użyć backslash.

    Odpowiedz
  6. avatar

    Jeż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.

    Odpowiedz
  7. avatar

    Mam 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…

    Odpowiedz

[na górę]

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Możesz użyć następujących tagów oraz atrybutów HTML-a: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">