Modelarea regresiei în excel. Regresia liniară simplă

26.08.2019 Windows Phone

Analiza de regresie în Microsoft Excel - cel mai cuprinzător ghid de utilizare a MS Excel pentru a rezolva problemele de analiză de regresie în domeniul inteligenței de afaceri. Konrad Karlberg explică problemele teoretice într-un mod accesibil, cunoașterea cărora vă va ajuta să evitați multe greșeli atât atunci când efectuați singur analizele de regresie, cât și atunci când evaluați rezultatele analizelor efectuate de alte persoane. Tot materialul, de la corelații simple și teste t până la analiza multiplă a covarianței, se bazează pe exemple din lumea reală și este însoțit de proceduri detaliate pas cu pas.

Cartea discută specificul și controversele din jurul funcțiilor de regresie ale Excel, examinează implicațiile fiecărei opțiuni și argument și explică modul de aplicare fiabilă a metodelor de regresie într-o varietate de domenii, de la cercetarea medicală la analiza financiară.

Konrad Karlberg. Analiza de regresie în Microsoft Excel. - M .: Dialectica, 2017 .-- 400 p.

Descărcați nota în format sau, exemple în format

Capitolul 1. Estimarea variabilității datelor

Există mulți indicatori de variație (variabilitate) la dispoziția statisticienilor. Una dintre ele este suma pătratelor abaterilor valorilor individuale de la medie. Excel folosește funcția SQUARE () pentru acesta. Dar varianța este folosită mai des. Varianta este pătratul mediu al abaterilor. 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 dimensiuni).

Excel oferă două funcții care returnează variația: VAR.G () și VAR.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 te interesează.
Utilizați funcția VAR.In () dacă valorile care trebuie procesate formează un eșantion dintr-o populație mai mare. Se presupune că există valori suplimentare, a căror varianță o puteți estima.

Dacă o cantitate, cum ar fi o medie sau un coeficient de corelație, este calculată pe baza populației, atunci se numește parametru. O valoare similară calculată pe baza unui eșantion se numește statistică. Numărarea abaterilor din medieîn acest set, veți obține suma pătratelor abaterilor de o magnitudine 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ă un singur eșantion cu o dimensiune mai mică decât dimensiunea populației generale, față de care să fii sigur că valoarea statisticii coincide cu valoarea parametrului.

Să presupunem că aveți un set de 100 de valori de creștere a căror medie diferă de media populației, oricât de mică ar fi diferența. Prin calcularea varianței pentru eșantion, veți obține o valoare, să spunem 4. Această valoare este mai mică decât oricare alta care poate fi obținută prin calcularea abaterii fiecăreia dintre cele 100 de valori de creștere în raport cu orice valoare, alta decât eșantionul. medie, inclusiv și relativ la media reală a populației generale. Prin urmare, varianța calculată va diferi și într-o măsură mai mică de varianța pe care ați obține dacă ați cunoaște și utiliza cumva nu media eșantionului, ci un parametru al populației generale.

Suma medie a pătratelor eșantionului oferă estimarea inferioară a varianței populației. Varianta calculată în acest fel se numește deplasat evaluare. Rezultă că pentru a elimina părtinirea și pentru a obține o estimare imparțială, este suficient să împărțim suma pătratelor abaterilor nu la n, Unde n este dimensiunea eșantionului și mai departe n - 1.

Magnitudinea n - 1 numit numărul (numărul) gradelor de libertate. Există diferite moduri de calculare a 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.B () este următoarea:

În funcția VARP.G (), suma pătratelor este împărțită la numărul de observații și, prin urmare, reprezintă estimarea părtinitoare a varianței, adevărata medie.
În funcția VAR În (), suma pătratelor se împarte la numărul de observații minus 1, adică. de 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.

Abaterea standard (rus. deviație standard, SD) este rădăcina pătrată a varianței:

Pătratarea abaterilor transformă scara de măsurare într-o altă metrică, care este pătratul originalului: metri - în metri pătrați, dolari - în dolari pătrați etc. Abaterea standard este rădăcina pătrată a varianței și, prin urmare, ne întoarce la unitățile noastre originale. Ceea ce este mai convenabil.

Este adesea necesar să se calculeze abaterea standard după o anumită manipulare a datelor. Ș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 eroare standard de măsurare, eroare standard de proporție, eroare standard de medie.

Să presupunem că colectați date privind înălțimea de la 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 obținute, la rândul lor, pot fi considerate observații. Din aceasta, puteți calcula abaterea lor standard, adică eroarea standard a mediei... Orez. 1. Compară distribuția a 1250 de valori individuale de bază (date privind înălțimea a 25 de bărbați în fiecare dintre cele 50 de state) cu distribuția valorilor medii a 50 de state. 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 de referință; n- numărul de observații din eșantion.

Orez. 1. Variația valorilor medii de la stat la stat este semnificativ mai mică decât variația observațiilor individuale

În statistică, există un acord cu privire la utilizarea literelor grecești și latine pentru a desemna cantități statistice. Se obișnuiește să se desemneze parametrii populației generale cu litere grecești, iar statisticile eșantionului în latină. Prin urmare, când vorbim despre abaterea standard a populației generale, o scriem ca σ; dacă se consideră abaterea standard a eșantionului, atunci folosim notația s. În ceea ce privește simbolurile pentru desemnarea mediilor, acestea nu sunt de acord bine între ele. Media populației este notată 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 observației în distribuție în unități de abatere standard. De exemplu, z = 1,5 înseamnă că observația este de 1,5 abateri standard de la medie în direcția valorilor mai mari. Termen scorul z utilizate pentru evaluări individuale, de ex. pentru dimensiunile atribuite articolelor individuale din eșantion. Pentru astfel de statisticieni (de exemplu, media de stat), utilizați termenul valoarea z:

unde X̅ este media eșantionului, μ este media populației generale, este eroarea standard a mediei setului de eșantioane:

unde σ este eroarea standard a populației generale (măsurători individuale), n Este dimensiunea eșantionului.

Să presupunem că ești instructor la un club de golf. Ai avut ocazia de a măsura intervalul de mult timp și știi că media este de 205 de metri, iar abaterea standard este de 36 de metri. Vi s-a oferit un nou club, susținând că vă va crește raza de acțiune cu 10 metri. Cereți fiecăruia dintre următorii 81 de participanți la club să încerce o lovitură de test cu un nou club și să-și înregistreze intervalul. S-a dovedit că raza medie de lovire a noului club a fost de 215 de metri. Care este probabilitatea ca diferența de 10 metri (215 - 205) să se datoreze exclusiv erorii de eșantionare? Sau, altfel spus: care este probabilitatea ca, atunci când este testat la scară mai mare, noul club să nu prezinte o creștere a razei de lovire peste media existentă pe termen lung de 205 de metri?

Putem testa acest lucru prin generarea unei valori z. Eroarea standard a mediei:

Atunci valoarea z este:

Trebuie să găsim probabilitatea ca media eșantionului să fie de 2,5σ de media populației. Dacă probabilitatea este mică, atunci diferențele nu se datorează aleatoriei, ci calității noului club. Excel nu are o funcție gata făcută pentru a determina probabilitatea unui scor z. Cu toate acestea, puteți utiliza formula = 1-NORM.ST.DIST (valoare z; TRUE), unde NORM.ST.DIST () returnează aria de sub curba normală la stânga valorii z (Figura 2) .

Orez. 2. Funcția NORM.S.DIST () returnează aria de sub curba la stânga valorii z; pentru a mări imaginea, faceți clic dreapta pe ea și selectați Deschideți 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 (μ) și abaterea standard (σ) ale populației nu sunt cunoscute, se utilizează valoarea t (vezi detalii). Structurile valorii z și t diferă prin aceea că abaterea standard s obținută din rezultatele eșantionului este utilizată pentru a găsi valoarea t, mai degrabă decât valoarea cunoscută a parametrului populației σ. Curba normală are o singură formă, iar forma distribuției valorilor t variază în funcție de numărul de grade de libertate df (din engleză. grade de libertate) din proba pe care o reprezintă. Numărul de grade de libertate ale eșantionului este 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 t a lui Student: STUDENT.DIST () returnează aria de sub curba la stânga unei valori t date, iar STUDENT.DIST.PX () returnează aria de sub curba curba la dreapta.

Capitolul 2. Corelația

Corelația este o măsură a relației dintre elementele unui set de perechi ordonate. Corelația se caracterizează prin 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 NSși Y, S xy- covarianta:

În această formulă, covarianța este împărțită la abaterile standard ale variabilelor NSș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 unor funcții precum STDEV (), DISP () sau COVARIATION (). Deși coeficientul de corelație pentru eșantion este o estimare părtinitoare, motivul părtinirii este diferit de cel în cazul varianței sau abaterii standard.

