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 și 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 cu 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 utilizat 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.

În postările anterioare, concentrarea analizei s-a concentrat adesea pe o singură variabilă numerică, cum ar fi randamentele fondurilor mutuale, timpii de încărcare a paginilor 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 cu un exemplu transversal. Prognoza 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ă a selectării 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 condițiile de închiriere favorabile sau ideea managerului despre locația ideală a magazinului. Imaginează-ți că ești șeful departamentului de proiecte speciale și planificare. Ai fost însărcinat să elaborezi un plan strategic pentru noi deschideri de magazine. Acest plan ar trebui să includă o prognoză pentru vânzările anuale ale magazinelor nou deschise. Credeți că vânzarea spațiului este direct legată de valoarea veniturilor și doriți să țineți cont de 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ă postare, ne vom uita la regresia liniară simplă, o tehnică statistică care prezice valorile variabilei dependente. Y prin valorile variabilei independente X... Următoarele note vor descrie un model de 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 format sau, exemple în format

Tipuri de modele de regresie

Unde ρ 1 - 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 testului Durbin-Watson se bazează pe o comparație a valorii D cu valori teoretice critice d Lș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ă d L< D < d U , nu există o bază suficientă pentru a lua o decizie. Când valoarea calculată D depaseste 2, apoi cu d Lși d U nu se compară coeficientul în sine D, și expresia (4 - D).

