Regresie liniară pereche în Excel. Analiza de regresie în excel
Analiza de regresie în Microsoft Excel- cel mai ghiduri complete privind utilizarea MS Excel pentru a rezolva probleme de analiză de regresie în domeniul analizei de afaceri. Konrad Carlberg explică clar problemele teoretice, cunoașterea cărora vă va ajuta să evitați multe greșeli, atât în conducere independentă analiza de regresie și la evaluarea rezultatelor analizelor efectuate de alții. Tot materialul, de la corelații simple și teste t până la analiza multiplă a covarianței, se bazează exemple reale si este insotita descriere detaliata procedurile pas cu pas corespunzătoare.
Cartea discută caracteristicile și controversele asociate cu Funcții Excel pentru a lucra cu regresia, discută consecințele utilizării fiecărei opțiuni și a fiecărui argument și explică cum să utilizați în mod fiabil metodele de regresie în cel mai mult zone diferite, de la cercetarea medicală la analiza financiară.
Konrad Carlberg. Analiza de regresie în Microsoft Excel. – M.: Dialectică, 2017. – 400 p.
Descărcați nota în sau format, exemple în format
Capitolul 1: Evaluarea variabilității datelor
Statisticienii au la dispoziție multe măsuri de variație. Una dintre ele este suma abaterilor pătrate ale valorilor individuale de la medie. În Excel, funcția SQUARE() este utilizată pentru aceasta. Dar varianța este mai des folosită. Dispersia este media abaterilor pătrate. Varianta este insensibilă la numărul de valori din setul de date studiat (în timp ce suma abaterilor pătrate crește odată cu numărul de măsurători).
Excel oferă două funcții care returnează varianță: DISP.G() și DISP.V():
- Utilizați funcția DISP.G() dacă valorile care trebuie procesate formează o populație. Adică, valorile conținute în interval sunt singurele valori care vă interesează.
- Utilizați funcția DISP.B() dacă valorile care trebuie procesate formează un eșantion dintr-o populație mai mare. Se presupune că există sensuri suplimentare, a cărei varianță o puteți estima.
Dacă o cantitate, cum ar fi o medie sau un coeficient de corelație, este calculată dintr-o populație, se numește parametru. O cantitate similară calculată pe baza unui eșantion se numește statistică. Numărarea abaterilor de la medie V acest set, veți obține o sumă a abaterilor pătrate care este mai mică decât dacă le-ați număra din orice altă valoare. O afirmație similară este valabilă pentru variație.
Cu cât dimensiunea eșantionului este mai mare, cu atât valoarea statistică calculată este mai precisă. Dar nu există o dimensiune a eșantionului mai mică decât dimensiunea populației pentru care puteți fi sigur că valoarea statistică se potrivește cu valoarea parametrului.
Să presupunem că aveți un set de 100 de valori de înălțime a căror medie diferă de media populației, oricât de mică ar fi diferența. Prin calcularea varianței pentru un eșantion, veți obține o valoare, să spunem 4. Această valoare este mai mică decât orice altă valoare care poate fi obținută prin calcularea abaterii fiecăreia dintre cele 100 de valori de înălțime în raport cu orice valoare, alta decât media eșantionului , inclusiv în raport cu adevărata medie generală. Prin urmare, varianța calculată va fi diferită și mai mică de varianța pe care ați obține-o dacă ați afla cumva și ați folosi un parametru de populație mai degrabă decât o medie a eșantionului.
Suma medie a pătratelor determinată pentru eșantion oferă o estimare mai mică a varianței populației. Varianta calculată în acest fel se numește deplasat evaluare. Se pare că, pentru a elimina părtinirea și pentru a obține o estimare imparțială, este suficient să împărțiți suma abaterilor pătrate, nu la n, Unde n- dimensiunea eșantionului și n – 1.
Magnitudinea n – 1 se numește numărul (numărul) de grade de libertate. Exista căi diferite calculul acestei cantități, deși toate implică fie scăderea unui număr din dimensiunea eșantionului, fie numărarea numărului de categorii în care se încadrează observațiile.
Esența diferenței dintre funcțiile DISP.G() și DISP.V() este următoarea:
- În funcția VAR.G(), suma pătratelor este împărțită la numărul de observații și, prin urmare, reprezintă o estimare părtinitoare a varianței, adevărata medie.
- În funcția DISP.B(), suma pătratelor este împărțită la numărul de observații minus 1, adică. prin numărul de grade de libertate, ceea ce oferă o estimare mai precisă și imparțială a varianței populației din care a fost extras eșantionul.
Deviație standard deviație standard, SD) – da Rădăcină pătrată din dispersie:
Abaterile la pătrat transformă scara de măsurare într-o altă metrică, care este pătratul celei inițiale: metri - în metri patrati, dolari - în dolari pătrați etc. Abaterea standard este rădăcina pătrată a varianței și, prin urmare, ne duce înapoi la unitățile de măsură inițiale. Oricare este mai convenabil.
Este adesea necesar să se calculeze abaterea standard după ce datele au fost supuse unor manipulări. Și deși în aceste cazuri rezultatele sunt, fără îndoială, abateri standard, ele sunt de obicei numite erori standard. Există mai multe tipuri de erori standard, inclusiv eroarea standard de măsurare, eroarea standard de proporție și eroarea standard a mediei.
Să presupunem că ați colectat date privind înălțimea pentru 25 de bărbați adulți selectați aleatoriu în fiecare dintre cele 50 de state. Apoi, calculați înălțimea medie a bărbaților adulți din fiecare stat. Cele 50 de valori medii rezultate pot fi, la rândul lor, considerate observații. Din aceasta ați putea calcula abaterea lor standard, adică eroarea standard a mediei. Orez. 1. compară distribuția a 1.250 de valori individuale brute (date de înălțime pentru 25 de bărbați în fiecare dintre cele 50 de state) cu distribuția celor 50 de medii de stat. Formula pentru estimarea erorii standard a mediei (adică abaterea standard a mediilor, nu observațiile individuale):
unde este eroarea standard a mediei; s– abaterea standard a observațiilor originale; n– numărul de observații din eșantion.
Orez. 1. Variația mediilor de la stat la stat este semnificativ mai mică decât variația observațiilor individuale.
În statistică există un acord cu privire la folosirea limbii grecești și litere latine pentru a desemna mărimi statistice. Se obișnuiește să se desemneze parametrii populației generale cu litere grecești și eșantionul de statistici cu litere latine. Prin urmare, dacă despre care vorbim despre deviația standard a populației, o scriem ca σ; dacă se consideră abaterea standard a eșantionului, atunci se folosește notația s. În ceea ce privește simbolurile pentru desemnarea mediilor, acestea nu sunt atât de bine de acord între ele. Media populației este notă cu litera greacă μ. Cu toate acestea, simbolul X̅ este folosit în mod tradițional pentru a reprezenta media eșantionului.
scorul z exprimă poziția unei observații în distribuție în unități de abatere standard. De exemplu, z = 1,5 înseamnă că observația este la 1,5 abateri standard de la medie valori mari. Termen scorul z utilizate pentru evaluări individuale, de ex. pentru dimensiunile atribuite elementelor individuale ale eșantionului. Termenul folosit pentru a se referi la astfel de statistici (cum ar fi media de stat) scorul z:
unde X̅ este media eșantionului, μ este media populației, este eroarea standard a mediei unui set de eșantioane:
unde σ este eroarea standard a populației (măsurători individuale), n- marime de mostra.
Să presupunem că lucrezi ca instructor la un club de golf. Ați reușit să măsurați distanța loviturilor dvs. pe o perioadă lungă de timp și știți că media este de 205 de metri, iar abaterea standard este de 36 de metri. Vi se oferă un nou club, susținând că acesta vă va crește distanța de lovire cu 10 metri. Cereți fiecăruia dintre următorii 81 de patroni ai clubului să facă o fotografie de probă cu un nou club și să înregistreze distanța lor de swing. S-a dovedit că distanța medie cu noul club a fost de 215 de metri. Care este probabilitatea ca o diferență de 10 yarzi (215 – 205) să se datoreze exclusiv erorii de eșantionare? Sau, altfel spus: Care este probabilitatea ca, în cadrul unor teste mai extinse, noul club să nu demonstreze o creștere a distanței de lovire peste media existentă pe termen lung de 205 de metri?
Putem verifica acest lucru prin generarea unui scor z. Eroarea standard a mediei:
Apoi scorul z:
Trebuie să găsim probabilitatea ca media eșantionului să fie la 2,5σ distanță de media populației. Dacă probabilitatea este mică, atunci diferențele nu se datorează întâmplării, ci calității noului club. Excel nu are un scor z pentru determinarea probabilității. functie terminata. Cu toate acestea, puteți utiliza formula =1-NORM.ST.DIST(z-score,TRUE), unde funcția NORM.ST.DIST() returnează aria de sub curba normală la stânga scorului z (Figura 2).
Orez. 2. Funcția NORM.ST.DIST() returnează aria de sub curba la stânga valorii z; Pentru a mări imaginea faceți clic pe ea Click dreapta mouse-ul și selectați Deschide imaginea într-o filă nouă
Al doilea argument al funcției NORM.ST.DIST() poate lua două valori: TRUE – funcția returnează aria zonei de sub curba la stânga punctului specificat de primul argument; FALSE – funcția returnează înălțimea curbei în punctul specificat de primul argument.
Dacă media populației (μ) și abaterea standard (σ) nu sunt cunoscute, se utilizează valoarea t (vezi detalii). Structurile z-score și t-score diferă prin aceea că abaterea standard s obținută din rezultatele eșantionului este utilizată pentru a găsi scorul t mai degrabă decât valoarea cunoscută a parametrului populației σ. Curba normală are o singură formă, iar forma distribuției valorii t variază în funcție de numărul de grade de libertate df. grade de libertate) din proba pe care o reprezintă. Numărul de grade de libertate ale eșantionului este egal cu n – 1, Unde n- dimensiunea probei (Fig. 3).
Orez. 3. Forma distribuțiilor t care apar în cazurile în care parametrul σ este necunoscut diferă de forma distribuției normale
Excel are două funcții pentru distribuția t, numită și distribuția Student: STUDENT.DIST() returnează aria de sub curba la stânga unei valori t date, iar STUDENT.DIST.PH() returnează aria la dreapta.
Capitolul 2. Corelația
Corelația este o măsură a dependenței dintre elementele unui set de perechi ordonate. Corelația este caracterizată Coeficienții de corelație Pearson–r. Coeficientul poate lua valori în intervalul de la –1,0 la +1,0.
Unde S xȘi S y– abaterile standard ale variabilelor XȘi Y, S xy- covarianta:
În această formulă, covarianța este împărțită la abaterile standard ale variabilelor XȘi Y, eliminând astfel efectele de scalare legate de unitate din covarianță. Excel folosește funcția CORREL(). Numele acestei funcții nu conține elementele de calificare Г și В, care sunt utilizate în numele funcțiilor precum STANDARDEV(), VARIANCE() sau COVARIANCE(). Deși coeficientul de corelație al eșantionului oferă o estimare părtinitoare, motivul părtinirii este diferit de cel în cazul varianței sau abaterii standard.
În funcție de mărimea coeficientului general de corelație (deseori notat cu litera greacă ρ ), coeficient de corelație r produce o estimare părtinitoare, cu efectul părtinirii crescând pe măsură ce dimensiunea eșantionului scade. Cu toate acestea, nu încercăm să corectăm această părtinire în același mod în care, de exemplu, am făcut-o la calcularea abaterii standard, când am înlocuit nu numărul de observații, ci numărul de grade de libertate în formula corespunzătoare. În realitate, numărul de observații utilizate pentru a calcula covarianța nu are niciun efect asupra mărimii.
Coeficientul de corelație standard este destinat utilizării cu variabile care sunt legate între ele printr-o relație liniară. Prezența neliniarității și/sau a erorilor în date (outliers) duce la calcularea incorectă a coeficientului de corelație. Pentru a diagnostica problemele de date, se recomandă crearea diagramelor de dispersie. Acesta este singurul tip de diagramă din Excel care tratează atât axele orizontale, cât și cele verticale ca axe valorice. O diagramă cu linii definește una dintre coloane ca axa categoriei, ceea ce distorsionează imaginea datelor (Fig. 4).
Orez. 4. Liniile de regresie par la fel, dar le compară ecuațiile între ele
Observații folosite pentru a construi diagramă cu linii, sunt situate echidistante de-a lungul axei orizontale. Etichetele de diviziune de-a lungul acestei axe sunt doar etichete, nu valori numerice.
Deși corelația înseamnă adesea că există o relație cauză-efect, ea nu poate fi folosită pentru a demonstra că acesta este cazul. Statisticile nu sunt folosite pentru a demonstra dacă o teorie este adevărată sau falsă. Pentru a exclude explicațiile concurente pentru rezultatele observaționale, pune experimente planificate. Statisticile sunt folosite pentru a rezuma informațiile colectate în timpul unor astfel de experimente și cuantificare probabilitatea ca decizia luată să fie incorectă având în vedere dovezile disponibile.
Capitolul 3: Regresia simplă
Dacă două variabile sunt legate între ele, astfel încât valoarea coeficientului de corelație depășește, să zicem, 0,5, atunci în acest caz este posibil să se prezică (cu oarecare precizie) valoarea necunoscută a unei variabile din valoarea cunoscută a celeilalte variabile. . Pentru a obține valorile prețurilor prognozate pe baza datelor prezentate în Fig. 5, oricare dintre mai multe pot fi utilizate moduri posibile, dar aproape sigur nu îl vei folosi pe cel prezentat în Fig. 5. Totuși, ar trebui să vă familiarizați cu ea, deoarece nicio altă metodă nu vă permite să demonstrați legătura dintre corelație și predicție la fel de clar ca aceasta. În fig. Figura 5 din intervalul B2:C12 arată un eșantion aleatoriu de zece case și oferă date despre suprafața fiecărei case (în metri pătrați) și prețul ei de vânzare.
Orez. 5. Valorile estimate ale prețurilor de vânzare formează o linie dreaptă
Găsiți mediile, abaterile standard și coeficientul de corelație (interval A14:C18). Calculați scorurile z ale zonei (E2:E12). De exemplu, celula E3 conține formula: =(B3-$B$14)/$B$15. Calculați scorurile z ale prețului prognozat (F2:F12). De exemplu, celula F3 conține formula: =ЕЗ*$В$18. Convertiți scorurile z în prețuri în dolari (H2:H12). În celula NZ formula este: =F3*$C$15+$C$14.
Rețineți că valoarea prezisă tinde întotdeauna să se deplaseze spre media lui 0. Cu cât coeficientul de corelație este mai aproape de zero, cu atât scorul z prezis este mai aproape de zero. În exemplul nostru, coeficientul de corelație dintre suprafață și prețul de vânzare este 0,67, iar prețul prognozat este 1,0 * 0,67, adică 0,67. Aceasta corespunde unui exces de valoare peste medie egal cu două treimi dintr-o abatere standard. Dacă coeficientul de corelație ar fi egal cu 0,5, atunci prețul prognozat ar fi 1,0 * 0,5, adică 0,5. Aceasta corespunde unui exces de valoare peste medie, egal cu doar jumătate de abatere standard. Ori de câte ori valoarea coeficientului de corelație diferă de valoarea ideală, i.e. mai mare de -1,0 și mai mic de 1,0, scorul variabilei prezise ar trebui să fie mai aproape de medie decât scorul variabilei (independente) de predicție față de propriul său. Acest fenomen se numește regresie la medie sau pur și simplu regresie.
Excel are mai multe funcții pentru determinarea coeficienților unei ecuații de regresie (numită linie de tendință în Excel) y =kx + b. Pentru determinare k serveste functiei
=SLOPE(valori_y_cunoscute, valori_x_cunoscute)
Aici la este variabila prezisă și X- variabila independenta. Trebuie să urmați cu strictețe această ordine a variabilelor. Panta dreptei de regresie, coeficientul de corelație, abaterile standard ale variabilelor și covarianța sunt strâns legate (Figura 6). Funcția INTERMEPT() returnează valoarea interceptată de linia de regresie pe axa verticală:
=LIMIT(valori_y_cunoscute, valori_x_cunoscute)
Orez. 6. Relația dintre abaterile standard transformă covarianța într-un coeficient de corelație și panta dreptei de regresie
Rețineți că numărul de valori x și y furnizate ca argumente pentru funcțiile SLOPE() și INTERCEPT() trebuie să fie același.
Analiza de regresie folosește alta indicator important– R 2 (R-pătrat) sau coeficientul de determinare. Ea determină ce contribuție la variabilitatea generală a datelor o are relația dintre XȘi la. În Excel, există o funcție pentru aceasta numită CVPIERSON(), care preia exact aceleași argumente ca și funcția CORREL().
Se spune că două variabile cu un coeficient de corelație diferit de zero între ele explică varianța sau au varianța explicată. Varianta explicată de obicei este exprimată ca procent. Asa de R 2 = 0,81 înseamnă că 81% din varianța (dispersia) a două variabile este explicată. Restul de 19% se datorează fluctuațiilor aleatorii.
Excel are o funcție TREND care face calculele mai ușoare. Funcția TREND():
- acceptă valorile cunoscute pe care le furnizați Xși valori cunoscute la;
- calculează panta dreptei de regresie și constanta (interceptarea);
- returnează valorile prezise la, determinat prin aplicarea unei ecuații de regresie la valori cunoscute X(Fig. 7).
Funcția TREND() este o funcție matrice (dacă nu ați mai întâlnit astfel de funcții, vă recomand).
Orez. 7. Utilizarea funcției TREND() vă permite să accelerați și să simplificați calculele în comparație cu utilizarea unei perechi de funcții SLOPE() și INTERCEPT()
Pentru a introduce funcția TREND() ca formulă matrice în celulele G3:G12, selectați intervalul G3:G12, introduceți formula TREND(NW:C12;B3:B12), apăsați și mențineți apăsate tastele
Funcția TREND() are încă două argumente: noi_valori_xȘi const. Primul vă permite să faceți o prognoză pentru viitor, iar al doilea poate forța linia de regresie să treacă prin origine (o valoare TRUE spune Excel să folosească constanta calculată, o valoare FALSE spune Excel să folosească o constantă = 0 ). Excel vă permite să desenați o linie de regresie pe un grafic, astfel încât să treacă prin origine. Începeți prin a desena o diagramă de dispersie, apoi faceți clic dreapta pe unul dintre marcatorii seriei de date. Selectați elementul din meniul contextual care se deschide Adăugați o linie de tendință; selecteaza o optiune Liniar; dacă este necesar, derulați în jos panoul, bifați caseta Configurați intersecția; Asigurați-vă că caseta de text asociată este setată la 0.0.
Dacă aveți trei variabile și doriți să determinați corelația dintre două dintre ele eliminând influența celei de-a treia, puteți utiliza corelație parțială. Să presupunem că sunteți interesat de relația dintre procentul de locuitori ai unui oraș care au absolvit facultatea și numărul de cărți din bibliotecile orașului. Ați colectat date pentru 50 de orașe, dar... Problema este că ambii acești parametri pot depinde de bunăstarea locuitorilor unui anumit oraș. Desigur, este foarte greu să găsești alte 50 de orașe caracterizate de exact același nivel de bunăstare a locuitorilor.
Folosind metode statistice pentru a controla influența bogăției atât asupra suportului financiar al bibliotecii, cât și asupra accesibilității colegiului, ați putea obține o cuantificare mai precisă a puterii relației dintre variabilele de interes, și anume numărul de cărți și numărul de absolvenți. O astfel de corelație condiționată între două variabile, atunci când valorile altor variabile sunt fixe, se numește corelație parțială. O modalitate de calcul este să utilizați ecuația:
Unde rC.B. . W- coeficient de corelație între variabilele Colegiu și Cărți cu influența exclusă ( valoare fixa) variabilă Avere; rC.B.- coeficientul de corelație între variabilele Facultate și Cărți; rCW- coeficientul de corelație între variabilele Colegiu și Welfare; rB.W.- coeficientul de corelaţie între variabilele Cărţi şi Bunăstare.
Pe de altă parte, corelația parțială poate fi calculată pe baza analizei reziduurilor, i.e. diferențele dintre valorile prezise și rezultatele asociate ale observațiilor reale (ambele metode sunt prezentate în Fig. 8).
Orez. 8. Corelația parțială ca corelație a reziduurilor
Pentru a simplifica calculul matricei coeficienților de corelație (B16:E19), utilizați pachetul Analiza Excel(meniul Date –> Analiză –> Analiza datelor). În mod implicit, acest pachet nu este activ în Excel. Pentru a-l instala, treceți prin meniu Fişier –> Opțiuni –> Suplimente. În partea de jos a ferestrei deschise Opțiuniexcela găsiți câmpul Control, Selectați Suplimenteexcela, faceți clic Merge. Bifați caseta de lângă programul de completare Pachet de analize. Faceți clic pe A analiza datelor, selectați opțiunea Corelație. Specificați $B$2:$D$13 ca interval de intrare, bifați caseta Etichete pe prima linie, specificați $B$16:$E$19 ca interval de ieșire.
O altă posibilitate este de a determina corelația semi-parțială. De exemplu, investigați efectele înălțimii și vârstei asupra greutății. Astfel, aveți două variabile predictoare - înălțimea și vârsta, și o variabilă predictor - greutatea. Doriți să excludeți influența unei variabile predictoare asupra alteia, dar nu asupra variabilei predictoare:
unde H – Înălțime, W – Greutate, A – Vârstă; indicele coeficientului de corelaţie semi-parţial foloseşte paranteze rotunde, cu ajutorul căruia se indică ce variabilă se elimină influența și din ce anume variabilă. ÎN în acest caz, denumirea W(H.A) indică faptul că efectul variabilei Vârstă este eliminat din variabila Înălțime, dar nu și din variabila Greutate.
Poate părea că problema discutată nu are o importanță semnificativă. La urma urmei, cel mai important lucru este cât de precis funcționează ecuație generală regresia, în timp ce problema contribuțiilor relative ale variabilelor individuale la varianța totală explicată pare să aibă o importanță secundară. Cu toate acestea, acesta nu este cazul. Odată ce începeți să vă întrebați dacă o variabilă merită folosită într-o ecuație de regresie multiplă, problema devine importantă. Poate influența evaluarea corectitudinii alegerii modelului pentru analiză.
Capitolul 4. Funcția LINEST().
Funcția LINEST() returnează 10 statistici de regresie. Funcția LINEST() este o funcție matrice. Pentru a o introduce, selectați un interval care conține cinci rânduri și două coloane, introduceți formula și faceți clic
LINEST(B2:B21;A2:A21,TRUE,TRUE)
Orez. 9. Funcția LINEST(): a) selectați intervalul D2:E6, b) introduceți formula așa cum se arată în bara de formule, c) faceți clic
Funcția LINEST() returnează:
- coeficientul de regresie (sau panta, celula D2);
- segment (sau constantă, celula E3);
- erori standard coeficient de regresie și constantă (interval D3:E3);
- coeficientul de determinare R2 pentru regresie (celula D4);
- eroare standard de estimare (celula E4);
- F-test pentru regresie completă (celula D5);
- numărul de grade de libertate pentru suma reziduală a pătratelor (celula E5);
- suma de regresie a pătratelor (celula D6);
- suma reziduală a pătratelor (celula E6).
Să ne uităm la fiecare dintre aceste statistici și la modul în care interacționează.
Eroare standardîn cazul nostru, este abaterea standard calculată pentru erorile de eșantionare. Adică, aceasta este o situație în care populația generală are o statistică, iar eșantionul are alta. Împărțirea coeficientului de regresie la eroarea standard vă oferă o valoare de 2,092/0,818 = 2,559. Cu alte cuvinte, un coeficient de regresie de 2,092 este la două erori standard și jumătate distanță de zero.
Dacă coeficientul de regresie egal cu zero, Acea cea mai bună estimare variabila prezisă este media ei. Două erori standard și jumătate sunt destul de mari și puteți presupune cu siguranță că coeficientul de regresie pentru populație este diferit de zero.
Puteți determina probabilitatea de a obține un coeficient de regresie al eșantionului de 2,092 dacă valoarea sa reală în populație este 0,0 utilizând funcția
STUDENT.DIST.PH (criteriul t = 2,559; numărul de grade de libertate = 18)
În general, numărul de grade de libertate = n – k – 1, unde n este numărul de observații și k este numărul de variabile predictoare.
Această formulă returnează 0,00987 sau rotunjită la 1%. Ne spune următoarele: dacă coeficientul de regresie pentru populație este 0%, atunci probabilitatea de a obține un eșantion de 20 de persoane pentru care valoare calculată Coeficientul de regresie este 2,092, un modest 1%.
Testul F (celula D5 din Fig. 9) îndeplinește aceleași funcții în raport cu regresia completă ca și testul t în raport cu coeficientul de regresie simplă perechi. Testul F este utilizat pentru a testa dacă coeficientul de determinare R 2 pentru o regresie este suficient de mare pentru a respinge ipoteza că în populație are o valoare de 0,0, ceea ce indică faptul că nu există varianță explicată de predictor și de variabila prezisă. Când există o singură variabilă predictivă, testul F este exact egal cu testul t pătrat.
Până acum ne-am uitat la variabilele de interval. Dacă aveți variabile care pot lua mai multe valori, reprezentând nume simple, de exemplu, Bărbat și Femeie sau Reptile, Amfibieni și Pești, imaginați-le ca cod numeric. Astfel de variabile se numesc nominale.
Statistici R2 cuantifică proporția de varianță explicată.
Eroare standard de estimare.În fig. Figura 4.9 prezintă valorile prezise ale variabilei Greutate, obținute pe baza relației acesteia cu variabila Înălțime. Intervalul E2:E21 conține valorile reziduale pentru variabila Greutate. Mai precis, aceste reziduuri sunt numite erori - de unde termenul de eroare standard de estimare.
Orez. 10. Atât R 2 cât și eroarea standard a estimării exprimă acuratețea prognozelor obținute prin regresie
Cu cât eroarea standard a estimării este mai mică, cu atât ecuația de regresie este mai precisă și cu atât vă așteptați ca predicția produsă de ecuație să se potrivească cu observația reală. Eroarea standard de estimare oferă o modalitate de a cuantifica aceste așteptări. Greutatea a 95% dintre persoanele cu o anumită înălțime va fi în intervalul:
(înălțime * 2,092 – 3,591) ± 2,092 * 21,118
F-statistică este raportul dintre variația între grupuri și varianța în interiorul grupului. Acest nume a fost introdus de statisticianul George Snedecor în onoarea lui Sir, care a dezvoltat analiza varianței (ANOVA, Analysis of Variance) la începutul secolului al XX-lea.
Coeficientul de determinare R2 exprimă proporția valoare totală pătrate asociate cu regresia. Valoarea (1 – R 2) exprimă proporția din suma totală a pătratelor asociate cu reziduuri - erori de prognoză. Testul F poate fi obținut utilizând funcția LINEST (celula F5 din Fig. 11), folosind sume de pătrate (interval G10:J11), folosind proporții de varianță (interval G14:J15). Formulele pot fi studiate în fișierul Excel atașat.
Orez. 11. Calculul criteriului F
Când se utilizează variabile nominale, se folosește codarea inactivă (Figura 12). Pentru a codifica valori, este convenabil să folosiți valorile 0 și 1. Probabilitatea F este calculată folosind funcția:
F.DIST.PH(K2;I2;I3)
Aici funcția F.DIST.PH() returnează probabilitatea de a obține un criteriu F care respectă distribuția F centrală (Fig. 13) pentru două seturi de date cu numerele de grade de libertate date în celulele I2 și I3, a cărui valoare coincide cu valoarea dată în celula K2.
Orez. 12. Analiza de regresie folosind variabile dummy
Orez. 13. Distribuția F centrală la λ = 0
Capitolul 5. Regresie multiplă
Când treceți de la o regresie simplă pe perechi cu o variabilă predictor la regresie multiplă, adăugați una sau mai multe variabile predictor. Stocați valorile variabilelor de predictor în coloanele adiacente, cum ar fi coloanele A și B în cazul a doi predictori sau A, B și C în cazul a trei predictori. Înainte de a introduce o formulă care include funcția LINEST(), selectați cinci rânduri și atâtea coloane câte variabile predictoare, plus încă una pentru constantă. În cazul regresiei cu două variabile predictoare, se poate folosi următoarea structură:
LINEST(A2: A41; B2: C41;;TRUE)
În mod similar, în cazul a trei variabile:
LINIE(A2:A61;B2:D61;;TRUE)
Să presupunem că doriți să studiați posibilele efecte ale vârstei și ale dietei asupra nivelurilor de LDL - lipoproteine cu densitate scăzută, despre care se crede că sunt responsabile pentru formarea plăcilor de ateroscleroză, care provoacă aterotromboză (Fig. 14).
Orez. 14. Regresie multiplă
R2 al regresiei multiple (reflectat în celula F13) este mai mare decât R2 al oricărei regresii simple (E4, H4). Regresia multiplă utilizează mai multe variabile predictoare simultan. În acest caz, R2 crește aproape întotdeauna.
Pentru orice ecuație simplă de regresie liniară cu o variabilă predictor, va exista întotdeauna o corelație perfectă între valorile prezise și valorile variabilei predictor deoarece ecuația înmulțește valorile predictorului cu o constantă și adaugă o altă constantă la fiecare produs. Acest efect nu persistă în regresia multiplă.
Afișarea rezultatelor returnate de funcția LINEST() pentru regresia multiplă (Figura 15). Coeficienții de regresie sunt ieșiți ca parte a rezultatelor returnate de funcția LINEST(). în ordinea inversă a variabilelor(G–H–I corespunde C–B–A).
Orez. 15. Coeficienții și erorile lor standard sunt afișate în ordine inversă urmărindu-le pe foaia de lucru
Principiile și procedurile utilizate în analiza de regresie a variabilelor cu un singur predictor sunt ușor de adaptat pentru a lua în considerare mai multe variabile predictoare. Se pare că o mare parte din această adaptare depinde de eliminarea influenței variabilelor predictoare una asupra celeilalte. Acesta din urmă este asociat cu corelații parțiale și semi-parțiale (Fig. 16).
Orez. 16. Regresia multiplă poate fi exprimată prin regresia perechi a reziduurilor (a se vedea fișierul Excel pentru formule)
În Excel, există funcții care oferă informații despre distribuțiile t și F. Funcțiile ale căror nume includ partea DIST, cum ar fi STUDENT.DIST() și F.DIST(), iau un test t sau F-test ca argument și returnează probabilitatea de a observa o valoare specificată. Funcțiile ale căror nume includ partea OBR, cum ar fi STUDENT.INV() și F.INR(), iau o valoare de probabilitate ca argument și returnează o valoare de criteriu corespunzătoare probabilității specificate.
Deoarece căutăm valori critice ale distribuției t care tăie marginile regiunilor de coadă, trecem 5% ca argument uneia dintre funcțiile STUDENT.INV(), care returnează valoarea corespunzătoare acestei probabilități. (Fig. 17, 18).
Orez. 17. Testul t cu două cozi
Orez. 18. Test t cu o coadă
Prin stabilirea unei reguli de decizie pentru regiunea alfa cu o singură coadă, creșteți puterea statistică a testului. Dacă intrați într-un experiment și sunteți încrezător că aveți toate motivele să vă așteptați la un coeficient de regresie pozitiv (sau negativ), atunci ar trebui să efectuați un test cu o singură coadă. În acest caz, probabilitatea că acceptați solutie corecta, respingând ipoteza unui coeficient de regresie zero în populație, va fi mai mare.
Statisticienii preferă să folosească termenul test dirijatîn locul termenului test cu o singură coadă si termen test nedirecționatîn locul termenului test cu două cozi. Termenii direcționat și nedirecționat sunt preferați deoarece ei subliniază mai degrabă tipul de ipoteză decât natura cozilor distribuției.
O abordare a evaluării impactului predictorilor pe baza comparației modelelor.În fig. Figura 19 prezintă rezultatele unei analize de regresie care testează contribuția variabilei Diet la ecuația de regresie.
Orez. 19. Compararea a două modele prin testarea diferențelor în rezultatele acestora
Rezultatele funcției LINEST() (interval H2:K6) sunt legate de ceea ce numesc eu model complet, care regresează variabila LDL pe variabilele Dietă, Vârstă și HDL. Intervalul H9:J13 prezintă calcule fără a lua în considerare variabila predictor Dietă. Eu numesc asta modelul limitat. În modelul complet, 49,2% din varianța variabilei dependente LDL a fost explicată de variabilele predictoare. În modelul restrâns, doar 30,8% din LDL este explicată de variabilele Vârstă și HDL. Pierderea în R 2 datorită excluderii variabilei Diet din model este de 0,183. În intervalul G15:L17 se fac calcule care arată că există doar o probabilitate de 0,0288 ca efectul variabilei Dietă să fie aleatoriu. În restul de 97,1%, dieta are un efect asupra LDL.
Capitolul 6: Ipoteze și precauții pentru analiza de regresie
Termenul „ipoteză” nu este definit suficient de strict, iar modul în care este utilizat sugerează că, dacă ipoteza nu este îndeplinită, atunci rezultatele întregii analize sunt cel puțin discutabile sau posibil invalide. Acesta nu este de fapt cazul, deși există cu siguranță cazuri în care încălcarea unei presupuneri schimbă fundamental imaginea. Ipoteze de bază: a) reziduurile variabilei Y sunt în mod normal distribuite în orice punct X de-a lungul dreptei de regresie; b) Valorile Y sunt în dependență liniară din valorile X; c) dispersia reziduurilor este aproximativ aceeași în fiecare punct X; d) nu există dependenţă între reziduuri.
Dacă ipotezele nu joacă un rol semnificativ, statisticienii spun că analiza este robustă la încălcarea ipotezei. În special, atunci când utilizați regresia pentru a testa diferențele dintre mediile grupului, ipoteza că valorile Y - și, prin urmare, reziduurile - sunt distribuite în mod normal, nu joacă un rol semnificativ: testele sunt robuste la încălcări ale ipotezei de normalitate. Este important să analizați datele folosind diagrame. De exemplu, inclus în supliment Analiza datelor instrument Regresia.
Dacă datele nu îndeplinesc ipotezele regresiei liniare, există alte abordări decât regresia liniară la dispoziție. Una dintre ele este regresia logistică (Fig. 20). Aproape de limitele superioare și inferioare ale variabilei predictoare, regresia liniară produce predicții nerealiste.
Orez. 20. Regresie logistică
În fig. Figura 6.8 prezintă rezultatele a două metode de analiză a datelor care vizează examinarea relației dintre venitul anual și probabilitatea de a cumpăra o locuință. Evident, probabilitatea de a face o achiziție va crește odată cu creșterea veniturilor. Graficele facilitează identificarea diferențelor dintre rezultatele pe care regresia liniară prezice probabilitatea de a cumpăra o casă și rezultatele pe care le-ați putea obține folosind o abordare diferită.
În limbajul statisticianului, respingerea ipotezei nule atunci când de fapt este adevărată se numește eroare de tip I.
În add-on Analiza datelor a oferit instrument la îndemână a genera numere aleatorii, permițând utilizatorului să specifice forma dorită a distribuției (de exemplu, Normală, Binomială sau Poisson), precum și media și abaterea standard.
Diferențele dintre funcțiile familiei STUDENT.DIST(). Incepand cu versiuni Excel 2010 trei disponibile forme diferite o funcție care returnează proporția distribuției la stânga și/sau la dreapta unei valori date de test t. Funcția STUDENT.DIST() returnează fracțiunea ariei de sub curba de distribuție la stânga valorii t-test pe care o specificați. Să presupunem că aveți 36 de observații, deci numărul de grade de libertate pentru analiză este 34 și valoarea testului t = 1,69. În acest caz formula
STUDENT.DIST(+1,69,34,TRUE)
returnează valoarea 0,05 sau 5% (Figura 21). Al treilea argument al funcției STUDENT.DIST() poate fi TRUE sau FALSE. Dacă este setată la TRUE, funcția returnează aria cumulată de sub curba din stânga dat testul t, exprimată ca fracție. Dacă este FALS, funcția returnează înălțimea relativă a curbei în punctul corespunzător testului t. Alte versiuni ale funcției STUDENT.DIST() - STUDENT.DIST.PH() și STUDENT.DIST.2X() - iau ca argumente doar valoarea testului t și numărul de grade de libertate și nu necesită specificarea unei treimi argument.
Orez. 21. Zona umbrită mai întunecată din coada stângă a distribuției corespunde proporției de suprafață sub curba din stânga unei valori pozitive mari a testului t
Pentru a determina zona din dreapta testului t, utilizați una dintre formulele:
1 — STIODENT.DIST (1, 69;34;TRUE)
STUDENT.DIST.PH(1,69;34)
Întreaga zonă de sub curbă trebuie să fie de 100%, astfel încât scăderea din 1 a fracțiunii ariei din stânga valorii testului t pe care o returnează funcția dă fracțiunea ariei din dreapta valorii testului t. S-ar putea să-l găsești mai mult varianta preferata obținând direct fracția de suprafață care vă interesează folosind funcția STUDENT.DIST.PH(), unde PH înseamnă coada dreaptă a distribuției (Fig. 22).
Orez. 22. 5% regiune alfa pentru testul direcțional
Utilizarea funcțiilor STUDENT.DIST() sau STUDENT.DIST.PH() implică faptul că ați ales o ipoteză de lucru direcțională. Ipoteza de lucru direcțională combinată cu setarea valorii alfa la 5% înseamnă că plasați toți cei 5% în coada dreaptă a distribuțiilor. Va trebui să respingeți ipoteza nulă doar dacă probabilitatea valorii testului t pe care o obțineți este de 5% sau mai puțin. Ipotezele direcționale au ca rezultat, în general, teste statistice mai sensibile (această sensibilitate mai mare se mai numește și putere statistică mai mare).
Într-un test nedirecționat, valoarea alfa rămâne la același nivel de 5%, dar distribuția va fi diferită. Deoarece trebuie să permiteți două rezultate, probabilitatea unui fals pozitiv trebuie să fie distribuită între cele două cozi ale distribuției. Este în general acceptat să se distribuie această probabilitate în mod egal (Fig. 23).
Folosind aceeași valoare t-test obținută și același număr de grade de libertate ca în exemplul anterior, utilizați formula
STUDENT.DIST.2Х(1,69;34)
Fără un motiv anume, funcția STUDENT.DIST.2X() returnează codul de eroare #NUM dacă i se dă o valoare t-test negativă ca prim argument!
Dacă probele conţin număr diferit date, utilizați testul t cu două eșantioane cu diferite variații incluse în pachet Analiza datelor.
Capitolul 7: Utilizarea regresiei pentru a testa diferențele dintre mediile de grup
Variabilele care au apărut anterior sub numele de variabile predictoare vor fi numite variabile de rezultat în acest capitol, iar termenul de variabile factor va fi folosit în locul termenului de variabile predictor.
Cea mai simplă abordare pentru codificarea unei variabile nominale este codificare dummy(Fig. 24).
Orez. 24. Analiza de regresie bazată pe codificare dummy
Atunci când utilizați codare inactivă de orice fel, trebuie respectate următoarele reguli:
- Numărul de coloane rezervate pentru date noi trebuie să fie egal cu numărul de niveluri de factor minus
- Fiecare vector reprezintă un nivel de factor.
- Subiecții dintr-unul dintre niveluri, care este adesea grupul de control, sunt codificați 0 în toți vectorii.
Formula din celulele F2:H6 =LINEST(A2:A22,C2:D22,;TRUE) returnează statistici de regresie. Pentru comparație, în fig. Figura 24 arată rezultatele ANOVA tradiționale returnate de instrument. ANOVA unidirecțională suplimente Analiza datelor.
Codarea efectelor.Într-un alt tip de codificare numit codificarea efectelor, Media fiecărui grup este comparată cu media medie a grupului. Acest aspect al codificării efectului se datorează utilizării lui -1 în loc de 0 ca cod pentru grup, care primește același cod în toți vectorii de cod (Figura 25).
Orez. 25. Codarea efectelor
Când se folosește codarea inactivă, valoarea constantă returnată de LINEST() este media grupului căruia i se atribuie coduri zero în toți vectorii (de obicei, grupul de referință). În cazul codificării efectelor, constanta este egală cu media generală (celula J2).
General model liniar - mod util conceptualizarea componentelor valorii variabilei rezultate:
Y ij = μ + α j + ε ij
Utilizarea literelor grecești în această formulă în locul literelor latine subliniază faptul că se referă la populația din care sunt extrase probe, dar poate fi rescrisă pentru a indica faptul că se referă la eșantioane extrase dintr-o anumită populație:
Y ij = Y̅ + a j + e ij
Ideea este că fiecare observație Y ij poate fi privită ca suma următoarelor trei componente: media mare, μ; efectul tratamentului j și j; valoarea e ij, care reprezintă abaterea indicatorului cantitativ individual Y ij de la valoarea combinată a mediei generale și a efectului j-al-lea tratament(Fig. 26). Scopul ecuației de regresie este de a minimiza suma pătratelor reziduurilor.
Orez. 26. Observații descompuse în componente ale unui model liniar general
Analiza factorilor. Dacă relația dintre variabila rezultat și doi sau mai mulți factori este studiată simultan, atunci în acest caz vorbim despre utilizarea analizei factoriale. Adăugarea unuia sau mai multor factori la un ANOVA unidirecțional poate crește puterea statistică. În analiza unidirecțională a varianței, varianța variabilei de rezultat care nu poate fi atribuită unui factor este inclusă în pătratul mediu rezidual. Dar se poate ca această variație să fie legată de un alt factor. Apoi această variație poate fi îndepărtată din eroarea pătratică medie, o scădere a căreia duce la o creștere a valorilor testului F și, prin urmare, la o creștere a puterii statistice a testului. Suprastructură Analiza datelor include un instrument care prelucrează doi factori simultan (Fig. 27).
Orez. 27. Instrument Analiza bidirecțională a varianței cu repetări ale pachetului de analize
Instrumentul ANOVA utilizat în această figură este util deoarece returnează media și varianța variabilei rezultat, precum și valoarea contorului, pentru fiecare grup inclus în proiect. In masa Analiza variatiei afișează doi parametri care nu sunt prezenți în rezultatul versiunii cu un singur factor a instrumentului ANOVA. Acordați atenție surselor de variație ProbăȘi Coloaneîn rândurile 27 şi 28. Sursa de variaţie Coloane se referă la gen. Sursa variației Probă se referă la orice variabilă ale cărei valori ocupă diverse coarde. În fig. 27 de valori pentru grupul KursLech1 sunt în rândurile 2-6, grupul KursLech2 este în rândurile 7-11, iar grupul KursLechZ este în rândurile 12-16.
Principalul punct este că ambii factori, Gen (eticheta Coloane în celula E28) și Tratament (eticheta Probă în celula E27), sunt incluși în tabelul ANOVA ca surse de variație. Mijloacele pentru bărbați sunt diferite de mijloacele pentru femei și acest lucru creează o sursă de variație. Mijloacele pentru cele trei tratamente diferă, de asemenea, oferind o altă sursă de variație. Există, de asemenea, o a treia sursă, Interacțiunea, care se referă la efectul combinat al variabilelor Gen și tratament.
Capitolul 8. Analiza covarianței
Analiza covarianței sau ANCOVA (Analiza covariației), reduce părtinirea și crește puterea statistică. Permiteți-mi să vă reamintesc că una dintre modalitățile de a evalua fiabilitatea ecuația de regresie sunt teste F:
F = regresia MS/MS rezidual
unde MS (Pătrat mediu) este pătratul mediu, iar indicii de regresie și rezidual indică componentele de regresie și, respectiv, reziduale. MS rezidual este calculat folosind formula:
MS Residual = SS Residual / df Residual
unde SS (Suma pătratelor) este suma pătratelor, iar df este numărul de grade de libertate. Când adăugați covarianță la o ecuație de regresie, o parte din suma totală a pătratelor este inclusă nu în SS ResiduaI, ci în SS Regression. Aceasta duce la o scădere a SS Residual și, prin urmare, MS Residual. Cu cât este mai mic MS Residual, cu atât testul F este mai mare și cu atât este mai probabil să respingi ipoteza nulă a lipsei de diferență între medii. Ca rezultat, redistribuiți variabilitatea variabilei rezultat. În ANOVA, atunci când covarianța nu este luată în considerare, variabilitatea devine eroare. Dar în ANCOVA, o parte din variabilitatea atribuită anterior termenului de eroare este atribuită unei covariate și devine parte a regresiei SS.
Luați în considerare un exemplu în care același set de date este analizat mai întâi cu ANOVA și apoi cu ANCOVA (Figura 28).
Orez. 28. Analiza ANOVA indică faptul că rezultatele obținute din ecuația de regresie sunt nesigure
Studiul compară efectele relative ale exercițiilor fizice, care îmbunătățesc forța musculară, și ale exercițiilor cognitive (realizarea de cuvinte încrucișate), care stimulează activitatea creierului. Subiectele au fost la întâmplare distribuite în două grupe astfel încât la începutul experimentului ambele grupuri se aflau în aceleași condiții. După trei luni, performanța cognitivă a subiecților a fost măsurată. Rezultatele acestor măsurători sunt prezentate în coloana B.
Intervalul A2:C21 conține datele sursă transmise funcției LINEST() pentru a efectua analiza utilizând codificarea efectelor. Rezultatele funcției LINEST() sunt date în intervalul E2:F6, unde celula E2 afișează coeficientul de regresie asociat vectorului de impact. Celula E8 conține testul t = 0,93, iar celula E9 testează fiabilitatea acestui test t. Valoarea conținută în celula E9 indică faptul că probabilitatea de a întâlni diferența dintre grupe înseamnă observată în acest experiment, este de 36% dacă mediile grupului sunt egale în populație. Puțini consideră că acest rezultat este semnificativ statistic.
În fig. Figura 29 arată ce se întâmplă atunci când adăugați o covariabilă la analiză. În acest caz, am adăugat vârsta fiecărui subiect la setul de date. Coeficientul de determinare R2 pentru ecuația de regresie care utilizează covariata este 0,80 (celula F4). Valoarea R2 în intervalul F15:G19, în care am replicat rezultatele ANOVA obținute fără covariată, este de numai 0,05 (celula F17). Prin urmare, o ecuație de regresie care include covariata prezice valorile pentru variabila Scor cognitiv mult mai precis decât folosind doar vectorul Impact. Pentru ANCOVA, probabilitatea de a obține din întâmplare valoarea testului F afișată în celula F5 este mai mică de 0,01%.
Orez. 29. ANCOVA readuce cu totul altă imagine
Pentru teritoriile regiunii sunt furnizate date pentru 200X.
| Numărul regiunii | Salariul mediu pe cap de locuitor pe zi al unei persoane apte de muncă, rub., x | Salariul mediu zilnic, rub., a |
|---|---|---|
| 1 | 78 | 133 |
| 2 | 82 | 148 |
| 3 | 87 | 134 |
| 4 | 79 | 154 |
| 5 | 89 | 162 |
| 6 | 106 | 195 |
| 7 | 67 | 139 |
| 8 | 88 | 158 |
| 9 | 73 | 152 |
| 10 | 87 | 162 |
| 11 | 76 | 159 |
| 12 | 115 | 173 |
Exercițiu:
1. Construiți un câmp de corelație și formulați o ipoteză despre forma conexiunii.
2. Calculați parametrii ecuației de regresie liniară
4. Dați folosind coeficientul de elasticitate mediu (general). evaluare comparativă puterea relației dintre factor și rezultat.
7. Calculați valoarea estimată a rezultatului dacă valoarea estimată a factorului crește cu 10% față de nivelul său mediu. Determinați intervalul de încredere al prognozei pentru nivelul de semnificație.
Soluţie:
Să decidem aceasta sarcina folosind Excel.
1. Comparând datele disponibile x și y, de exemplu, ierarhându-le în ordinea crescătoare a factorului x, se poate observa prezența unei relații directe între caracteristici, atunci când o creștere a nivelului mediu de existență pe cap de locuitor crește media zilnică. salariu. Pe baza acestui fapt, putem presupune că relația dintre caracteristici este directă și poate fi descrisă printr-o ecuație în linie dreaptă. Aceeași concluzie este confirmată pe baza analizei grafice.
Pentru a construi un câmp de corelare, puteți utiliza Excel PPP. Introduceți datele inițiale în succesiune: mai întâi x, apoi y.
Selectați zona celulelor care conține date.
Atunci alege: Inserare / Scatter Plot / Scatter with Markers așa cum se arată în figura 1.
Figura 1 Construcția câmpului de corelație
Analiza câmpului de corelație arată prezența unei dependențe apropiate de liniară, deoarece punctele sunt situate aproape în linie dreaptă.
2. Să se calculeze parametrii ecuației de regresie liniară
hai să folosim sistemul încorporat functie statistica LINEST.
Pentru aceasta:
1) Deschide fișier existent, cuprinzând datele analizate;
2) Selectați o zonă de 5x2 de celule goale (5 rânduri, 2 coloane) pentru a afișa rezultatele statistici de regresie.
3) Activați Expertul de funcții: în meniul principal selectați Formule / Funcție de inserare.
4) În fereastră Categorie iei Statistic, în fereastra de funcții - LINEST. Faceți clic pe butonul Bine așa cum se arată în Figura 2;
Figura 2 Caseta de dialog Function Wizard
5) Completați argumentele funcției:
Valori cunoscute pentru
Valorile cunoscute ale lui x
Constant- o valoare logică care indică prezența sau absența unui termen liber în ecuație; dacă Constant = 1, atunci termenul liber se calculează în mod obișnuit, dacă Constant = 0, atunci termenul liber este 0;
Statistici- o valoare logică care indică dacă se afișează sau nu informații suplimentare despre analiza de regresie. Dacă Statistica = 1, atunci Informații suplimentare este afișat, dacă Statistics = 0, atunci sunt afișate doar estimări ale parametrilor ecuației.
Faceți clic pe butonul Bine;
Figura 3 Caseta de dialog LINEST Function Arguments
6) Primul element al tabelului final va apărea în celula din stânga sus a zonei selectate. Pentru a deschide întregul tabel, apăsați tasta
Statisticile de regresie suplimentare vor fi afișate în ordinea prezentată în diagrama următoare:
| Valoarea coeficientului b | Valoarea coeficientului a |
| Eroare standard b | Eroare standard a |
| Eroare standard y | |
| F-statistică | |
| Suma de regresie a pătratelor
|
Figura 4 Rezultatul calculării funcției LINEST
Am obținut nivelul de regresie:
Concluzionăm: Cu o creștere a nivelului mediu de existență pe cap de locuitor cu 1 rub. salariul mediu zilnic crește cu o medie de 0,92 ruble.
Aceasta înseamnă că 52% din variația salariilor (y) se explică prin variația factorului x - salariul mediu pe cap de locuitor, iar 48% - prin acțiunea altor factori neincluși în model.
Folosind coeficientul de determinare calculat, coeficientul de corelație poate fi calculat: 
.
Legătura este evaluată ca fiind strânsă.
4. Folosind coeficientul de elasticitate mediu (general), determinăm puterea influenței factorului asupra rezultatului.
Pentru o ecuație în linie dreaptă, determinăm coeficientul de elasticitate mediu (total) folosind formula:
Vom găsi valorile medii selectând zona celulelor cu valori x și selectând Formule / AutoSum / Medieși vom face același lucru cu valorile lui y.
Figura 5 Calculul valorilor medii ale funcției și argument
Astfel, dacă costul mediu al vieții pe cap de locuitor se modifică cu 1% față de valoarea sa medie, salariul mediu zilnic se va modifica cu o medie de 0,51%.
Utilizarea unui instrument de analiză a datelor Regresia disponibil:
- rezultatele statisticilor de regresie,
- rezultatele analizei varianței,
- rezultatele intervalelor de încredere,
- grafice de ajustare a liniilor reziduale și de regresie,
- reziduuri și probabilitate normală.
Procedura este după cum urmează:
1) verificați accesul la Pachet de analize. În meniul principal, selectați: Fișier/Opțiuni/Suplimente.
2) În lista derulantă Control selectați elementul Suplimente Excel și apăsați butonul Merge.
3) În fereastră Suplimente bifeaza casuta Pachet de analize, apoi faceți clic pe butonul Bine.
Dacă Pachet de analize nu în lista de câmpuri Suplimente disponibile, apasa butonul Revizuire pentru a efectua o căutare.
Dacă primiți un mesaj care indică faptul că pachetul de analiză nu este instalat pe computer, faceți clic pe da pentru a-l instala.
4) În meniul principal, selectați: Date / Analiza datelor / Instrumente de analiză / Regresie, apoi faceți clic pe butonul Bine.
5) Completați caseta de dialog pentru parametrii de intrare și ieșire a datelor:
Intervalul de intrare Y- interval care conține date ale atributului rezultat;
Intervalul de intrare X- interval care conține date ale caracteristicii factorului;
Etichete- un steag care indică dacă prima linie conține nume de coloane sau nu;
Constanta - zero- un steag care indică prezența sau absența unui termen liber în ecuație;
Interval de ieșire- este suficient să indicați celula din stânga sus a intervalului viitor;
6) Foaie de lucru nouă - puteți specifica un nume arbitrar pentru noua foaie.
Apoi faceți clic pe butonul Bine.
Figura 6 Caseta de dialog pentru introducerea parametrilor pentru instrumentul de regresie
Rezultatele analizei de regresie pentru datele problemei sunt prezentate în Figura 7.
Figura 7 Rezultatul utilizării instrumentului de regresie
5. Să evaluăm calitatea ecuațiilor folosind eroarea medie de aproximare. Să folosim rezultatele analizei de regresie prezentate în Figura 8.
Figura 8 Rezultatul utilizării instrumentului de regresie „Retragerea restului”
Să compunem masa noua așa cum se arată în Figura 9. În coloana C, calculăm eroarea relativă de aproximare folosind formula:
Figura 9 Calculul erorii medii de aproximare
Eroarea medie de aproximare se calculează folosind formula:
Calitatea modelului construit este evaluată ca bună, deoarece nu depășește 8 - 10%.
6. Din tabelul cu statistici de regresie (Figura 4) notăm valoarea reală a testului F Fisher: 
Deoarece 
la un nivel de semnificație de 5%, atunci putem concluziona că ecuația de regresie este semnificativă (relația a fost dovedită).
8. Evaluare semnificație statistică Vom efectua parametrii de regresie folosind statisticile t ale lui Student și calculând intervalul de încredere al fiecărui indicator.
Propunem ipoteza H 0 despre o diferență nesemnificativă statistic între indicatori și zero:
.
pentru numărul de grade de libertate
Figura 7 are valorile t-statistice reale:
Testul t pentru coeficientul de corelație poate fi calculat în două moduri:
Metoda I:
Unde 
- eroare aleatorie coeficient de corelație.
Vom lua datele pentru calcul din tabelul din Figura 7.
Metoda II:
Valorile t-statistice reale depășesc valorile tabelului:
Prin urmare, ipoteza H 0 este respinsă, adică parametrii de regresie și coeficientul de corelație nu diferă de zero întâmplător, ci sunt semnificative statistic.
Intervalul de încredere pentru parametrul a este definit ca
Pentru parametrul a, limitele de 95%, așa cum se arată în Figura 7, au fost:
Intervalul de încredere pentru coeficientul de regresie este definit ca
Pentru coeficientul de regresie b, limitele de 95%, așa cum se arată în Figura 7, au fost:
Analiza vârfului și limite inferioare intervalele de încredere conduce la concluzia că cu probabilitate 
parametrii a și b, fiind în limitele specificate, nu acceptă valori zero, adică nu sunt nesemnificative statistic și semnificativ diferite de zero.
7. Estimările obţinute ale ecuaţiei de regresie permit utilizarea acesteia pentru prognoză. Dacă costul estimat al vieții este:
Atunci valoarea estimată a costului vieții va fi:
Calculăm eroarea de prognoză folosind formula:
Unde 
De asemenea, vom calcula varianța folosind Excel PPP. Pentru aceasta:
1) Activați Expertul de funcții: în meniul principal selectați Formule / Funcție de inserare.
3) Completați intervalul care conține datele numerice ale caracteristicii factorului. Clic Bine.
Figura 10 Calculul varianței
Avem valoarea varianței 
Pentru a calcula varianța reziduală pe grad de libertate, vom folosi rezultatele analizei varianței așa cum se arată în Figura 7.
Intervalele de încredere pentru prezicerea valorilor individuale ale lui y cu o probabilitate de 0,95 sunt determinate de expresia:
Intervalul este destul de larg, în primul rând din cauza volumului mic de observații. În general, prognoza pentru salariul mediu lunar s-a dovedit a fi credibilă.
Starea problemei este preluată din: Atelier de econometrie: Proc. indemnizatie / I.I. Eliseeva, S.V. Kurysheva, N.M. Gordeenko și alții; Ed. I.I. Eliseeva. - M.: Finanţe şi Statistică, 2003. - 192 p.: ill.
Metoda regresiei liniare ne permite să descriem o linie dreaptă care se potrivește cel mai bine unei serii de perechi ordonate (x, y). Ecuația pentru o linie dreaptă, cunoscută ca ecuație liniară, este prezentat mai jos:
ŷ este valoarea așteptată a lui y at valoarea stabilită X,
x este variabila independentă,
a este un segment pe axa y pentru o linie dreaptă,
b este panta dreptei.
Figura de mai jos ilustrează acest concept grafic:
Figura de mai sus arată linia descrisă de ecuația ŷ =2+0,5x. Intersecția cu y este punctul în care linia intersectează axa y; în cazul nostru, a = 2. Panta dreptei, b, raportul dintre creșterea dreptei și lungimea dreptei, are o valoare de 0,5. O pantă pozitivă înseamnă că linia se ridică de la stânga la dreapta. Dacă b = 0, linia este orizontală, ceea ce înseamnă că nu există nicio relație între variabilele dependente și independente. Cu alte cuvinte, modificarea valorii lui x nu afectează valoarea lui y.
ŷ și y sunt adesea confundate. Graficul prezintă 6 perechi ordonate de puncte și o dreaptă, conform ecuației date
Această figură arată punctul corespunzător perechii ordonate x = 2 și y = 4. Rețineți că valoarea așteptată a lui y conform dreptei de la X= 2 este ŷ. Putem confirma acest lucru cu următoarea ecuație:
ŷ = 2 + 0,5х =2 +0,5(2) =3.
Valoarea y reprezintă punctul real, iar valoarea ŷ este valoarea așteptată a lui y folosind o ecuație liniară pentru o valoare dată a lui x.
Următorul pas este determinarea ecuației liniare care se potrivește cel mai bine cu mulțimea de perechi ordonate, despre asta am vorbit în articolul anterior, unde am determinat tipul de ecuație prin .
Utilizarea Excel pentru a defini regresia liniară
Pentru a utiliza instrumentul de analiză de regresie încorporat în Excel, trebuie să activați programul de completare Pachet de analize. Îl puteți găsi făcând clic pe filă Fișier -> Opțiuni(2007+), în caseta de dialog care apare Opțiuniexcela accesați fila Suplimente.În câmp Control alege Suplimenteexcelași faceți clic Merge.În fereastra care apare, bifați caseta de lângă Pachet de analize, clic BINE.
În fila Date in grup Analiză va aparea buton nou Analiza datelor.
Pentru a demonstra funcționarea suplimentului, vom folosi date în care un tip și o fată împart o masă în baie. Introduceți datele din exemplul nostru de cadă în coloanele A și B ale foii goale.
Accesați fila Date, in grup Analiză clic Analiza datelor.În fereastra care apare Analiza datelor Selectați Regresia așa cum se arată în figură și faceți clic pe OK.
Setați parametrii necesari de regresie în fereastră Regresia, așa cum se arată în imagine:
Clic BINE.În figura de mai jos sunt prezentate rezultatele obținute:
Aceste rezultate sunt în concordanță cu cele pe care le-am obținut făcând propriile calcule în .
Pachetul MS Excel vă permite să faceți cea mai mare parte a muncii foarte rapid atunci când construiți o ecuație de regresie liniară. Este important să înțelegeți cum să interpretați rezultatele obținute. Pentru a construi un model de regresie, trebuie să selectați Tools\Data Analysis\Regression (în Excel 2007 acest mod se află în blocul Date/Data Analysis/Regression). Apoi copiați rezultatele într-un bloc pentru analiză.
Linia de regresie este o reflectare grafică a relației dintre fenomene. Puteți construi foarte clar o linie de regresie în Excel.
Pentru a face acest lucru aveți nevoie de:
1.Deschideți Excel
2.Creați coloane de date. În exemplul nostru, vom construi o linie de regresie, sau o relație, între agresivitate și îndoială de sine la elevii de clasa întâi. La experiment au participat 30 de copii, datele sunt prezentate în tabelul Excel:
1 coloană - numărul subiectului
2 coloana - agresivitateîn puncte
3 coloana - neîncredereaîn puncte
3. Apoi trebuie să selectați ambele coloane (fără numele coloanei), faceți clic pe tab introduce , alege loc , și alegeți-l pe primul dintre machetele propuse punct cu markere .
4. Deci avem un șablon pentru linia de regresie - așa-numita - diagramă de dispersie. Pentru a merge la linia de regresie, faceți clic pe figura rezultată și apăsați pe tab constructor, găsiți pe panou layout-uri grafice și alegeți M A ket9 , se mai spune f(x)
5. Deci, avem o linie de regresie. Graficul arată, de asemenea, ecuația și pătratul coeficientului de corelație
6. Mai rămâne doar să adăugați numele graficului și numele axelor. De asemenea, dacă doriți, puteți elimina legenda, reduceți numărul linii orizontale grile (tab aspect , apoi net ). Modificările și setările de bază sunt făcute în filă Aspect
Linia de regresie a fost construită în MS Excel. Acum îl puteți adăuga la textul lucrării.
