Graficul de regresie liniară în Excel. Construirea unei ecuații de regresie multiplă în Excel

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

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.

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;
• putere;
• logaritmică;
• exponențial;
• demonstrație;
• hiperbolic;
• regresie liniara.

Despre executarea ultimei vederi analiza regresiei Vom vorbi mai multe despre Excel mai târziu.

Regresia liniară în Excel

Mai jos, de exemplu, este un tabel care arată temperatura medie zilnică a aerului de pe stradă și numărul de clienți ai magazinului pentru ziua lucrătoare corespunzătoare. Să aflăm cu ajutorul analizei 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ă arată astfel: Y = a0 + a1x1 + ... + axk. În această formulă Yînseamnă variabila a cărei influență încercăm să o studiem. În cazul nostru, acesta este numărul de cumpărători. Sens X- Acest diverși factori care afectează variabila. Opțiuni A sunt coeficienții de regresie. Adică, ele determină semnificația unui anumit factor. Index 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 R-pătrat. Indică calitatea modelului. În cazul nostru coeficient dat este 0,705 sau aproximativ 70,5%. Acesta este un nivel acceptabil de calitate. O relație mai mică de 0,5 este proastă.

Un alt indicator important este situat în celula de la intersecția liniei „Intersecția în Y” si coloana „Coeficienți”. Aici este indicată ce valoare va avea Y, iar î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 „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 din magazin de temperatură. Un coeficient de 1,31 este considerat destul de bun Rata ridicată influență.

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.

Metodă regresie liniara ne permite să descriem o linie dreaptă care corespunde cât mai aproape cu putință unei serii de perechi ordonate (x, y). Ecuația pentru o linie dreaptă, cunoscută sub numele de ecuație liniară, este dată mai jos:

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

x este o variabilă independentă,

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

b este panta dreptei.

În figura de mai jos, acest concept este reprezentat grafic:

Figura de mai sus prezintă o dreaptă descrisă de ecuația ŷ =2+0,5x. Segmentul de linie de pe axa y este punctul în care linia intersectează axa y; în cazul nostru, a = 2. Panta dreptei, b, raportul dintre ridicarea liniei și lungimea liniei, 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 este punctul real, iar valoarea ŷ este valoarea y așteptată folosind ecuație liniară pentru o valoare dată a lui x.

Următorul pas este determinarea ecuației liniare care corespunde maxim mulțimii de perechi ordonate, despre asta am vorbit în articolul anterior, unde am determinat forma ecuației prin .

Utilizarea Excel pentru a defini regresia liniară

Pentru a utiliza instrumentul de analiză de regresie încorporat în Excel, trebuie să activați programul de completare Pachet de analize. Îl puteți găsi făcând clic pe filă Fișier –> Opțiuni(2007+), în dialogul 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 apărea un nou buton Analiza datelor.

Pentru a demonstra cum funcționează suplimentul, să folosim datele , unde un tip și o fată împart o masă în baie. Introduceți datele pentru exemplul nostru de baie în coloanele A și B ale unei foi 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 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 calcule independente în .

Folosind metoda grafică.
Această metodă este utilizată pentru vizualizarea formei de comunicare între indicatorii economici studiați. Pentru a face acest lucru, un grafic este trasat într-un sistem de coordonate dreptunghiular, valorile individuale ale atributului rezultat Y sunt reprezentate de-a lungul axei ordonatelor, iar valorile individuale ale atributului factorului X sunt reprezentate de-a lungul axei absciselor.
Se numește setul de puncte al semnelor efective și factorilor câmpul de corelare.
Pe baza câmpului de corelație, poate fi formulată o ipoteză (pentru populatie) că relația dintre toate valorile posibile ale lui X și Y este liniară.

Ecuația de regresie liniară are forma y = bx + a + ε
Aici ε este o eroare aleatorie (abatere, perturbare).
Motive pentru existența unei erori aleatorii:
1. Neincluderea variabilelor explicative semnificative în modelul de regresie;
2. Agregarea variabilelor. De exemplu, funcția de consum total este o încercare de exprimare generală a totalității deciziilor individuale de cheltuieli ale indivizilor. Aceasta este doar o aproximare a relațiilor individuale care au parametri diferiți.
3. Descrierea incorectă a structurii modelului;
4. Specificație funcțională greșită;
5. Erori de măsurare.
Deoarece abaterile ε i pentru fiecare observație particulară i sunt aleatoare și valorile lor în eșantion sunt necunoscute, atunci:
1) conform observațiilor x i și y i se pot obține doar estimări ale parametrilor α și β
2) Estimările parametrilor α și β ai modelului de regresie sunt, respectiv, valorile a și b, care sunt de natură aleatorie, deoarece corespund unui eșantion aleatoriu;
Apoi, ecuația de regresie estimată (construită din datele eșantionului) va arăta ca y = bx + a + ε, unde e i sunt valorile observate (estimări) ale erorilor ε i și, respectiv, b, estimările parametrii α și β ai modelului de regresie care ar trebui găsiți.
Pentru a estima parametrii α și β - utilizați LSM (metoda cele mai mici pătrate).
Sistem de ecuații normale.

Pentru datele noastre, sistemul de ecuații are forma:

10a + 356b = 49
356a + 2135b = 9485

Exprimați a din prima ecuație și înlocuiți-l în a doua ecuație
Obținem b = 68,16, a = 11,17

Ecuația de regresie:
y = 68,16 x - 11,17

1. Parametrii ecuației de regresie.
Eșantion înseamnă.



Variante de eșantion.


deviație standard

1.1. Coeficient de corelație
Calculăm indicatorul de apropiere a comunicării. Acest indicator este un eșantion coeficient liniar corelație, care se calculează prin formula:

Coeficientul de corelație liniară ia valori de la –1 la +1.
Relațiile dintre caracteristici pot fi slabe sau puternice (strânse). Criteriile lor sunt punctate pe Scala Chaddock:
0.1 < r xy < 0.3: слабая;
0.3 < r xy < 0.5: умеренная;
0.5 < r xy < 0.7: заметная;
0.7 < r xy < 0.9: высокая;
0.9 < r xy < 1: весьма высокая;
În exemplul nostru, relația dintre caracteristica Y factorul X este foarte mare și directă.

1.2. Ecuația de regresie(evaluarea ecuației de regresie).

Ecuația de regresie liniară este y = 68,16 x -11,17
Se pot da coeficienții ecuației de regresie liniară sens economic. Coeficientul ecuației de regresie arată câte unități rezultatul se va schimba atunci când factorul se schimbă cu 1 unitate.
Coeficientul b = 68,16 arată modificarea medie a indicatorului efectiv (în unități de y) cu o creștere sau scădere a valorii factorului x pe unitatea de măsură a acestuia. În acest exemplu, cu o creștere de 1 unitate, y crește cu o medie de 68,16.
Coeficientul a = -11,17 arată în mod formal nivelul prezis al lui y, dar numai dacă x=0 este aproape de valorile eșantionului.
Dar dacă x=0 este departe de valorile eșantionului x, atunci o interpretare literală poate duce la rezultate incorecte și chiar dacă linia de regresie descrie cu acuratețe valorile eșantionului observat, nu există nicio garanție că aceasta va fi, de asemenea, cazul extrapolării la stânga sau la dreapta.
Prin înlocuirea valorilor corespunzătoare ale lui x în ecuația de regresie, este posibil să se determine valorile aliniate (prevăzute) ale indicatorului efectiv y(x) pentru fiecare observație.
Relația dintre y și x determină semnul coeficientului de regresie b (dacă > 0 - relație directă, în caz contrar - inversă). În exemplul nostru, conexiunea este directă.

1.3. coeficient de elasticitate.
Nu este de dorit să se utilizeze coeficienți de regresie (în exemplul b) pentru o evaluare directă a influenței factorilor asupra atributului efectiv în cazul în care există o diferență între unitățile de măsură ale indicatorului efectiv y și atributul factorului x.
În aceste scopuri, se calculează coeficienții de elasticitate și coeficienții beta. Coeficientul de elasticitate se gaseste prin formula:


Arată câte procente se modifică în medie atributul efectiv y atunci când atributul factorului x se modifică cu 1%. Nu ține cont de gradul de fluctuație al factorilor.
În exemplul nostru, coeficientul de elasticitate este mai mare decât 1. Prin urmare, dacă X se modifică cu 1%, Y se va modifica cu mai mult de 1%. Cu alte cuvinte, X îl afectează semnificativ pe Y.
Coeficientul beta arată cu ce parte a valorii abaterii sale standard se va modifica în medie valoarea atributului efectiv atunci când atributul factorului se modifică cu valoarea abaterii sale standard cu valoarea variabilelor independente rămase fixată la un nivel constant:

Acestea. o creștere a x cu valoarea abaterii standard a acestui indicator va duce la o creștere a mediei Y cu 0,9796 a abaterii standard a acestui indicator.

1.4. Eroare de aproximare.
Să evaluăm calitatea ecuației de regresie folosind eroarea de aproximare absolută.


Deoarece eroarea este mai mare de 15%, această ecuație nu este de dorit să fie utilizată ca regresie.

1.6. Coeficient de determinare.
Pătratul coeficientului de corelație (multiplu) se numește coeficient de determinare, care arată proporția variației atributului rezultat explicată de variația atributului factorului.
Cel mai adesea, dând o interpretare a coeficientului de determinare, acesta este exprimat ca procent.
R2 = 0,982 = 0,9596
acestea. în 95,96% din cazuri, modificările în x conduc la o schimbare în y. Cu alte cuvinte, acuratețea selecției ecuației de regresie este mare. Variația rămasă de 4,04% în Y se datorează unor factori care nu au fost luați în considerare în model.

X	y	x2	y2	X y	y(x)	(y i -y cp) 2	(y-y(x)) 2	(x i -x cp) 2	|y - y x |:y
0.371	15.6	0.1376	243.36	5.79	14.11	780.89	2.21	0.1864	0.0953
0.399	19.9	0.1592	396.01	7.94	16.02	559.06	15.04	0.163	0.1949
0.502	22.7	0.252	515.29	11.4	23.04	434.49	0.1176	0.0905	0.0151
0.572	34.2	0.3272	1169.64	19.56	27.81	87.32	40.78	0.0533	0.1867
0.607	44.5	.3684	1980.25	27.01	30.2	0.9131	204.49	0.0383	0.3214
0.655	26.8	0.429	718.24	17.55	33.47	280.38	44.51	0.0218	0.2489
0.763	35.7	0.5822	1274.49	27.24	40.83	61.54	26.35	0.0016	0.1438
0.873	30.6	0.7621	936.36	26.71	48.33	167.56	314.39	0.0049	0.5794
2.48	161.9	6.17	26211.61	402	158.07	14008.04	14.66	2.82	0.0236
7.23	391.9	9.18	33445.25	545.2	391.9	16380.18	662.54	3.38	1.81

2. Estimarea parametrilor ecuației de regresie.
2.1. Semnificația coeficientului de corelație.

