Funcția LINEST Excel - Statistici de regresie liniară

Descărcați Exemplu de registru de lucru

Descărcați exemplul de registru de lucru

Acest tutorial arată cum să utilizați Funcția Excel LINEST în Excel pentru a calcula statistici despre un trendline.

Prezentare generală a funcției LINEST

Funcția LINEST Calculează statistici despre o linie de tendință adaptată punctelor de date cunoscute utilizând metoda celor mai mici pătrate.

Pentru a utiliza funcția Foaie de lucru LINEST Excel, selectați o celulă și tastați:

(Observați cum apar datele introduse de formulă)

Funcția LINEST Sintaxă și intrări

1	= LINEST (cunoscut_y, cunoscut_xs, const, statistici)

cunoscut_y’s - O serie de valori Y cunoscute.

cunoscut_x - O serie de valori X cunoscute.

const - OPȚIONAL. Valoare logică care indică dacă se calculează B (intercepția în y = mx + b) folosind metoda celor mai mici pătrate (ADEVĂRAT sau Omit) sau dacă se setează manual B = 0 (FALS).

statistici - OPȚIONAL. Returnează statistici suplimentare (TRUE) sau returnează doar m (panta) și b (interceptare) (FALSE sau Omise)

Ce este LINEST?

Funcția LINEST din Excel este o funcție utilizată pentru a genera statistici de regresie pentru un model de regresie liniară. LINEST este o formulă matrice și poate fi utilizat singur sau cu alte funcții pentru a calcula statistici specifice despre model.

Regresia liniară este o metodă în statistici utilizată pentru prezicerea datelor urmând o linie dreaptă folosind date cunoscute. Regresia este utilizată pentru a prezice valori precum creșterea vânzărilor, cerințele de stoc sau tendințele simple ale pieței.

LINEST este ca FORECAST prin faptul că obține un rezultat similar, dar cu mult mai multe informații despre modelul de regresie, precum și opțiunea de a se potrivi mai mult de o variabilă independentă.

Să presupunem că am un tabel de date cu X și y valori unde X este variabila independentă și y este variabila dependentă:

Vreau să știu care este ecuația de regresie a datelor de mai sus. Folosind LINEST:

1	= LINEST (B3: B7, C3: C7, ADEVĂRAT, FALS)

Valoarea interceptării y aici este echivalentă cu 0, în notația științifică.

Ecuația liniei este y = 2x + 0. Rețineți că LINEST revine ambii panta și interceptarea liniei. Pentru a returna ambele valori, formula trebuie introdusă ca formulă matrice. Mai multe despre formule matrice mai târziu.

Cum se utilizează LINEST

Funcția LINEST ia patru argumente:

1	= LINEST (cunoscut_y, cunoscut_x, const, statistici)

Unde,

Argument	Descriere
cunoscut_y’s și cunoscut_x	Este X și y date din tabelul dvs. de date
const	Opțiune TRUE / FALSE pentru dacă interceptarea y trebuie forțată la 0 sau calculată normal
statistici	Opțiune TRUE / FALSE dacă ar trebui returnate statistici de regresie suplimentare

Folosind primul nostru exemplu, funcția este scrisă ca:

1	= LINEST (B3: B7, C3: C7, ADEVĂRAT, FALS)

Cand statistici opțiunea este setată la TRUE, organizarea statisticilor de regresie sunt după cum urmează:

S-ar putea să vă întrebați ce înseamnă fiecare variabilă.

Statistic	Descriere
mn	Coeficienți de înclinație pentru x variabile
b	y-interceptare
sen	Eroare standard pentru fiecare coeficient de pantă
seb	Eroare standard pentru interceptarea y
r2	Coeficient de determinare
sey	Eroare standard pentru y estima
F	Statistica F (pentru a determina dacă relația variabilelor apare întâmplător)
df	Grade de libertate
ssreg	Suma de regresie a pătratelor
ssrez	Suma reziduală de pătrate

Principalele statistici de înțeles sunt coeficienții de pantă, interceptarea y și coeficientul de determinare sau r² valoarea modelului.

Folosind exemplul de mai sus și selectând TRUE pentru statistici parametru:

Celulele evidențiate arată panta = 2, interceptarea = 0 și r² = 1.

R² valoarea este un indicator al puterii corelației modelului. Poate fi gândit ca un indicator al potrivirii. Un r mic² valoarea ar însemna o corelație slabă între variabilele dvs. dependente și independente, iar opusul este adevărat pentru r mare² valori, cu r² = 1 fiind o potrivire perfectă.

