Cum să trageți concluzii în econometrie excel. Regresia în Excel

Analiza de regresie în Microsoft Excel- cel mai ghiduri complete despre 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 atunci când efectuați singuri analiza 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ă exemple reale si este insotita descriere detaliata procedurile pas cu pas corespunzătoare.

Cartea discută ciudațiile și controversele asociate cu funcțiile de regresie ale Excel, examinează implicațiile fiecărei opțiuni și argument și explică cum să utilizați în mod fiabil metodele de regresie în cea mai mare măsură. 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ă dintr-o populație se calculează o cantitate, cum ar fi o medie sau un coeficient de corelaț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 înălțimi 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 media reală.populația 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) – este rădăcina pătrată a varianței:

Punerea la pătrat a abaterilor transformă scara de măsurare într-o altă metrică, care este pătratul celei inițiale: 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 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ții ș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, 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 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. 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. Eroare standard in medie:

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 de corelație generală (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țiile utilizate pentru a construi diagrama cu linii sunt dispuse 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 el, pentru că 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. 5 în intervalul B2:C12 arată un eșantion aleatoriu de zece case și oferă date despre suprafața casei de pe plajă (în metri pătrați) și prețul 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 numită CVPIERSON(), 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 varianța explicată. Varianta explicată de obicei este exprimată ca procent. Asa de R 2 = 0,81 înseamnă că 81% din varianța (împrăștierea) 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:S12;V3:B12), apăsați și mențineți apăsate tastele și numai după aceea apăsați tasta . Rețineți că formula este închisă între acolade: ( și ). Așa vă spune Excel această formulă percepută tocmai ca o formulă matrice. Nu introduceți singur parantezele: dacă încercați să le introduceți singur ca parte a unei formule, Excel va trata intrarea dvs. ca pe un șir de text obișnuit.

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 în același timp 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 College și Books; 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 coeficientului 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 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, notația 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 (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) faceți clic

Funcția LINEST() returnează:

coeficientul de regresie (sau panta, celula D2);
segment (sau constantă, celulă E3);
erori standard ale coeficientului 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 este zero, atunci 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 R 2 exprimă proporția din suma totală a pătratelor asociată 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:

LINEST(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 aproape întotdeauna crește.

Pentru orice simplu ecuație liniarăÎntr-o regresie cu o variabilă predictor, 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 o altă constantă este adăugat 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 standard ale acestora sunt afișați î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 dovedește 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ă, atunci când începeți un experiment, 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 din cauza 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 poate invalide. Acesta nu este de fapt cazul, deși există cu siguranță cazuri în care încălcarea unei ipoteze 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 oferă un instrument convenabil pentru generare 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ă găsești de preferat să obții direct fracția de suprafață care te 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

Folosirea 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 toate cele 5% în coada dreaptă a distribuțiilor. Va trebui doar să respingeți ipoteza nulă dacă probabilitatea valorii testului t pe care o obțineți este de 5% sau mai mică. 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ă 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 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 mediei 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).