Conform tabelului Student cu nivel de semnificație α=0,05 și grade de libertate k=7 găsim t crit:
t crit = (7;0,05) = 1,895
unde m = 1 este numărul de variabile explicative.
Dacă t obs > t este critic, atunci valoarea obținută a coeficientului de corelație este recunoscută ca semnificativă (se respinge ipoteza nulă care afirmă că coeficientul de corelație este egal cu zero).
Deoarece t obl > t crit, respingem ipoteza că coeficientul de corelație este egal cu 0. Cu alte cuvinte, coeficientul de corelație este semnificativ statistic
Într-o regresie liniară pereche, t 2 r = t 2 b, iar apoi testarea ipotezelor despre semnificația coeficienților de regresie și corelație este echivalentă cu testarea ipotezei despre semnificația ecuației de regresie liniară.

2.3. Analiza acurateței determinării estimărilor coeficienților de regresie.
Estimarea imparțială a varianței perturbațiilor este valoarea:


S 2 y = 94,6484 - varianță inexplicabilă (o măsură a dispersiei variabilei dependente în jurul liniei de regresie).
S y = 9,7287 - eroare standard estimări (eroarea standard de regresie).
Sa- deviație standard variabilă aleatorie A.


S b - abaterea standard a variabilei aleatoare b.

2.4. Intervale de încredere pentru variabila dependentă.
Prognoza economică bazată pe modelul construit presupune că relațiile preexistente ale variabilelor sunt păstrate și pentru perioada de plumb.
Pentru a prezice variabila dependentă a atributului rezultat, este necesar să se cunoască valorile predictive ale tuturor factorilor incluși în model.
Valorile predictive ale factorilor sunt substituite în model și se obțin estimări predictive punctuale ale indicatorului studiat. (a + bx p ± ε)
Unde

Să calculăm limitele intervalului în care 95% din valorile posibile ale lui Y vor fi concentrate cu un număr nelimitat de observații și X p = 1 (-11,17 + 68,16*1 ± 6,4554)
(50.53;63.44)

Intervalele individuale de încredere pentruYla o valoare datăX.
(a + bx i ± ε)
Unde

x i	y = -11,17 + 68,16x i	ε i	ymin	ymax
0.371	14.11	19.91	-5.8	34.02
0.399	16.02	19.85	-3.83	35.87
0.502	23.04	19.67	3.38	42.71
0.572	27.81	19.57	8.24	47.38
0.607	30.2	19.53	10.67	49.73
0.655	33.47	19.49	13.98	52.96
0.763	40.83	19.44	21.4	60.27
0.873	48.33	19.45	28.88	67.78
2.48	158.07	25.72	132.36	183.79

Cu o probabilitate de 95%, se poate garanta că valoarea lui Y cu un număr nelimitat de observații nu va depăși limitele intervalelor găsite.

2.5. Testarea ipotezelor privind coeficienții ecuației de regresie liniară.
1) t-statistici. Criteriul elevului.
Să testăm ipoteza H 0 despre egalitatea coeficienților individuali de regresie la zero (cu alternativa H 1 nu este egală) la nivelul de semnificație α=0,05.
t crit = (7;0,05) = 1,895


Deoarece 12,8866 > 1,895 se confirmă semnificația statistică a coeficientului de regresie b (respingem ipoteza că acest coeficient este egal cu zero).


Deoarece 2,0914 > 1,895 se confirmă semnificația statistică a coeficientului de regresie a (respingem ipoteza că acest coeficient este egal cu zero).

Interval de încredere pentru coeficienții ecuației de regresie.
Să determinăm intervalele de încredere ale coeficienților de regresie, care, cu o fiabilitate de 95%, vor fi după cum urmează:
(b - t crit S b; b + t crit S b)
(68.1618 - 1.895 5.2894; 68.1618 + 1.895 5.2894)
(58.1385;78.1852)
Cu o probabilitate de 95%, se poate argumenta că valoarea acestui parametru se va afla în intervalul găsit.
(la o)
(-11.1744 - 1.895 5.3429; -11.1744 + 1.895 5.3429)
(-21.2992;-1.0496)
Cu o probabilitate de 95%, se poate argumenta că valoarea acestui parametru se va afla în intervalul găsit.

2) F-statistici. criteriul lui Fisher.
Semnificația modelului de regresie este verificată cu ajutorul testului F Fisher, a cărui valoare calculată se găsește ca raport dintre varianța seriei inițiale de observații a indicatorului studiat și estimarea nepărtinitoare a varianței secvenței reziduale pentru acest model.
Dacă valoarea calculată cu lang=EN-US>n-m-1) grade de libertate este mai mare decât valoarea tabelată la un anumit nivel de semnificație, atunci modelul este considerat semnificativ.

unde m este numărul de factori din model.
Evaluarea semnificației statistice a regresiei liniare perechi se efectuează conform următorului algoritm:
1. Se propune o ipoteză nulă conform căreia ecuația în ansamblu este nesemnificativă statistic: H 0: R 2 =0 la nivelul de semnificație α.
2. Apoi, determinați valoarea reală a criteriului F:


unde m=1 pentru regresia pe perechi.
3. Valoarea tabelului este determinată din tabelele de distribuție Fisher pentru un anumit nivel de semnificație, ținând cont de faptul că numărul de grade de libertate pentru suma totală a pătratelor (varianță mai mare) este 1 și numărul de grade de libertate pentru suma reziduală a pătrate (varianță mai mică) în regresia liniară este n-2 .
4. Dacă valoarea reală a criteriului F este mai mică decât valoarea tabelului, atunci ei spun că nu există niciun motiv pentru a respinge ipoteza nulă.
În caz contrar, ipoteza nulă este respinsă și cu probabilitate (1-α) ipoteza alternativă despre semnificație statistică ecuații în general.
Valoarea tabelului criteriului cu grade de libertate k1=1 și k2=7, Fkp = 5,59
Deoarece valoarea reală a lui F > Fkp, coeficientul de determinare este semnificativ statistic (estimarea găsită a ecuației de regresie este fiabilă statistic).