În funcție de valoarea coeficientului general de corelație (deseori notat cu litera greacă ρ ), coeficient de corelație r oferă o estimare părtinitoare, cu efectul părtinirii crescând odată cu scăderea dimensiunii eșantionului. Cu toate acestea, nu încercăm să corectăm această părtinire în același mod în care, de exemplu, am făcut-o când am calculat abaterea standard, când am înlocuit nu numărul de observații, ci numărul de grade de libertate în formula corespunzătoare. De fapt, 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 înrudite liniar. Neliniaritatea și/sau erorile în date (outliers) conduc la calcularea incorectă a coeficientului de corelație. Se recomandă utilizarea diagramelor de dispersie pentru a diagnostica problemele de date. 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, pe de altă parte, definește una dintre coloane ca o axă a categoriilor, ceea ce distorsionează imaginea datelor (Fig. 4).

Orez. 4. Liniile de regresie par a fi aceleași, dar comparați ecuațiile lor.

Observațiile utilizate pentru a reprezenta graficul cu linii sunt echidistante de-a lungul axei orizontale. Marcajele de-a lungul acestei axe sunt doar etichete, nu valori numerice.

În timp ce corelația înseamnă adesea cauzalitate, nu poate fi folosită ca dovadă că este. Statisticile nu sunt folosite pentru a demonstra dacă o teorie este adevărată sau falsă. Pentru a exclude explicațiile concurente ale rezultatelor observației, pune experimente planificate... Statisticile sunt folosite pentru a rezuma informațiile adunate în cursul unor astfel de experimente și pentru a cuantifica probabilitatea ca o decizie luată să fie greșită, având în vedere baza de dovezi 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 o oarecare precizie) valoarea necunoscută a unei variabile din valoarea cunoscută a celeilalte variabile. . Pentru a obține valori estimate ale prețului pe baza datelor prezentate în Fig. 5, puteți utiliza oricare dintre mai multe metode posibile, dar aproape sigur nu o veți folosi pe cea prezentată în Fig. 5. Ar trebui să fii familiarizat, totuși, pentru că nicio altă metodă nu poate demonstra relația dintre corelație și predicție la fel de clar ca aceasta. În fig. 5, în intervalul B2: C12, este prezentat un eșantion aleatoriu de zece case și este dat suprafața (în metri pătrați) a fiecărei case și prețul de vânzare al acesteia.

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 EZ 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: = EZ * $ B $ 18. Convertiți scorurile z în prețuri în dolari (H2: H12). În celula НЗ, formula este: = F3 * $ C $ 15 + $ C $ 14.

Vă rugăm să rețineți că valoarea prezisă tinde întotdeauna să se miște spre media egală cu 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, i.e. 0,67. Aceasta corespunde excesului valorii peste medie, egal cu două treimi din abaterea standard. Dacă coeficientul de corelație ar fi egal cu 0,5, atunci prețul prezis ar fi 1,0 * 0,5, adică 0,5. Aceasta corespunde excesului valorii peste medie, egal cu doar jumătate din abaterea standard. Ori de câte ori valoarea coeficientului de corelație diferă de ideal, i.e. mai mare de -1,0 și mai mică de 1,0, estimarea variabilei prezise ar trebui să fie mai aproape de medie decât estimarea variabilei (independente) de predicție față de propria sa. Acest fenomen se numește regresie la medie sau pur și simplu regresie.

Excel are mai multe funcții pentru determinarea coeficienților ecuației liniilor de regresie (în Excel se numește linie de tendință) y =kx + b... Pentru determinare k servește funcției

= PANTĂ (y_cunoscute, x_cunoscute)

Aici la Este variabila prezisă și NS Este variabila independentă. Trebuie să urmați cu strictețe această ordine a variabilelor. Panta de regresie, coeficientul de corelație, abaterile standard ale variabilelor și covarianța sunt strâns legate (Figura 6). INTERCEPT () returnează valoarea tăiată de linia de regresie pe axa verticală:

= INTERCEPTARE (y_cunoscute, x_cunoscute)

Orez. 6. Raportul dintre abaterile standard convertește covarianța în coeficientul de corelație și panta dreptei de regresie

Rețineți că numărul de valori x și y furnizate funcțiilor SLOPE () și INTERCEPT () ca argumente trebuie să fie același.

În analiza de regresie, se folosește un alt indicator important - R 2 (R-pătrat), sau coeficientul de determinare. Ea determină modul în care relația dintre NSși la... Excel are funcția KVPIRSON (), care ia 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 o varianță explicată. De obicei, varianța explicată este exprimată ca procent. Asa de R 2 = 0,81 înseamnă că 81% din varianța (spread) celor 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 ():

