LINEST Calculeaza statistica pentru o linie utilizand metoda celor mai mici patrate

LINEST   Calculeaza statistica pentru o linie utilizand metoda celor mai mici patrate pentru a calcula o linie dreapta care descrie cel mai bine datele si intoarce o matrice care descrie acea linie. Deoarece aceasta functie intoarce o matrice de valori, ea trebuie introdusa ca o formula de matrice.

Ecuatia pentru linie este:

y = mx + b sau

y = m1x1 + m2x2 + + b (daca sunt mai multe intervale de valori x)

unde valoarea dependenta y este o functie de valorile x independente. Valorile m sunt coeficienti corespunzatori fiecarei valori x, iar b este o valoare constanta. De retinut ca y, x si m pot fi vectori. Matricea pe care o intoarce functia LINEST este . LINEST mai poate intoarce si statistici de regresie aditionale.

Sintaxa : LINEST(known_y's;known_x's;const;stats)

Y_cunoscut   este setul de valori y pe care le cunoasteti deja din relatia y = mx + b.

Daca matricea y_cunoscut este intr-o singura coloana, atunci fiecare coloana de valori x din x_cunoscut este interpretata ca o variabila separata.

Daca matricea y_cunoscut este intr-un singur rand, atunci fiecare rand de valori din x_cunoscut este interpretat ca o variabila separata.

X_cunoscut   este un set optional de valori x pe care le-ati putea cunoaste deja din relatia y = mx + b.

Matricea x_cunoscut poate contine unul sau mai multe seturi de variabile. Daca este utilizata o singura variabila, y_cunoscut si x_cunoscut pot fi zone de orice marime, atat timp cat au dimensiuni egale. Daca sunt utilizate mai multe variabile, y_cunoscut trebuie sa fie un vector (adica o zona cu inaltimea de un rand sau cu latimea de o coloana).

Daca x_cunoscut este omis, se considera a fi matricea care este de aceeasi marime cu y_cunoscut.

Const   este o valoare logica ce specifica daca constanta b va fi fortata la valoarea 0.

Daca const este TRUE sau omisa, b este calculat normal.

Daca argumentul const este FALSE, b este setat la valoarea 0 si valorile m sunt ajustate pentru a respecta ecuatia y = mx.

Stats   este o valoare logica ce specifica daca sa intoarca statistica de regresie aditionala.

Daca stats este TRUE, LINEST intoarce statisticile de regresie aditionale, astfel incat matricea returnata este .

Daca stats este FALSE sau omis, LINEST intoarce numai coeficientii m si constanta b.

Statisticile de regresie suplimentare sunt dupa cum urmeaza:

Figura urmatoare arata ordinea in care sunt returnate statisticile de regresie aditionale.

Observatii

Descrieti orice dreapta cu ajutorul pantei si a intersectiei cu axa y:

Panta (m):
Pentru a gasi panta unei linii, deseori scrisa ca m, luati doua valori de pe linie, (x1;y1) si (x2;y2); panta este egala cu (y2 - y1)/(x2 - x1).

Intersectia cu axa Y (b):

Intersectia cu axa Y a unei linii, deseori scrisa ca b, este valoarea lui y in punctul in care linia traverseaza axa Y.

Ecuatia unei drepte este y = mx + b. Odata ce cunoasteti valorile pentru m si b, puteti calcula orice punct al liniei inlocuind valorile x sau y in ecuatie. La fel de bine puteti utiliza functia TREND.

Cand aveti o singura variabila independenta x, puteti obtine panta si intersectia cu axa y in mod direct, utilizand urmatoarele formule:

Panta : INDEX(LINEST(y_cunoscut;x_cunoscut);1)

Intersectia cu axa Y: =INDEX(LINEST(y_cunoscut;x_cunoscut);2)

Acuratetea liniei calculate de functia LINEST depinde de gradul de imprastiere din datele dvs. Cu cat sunt mai liniare datele, cu atat modelul liniar LINEST va fi mai neted. LINEST utilizeaza metoda celor mai mici patrate pentru a determina cea mai buna aproximare a datelor. Cand aveti o singura variabila independenta x, calculele pentru panta m si intersectia b se bazeaza pe urmatoarele formule: si