Verificați autocorelarea reziduurilor.
O condiție prealabilă importantă pentru construirea unui model de regresie calitativă folosind LSM este independența valorilor abaterilor aleatoare față de valorile abaterilor din toate celelalte observații. Acest lucru asigură că nu există nicio corelație între orice abateri și, în special, între abaterile adiacente.
Autocorelație (corelație în serie) definită ca corelația dintre măsurile observate ordonate în timp (seri de timp) sau spațiu (seri încrucișate). Autocorelarea reziduurilor (outliers) este frecvent întâlnită în analiza de regresie atunci când se utilizează date de serie de timp și foarte rar când se utilizează date transversale.
În sarcinile economice, este mult mai frecvent autocorelație pozitivă decât autocorelație negativă. În cele mai multe cazuri, autocorelația pozitivă este cauzată de o influență constantă direcțională a unor factori neluați în considerare în model.
Autocorelație negativăînseamnă de fapt că o abatere pozitivă este urmată de una negativă și invers. O astfel de situație poate avea loc dacă se consideră aceeași relație între cererea de băuturi răcoritoare și venituri în funcție de datele sezoniere (iarna-vara).
Printre cauzele principale care determină autocorelarea, se pot distinge următoarele:
1. Erori de specificație. Eșecul de a lua în considerare vreo variabilă explicativă importantă în model sau alegerea greșită a formei de dependență duce de obicei la abateri sistemice ale punctelor de observație de la linia de regresie, ceea ce poate duce la autocorelare.
2. Inerția. Mulți indicatori economici (inflație, șomaj, PNB etc.) au o anumită ciclicitate asociată cu ondulația activității afacerilor. Prin urmare, modificarea indicatorilor nu are loc instantaneu, ci are o anumită inerție.
3. Efect web. În multe domenii industriale și de altă natură, indicatorii economici reacționează la schimbările condițiilor economice cu întârziere (decalaj de timp).
4. Netezirea datelor. Adesea, datele pentru o anumită perioadă lungă de timp sunt obținute prin mediarea datelor pe intervalele lor constitutive. Acest lucru poate duce la o anumită netezire a fluctuațiilor care au existat în perioada luată în considerare, care, la rândul său, poate provoca autocorelare.
Consecințele autocorelației sunt similare cu cele ale heteroscedasticității: concluziile privind statisticile t și F care determină semnificația coeficientului de regresie și a coeficientului de determinare pot fi incorecte.

Detectarea autocorelației

1. Metoda grafică
Există o serie de opțiuni pentru definirea grafică a autocorelației. Una dintre ele raportează abaterile e i de momentele primirii lor i. În același timp, fie timpul de obținere a datelor statistice, fie numărul de serie al observației este reprezentat de-a lungul axei absciselor, iar deviațiile e i (sau estimările abaterilor) sunt reprezentate de-a lungul axei ordonatelor.
Este firesc să presupunem că, dacă există o anumită relație între abateri, atunci are loc autocorelația. Absența dependenței va indica cel mai probabil absența autocorelației.
Autocorelația devine mai clară dacă trasați e i față de e i-1 .

Testul Durbin-Watson.
Acest criteriu este cel mai cunoscut pentru detectarea autocorelației.
În analiza statistică a ecuaţiei de regresie pe stadiul inițial adesea ei verifică fezabilitatea unei premise: condițiile independenței statistice a abaterilor unele față de altele. În acest caz, se verifică necorelarea valorilor învecinate e i.

y	y(x)	e i = y-y(x)	e 2	(e i - e i-1) 2
15.6	14.11	1.49	2.21	0
19.9	16.02	3.88	15.04	5.72
22.7	23.04	-0.3429	0.1176	17.81
34.2	27.81	6.39	40.78	45.28
44.5	30.2	14.3	204.49	62.64
26.8	33.47	-6.67	44.51	439.82
35.7	40.83	-5.13	26.35	2.37
30.6	48.33	-17.73	314.39	158.7
161.9	158.07	3.83	14.66	464.81
			662.54	1197.14

Pentru a analiza corelația abaterilor, se folosesc statisticile Durbin-Watson:

Valorile critice d 1 și d 2 sunt determinate pe baza unor tabele speciale pentru nivelul de semnificație necesar α, numărul de observații n = 9 și numărul de variabile explicative m=1.
Nu există autocorelare dacă următoarea condiție este adevărată:
d1< DW и d 2 < DW < 4 - d 2 .
Fără a ne referi la tabele, putem folosi regula aproximativă și putem presupune că nu există o autocorelare a reziduurilor dacă 1,5< DW < 2.5. Для более надежного вывода целесообразно обращаться к табличным значениям.

Linia de regresie este o reflectare grafică a relației dintre fenomene. Puteți construi cu ușurință o linie de regresie în Excel.

Pentru asta ai nevoie de:

1.Deschideți programul Excel

2. Creați coloane cu date. În exemplul nostru, vom construi o linie de regresie, sau o relație, între agresivitate și îndoială de sine la elevii de clasa întâi. Experimentul a implicat 30 de copii, datele sunt prezentate în tabelul Excel:

1 coloană - numărul subiectului

2 coloană - agresivitateîn puncte

3 coloana - neîncredereaîn puncte

3. Apoi trebuie să selectați ambele coloane (fără numele coloanei), apăsați fila introduce , alege punct , iar dintre machetele propuse alegeți chiar primul punct cu markere .

4. Așa că am obținut un spațiu liber pentru linia de regresie - așa-numita - diagramă de dispersie. Pentru a merge la linia de regresie, trebuie să faceți clic pe figura rezultată, faceți clic pe fila constructor, găsiți pe panou layout-uri grafice și alegeți M A ket9 , se mai spune f(x)

5. Deci, avem o linie de regresie. Graficul arată, de asemenea, ecuația și pătratul coeficientului de corelație