Pentru a calcula statisticile Durbin-Watson în Excel, să ne referim la tabelul de jos din Fig. paisprezece Retragerea restului... Numătorul din expresia (10) se calculează folosind funcția = SUMKVRAZN (matrice1; matrice 2), iar numitorul = SUMKV (matrice) (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 D cu valorile critice ( d Lși d U) în funcţie de numărul de observaţii n iar 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 cu livrare la domiciliu, există o variabilă independentă ( k= 1), 15 observații ( n= 15) și nivelul de semnificație α = 0,05. Prin urmare, d L= 1,08 și dU= 1,36. În măsura în care D = 0,883 < d L= 1,08, există o autocorelație pozitivă între reziduuri, metoda celor mai mici pătrate nu poate fi utilizată.

Testarea pantei și a ipotezei de corelație

Regresia de mai sus a fost folosită numai pentru prognoză. Pentru a determina coeficienții de regresie și pentru a prezice valoarea unei variabile Y pentru o valoare dată a variabilei X s-a folosit metoda celor mai mici pătrate. În plus, am analizat eroarea pătratică medie a estimării și coeficientul de corelație mixt. Dacă analiza reziduurilor confirmă că nu sunt încălcate condițiile de aplicabilitate ale metodei celor mai mici pătrate, iar modelul de regresie liniară simplă este adecvat, pe baza datelor eșantionate, se poate susține că există o relație liniară între variabilele din populația generală.

Aplicațiet - criteriul pantei. Prin verificarea 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 se formulează astfel: H 0: β 1 = 0 (nu există dependență liniară), H1: β 1 ≠ 0 (există o dependență 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 pătratică medie a estimării pantei:

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

Unde b 1 Este panta dreptei de regresie bazată pe datele eșantionului, β1 este panta ipotetică a dreptei a populației generale, , și statisticile testului 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 cu α = 0,05. t-criteriul este afișat împreună cu alți parametri la utilizare Pachet de analize(opțiune Regresia). Rezultatele complete ale pachetului de analize sunt prezentate în Fig. 4, un fragment legat de t-statistica este prezentat în Fig. optsprezece.

Orez. 18. Rezultatele aplicării t

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

În măsura în care t-statistica = 10,64> t U= 2,1788 (Fig. 19), ipoteză nulă H 0 deviază. Pe de alta parte, R-valoare pentru NS= 10,6411, calculat prin formula = 1-STUDENT.DIST (D3; 12; TRUE), este aproximativ egal cu zero, prin urmare ipoteza H 0 deviază din nou. Faptul că R-valoare aproape egală cu zero înseamnă că dacă nu ar exista o relație liniară reală între dimensiunea magazinului și vânzările anuale, ar fi aproape imposibil de detectat folosind regresia liniară. Prin urmare, există o relație liniară semnificativă statistic între vânzările medii anuale în magazine și dimensiunea acestora.

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 - criteriul pantei. O abordare alternativă pentru testarea ipotezelor de pante de regresie liniară simplă este utilizarea F-criteriu. 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), prin urmare F-criteriul folosește raportul de varianță explicat prin regresie (adică, valorile SSRîmpărțit la numărul de variabile independente k), la variația erorilor ( MSE = S YX 2 ).

A-prioriu F- Statistica este egală cu pătratul mediu al abaterii datorate regresiei (MSR) împărțit la varianța erorii (MSE): F = MSR/ MSE, Unde MSR =SSR / k, MSE =SSE/(n- k - 1), k- 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 rezumativ de analiză a varianței, sunt prezentate în Fig. douăzeci.

Orez. 20. Un tabel de analiză a varianței pentru a testa ipoteza despre semnificația statistică a coeficientului de regresie

De asemenea t-criteriu F-criteriul este afișat în tabel atunci când este utilizat Pachet de analize(opțiune Regresia). În totalitate rezultate ale muncii Pachet de analize sunt prezentate în Fig. 4, un fragment legat de F-statistica - in Fig. 21.

Orez. 21. Rezultatele aplicării F-criterii obtinute cu ajutorul pachetului de analiza Excel

Statistica F 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-distributiile cu unu si 12 grade de libertate pot fi date prin formula F U= F. OBR (1-0,05; 1; 12) = 4,7472 (Fig. 22). În măsura în care F = 113,23 > F U= 4,7472 și R-valoare apropiată de 0< 0,05, нулевая гипотеза H 0 deviază, adică dimensiunea magazinului este strâns legată de vânzările sale anuale.

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 care conține panta β 1. Pentru a testa ipoteza despre existența unei relații liniare între variabile, puteți construi un interval de încredere care să conțină panta β 1 ​​și să 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 S b 1

După cum se arată în fig. optsprezece, b 1 = +1,670, n = 14, S b 1 = 0,157. t 12 = STUDENT.OBR (0,975; 12) = 2,1788. Prin urmare, b 1 ±t n –2 S b 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342 sau + 1,328 ≤ β 1 ≤ +2,012. Astfel, panta populației generale 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 dependență între variabile. În plus, intervalul de încredere înseamnă că fiecare creștere a suprafeței magazinului cu 1000 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 -criteriul 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 stabili dacă există o relație semnificativă statistic între două variabile. Să notăm coeficientul de corelație dintre populațiile generale ale 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 despre lanțul de magazine Floarea soarelui r 2= 0,904 și b 1- +1,670 (vezi fig. 4). În măsura în care b 1> 0, coeficientul de corelație dintre vânzările anuale și dimensiunea magazinului este r= + √0,904 = +0,951. Verificați 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.

Intervalele de încredere și criteriile pentru testarea ipotezelor sunt utilizate în mod interschimbabil atunci când se discută concluziile despre panta populației. Totuși, calcularea intervalului de încredere care conține coeficientul de corelație se dovedește a fi mai dificilă, deoarece forma distribuției eșantionului 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 evaluare a răspunsului așteptat Yși predicții ale valorilor individuale Y la 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, dar această estimare a așteptărilor matematice a populației generale este punctuală. pentru a evalua așteptările matematice ale populației generale s-a propus conceptul de interval de încredere. În mod similar, putem introduce conceptul interval de încredere pentru răspunsul așteptat pentru o valoare dată a variabilei X:

Unde , = b 0 + b 1 X i- valoarea prezisă este variabilă Y la X = X i, S YX- eroare pătratică medie, n- marime de mostra, Xi- valoarea setată a variabilei X, µ Y|X = Xi- așteptarea matematică a unei variabile Y la NS = 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 oscilaț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 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ă trasăm un interval de încredere de 95% pentru vânzările medii anuale pentru toate magazinele cu o suprafață de 4000 mp. picioare:

În consecință, vânzările medii anuale î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.

Calcularea intervalului de încredere pentru valoarea prezisă. Pe lângă intervalul de încredere pentru așteptarea matematică a răspunsului la o valoare dată a variabilei X, este adesea necesar să se cunoască intervalul de încredere pentru valoarea prezisă. În ciuda faptului că formula pentru calcularea acestui interval de încredere este foarte asemănătoare cu formula (13), acest interval conține valoarea prezisă, nu estimarea parametrului. Intervalul de răspuns estimat YX = Xi la o anumită valoare a variabilei Xi determinat de formula:

Să presupunem că atunci când alegem o locație pentru un magazin, dorim să trasăm un interval de încredere de 95% pentru vânzările anuale estimate pentru un magazin cu o suprafață de 4000 mp. picioare:

Prin urmare, volumul anual de vânzări estimat pentru un magazin cu o suprafață de 4000 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 prezisă a răspunsului 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 atunci când se evaluează așteptările matematice.

Capcane și probleme etice cu regresia

Dificultăți cu analiza de regresie:

• Ignorarea condițiilor de aplicabilitate a metodei celor mai mici pătrate.
• Evaluarea eronată a condițiilor de aplicabilitate a metodei celor mai mici pătrate.
• Alegerea greșită a metodelor alternative atunci când sunt încălcate condițiile de aplicabilitate ale metodei celor mai mici pătrate.
• Aplicarea analizei de regresie fără cunoaștere profundă a subiectului de cercetare.
• Extrapolarea regresiei dincolo de intervalul variabilei explicative.
• Confuzie între relațiile statistice și cauzale.

Adoptarea pe scară largă a foilor de calcul și a software-ului statistic a eliminat problemele de calcul care împiedicau utilizarea analizei de regresie. Cu toate acestea, acest lucru a condus la faptul că utilizatorii care nu aveau suficiente calificări și cunoștințe au început să utilizeze analiza de regresie. De unde știu utilizatorii despre metodele alternative, dacă mulți dintre ei nu au nicio idee despre condițiile de aplicabilitate a metodei celor mai mici pătrate și nu știu cum să le verifice implementarea?

Cercetătorul nu ar trebui să se lase dus de numere de măcinare - calculând schimbarea, 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 poza pentru a o mari)

