Un exemplu de utilizare a analizei de regresie. Analiza corelației și regresiei în Excel: instrucțiuni de execuție. Hiperbolice, liniară și logaritmică

6. Principalul indicator care reflectă calitatea analizei de regresie este coeficient de determinare (R 2).

Coeficient de determinare arată ce proporție din variabila dependentă " la„ este luată în considerare în analiză și este cauzată de influența asupra acesteia a factorilor incluși în analiză.

Coeficient de determinare (R 2) ia valori în intervalul . Ecuația de regresie este calitativă dacă R 2 ≥0,8.

Coeficientul de determinare este egal cu pătratul coeficientului de corelație, i.e.

Exemplul 6.1. Pe baza următoarelor date, construiți și analizați o ecuație de regresie:

Soluţie.

1) Calculați coeficientul de corelație: . Relația dintre semne este directă și moderată.

2) Construiți o ecuație de regresie liniară pereche.

2.1) Creați un tabel de calcul.

,

Ecuație de regresie liniară pereche: y x =25,17+0,087x.

3) Găsiți valorile teoretice" y x„prin înlocuirea valorilor reale în ecuația de regresie” X».

4) Construiți grafice cu " y"și valori teoretice" y x„caracteristică efectivă (Figura 6.1):r xy =0,47) și un număr mic de observații.

7) Calculați coeficientul de determinare: R 2=(0,47) 2 =0,22. Ecuația construită este de proastă calitate.

Deoarece calculele la efectuarea analizei de regresie sunt destul de extinse se recomanda folosirea unor programe speciale (Statistica 10, SPSS etc.).

Figura 6.2 prezintă un tabel cu rezultatele analizei de regresie efectuată cu ajutorul programului Statistica 10.

Figura 6.2. Rezultatele analizei de regresie efectuate cu ajutorul programului Statistica 10

5. Literatură:

1. Gmurman V.E. Teoria probabilității și statistică matematică: Manual. manual pentru universități / V.E. Gmurman. - M.: Şcoala superioară, 2003. - 479 p.

2. Koychubekov B.K. Biostatistică: manual. - Almaty: Evero, 2014. - 154 p.

3. Lobotskaya N.L. Matematică superioară. / N.L. Lobotskaya, Yu.V. Morozov, A.A. Dunaev. - Mn.: Şcoala superioară, 1987. - 319 p.

4. Medic V.A., Tokmachev M.S., Fishman B.B. Statistica în medicină și biologie: un ghid. În 2 volume / Ed. Yu.M. Komarova. T. 1. Statistica teoretică. - M.: Medicină, 2000. - 412 p.

5. Aplicarea metodelor de analiză statistică pentru studiul sănătăţii publice şi asistenţei medicale: manual / ed. Kucherenko V.Z. - Ed. a IV-a, revizuită. si suplimentare – M.: GEOTAR - Media, 2011. - 256 p.

Scopul analizei de regresie este de a măsura relația dintre o variabilă dependentă și una (analiza de regresie în perechi) sau mai multe (multiple) variabile independente. Variabilele independente mai sunt numite și variabile factori, explicative, determinante, regresoare și predictoare.

Variabila dependentă este uneori numită variabilă determinată, explicată sau „răspuns”. Utilizarea extrem de răspândită a analizei de regresie în cercetarea empirică nu se datorează doar faptului că este un instrument convenabil pentru testarea ipotezelor. Regresia, în special regresia multiplă, este o metodă eficientă de modelare și prognoză.

Să începem să explicăm principiile de lucru cu analiza de regresie cu una mai simplă - metoda perechii.

Analiza de regresie pereche

Primii pași atunci când se utilizează analiza de regresie vor fi aproape identici cu cei pe care i-am făcut în calcularea coeficientului de corelație. Cele trei condiții principale pentru eficacitatea analizei corelației folosind metoda Pearson - distribuția normală a variabilelor, măsurarea pe intervale a variabilelor, relația liniară între variabile - sunt de asemenea relevante pentru regresia multiplă. În consecință, în prima etapă, se construiesc diagrame de dispersie, se efectuează o analiză statistică și descriptivă a variabilelor și se calculează o linie de regresie. Ca și în cadrul analizei de corelație, liniile de regresie sunt construite folosind metoda celor mai mici pătrate.