ia valorile cunoscute pe care le furnizați NSși valori cunoscute la;
calculează panta dreptei de regresie și o constantă (segment);
returnează valorile prezise la determinată prin aplicarea ecuaţiei de regresie la valorile cunoscute NS(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 TILT () și INTERCEPT ()

Pentru a introduce funcția TREND () ca formulă matrice în celulele G3: G12, selectați intervalul G3: G12, introduceți formula TREND (SZ: C12; OZ: B12), apăsați și mențineți apăsate tastele și abia apoi apăsați tasta ... Rețineți că formula este închisă între acolade: (și). Acesta este modul în care Excel vă informează că formula dată este interpretată ca o formulă matrice. Nu introduceți parantezele în sine: dacă încercați să le introduceți singur ca parte a unei formule, Excel vă va interpreta intrarea ca un șir de text obișnuit.

Funcția TREND () are încă două argumente: new_x'sși const... Primul vă permite să preziceți viitorul, în timp ce cel din urmă poate forța linia de regresie să treacă prin origine (TRUE spune Excel să folosească o constantă calculată, 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 reprezenta un grafic de dispersie și apoi faceți clic dreapta pe unul dintre marcatorii seriei de date. Selectați elementul din meniul contextual deschis Adăugați linia de tendință; selectați opțiunea 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, excluzâ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 orașului 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ș. Este, desigur, foarte greu să găsești celelalte 50 de orașe cu exact același nivel de bunăstare.

Folosind tehnici statistice pentru a elimina impactul bunăstării atât asupra sprijinului financiar pentru biblioteci, cât și asupra disponibilității educației universitare, s-ar putea să puteți cuantifica mai precis relația dintre variabilele de interes, și anume numărul de cărți și numărul de absolvenți. Această corelație condiționată între două variabile, atunci când valorile celorlalte variabile sunt fixe, se numește corelație parțială. O modalitate de calcul este să utilizați ecuația:

Unde rCB . W- coeficientul de corelație dintre variabilele Colegiu și Cărți cu influența exclusă (valoare fixă) a variabilei Avere; rCB- coeficientul de corelație dintre variabilele Colegiu și Cărți; rCW- coeficientul de corelare dintre variabilele Colegiu și Bunăstare; rBw este coeficientul de corelație dintre 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 observațiile reale asociate (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 de analiză Excel (meniu 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 care se deschide Opțiuniexcela găsi câmpul Control, Selectați Suplimenteexcela, faceți clic Mergi la... Bifați caseta de lângă supliment Pachet de analize... Faceți clic pe A analiza datelor, selecteaza o optiune Corelație... Introduceți $ B $ 2: $ D $ 13 ca interval de intrare, bifați caseta Etichete pe prima linie, introduceți $ B $ 16: $ E $ 19 ca interval de ieșire.

O altă posibilitate este definirea unei corelații semi-private. De exemplu, cercetați efectul înălțimii și vârstei asupra greutății. Deci, aveți două variabile de predicție, înălțimea și vârsta, și o variabilă de predicție, greutatea. Doriți să eliminați influența unei variabile predictive asupra alteia, dar nu asupra variabilei prezise:

unde H este înălțimea, W este greutatea, A este vârsta; în indicele coeficientului de corelaţie semiprivată se folosesc paranteze, cu ajutorul cărora se indică influenţa cărei variabile se elimină şi din ce anume variabilă. În acest caz, notația W (NA) indică faptul că efectul variabilei Vârstă este eliminat din variabila Înălțime, dar nu și din variabila Greutate.

S-ar putea avea impresia că problema în discuție nu este semnificativă. La urma urmei, cel mai important lucru este cât de exact funcționează ecuația generală de regresie, în timp ce problema contribuțiilor relative ale variabilelor individuale la varianța totală explicată pare să fie de importanță secundară. Cu toate acestea, acesta nu este cazul. Odată ce începeți să vă întrebați dacă să utilizați o variabilă în ecuația de regresie multiplă, problema devine importantă. Poate influența evaluarea corectitudinii alegerii modelului pentru analiză.

Capitolul 4. Funcția LINEST ().

LINEST () returnează 10 statistici de regresie. 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 apăsați (fig. 9):

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) apăsați

LINEA () returnează:

coeficientul de regresie (sau panta, celula D2);
segment (sau constantă, celulă E3);
erori standard ale coeficientului de regresie și constantelor (domeniul D3: E3);
coeficientul de determinare R2 pentru regresie (celula D4);
eroarea standard a estimării (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ă aruncăm o privire 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țind coeficientul de regresie la eroarea standard, obțineți 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 este zero, atunci media variabilei prezise este cea mai bună estimare. Două erori standard și jumătate este un număr destul de mare ș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 folosind funcția

STUDENT.DIST.RF (test t = 2,559; 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 1% rotunjit. Ne spune că dacă coeficientul de regresie al populației este 0%, atunci probabilitatea de a obține un eșantion de 20 de persoane pentru care coeficientul de regresie estimat este 2,092 este de 1%.

Testul F (celula D5 din Figura 9) îndeplinește aceeași funcție în ceea ce privește regresia completă ca și testul t în ceea ce privește coeficientul de regresie simplă pe perechi. Testul F este utilizat pentru a testa dacă coeficientul de determinare R 2 pentru regresie este suficient de mare pentru a respinge ipoteza că are o valoare de 0,0 în populația generală, ceea ce indică faptul că nu există nicio varianță explicată de predictor și prezisă. variabil. Dacă există o singură variabilă predictivă, testul F este exact egal cu pătratul testului t.

Până acum, ne-am uitat la variabilele de interval. Dacă aveți variabile care pot lua mai multe valori care reprezintă nume simple, de exemplu, Bărbat și Femeie sau Reptile, Amfibieni și Pești, reprezentați-le ca un cod numeric. Astfel de variabile se numesc nominale.

Statistica R 2 cuantifică proporția de varianță explicată.

Eroarea standard a estimării.În fig. 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 exact, aceste reziduuri sunt numite erori - de unde termenul de eroare standard a estimării.

Orez. 10. Atât R 2 cât și eroarea standard a estimării exprimă acuratețea predicțiilor 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 orice predicție făcută de ecuație se va potrivi cu observația reală pe care o așteptați. Eroarea standard a unei estimări 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-statistici Este raportul dintre variația intergrup și varianța intragrup. Acest nume a fost inventat de statisticianul George Snedecor în onoarea lui Sir, care a dezvoltat Analysis of Variance (ANOVA) la începutul secolului al XX-lea.

Coeficientul de determinare R 2 exprimă proporția din suma totală de pătrate asociată regresiei. Valoarea (1 - R 2) exprimă fracția din suma totală a pătratelor asociate cu reziduuri - erori de predicție. Scorul F poate fi obținut folosind LINEST (celula F5 din Figura 11), folosind sume de pătrate (interval G10: J11), folosind fracții de varianță (interval G14: J15). Formulele pot fi examinate în fișierul Excel atașat.

Orez. 11. Calculul criteriului F

Când se utilizează variabile nominale, se folosește codarea inactivă (Fig. 12). Pentru codificarea valorilor, este convenabil să folosiți valorile 0 și 1. Probabilitatea F este calculată folosind funcția:

F.DIST.RF (K2; I2; I3)

Aici funcția F.DIST.RT () returnează probabilitatea de a obține un criteriu F care respectă distribuția F centrală (Figura 13) pentru două seturi de date cu grade de libertate date în celulele I2 și I3, a căror valoare este aceeași 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, de exemplu, coloanele A și B pentru doi predictori sau A, B și C pentru 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 unei regresii cu două variabile predictoare, se poate folosi următoarea structură:

LINIE (A2: A41; B2: C41 ;; ADEVĂRAT)

La fel și în cazul a trei variabile:

LINEST (A2: A61; B2: D61 ;; TRUE)

Să presupunem că doriți să studiați posibilele efecte ale vârstei și ale dietei asupra LDL, o lipoproteină cu densitate scăzută despre care se crede că este responsabilă pentru placa de ateroscleroză care provoacă aterotromboza (Figura 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 în același timp. În acest caz, R2 crește aproape întotdeauna.

Pentru orice ecuație de regresie liniară simplă cu o variabilă predictivă, va exista întotdeauna o corelație perfectă între valorile prezise și valorile variabilei predictoare, deoarece într-o astfel de ecuație valorile predictorului sunt înmulțite cu o constantă și se adaugă o altă constantă. la fiecare produs. Acest efect nu persistă în regresia multiplă.

Afișează rezultatele returnate de LINEST () pentru regresia multiplă (Figura 15). Coeficienții de regresie sunt afișați ca parte a rezultatelor returnate de LINEST () în ordinea inversă a variabilelor(G – H – I corespunde C – B – A).

Orez. 15. Coeficienții și erorile standard ale acestora sunt afișați în ordine inversă pe foaia de lucru.

Principiile și procedurile utilizate în analiza de regresie cu o variabilă 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 o parte dintr-un DIST, cum ar fi STUDENT.DIST () și F.DIST (), iau un test t sau F ca argument și returnează probabilitatea de a vedea valoarea specificată. Funcțiile ale căror nume includ o parte a OBR, cum ar fi STUDENT.INV () și F.OBR (), iau o valoare de probabilitate ca argument și returnează o valoare de criteriu corespunzătoare probabilității specificate.

Deoarece căutăm valorile critice ale distribuției t care tăie marginile regiunilor sale de coadă, trecem 5% ca argument uneia dintre funcțiile STUDENT.OBR (), care returnează o valoare corespunzătoare acestei probabilitate (Fig. 17, 18).

Orez. 17. Testul t cu două cozi

Orez. 18. Test t cu o coadă

Prin stabilirea unei reguli de decizie în cazul unei regiuni alfa cu o singură coadă, creșteți puterea statistică a testului. Dacă, atunci când începeți să experimentați, sunteți sigur 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ă luați decizia corectă, respingând ipoteza unui coeficient de regresie zero în populație, va fi mai mare.

Statisticienii preferă să folosească termenul test de direcțieîn locul termenului test cu o singură coadă iar termenul test nedirecționatîn locul termenului test cu coadă dublă... Termenii direcțional și nedirecțional sunt preferați deoarece ei subliniază mai degrabă tipul de ipoteză decât natura coziilor de distribuție.

O abordare bazată pe model pentru evaluarea influenței predictorilor.În fig. 19 prezintă rezultatele unei analize de regresie care testează contribuția variabilei Diet la ecuația de regresie.

Orez. 19. Compararea celor două modele prin verificarea diferențelor rezultatelor acestora

Rezultatele LINEST () (intervalul H2: K6) se referă la ceea ce eu numesc modelul complet, care regresează LDL față de Dietă, Vârstă și HDL. În intervalul H9: J13, calculele sunt prezentate 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 este explicată de variabilele predictoare. În modelul limitat, doar 30,8% din LDL se explică prin variabilele Vârstă și HDL. Pierderea R 2 din cauza excluderii variabilei Dieta din model este 0,183. În intervalul G15: L17 se fac calcule care arată că doar cu o probabilitate de 0,0288 influența variabilei Dieta este aleatorie. În restul de 97,1%, dieta are un efect asupra LDL.

Capitolul 6. Ipoteze și avertismente ale analizei de regresie

Termenul „presupune” nu este strict definit, 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. De fapt, nu este cazul, deși, desigur, există cazuri în care încălcarea ipotezei schimbă radical 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 dependente liniar de valorile X; c) varianța reziduurilor este aproximativ aceeași în fiecare punct X; d) nu există dependenţă între reziduuri.

Dacă ipotezele nu joacă un rol semnificativ, statisticienii vorbesc despre robustețea analizei în raport cu î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 este esențială: testele sunt robuste împotriva încălcării ipotezei de normalitate. Acestea fiind spuse, este important să analizați datele folosind diagrame. De exemplu, inclus în supliment Analiza datelor instrument Regresia.

Dacă datele dumneavoastră nu se potrivesc cu ipotezele regresiei liniare, aveți abordări diferite decât regresia liniară. Una dintre ele este regresia logistică (Figura 20). În apropierea valorilor limită superioară și inferioară ale variabilei predictoare, regresia liniară duce la predicții nerealiste.

Orez. 20. Regresie logistică

În fig. Figura 6.8 prezintă rezultatele a două metode de analiză a datelor care vizează investigarea 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ă diferența dintre rezultatele care prezic probabilitatea de a cumpăra o casă folosind regresia liniară și rezultatele pe care le puteți obține folosind o abordare diferită.

În limbajul statistician, eliminarea unei ipoteze nule atunci când este de fapt adevărată se numește eroare de tip I.

În suprastructură Analiza datelor oferă un instrument convenabil pentru generarea de numere aleatorii, oferind utilizatorului posibilitatea de a specifica forma dorită a distribuției (de exemplu, Normală, Binomială sau Poisson), precum și media și abaterea standard.

Diferențele între funcțiile familiei STUDENT.DIST ().Începând cu Excel 2010, există trei forme diferite ale unei funcții care returnează proporția unei distribuții 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 cazuri, deci numărul de grade de libertate de analizat este 34 și testul t este 1,69. În acest caz, formula

STUDENT DIST (+1,69; 34; ADEVĂRAT)

returnează 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 testului t specificat, exprimată ca o fracție. Dacă este FALS, funcția returnează înălțimea relativă a curbei în punctul care îndeplinește testul t. Alte versiuni ale funcției STUDENT.DIST () - STUDENT.DIST.PX () și STUDENT.DIST.2X () - iau drept argumente doar valoarea criteriului t și numărul de grade de libertate și nu necesită specificarea celui de-al treilea argument.

Orez. 21. Zona umbrită mai întunecată din coada stângă a distribuției corespunde fracțiunii ariei de sub curba din stânga valorii pozitive mari a testului t

Pentru a determina zona din dreapta criteriului t, utilizați una dintre formulele:

1 - DISTANȚA ȘODENTULUI (1, 69; 34; ADEVĂRAT)

STUD.DIST.PH (1,69; 34)

Întreaga zonă de sub curbă trebuie să fie de 100%, deci scăzând 1 din aria din stânga testului t, pe care o returnează funcția, se obține aria din dreapta testului t. S-ar putea să găsești de preferat să obții direct fracția de zonă care te interesează folosind funcția STUDENT.DIST.RX (), unde RH reprezintă coada dreaptă a distribuției (Fig. 22).

Orez. 22,5% zonă alfa pentru testarea direcțională

Utilizarea funcțiilor STUDENT.DIST () sau STUDENT.DIST.PX () presupune că ați ales o ipoteză de lucru direcționată. O ipoteză de lucru direcțională combinată cu o setare alfa de 5% înseamnă că puneți toate 5% în coada dreaptă a distribuțiilor. Va trebui să respingeți ipoteza nulă doar dacă probabilitatea valorii testului t este de 5% sau mai puțin. Ipotezele direcționale conduc de obicei la 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 același test t obținut și același număr de grade de libertate ca în exemplul anterior, utilizați formula

STUD.DIST.2X (1,69; 34)

Fără un motiv anume, STUDENT.DIST.2X () returnează codul de eroare #NUM! dacă i se dă un test t negativ ca prim argument.

Dacă eșantioanele conțin cantități diferite de 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 menționate anterior ca variabile prezise vor fi denumite variabile de rezultat în acest capitol, iar termenul de variabile factori va fi folosit în locul variabilelor predictoare.

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 de un nivel, care este adesea un grup de control, primesc un cod de 0 în toți vectorii.

Formula din celulele F2: H6 = LINEST (A2: A22; C2: D22 ;; TRUE) returnează statistici de regresie. Pentru comparație, Fig. 24 arată rezultatele ANOVA tradiționale returnate de instrument Analiza unidirecțională a varianței suprastructuri Analiza datelor.

Codarea efectelor.Într-un alt tip de codificare numit efecte de codare, media fiecărui grup este comparată cu media mediei grupului. Acest aspect al codificării efectului se datorează utilizării lui -1 în loc de 0 ca cod pentru un grup care primește același cod în toți vectorii de cod (Figura 25).

Orez. 25. Efecte de codare

Când se utilizează codificarea inactivă, valoarea constantă returnată de LINEST () este media grupului căruia îi sunt atribuite codurile zero în toți vectorii (de obicei grupul de control). În cazul efectelor de codare, constanta este egală cu media totală (celula J2).

Modelul liniar general este o modalitate utilă de a conceptualiza componentele valorii variabilei rezultate:

Y ij = μ + α j + ε ij

Folosirea literelor grecești în locul literelor latine în această formulă subliniază faptul că se referă la populația din care sunt extrase probele, dar poate fi rescrisă pentru a indica că se referă la eșantioanele extrase din populația publicată:

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 generală, μ; efectul de procesare j și j; valoarea lui e ij, care reprezintă abaterea indicatorului cantitativ individual Y ij de la valoarea combinată a mediei totale și efectul tratamentului j-a (Fig. 26). Scopul ecuației de regresie este de a minimiza suma pătratelor reziduurilor.

Orez. 26. Observații descompuse în componente ale modelului liniar general

Analiza factorilor. Dacă relația dintre variabila efectivă și doi sau mai mulți factori este investigată simultan, atunci în acest caz se vorbește despre utilizarea analizei factoriale. Adăugarea unuia sau mai multor factori la ANOVA univariată poate crește puterea statistică. În ANOVA univariată, variația unei variabile 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 tricotată cu un alt factor. Apoi, această variație poate fi eliminată din eroarea pătratică medie, o scădere care duce la o creștere a valorilor criteriului F și, prin urmare, la o creștere a puterii statistice a testului. Suprastructură Analiza datelor include un instrument care asigură prelucrarea a doi factori în același timp (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 prin faptul că returnează media și varianța variabilei rezultat, precum și valoarea contorului pentru fiecare grup inclus în plan. In masa ANOVA sunt afișați doi parametri care nu sunt prezenți în rezultatul instrumentului ANOVA unidirecțional. Acordați atenție surselor de variație Probăși Coloane pe rândurile 27 și 28. Sursa de variație Coloane se referă la gen. Sursa de variație Probă se referă la orice variabilă ale cărei valori sunt pe linii diferite. În fig. 27 de valori pentru grupul KursLech1 sunt în rândurile 2-6, pentru grupul KursLech2 - în rândurile 7-11, iar pentru grupul KursLechZ - în rândurile 12-16.

Punctul cheie este că atât Genul (titlu Coloanele din celula E28) cât și Tratamentul (titrarea Eșantion în celula E27) sunt incluse în tabelul ANOVA ca surse de variație. Mediile pentru bărbați sunt diferite de mediile pentru femei, iar acest lucru creează o sursă de variație. Mijloacele pentru cele trei tratamente diferă și ele - iată 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 covariației (ANCOVA) reduce părtinirea și crește puterea statistică. Permiteți-mi să vă reamintesc că una dintre modalitățile de a evalua fiabilitatea unei ecuații de regresie este testele 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. Calculul MS Residual se efectuează conform formulei:

MS Residual = SS Residual / df Residual

unde SS (Suma pătratelor) este suma pătratelor și 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 scăzut MS Residual, cu atât este mai mare scorul F și cu atât mai probabil vei respinge ipoteza nulă că nu există diferențe între medii. Ca rezultat, redistribuiți volatilitatea variabilei rezultate. În ANOVA, atunci când covarianța nu este luată în considerare, volatilitatea devine eroare. Dar în ANCOVA, o parte din variabilitatea atribuită anterior erorii este atribuită covariatei ș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. ANOVA indică că rezultatele ecuației de regresie nu sunt de încredere

Studiul a comparat efectele relative ale exercițiilor fizice, care dezvoltă forța musculară, și ale exercițiilor cognitive (realizarea de cuvinte încrucișate), care stimulează activitatea creierului. Subiecții au fost repartizați aleatoriu în două grupuri, astfel încât ambele grupuri să fie în aceleași condiții la începutul experimentului. După trei luni, au fost măsurate caracteristicile cognitive ale subiecților. Rezultatele acestor măsurători sunt prezentate în coloana B.

Intervalul A2: C21 conține datele inițiale transmise funcției LINEST () pentru a efectua analiza folosind efecte de codare. Rezultatele funcției LINEST () sunt afișate în intervalul E2: F6, unde celula E2 afișează coeficientul de regresie asociat cu vectorul stimul. Celula E8 conține un test t = 0,93, iar celula E9 testează fiabilitatea acestui test t. Valoarea din celula E9 indică faptul că probabilitatea de a întâlni o diferență între mediile grupului observate în acest experiment este de 36%, dacă în populația generală mediile grupului sunt egale. Puțini consideră acest rezultat semnificativ statistic.

În fig. 29 arată ce se întâmplă atunci când covariabile sunt adăugate la analiză. În acest caz, am adăugat vârsta fiecărui subiect la setul de date. Coeficientul de determinare, R 2, pentru ecuația de regresie care utilizează covariata este 0,80 (celula F4). Valoarea R 2 în intervalul F15: G19, în care am reprodus rezultatele ANOVA obținute fără utilizarea covariatei, este doar 0,05 (celula F17). Prin urmare, o ecuație de regresie care include covariata prezice valorile variabilei Scor cognitiv mult mai precis decât folosind doar vectorul Impact. Pentru ANCOVA, probabilitatea de a obține aleatoriu scorul F afișat în celula F5 este mai mică de 0,01%.

Orez. 29. ANCOVA readuce cu totul altă imagine

Prelucrarea statistică a datelor poate fi efectuată și folosind programul de completare PACHET DE ANALIZĂ(fig. 62).

Dintre articolele propuse, el alege articolul " REGRESIE„Și faceți clic pe el cu butonul stâng al mouse-ului. Apoi faceți clic pe OK.

Fereastra prezentată în fig. 63.

Instrument de analiză" REGRESIE»Este folosit pentru a potrivi un grafic pentru un set de observații folosind metoda celor mai mici pătrate. Regresia este utilizată pentru a analiza efectul asupra unei variabile dependente individuale al valorilor uneia sau mai multor variabile explicative. De exemplu, mai mulți factori afectează performanța atletică a unui atlet, inclusiv vârsta, înălțimea și greutatea. Puteți calcula impactul fiecăruia dintre acești trei factori asupra performanței unui atlet și apoi utilizați acele date pentru a prezice performanța altui sportiv.

Instrumentul de regresie folosește funcția LINIST.

Caseta de dialog REGRESIUNE

Etichete Selectați caseta de validare dacă primul rând sau prima coloană a intervalului de intrare conține anteturi. Debifați această casetă de validare dacă nu există titluri. În acest caz, anteturile adecvate pentru datele din tabelul de ieșire vor fi generate automat.

Nivel de încredere Selectați caseta de selectare pentru a include un nivel suplimentar în tabelul cu totaluri de ieșire. În câmpul corespunzător, introduceți nivelul de fiabilitate de aplicat, în plus față de nivelul implicit de 95%.

Constant - zero Selectați caseta de selectare pentru a face ca linia de regresie să treacă prin origine.

Spațiere ieșire Introduceți o referință la celula din stânga sus a intervalului de ieșire. Alocați cel puțin șapte coloane pentru tabelul cu totaluri de ieșire, care va include: rezultate ANOVA, coeficienți, eroarea standard de calcul Y, abaterile standard, numărul de observații, erori standard pentru coeficienți.

Foaie de lucru nouă Selectați acest comutator pentru a deschide o nouă foaie de lucru în registrul de lucru și inserați rezultatele analizei începând din celula A1. Dacă este necesar, introduceți un nume pentru noua foaie în câmpul opus poziției corespunzătoare a comutatorului.

Nou registru de lucru Faceți clic pe comutatorul în această poziție pentru a crea un nou registru de lucru în care rezultatele vor fi adăugate la o nouă foaie.

Reziduuri Selectați caseta de selectare pentru a include reziduurile în tabelul de ieșire.

Reziduuri standardizate Selectați caseta de selectare pentru a include reziduurile standardizate în tabelul de ieșire.

Plot Residuals Selectați caseta de selectare pentru a reprezenta un grafic reziduurile pentru fiecare variabilă independentă.

Fitting plot Selectați caseta de selectare pentru a reprezenta graficul valorilor prezise în raport cu valorile observate.

Graficul de probabilitate normală Bifați caseta pentru a reprezenta graficul probabilității normale.

Funcţie LINIST

Pentru a efectua calcule, selectați celula în care dorim să afișam valoarea medie cu cursorul și apăsați tasta = de pe tastatură. Apoi, în câmpul Nume, indicați funcția dorită, de exemplu IN MEDIE(fig. 22).

Funcţie LINIST calculează statistici pentru o serie folosind cele mai mici pătrate pentru a calcula linia dreaptă care se potrivește cel mai bine cu datele disponibile, apoi returnează o matrice care descrie linia dreaptă rezultată. De asemenea, puteți combina funcția LINIST cu alte funcții pentru a calcula alte tipuri de modele care sunt liniare în parametri necunoscuți (ai căror parametri necunoscuți sunt liniari), inclusiv serii polinomiale, logaritmice, exponențiale și de putere. Deoarece este returnată o matrice de valori, funcția trebuie specificată ca formulă matrice.

Ecuația unei linii drepte este următoarea:

y = m 1 x 1 + m 2 x 2 +… + b (în cazul mai multor intervale de valori x),

unde valoarea dependentă y este o funcție a valorii independente x, valorile m sunt coeficienții corespunzători fiecărei variabile x independente, iar b este o constantă. Rețineți că y, x și m pot fi vectori. Funcţie LINIST returnează o matrice (mn; mn-1;…; m 1; b). LINIST poate returna, de asemenea, statistici de regresie suplimentare.

LINIST(cunoscute_y; cunoscute_x; const; statistici)

Known_y's este mulțimea de valori y care sunt deja cunoscute pentru relația y = mx + b.

Dacă cunoscutul_y are o coloană, atunci fiecare coloană din cunoscutul_x este interpretată ca o variabilă separată.

Dacă cunoscutul_y are un singur rând, atunci fiecare rând din cunoscutul_x este interpretat ca o variabilă separată.

Known_x-urile sunt un set opțional de valori x care sunt deja cunoscute pentru y = mx + b.

Known_x-urile pot conține unul sau mai multe seturi de variabile. Dacă este folosită o singură variabilă, atunci cunoscute_y și cunoscute_x pot fi de orice formă, atâta timp cât au aceeași dimensiune. Dacă se utilizează mai mult de o variabilă, cunoscute_y trebuie să fie un vector (adică un rând înălțime sau o lățime de coloană).

Dacă array_cunoscut_x este omis, atunci această matrice (1; 2; 3; ...) se presupune a fi de aceeași dimensiune cu array_cunoscut_y.

Const este o valoare booleană care indică dacă constanta b trebuie să fie 0.

Dacă const este TRUE sau omis, constanta b este evaluată în mod obișnuit.

Dacă argumentul „const” este FALS, atunci valoarea lui b este setată egală cu 0 și valorile lui m sunt selectate în așa fel încât relația y = mx să fie satisfăcută.

Statistics este o valoare booleană care indică dacă doriți să returnați statistici suplimentare pentru regresie.

Dacă statisticile este TRUE, LINEST returnează statistici de regresie suplimentare. Matricea returnată va arăta astfel: (mn; mn-1; ...; m1; b: sen; sen-1; ...; se1; seb: r2; sey: F; df: ssreg; ssresid).

Dacă statistica este FALSĂ sau omisă, LINEST returnează numai coeficienții m și constanta b.

Statistici suplimentare de regresie (Tabelul 17).

Magnitudinea	Descriere
se1, se2, ..., sen	Valori de eroare standard pentru coeficienții m1, m2, ..., mn.
seb	Valoarea erorii standard pentru constanta b (seb = # N / A dacă const este FALS).
r2	Coeficientul de determinism. Valorile reale ale y sunt comparate cu valorile obținute din ecuația dreptei; pe baza rezultatelor comparației se calculează coeficientul de determinism, normalizat de la 0 la 1. Dacă este egal cu 1, atunci există o corelație completă cu modelul, adică nu există nicio diferență între efectivul și cel estimat. valorile lui y. În caz contrar, dacă coeficientul de determinism este 0, nu are sens să folosești o ecuație de regresie pentru a prezice valorile y. Pentru mai multe informații despre cum se calculează r2, consultați „Observații” de la sfârșitul acestei secțiuni.
sey	Eroarea standard pentru estimarea lui y.
F	F-statistică sau F-valoare observată. Statistica F este utilizată pentru a determina dacă relația observată între variabilele dependente și independente este aleatorie.
df	Grade de libertate. Gradele de libertate sunt utile pentru găsirea valorilor critice F într-un tabel statistic. Pentru a determina nivelul de fiabilitate al modelului, comparați valorile din tabel cu statistica F returnată de LINEST. Pentru mai multe informații despre calcularea df, consultați Observațiile de la sfârșitul acestei secțiuni. Exemplul 4 de mai jos arată utilizarea valorilor F și df.
ssreg	Suma de regresie a pătratelor.
ssresid	Suma reziduală a pătratelor. Pentru mai multe informații despre calcularea valorilor ssreg și ssresid, consultați Observațiile de la sfârșitul acestei secțiuni.

Figura de mai jos arată ordinea în care sunt returnate statisticile suplimentare de regresie (Figura 64).

Note:

Orice linie dreaptă poate fi descrisă prin panta ei și prin intersecția cu axa y:

Panta (m): pentru a determina panta unei linii drepte, de obicei notată cu m, trebuie să luați două puncte ale dreptei (x 1, y 1) și (x 2, y 2); panta va fi (y 2 -y 1) / (x 2 -x 1).

Intersecția Y (b): Intersecția y a unei linii, de obicei notat cu b, este valoarea y a punctului în care linia intersectează axa y.

Ecuația dreptei are forma y = mx + b. Dacă cunoașteți valorile lui m și b, puteți calcula orice punct de pe linie înlocuind valorile y sau x din ecuație. De asemenea, puteți utiliza funcția TREND.

Dacă există o singură variabilă independentă x, puteți obține direct panta și intersecția cu y folosind următoarele formule:

Pantă: INDEX (LINIE (y_cunoscute; x_cunoscute); 1)

Intersecția în Y: INDEX (LINEST (cunoscute_y; cunoscute_x); 2)

Precizia aproximării liniei LINEST depinde de gradul de împrăștiere a datelor. Cu cât datele sunt mai aproape de o linie dreaptă, cu atât modelul LINEST este mai precis. LINEST folosește metoda celor mai mici pătrate pentru a determina cea mai bună potrivire la date. Când există o singură variabilă independentă x, m și b sunt calculate folosind următoarele formule:

unde x și y sunt medii eșantion, de exemplu x = MEDIE (cunoscute_x) și y = MEDIE (cunoscute_y).

Funcțiile de potrivire LINEST și LOGEST pot calcula curba dreaptă sau exponențială care descrie cel mai bine datele. Cu toate acestea, ele nu răspund la întrebarea care dintre cele două rezultate este mai potrivit pentru rezolvarea sarcinii în cauză. De asemenea, puteți calcula TREND (y_cunoscute; x_cunoscute) pentru o linie dreaptă sau CREȘTERE (y_cunoscute; x cunoscute) pentru o curbă exponențială. Aceste funcții, dacă nu specificați new_x_values, returnează o matrice de valori y calculate pentru valorile x reale de-a lungul unei linii drepte sau unei curbe. Valorile calculate pot fi apoi comparate cu valorile reale. De asemenea, puteți crea diagrame pentru comparație vizuală.

Cu analiza de regresie, Microsoft Excel calculează, pentru fiecare punct, pătratul diferenței dintre valoarea y prezisă și valoarea y reală. Suma acestor diferențe pătrate se numește suma reziduală a pătratelor (ssresid). Microsoft Excel calculează apoi suma totală de pătrate (sstotal). Dacă const = TRUE sau omis, suma totală a pătratelor este egală cu suma pătratelor diferenței dintre valorile reale y și valorile medii y. Când const = FALS, suma totală a pătratelor va fi egală cu suma pătratelor valorilor reale ale lui y (fără a scădea valoarea medie a lui y din valoarea coeficientului lui y). Suma de regresie a pătratelor poate fi calculată după cum urmează: ssreg = sstotal - ssresid. Cu cât suma reziduală a pătratelor este mai mică, cu atât valoarea coeficientului de determinism r2 este mai mare, ceea ce arată cât de bine explică relația dintre variabile ecuația obținută cu ajutorul analizei de regresie. Coeficientul r2 este ssreg / sstotal.

În unele cazuri, una sau mai multe coloane X (să fie valorile Y și X în coloane) nu au o valoare predicativă suplimentară în celelalte coloane X. Cu alte cuvinte, ștergerea uneia sau mai multor coloane X poate avea ca rezultat valori Y calculat cu aceeasi precizie. În acest caz, coloanele X redundante vor fi excluse din modelul de regresie. Acest fenomen se numește „colinearitate” deoarece coloanele X redundante pot fi reprezentate ca suma a mai multor coloane neredundante. LINEST verifică coliniaritatea și elimină orice coloane X redundante din modelul de regresie dacă le găsește. Coloanele X șterse pot fi identificate în rezultatul LINEST printr-un factor de 0 și o valoare se de 0. Eliminarea uneia sau mai multor coloane ca redundante modifică valoarea df deoarece depinde de numărul de coloane X utilizate efectiv în scopuri predictive. Pentru mai multe informații despre calcularea df, consultați exemplul 4 de mai jos. Când df se modifică din cauza eliminării coloanelor redundante, sey și F se modifică și ele. Coliniaritatea este adesea descurajată. Cu toate acestea, ar trebui utilizat dacă unele dintre coloanele X conțin 0 sau 1 ca indicator care indică dacă subiectul experimentului se află într-un grup separat. Dacă const = TRUE sau omis, LINEST inserează o coloană X suplimentară pentru a simula punctul de intersecție. Dacă există o coloană cu valori de 1 pentru bărbați și 0 pentru femei și există și o coloană cu valori de 1 pentru femei și 0 pentru bărbați, atunci ultima coloană este eliminată deoarece valorile sale pot fi obţinut din coloana cu „indicatorul de sex masculin”.

Calculul df pentru cazurile în care coloanele lui X nu sunt eliminate din model din cauza coliniarității este după cum urmează: dacă există k coloane de cunoscute_x și valoarea lui const = TRUE sau nu este specificată, atunci df = n - k - 1. Dacă const = FALS, atunci df = n - k. În ambele cazuri, eliminarea coloanelor X din cauza coliniarității crește valoarea df cu 1.

Formulele care returnează matrice trebuie introduse ca formule matrice.

Când introduceți o matrice de constante pentru, de exemplu, cunoscute_x, utilizați un punct și virgulă pentru a separa valorile pe aceeași linie și două puncte pentru a separa liniile. Caracterele de separare variază în funcție de opțiunile setate în fereastra Limbă și standarde de pe panoul de control.

Trebuie remarcat faptul că valorile y prezise de ecuația de regresie pot să nu fie corecte dacă se află în afara intervalului de valori y care au fost utilizate pentru a defini ecuația.

Algoritmul principal utilizat în funcție LINIST, diferă de algoritmul principal al funcțiilor ÎNCLINAŢIEși SECȚIUNE... Diferențele dintre algoritmi pot duce la rezultate diferite pentru date nedefinite și coliniare. De exemplu, dacă punctele de date ale cunoscute_y sunt 0 și punctele de date ale cunoscute_x sunt 1, atunci:

Funcţie LINIST returnează o valoare egală cu 0. Algoritmul funcției LINIST este folosit pentru a returna valori valide pentru datele coliniare, caz în care poate fi găsit cel puțin un răspuns.

Funcțiile SLOPE și INTERCEPT returnează eroarea # DIV / 0!. Algoritmul funcției SLOPE și INTERCEPT este folosit pentru a căuta un singur răspuns, iar în acest caz pot exista mai multe.

Pe lângă calcularea statisticilor pentru alte tipuri de regresie, LINEST poate fi utilizat pentru a calcula intervale pentru alte tipuri de regresie prin introducerea funcțiilor lui x și y ca serii de x și y pentru LINEST. De exemplu, următoarea formulă:

LINIE (valori y, valori x ^ COLONA ($ A: $ C))

funcționează având o coloană de valori Y și o coloană de valori X pentru a calcula o aproximare la un cub (polinom de gradul 3) de următoarea formă:

y = m 1 x + m 2 x 2 + m 3 x 3 + b

Formula poate fi modificată pentru a calcula alte tipuri de regresie, dar în unele cazuri sunt necesare ajustări ale valorilor de ieșire și alte statistici.

V excela există o modalitate și mai rapidă și mai convenabilă de a reprezenta un grafic regresia liniară (și chiar tipurile de bază de regresii neliniare, așa cum se discută mai jos). Acest lucru se poate face după cum urmează:

1) selectați coloanele cu date Xși Y(trebuie să fie în ordinea aceea!);

2) sunați Chart Wizardși alegeți în grup Tip de – Punctși apăsați imediat Gata;

3) fără a renunța la selecția din diagramă, selectați elementul apărut din meniul principal Diagramă, în care ar trebui să selectați elementul Adăugați linia de tendință;

4) în caseta de dialog care apare Linie de tendințeîn filă Tip de Selectați Liniar;

5) în filă Opțiuni comutatorul poate fi activat Afișați ecuația în diagramă, care vă va permite să vedeți ecuația de regresie liniară (4.4), în care se vor calcula coeficienții (4.5).

