Regresia în Excel

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

Orez. 17. Fereastra SUPERSTRUCTURILE

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

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

Opțiuni Excel Buton de birou

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

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

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

Fereastra prezentată în fig. 21

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

Instrumentul de regresie folosește funcția LINIST.

Caseta de dialog REGRESIUNE

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

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

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

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

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

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

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

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

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

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

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

Funcţie LINIST

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

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

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

Orez. 23. Asistent de funcții

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

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

Alte funcții Referințe și tablouri Matematic

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

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

Ecuația unei linii drepte este următoarea:

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

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

LINIST(cunoscute_y; cunoscute_x; const; statistici)

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

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

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

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

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

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

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

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

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

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

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

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

Statistici suplimentare de regresie.

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

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

Note:

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

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

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

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

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

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

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

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

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

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

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

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

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

Formulele care returnează matrice trebuie introduse ca formule matrice.

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

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

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

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

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

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

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

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

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

Afișează efectul unor valori (independente, independente) asupra variabilei dependente. De exemplu, modul în care numărul populației active din punct de vedere economic depinde de numărul de întreprinderi, de mărimea salariilor și de alți parametri. Sau: cum afectează investițiile străine, prețurile la energie etc. nivelul PIB-ului.

Rezultatul analizei vă permite să stabiliți priorități. Și pe baza factorilor principali, anticipați, planificați dezvoltarea zonelor prioritare, luați decizii de management.

Are loc regresia:

Linear (y = a + bx);

Parabolic (y = a + bx + cx 2);

Exponențial (y = a * exp (bx));