Pentru a ilustra mai clar diferențele dintre cele două metode de analiză a datelor, să ne întoarcem la exemplul deja discutat cu variabilele „sprijin SPS” și „cota populației rurale”. Datele sursă sunt identice. Diferența dintre diagramele de dispersie va fi că în analiza de regresie este corect să se traseze variabila dependentă - în cazul nostru, „suport SPS” pe axa Y, în timp ce în analiza corelației acest lucru nu contează. După curățarea valorilor aberante, diagrama de dispersie arată astfel:

Ideea fundamentală a analizei de regresie este că, având o tendință generală pentru variabile - sub forma unei linii de regresie - este posibil să se prezică valoarea variabilei dependente, având în vedere valorile celei independente.

Să ne imaginăm o funcție liniară matematică obișnuită. Orice linie din spațiul euclidian poate fi descrisă prin formula:

unde a este o constantă care specifică deplasarea de-a lungul axei ordonatelor; b este un coeficient care determină unghiul de înclinare al dreptei.

Cunoscând panta și constanta, puteți calcula (preva) valoarea lui y pentru orice x.

Această funcție cea mai simplă a stat la baza modelului de analiză de regresie cu avertismentul că nu vom prezice valoarea lui y exact, ci într-un anumit interval de încredere, i.e. aproximativ.

Constanta este punctul de intersecție al dreptei de regresie și a axei y (intersecția F, denumită de obicei „interceptor” în pachetele statistice). În exemplul nostru cu votul pentru Uniunea Forțelor Dreapte, valoarea sa rotunjită va fi 10,55. Coeficientul unghiular b va fi de aproximativ -0,1 (ca și în analiza corelației, semnul arată tipul de conexiune - directă sau inversă). Astfel, modelul rezultat va avea forma SP C = -0,1 x Sel. S.U.A. + 10,55.

Astfel, pentru cazul „Republicii Adygea” cu o pondere a populației rurale de 47%, valoarea prognozată va fi de 5,63:

ATP = -0,10 x 47 + 10,55 = 5,63.

Diferența dintre valorile inițiale și cele prezise se numește rest (am întâlnit deja acest termen, care este fundamental pentru statistică, atunci când analizăm tabelele de contingență). Deci, pentru cazul „Republicii Adygea” restul va fi egal cu 3,92 - 5,63 = -1,71. Cu cât valoarea modulară a restului este mai mare, cu atât valoarea prezisă este mai puțin reușită.

Analiza raportului dintre valorile inițiale și cele prezise servește la evaluarea calității modelului rezultat și a capacității sale de predicție. Unul dintre principalii indicatori ai statisticilor de regresie este coeficientul de corelație multiplă R - coeficientul de corelație dintre valorile inițiale și cele prezise ale variabilei dependente. În analiza de regresie pereche, este egal cu coeficientul obișnuit de corelație Pearson între variabilele dependente și independente, în cazul nostru - 0,63. Pentru a interpreta în mod semnificativ mai mulți R, acesta trebuie convertit într-un coeficient de determinare. Acest lucru se face în același mod ca în analiza corelației - prin pătrare. Coeficientul de determinare R-pătrat (R 2) arată proporția de variație a variabilei dependente care este explicată de variabila(e) independentă(e).

În cazul nostru, R2 = 0,39 (0,63 2); aceasta înseamnă că variabila „ponderea populației rurale” explică aproximativ 40% din variația variabilei „sprijin SPS”. Cu cât coeficientul de determinare este mai mare, cu atât calitatea modelului este mai mare.

Un alt indicator al calității modelului este eroarea standard de estimare. Aceasta este o măsură a cât de larg sunt „împrăștiate” punctele în jurul liniei de regresie. Măsura împrăștierii pentru variabilele de interval este abaterea standard. În consecință, eroarea standard a estimării este abaterea standard a distribuției reziduurilor. Cu cât valoarea sa este mai mare, cu atât este mai mare împrăștierea și modelul este mai rău. În cazul nostru, eroarea standard este 2.18. Cu această sumă, modelul nostru va „greșa în medie” atunci când prezice valoarea variabilei „Suport SPS”.

