LINEST (funcția LINEST)

Descriere

Funcția LINEST calculează statistica pentru o linie utilizând metoda celor mai mici pătrate pentru a calcula o linie dreaptă care descrie cel mai bine datele și returnează o matrice care descrie acea linie. De asemenea, aveți posibilitatea să combinați LINEST cu alte funcții, pentru a calcula statisticile pentru alte tipuri de modele care sunt liniare în parametri necunoscuți, inclusiv seriile polinomiale, logaritmice, exponențiale și de puteri. Deoarece această funcție returnează o matrice de valori, ea trebuie introdusă ca o formulă de matrice. Acest articol conține instrucțiuni împreună cu exemple.

Ecuația pentru linie este:

y = mx + b

- sau -

y = m1x1 + m2x2 + ... + b

dacă există mai multe zone de valori x, unde valorile dependente y sunt o funcție de valorile x independente. Valorile m sunt coeficienți corespunzători fiecărei valori x, iar b este o valoare constantă. De reținut că y, x și m pot fi vectori. Matricea pe care o întoarce funcția LINEST este {mn;mn-1;...;m1;b}. LINEST mai poate întoarce și statistici de regresie adiționale.

Sintaxă

LINEST(valori_y_cunoscute, [valori_x_cunoscute], [const], [statistici])

Sintaxa funcției LINEST are următoarele argumente:

Sintaxă

• valori_y_cunoscute    Obligatoriu. Este este setul de valori y pe care le cunoașteți deja din relația y = mx + b.

  • Dacă zona de valori_y_cunoscute este o singură coloană, fiecare coloană de valori_x_cunoscute se interpretează ca o variabilă separată.

  • Dacă zona de valori_y_cunoscute este conținută într-un singur rând, fiecare rând de valori_x_cunoscute se interpretează ca o variabilă separată.

• valori_x_cunoscute    Opțional. Este un set de valori x pe care este posibil să le cunoașteți deja din relația y = mx + b.

  • Matricea de valori_x_cunoscute poate conține unul sau mai multe seturi de variabile. Dacă este utilizată o singură variabilă, atunci valori_y_cunoscute și valori_x_cunoscute pot fi zone de orice formă, atât timp cât au dimensiuni egale. Dacă sunt utilizate mai multe variabile, atunci valori_y_cunoscute trebuie să fie un vector (adică o zonă cu înălțimea de un rând sau cu lățimea de o coloană).

  • Dacă valori_x_cunoscute este omis, se consideră a fi matricea {1;2;3;...} care este de aceeași mărime cu valori_y_cunoscute.

• const    Opțional. Este o valoare logică ce specifică dacă se impune valoarea 0 pentru constanta b.

  • Dacă const are valoarea TRUE sau se omite, b se calculează normal.

  • Dacă argumentul const este FALSE, b este setat la valoarea 0 și valorile m sunt ajustate pentru a respecta ecuația y = mx.

• stat    Opțional. Este o valoare logică ce specifică dacă să întoarcă statistica de regresie adițională.

  • Dacă statistică este TRUE, LINEST întoarce statistica de regresie suplimentară; prin urmare, matricea returnată este {mn;mn-1,...,m1;b; sen,sen-1,...,se1,seb; r^2,sey; F,df; ssreg,ssresid}.

  • Dacă stat este FALSE sau omis, LINEST întoarce numai coeficienții m și constanta b.

    Statisticile de regresie suplimentare sunt după cum urmează:

Figura următoare arată ordinea în care sunt returnate statisticile de regresie adiționale.

Observații