· Puterea (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 un exemplu de construire a unui model de regresie în Excel și de interpretare a rezultatelor. Să luăm un tip de regresie liniară.

Sarcină. La 6 întreprinderi s-a analizat 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ă este următorul:

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

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

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

Excel are funcții încorporate pe care le puteți utiliza pentru a calcula parametrii unui model de regresie liniară. Dar programul de completare Analysis Package o va face mai rapid.

Activăm un instrument analitic puternic:

1. Apăsați butonul „Office” și accesați fila „Opțiuni Excel”. „Suplimente”.

2. În partea de jos, sub lista drop-down, în câmpul „Control” va apărea o inscripție „Excel add-ins” (dacă nu este acolo, dați clic pe caseta de selectare din dreapta și selectați). Și butonul „Du-te”. Apăsăm.

3. Se deschide o listă de programe de completare disponibile. Selectați „Pachet de analiză” și faceți clic pe OK.

La activare, programul de completare va fi disponibil în fila „Date”.

Acum să trecem direct la analiza de regresie.

1. Deschideți meniul instrumentului „Analiza datelor”. Selectăm „Regresie”.

2. Se va deschide un meniu pentru selectarea valorilor de intrare și a parametrilor de ieșire (unde se afișează rezultatul). În câmpurile pentru datele inițiale, indicăm intervalul parametrului descris (Y) și factorul care îl influențează (X). Restul poate fi lăsat necompletat.

3. După ce faceți clic pe OK, programul va afișa calculele pe o nouă foaie (puteți selecta intervalul de afișat pe foaia curentă sau puteți atribui rezultate unei cărți noi).

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

R-pătratul este coeficientul de determinare. În exemplul nostru - 0,755 sau 75,5%. Aceasta înseamnă că parametrii calculați ai modelului explică relația dintre parametrii studiați cu 75,5%. Cu cât coeficientul de determinare este mai mare, cu atât modelul este mai bun. Bun - peste 0,8. Rău - mai puțin de 0,5 (o astfel de analiză nu poate fi considerată 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ă alți factori care nu sunt descriși în model afectează și valoarea parametrului analizat.

Coeficientul -0,16285 arată ponderea variabilei X pe Y. Adică salariul mediu lunar în cadrul acestui model influențează numărul de persoane care renunță 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țini renunțări. Ceea ce este corect.

Construirea unei regresii liniare, estimarea parametrilor acesteia și a semnificației acestora poate fi realizată mult mai rapid folosind pachetul de analiză Excel (Regression). Luați în considerare interpretarea rezultatelor obținute în cazul general ( k variabile explicative) conform exemplului 3.6.

In masa statistici de regresie valorile sunt date:

Multiplu R - coeficient de corelație multiplă;

R- pătrat- coeficient de determinare R 2 ;

Normalizat R - pătrat- ajustat R 2 corectat pentru numărul de grade de libertate;

Eroare standard- eroare standard de regresie S;

Observatii - numărul de observații n.

In masa ANOVA sunt date:

1. Coloana df - numărul de grade de libertate, egal cu

pentru sfoară Regresia df = k;

pentru sfoară Restdf = n – k – 1;

pentru sfoară Totaldf = n– 1.

2. Coloana SS - suma pătratelor abaterilor egală cu

pentru sfoară Regresia ;

pentru sfoară Rest ;

pentru sfoară Total .

3. Coloana DOMNIȘOARĂ varianțe determinate de formulă DOMNIȘOARĂ = SS/df:

pentru sfoară Regresia- varianta factoriala;

pentru sfoară Rest- variaţia reziduală.

4. Coloana F - valoarea calculată F-criteriul calculat prin formula

F = DOMNIȘOARĂ(regresie)/ DOMNIȘOARĂ(rest).

5. Coloana Semnificaţie F - valoarea nivelului de semnificaţie corespunzător celui calculat F-statistici .

Semnificaţie F= FDIST ( F- statistici, df(regresie), df(rest)).

Dacă semnificaţia F < стандартного уровня значимости, то R 2 este semnificativ statistic.

	Coeficienți	Eroare standard	t-statistică	Valoarea P	De jos 95%	Top 95%
Y	65,92	11,74	5,61	0,00080	38,16	93,68
X	0,107	0,014	7,32	0,00016	0,0728	0,142

Acest tabel arată:

1. Cote- valorile coeficientului A, b.

2. Eroare standard–Erorile standard ale coeficienților de regresie S a, S b.

3. t- statistici- valori calculate t -criterii calculate prin formula:

t-statistic = Coeficienți / Eroare standard.

4.R-valoare (semnificație t) Este valoarea nivelului de semnificație corespunzătoare valorii calculate t- statistici.

R-valoare = TDIST(t-statistici, df(rest)).

Dacă R-sens< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5... 95% de jos și 95% de sus- limitele inferioare și superioare ale intervalelor de încredere de 95% pentru coeficienții ecuației teoretice de regresie liniară.

RETRAGERE RĂMÂNĂ
Observare	A prezis y	Rămâne e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

In masa RETRAGERE RĂMÂNĂ indicat:

în coloană Observare- numarul de observatie;

în coloană Cel prezis y - valorile calculate ale variabilei dependente;

în coloană Resturi e - diferența dintre valorile observate și calculate ale variabilei dependente.

Exemplul 3.6. Există date (unități convenționale) privind costurile cu alimentele yși venitul pe cap de locuitor X pentru nouă grupuri de familii:

X
y

Folosind rezultatele pachetului de analiză Excel (Regresie), să analizăm dependența costurilor alimentare de valoarea venitului pe cap de locuitor.

Se obișnuiește să scrieți rezultatele analizei de regresie sub forma:

unde erorile standard ale coeficienților de regresie sunt indicate în paranteze.

Coeficienți de regresie A = 65,92 și b= 0,107. Direcția de comunicare între yși X determină semnul coeficientului de regresie b= 0,107, adică legătura este directă și pozitivă. Coeficient b= 0,107 arată că cu o creștere a venitului pe cap de locuitor cu 1 conv. unitati costurile cu alimente cresc cu 0,107 conv. unitati

Să estimăm semnificația coeficienților modelului rezultat. Semnificația coeficienților ( a, b) este verificat de t-Test:

valoarea p ( A) = 0,00080 < 0,01 < 0,05

valoarea p ( b) = 0,00016 < 0,01 < 0,05,

prin urmare, coeficienții ( a, b) sunt semnificative la nivelul de 1% și cu atât mai mult la nivelul de semnificație de 5%. Astfel, coeficienții de regresie sunt semnificativi și modelul este adecvat datelor originale.

Rezultatele estimării regresiei sunt compatibile nu numai cu valorile obținute ale coeficienților de regresie, ci și cu o parte din setul acestora (interval de încredere). Cu o probabilitate de 95%, intervalele de încredere pentru coeficienți sunt (38,16 - 93,68) pentru Aşi (0,0728 - 0,142) pentru b.

Calitatea modelului este evaluată prin coeficientul de determinare R 2 .

Magnitudinea R 2 = 0,884 înseamnă că 88,4% din variația (răspândirea) cheltuielilor cu alimente poate fi explicată prin factorul venit pe cap de locuitor.

Semnificaţie R 2 este verificat de F- test: semnificație F = 0,00016 < 0,01 < 0,05, следовательно, R 2 este semnificativ la nivelul de 1% și cu atât mai mult la nivelul de semnificație de 5%.

În cazul regresiei liniare perechi, coeficientul de corelație poate fi definit ca ... Valoarea obținută a coeficientului de corelație indică faptul că relația dintre cheltuielile alimentare și venitul pe cap de locuitor este foarte strânsă.

Analiza regresiei este una dintre cele mai solicitate metode de cercetare statistică. Poate fi folosit pentru a stabili gradul de influență al variabilelor independente asupra variabilei dependente. În funcționalitatea Microsoft Excel, există instrumente concepute pentru acest tip de analiză. Să aruncăm o privire la ce sunt acestea și cum să le folosim.

Conexiune pachet de analiză

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

1. Treceți la fila „Fișier”.
2. Accesați secțiunea „Parametri”.
3. Se deschide fereastra Opțiuni Excel. Accesați subsecțiunea „Suplimente”.
4. Î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 poziție diferită. Faceți clic pe butonul „Go”.
5. Se deschide fereastra de suplimente Excel disponibile. Punem o bifă lângă elementul „Pachet de analiză”. Faceți clic pe butonul „OK”.

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

Tipuri de analiză de regresie

Există mai multe tipuri de regresii:

• parabolic;
• putere-lege;
• logaritmică;
• exponențial;
• indicativ;
• hiperbolic;
• regresie liniara.

Vom vorbi mai detaliat despre performanța ultimului tip de analiză de regresie în Excel.

Regresia liniară în Excel

Mai jos, de exemplu, există un tabel care arată temperatura medie zilnică a aerului de afară și numărul de cumpărători din magazin pentru ziua lucrătoare corespunzătoare. Să aflăm cu ajutorul analizei de regresie exact cum condițiile meteorologice sub formă de temperatura aerului pot afecta prezența la priză.

Ecuația generală de regresie liniară arată astfel: Y = a0 + a1x1 +… + akhk. În această formulă, Y înseamnă variabila, influența factorilor asupra cărora încercăm să studiem. În cazul nostru, acesta este numărul de cumpărători. Valoarea x reprezintă diferiții factori care afectează variabila. Parametrii a sunt coeficienții de regresie. Adică, ei sunt cei care determină semnificația acestui sau aceluia factor. Indicele k 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 pătratul R. Indică calitatea modelului. În cazul nostru, acest raport 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 Y” și a coloanei „Coeficienți”. 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, este nivelul de dependență a numărului de clienți ai magazinului de temperatură. Un raport de 1,31 este considerat un indicator de impact destul de ridicat.

După cum puteți vedea, este destul de ușor să creați un tabel de analiză de regresie folosind Microsoft Excel. Dar, doar o persoană instruită poate lucra cu datele obținute la 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, detaliind esența problemei. Experții noștri vor încerca să răspundă cât mai repede posibil.

Te-a ajutat acest articol?

Regresia liniară ne permite să descriem linia dreaptă care se potrivește cel mai bine cu o serie de perechi ordonate (x, y). Ecuația pentru o linie dreaptă, cunoscută sub numele de ecuație liniară, este prezentată mai jos:

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

x este variabila independentă,

a - segment pe axa y pentru o linie dreaptă,

b - panta unei drepte.

Figura de mai jos ilustrează acest concept grafic:

Imaginea de mai sus arată linia descrisă de ecuația ŷ = 2 + 0,5x. Segmentul de pe axa y este punctul de intersecție al dreptei cu 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 urcă 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 cele independente. Cu alte cuvinte, modificarea valorii x nu afectează valoarea y.

Ŷ și y sunt adesea confundate. Graficul arată 6 perechi ordonate de puncte și o linie conform acestei ecuații

Această figură arată punctul corespunzător perechii ordonate x = 2 și y = 4. Rețineți că valoarea așteptată a lui y conform dreptei de la NS= 2 este ŷ. Putem confirma acest lucru cu următoarea ecuație:

ŷ = 2 + 0,5x = 2 +0,5 (2) = 3.

Valoarea y este punctul real, iar valoarea este valoarea y așteptată folosind o ecuație liniară pentru o valoare x dată.

Următorul pas este determinarea ecuației liniare care se potrivește cel mai mult cu setul 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, puneți o bifă vizavi Pachet de analize, apăsăm BINE.

În fila Dateîntr-un grup Analiză va apărea un nou buton Analiza datelor.

Pentru a demonstra cum funcționează suplimentul, să folosim datele din articolul anterior, unde un tip și o fată împart o masă în baie. Introduceți datele pentru exemplul nostru de cadă de baie în coloanele A și B ale tabloului liber.

Accesați fila Date,într-un grup Analiză clic Analiza datelor.În fereastra care apare Analiza datelor alege Regresia așa cum se arată și faceți clic pe OK.

Setați parametrii de regresie necesari î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 prin autocalculare în articolul anterior.

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

Tipuri de regresie

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

• liniar;
• parabolic;
• putere-lege;
• exponențial;
• hiperbolic;
• indicativ;
• logaritmică.

Exemplul 1

Să luăm în considerare problema determinării dependenței numărului de angajați care și-au părăsit locul de muncă de salariul mediu la 6 întreprinderi industriale.

Sarcină. Șase întreprinderi au analizat salariul mediu lunar și numărul de angajați care au renunțat voluntar. În formă tabelară, avem:

Pentru problema determinării dependenței numărului de salariați părăsiți de salariul mediu la 6 întreprinderi, modelul de regresie are forma ecuației Y = a0 + a1 × 1 +… + akxk, unde xi sunt variabile de influență, ai sunt coeficienți 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.

Utilizarea capabilităților procesorului de tabel 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 programul de completare foarte util „Pachet de analiză”. Pentru a-l activa aveți nevoie de:

• din fila „Fișier” accesați secțiunea „Parametri”;
• în fereastra care se deschide, selectați linia „Suplimente”;
• faceți clic pe butonul „Go” situat mai jos, în dreapta liniei „Control”;
• bifați 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ă avem la îndemână toate instrumentele virtuale necesare pentru efectuarea calculelor 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 au demisionat) și pentru X (salariile lor);
• ne confirmăm acțiunile apăsând butonul „Ok”.

Ca urmare, programul va completa automat noua foaie a procesorului de foi de calcul cu datele analizei de regresie. Notă! Excel are capacitatea de a defini independent locația pe care o preferați în acest scop. De exemplu, ar putea fi aceeași foaie care conține valorile Y și X sau chiar un nou registru de lucru special conceput pentru a stoca acest tip de date.

Analizarea rezultatelor regresiei pentru R-pătrat

În Excel, datele obținute în cursul prelucrării datelor din exemplul în cauză sunt următoarele:

În primul rând, ar trebui să acordați atenție valorii pătratului R. 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 considerați cu 75,5%. Cu cât valoarea coeficientului de determinare este mai mare, cu atât modelul ales este considerat a fi mai aplicabil pentru o anumită sarcină. Se crede că descrie corect situația reală când valoarea R-pătratului este peste 0,8. Dacă R-pătratul este tcr, atunci ipoteza nesemnificației termenului liber al ecuației liniare este respinsă.

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

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

Problema oportunității cumpără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ă aplicată specifică.

Conducerea companiei „NNN” trebuie să decidă oportunitatea achiziționării unei participații de 20% la SA „MMM”. Costul pachetului (JV) este de 70 milioane USD. Specialiștii NNN au colectat date despre tranzacții similare. S-a decis evaluarea valorii blocului de acțiuni prin astfel de parametri, exprimați în milioane de dolari SUA, astfel:

• conturi de plătit (VK);
• volumul cifrei de afaceri anuale (VO);
• conturi de încasat (VD);
• costul mijloacelor fixe (SOF).

În plus, parametrul este restanțele salariale ale întreprinderii (V3 P) în mii de dolari SUA.

Soluție Excel pentru foi de calcul

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

• apelați fereastra „Analiza datelor”;
• selectați secțiunea „Regresie”;
• intervalul de valori ale variabilelor dependente din coloana G este introdus în caseta „Interval de intrare Y”;
• faceți clic pe pictograma cu o săgeată roșie din dreapta ferestrei „Interval de intrare X” și selectați pe foaie intervalul tuturor valorilor din coloanele B, C, D, F.

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

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

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ă ca:

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

Datele pentru JSC „MMM” sunt prezentate în tabel:

Înlocuindu-le în ecuația de regresie, cifra este de 64,72 milioane de dolari SUA. Aceasta înseamnă că acțiunile SA „MMM” nu ar trebui cumpărate, deoarece valoarea lor de 70 de milioane de dolari SUA este mai degrabă supraevaluată.

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 oportunitatea unei tranzacții foarte specifice.

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