tutorialul explică elementele de bază ale analizei de regresie și arată câteva moduri diferite de a face regresie liniară în Excel.

Imaginați-vă acest lucru: vi se oferă o mulțime de date diferite și vi se cere să preziceți numerele de vânzări de anul viitor pentru compania dvs. Ați descoperit zeci, poate chiar sute, de factori care pot afecta numerele. Dar de unde știi care sunt cu adevărat importante? Rulați analiza de regresie în Excel. Acesta vă va oferi un răspuns la această și multe alte întrebări: Ce factori contează și care pot fi ignorați? Cât de strâns sunt acești factori legați unul de celălalt? Și cât de sigur poți fi despre predicții?

• analiza de regresie în Excel
• regresie liniară în Excel cu analiza ToolPak
• desenați un grafic de regresie liniară
• analiza de regresie în Excel cu formule

analiza de regresie în Excel – elementele de bază

în modelarea statistică, analiza de regresie este utilizată pentru a estima relațiile dintre:

variabila dependentă (aka variabila criterion) este principalul factor pe care încercați să îl înțelegeți și să îl preziceți.

variabilele independente (aka variabile explicative sau predictori) sunt factorii care ar putea influența variabila dependentă.

analiza de regresie vă ajută să înțelegeți modul în care variabila dependentă se schimbă atunci când una dintre variabilele independente variază și permite determinarea matematică care dintre aceste variabile are într-adevăr un impact.din punct de vedere tehnic, un model de analiză de regresie se bazează pe suma pătratelor, care este o modalitate matematică de a găsi dispersia punctelor de date. Scopul unui model este de a obține cea mai mică sumă posibilă de pătrate și de a desena o linie care se apropie cel mai mult de date.

în statistici, ele diferențiază între o regresie liniară simplă și multiplă. Regresia liniară simplă modelează relația dintre o variabilă dependentă și o variabilă independentă folosind o funcție liniară. Dacă utilizați două sau mai multe variabile explicative pentru a prezice variabila dependentă, aveți de-a face cu regresie liniară multiplă. Dacă variabila dependentă este modelată ca o funcție neliniară, deoarece relațiile de date nu urmează o linie dreaptă, utilizați regresie neliniară în schimb. Accentul acestui tutorial va fi pe o regresie liniară simplă.

de exemplu, să luăm numerele de vânzări pentru umbrele din ultimele 24 de luni și să aflăm precipitațiile medii lunare pentru aceeași perioadă. Trasați aceste informații într-o diagramă, iar linia de regresie va demonstra relația dintre variabila independentă (precipitații) și variabila dependentă (vânzări umbrelă):

ecuația de regresie liniară

matematic, o regresie liniară este definită de această ecuație:

unde:

• x este o variabilă independentă.
• y este o variabilă dependentă.
• a este interceptarea Y, care este valoarea medie așteptată a lui y când toate variabilele x sunt egale cu 0. Pe un grafic de regresie, este punctul în care linia traversează axa Y.
• b este panta unei linii de regresie, care este rata de schimbare pentru y ca X modificări.
• – ul este termenul de eroare aleatorie, care este diferența dintre valoarea reală a unei variabile dependente și valoarea sa prezisă.

ecuația de regresie liniară are întotdeauna un termen de eroare, deoarece, în viața reală, predictorii nu sunt niciodată perfect preciși. Cu toate acestea, unele programe, inclusiv Excel, fac calculul termenului de eroare în culise. Deci, în Excel, faceți regresie liniară folosind metoda celor mai mici pătrate și căutați coeficienții a și b astfel încât:

pentru exemplul nostru, ecuația de regresie liniară ia următoarea formă:

Umbrellas sold = b * rainfall + a

există o mână de moduri diferite de a găsi a și b. cele trei metode principale pentru a efectua analiza de regresie liniară:

• instrument de regresie inclus în analiza ToolPak
• Diagramă Scatter cu o linie de trend
• formula de regresie liniară

mai jos veți găsi instrucțiunile detaliate privind utilizarea fiecărei metode.