Aproximarile prin drepte sau curbe exponentiale date de functiile LINEST si LOGEST, respectiv, pot calcula cele mai potrivite drepte sau exponentiale pentru datele dvs. Oricum, trebuie sa decideti care dintre cele doua rezultate aproximeaza mai bine. Aveti posibilitatea sa calculati tendinta TREND(y_cunoscut;x_cunoscut) pentru o linie dreapta sau cresterea GROWTH(y_cunoscut; x_cunoscut) pentru o curba exponentiala. Aceste functii, fara argumentul x_nou, intorc o matrice de valori y estimate de-a lungul dreptei sau exponentialei reperelor de date actuale. Comparati apoi valorile estimate cu cele actuale. Apoi faceti diagrame pentru o comparare vizuala.

In analizele de regresie, Microsoft Excel calculeaza pentru fiecare punct patratul diferentei dintre valoarea y estimata in punctul respectiv si valoarea y actuala. Suma patratelor acestor diferente este denumita suma de patrate reziduala. Microsoft Excel calculeaza apoi suma patratelor diferentelor dintre valorile actuale y si mediile valorilor y, denumita suma de patrate totala (suma de patrate de regresie + suma de patrate reziduala). Cu cat este mai mica suma de patrate reziduala, in comparatie cu suma de patrate totala, cu atat este mai mare valoarea coeficientului de determinare, r2, care este un indicator pentru cat de bine este explicata relatia dintre variabile de catre ecuatia rezultata din analiza de regresie.

Formulele care returneaza matrice trebuie introduse ca formule matrice.

Cand introduceti o constanta matrice, cum ar fi x_cunoscut ca argument, utilizati punct si virgula (;) pentru separarea valorilor din acelasi rand si bare verticale (|) pentru separarea randurilor. Caracterele utilizate ca separatori pot diferi in functie de setarile locale din Regional Settings sau Regional Options din Control Panel.

De retinut ca valorile y estimate de ecuatia de regresie pot sa nu fie valide daca ele se situeaza in afara intervalului de valori y pe care l-ati utilizat pentru a determina ecuatia.

Exemplul 1   Panta si intersectia cu axa Y

A B 1 y cunoscut x cunoscut 2 1 0 3 9 4 4 5 2 5 7 3 Formula Formula =LINEST(A2:A5;B2:B5;;FALSE)

Nota   Formula din exemplu trebuie introdusa ca o formula matrice. Dupa copierea exemplului intr-o foaie de lucru goala, selectati zona A7:B7 incepand cu celula in care se afla formula. Apasati F2, apoi apasati CTRL+SHIFT+ENTER. Daca formula nu este introdusa ca o formula de matrice, unicul rezultat este 2.

Cand este introdusa ca o matrice, sunt intoarse panta (2) si punctul de intersectie cu axa Y (1).

Exemplul 2   Regresie liniara simpla

A B 1 Luna Vanzari 2 1 3100 3 2 4500 4 3 4400 5 4 5400 6 5 7500 7 6 8100 Formula Descriere (Rezultat) =SUM(LINEST(B2:B7; A2:A7)*) Estimeaza vanzarile pentru luna a noua (11000)

In general, SUM(*) egal mx + b, valoarea y estimata pentru o valoare x data. De asemenea, se poate utiliza functia TREND.

Exemplul 3   Regresie liniara multipla

Sa presupunem ca un comerciant se gandeste sa achizitioneze un grup de mici cladiri pentru birouri intr-un cartier organizat pentru afaceri.

Comerciantul poate utiliza analiza de regresie liniara multipla pentru a estima valoarea unei cladiri de birouri dintr-o zona anumita pe baza urmatoarelor variabile.

In acest exemplu se considera ca exista o relatie liniara intre fiecare variabila independenta (x1, x2, x3 si x4) si variabila dependenta (y), valoarea cladirilor din zona.

Investitorul alege la intamplare un esantion de 11 cladiri cu birouri dintr-un total posibil de 1.500 de astfel de cladiri si obtine urmatoarele date. "Jumatate de intrare" inseamna doar o intrare pentru marfuri.

A B C D E 1 Suprafata (x1) Birouri (x2) Intrari (x3) Varsta (x4) Valoare estimata (y) 2 2310 2 2 20 142.000 3 2333 2 2 12 144.000 4 2356 3 1,5 33 151.000 5 2379 3 2 43 150.000 6 2402 2 3 53 139.000 7 2425 4 2 23 169.000 8 2448 2 1,5 99 126.000 9 2471 2 2 34 142.900 10 2494 3 3 23 163.000 11 2517 4 4 55 169.000 12 2540 2 3 22 149.000 Formula =LINEST(E2:E12;A2:D12;TRUE;TRUE)