Deci, din punct de vedere al analizei de regresie, toate aceste seturi de date sunt complet identice. Dacă analiza s-ar termina, am pierde o mulțime de informații utile. Acest lucru este evidențiat de diagramele de dispersie (Figura 25) și diagramele reziduale (Figura 26) reprezentate 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 diferă unele de altele. Singura multime distribuita de-a lungul unei drepte este multimea A. Graficul reziduurilor calculate din multimea A nu are regularitate. Nu același lucru se poate spune pentru seturile B, C și D. Graficul de dispersie bazat pe setul B demonstrează un model pătratic pronunțat. Această concluzie este confirmată de graficul reziduurilor, care are 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. O tehnică pentru detectarea și eliminarea valorii aberante din observații se numește analiză de impact. După eliminarea valorii aberante, rezultatul reevaluării modelului poate fi complet diferit. Un grafic de dispersie din setul de date D ilustrează situația neobișnuită în care modelul empiric este foarte dependent de un răspuns individual ( X 8 = 19, Y 8 = 12,5). Astfel de modele de regresie trebuie calculate cu o atenție deosebită. 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 de încredere.

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 prin a reprezenta o diagramă de dispersie.
• Verificați condițiile de aplicabilitate înainte de a interpreta rezultatele analizei de regresie.
• Reprezentați grafic reziduurile față de variabila independentă. Acest lucru vă va permite să determinați modul în care modelul empiric corespunde rezultatelor observației și să detectați o încălcare a constantei varianței.
• Utilizați histograme, diagrame cu tulpini și frunze, diagrame cu case și diagrame de distribuție normală pentru a testa ipoteza de eroare normală.
• Dacă nu sunt îndeplinite condițiile pentru metoda 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 a 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ă relațiile 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 modelul de regresie liniară simplă, condițiile de aplicabilitate a acestuia și modul de verificare a acestor condiții. Considerat t-criteriul de verificare a semnificaţiei statistice a pantei regresiei. Un model de regresie a fost utilizat pentru a prezice valorile variabilei dependente. Un exemplu este considerat legat de alegerea unei locații pentru un punct de vânzare cu amănuntul, în care este investigată dependența volumului anual de vânzări de zona 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, vom continua discuția despre analiza regresiei și, de asemenea, vom analiza modelele de regresie multiple.