cum se face regresia liniară în Excel cu Analysis ToolPak

Acest exemplu arată cum se execută regresia în Excel utilizând un instrument special inclus în programul de completare Analysis ToolPak.

activați programul de completare Analysis ToolPak

Analysis Toolpak este disponibil în toate versiunile de Excel 2019 până în 2003, dar nu este activat în mod implicit. Deci, trebuie să o porniți manual. Iată cum:

1. În Excel, faceți clic pe fișier > Opțiuni.
2. în caseta de dialog Opțiuni Excel, selectați Programe de completare din bara laterală din stânga, asigurați-vă că programele de completare Excel sunt selectate în caseta gestionare și faceți clic pe Go.
   
3. în caseta de dialog Programe de completare, bifați Analysis Toolpak și faceți clic pe OK:
   

aceasta va adăuga instrumentele de analiză a datelor în fila Date din Panglica Excel.

rulați analiza de regresie

În acest exemplu, vom face o regresie liniară simplă în Excel. Ceea ce avem este o listă a precipitațiilor medii lunare pentru ultimele 24 de luni în coloana B, care este variabila noastră independentă (predictor) și numărul de umbrele vândute în coloana C, care este variabila dependentă. Desigur, există mulți alți factori care pot afecta vânzările, dar deocamdată ne concentrăm doar pe aceste două variabile:

cu analiza Toolpak adăugată activată, efectuați acești pași pentru a efectua analiza de regresie în Excel:

1. În fila Date, în grupul analiză, faceți clic pe butonul Analiză date.
   
2. selectați regresia și faceți clic pe OK.
   
3. în caseta de dialog regresie, configurați următoarele setări:
   • selectați intervalul de intrare Y, care este variabila dvs. dependentă. În cazul nostru, sunt vânzări umbrelă (C1:C25).
   • selectați intervalul de intrare X, adică variabila dvs. independentă. În acest exemplu, este precipitațiile medii lunare (B1:B25).

   dacă construiți un model de regresie multiplă, selectați două sau mai multe coloane adiacente cu variabile independente diferite.

   • bifați caseta Etichete dacă există anteturi în partea de sus a intervalelor X și Y.
   • alegeți opțiunea de ieșire preferată, o nouă foaie de lucru în cazul nostru.
   • opțional, bifați caseta de selectare reziduuri pentru a obține diferența dintre valorile prezise și cele reale.
     
4. Faceți clic pe OK și observați rezultatul analizei de regresie creat de Excel.

interpretați rezultatul analizei de regresie

după cum tocmai ați văzut, rularea regresiei în Excel este ușoară, deoarece toate calculele sunt preformate automat. Interpretarea rezultatelor este un pic mai complicată, deoarece trebuie să știți ce se află în spatele fiecărui număr. Mai jos veți găsi o defalcare a 4 părți majore ale rezultatului analizei de regresie.

ieșire analiză regresie: ieșire rezumat

această parte vă spune cât de bine se potrivește ecuația de regresie liniară calculată datelor sursă.

Iată ce înseamnă fiecare informație:

multiplu R. este coeficientul de corelație care măsoară puterea unei relații liniare între două variabile. Coeficientul de corelație poate fi orice valoare între -1 și 1, iar valoarea sa absolută indică puterea relației. Cu cât valoarea absolută este mai mare, cu atât relația este mai puternică:

• 1 înseamnă o relație pozitivă puternică
• -1 înseamnă o relație negativă puternică
• 0 înseamnă nicio relație

R pătrat. Este coeficientul de determinare, care este folosit ca indicator al bunătății potrivirii. Acesta arată câte puncte cad pe linia de regresie. Valoarea R2 este calculată din suma totală a pătratelor, mai precis, este suma abaterilor pătrate ale datelor originale din medie.

în exemplul nostru, R2 este 0,91 (rotunjit la 2 cifre), ceea ce este o zână bună. Aceasta înseamnă că 91% din valorile noastre se potrivesc modelului de analiză de regresie. Cu alte cuvinte, 91% din variabilele dependente (valorile y) sunt explicate de variabilele independente (valorile x). În general, R pătrat de 95% sau mai mult este considerat o potrivire bună.