• Descrieți orice dreaptă cu ajutorul pantei și a intersecției cu axa y:

  Pantă (m):
  Pentru a găsi panta unei linii, adesea scrisă ca m, luați două puncte pe linie, (x1,y1) și (x2,y2); panta este egală cu (y2 - y1)/(x2 - x1).

  Intersecția cu axa Y (b):
  Intersecția cu axa y a unei linii, adesea scrisă ca b, este valoarea lui y în punctul în care linia intersectează axa y.

  Ecuația unei drepte este y = mx + b. Odată ce cunoașteți valorile pentru m și b, aveți posibilitatea să calculați orice punct al liniei înlocuind valorile x sau y în ecuație. La fel de bine se poate utiliza și funcția TREND.

• Când aveți o singură variabilă independentă x, puteți obține panta și intersecția cu axa y în mod direct, utilizând următoarele formule:

  Pantă:
  =INDEX(LINEST(known_y,known_x),1)

  Intersecția cu axa Y:
  =INDEX(LINEST(known_y,known_x),2)

• Acuratețea liniei calculate de funcția LINEST depinde de gradul de împrăștiere din datele dvs. Cu cât sunt mai liniare datele, cu atât modelul liniar LINEST va fi mai neted. LINEST utilizează metoda celor mai mici pătrate pentru a determina cea mai bună aproximare a datelor. Când aveți o singură variabilă independentă x, calculele pentru panta m și intersecția b se bazează pe următoarele formule:

  

  

  unde x și y sunt mediile pentru eșantioane, adică, x = AVERAGE(valori_x_cunoscute) și y = AVERAGE(valori_y_cunoscute).

• Funcțiile de aranjare a liniilor și curbelor LINEST și LOGEST pot calcula cea mai bună linie dreaptă sau curbă exponențială care se potrivește datelor dvs. Totuși, trebuie să decideți care dintre cele două rezultate corespund cel mai bine datelor dvs. Puteți calcula TREND(known_y,known_x) pentru o linie dreaptă sau GROWTH(known_y, known_x) pentru o curbă exponențială. Aceste funcții, fără argumentul new_x , returnează o matrice de valori y estimate de-a lungul acelei linii sau curbe la punctele de date reale. Apoi puteți compara valorile estimate cu valorile reale. Se recomandă să le creați o diagramă pentru o comparație vizuală.

• În analizele de regresie, Excel calculează pentru fiecare punct pătratul diferenței dintre valoarea y estimată în punctul respectiv și valoarea y actuală. Suma pătratelor acestor diferențe este denumită sumă reziduală de pătrate, ssresid. Excel calculează apoi suma totală pătratelor, sstotal. Când const = TRUE sau este omis, suma totală a pătratelor este suma pătratelor diferențelor dintre valorile y efective și media valorilor y. Când const = FALSE, suma totală a pătratelor este suma pătratelor valorilor y efective (fără scăderea mediei valorilor y din fiecare valoare y). Apoi, suma de regresie a pătratelor, ssreg, se poate afla din: ssreg = sstotal - ssresid. Cu cât este mai mică suma reziduală a pătratelor, comparativ cu suma totală a pătratelor, cu atât este mai mare valoarea coeficientului de determinare, r², care este un indicator al cât de bine explică relația dintre variabile ecuația rezultată din analiza de regresie. Valoarea r² este egală cu ssreg/sstotal.