Statisticile de regresie includ și analiza varianței. Cu ajutorul ei aflăm: 1) ce proporţie din variaţia (dispersia) variabilei dependente este explicată de variabila independentă; 2) ce proporție din varianța variabilei dependente este contabilizată de reziduuri (partea neexplicată); 3) care este raportul dintre aceste două cantități (/"-raport). Statisticile de dispersie sunt deosebit de importante pentru studiile prin eșantion - arată cât de probabil este să existe o relație între variabilele independente și dependente în populație. Cu toate acestea, pentru studiile continue (ca în exemplul nostru) studiul rezultatele analizei varianței nu sunt utile În acest caz, ele verifică dacă modelul statistic identificat este cauzat de o coincidență de circumstanțe aleatorii, în ce măsură este caracteristic setului de condiții. în care se află populația studiată, adică se stabilește că rezultatul obținut nu este adevărat pentru un agregat general mai larg, ci gradul de regularitate a acestuia, lipsa de influențe aleatorii.

În cazul nostru, statisticile ANOVA sunt după cum urmează:

Raportul F de 54,29 este semnificativ la nivelul 0,0000000001. În consecință, putem respinge cu încredere ipoteza nulă (că relația pe care am descoperit-o se datorează întâmplării).

Criteriul t îndeplinește o funcție similară, dar în raport cu coeficienții de regresie (unghiular și F-intersecție). Utilizând criteriul /, testăm ipoteza că în populația generală coeficienții de regresie sunt egali cu zero. În cazul nostru, putem respinge din nou cu încredere ipoteza nulă.

Analiza de regresie multiplă

Modelul de regresie multiplă este aproape identic cu modelul de regresie pereche; singura diferență este că mai multe variabile independente sunt incluse secvențial în funcția liniară:

Y = b1X1 + b2X2 + …+ bpXp + a.

Dacă există mai mult de două variabile independente, nu ne putem face o idee vizuală a relației lor în acest sens, regresia multiplă este mai puțin „vizuală” decât regresia pe perechi. Când aveți două variabile independente, poate fi util să afișați datele într-un grafic de dispersie 3D. În pachetele software profesionale de statistică (de exemplu, Statistica) există o opțiune de a roti o diagramă tridimensională, care vă permite să reprezentați vizual bine structura datelor.

Când se lucrează cu regresia multiplă, spre deosebire de regresia perechi, este necesar să se determine algoritmul de analiză. Algoritmul standard include toți predictorii disponibili în modelul de regresie final. Algoritmul pas cu pas implică includerea (excluderea) secvențială a variabilelor independente pe baza „greutății” lor explicative. Metoda în trepte este bună când există multe variabile independente; „curăță” modelul predictorilor sincer slabi, făcându-l mai compact și mai concis.

O condiție suplimentară pentru corectitudinea regresiei multiple (împreună cu intervalul, normalitatea și liniaritatea) este absența multicolinearității - prezența unor corelații puternice între variabilele independente.

Interpretarea statisticilor de regresie multiplă include toate elementele pe care le-am luat în considerare pentru cazul regresiei pe perechi. În plus, există și alte componente importante pentru statisticile analizei de regresie multiplă.

Vom ilustra munca cu regresie multiplă folosind exemplul de testare a ipotezelor care explică diferențele de nivel de activitate electorală în regiunile rusești. Studiile empirice specifice au sugerat că nivelul prezenței la vot este influențat de:

Factorul național (variabila „populație rusă”; operaționalizată ca pondere a populației ruse în entitățile constitutive ale Federației Ruse). Se presupune că o creștere a ponderii populației ruse duce la o scădere a prezenței la vot;

Factorul de urbanizare (variabila „populație urbană”; operaționalizat ca pondere a populației urbane în entitățile constitutive ale Federației Ruse; am lucrat deja cu acest factor ca parte a analizei de corelație). Se presupune că o creștere a ponderii populației urbane duce și la o scădere a prezenței la vot.

Variabila dependentă - „intensitatea activității electorale” („activă”) este operaționalizată prin datele privind prezența medie la vot pe regiune la alegerile federale din 1995 până în 2003. Tabelul de date inițial pentru două variabile independente și una dependentă va fi următorul:

etc. (după curățarea emisiilor, rămân 83 de cazuri din 88)

Statistici care descriu calitatea modelului:

1. Multiplu R = 0,62; L-pătrat = 0,38. În consecință, factorul național și factorul de urbanizare explică împreună aproximativ 38% din variația variabilei „activitate electorală”.

2. Eroarea medie este 3,38. Acesta este exact cât de „greșit în medie” este modelul construit atunci când prezice nivelul de prezență la vot.

3. Raportul /l al variației explicate și inexplicabile este de 25,2 la nivelul 0,000000003. Ipoteza nulă despre caracterul aleatoriu al relațiilor identificate este respinsă.

4. Criteriul / pentru coeficienții constanti și de regresie ai variabilelor „populație urbană” și „populație rusă” este semnificativ la nivelul de 0,0000001; 0,00005 și respectiv 0,007. Ipoteza nulă conform căreia coeficienții sunt aleatori este respinsă.

Statistici suplimentare utile în analiza relației dintre valorile inițiale și cele prezise ale variabilei dependente sunt distanța Mahalanobis și distanța lui Cook. Prima este o măsură a unicității cazului (arată cât de mult se abate combinația de valori ale tuturor variabilelor independente pentru un caz dat de la valoarea medie pentru toate variabilele independente simultan). Al doilea este o măsură a influenței cazului. Diferite observații au efecte diferite asupra pantei dreptei de regresie, iar distanța lui Cook poate fi folosită pentru a le compara pe acest indicator. Acest lucru poate fi util atunci când curățați valorile aberante (un lucru aberant poate fi considerat un caz prea influent).

În exemplul nostru, cazurile unice și influente includ Daghestan.

Modelul de regresie în sine are următorii parametri: Y-intercept (constant) = 75,99; b (orizontal) = -0,1; Kommersant (nas. rus.) = -0,06. Formula finala:

Aactiv, = -0,1 x Hor. us.n+- 0,06 x Rus. us.n + 75,99.

Putem compara „puterea explicativă” a predictorilor pe baza valorii coeficientului de 61. În acest caz, da, deoarece ambele variabile independente au același format procentual. Cu toate acestea, cel mai adesea regresia multiplă se ocupă de variabile măsurate pe diferite scale (de exemplu, nivelul veniturilor în ruble și vârsta în ani). Prin urmare, în cazul general, este incorect să comparăm capacitățile predictive ale variabilelor folosind un coeficient de regresie. În statisticile de regresie multiplă, există un coeficient beta special (B) în acest scop, calculat separat pentru fiecare variabilă independentă. Reprezintă coeficientul de corelație parțial (calculat după luarea în considerare a influenței tuturor celorlalți predictori) dintre factor și răspuns și arată contribuția independentă a factorului la predicția valorilor răspunsului. În analiza regresiei perechi, coeficientul beta este, în mod înțeles, egal cu coeficientul de corelație perechi dintre variabila dependentă și cea independentă.

În exemplul nostru, beta (populația din Highlands) = -0,43, beta (populația rusă) = -0,28. Astfel, ambii factori afectează negativ nivelul activității electorale, în timp ce importanța factorului de urbanizare este semnificativ mai mare decât importanța factorului național. Influența combinată a ambilor factori determină aproximativ 38% din variația variabilei „activitate electorală” (vezi valoarea L-pătratului).

Analiza regresiei

Regresia (liniar) analiză- o metodă statistică pentru studierea influenței uneia sau mai multor variabile independente asupra unei variabile dependente. Variabilele independente sunt denumite altfel regresori sau predictori, iar variabilele dependente sunt numite variabile criteriu. Terminologie dependenteŞi independent variabile reflectă doar dependența matematică a variabilelor ( vezi Corelație falsă), mai degrabă decât relațiile cauză-efect.

Obiectivele analizei de regresie

1. Determinarea gradului de determinare a variației unei variabile de criteriu (dependente) de către predictori (variabile independente)
2. Predicția valorii unei variabile dependente folosind variabilele independente
3. Determinarea contribuției variabilelor independente individuale la variația variabilei dependente

Analiza de regresie nu poate fi utilizată pentru a determina dacă există o relație între variabile, deoarece prezența unei astfel de relații este o condiție prealabilă pentru aplicarea analizei.

Definiția matematică a regresiei

O relație strict de regresie poate fi definită după cum urmează. Fie , variabile aleatoare cu o distribuție de probabilitate comună dată. Dacă pentru fiecare set de valori este definită o așteptare matematică condiționată

atunci funcția este apelată regresie valorile lui Y după valori, iar graficul său este linie de regresie de , sau ecuația de regresie.

Dependența de se manifestă prin modificarea valorilor medii ale lui Y cu o modificare a . Deși, pentru fiecare set fix de valori, valoarea rămâne o variabilă aleatorie cu o anumită împrăștiere.

Pentru a clarifica întrebarea cu cât de precis este estimată analiza de regresie modificarea lui Y atunci când se schimbă, se utilizează valoarea medie a dispersiei lui Y pentru diferite seturi de valori (de fapt, vorbim despre măsura dispersiei variabilei dependente). în jurul dreptei de regresie).