Model liniar general - 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 al j-lea efect prelucrare (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ă linii diferite. Î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 este mai mare testul F ș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 dezvoltă 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 în mod întâmplător valoarea F-test afișată în celula F5 este mai mică de 0,01%.

Orez. 29. ANCOVA readuce cu totul altă imagine

Prelucrarea datelor statistice poate fi efectuată și folosind un add-on PACHET DE ANALIZĂ(Fig. 62).

Din elementele sugerate, selectați elementul „ REGRESIE" și faceți clic pe el cu butonul stâng al mouse-ului. Apoi, faceți clic pe OK.

Va apărea o fereastră așa cum se arată în Fig. 63.

Instrument de analiză " REGRESIE» este folosit pentru a potrivi un grafic la un set de observații folosind metoda celor mai mici pătrate. Regresia este utilizată pentru a analiza impactul asupra unui individ dependent variabilă de valoare una sau mai multe variabile independente. De exemplu, mai mulți factori influențează performanța atletică a unui atlet, inclusiv vârsta, înălțimea și greutatea. Este posibil să se calculeze gradul în care fiecare dintre acești trei factori influențează performanța unui atlet și apoi să se utilizeze acele date pentru a prezice performanța altui sportiv.

Instrumentul de regresie folosește funcția LINEST.

Caseta de dialog REGRESIUNE

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

Nivel de fiabilitate Bifați caseta de selectare pentru a include un nivel suplimentar în tabelul rezumat de ieșire. În câmpul corespunzător, introduceți nivelul de încredere pe care doriți să îl aplicați, în plus față de nivelul implicit de 95%.

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

Interval de ieșire Introduceți referința la celula din stânga sus a intervalului de ieșire. Furnizați cel puțin șapte coloane pentru tabelul rezumat de ieșire, care va include: rezultate ANOVA, coeficienți, eroarea standard a calculului Y, abaterile standard, numărul de observații, erori standard pentru coeficienți.

Foaie de lucru nouă Setați comutatorul în această poziție pentru a se deschide frunză nouăîn registrul de lucru și lipiți rezultatele analizei începând din celula A1. Dacă este necesar, introduceți un nume pentru noua foaie în câmpul situat vizavi de butonul radio corespunzător.

Nou registrul de lucru Setați 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 validare pentru a include reziduurile standardizate în tabelul de ieșire.

Grafic rezidual Selectați caseta de validare pentru a reprezenta grafic reziduurile pentru fiecare variabilă independentă.

Fit Plot Selectați caseta de validare pentru a reprezenta graficul dintre valorile estimate și cele observate.

Graficul de probabilitate normală Bifați caseta de selectare pentru a reprezenta un grafic de probabilitate normală.

Funcţie LINEST

Pentru a efectua calcule, selectați cu cursorul celula în care dorim să afișăm valoarea medie ș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 LINEST calculează statistici pentru o serie folosind metoda celor mai mici pătrate pentru a calcula linia dreaptă care aproximează cel mai bine datele disponibile și apoi returnează o matrice care descrie linia dreaptă rezultată. De asemenea, puteți combina funcția LINEST 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 pentru o dreaptă este:

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 independente x, iar b este o constantă. Rețineți că y, x și m pot fi vectori. Funcţie LINEST returnează matrice (mn;mn-1;…;m 1 ;b). LINEST poate returna, de asemenea, statistici de regresie suplimentare.

LINEST(valori_cunoscute_y; valori_cunoscute_x; const; statistici)

Known_y_values - un set de valori y care sunt deja cunoscute pentru relația y=mx+b.

Dacă matricea known_y_values are o coloană, atunci fiecare coloană din matricea known_x_values este tratată ca o variabilă separată.

Dacă matricea known_y_values are un rând, atunci fiecare rând din matricea known_x_values este tratată ca o variabilă separată.

Valorile_x cunoscute sunt un set opțional de valori x care sunt deja cunoscute pentru relația y=mx+b.

Tabloul known_x_values poate conține unul sau mai multe seturi de variabile. Dacă este utilizată o singură variabilă, atunci tablourile cunoscute_y_values și cunoscute_x_values pot avea orice formă - atâta timp cât au aceeași dimensiune. Dacă se utilizează mai mult de o variabilă, atunci cunoscute_y_values trebuie să fie un vector (adică, un interval de un rând înălțime sau o coloană lată).

Dacă array_known_x_values este omisă, atunci matricea (1;2;3;...) se presupune că are aceeași dimensiune cu array_known_values_y.

Const este o valoare booleană care specifică dacă constanta b trebuie să fie egală cu 0.

Dacă argumentul „const” este TRUE sau omis, atunci constanta b este evaluată ca de obicei.

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

Statistici - O valoare booleană care indică dacă trebuie returnate statistici suplimentare de 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.

Adiţional statistici 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. Se compară valorile reale ale lui y și 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 valorile reale și cele estimate. de y. În cazul opus, dacă coeficientul de determinare este 0, nu are rost să folosim ecuația de regresie pentru a prezice valorile lui y. Pentru mai multe informații despre cum se calculează r2, consultați „Note” la sfârșit aceasta sectiune.
sey	Eroare standard pentru estimarea y.
F	F-statistică sau F-valoare observată. Statistica F este utilizată pentru a determina dacă relația observată între o variabilă dependentă și cea independentă se datorează întâmplării.
df	Grade de libertate. Gradele de libertate sunt utile pentru găsirea valorilor critice F într-un tabel statistic. Pentru a determina nivelul de încredere al modelului, comparați valorile din tabel cu statistica F returnată de funcția LINEST. Pentru mai multe informații despre calcularea df, consultați „Note” de la sfârșitul acestei secțiuni. În continuare, Exemplul 4 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 ssreg și ssresid, consultați „Note” de la sfârșitul acestei secțiuni.

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

Note:

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

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

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

Ecuația dreptei este y=mx+b. Dacă valorile lui m și b sunt cunoscute, atunci orice punct de pe linie poate fi calculat prin înlocuirea valorilor lui y sau x în 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(LINEST(valori_y_cunoscute; valori_x_cunoscute); 1)

Intersecție cu Y: INDEX(LINEST(valori_y_cunoscute; valori_x_cunoscute); 2)

Precizia aproximării folosind linia dreaptă calculată de funcția LINEST depinde de gradul de împrăștiere a datelor. Cu cât datele sunt mai aproape de o linie dreaptă, cu atât este mai precis modelul folosit de funcția LINEST. Funcția LINEST folosește cele 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 (x-uri cunoscute) și y = MEDIE (y-cunoscute).

Funcțiile de potrivire LINEST și LGRFPRIBL pot calcula linia dreaptă sau curba exponențială care se potrivește cel mai bine datelor. Cu toate acestea, ele nu răspund la întrebarea care dintre cele două rezultate este mai potrivit pentru rezolvarea problemei. De asemenea, puteți evalua funcția TREND(cunoscute_y; cunoscute_x) pentru o linie dreaptă sau funcția GROW (cunoscute_y; cunoscute_x) pentru o curbă exponențială. Aceste funcții, cu excepția cazului în care sunt specificate valori noi_x, returnează o matrice de valori y calculate pentru valorile x reale de-a lungul unei linii sau curbe. Apoi puteți compara valorile calculate cu valorile reale. De asemenea, puteți crea diagrame pentru comparație vizuală.

La efectuarea analizei 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 valoarea acestui argument nu este specificată, valoare totală pătratele vor fi egale cu suma pătratelor diferențelor dintre valorile reale ale lui y și valorile medii ale lui y. Când const = FALS, suma totală a pătratelor va fi egală cu suma pătratelor valorilor reale y (fără a scădea valoarea medie y din valoarea y parțială). 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 mai multă valoare coeficientul de determinare r2, care arată cât de bine explică ecuația obținută prin analiza de regresie relațiile dintre variabile. Coeficientul r2 este egal cu ssreg/sstotal.

În unele cazuri, una sau mai multe coloane X (fie ca valorile Y și X să fie în coloane) nu au o valoare predicativă suplimentară în alte coloane X. Cu alte cuvinte, eliminarea uneia sau mai multor coloane X poate avea ca rezultat valori Y calculate cu aceeasi precizie. În acest caz, coloanele X redundante vor fi excluse din modelul de regresie. Acest fenomen se numește „colinearitate” deoarece coloanele redundante ale lui X pot fi reprezentate ca suma mai multor coloane neredundante. Funcția LINEST verifică coliniaritatea și elimină orice coloane X redundante din modelul de regresie dacă le detectează. Coloanele X eliminate pot fi identificate în ieșirea 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, se modifică și valorile lui sey și F. Nu este recomandat să folosiți des coliniaritatea. Cu toate acestea, ar trebui utilizat dacă unele coloane X conțin 0 sau 1 ca indicator care indică dacă subiectul experimentului este inclus în grup separat. Dacă const = TRUE sau nu este specificată o valoare pentru acest argument, LINEST inserează o coloană X suplimentară pentru a modela punctul de intersecție. Dacă există o coloană cu valori de 1 pentru bărbați și 0 pentru femei și există 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ținute din coloana „indicator masculin”.

Calculul df pentru cazurile în care X coloane nu sunt eliminate din model din cauza coliniarității are loc după cum urmează: dacă există k cunoscute_x coloane și valoarea 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 ca argument, de exemplu, cunoscute_x_values, ar trebui să utilizați un punct și virgulă pentru a separa valorile pe aceeași linie și două puncte pentru a separa liniile. Caracterele de separare pot varia în funcție de setările din fereastra Limbă și setări din Panoul de control.

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

Algoritm de bază utilizat în funcție LINEST, diferă de algoritmul funcției principale ÎNCLINAŢIEȘi SEGMENT DE LINIE. Diferența dintre algoritmi poate duce la rezultate diferite cu date incerte și coliniare. De exemplu, dacă punctele de date argument cunoscute_y_values sunt 0 și punctele de date argument cunoscute_x_values sunt 1, atunci:

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

Funcțiile SLOPE și LINE returnează eroarea #DIV/0!. Algoritmul funcțiilor SLOPE și INTERCEPT este folosit pentru a găsi un singur răspuns, dar î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 variabilelor x și y ca serii ale variabilelor x și y pentru LINEST. De exemplu, următoarea formulă:

LINIE(valori_y, valori_x^COLUMN($A:$C))

funcționează având o coloană de valori Y și o coloană de valori X pentru a calcula o aproximare a cubului (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 este posibil ca valorile de ieșire și alte statistici să fie nevoie să fie ajustate.

Analiza regresiei este una dintre cele mai populare metode de cercetare statistică. Poate fi folosit pentru a stabili gradul de influență al variabilelor independente asupra variabilei dependente. Microsoft Excel are instrumente concepute pentru a efectua acest tip de analiză. Să vedem ce sunt și cum să le folosim.

Conectarea pachetului de analiză

Dar, pentru a utiliza funcția care vă permite să efectuați o analiză de regresie, trebuie mai întâi să activați Pachetul de analiză. Abia atunci instrumentele necesare pentru această procedură vor apărea pe panglica Excel.

Treceți la fila „Fișier”.
Accesați secțiunea „Setări”.
Se deschide o fereastră setări Excel. Accesați subsecțiunea „Suplimente”.
În partea de jos a ferestrei care se deschide, mutați comutatorul din blocul „Control” în poziția „ Suplimente Excel„dacă se află într-o altă poziție. Faceți clic pe butonul „Go”.
Se deschide o fereastră cu programe de completare Excel disponibile. Bifați caseta de lângă „Pachet de analiză”. Faceți clic pe butonul „OK”.

Acum, când mergem la fila „Date”, pe panglica din blocul instrument „Analiză”, vom vedea un buton nou - „Analiza datelor”.

Tipuri de analiză de regresie

Există mai multe tipuri de regresii:

parabolic;
potolit;
logaritmică;
exponențial;
demonstrativ;
hiperbolic;
regresie liniara.

Vom vorbi mai detaliat despre efectuarea ultimului tip de analiză de regresie în Excel mai târziu.

Regresia liniară în Excel

Mai jos, de exemplu, este un tabel care arată temperatura medie zilnică a aerului exterior și numărul de clienți ai magazinului pentru ziua lucrătoare corespunzătoare. Să aflăm folosind analiza de regresie exact cum vreme sub formă de temperatură a aerului poate afecta frecventarea unei unități de vânzare cu amănuntul.

Ecuația generală de regresie liniară este următoarea: Y = a0 + a1x1 +…+ akhk. În această formulă, Y înseamnă variabila asupra căreia încercăm să studiem influența factorilor. În cazul nostru, acesta este numărul de cumpărători. Valoarea lui x reprezintă diferiții factori care influențează variabila. Parametrii a sunt coeficienții de regresie. Adică ei sunt cei care determină semnificația unui anumit factor. Indicele k denotă numărul total al acestor aceiași factori.

Analiza rezultatelor analizei

Rezultatele analizei de regresie sunt afișate sub forma unui tabel în locul specificat în setări.

Unul dintre principalii indicatori este R-pătrat. Indică calitatea modelului. În cazul nostru, acest coeficient este de 0,705 sau aproximativ 70,5%. Acesta este un nivel acceptabil de calitate. Dependența mai mică de 0,5 este rea.

Un alt indicator important este situat în celula de la intersecția rândului „Intersecție Y” și coloanei „Coeficienți”. Aceasta indică ce valoare va avea Y și, în cazul nostru, acesta este numărul de cumpărători, cu toți ceilalți factori egali cu zero. În acest tabel, această valoare este 58,04.

Valoarea de la intersecția coloanelor „Variabila X1” și „Coeficienți” arată nivelul de dependență al lui Y față de X. În cazul nostru, acesta este nivelul de dependență a numărului de clienți ai magazinului de temperatură. Un coeficient de 1,31 este considerat un indicator de influență destul de ridicat.

După cum putem vedea, folosind programe Microsoft Excel este destul de ușor să creezi un tabel de analiză de regresie. Dar numai o persoană instruită poate lucra cu datele de ieșire și poate înțelege esența acestora.

Ne bucurăm că am putut să vă ajutăm să rezolvați problema.

Pune-ți întrebarea în comentarii, descriind esența problemei în detaliu. Specialistii nostri vor incerca sa raspunda cat mai repede posibil.

Te-a ajutat acest articol?

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ă sub numele de ecuație liniară, este dată mai jos:

ŷ - valoarea așteptată a lui y pentru o valoare dată a lui x,

x - variabilă independentă,

a - 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 liniei 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 forma ecuației folosind metoda celor mai mici pătrate.

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 cum funcționează suplimentul, să folosim datele dintr-un articol anterior, î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 articolul anterior.

Analiza regresiei este o metodă de cercetare statistică care vă permite să arătați dependența unui anumit parametru de una sau mai multe variabile independente. În era pre-computer, utilizarea sa era destul de dificilă, mai ales când era vorba de volume mari de date. Astăzi, după ce ați învățat cum să construiți regresia în Excel, puteți rezolva probleme statistice complexe în doar câteva minute. Mai jos sunt exemple concrete din domeniul economiei.

Tipuri de regresie

Acest concept în sine a fost introdus în matematică de Francis Galton în 1886. Are loc regresia:

liniar;
parabolic;
potolit;
exponențial;
hiperbolic;
demonstrativ;
logaritmică.

Exemplul 1

Să luăm în considerare problema determinării dependenței numărului de membri ai echipei care au renunțat salariu mediu la 6 întreprinderi industriale.

Sarcină. La șase întreprinderi, salariul mediu lunar și numărul de angajați care au demisionat din cauza după plac. ÎN formă tabelară avem:

Pentru sarcina de a determina dependența numărului de lucrători care renunță la salariul mediu la 6 întreprinderi, modelul de regresie are forma ecuației Y = a0 + a1×1 +…+аkxk, unde хi sunt variabilele de influență, ai sunt coeficienții de regresie, iar k este numărul de factori.

Pentru această sarcină, Y este indicatorul angajaților care au demisionat, iar factorul de influență este salariul, pe care îl notăm cu X.

Folosind capacitățile procesorului de foi de calcul Excel

Analiza de regresie în Excel trebuie să fie precedată de aplicarea funcțiilor încorporate la datele tabelare existente. Cu toate acestea, în aceste scopuri este mai bine să utilizați extensia foarte utilă „Analysis Pack”. Pentru a-l activa aveți nevoie de:

din fila „Fișier” accesați secțiunea „Opțiuni”;
în fereastra care se deschide, selectați linia „Suplimente”;
faceți clic pe butonul „Go” situat mai jos, în dreapta liniei „Management”;
bifați caseta de lângă numele „Pachet de analiză” și confirmați acțiunile făcând clic pe „Ok”.

Dacă totul este făcut corect, butonul necesar va apărea în partea dreaptă a filei „Date”, situată deasupra foii de lucru Excel.

Regresia liniară în Excel

Acum că ai tot ce ai nevoie la îndemână instrumente virtuale pentru a efectua calcule econometrice, putem începe să ne rezolvăm problema. Pentru aceasta:

Faceți clic pe butonul „Analiza datelor”;
în fereastra care se deschide, faceți clic pe butonul „Regresie”;
în fila care apare, introduceți intervalul de valori pentru Y (numărul de angajați care demisionează) și pentru X (salariile acestora);
Confirmăm acțiunile noastre apăsând butonul „Ok”.

Ca rezultat, programul va completa automat o nouă foaie de calcul cu date de analiză de regresie. Notă! Excel vă permite să setați manual locația pe care o preferați în acest scop. De exemplu, ar putea fi aceeași foaie în care sunt situate valorile Y și X, sau chiar O carte noua, special conceput pentru stocarea unor astfel de date.

Analiza rezultatelor regresiei pentru R-pătrat

ÎN date Excel obţinute în timpul prelucrării datelor din exemplul în cauză au forma:

În primul rând, ar trebui să acordați atenție valorii R pătrat. Reprezintă coeficientul de determinare. În acest exemplu, R-pătrat = 0,755 (75,5%), adică parametrii calculați ai modelului explică relația dintre parametrii luați în considerare cu 75,5%. Cu cât valoarea coeficientului de determinare este mai mare, cu atât modelul selectat este considerat mai aplicabil pentru sarcina specifica. Se consideră că se descrie corect situația reală când valoarea R-pătratului este peste 0,8. Dacă R-pătrat este tcr, atunci ipoteza despre nesemnificația termenului liber al ecuației liniare este respinsă.

În problema luată în considerare pentru termenul liber, folosind instrumentele Excel, s-a obținut că t = 169,20903 și p = 2,89E-12, adică avem probabilitate zero ca ipoteza corectă despre nesemnificația termenului liber să fie respinsă. . Pentru coeficientul necunoscutului t=5,79405 și p=0,001158. Cu alte cuvinte, probabilitatea ca ipoteza corectă despre nesemnificația coeficientului pentru o necunoscută să fie respinsă este de 0,12%.

Astfel, se poate susține că ecuația de regresie liniară rezultată este adecvată.

Problema fezabilității achiziționării unui bloc de acțiuni

Regresia multiplă în Excel este efectuată folosind același instrument de analiză a datelor. Să luăm în considerare o problemă specifică de aplicare.

Conducerea companiei NNN trebuie să decidă oportunitatea achiziționării unui pachet de 20% din MMM JSC. Costul pachetului (SP) este de 70 de milioane de dolari SUA. Specialiștii NNN au colectat date despre tranzacții similare. S-a decis evaluarea valorii blocului de acțiuni în funcție de astfel de parametri, exprimați în milioane de dolari SUA, astfel:

conturi de plătit (VK);
volumul anual al cifrei de afaceri (VO);
conturi de încasat (VD);
costul mijloacelor fixe (COF).

În plus, se utilizează parametrul restanțelor salariale ale întreprinderii (V3 P) în mii de dolari SUA.

Soluție folosind procesorul de foi de calcul Excel

În primul rând, trebuie să creați un tabel de date sursă. Arata cam asa:

apelați fereastra „Analiza datelor”;
selectați secțiunea „Regresie”;
În caseta „Interval de intrare Y”, introduceți intervalul de valori ale variabilelor dependente din coloana G;
faceți clic pe pictograma săgeată roșie din dreapta ferestrei „Input Range X” și evidențiați pe foaie intervalul tuturor valorilor de la coloanele B,C,D,F.

Marcați elementul „Foaie de lucru nouă” și faceți clic pe „Ok”.

Obțineți o analiză de regresie pentru o anumită problemă.

Studiul rezultatelor și concluziilor

„Colectăm” ecuația de regresie din datele rotunjite prezentate mai sus pe foaia de calcul Excel:

SP = 0,103*SOF + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

Într-o formă matematică mai familiară, poate fi scrisă astfel:

y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844

Datele pentru MMM JSC sunt prezentate în tabel:

Înlocuindu-le în ecuația de regresie, obținem o cifră de 64,72 milioane de dolari SUA. Aceasta înseamnă că acțiunile MMM JSC nu merită cumpărate, deoarece valoarea lor de 70 de milioane de dolari SUA este destul de umflată.

După cum puteți vedea, utilizarea foii de calcul Excel și a ecuației de regresie au făcut posibilă luarea unei decizii informate cu privire la fezabilitatea unei tranzacții foarte specifice.

Acum știi ce este regresia. Exemplele Excel discutate mai sus vă vor ajuta să rezolvați probleme practice din domeniul econometriei.

Analiza de regresie și corelație sunt metode de cercetare statistică. Acestea sunt cele mai comune moduri de a arăta dependența unui parametru de una sau mai multe variabile independente.

Mai jos, folosind exemple practice specifice, vom lua în considerare aceste două analize foarte populare în rândul economiștilor. Vom da, de asemenea, un exemplu de obținere a rezultatelor atunci când le combinăm.

Analiza de regresie în Excel

Arată influența unor valori (independente, independente) asupra variabilei dependente. De exemplu, cum depinde numărul populației active din punct de vedere economic de numărul de întreprinderi, salarii și alți parametri. Sau: cum afectează investițiile străine, prețurile la energie etc. nivelul PIB-ului.

Rezultatul analizei vă permite să evidențiați prioritățile. Și pe baza factorilor principali, preziceți și planificați dezvoltarea domenii prioritare, luați decizii de management.

Are loc regresia:

liniară (y = a + bx);
parabolic (y = a + bx + cx 2);
exponențial (y = a * exp(bx));
putere (y = a*x^b);
hiperbolic (y = b/x + a);
logaritmică (y = b * 1n(x) + a);
exponențial (y = a * b^x).

Să ne uităm la construcție ca exemplu model de regresieîn Excel și interpretarea rezultatelor. Să luăm tipul liniar de regresie.

Sarcină. La 6 întreprinderi au fost analizate salariul mediu lunar și numărul de angajați care au demisionat. Este necesar să se determine dependența numărului de angajați care renunță la salariul mediu.

Modelul de regresie liniară arată astfel:

Y = a 0 + a 1 x 1 +…+a k x k.

Unde a sunt coeficienți de regresie, x sunt variabile de influență, k este numărul de factori.

În exemplul nostru, Y este indicatorul renunțării angajaților. Factorul de influență este salariul (x).

Excel are funcții încorporate care vă pot ajuta să calculați parametrii unui model de regresie liniară. Dar suplimentul „Pachet de analiză” va face acest lucru mai repede.

Activăm un instrument analitic puternic:

Odată activat, suplimentul va fi disponibil în fila Date.

Acum să facem însăși analiza de regresie.

În primul rând, acordăm atenție R-pătratului și coeficienților.

R-pătrat este coeficientul de determinare. În exemplul nostru – 0,755 sau 75,5%. Aceasta înseamnă că parametrii calculați ai modelului explică 75,5% din relația dintre parametrii studiați. Cu cât coeficientul de determinare este mai mare, cu atât model de calitate mai buna. Bun - peste 0,8. Rău – mai puțin de 0,5 (o astfel de analiză poate fi considerată cu greu rezonabilă). În exemplul nostru – „nu e rău”.

Coeficientul 64,1428 arată ce va fi Y dacă toate variabilele din modelul luat în considerare sunt egale cu 0. Adică valoarea parametrului analizat este influențată și de alți factori nedescriși în model.

Coeficientul -0,16285 arată ponderea variabilei X pe Y. Adică salariul mediu lunar în cadrul acestui model afectează numărul de renunțați cu o pondere de -0,16285 (acesta este un grad mic de influență). Semnul „-” indică un impact negativ: cu cât salariul este mai mare, cu atât mai puține persoane renunță. Ceea ce este corect.

Analiza corelației în Excel

Analiza corelației ajută la determinarea dacă există o relație între indicatorii din unul sau două eșantioane. De exemplu, între timpul de funcționare al unei mașini și costul reparațiilor, prețul echipamentului și durata de funcționare, înălțimea și greutatea copiilor etc.

Dacă există o conexiune, atunci o creștere a unui parametru duce la o creștere (corelație pozitivă) sau o scădere (negativă) a celuilalt. Analiza corelației ajută analistul să determine dacă valoarea unui indicator poate fi utilizată pentru a prezice valoarea posibilă a altuia.

Coeficientul de corelație se notează cu r. Variază de la +1 la -1. Clasificarea corelaţiilor pentru zone diferite va fi diferit. Când coeficientul este 0, nu există o relație liniară între probe.

Să ne uităm la modul de utilizare Instrumente Excel găsiți coeficientul de corelație.

Pentru a găsi coeficienți perechi, este utilizată funcția CORREL.

Obiectiv: Determinați dacă există o relație între timpul de funcționare strungși costul întreținerii acestuia.

Plasați cursorul în orice celulă și apăsați butonul fx.

În categoria „Statistică”, selectați funcția CORREL.
Argumentul „Matrice 1” - primul interval de valori – timpul de funcționare al mașinii: A2:A14.
Argumentul „Matrice 2” - al doilea interval de valori – costul reparației: B2:B14. Faceți clic pe OK.

Pentru a determina tipul de conexiune, trebuie să vă uitați la numărul absolut al coeficientului (fiecare domeniu de activitate are propria sa scară).

Pentru analiza corelației mai multor parametri (mai mult de 2), este mai convenabil să utilizați „Analiza datelor” (suplimentul „Pachet de analiză”). Trebuie să selectați corelația din listă și să desemnați matricea. Toate.

Coeficienții rezultați vor fi afișați în matricea de corelație. Ca aceasta:

Analiza corelației și regresiei

În practică, aceste două tehnici sunt adesea folosite împreună.

Exemplu:

Acum datele analizei de regresie au devenit vizibile.

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/Data Analysis/Regression). Apoi copiați rezultatele într-un bloc pentru analiză.