În versiunile ulterioare din ianuarie 2022 ale Excel-ului în Microsoft 365 (anterior Office 365), matricile dinamice au schimbat modul în care sunt evaluate formulele matrice. Nu mai este necesar să utilizați CTRL + SHIFT + ENTER sau să evidențiați zona celulelor pe care matricea le va ocupa. Pur și simplu introduceți formula și faceți clic pe Enter și celulele rezultate se vor „revărsa” în matrice.

Pentru restul acestui articol vom face referire la utilizarea LINEST în ceea ce privește matricele dinamice în Microsoft 365 Excel.

Prognoza cu LINEST (regresie simplă)

Combinarea funcțiilor LINEST și SUM poate fi utilizată pentru a prezice valoarea unei variabile dependente y, dat cunoscut X și y date. Mai jos este un exemplu care arată ce y valoarea va fi când x = 14.

1	= SUM (LINEST (C3: C7, B3: B7) * {14,1})

Modelul vine sub forma y = mx + b. Este la fel ca y = a + bx, doar un mod diferit de a reprezenta ecuația. Un sfat de reținut pentru ecuațiile liniare este variabila de lângă X este întotdeauna panta, iar variabila care urmează unui semn plus sau minus este întotdeauna interceptarea, indiferent de literele utilizate în ecuație.

Folosind formula: = SUM (LINEST (C3: C7, B3: B7) * {14,1}) returnează rezultatul de 28. Deoarece acesta este un singur rezultat, nu este necesar să introduceți ca matrice.

Capătul din urmă al formulei de mai sus * {14,1} specifică variabila independentă care trebuie utilizată pentru prezicerea variabilei dependente, în acest caz, 14.

Putem verifica acest lucru introducând x = 14 în ecuația liniei, y = 2x + 0.

Prognoza cu LINEST (regresie liniară multiplă)

Următorul tabel de date provine de la pagina LINEST a site-ului de asistență Microsoft.

În unele cazuri, există mai multe variabile independente care ar trebui luate în considerare la crearea unui model de regresie liniară. Aceasta se numește regresie liniară multiplă (adică variabile multiple independente). Dacă vreau să estimez costul unei clădiri de birouri, lucruri precum spațiul, numărul de intrări în clădire, vechimea clădirii și numărul de birouri ar face parte din ecuație. Să vedem un exemplu.

Introducând formula LINEST în celula G29 și executând-o, obținem:

1	= LINEST (E3: E13, A3: D13, TRUE, TRUE)

Modelul vine sub forma:

Amintiți-vă că matricea de rezultate LINEST este în ordine inversă față de ecuație. În exemplul de mai sus, 52,317,8 este interceptarea noastră, b și 27,6 este m-ul nostru₁ sau valoarea pantei pentru variabila Spațiu etaj, X₁.

Folosind funcția LINEST cu datele furnizate, modelul nostru de regresie este:

Cu un r² valoarea de 0,997, care indică un model puternic sau foarte corelat. Folosind modelul, puteți prezice acum care va fi valoarea evaluată a unei clădiri de birouri pe baza oricărei combinații a variabilelor independente de mai sus.

LINEST Tips

1. Asigurați-vă că aveți cea mai actualizată versiune de Microsoft 365 pentru a utiliza LINEST cu matrice dinamice. Este posibil să trebuiască să activați canalul Office Insider Current (Preview) pentru a utiliza funcțiile din matrice dinamică. Pe pagina contului:
   
2. Dacă utilizați o versiune non-Microsoft 365, va trebui să utilizați metoda moștenită CTRL + SHIFT + ENTER (CSE) de evaluare a formulelor matrice.
3. Dacă utilizați metoda moștenită, numărul de coloane de evidențiat atunci când introduceți o funcție matrice LINEST este întotdeauna numărul de X variabile din datele dvs. plus 1. Numărul de rânduri de selectat pentru matrice este 5.
4. Dacă veți partaja versiunea Excel activată a matricei dinamice cu cineva care folosește o versiune non-Microsoft 365, utilizați metoda CSE moștenită pentru a evita problemele de compatibilitate.

Vă interesează mai multe prognoze?

Consultați celelalte articole despre prognoză cu funcții de netezire exponențială, TREND, GROWTH și LOGEST.

CEL MAI LIN funcția în Foi de calcul Google

Funcția LINEST funcționează exact la fel în Foi de calcul Google ca în Excel.

LINEST Exemple în VBA

De asemenea, puteți utiliza funcția LINEST în VBA. Tip:
application.worksheetfunction.linest (cunoscut_ys, cunoscut_xs, const, statistici)

Executarea următoarei instrucțiuni VBA

1	Range ("D2") = Application.WorksheetFunction.LinEst (Range ("A2: A8"), Range ("B2: B8"))

va produce următoarele rezultate

Pentru argumentele funcției (cunoscut_y, etc.), le puteți introduce direct în funcție sau puteți defini variabile de utilizat.

Reveniți la lista tuturor funcțiilor din Excel