Metoda celor mai mici pătrate (calcul coeficienților)

În practică, linia de regresie este căutată cel mai adesea sub forma unei funcții liniare (regresie liniară), care aproximează cel mai bine curba dorită. Acest lucru se face folosind metoda celor mai mici pătrate, când suma abaterilor pătrate ale celor observate efectiv de la estimările lor este minimizată (adică estimări folosind o linie dreaptă care pretinde să reprezinte relația de regresie dorită):

(M - dimensiunea eșantionului). Această abordare se bazează pe faptul binecunoscut că suma care apare în expresia de mai sus capătă o valoare minimă tocmai pentru cazul când .

Pentru a rezolva problema analizei regresiei folosind metoda celor mai mici pătrate, se introduce conceptul funcții reziduale:

Condiție minimă pentru funcția reziduală:

Sistemul rezultat este un sistem de ecuații liniare cu necunoscute

Dacă reprezentăm termenii liberi din partea stângă a ecuațiilor ca o matrice

iar coeficienții pentru necunoscutele din partea dreaptă sunt matricea

atunci obținem ecuația matriceală: , care se rezolvă ușor prin metoda Gauss. Matricea rezultată va fi o matrice care conține coeficienții ecuației dreptei de regresie:

Pentru a obține cele mai bune estimări, este necesar să se îndeplinească condițiile prealabile ale MCO (condiții Gauss-Markov). În literatura engleză, astfel de estimări sunt numite BLUE (Best Linear Unbiased Estimators).