6) În aceeași filă, puteți activa comutatorul Plasați valoarea de încredere a aproximării (R ^ 2) pe diagramă... Această mărime este pătratul coeficientului de corelație (4.3) și arată cât de bine ecuația calculată descrie dependența experimentală. Dacă R 2 este aproape de unitate, atunci ecuația de regresie teoretică descrie bine dependența experimentală (teoria este de acord cu experimentul) și dacă R 2 este aproape de zero, atunci această ecuație nu este potrivită pentru a descrie dependența experimentală (teoria nu este de acord cu experimentul).

Ca urmare a efectuării acțiunilor descrise, veți obține o diagramă cu un grafic de regresie și ecuația acestuia.

§4.3. Principalele tipuri de regresie neliniară

Regresie parabolică și polinomială.

Parabolic dependenta de cantitate Y asupra valorii NS se numește dependență exprimată printr-o funcție pătratică (parabolă de ordinul 2):

Această ecuație se numește ecuația de regresie parabolică Y pe NS... Opțiuni A, b, cu sunt numite coeficienții de regresie parabolic... Calcularea coeficienților de regresie parabolică este întotdeauna greoaie, de aceea se recomandă utilizarea unui computer pentru calcule.

Ecuația (4.8) a regresiei parabolice este un caz special al unei regresii mai generale numită polinom. Polinom dependenta de cantitate Y asupra valorii NS se numește dependență exprimată de polinom n-a comanda:

unde sunt numerele și eu (i=0,1,…, n) sunt numite coeficienții de regresie polinomială.

Regresia puterii.

Exponenţial dependenta de cantitate Y asupra valorii NS dependența formei se numește:

Această ecuație se numește ecuația de regresie a puterii Y pe NS... Opțiuni Ași b sunt numite coeficienții de regresie a puterii.

ln = ln A+b ln X. (4.11)

Această ecuație descrie o dreaptă într-un plan cu axe de coordonate logaritmice ln Xși ln. Prin urmare, criteriul de aplicabilitate a regresiei puterii este cerința ca punctele logaritmilor datelor empirice ln x iși ln i erau cel mai aproape de linia dreaptă (4.11).

Regresie exponențială.

Indicativ(sau exponenţială) prin dependenţa cantităţii Y asupra valorii NS dependența formei se numește:

(sau ). (4,12)

Această ecuație se numește ecuația exponențială(sau exponenţială) regresie Y pe NS... Opțiuni A(sau k) și b sunt numite exponenţială(sau exponenţială) regresii.

Dacă luăm logaritmul ambelor părți ale ecuației de regresie a puterii, obținem ecuația

ln = X ln A+ ln b(sau ln = k x+ ln b). (4.13)

Această ecuație descrie dependența liniară a logaritmului unei mărimi ln față de o altă mărime X... Prin urmare, criteriul de aplicabilitate a regresiei puterii este cerința ca datele empirice să pună în considerare aceeași cantitate. x i iar logaritmii unei alte mărimi ln i erau cel mai aproape de linia dreaptă (4.13).