6. Rămâne de adăugat numele graficului, numele axelor. De asemenea, dacă doriți, puteți elimina legenda, puteți reduce numărul de linii orizontale ale grilei (tab aspect , apoi net ). Principalele modificări și setări sunt făcute în filă Aspect

Linia de regresie este construită în MS Excel. Acum poate fi adăugat la textul lucrării.

În notele anterioare, accentul a fost adesea pus pe o singură variabilă numerică, cum ar fi randamentul fondurilor mutuale, timpul de încărcare a paginii web sau consumul de băuturi răcoritoare. În aceasta și următoarele note, vom lua în considerare metode de predicție a valorilor unei variabile numerice în funcție de valorile uneia sau mai multor alte variabile numerice.

Materialul va fi ilustrat printr-un exemplu. Estimarea volumului vânzărilor într-un magazin de îmbrăcăminte. Lanțul de magazine de îmbrăcăminte cu discount Sunflowers se extinde constant de 25 de ani. Cu toate acestea, compania nu are în prezent o abordare sistematică pentru selectarea de noi puncte de vânzare. Locația în care compania intenționează să deschidă un nou magazin este determinată pe baza unor considerente subiective. Criteriile de selecție sunt termeni profitabiliînchiriere sau ideea managerului de locație ideală a magazinului. Imaginează-ți că ești șeful Departamentului Proiecte Speciale și Planificare. Ai fost însărcinat cu elaborarea unui plan strategic pentru deschiderea de noi magazine. Acest plan ar trebui să conțină o prognoză a vânzărilor anuale în magazinele nou deschise. Considerați că vânzarea spațiului este direct legată de venituri și doriți să luați în considerare acest fapt în procesul de luare a deciziilor. Cum dezvoltați un model statistic care prezice vânzările anuale pe baza dimensiunii noii magazin?

De obicei, analiza de regresie este utilizată pentru a prezice valorile unei variabile. Scopul său este de a dezvolta un model statistic care prezice valorile variabilei dependente, sau răspunsul, din valorile a cel puțin unei variabile independente sau explicative. În această notă, vom lua în considerare o regresie liniară simplă - o metodă statistică care vă permite să preziceți valorile variabilei dependente Y prin valorile variabilei independente X. Următoarele note vor descrie modelul regresie multiplă, conceput pentru a prezice valorile variabilei independente Y prin valorile mai multor variabile dependente ( X 1 , X 2 , …, X k).

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

Tipuri de modele de regresie

Unde ρ 1 este coeficientul de autocorelare; Dacă ρ 1 = 0 (fără autocorelare), D≈ 2; Dacă ρ 1 ≈ 1 (autocorelație pozitivă), D≈ 0; Dacă ρ 1 = -1 (autocorelație negativă), D ≈ 4.

În practică, aplicarea criteriului Durbin-Watson se bazează pe o comparație a valorii D cu valori teoretice critice dLȘi d U pentru un număr dat de observații n, numărul de variabile independente ale modelului k(pentru regresie liniară simplă k= 1) și nivelul de semnificație α. Dacă D< d L , se respinge ipoteza independenței abaterilor aleatoare (deci, există o autocorelație pozitivă); Dacă D > d U, ipoteza nu este respinsă (adică nu există autocorelație); Dacă dL< D < d U nu există suficiente motive pentru a lua o decizie. Când valoarea calculată D depășește 2, atunci dLȘi d U nu coeficientul în sine este comparat D, iar expresia (4 – D).

Pentru a calcula statisticile Durbin-Watson în Excel, ne întoarcem la tabelul de jos din Fig. 14 Retragerea soldului. Numătorul din expresia (10) este calculat folosind funcția = SUMMQDIFF(array1, array2) și numitorul = SUMMQ(array) (Fig. 16).

Orez. 16. Formule pentru calcularea statisticilor Durbin-Watson

În exemplul nostru D= 0,883. Întrebarea principală este: ce valoare a statisticii Durbin-Watson ar trebui considerată suficient de mică pentru a concluziona că există o autocorelație pozitivă? Este necesar să se coreleze valoarea lui D cu valorile critice ( dLȘi d U) în funcţie de numărul de observaţii nși nivelul de semnificație α (Fig. 17).

Orez. 17. Valorile critice ale statisticilor Durbin-Watson (fragment de tabel)

Astfel, în problema volumului vânzărilor într-un magazin care livrează mărfuri la domiciliu, există o variabilă independentă ( k= 1), 15 observații ( n= 15) și nivelul de semnificație α = 0,05. Prin urmare, dL= 1,08 și dU= 1,36. Deoarece D = 0,883 < dL= 1,08, există o autocorelație pozitivă între reziduuri, metoda celor mai mici pătrate nu poate fi aplicată.

Testarea ipotezelor despre panta și coeficientul de corelație

Regresia de mai sus a fost aplicată numai pentru prognoză. Pentru a determina coeficienții de regresie și pentru a prezice valoarea unei variabile Y pentru o anumită valoare variabilă X s-a folosit metoda celor mai mici pătrate. În plus, am luat în considerare eroarea standard a estimării și coeficientul de corelație mixtă. Dacă analiza reziduală confirmă că nu sunt încălcate condițiile de aplicabilitate a metodei celor mai mici pătrate, iar modelul de regresie liniară simplă este adecvat, pe baza datelor din eșantion, se poate argumenta că între variabilele din populație există dependență liniară.

Aplicațiet -criterii pentru panta. Verificând dacă panta populației β 1 este egală cu zero, se poate determina dacă există o relație semnificativă statistic între variabile XȘi Y. Dacă această ipoteză este respinsă, se poate argumenta că între variabile XȘi Y există o relație liniară. Ipotezele nule și alternative sunt formulate astfel: H 0: β 1 = 0 (fără relație liniară), H1: β 1 ≠ 0 (există o relație liniară). A-prioriu t-statistica este egală cu diferența dintre panta eșantionului și panta ipotetică a populației, împărțită la eroarea standard a estimării pantei:

(11) t = (b 1 – β 1 ) / Sb 1

Unde b 1 este panta regresiei directe pe baza datelor eșantionului, β1 este panta ipotetică a populației generale directe, , și statistici de testare t Are t- distributie cu n - 2 grade de libertate.

Să verificăm dacă există o relație semnificativă statistic între dimensiunea magazinului și vânzările anuale la α = 0,05. t-criteriile sunt afișate împreună cu alți parametri când se utilizează Pachet de analize(opțiune Regresia). Rezultatele complete ale pachetului de analiză sunt prezentate în Fig. 4, un fragment legat de t-statistica - în fig. 18.

Orez. 18. Rezultatele aplicării t

Deoarece numărul de magazine n= 14 (vezi Fig. 3), valoare critică t-statisticile la un nivel de semnificație α = 0,05 pot fi găsite prin formula: t L=STUDENT.INV(0,025;12) = -2,1788 unde 0,025 este jumătate din nivelul de semnificație și 12 = n – 2; t U\u003d STUDENT.INR (0,975, 12) \u003d +2,1788.

Deoarece t-statistica = 10,64 > t U= 2,1788 (Fig. 19), ipoteză nulă H 0 este respins. Pe de alta parte, R-valoare pentru X\u003d 10,6411, calculat prin formula \u003d 1-STUDENT.DIST (D3, 12, TRUE), este aproximativ egal cu zero, deci ipoteza H 0 este respins din nou. Faptul că R-valoarea este aproape zero, ceea ce înseamnă că dacă nu ar exista o relație liniară reală între dimensiunea magazinului și vânzările anuale, ar fi aproape imposibil să o găsim folosind regresia liniară. Prin urmare, există o relație liniară semnificativă statistic între vânzările medii anuale ale magazinului și dimensiunea magazinului.

Orez. 19. Testarea ipotezei despre panta populației generale la un nivel de semnificație de 0,05 și 12 grade de libertate

AplicațieF -criterii pentru panta. O abordare alternativă pentru testarea ipotezelor despre panta unei regresii liniare simple este de a utiliza F-criterii. Amintește-ți asta F-criteriul este folosit pentru a testa relația dintre două varianțe (vezi detalii). Când se testează ipoteza pantei, măsura erorilor aleatoare este varianța erorii (suma erorilor pătrate împărțită la numărul de grade de libertate), deci F-testul folosește raportul varianței explicat prin regresie (adică, valorile SSRîmpărțit la numărul de variabile independente k), la variația erorii ( MSE=SYX 2 ).

A-prioriu F-statistica este egală cu abaterile pătratice medii datorate regresiei (MSR) împărțite la varianța erorii (MSE): F = MSR/ MSE, Unde MSR=SSR / k, MSE =SSE/(n– k – 1), k este numărul de variabile independente din modelul de regresie. Test statistici F Are F- distributie cu kȘi n– k – 1 grade de libertate.

Pentru un nivel de semnificaţie dat α, regula de decizie se formulează astfel: dacă F > FU, se respinge ipoteza nulă; în caz contrar, nu este respins. Rezultatele prezentate sub forma unui tabel pivot analiza variatiei sunt prezentate în fig. 20.

Orez. 20. Tabel de analiză a varianței pentru testarea ipotezei semnificației statistice a coeficientului de regresie

În mod similar t-criteriu F-criteriile sunt afișate în tabel când se utilizează Pachet de analize(opțiune Regresia). Rezultatele complete ale lucrării Pachet de analize prezentată în fig. 4, fragment legat de F-statistica - in fig. 21.

Orez. 21. Rezultatele aplicării F- Criterii obținute folosind Excel Analysis ToolPack

F-statistica este 113,23 și R-valoare apropiată de zero (celula SemnificaţieF). Dacă nivelul de semnificație α este 0,05, determinați valoarea critică F-din formula se pot obtine distributii cu unu si 12 grade de libertate F U\u003d F. OBR (1-0,05; 1; 12) \u003d 4,7472 (Fig. 22). Deoarece F = 113,23 > F U= 4,7472 și R-valoare apropiată de 0< 0,05, нулевая гипотеза H 0 deviază, adică Mărimea unui magazin este strâns legată de volumul său anual de vânzări.

Orez. 22. Testarea ipotezei despre panta populației generale la un nivel de semnificație de 0,05, cu unu și 12 grade de libertate

Interval de încredere conţinând panta β 1 ​​. Pentru a testa ipoteza existenței unei relații liniare între variabile, puteți construi un interval de încredere care conține panta β 1 ​​și vă asigurați că valoarea ipotetică β 1 = 0 aparține acestui interval. Centrul intervalului de încredere care conține panta β 1 ​​este panta eșantionului b 1 , iar limitele sale sunt cantitățile b 1 ±t n –2 Sb 1

După cum se arată în fig. 18, b 1 = +1,670, n = 14, Sb 1 = 0,157. t 12 \u003d STUDENT.OBR (0,975, 12) \u003d 2,1788. Prin urmare, b 1 ±t n –2 Sb 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342 sau + 1,328 ≤ β 1 ≤ +2,012. Astfel, panta populației cu o probabilitate de 0,95 se află în intervalul de la +1,328 la +2,012 (adică de la 1.328.000 USD la 2.012.000 USD). Deoarece aceste valori sunt mai mari decât zero, există o relație liniară semnificativă statistic între vânzările anuale și suprafața magazinului. Dacă intervalul de încredere ar conține zero, nu ar exista nicio relație între variabile. În plus, intervalul de încredere înseamnă că la fiecare 1.000 mp. picioare are ca rezultat o creștere a vânzărilor medii de la 1.328.000 USD la 2.012.000 USD.

