LINEST
Funcția LINEST returnează o matrice a datelor statistice pentru o linie dreaptă care corespunde cel mai bine datelor specificate utilizând metoda celor mai mici pătrate.
LINEST(valori-y-cunoscute; valori-x-cunoscute; intersecție-y-nonzero; alte-statistici)
valori-y-cunoscute: O colecție care conține valorile y cunoscute. valori-y-cunoscute trebuie să conțină fie valori numerice, fie valori de dată/oră. Dacă există o singură colecție de valori x cunoscute, valori-y-cunoscute pot avea orice dimensiune. Dacă există mai multe colecții de valori x cunoscute, valori-y-cunoscute pot fi o coloană care conține valorile sau un rând care conține valorile, însă nu ambele.
valori-x-cunoscute: O colecție opțională care conține valorile x cunoscute. valori-x-cunoscute trebuie să conțină fie valori numerice, fie valori de dată/oră. Dacă este omisă, se presupune că este un set de aceeași mărime ca valori-y-cunoscute începând cu 1 - de exemplu, 1, 2, 3 dacă sunt trei valori-y-cunoscute. Dacă există un singur set de valori x cunoscute, valori-x-cunoscute, dacă sunt specificate, ar trebui să aibă aceeași mărime cu valori-y-cunoscute. Dacă există mai multe seturi de valori x cunoscute, fiecare rând/coloană de valori-x-cunoscute se consideră a fi un set, iar dimensiunea fiecărui rând/fiecărei coloane trebuie să fie aceeași cu dimensiunea rândului/coloanei valori-y-cunoscute.
intersecție-y-nonzero: O valoare modală opțională care specifică modul în care ar trebui să fie calculată intersecția cu axa y (constanta b).
normal (1, TRUE, sau omis): Valoarea intersecției cu axa y (constanta b) ar trebui să fie calculată normal.
forțare valoare 0 (0, FALSE): Valoarea intersecției cu axa y (constanta b) ar trebui să fie forțată să fie 0.
alte-statistici: O valoare modală opțională care specifică dacă ar trebui returnate informații statistice suplimentare.
fără statistici suplimentare (0, FALSE, sau omis): Nu se returnează statistici de regresie suplimentare în matricea returnată.
statistici suplimentare (1, TRUE): Returnează statistici de regresie suplimentare în matricea returnată.
Note
Valorile returnate de funcție sunt conținute într-o matrice. O metodă de citire a valorilor din matrice este utilizarea funcției INDEX. Puteți introduce funcția LINEST în funcția INDEX: =INDEX(LINEST(valori-y-cunoscute; valori-x-cunoscute; intersecție-y; alte-statistici), y, x) unde y și x reprezintă indexul de coloană și de rând ale valorii dorite.
Dacă nu sunt returnate statistici suplimentare (alte-statistici are valoarea FALSE), matricea returnată are un rând. Numărul de coloane este egal cu numărul de seturi de valori-x-cunoscute plus 1. Acesta conține pantele curbei (câte o valoare pentru fiecare rând/coloană de valori x) în ordine inversă (prima valoare este asociată cu ultimul rând/ultima coloană de valori x) și apoi valoarea pentru b, intersecția.
Dacă sunt returnate statistici suplimentare (alte-statistici are valoarea TRUE), matricea returnată conține cinci rânduri. Găsiți informații suplimentare despre această matrice imediat după exemple.
Exemple |
|---|
Fiind dat următorul tabel de valori-x-cunoscute (celulele A2:A6) și valori-y-cunoscute (celulele B2:B6): |
A | B | |
|---|---|---|
1 | X | Y |
2 | 0 | -1 |
3 | 8 | 10 |
4 | 9 | 12 |
5 | 4 | 5 |
6 | 1 | 3 |
=INDEX(LINEST(A2:A6; B2:B6; 1; 0); 1) returnează aproximativ 0,752707581227437, fiind dată o valoare normală (1) pentru intersecție-y-non-zero. Aceasta este curba celei mai bune aproximări liniare, deoarece am specificat că dorim prima valoare din matrice returnată de INDEX și am specificat doar un set de valori-x-cunoscute. =INDEX(LINEST(A2:A6; B2:B6; 1; 0); 2) returnează aproximativ 0,0342960288808646, care este b, intersecția pentru dreapta celei mai bune aproximări liniare. Intersecția a fost returnată deoarece am specificat că dorim a doua valoarea din matrice returnată de INDEX, care ar fi cea de-a doua valoare, deoarece am specificat doar un set de valori-x-cunoscute. |
Conținutul matricei de statistici suplimentare
LINEST poate include informații statistice suplimentare în matricea returnată de funcție. În scopul următoarei discuții, presupunem că există cinci seturi de valori x cunoscute, în plus față de valorile y cunoscute. În continuare, presupunem că valorile-x-cunoscute sunt în cinci rânduri de tabel sau în cinci coloane de tabel. Prin urmare, matricea returnată de LINEST ar conține următoarele valori.
1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
1 | S5 | S4 | S3 | S2 | S1 | b |
2 | SE5 | SE4 | SE3 | SE2 | SE1 | SEb |
3 | C | SEy | ||||
4 | F | DF | ||||
5 | R1 | R2 |
Rândul 1, coloana 1 conține S5 (panta pentru cel de-al cincilea set de valori-x-cunoscute) continuând până la coloana 5, care va conține S1 (panta pentru primul set de valori-x-cunoscute). Rețineți că pantele asociate fiecăruia dintre seturile de valori-x-cunoscute sunt returnate în ordine inversă.
Ultima celulă din rândul 1 conține b, intersecția cu axa y pentru valorile x cunoscute. În exemplul nostru, aceasta ar fi rândul 1 coloana 6.
Rândul 2, coloana 1 conține SE5 (eroarea standard pentru coeficientul asociat cu cel de-al cincilea set de valori-x-cunoscute) continuând până la coloana 5, care va conține SE1 (coeficientul de eroare standard pentru primul set de valori-x-cunoscute). Aceste valori sunt returnate în ordine inversă; adică, dacă există cinci seturi de valori x cunoscute, valoarea pentru al cincilea set este returnată prima în matrice. În același mod sunt returnate și valorile pantei.
Ultima celulă din rândul 2 conține SEb, eroarea standard asociată cu valoarea intersecție-y (b). În exemplul nostru, aceasta ar fi rândul 2 coloana 6.
Rândul 3, coloana 1 conține C, coeficientul determinării. Această statistică compară valorile estimate și cele efective. Dacă acesta este 1, nu există nicio diferență între valoarea estimată y și valoarea efectivă y. Aceasta se numește o corelație perfectă. În cazul în care coeficientul determinării este 0, nu există nicio corelație, iar ecuația de regresie dată nu este utilă pentru prognozarea unei valori y.
Rândul 3, coloana 2 conține SEy, eroarea standard asociată cu estimarea valorii y.
Rândul 4, coloana 1 conține F, valoarea F observată. Valoarea F observată poate fi utilizată pentru a putea determina dacă relația observată dintre variabila dependentă și cea independentă este o coincidență.
Rândul 4, coloana 2 conține DF, gradele de libertate. Utilizați statistica gradelor de libertate pentru a putea determina un nivel de încredere.
Rândul 5, coloana 1 conține R1, suma de regresie a pătratelor.
Rândul 5, coloana 2 conține R2, suma reziduală a pătratelor.
Urmează câteva informații de reținut în legătură cu matricea de statistici suplimentare:
Nu contează dacă valorile x cunoscute și valorile y cunoscute sunt în rânduri sau în coloane. În ambele cazuri, matricea returnată este ordonată în rânduri, după cum se poate observa în tabel.
Exemplul presupune cinci seturi de valori x cunoscute. Dacă ar exista mai mult sau mai puțin de cinci, numărul de coloane din matricea returnată s-ar va schimba corespunzător (acesta este egal întotdeauna cu numărul de seturi de valori x cunoscute plus 1), însă numărul de rânduri ar rămâne constant.
Dacă nu sunt specificate statistici suplimentare în argumentele funcției LINEST, matricea returnată este egală doar cu primul rând.