Regresie logaritmică.

Logaritmic dependenta de cantitate Y asupra valorii NS dependența formei se numește:

=A+b ln X. (4.14)

Această ecuație se numește ecuația de regresie logaritmică Y pe NS... Opțiuni Ași b sunt numite coeficienții de regresie logaritmică.

Regresie hiperbolica.

Hiperbolic dependenta de cantitate Y asupra valorii NS dependența formei se numește:

Această ecuație se numește ecuația de regresie hiperbolică Y pe NS... Opțiuni Ași b sunt numite coeficienții de regresie hiperbolicși sunt determinate prin metoda celor mai mici pătrate. Aplicarea acestei metode conduce la formulele:

În formulele (4.16-4.17), însumarea se realizează peste indice i de la unu la numărul de observaţii n.

Din pacate in excela nu există nicio funcţie care să calculeze coeficienţii regresiei hiperbolice. În cazurile în care nu se știe în prealabil că mărimile măsurate sunt legate prin proporționalitate inversă, se recomandă ca în loc de ecuația de regresie hiperbolică să se caute ecuația de regresie a puterii, ca în excela există o procedură pentru a-l găsi. Dacă se presupune o dependență hiperbolică între valorile măsurate, atunci coeficienții regresiei acesteia vor trebui să fie calculați folosind tabele de calcul auxiliare și operații de însumare folosind formule (4.16-4.17).