Interpretarea parametrilor de regresie

Parametrii sunt coeficienți de corelație parțială; este interpretată ca proporția varianței lui Y explicată prin fixarea influenței predictorilor rămași, adică măsoară contribuția individuală la explicarea lui Y. În cazul predictorilor corelați, se pune problema incertitudinii în estimări, care devin dependente de ordinea în care predictorii sunt incluși în model. În astfel de cazuri, este necesar să se utilizeze metode de analiză a corelației și regresiei în trepte.

Când vorbim despre modele neliniare de analiză de regresie, este important să acordăm atenție dacă vorbim despre neliniaritate în variabile independente (din punct de vedere formal, ușor de redus la regresie liniară), sau despre neliniaritate în parametrii estimați (care provoacă serioase dificultăţi de calcul). În cazul neliniarității de primul tip, din punct de vedere substanțial, este important de evidențiat apariția în model a termenilor de forma , , indicând prezența interacțiunilor între caracteristici , etc. (vezi Multicoliniaritate).

Vezi de asemenea

Legături

• www.kgafk.ru - Prelegere pe tema „Analiza regresiei”
• www.basegroup.ru - metode de selectare a variabilelor în modelele de regresie

Literatură

• Norman Draper, Harry Smith Analiza de regresie aplicată. Regresie multiplă = Analiză de regresie aplicată. - Ed. a 3-a. - M.: „Dialectică”, 2007. - P. 912. - ISBN 0-471-17082-8
• Metode robuste de estimare a modelelor statistice: Monografie. - K.: PP „Sansparel”, 2005. - P. 504. - ISBN 966-96574-0-7, UDC: 519.237.5:515.126.2, BBK 22.172+22.152
• Radcenko Stanislav Grigorievici, Metodologia analizei de regresie: Monografie. - K.: „Korniychuk”, 2011. - P. 376. - ISBN 978-966-7599-72-0