Orez. 27. Schema bloc a notei

Materiale folosite din cartea Levin și alte statistici pentru manageri. - M .: Williams, 2004 .-- p. 792-872

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

Prelucrarea statistică a datelor poate fi efectuată și folosind programul de completare PACHET DE ANALIZĂ(fig. 62).

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. 63.

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).

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 și 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:

y = m 1 x 1 + m 2 x 2 +… + b (în cazul mai multor intervale de valori x),

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

LINIST(cunoscute_y; cunoscute_x; const; statistici)

Known_y's este mulțimea de valori y care sunt deja cunoscute pentru relația y = mx + b.

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 y = mx + b.

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 cu 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 și valorile lui m sunt selectate în așa fel încât relația y = mx să fie satisfăcută.

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 (Tabelul 17).

Magnitudinea	Descriere
se1, se2, ..., sen	Valori de eroare standard pentru coeficienții m1, m2, ..., mn.
seb	Valoarea erorii standard pentru constanta b (seb = # N / A dacă const este FALS).
r2	Coeficientul de determinism. 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 (Figura 64).

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, de obicei notată cu m, trebuie să luați două puncte ale dreptei (x 1, y 1) și (x 2, y 2); panta va fi (y 2 -y 1) / (x 2 -x 1).

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 y = mx + b. 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 utilizat 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ă:

y = m 1 x + m 2 x 2 + m 3 x 3 + b

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

Este cunoscut pentru că este util în diverse domenii de activitate, inclusiv în disciplină precum econometria, unde acest utilitar software este utilizat în activitatea sa. Practic, toate acțiunile exercițiilor practice și de laborator sunt efectuate în Excel, ceea ce facilitează foarte mult munca, dând 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ă luăm în considerare ce este acest instrument al programului și care este beneficiul acestuia pentru utilizatori. Mai jos este, de asemenea, o instrucțiune scurtă, dar ușor de înțeles pentru construirea unui model de regresie.

Principalele sarcini și tipuri de regresie

Regresia este o relație între variabile date, datorită căreia se poate determina predicția comportamentului viitor al acestor variabile. Variabilele sunt diverse fenomene periodice, inclusiv comportamentul uman. Această 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.

Regresia este de obicei prezentată ca o ecuație simplă care dezvăluie relația și puterea relației dintre două grupuri de variabile, unde un grup este dependent sau endogen, iar celălalt este independent sau exogen. În prezența unui 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:

1. Selectarea variabilelor independente semnificative (X1, X2,…, Xk).
2. Selectarea tipului de funcție.
3. Construirea estimărilor pentru coeficienți.
4. Construirea intervalelor de încredere și a funcțiilor de regresie.
5. 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:

1. Linear, ilustrând o relație liniară strictă între variabile.
2. Neliniar - Ecuații care pot include puteri, fracții și funcții trigonometrice.

Instrucțiuni de construcție a modelelor

Pentru a finaliza construcția specificată în Excel, trebuie să urmați instrucțiunile:

Pentru calcule suplimentare, utilizați funcția "Liniar ()", specificând valorile Y, valorile X, Const și statistici. Apoi definiți setul de puncte pe linia de regresie folosind funcția „Trend” - Valori Y, Valori X, Valori noi, Const. Folosind parametrii specificați, calculați valoarea necunoscută a coeficienților pe baza condițiilor specificate ale problemei.

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.

Este necesar un supliment pentru a funcționa Pachet de analize pentru a fi incluse în elementul de meniu Service \ Suplimente

În Excel 2007, pentru a activa pachetul de analiză, trebuie să faceți clic pe Go to block Opțiuni Excel făcând clic pe butonul din colțul din stânga sus și apoi pe butonul " Opțiuni Excel„În partea de jos a ferestrei:

Pentru a construi un model de regresie, selectați elementul Serviciu \ Analiza datelor \ Regresie... (În Excel 2007, acest mod este în casetă Date / Analiza datelor / Regresie). Va apărea o casetă de dialog care trebuie completată:

1) Interval de intrare Y¾ conține o legătură către celulele care conțin valorile caracteristicii rezultate y... Valorile trebuie să fie situate într-o coloană;