Nota   Formula din exemplu trebuie introdusa ca formula matrice. Dupa copierea exemplului intr-o foaie de lucru goala, selectati zona A14:E18 incepand cu celula in care se afla formula. Apasati F2, apoi apasati CTRL+SHIFT+ENTER. Daca formula nu este introdusa ca formula matrice, singurul rezultat este -234,2371645.

Cand este introdusa ca matrice, sunt intoarse urmatoarele statistici de regresie. Utilizati aceasta tasta pentru a identifica statistica dorita.

Ecuatia regresiei multiple, y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b, poate fi obtinuta in acest moment utilizand valorile din randul 14:

y = 27 *x1 + 12.530*x2 + 2.553*x3+ 234,24*x4 + 52.318

Comerciantul poate estima in acest moment valoarea stabilita pentru o cladire cu birouri din aceeasi zona care are 2.500 picioare patrate, trei birouri, doua intrari si varsta de 25 de ani, utilizand ecuatia urmatoare:

y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158.261 LEI

O alternativa este copierea tabelului urmator in celula A21 a registrului de lucru exemplu.

Se poate utiliza si functia TREND pentru a calcula aceasta valoare.

Exemplul 4   Utilizarea statisticilor F si R2

In exemplul anterior, coeficientul de determinare sau r2, este 0,99675 (vezi celula A17 din raspunsul functiei LINEST), care indica o relatie foarte stransa intre variabilele independente si pretul de vanzare. Puteti utiliza statistica F pentru a determina daca aceste rezultate, cu un coeficient r2 atat de mare, au aparut dintr-o intamplare.

Presupuneti pentru moment ca de fapt nu exista nici o relatie intre variabile si ca relatia stransa demonstrata de analiza statistica se bazeaza pe faptul ca ati ales un esantion norocos de 11 cladiri. Termenul "Alfa" este utilizat pentru probabilitatea de a trage concluzia eronata ca ar exista o relatie.

Exista o relatie intre variabile daca statistica observata F este mai mare decat valoarea critica F. Valoarea critica F poate fi obtinuta din tabelele de valori critice F din literatura de specialitate. Pentru a interpreta un tabel, presupuneti un test uni-alternativa, utilizati o valoare Alfa de 0,05, iar pentru gradele de libertate (abreviate in majoritatea tabelelor cu v1 si v2), utilizati v1 = k = 4 si v2 = n - (k + 1) = 11 - (4 + 1) = 6, unde k este numarul variabilelor din analiza de regresie si n este numarul reperelor de date. Valoarea critica F este 4,53.

Valoarea F observata este 459,753674 (celula A18), care este substantial mai mare decat valoarea critica F de 4,53. De aceea, ecuatia de regresie este utila pentru aprecierea valorilor estimate pentru cladirile de birouri din zona respectiva.

Exemplul 5   Calculul statisticii T

Un alt test ipotetic va determina daca fiecare coeficient al pantei este utilizator la estimarea valorii unei cladiri de birouri de la exemplul 3. De exemplu, pentru a testa importanta statistica a coeficientului de varsta, impartiti -234,24 (coeficientul pantei varstei) la 13,268 (eroarea standard estimata pentru coeficientii de varsta din celula A15). Mai jos este calculata valoarea observata t:

t = m4 se4 = -234,24 13,268 = -17,7

Daca consultati un tabel dintr-un manual de statistica, veti gasi ca t critic, uni-alternativa, cu 6 grade de libertate si Alfa = 0,05 este 1,94. Deoarece valoarea absoluta a lui t, 17,7, este mai mare decat 1,94, varsta reprezinta o variabila importanta pentru estimarea valorii stabilite pentru o cladire de birouri. Fiecare dintre celelalte variabile independente poate fi testata pentru semnificatia sa statistica in mod asemanator. In continuare se dau valorile t observate pentru fiecare variabila independenta.

Aceste variabile au toate valori absolute mai mari decat 1,94; in consecinta, toate variabilele utilizate in ecuatia de regresie sunt utile pentru estimarea valorii stabilite pentru cladirile de birouri din acea zona.