Fundația Wikimedia.

2010.

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.

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. Acum, când mergem la filă„Date” , pe panglica din cutia de instrumente"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 liniară.

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 condițiile meteorologice sub forma temperaturii aerului pot afecta prezența 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 xînseamnă o variabilă, influența factorilor asupra cărora încercăm să studiem. În cazul nostru, acesta este numărul de cumpărători. Sens sunt diferiții factori care influențează o variabilă. Opțiuni o k sunt coeficienți de regresie. Adică ei sunt cei care determină semnificația unui anumit factor. Index

denotă numărul total al acelorași factori.

Analiza rezultatelor analizei

Rezultatele analizei de regresie sunt afișate sub formă de tabel în locul specificat în setări. Unul dintre principalii indicatori este. 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 liniei „Intersecția în Y” si coloana "Cote". 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.

Valoare la intersecția graficului „Variabila X1”Şi "Cote" arată nivelul de dependență al lui Y față de X. În cazul nostru, acesta este nivelul de dependență a numărului de clienți din magazin față de temperatură. Un coeficient de 1,31 este considerat un indicator de influență destul de ridicat.

După cum puteți vedea, folosind Microsoft Excel este destul de ușor să creați 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.

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 specifice din domeniul economiei.

Tipuri de regresie

Acest concept în sine a fost introdus în matematică î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 renunță la salariul mediu la 6 întreprinderi industriale.

Sarcină. La șase întreprinderi s-a analizat salariul mediu lunar și numărul de angajați care au părăsit voluntar. Î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 = a 0 + a 1 x 1 +...+a k x k, unde x i sunt variabilele care influențează, a i sunt coeficienții de regresie și k este numărul de factori.

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

Utilizarea capabilităților 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.

în Excel

Acum că avem la îndemână toate instrumentele virtuale necesare pentru a efectua calcule econometrice, putem începe să ne rezolvăm problema. Pentru a face acest lucru:

• 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. Fiţi atenți! Excel vă permite să setați manual locația pe care o preferați în acest scop. De exemplu, aceasta ar putea fi aceeași foaie în care se află valorile Y și X sau chiar un nou registru de lucru special conceput pentru a stoca astfel de date.

Analiza rezultatelor regresiei pentru R-pătrat

În Excel, datele obținute în timpul procesării datelor din exemplul luat în considerare 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 mai potrivit pentru o anumită sarcină. Se consideră că se descrie corect situația reală când valoarea R-pătratului este peste 0,8. Dacă R-pătrat<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Analiza cotelor

Numărul 64,1428 arată care va fi valoarea lui Y dacă toate variabilele xi din modelul pe care îl luăm în considerare sunt resetate la zero. Cu alte cuvinte, se poate susține că valoarea parametrului analizat este influențată și de alți factori care nu sunt descriși într-un anumit model.

Următorul coeficient -0,16285, situat în celula B18, arată ponderea influenței variabilei X asupra Y. Aceasta înseamnă că salariul mediu lunar al angajaților din cadrul modelului luat în considerare afectează numărul de renunțați cu o pondere de -0,16285, adică. gradul de influență este complet mic. Semnul „-” indică faptul că coeficientul este negativ. Acest lucru este evident, deoarece toată lumea știe că, cu cât salariul la întreprindere este mai mare, cu atât mai puține persoane își exprimă dorința de a rezilia contractul de muncă sau de a renunța.

Regresie multiplă

Acest termen se referă la o ecuație de relație cu mai multe variabile independente de forma:

y=f(x 1 +x 2 +…x m) + ε, unde y este caracteristica rezultantă (variabilă dependentă), iar x 1, x 2,…x m sunt caracteristici factoriale (variabile independente).