ajustat R pătrat. Este pătratul R ajustat pentru numărul de variabile independente din model. Veți dori să utilizați această valoare în loc de R pătrat pentru analiza de regresie multiplă.

eroare Standard. Este o altă măsură de bunătate care arată precizia analizei dvs. de regresie – cu cât numărul este mai mic, cu atât puteți fi mai sigur cu privire la ecuația dvs. de regresie. În timp ce R2 reprezintă procentul varianței variabilelor dependente care se explică prin model, eroarea Standard este o măsură absolută care arată distanța medie pe care punctele de date o cad din linia de regresie.

observații. Este pur și simplu numărul de observații din modelul dvs.

analiza regresiei ieșire: ANOVA

a doua parte a ieșirii este analiza varianței (ANOVA):

practic, împarte suma pătratelor în componente individuale care oferă informații despre nivelurile de variabilitate din cadrul modelului dvs. de regresie:

• df este numărul gradelor de libertate asociate surselor de varianță.
• SS este suma pătratelor. Cu cât SS-ul rezidual este mai mic în comparație cu SS-ul Total, cu atât modelul dvs. se potrivește mai bine datelor.
• MS este pătratul mediu.
• F este statistica F, sau testul F pentru ipoteza nulă. Este folosit pentru a testa semnificația generală a modelului.
• semnificația F este valoarea P a lui F.

partea ANOVA este rar utilizată pentru o analiză simplă de regresie liniară în Excel, dar ar trebui să aveți cu siguranță o privire atentă la ultima componentă. Valoarea semnificației F oferă o idee despre cât de fiabile (semnificative statistic) sunt rezultatele dvs. Dacă semnificația F este mai mică de 0,05 (5%), modelul dvs. este OK. Dacă este mai mare de 0,05, probabil că ar fi mai bine să alegeți o altă variabilă independentă.

rezultatul analizei de regresie: coeficienții

această secțiune oferă informații specifice despre componentele analizei dvs.:

componenta cea mai utilă din această secțiune este coeficienții. Vă permite să construiți o ecuație de regresie liniară în Excel:

pentru setul nostru de date, unde y este numărul de umbrele vândute și x este o ploaie medie lunară, formula noastră de regresie liniară merge după cum urmează:

Y = Rainfall Coefficient * x + Intercept

echipat cu valori a și b rotunjite la trei zecimale, se:

Y=0.45*x-19.074

de exemplu, cu precipitațiile medii lunare egale cu 82 mm, vânzările umbrelelor ar fi de aproximativ 17,8:

0.45*82-19.074=17.8

într-un mod similar, puteți afla câte umbrele vor fi vândute cu orice alte precipitații lunare (x variabilă) specificați.

rezultatul analizei de regresie: reziduuri

dacă comparați numărul estimat și real de umbrele vândute corespunzător precipitațiilor lunare de 82 mm, veți vedea că aceste numere sunt ușor diferite:

• estimat: 17.8 (calculat mai sus)
• Real: 15 (rândul 2 al datelor sursă)

De ce este diferența? Deoarece variabilele independente nu sunt niciodată predictori perfecți ai variabilelor dependente. Iar reziduurile vă pot ajuta să înțelegeți cât de departe sunt valorile reale de valorile prezise:

cum se face un grafic de regresie liniară în Excel

dacă trebuie să vizualizați rapid relația dintre cele două variabile, desenați o diagramă de regresie liniară. E foarte ușor! Iată cum:

1. selectați cele două coloane cu datele dvs., inclusiv anteturile.
2. în fila Inset, în grupul Chats, faceți clic pe pictograma diagramă Scatter și selectați miniatura Scatter (prima):
   

   aceasta va introduce un grafic scatter în foaia dvs. de lucru, care va semăna cu acesta:
   