Regresia în Excel

Prelucrarea datelor statistice poate fi efectuată și utilizând add-in-ul pachetului de analiză din elementul de meniu „Serviciu”. În Excel 2003, dacă deschideți SERVICIU, nu găsim fila ANALIZA DATELOR, apoi făcând clic pe butonul stâng al mouse-ului deschide fila SUPERSTRUCTURILE si punctul opus PACHET DE ANALIZĂ făcând clic pe butonul stâng al mouse-ului, bifați (Fig. 17).

Orez. 17. Fereastra SUPERSTRUCTURILE

După aceea în meniu SERVICIU apare o filă ANALIZA DATELOR.

În Excel 2007 pentru a instala PACHET DE ANALIZĂ trebuie să faceți clic pe butonul OFFICE din colțul din stânga sus al foii (Fig. 18a). Apoi, faceți clic pe butonul PARAMETRI EXCEL... În fereastra care apare PARAMETRI EXCEL faceți clic stânga pe element SUPERSTRUCTURILE iar în partea dreaptă a listei derulante, selectați elementul PACHET DE ANALIZĂ. Apoi, faceți clic pe Bine.

Opțiuni Excel Buton de birou

Orez. 18. Instalare PACHET DE ANALIZĂîn Excel 2007

Pentru a instala pachetul de analiză, faceți clic pe butonul MERGE, situat în partea de jos a ferestrei deschise. Fereastra prezentată în fig. 12. Pune o căpușă vizavi PACHET DE ANALIZĂ.În fila DATE va apărea un buton ANALIZA DATELOR(fig. 19).

Dintre articolele propuse, el alege articolul " REGRESIE„Și faceți clic pe el cu butonul stâng al mouse-ului. Apoi faceți clic pe OK.

Fereastra prezentată în fig. 21

Instrumentul de regresie folosește funcția LINIST.

Caseta de dialog REGRESIUNE

Constant - zero Selectați caseta de selectare pentru a face ca linia de regresie să treacă prin origine.

Nou registru de lucru Faceți clic pe comutatorul în această poziție pentru a crea un nou registru de lucru în care rezultatele vor fi adăugate la o nouă foaie.

Reziduuri Selectați caseta de selectare pentru a include reziduurile în tabelul de ieșire.

Reziduuri standardizate Selectați caseta de selectare pentru a include reziduurile standardizate în tabelul de ieșire.

Plot Residuals Selectați caseta de selectare pentru a reprezenta un grafic reziduurile pentru fiecare variabilă independentă.

Fitting plot Selectați caseta de selectare pentru a reprezenta graficul valorilor prezise în raport cu valorile observate.

Graficul de probabilitate normală Bifați caseta pentru a reprezenta graficul probabilității normale.

Funcţie LINIST

Orez. 22 Găsirea funcțiilor în Excel 2003

Dacă în câmp NUME numele functiei nu apare, apoi click stanga pe triunghiul de langa camp, dupa care va aparea o fereastra cu o lista de functii. Dacă această funcție nu este în listă, faceți clic stânga pe elementul din listă ALTE FUNCȚII, va apărea o casetă de dialog MAESTRU DE FUNCȚII, în care, folosind derularea verticală, selectați funcția dorită, selectați-o cu cursorul și faceți clic pe Bine(fig. 23).

Orez. 23. Asistent de funcții

Pentru a căuta o funcție în Excel 2007, în meniu se poate deschide orice filă, apoi pentru calcule se selectează celula în care dorim să afișăm valoarea medie și se apasă tasta = de pe tastatură. Apoi, în câmpul Nume, specificați funcția IN MEDIE... Fereastra pentru calcularea funcției este similară cu cea afișată în Excel 2003.

De asemenea, puteți selecta fila Formule și faceți clic stânga pe butonul din meniu " FUNCȚIE DE INSERARE”(Fig. 24), va apărea o fereastră MAESTRU DE FUNCȚII, al cărui tip este similar cu Excel 2003. Tot în meniu puteți selecta imediat categoria de funcții (utilizate recent, financiare, logice, text, dată și oră, matematice, alte funcții), în care vom căuta funcția dorită.

Alte funcții Referințe și tablouri

Matematic

Orez. 24 Selectarea unei funcții în Excel 2007

Ecuația unei linii drepte este următoarea:

(în cazul mai multor intervale de valori x),

unde valoarea dependentă y este o funcție a valorii independente x, valorile m sunt coeficienții corespunzători fiecărei variabile x independente, iar b este o constantă. Rețineți că y, x și m pot fi vectori. Funcţie LINIST returnează o matrice . LINIST poate returna, de asemenea, statistici de regresie suplimentare.

LINIST(cunoscute_y; cunoscute_x; const; statistici)

Known_y's sunt setul de valori y care sunt deja cunoscute pentru relație.

Dacă cunoscutul_y are o coloană, atunci fiecare coloană din cunoscutul_x este interpretată ca o variabilă separată.

Dacă cunoscutul_y are un singur rând, atunci fiecare rând din cunoscutul_x este interpretat ca o variabilă separată.

Known_x-urile sunt un set opțional de valori x care sunt deja cunoscute pentru relație.

Dacă array_cunoscut_x este omis, atunci această matrice (1; 2; 3; ...) se presupune a fi de aceeași dimensiune cu array_cunoscut_y.

Const este o valoare booleană care indică dacă constanta b trebuie să fie 0.

Dacă const este TRUE sau omis, constanta b este evaluată în mod obișnuit.

Dacă argumentul „const” este FALS, atunci valoarea lui b este setată egală cu 0, iar valorile lui m sunt selectate în așa fel încât relația să fie valabilă.

Statistics este o valoare booleană care indică dacă doriți să returnați statistici suplimentare pentru regresie.

Dacă statistica este FALSĂ sau omisă, LINEST returnează numai coeficienții m și constanta b.

Statistici suplimentare de regresie.