• În unele cazuri, una sau mai multe dintre coloanele X (să presupunem că Y și X sunt în coloane) pot să nu aibă nicio valoare predictivă suplimentară în prezența celorlalte coloane X. Cu alte cuvinte, eliminarea unei coloane X sau a mai multor coloane poate duce la valori Y estimate la fel de precise. În acest caz, aceste coloane X redundante ar trebui omise din modelul de regresie. Acest fenomen se numește "coliniaritate", deoarece orice coloană X redundantă poate fi exprimată ca o sumă de multipli ai coloanelor X care nu sunt redundante. Funcția LINEST verifică colinearitatea și elimină toate coloanele X redundante din modelul de regresie atunci când le identifică. Coloanele X eliminate pot fi recunoscute în rezultatul LINEST ca având coeficienți 0 în plus față de valorile se 0. Dacă una sau mai multe coloane sunt eliminate ca redundante, df este afectat, deoarece df depinde de numărul de coloane X utilizate efectiv în scopuri predictive. Pentru detalii despre calculul df, consultați Exemplul 4. Dacă df este modificat, deoarece coloanele X redundante sunt eliminate, sunt afectate și valorile sey și F. Collinearity ar trebui să fie relativ rare în practică. Cu toate acestea, un caz în care este mai probabil să apară este atunci când unele coloane X conțin numai valorile 0 și 1 ca indicatori dacă un subiect dintr-un experiment este sau nu membru al unui anumit grup. Dacă const = TRUE sau este omis, funcția LINEST inserează efectiv o coloană X suplimentară cu toate cele 1 valori pentru a modela intersecția. Dacă aveți o coloană cu 1 pentru fiecare subiect dacă este masculin sau 0 dacă nu, și aveți și o coloană cu 1 pentru fiecare subiect dacă este feminin sau 0, această a doua coloană este redundantă, deoarece intrările din ea pot fi obținute din scăderea intrării din coloana "indicator masculin" din intrarea din coloana suplimentară a tuturor valorilor 1 adăugate de funcția LINEST .

• Valoarea df se calculează ca mai jos atunci când nicio coloană X nu este eliminată din model datorită coliniarității: dacă există k coloane de valori_x_cunoscute și const = TRUE sau este omis, atunci df = n – k – 1. În cazul în care const= FALSE, atunci df = n - k. În ambele cazuri, fiecare coloană X eliminată datorită coliniarității mărește df cu 1.

• Când introduceți o constantă matrice, (cum ar fi valori_x_cunoscute) ca argument, utilizați punct și virgulă (;) pentru separarea valorilor din același rând și bare verticale (|) pentru separarea rândurilor. Caracterele separatoare pot fi diferite, în funcție de setările regionale.

• De reținut că valorile y estimate de ecuația de regresie pot să nu fie valide dacă ele se situează în afara intervalului de valori y pe care l-ați utilizat pentru a determina ecuația.

• Algoritmul de bază utilizat în funcția LINEST diferă de algoritmul de bază din funcțiile SLOPE și INTERCEPT. Diferențele dintre acești algoritmi pot conduce la rezultate diferite când datele sunt nedeterminate și colineare. De exemplu, dacă punctele de date ale argumentului valori_y_cunoscute sunt 0 și punctele de date ale argumentului valori_x_cunoscute sunt 1:

  • LINEST returnează valoarea 0. Algoritmul funcției LINEST este proiectat să returneze valorile rezonabile pentru datele colineare și, în acest caz, se poate găsi cel puțin un răspuns.

  • SLOPE și INTERCEPT returnează o #DIV/0! eroare. Algoritmul funcțiilor SLOPE și INTERCEPT este proiectat să caute un singur răspuns, iar în acest caz pot exista mai multe răspunsuri.

• În plus față de utilizarea LOGEST pentru a calcula statistici pentru alte tipuri de regresii, aveți posibilitatea să utilizați LINEST pentru a calcula alte tipuri de regresii, introducând funcții ale variabilelor x și y ca serii x și y pentru LINEST. De exemplu, următoarea formulă:

  =LINEST(yvalori; xvalori^COLUMN($A:$C))

  funcționează atunci când aveți o singură coloană de valori y și o singură coloană de valori x pentru a calcula aproximarea cubică (polinomială de ordinul trei) a formulei:

  y = m1*x + m2*x^2 + m3*x^3 + b

  Aveți posibilitatea să reglați această formulă pentru a calcula alte tipuri de regresii, însă în unele cazuri este necesară reglarea valorilor de ieșire și a altor statistici.

• Valoarea F-test returnată de funcția LINEST diferă de valoarea F-test returnată de funcția LINEST. LINEST returnează statistica F, în timp ce FTEST returnează probabilitatea.

Exemple