Utilizaret -criterii pentru coeficientul de corelare. a fost introdus coeficientul de corelare r, care este o măsură a relației dintre două variabile numerice. Poate fi folosit pentru a determina dacă există o relație semnificativă statistic între două variabile. Să notăm coeficientul de corelație dintre populațiile ambelor variabile prin simbolul ρ. Ipotezele nule și alternative sunt formulate după cum urmează: H 0: ρ = 0 (fără corelație), H 1: ρ ≠ 0 (există o corelație). Verificarea existenței unei corelații:

Unde r = + , Dacă b 1 > 0, r = – , Dacă b 1 < 0. Тестовая статистика t Are t- distributie cu n - 2 grade de libertate.

În problema lanțului de magazine Floarea soarelui r2= 0,904 și b 1- +1,670 (vezi Fig. 4). Deoarece b 1> 0, coeficientul de corelație dintre vânzările anuale și dimensiunea magazinului este r= +√0,904 = +0,951. Să testăm ipoteza nulă că nu există nicio corelație între aceste variabile folosind t- statistici:

La un nivel de semnificație de α = 0,05, ipoteza nulă ar trebui respinsă deoarece t= 10,64 > 2,1788. Astfel, se poate susține că există o relație semnificativă statistic între vânzările anuale și dimensiunea magazinului.

Când discutăm inferențe despre pantele populației, intervalele de încredere și criteriile pentru testarea ipotezelor sunt instrumente interschimbabile. Totuși, calculul intervalului de încredere care conține coeficientul de corelație se dovedește a fi mai dificil, deoarece forma distribuției de eșantionare a statisticii r depinde de coeficientul de corelație adevărat.

Estimarea așteptărilor matematice și predicția valorilor individuale

Această secțiune discută metode de estimare a răspunsului așteptat Yși predicții ale valorilor individuale Y pentru valorile date ale variabilei X.

Construirea unui interval de încredere.În exemplul 2 (vezi secțiunea de mai sus Metoda celor mai mici pătrate) ecuația de regresie a făcut posibilă prezicerea valorii variabilei Y X. În problema alegerii unei locații pentru un punct de vânzare cu amănuntul, vânzările medii anuale într-un magazin cu o suprafață de 4000 mp. picioare a fost egală cu 7,644 milioane de dolari.Totuși, această estimare a așteptărilor matematice a populației generale este un punct. pentru estimarea așteptării matematice a populației generale s-a propus conceptul de interval de încredere. În mod similar, se poate introduce conceptul interval de încredere pentru așteptarea matematică a răspunsului pentru o valoare dată a unei variabile X:

Unde , = b 0 + b 1 X i– variabilă de valoare prezisă Y la X = X i, S YX este eroarea pătratică medie, n este dimensiunea eșantionului, Xi- valoarea dată a variabilei X, µ Y|X = Xi – valorea estimata variabil Y la X = Х i,SSX=

Analiza formulei (13) arată că lățimea intervalului de încredere depinde de mai mulți factori. La un anumit nivel de semnificație, o creștere a amplitudinii fluctuațiilor în jurul dreptei de regresie, măsurată folosind eroarea pătratică medie, duce la o creștere a lățimii intervalului. Pe de altă parte, așa cum era de așteptat, o creștere a dimensiunii eșantionului este însoțită de o îngustare a intervalului. În plus, lățimea intervalului se modifică în funcție de valori Xi. Dacă valoarea variabilei Y prezis pentru cantităţi X, aproape de valoarea medie , intervalul de încredere se dovedește a fi mai îngust decât atunci când se prezică răspunsul pentru valori departe de medie.

Să presupunem că atunci când alegem o locație pentru un magazin, dorim să construim un interval de încredere de 95% pentru vânzările medii anuale în toate magazinele cu o suprafață de 4000 de metri pătrați. picioare:

Prin urmare, volumul mediu anual de vânzări în toate magazinele cu o suprafață de 4.000 de metri pătrați. picioare, cu o probabilitate de 95% se află în intervalul de la 6,971 la 8,317 milioane de dolari.

Calculați intervalul de încredere pentru valoarea prezisă. Pe lângă intervalul de încredere pentru așteptarea matematică a răspunsului pentru o valoare dată a variabilei X, este adesea necesar să se cunoască intervalul de încredere pentru valoarea prezisă. Deși formula pentru calcularea unui astfel de interval de încredere este foarte asemănătoare cu formula (13), acest interval conține o valoare prezisă și nu o estimare a parametrului. Interval pentru răspunsul prezis YX = Xi pentru o anumită valoare a variabilei Xi este determinată de formula:

Să presupunem că atunci când alegem o locație pentru un punct de vânzare cu amănuntul, dorim să construim un interval de încredere de 95% pentru volumul anual de vânzări estimat într-un magazin cu o suprafață de 4000 de metri pătrați. picioare:

Prin urmare, volumul anual de vânzări estimat pentru o suprafață de 4.000 mp. picioare, cu o probabilitate de 95% se află în intervalul de la 5,433 la 9,854 milioane de dolari. După cum puteți vedea, intervalul de încredere pentru valoarea răspunsului prezis este mult mai larg decât intervalul de încredere pentru așteptarea sa matematică. Acest lucru se datorează faptului că variabilitatea în prezicerea valorilor individuale este mult mai mare decât în ​​estimarea valorii așteptate.

Capcane și probleme etice asociate cu utilizarea regresiei

Dificultăți asociate cu analiza de regresie:

• Ignorarea condițiilor de aplicabilitate a metodei celor mai mici pătrate.
• O estimare eronată a condițiilor de aplicabilitate a metodei celor mai mici pătrate.
• Alegerea greșită a metodelor alternative cu încălcarea condițiilor de aplicabilitate a metodei celor mai mici pătrate.
• Aplicarea analizei de regresie fără cunoaștere aprofundată a subiectului de studiu.
• Extrapolarea regresiei dincolo de intervalul variabilei explicative.
• Confuzia între relațiile statistice și cauzale.

Răspândirea foilor de calcul și software pentru calculele statistice a eliminat problemele de calcul care împiedicau utilizarea analizei de regresie. Cu toate acestea, acest lucru a condus la faptul că analiza de regresie a început să fie utilizată de utilizatori care nu au suficiente calificări și cunoștințe. Cum știu utilizatorii despre metode alternative, dacă mulți dintre ele nu au niciuna cea mai mica idee despre condițiile de aplicabilitate a metodei celor mai mici pătrate și nu știți cum să verificați implementarea lor?

Cercetătorul nu trebuie să se lase dus de măcinarea numerelor - calculând deplasarea, panta și coeficientul de corelație mixt. Are nevoie de cunoștințe mai profunde. Să ilustrăm acest lucru cu un exemplu clasic luat din manuale. Anscombe a arătat că toate cele patru seturi de date prezentate în Fig. 23 au aceiași parametri de regresie (Fig. 24).

Orez. 23. Patru seturi de date artificiale

Orez. 24. Analiza de regresie a patru seturi de date artificiale; am terminat-o cu Pachet de analize(click pe imagine pentru a mari imaginea)

Deci, din punctul de vedere al analizei de regresie, toate aceste seturi de date sunt complet identice. Dacă analiza s-ar fi terminat pe asta, am fi pierdut foarte mult Informatii utile. Acest lucru este evidențiat de diagramele de dispersie (Fig. 25) și diagramele reziduale (Fig. 26) construite pentru aceste seturi de date.

Orez. 25. Diagrame de dispersie pentru patru seturi de date

Diagramele de dispersie și diagramele reziduale arată că aceste date sunt diferite unele de altele. Singura multime distribuita de-a lungul unei linii drepte este multimea A. Graficul reziduurilor calculate din multimea A nu are un model. Nu același lucru se poate spune și pentru mulțimile B, C și D. Graficul de dispersie reprezentat pentru setul B arată un model pătratic pronunțat. Această concluzie este confirmată de graficul reziduurilor, care are o formă parabolică. Graficul de dispersie și graficul rezidual arată că setul de date B conține un valori abere. În această situație, este necesar să excludeți valorile aberante din setul de date și să repetați analiza. Tehnica de detectare și eliminare a valorii aberante din observații se numește analiză de influență. După eliminarea valorii aberante, rezultatul reevaluării modelului poate fi complet diferit. Un grafic de dispersie reprezentat din setul de date D ilustrează o situație neobișnuită în care modelul empiric este foarte dependent de un singur răspuns ( X 8 = 19, Y 8 = 12,5). Astfel de modele de regresie trebuie calculate cu deosebită atenție. Deci, diagramele de dispersie și graficele reziduale sunt un instrument esențial pentru analiza regresiei și ar trebui să fie o parte integrantă a acesteia. Fără ele, analiza de regresie nu este credibilă.

Orez. 26. Grafice de reziduuri pentru patru seturi de date

Cum să evitați capcanele în analiza de regresie:

• Analiza relației posibile dintre variabile XȘi Yîncepe întotdeauna cu un grafic de dispersie.
• Înainte de a interpreta rezultatele unei analize de regresie, verificați condițiile de aplicabilitate a acesteia.
• Reprezentați grafic reziduurile față de variabila independentă. Acest lucru va permite să se determine modul în care modelul empiric corespunde rezultatelor observației și să se detecteze încălcarea constanței varianței.
• Pentru a testa ipoteza despre distributie normala erori, utilizați histograme, diagrame cu tulpini și frunze, diagrame cu case și diagrame de distribuție normală.
• Dacă nu sunt îndeplinite condițiile de aplicabilitate ale metodei celor mai mici pătrate, utilizați metode alternative(de exemplu, modele de regresie pătratică sau multiplă).
• Dacă sunt îndeplinite condițiile de aplicabilitate ale metodei celor mai mici pătrate, este necesar să se testeze ipoteza despre semnificația statistică a coeficienților de regresie și să se construiască intervale de încredere care să conțină așteptarea matematică și valoarea răspunsului prezis.
• Evitați prezicerea valorilor variabilei dependente în afara intervalului variabilei independente.
• Rețineți că dependențele statistice nu sunt întotdeauna cauzale. Amintiți-vă că corelația dintre variabile nu înseamnă că există o relație cauzală între ele.

Rezumat. După cum se arată în diagrama bloc (Fig. 27), nota descrie un model simplu de regresie liniară, condițiile de aplicabilitate a acestuia și modalitățile de testare a acestor condiții. Considerat t-criteriul de testare a semnificaţiei statistice a pantei regresiei. A fost utilizat un model de regresie pentru a prezice valorile variabilei dependente. Se consideră un exemplu legat de alegerea unui loc pentru un punct de vânzare cu amănuntul, în care se studiază dependența volumului anual de vânzări de suprafața magazinului. Informațiile obținute vă permit să selectați mai precis o locație pentru magazin și să preziceți vânzările anuale ale acestuia. În notele următoare, discuția despre analiza regresiei va continua, precum și modelele de regresie multiple.

Orez. 27. Schema bloc a unei note

Sunt folosite materiale din cartea Levin et al. Statistici pentru manageri. - M.: Williams, 2004. - p. 792–872

Dacă variabila dependentă este categorică, trebuie aplicată regresia logistică.