Magnitudinea Descriere se1, se2, ..., sen Valori de eroare standard pentru coeficienții m1, m2, ..., mn. seb Valoarea erorii standard pentru constanta b (seb = # N / A dacă const este FALS). r2 Coeficientul de determinism. Valorile reale ale y sunt comparate cu valorile obținute din ecuația dreptei; pe baza rezultatelor comparației se calculează coeficientul de determinism, normalizat de la 0 la 1. Dacă este egal cu 1, atunci există o corelație completă cu modelul, adică nu există nicio diferență între efectivul și cel estimat. valorile lui y. În caz contrar, dacă coeficientul de determinism este 0, nu are sens să folosești o ecuație de regresie pentru a prezice valorile y. Pentru mai multe informații despre cum se calculează r2, consultați „Observații” de la sfârșitul acestei secțiuni. sey Eroarea standard pentru estimarea lui y. F F-statistică sau F-valoare observată. Statistica F este utilizată pentru a determina dacă relația observată între variabilele dependente și independente este aleatorie. df Grade de libertate. Gradele de libertate sunt utile pentru găsirea valorilor critice F într-un tabel statistic. Pentru a determina nivelul de fiabilitate al modelului, comparați valorile din tabel cu statistica F returnată de LINEST. Pentru mai multe informații despre calcularea df, consultați Observațiile de la sfârșitul acestei secțiuni. Exemplul 4 de mai jos arată utilizarea valorilor F și df. ssreg Suma de regresie a pătratelor. ssresid Suma reziduală a pătratelor. Pentru mai multe informații despre calcularea valorilor ssreg și ssresid, consultați Observațiile de la sfârșitul acestei secțiuni.

Figura de mai jos arată ordinea în care sunt returnate statisticile suplimentare de regresie.

Note:

Orice linie dreaptă poate fi descrisă prin panta ei și prin intersecția cu axa y:

Panta (m): Pentru a determina panta unei linii drepte, notată de obicei cu m, trebuie să luați două puncte ale dreptei și; panta va fi .

Intersecția Y (b): Intersecția y a unei linii, de obicei notat cu b, este valoarea y a punctului în care linia intersectează axa y.

Ecuația dreptei are forma. Dacă cunoașteți valorile lui m și b, puteți calcula orice punct de pe linie înlocuind valorile y sau x din ecuație. De asemenea, puteți utiliza funcția TREND.

Dacă există o singură variabilă independentă x, puteți obține direct panta și intersecția cu y folosind următoarele formule:

Pantă: INDEX (LINIE (y_cunoscute; x_cunoscute); 1)

Intersecția în Y: INDEX (LINEST (cunoscute_y; cunoscute_x); 2)

unde x și y sunt medii eșantion, de exemplu x = MEDIE (cunoscute_x) și y = MEDIE (cunoscute_y).

Formulele care returnează matrice trebuie introduse ca formule matrice.

Trebuie remarcat faptul că valorile y prezise de ecuația de regresie pot să nu fie corecte dacă se află în afara intervalului de valori y care au fost utilizate pentru a defini ecuația.

Funcțiile SLOPE și INTERCEPT returnează eroarea # DIV / 0!. Algoritmul funcției SLOPE și INTERCEPT este folosit pentru a căuta un singur răspuns, iar în acest caz pot exista mai multe.

LINIE (valori y, valori x ^ COLONA ($ A: $ C))

funcționează având o coloană de valori Y și o coloană de valori X pentru a calcula o aproximare la un cub (polinom de gradul 3) de următoarea formă:

Formula poate fi modificată pentru a calcula alte tipuri de regresie, dar în unele cazuri sunt necesare ajustări ale valorilor de ieșire și alte statistici.

După părerea mea, ca student, econometria este una dintre cele mai aplicate științe cu care am reușit să mă familiarizez între zidurile universității mele. Cu ajutorul acestuia, într-adevăr, este posibil să se rezolve probleme de natură aplicativă la scara unei întreprinderi. Cât de eficiente vor fi aceste soluții este a treia întrebare. Concluzia este că majoritatea cunoștințelor vor rămâne teorie, dar econometria și analiza de regresie merită totuși studiate cu o atenție deosebită.

Ce explică regresia?

Înainte de a începe să examinăm funcțiile MS Excel care permit rezolvarea acestor probleme, aș dori să vă explic pe degete ce presupune, în esență, analiza de regresie. Acest lucru vă va face mai ușor să treceți examenul și, cel mai important, va fi mai interesant să studiați materia.

Sperăm că sunteți familiarizat cu conceptul de funcție din matematică. O funcție este o relație între două variabile. Când o variabilă se schimbă, se întâmplă ceva cu cealaltă. Schimbăm X și, respectiv, modificările Y. Funcțiile descriu diverse legi. Cunoscând funcția, putem înlocui valorile arbitrare pentru X și să vedem cum se modifică acest lucru pe Y.

Acest lucru este de mare importanță, deoarece regresia este o încercare de a explica procese aparent nesistematice și haotice folosind o anumită funcție. Deci, de exemplu, este posibil să se identifice relația dintre cursul de schimb al dolarului și șomajul în Rusia.

Dacă acest model poate fi detectat, atunci folosind funcția pe care am obținut-o în cursul calculelor, vom putea face o prognoză a ratei șomajului la cursul N-a al dolarului față de rublă.
Această relație se va numi corelație. Analiza de regresie presupune calcularea unui coeficient de corelație care va explica strânsoarea relației dintre variabilele pe care le luăm în considerare (cursul de schimb al dolarului și numărul de locuri de muncă).

Acest raport poate fi pozitiv sau negativ. Valorile sale variază de la -1 la 1. În consecință, putem observa o corelație negativă sau pozitivă ridicată. Dacă este pozitivă, atunci creșterea cursului dolarului va fi urmată de apariția de noi locuri de muncă. Daca este negativ, inseamna ca o crestere a cursului de schimb va fi urmata de o scadere a locurilor de munca.

Există mai multe tipuri de regresie. Poate fi liniar, parabolic, exponențial etc. Alegem un model în funcție de care regresie va corespunde în mod specific cazului nostru, care model va fi cât mai aproape de corelația noastră. Să luăm în considerare acest lucru pe un exemplu de problemă și să o rezolvăm în MS Excel.

Regresia liniară în MS Excel

Pentru a rezolva probleme de regresie liniară, veți avea nevoie de funcționalitatea de analiză a datelor. Este posibil să nu fie activat pentru dvs., așa că trebuie să îl activați.

Faceți clic pe butonul „Fișier”;
Selectăm elementul „Parametri”;
Faceți clic pe penultima filă „Suplimente” din partea stângă;

Mai jos vom vedea inscripția „Control” și butonul „Go”. Apăsăm pe el;
Punem o bifă pe „Pachetul de analiză”;
Apăsăm „ok”.

Sarcină de exemplu

Funcția de analiză a lotului este activată. Să rezolvăm următoarea problemă. Avem un eșantion de date de câțiva ani privind numărul de situații de urgență pe teritoriul întreprinderii și numărul de lucrători angajați. Trebuie să identificăm relația dintre aceste două variabile. Există o variabilă explicativă X - acesta este numărul de lucrători și o variabilă explicată - Y - este numărul de accidente. Să împărțim datele inițiale în două coloane.

Să mergem la fila „date” și să selectăm „Analiza datelor”

În lista care apare, selectați „Regresie”. În intervalele de intrare Y și X, selectați valorile corespunzătoare.

Faceți clic pe „Ok”. Analiza a fost efectuată, iar într-o nouă fișă vom vedea rezultatele.

Cele mai semnificative valori pentru noi sunt marcate în figura de mai jos.

Multiplu R este coeficientul de determinare. Are o formulă de calcul complexă și arată cât de mult poți avea încredere în coeficientul nostru de corelație. În consecință, cu cât această valoare este mai mare, cu atât mai multă încredere, cu atât modelul nostru în ansamblu este mai de succes.

Intersecția Y și Intersecția X1 sunt coeficienții regresiei noastre. După cum am menționat deja, regresia este o funcție și are anumiți coeficienți. Astfel, funcția noastră va arăta astfel: Y = 0,64 * X-2,84.

Ce ne oferă? Acest lucru ne permite să facem o prognoză. Să presupunem că vrem să angajăm 25 de muncitori pentru o companie și trebuie să ne imaginăm aproximativ care va fi numărul de accidente. Inlocuim aceasta valoare in functia noastra si obtinem rezultatul Y = 0,64 * 25 - 2,84. În țara noastră se vor produce aproximativ 13 situații de urgență.

Să vedem cum funcționează. Aruncă o privire la poza de mai jos. Funcția pe care am obținut-o este înlocuită cu valorile reale pentru angajații implicați. Vedeți cât de apropiate sunt valorile de jocurile reale.

De asemenea, puteți construi un câmp de corelare evidențiind zona jucători și xs, făcând clic pe fila „inserare” și alegând un grafic de dispersie.

Punctele sunt împrăștiate, dar în general se deplasează în sus, ca și cum ar fi într-o linie dreaptă în mijloc. De asemenea, puteți adăuga această linie accesând fila „Aspect” din MS Excel și selectând elementul „Linie de tendință”

Faceți dublu clic pe linia care apare și veți vedea ce s-a menționat mai devreme. Puteți schimba tipul de regresie în funcție de cum arată câmpul de corelare.

Poate că simțiți că punctele desenează o parabolă, nu o linie dreaptă și este mai potrivit să alegeți un alt tip de regresie.

Concluzie

Sperăm că acest articol v-a oferit o mai bună înțelegere a ce este analiza de regresie și pentru ce este aceasta. Toate acestea au o mare importanță practică.