Estimarea parametrilor

Pentru regresia multiplă (MR), se efectuează folosind metoda celor mai mici pătrate (OLS). Pentru ecuații liniare de forma Y = a + b 1 x 1 +…+b m x m + ε construim un sistem de ecuații normale (vezi mai jos)

Pentru a înțelege principiul metodei, luați în considerare un caz cu doi factori. Atunci avem o situație descrisă de formula

De aici obținem:

unde σ este varianța caracteristicii corespunzătoare reflectate în indice.

OLS este aplicabilă ecuației MR pe o scară standardizată. În acest caz, obținem ecuația:

în care t y, t x 1, ... t xm sunt variabile standardizate, pentru care valorile medii sunt egale cu 0; β i sunt coeficienții de regresie standardizați, iar abaterea standard este 1.

Vă rugăm să rețineți că toate β i în acest caz sunt specificate ca normalizate și centralizate, prin urmare compararea lor între ele este considerată corectă și acceptabilă. În plus, se obișnuiește să se elimine factorii prin eliminarea celor cu cele mai mici valori βi.

Problemă folosind ecuația de regresie liniară

Să presupunem că avem un tabel cu dinamica prețurilor pentru un anumit produs N în ultimele 8 luni. Este necesar să luați o decizie cu privire la oportunitatea achiziționării unui lot la un preț de 1850 de ruble/t.

Pentru a rezolva această problemă în procesorul de foi de calcul Excel, trebuie să utilizați instrumentul „Analiza datelor”, deja cunoscut din exemplul prezentat mai sus. Apoi, selectați secțiunea „Regresie” și setați parametrii. Trebuie reținut că în câmpul „Interval de intrare Y” trebuie introdus un interval de valori pentru variabila dependentă (în acest caz, prețurile pentru mărfuri în anumite luni ale anului), iar în „Intervalul de intrare X” - pentru variabila independentă (numărul lunii). Confirmați acțiunea făcând clic pe „Ok”. Pe o foaie nouă (dacă este indicat) obținem date pentru regresie.

Utilizându-le, construim o ecuație liniară de forma y=ax+b, unde parametrii a și b sunt coeficienții dreptei cu numele numărului lunii și coeficienții și liniile „Y-intersection” din foaia cu rezultatele analizei de regresie. Astfel, ecuația de regresie liniară (LR) pentru sarcina 3 este scrisă ca:

Prețul produsului N = 11,714* număr lunar + 1727,54.

sau în notație algebrică

y = 11,714 x + 1727,54

Analiza rezultatelor

Pentru a decide dacă ecuația de regresie liniară rezultată este adecvată, se folosesc coeficienții de corelație multiplă (MCC) și de determinare, precum și testul Fisher și testul t Student. În foaia de calcul Excel cu rezultate de regresie, acestea sunt numite multiple R, R-pătrat, F-statistic și, respectiv, t-statistic.

KMC R face posibilă evaluarea gradului de apropiere a relației probabilistice dintre variabilele independente și dependente. Valoarea sa ridicată indică o legătură destul de puternică între variabilele „Numărul lunii” și „Prețul produsului N în ruble pe 1 tonă”. Cu toate acestea, natura acestei relații rămâne necunoscută.

Pătratul coeficientului de determinare R2 (RI) este o caracteristică numerică a proporției împrăștierii totale și arată a cărei împrăștiere este parte din datele experimentale, i.e. valorile variabilei dependente corespund ecuației de regresie liniară. În problema luată în considerare, această valoare este egală cu 84,8%, adică datele statistice sunt descrise cu un grad ridicat de acuratețe de către SD-ul rezultat.

F-statisticile, numite și testul lui Fisher, sunt folosite pentru a evalua semnificația unei relații liniare, infirmând sau confirmând ipoteza existenței acesteia.

(Testul Studentului) ajută la evaluarea semnificației coeficientului pentru un termen necunoscut sau liber al unei relații liniare. Dacă valoarea testului t > 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 cu o săgeată roșie din dreapta ferestrei „Interval de intrare X” și evidențiați intervalul tuturor valorilor din coloanele B, C, D, F de pe foaie.

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 procesorului de foi 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.