Cum se construiește regresia neliniară în Excel. Metode matematice în psihologie
Pachetul MS Excel vă permite să faceți cea mai mare parte a muncii foarte rapid atunci când construiți o ecuație de regresie liniară. Este important să înțelegeți cum să interpretați rezultatele obținute. Pentru a construi un model de regresie, trebuie să selectați Tools\Data Analysis\Regression (în Excel 2007 acest mod se află în blocul Date/Data Analysis/Regression). Apoi copiați rezultatele într-un bloc pentru analiză.
Este cunoscut pentru că este util în diverse domenii de activitate, inclusiv într-o disciplină precum econometria, unde acest utilitar software este folosit în muncă. Practic, toate acțiunile orelor practice și de laborator sunt efectuate în Excel, ceea ce facilitează foarte mult munca, oferind explicații detaliate ale anumitor acțiuni. Astfel, unul dintre instrumentele de analiză „Regresia” este folosit pentru a selecta un grafic pentru un set de observații folosind metoda celor mai mici pătrate. Să ne uităm la ce este acest instrument de program și care sunt beneficiile sale pentru utilizatori. Mai jos veți găsi, de asemenea, instrucțiuni scurte, dar clare pentru construirea unui model de regresie.
Principalele sarcini și tipuri de regresie
Regresia reprezintă relația dintre variabile date, făcând astfel posibilă prezicerea comportamentului viitor al acestor variabile. Variabilele sunt diverse fenomene periodice, inclusiv comportamentul uman. Acest tip de analiză Excel este utilizat pentru a analiza impactul asupra unei anumite variabile dependente al valorilor uneia sau mai multor variabile. De exemplu, vânzările într-un magazin sunt influențate de mai mulți factori, inclusiv sortimentul, prețurile și locația magazinului. Datorită regresiei în Excel, puteți determina gradul de influență al fiecăruia dintre acești factori pe baza rezultatelor vânzărilor existente, iar apoi aplicați datele obținute pentru prognoza vânzărilor pentru încă o lună sau pentru un alt magazin situat în apropiere.
De obicei, regresia este prezentată ca o ecuație simplă care dezvăluie relațiile și punctele forte ale relațiilor dintre două grupuri de variabile, unde un grup este dependent sau endogen, iar celălalt este independent sau exogen. Dacă există un grup de indicatori interrelaționați, variabila dependentă Y este determinată pe baza logicii raționamentului, iar restul acționează ca variabile X independente.
Sarcinile principale ale construirii unui model de regresie sunt următoarele:
- Selectarea variabilelor independente semnificative (X1, X2, ..., Xk).
- Selectarea tipului de funcție.
- Construirea de estimări pentru coeficienți.
- Construirea intervalelor de încredere și a funcțiilor de regresie.
- Verificarea semnificației estimărilor calculate și a ecuației de regresie construită.
Există mai multe tipuri de analiză de regresie:
- pereche (1 variabilă dependentă și 1 variabilă independentă);
- multiple (mai multe variabile independente).
Există două tipuri de ecuații de regresie:
- Linear, ilustrând o relație liniară strictă între variabile.
- Neliniar - Ecuații care pot include puteri, fracții și funcții trigonometrice.
Instrucțiuni pentru construirea unui model
Pentru a efectua o anumită construcție în Excel, trebuie să urmați instrucțiunile:
Pentru calcule suplimentare, utilizați funcția „Linear()”, specificând Valorile Y, Valorile X, Const și Statisticile. După aceasta, determinați setul de puncte pe linia de regresie folosind funcția „Trend” - Valori Y, Valori X, Valori noi, Const. Folosind parametrii dați, calculați valoarea necunoscută a coeficienților, pe baza condițiilor date ale problemei.
În opinia mea, ca student, econometria este una dintre cele mai aplicate științe cu care am putut să mă familiarizez între zidurile universității mele. Cu ajutorul acestuia, este într-adevăr posibil să se rezolve probleme aplicate la scară întreprindere. Cât de eficiente vor fi aceste decizii este a treia întrebare. Concluzia este că majoritatea cunoștințelor vor rămâne teorie, dar econometria și analiza de regresie merită în continuare studiate cu o atenție specială.
Ce explică regresia?
Înainte de a începe să luăm în considerare funcțiile MS Excel care ne permit să rezolvăm aceste probleme, aș dori să vă explic în detaliu ce presupune, în esență, analiza regresiei. Acest lucru vă va face mai ușor să promovați examenul și, cel mai important, va fi mai interesant să studiați materia.
Sper că sunteți familiarizat cu conceptul de funcție din matematică. O funcție este relația dintre două variabile. Când o variabilă se schimbă, ceva i se întâmplă alteia. Schimbăm X și Y se schimbă în consecință. Funcțiile descriu diverse legi. Cunoscând funcția, putem înlocui valorile arbitrare ale lui X și vedem cum se modifică Y.
Acest lucru este de mare importanță deoarece regresia este o încercare de a explica, la prima vedere, procesele nesistematice și haotice folosind o anumită funcție. De exemplu, este posibil să se identifice relația dintre cursul de schimb al dolarului și șomajul în Rusia.
Dacă acest model poate fi descoperit, atunci folosind funcția pe care am obținut-o în timpul calculelor, vom putea face o prognoză a ratei șomajului la cursul de schimb al al-lea dolar față de rublă.
Această relație se va numi corelație. Analiza de regresie presupune calcularea unui coeficient de corelație care să explice relația strânsă dintre variabilele pe care le luăm în considerare (cursul de schimb al dolarului și numărul de locuri de muncă).
Acest coeficient poate fi pozitiv sau negativ. Valorile sale variază de la -1 la 1. În consecință, putem observa o corelație negativă sau pozitivă ridicată. Dacă este pozitivă, atunci creșterea cursului dolarului va fi urmată de crearea de noi locuri de muncă. Dacă este negativă, înseamnă că o creștere a cursului de schimb va fi urmată de o scădere a locurilor de muncă.
Există mai multe tipuri de regresie. Poate fi liniar, parabolic, de putere, exponențial etc. Alegem un model în funcție de care regresie va corespunde în mod specific cazului nostru, care model va fi cât mai aproape de corelația noastră. Să ne uităm la asta folosind un exemplu de problemă și să o rezolvăm în MS Excel.
Regresia liniară în MS Excel
Pentru a rezolva probleme de regresie liniară, veți avea nevoie de funcționalitatea de analiză a datelor. Este posibil să nu fie activat pentru dvs., așa că trebuie să îl activați.
- Faceți clic pe butonul „Fișier”;
- Selectați elementul „Opțiuni”;
- Faceți clic pe penultima filă „Suplimente” din partea stângă;
- Mai jos vom vedea inscripția „Management” și butonul „Go”. Apasa pe el;
- Bifați caseta pentru „Pachet de analiză”;
- Faceți clic pe „ok”.
Exemplu de sarcină
Funcția de analiză a lotului este activată. Să rezolvăm următoarea problemă. Avem un eșantion de date de câțiva ani privind numărul de situații de urgență pe teritoriul întreprinderii și numărul de lucrători angajați. Trebuie să identificăm relația dintre aceste două variabile. Există o variabilă explicativă X - acesta este numărul de lucrători și o variabilă explicativă - Y - acesta este numărul de incidente de urgență. Să distribuim datele sursă în două coloane.
Să mergem la fila „date” și să selectăm „Analiza datelor”
În lista care apare, selectați „Regresie”. În intervalele de intrare Y și X selectăm valorile corespunzătoare.
Faceți clic pe „Ok”. Analiza este finalizată, iar rezultatele vom vedea într-o nouă fișă.
Cele mai semnificative valori pentru noi sunt marcate în figura de mai jos.
Multiplu R este coeficientul de determinare. Are o formulă de calcul complexă și arată cât de mult poți avea încredere în coeficientul nostru de corelație. În consecință, cu cât această valoare este mai mare, cu atât mai multă încredere, cu atât modelul nostru în ansamblu este mai de succes.
Y-Intercept și X1-Intercept sunt coeficienții noștri de regresie. După cum am menționat deja, regresia este o funcție și are anumiți coeficienți. Astfel, funcția noastră va arăta astfel: Y = 0,64*X-2,84.
Ce ne oferă asta? Acest lucru ne oferă posibilitatea de a face o prognoză. Să presupunem că vrem să angajăm 25 de lucrători pentru o întreprindere și trebuie să ne imaginăm aproximativ care va fi numărul de incidente de urgență. Inlocuim aceasta valoare in functia noastra si obtinem rezultatul Y = 0,64 * 25 – 2,84. Vom avea aproximativ 13 urgențe.
Să vedem cum funcționează. Aruncă o privire la poza de mai jos. Funcția pe care am obținut-o conține valorile reale pentru angajații implicați. Vedeți cât de aproape sunt valorile de jucătorii adevărați.
De asemenea, puteți construi un câmp de corelare selectând zona Y și X, făcând clic pe fila „inserați” și selectând graficul de dispersie.
Punctele sunt împrăștiate, dar în general se deplasează în sus, ca și cum ar fi o linie dreaptă în mijloc. De asemenea, puteți adăuga această linie accesând fila „Layout” din MS Excel și selectând „Trend Line”
Faceți dublu clic pe linia care apare și veți vedea ce s-a menționat mai devreme. Puteți schimba tipul de regresie în funcție de cum arată câmpul de corelare.
S-ar putea să simți că punctele desenează mai degrabă o parabolă decât o linie dreaptă și că ar fi mai bine să alegi un alt tip de regresie.
Concluzie
Sperăm că acest articol v-a oferit o înțelegere mai bună a ce este analiza de regresie și de ce este necesară. Toate acestea au o mare importanță practică.
Construcția regresiei liniare, evaluarea parametrilor acesteia și a semnificației acestora pot fi realizate mult mai rapid atunci când se utilizează pachetul de analiză Excel (Regression). Să luăm în considerare interpretarea rezultatelor obținute în cazul general ( k variabile explicative) conform exemplului 3.6.
In masa statistici de regresie se dau urmatoarele valori:
Multiplu R – coeficient de corelație multiplă;
R- pătrat- coeficient de determinare R 2 ;
Normalizat R - pătrat- ajustat R 2 ajustat pentru numărul de grade de libertate;
Eroare standard– eroare standard de regresie S;
Observatii - numărul de observații n.
In masa Analiza variatiei sunt date:
1. Coloana df - număr 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 abaterilor pătrate 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– dispersia factorilor;
pentru sfoară Rest– variația reziduală.
4. Coloana F – valoarea calculată F-criteriul calculat folosind formula
F = DOMNIȘOARĂ(regresie)/ DOMNIȘOARĂ(rest).
5. Coloana Semnificaţie F – valoarea nivelului de semnificație corespunzătoare celei calculate F-statistici .
Semnificaţie F= FDIST( F- statistici, df(regresie), df(rest)).
Dacă semnificație F < стандартного уровня значимости, то R 2 este semnificativ statistic.
| Cote | Eroare standard | t-statistici | 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– erori standard ale coeficienților de regresie S a, Sb.
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 = STUDIDIST(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 A RESTULUI | ||
| Observare | A prezis y | Reziduuri 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 A RESTULUI indicat:
în coloană Observare– numărul de observație;
în coloană Prevestit 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) despre costurile alimentelor yși venitul pe cap de locuitor X pentru nouă grupuri de familii:
| X | |||||||||
| y |
Folosind rezultatele pachetului de analiză Excel (Regresie), vom analiza dependența costurilor alimentare de venitul pe cap de locuitor.
Rezultatele analizei de regresie sunt de obicei scrise 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 convențional. unitati costurile cu alimentele cresc cu 0,107 unităţi convenţionale. unitati
Să evaluă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 un anumit set al 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ă factorul venit pe cap de locuitor poate explica 88,4% din variația (împrăștierea) cheltuielilor cu alimente.
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 pe perechi, coeficientul de corelație poate fi definit ca 
. Valoarea obținută a coeficientului de corelație indică faptul că relația dintre cheltuielile cu alimentele și venitul pe cap de locuitor este foarte strânsă.
ÎN excela Există o modalitate și mai rapidă și mai convenabilă de a reprezenta un grafic regresia liniară (și chiar principalele tipuri de regresii neliniare, așa cum se discută mai jos). Acest lucru se poate face după cum urmează:
1) selectați coloanele cu date XȘi Y(ar trebui să fie în ordinea aceea!);
2) sunați Chart Wizardși selectați din grup Tip – Locși apăsați imediat Gata;
3) fără a deselecta diagrama, selectați elementul din meniul principal care apare Diagramă, în care ar trebui să selectați elementul Adăugați o linie de tendință;
4) în caseta de dialog care apare Linie de tendințeîn filă Tip alege Liniar;
5) în filă Opțiuni poți activa comutatorul Arată ecuația în diagramă, care vă va permite să vedeți ecuația de regresie liniară (4.4), în care se vor calcula coeficienții (4.5).
6) În aceeași filă puteți activa comutatorul Plasați valoarea de fiabilitate a aproximării (R^2) pe diagramă. Această valoare este pătratul coeficientului de corelație (4.3) și arată cât de bine ecuația calculată descrie dependența experimentală. Dacă R 2 este aproape de unitate, atunci ecuația de regresie teoretică descrie bine dependența experimentală (teoria este de acord cu experimentul) și dacă R 2 este aproape de zero, atunci această ecuație nu este potrivită pentru a descrie dependența experimentală (teoria nu este de acord cu experimentul).
Ca urmare a efectuării acțiunilor descrise, veți obține o diagramă cu un grafic de regresie și ecuația acestuia.
§4.3. Principalele tipuri de regresie neliniară
Regresia parabolica si polinomiala.
Parabolic dependenta de valoare Y de la dimensiune X se numește dependență exprimată printr-o funcție pătratică (parabolă de ordinul 2):
Această ecuație se numește ecuația de regresie parabolică Y pe X. Opțiuni A, b, Cu sunt numite coeficienții de regresie parabolic. Calcularea coeficienților de regresie parabolică este întotdeauna greoaie, de aceea se recomandă utilizarea unui computer pentru calcule.
Ecuația (4.8) a regresiei parabolice este un caz special al unei regresii mai generale numită polinom. Polinom dependenta de valoare Y de la dimensiune X se numește dependență exprimată printr-un polinom n-a ordinea:
unde sunt numerele și eu (i=0,1,…, n) sunt numite coeficienții de regresie polinomială.
Regresia puterii.
Putere dependenta de valoare Y de la dimensiune X se numește dependență de forma:
Această ecuație se numește ecuația de regresie a puterii Y pe X. Opțiuni AȘi b sunt numite coeficienții de regresie a puterii.
ln =ln A+b· ln X. (4.11)
Această ecuație descrie o dreaptă pe un plan cu axe de coordonate logaritmice ln Xși ln. Prin urmare, criteriul de aplicabilitate a regresiei puterii este cerința ca punctele de logaritmi ale datelor empirice ln x iși ln y eu erau cel mai aproape de linia dreaptă (4.11).
Regresie exponențială.
Indicativ(sau exponenţială) dependența valorii Y de la dimensiune X se numește dependență de forma:
(sau ). (4,12)
Această ecuație se numește ecuație exponențială(sau exponenţială) regresie Y pe X. Opțiuni A(sau k) Și b sunt numite coeficienți exponențiali(sau exponenţială) regresie.
Dacă luăm logaritmul ambelor părți ale ecuației de regresie a puterii, obținem ecuația
ln = X ln A+ln b(sau ln = k x+ln b). (4.13)
Această ecuație descrie dependența liniară a logaritmului unei mărimi ln față de o altă mărime X. Prin urmare, criteriul de aplicabilitate a regresiei puterii este cerința ca datele empirice să aibă aceeași valoare x işi logaritmii unei alte mărimi ln y eu au fost cele mai apropiate de linia dreaptă (4.13).
Regresie logaritmică.
Logaritmic dependenta de valoare Y de la dimensiune X se numește dependență de forma:
=A+b· ln X. (4.14)
Această ecuație se numește ecuația de regresie logaritmică Y pe X. Opțiuni AȘi b sunt numite coeficienții de regresie logaritmică.
Regresie hiperbolica.
Hiperbolic dependenta de valoare Y de la dimensiune X se numește dependență de forma:
Această ecuație se numește ecuația de regresie hiperbolică Y pe X. Opțiuni AȘi b sunt numite coeficienții de regresie hiperbolicși sunt determinate prin metoda celor mai mici pătrate. Aplicarea acestei metode conduce la formulele:
În formulele (4.16-4.17) însumarea se realizează peste indice i de la unu la numărul de observaţii n.
Din păcate, în excela nu există funcții care să calculeze coeficienții de regresie hiperbolic. În cazurile în care nu se știe că mărimile măsurate sunt legate prin proporționalitate inversă, se recomandă să se caute o ecuație de regresie a puterii în locul ecuației de regresie hiperbolică, deci în excela Există o procedură pentru a-l găsi. Dacă se presupune o dependență hiperbolică între mărimile măsurate, atunci coeficienții săi de regresie vor trebui să fie calculați folosind tabele de calcul auxiliare și operații de însumare folosind formule (4.16-4.17).