3. cele mai mici pătrate Linie de regresie. Pentru a face acest lucru, faceți clic dreapta pe orice punct și alegeți Adăugați Trendline… din meniul contextual.
   
4. în panoul din dreapta, selectați forma liniei de tendință liniară și, opțional, verificați ecuația de afișare pe diagramă pentru a obține formula de regresie:
   

   după cum puteți observa, ecuația de regresie pe care Excel a creat-o pentru noi este aceeași cu formula de regresie liniară pe care am construit-o pe baza coeficienților de ieșire.

5. treceți la Fill& fila linie și personalizați linia după bunul plac. De exemplu, puteți alege o altă culoare de linie și de a folosi o linie solidă în loc de o linie punctată (selectați linie solidă în caseta de tip Dash):
   

în acest moment, graficul arata deja ca un grafic de regresie decent:

totuși, poate doriți să facă câteva îmbunătățiri:

• trageți ecuația oriunde doriți.
• Adăugați titluri axe (butonul Elemente diagramă> titluri axe).
• dacă punctele dvs. de date încep în mijlocul axei orizontale și / sau verticale ca în acest exemplu, poate doriți să scăpați de spațiul alb excesiv. Următorul sfat explică cum se face acest lucru: scalați axele diagramei pentru a reduce spațiul alb.

  și așa arată graficul nostru de regresie îmbunătățit:
  

  notă importantă! În graficul de regresie, variabila independentă ar trebui să fie întotdeauna pe axa X și variabila dependentă pe axa Y. În cazul în care graficul este reprezentat în ordine inversă, swap coloanele din foaia de lucru, și apoi trage graficul din nou. Dacă nu aveți voie să rearanjați datele sursă, atunci puteți comuta axele X și Y direct într-o diagramă.

cum se face regresia în Excel folosind formule

Microsoft Excel are câteva funcții statistice care vă pot ajuta să faceți analize de regresie liniară, cum ar fi LINEST, SLOPE, INTERCPET și CORREL.

funcția LINEST folosește metoda regresiei celor mai mici pătrate pentru a calcula o linie dreaptă care explică cel mai bine relația dintre variabilele dvs. și returnează o matrice care descrie acea linie. Puteți găsi explicația detaliată a sintaxei funcției în acest tutorial. Pentru moment, să facem doar o formulă pentru setul nostru de date eșantion:

=LINEST(C2:C25, B2:B25)

deoarece funcția LINEST returnează o serie de valori, trebuie să o introduceți ca o formulă matrice. Selectați două celule adiacente în același rând, E2:F2 în cazul nostru, tastați formula și apăsați Ctrl + Shift + Enter pentru ao finaliza.

formula returnează coeficientul b (E1) și constanta a (F1) pentru ecuația de regresie liniară deja familiară:

y = bx + a

dacă evitați să utilizați formule matrice în foile de lucru, puteți calcula a și b individual cu formule regulate:

ia y-intercept (a):

=INTERCEPT(C2:C25, B2:B25)

ia panta (B):

=SLOPE(C2:C25, B2:B25)

În plus, puteți găsi coeficientul de corelație (r multiplu în rezultatul sumar al analizei de regresie) care indică cât de puternic sunt legate cele două variabile:

=CORREL(B2:B25,C2:C25)

următoarea captură de ecran arată toate aceste formule de regresie Excel în acțiune:

așa se face regresia liniară în Excel. Acestea fiind spuse, rețineți că Microsoft Excel nu este un program statistic. Dacă trebuie să efectuați analize de regresie la nivel profesional, poate doriți să utilizați software vizat, cum ar fi XLSTAT, RegressIt etc.

descărcări disponibile:

pentru a avea o privire mai atentă la formulele noastre de regresie liniară și alte tehnici discutate în acest tutorial, sunteți binevenit să descărcați analiza noastră de regresie eșantion în registrul de lucru Excel.

• cum se utilizează Solver în Excel cu exemple
• cum se calculează dobânda compusă în Excel
• cum se calculează CAGR (rata anuală de creștere compusă) în Excel