LINEST (funcția LINEST)

Acest articol descrie sintaxa de formulă și utilizarea funcției LINEST în Microsoft Excel. Găsiți linkuri către mai multe informații despre crearea de diagrame și efectuarea unei analize de regresie în secțiunea Consultați și.

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ă care specifică dacă să întoarcă statistica de regresie adițională.

    • Dacă stat este TRUE, LINEST întoarce statisticile de regresie suplimentare, astfel încât matricea returnată este {mn;mn-1;...;m1;b|sen;sen-1;...;se1;seb|r2;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ă:

Statistica

Descriere

se1,se2,...,sen

Valorile de eroare standard pentru coeficienții m1,m2,...,mn.

seb

Valoarea de eroare standard pentru constanta b (seb = #N/A când const este FALSE).

r2

Coeficientul de determinare. Compară valorile y estimate și actuale și este cuprins în intervalul de la 0 la 1. Dacă este 1, există o corelație perfectă în eșantion (nu există nicio diferență între valorile y estimate și cele actuale). La cealaltă extremă, în cazul în care coeficientul de determinare este 0, ecuația regresiei nu ajută la estimarea unei valori y. Pentru informații despre modul de calcul al r2, vedeți „Observații” mai departe la acest subiect.

sey

Eroarea standard pentru y estimat.

V

Statistica F sau valoarea F observată. Utilizați statistica F pentru a determina dacă relația observată între variabilele dependente și independente are loc din întâmplare.

df

Gradele de libertate. Utilizați gradele de libertate pentru a găsi valorile critice F dintr-un tabel statistic. Comparați valorile găsite în tabel cu statistica F returnată de funcția LINEST pentru a determina nivelul de încredere pentru model. Pentru informații despre cum se calculează df, consultați „Observații” mai departe în acest capitol. Exemplul 4de mai jos ilustrează cum se utilizează F și df.

ssreg

Suma de regresie a pătratelor.

ssresid

Suma reziduală a pătratelor. Pentru informații despre cum se calculează ssreg și ssresid, consultați „Observații" din acest articol.

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

O cheie a statisticilor de regresie

Observații

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

    Panta (m):
    Pentru a găsi panta unei linii, deseori scrisă ca m, luați două valori de 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, deseori 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:

    Panta:
    INDEX(LINEST(cunoscute_y;cunoscute_x);1)

    Intersecția cu axa Y:
    =INDEX(LINEST(cunoscute_y;cunoscute_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:

    Ecuație

    Ecuație

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

  • Aproximările prin drepte sau curbe exponențiale date de funcțiile LINEST și LOGEST, pot calcula cele mai potrivite drepte sau exponențiale pentru datele dvs. Însă, trebuie să decideți care dintre cele două rezultate aproximează mai bine. Aveți posibilitatea să calculați tendința TREND(valori_y_cunoscute;valori_x_cunoscute) pentru o linie dreaptă sau creșterea GROWTH(valori_y_cunoscute; valori_x_cunoscute) pentru o curbă exponențială. Aceste funcții, fără argumentul valori_x_noi, returnează o matrice de valori y estimate de-a lungul dreptei sau exponențialei reperelor de date actuale. Comparați apoi valorile estimate cu cele actuale. Apoi faceți diagrame pentru o comparare 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 în comparație cu suma totală a pătratelor, cu atât este mai mare valoarea coeficientului de determinare, r2, care este un indicator pentru cât de bine este explicată relația dintre variabile de către ecuația rezultată din analiza de regresie. Valoarea r2 este egală cu ssreg/sstotal.

  • În unele cazuri, una sau mai multe coloane X (presupunem că Y și X sunt în coloane) pot să nu aibă valoare predictivă suplimentară în prezența altor coloane X. Cu alte cuvinte, eliminarea uneia sau mai multor coloane X poate conduce la valori predictive Y egal de precise. În acest caz, aceste coloane X redundante se pot omite din modelul de regresie. Acest fenomen este denumit „colinearitate” deoarece orice coloană X redundantă poate fi exprimată ca sumă a mai multor coloane X neredundante. LINEST verifică colinearitatea și elimină toate coloanele X redundante din modelul de regresie, când le identifică. Coloanele X eliminate pot fi recunoscute în răspunsul LINEST, având coeficienții 0 pe lângă valorile 0. Dacă una sau mai multe coloane sunt eliminate ca redundante, atunci df este afectat, deoarece df depinde de numărul de coloane X utilizate efectiv în scopuri de predicție. Pentru detalii despre calculul lui df, consultați Exemplul 4 de mai jos. Dacă df se modifică datorită coloanelor X redundante eliminate, valorile sey și F sunt, de asemenea, afectate. Colinearitatea este relativ rară în practică. Însă, una dintre situațiile unde este cel mai posibil să apară este atunci când unele coloane X conțin numai valori 0 și 1 ca indicatori ai prezenței sau absenței unui subiect al unui experiment dintr-un grup anume. Dacă const = TRUE sau este omis, funcția LINEST inserează o coloană X suplimentară cu valori 1 pentru a modela intersecția. Dacă aveți o coloană cu un 1 pentru fiecare subiect dacă este bărbat sau 0 dacă nu este și aveți o altă coloană cu 1 pentru fiecare subiect care este femeie sau 0 dacă nu este, această ultimă coloană este redundantă deoarece intrările din ea pot fi obținute prin scăderea intrării din coloana „indicator masculin” din coloana suplimentară cu valori 1 adăugată 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.

  • Formulele care returnează matrice trebuie introduse ca formule matrice.

    Notă :  În Excel Online nu puteți crea formule de matrice.

  • 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 eroare #DIV/0!. Algoritmii funcțiilor SLOPE și INTERCEPT sunt proiectați pentru a căuta un singur răspuns și numai unul, iar în acest caz pot fi 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

Exemplul 1  - panta și intersecția cu axa Y

Copiați datele din exemplele din următorul tabel și lipiți-le în celula A1 a noii foi de lucru Excel. Pentru ca formulele să afișeze rezultate, selectați-le, apăsați pe F2, apoi pe Enter. Dacă trebuie, puteți ajusta lățimea coloanei pentru a vedea toate datele.

Y cunoscut

X cunoscut

1

0

9

4

5

2

7

3

Rezultat (pantă)

Rezultat (intersecția cu axa y)

2

1

Formulă (formulă matrice în celulele A7:B7)

=LINEST(A2:A5;B2:B5;;FALSE)

Exemplul 2 - Regresie liniară simplă

Copiați datele din exemplele din următorul tabel și lipiți-le în celula A1 a noii foi de lucru Excel. Pentru ca formulele să afișeze rezultate, selectați-le, apăsați pe F2, apoi pe Enter. Dacă trebuie, puteți ajusta lățimea coloanei pentru a vedea toate datele.

Lună

Vânzări

1

3.100 lei

2

4.500 lei

3

4.400 lei

4

5.400 lei

5

7.500 lei

6

8.100 lei

Formulă

Rezultat

=SUM(LINEST(B1:B6; A1:A6)*{9;1})

11.000 lei

Calculează estimarea vânzărilor pentru luna a noua, pe baza vânzărilor din lunile de la 1 la 6.

Exemplul 3 - Regresie liniară multiplă

Copiați datele din exemplele din următorul tabel și lipiți-le în celula A1 a noii foi de lucru Excel. Pentru ca formulele să afișeze rezultate, selectați-le, apăsați pe F2, apoi pe Enter. Dacă trebuie, puteți ajusta lățimea coloanei pentru a vedea toate datele.

Suprafață (x1)

Birouri (x2)

Intrări (x3)

Vârsta (x4)

Valoare estimată (y)

2310

2

2

20

142.000 lei

2333

2

2

12

144.000 lei

2356

3

1,5

33

151.000 lei

2379

3

2

43

150.000 lei

2402

2

3

53

139.000 lei

2425

4

2

23

169.000 lei

2448

2

1,5

99

126.000 lei

2471

2

2

34

142.900 lei

2494

3

3

23

163.000 lei

2517

4

4

55

169.000 lei

2540

2

3

22

149.000 lei

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Formulă (formulă matrice introdusă în celulele A14:A18)

=LINEST(E2:E12;A2:D12;TRUE;TRUE)

Exemplul 4 - Utilizarea statisticilor F și r2

În exemplul anterior, coeficientul de determinare sau r2, este 0,99675 (vedeți celula A17 din răspunsul funcției LINEST), care indică o relație foarte strânsă între variabilele independente și prețul de vânzare. Aveți posibilitatea să utilizați statistica F pentru a determina dacă aceste rezultate, cu un coeficient r2 atât de mare, au apărut din întâmplare.

Presupuneți pentru moment că de fapt nu există nicio relație între variabile și că relația strânsă demonstrată de analiza statistică se bazează pe faptul că ați ales un eșantion norocos de 11 clădiri. Termenul „Alfa” este utilizat pentru probabilitatea de a trage concluzia eronată că ar exista o relație.

Valorile F și df din răspunsul LINEST se pot utiliza ca estimări ale probabilității ca o valoare F mai mare să apară întâmplător. F poate fi comparat cu valorile critice din tabele de repartiție F sau se poate utiliza funcția FDIST din Excel și pentru a calcula probabilitatea apariției întâmplătoare a unei valori F mai mari. Repartiția F adecvată are v1 și v2 grade de libertate. Dacă n este numărul reperelor de date și const = TRUE sau este omis, atunci v1 = n – df – 1 și v2 = df. (Dacă const = FALSE, atunci v1 = n – df și v2 = df.) Funcția Funcția FDIST, cu sintaxa FDIST (F;v1;v2),  va returna probabilitatea apariției întâmplătoare a unei valori F mai mari. În acest exemplu, df = 6 (celula B18) și F = 459,753674 (celula A18).

Presupunând o valoare Alfa de 0,05, v1 = 11 – 6 – 1 = 4 și v2 = 6, nivelul critic al lui F este 4,53. Cum F = 459,753674 este mult mai mare decât 4,53, este extrem de puțin probabil ca o valoare F mare să apară întâmplător. (Cu Alfa = 0,05, ipoteza că nu există nicio relație între valori_y_cunoscute și valori_x_cunoscute este infirmată când F depășește nivelul critic, 4,53.) Aveți posibilitatea să utilizați funcția FDIST din Excel pentru a obține probabilitatea ca o valoare F atât de mare să apară întâmplător. De exemplu, FDIST(459,753674; 4; 6) = 1,37E-7, o probabilitate extrem de mică. Se poate concluziona, fie căutând nivelul critic al lui F într-un tabel, fie utilizând FDIST, că ecuația de regresie este utilă pentru predicția valorii estimate a clădirilor cu birouri din zonă. Este important să se utilizeze valorile corecte ale lui v1 și v2 calculate în paragraful anterior.

Exemplul 5 - Calculul statisticii t

Un alt test ipotetic va determina dacă fiecare coeficient al pantei este util la estimarea valorii unei clădiri de birouri de la Exemplul 3. De exemplu, pentru a testa importanța statistică a coeficientului de vârstă, împărțiți -234,24 (coeficientul pantei vârstei) la 13,268 (eroarea standard estimată pentru coeficienții de vârstă din celula A15). Mai jos este calculată valoarea observată t:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

Dacă valoarea absolută a lui t este suficient de mare, se poate concluziona că coeficientul pantei este util pentru estimarea valorii unei clădiri de birouri din Exemplul 3. Tabelul de mai jos arată valorile absolute ale celor 4 valori t observate.

Dacă consultați un tabel dintr-un manual de statistică, veți găsi că t critic, bi-alternativă, cu 6 grade de libertate și Alfa = 0,05 este 2,447. Această valoare critică poate fi de asemenea găsită utilizând funcția TINV din Excel. TINV(0.05;6) = 2,447. Deoarece valoarea absolută a lui t (17,7) este mai mare decât 2,447, vârsta reprezintă o variabilă importantă pentru estimarea valorii stabilite pentru o clădire de birouri. Fiecare dintre celelalte variabile independente poate fi testată pentru semnificația sa statistică în mod asemănător. În continuare se dau valorile t observate pentru fiecare variabilă independentă.

Variabilă

valoarea t observată

Suprafața utilă

5,1

Număr de birouri

31,3

Număr de intrări

4,8

Vârsta

17,7

Aceste variabile au toate valori absolute mai mari decât 2,447; în consecință, toate variabilele utilizate în ecuația de regresie sunt utile pentru estimarea valorii stabilite pentru clădirile de birouri din acea zonă.

Extindeți-vă competențele
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×