2) Intervalul de intrare X¾ conține o referință la celulele care conțin valorile factorilor. Valorile trebuie să fie în coloane;

3) Semnează Etichete setați dacă primele celule conțin text explicativ (semnături de date);

4) Nivel de fiabilitate¾ Acesta este nivelul de încredere, care se presupune că este de 95% în mod implicit. Dacă această valoare nu vă convine, atunci trebuie să activați această caracteristică și să introduceți valoarea necesară;

5) Semnează Constant-zero se aprinde dacă este necesar să se construiască o ecuație în care există o variabilă liberă;

6) Opțiuni de ieșire determina unde trebuie plasate rezultatele. În mod implicit, construiește modul Foaie de lucru nouă;

7) Blocare Resturi vă permite să activați retragerea reziduurilor și construirea graficelor acestora.

Ca urmare, informațiile sunt afișate conținând toate informațiile necesare și grupate în trei blocuri: Statistici de regresie, ANOVA, Retragerea restului... Să le luăm în considerare mai detaliat.

1. Statistici de regresie:

multiplu R este definit de formula ( Coeficientul de corelație al lui Pearson);

R (coeficient de determinare);

Normalizat R-patratul se calculeaza prin formula (utilizat pentru regresia multiplă);

Eroare standard S calculate prin formula ;

Observațiile sunt cantitatea de date n.

2. ANOVA, linie Regresia:

Parametru df este egal cu m(numărul de seturi de factori X);

Parametru SS este determinat de formula;

Parametru DOMNIȘOARĂ este determinat de formula;

Statistici F este determinat de formula;

Semnificaţie F... Dacă numărul primit depășește, atunci ipoteza este acceptată (nu există relație liniară), în caz contrar ipoteza este acceptată (există o relație liniară).

3. ANOVA, linie Rest:

Parametru df este egal;

Parametru SS este definit de formula ;

Parametru DOMNIȘOARĂ este definit de formula.

4. ANOVA, linie Total conţine suma primelor două coloane.

5. ANOVA, linie Intersecția în Y conţine valoarea coeficientului, eroarea standard şi t-statistici.

P-valoarea ¾ este valoarea nivelurilor de semnificație corespunzătoare celei calculate t-statisticieni. Determinat de funcția TDIST ( t-statistici; ). Dacă P-valoarea depășește, atunci variabila corespunzătoare este nesemnificativă statistic și poate fi exclusă din model.

De jos 95%și Top 95%¾ acestea sunt limitele inferioare și superioare ale intervalelor de încredere de 95 la sută pentru coeficienții ecuației teoretice de regresie liniară. Dacă în blocul de introducere a datelor nivelul de încredere a fost lăsat implicit, atunci ultimele două coloane le vor duplica pe cele anterioare. Dacă utilizatorul a introdus propriul nivel de încredere, atunci ultimele două coloane conțin limitele inferioare și superioare pentru nivelul de încredere specificat.

6. ANOVA, liniile conțin valorile coeficienților, erori standard, t-statistician, P-valori și intervale de încredere pentru cele corespunzătoare.

7. Blocare Retragerea restului conţine valorile prezise y(în notația noastră este) și rămășițele.