Regresie liniară pe perechi în Excel. Regresia î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.

Conectarea pachetului de analiză

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

1. Treceți la fila „Fișier”.
2. Accesați secțiunea „Setări”.
3. Se deschide fereastra Opțiuni Excel. Accesați subsecțiunea „Suplimente”.
4. În partea de jos a ferestrei care se deschide, rearanjam comutatorul din blocul „Management” în poziția „Excel Add-ins”, dacă se află într-o poziție diferită. Faceți clic pe butonul „Go”.
5. Se deschide fereastra de suplimente Excel. Bifați caseta de lângă „Pachet de analiză”. Faceți clic pe butonul „OK”.

Acum, când mergem la fila „Date”, pe panglica din blocul instrument „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, ca exemplu, este un tabel care arată temperatura medie zilnică a aerului 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 asupra căreia încercăm să studiem influența factorilor. În cazul nostru, acesta este numărul de cumpărători. Valoarea x este diverși factori care afectează variabila. Parametrii a sunt coeficienții de regresie. Adică, ele determină semnificația unui anumit factor. Indicele k denotă numărul total al acestor aceiași factori.

Analiza rezultatelor analizei

Rezultatele analizei de regresie sunt afișate sub formă de tabel în locul specificat în setări.

Unul dintre principalii indicatori este pătratul R. Indică calitatea modelului. În cazul nostru 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 se află în celula de la intersecția liniei „Y-intersection” și a coloanei „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 valoare dată este egal cu 58,04.

Valoarea de la intersecția coloanei „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 ai magazinului 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.

Ne bucurăm că am putut să vă ajutăm să rezolvați problema.

Pune-ți întrebarea în comentarii, descriind în detaliu esența problemei. Experții noștri vor încerca să răspundă cât mai repede posibil.

Te-a ajutat acest articol?

Metoda regresiei liniare ne permite să descriem o linie dreaptă care se potrivește cel mai bine 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 o valoare dată a lui x,

x - 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 pe axa y este punctul de intersecție al dreptei cu 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 și valoarea ŷ este valoarea y așteptată folosind ecuație liniară pentru o valoare dată a lui x.

Următorul pas este de a determina o ecuație liniară care să corespundă maxim unui set de perechi ordonate, despre asta am vorbit într-un articol anterior, unde am determinat forma ecuației folosind metoda cele mai mici pătrate.

Utilizarea Excel pentru a defini regresia liniară

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

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

Accesați fila Date,într-un 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 articolul anterior.

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

Tipuri de regresie

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

• liniar;
• parabolic;
• putere;
• exponențial;
• hiperbolic;
• demonstrativ;
• logaritmică.

Exemplul 1

Luați în considerare problema determinării dependenței numărului de membri ai echipei pensionari de salariul mediu la 6 întreprinderi industriale.

O sarcină. La șase întreprinderi, am analizat salariul mediu lunar și numărul de angajați plecați din cauza propria voinţă. În formă tabelară avem:

Pentru problema determinării dependenței numărului de lucrători disponibilizați de salariul mediu la 6 întreprinderi, modelul de regresie are forma ecuației Y = a0 + a1 × 1 + ... + akxk, unde хi sunt variabilele care influențează, ai sunt coeficienții de regresie și k este numărul de factori.

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

Utilizarea capabilităților foii de calcul „Excel”

Analiza de regresie în Excel trebuie să fie precedată de aplicarea funcțiilor încorporate la datele tabelare disponibile. Cu toate acestea, în aceste scopuri, este mai bine să utilizați programul de completare foarte util „Setul de instrumente de analiză”. Pentru a-l activa aveți nevoie de:

• din fila „Fișier”, accesați secțiunea „Opțiuni”;
• în fereastra care se deschide, selectați linia „Suplimente”;
• faceți clic pe butonul „Go” situat în jos, în dreapta liniei „Management”;
• bifați caseta de lângă numele „Pachet de analiză” și confirmați acțiunile făcând clic pe „OK”.

Dacă totul este făcut corect, butonul dorit va apărea în partea dreaptă a filei Date, situată deasupra foii de lucru Excel.

Regresia liniară în Excel

Acum că avem la îndemână toate instrumentele virtuale necesare pentru efectuarea calculelor econometrice, putem începe să ne rezolvăm problema. Pentru aceasta:

• faceți clic pe butonul „Analiza datelor”;
• în fereastra care se deschide, faceți clic pe butonul „Regresie”;
• în fila care apare, introduceți intervalul de valori pentru Y (numărul de angajați care au demisionat) și pentru X (salariile lor);
• Confirmăm acțiunile noastre apăsând butonul „Ok”.

Ca rezultat, programul va completa automat o nouă foaie a foii de calcul cu date de analiză de regresie. Notă! Excel are capacitatea de a seta manual locația pe care o preferați în acest scop. De exemplu, ar putea fi aceeași foaie în care sunt valorile Y și X sau chiar O carte noua, special conceput pentru stocarea unor astfel de date.

Analiza rezultatelor regresiei pentru R-pătrat

În Excel, datele obținute în timpul procesării datelor din exemplul considerat arată astfel:

În primul rând, ar trebui să acordați atenție valorii pătratului R. Este coeficientul de determinare. În acest exemplu, R-pătrat = 0,755 (75,5%), adică parametrii calculați ai modelului explică relația dintre parametrii considerați cu 75,5%. Cu cât valoarea coeficientului de determinare este mai mare, cu atât modelul ales este mai aplicabil pentru o anumită sarcină. Se crede că descrie corect situația reală cu o valoare R-pătrat peste 0,8. Dacă R-pătratul este tcr, atunci ipoteza nesemnificației termenului liber al ecuației liniare este respinsă.

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

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

Problema oportunității cumpărării unui bloc de acțiuni

Regresia multiplă în Excel este efectuată folosind același instrument de analiză a datelor. Luați în considerare o problemă aplicată specifică.

Conducerea NNN trebuie să ia o decizie cu privire la oportunitatea achiziționării unui pachet de 20% din MMM SA. Costul pachetului (JV) este de 70 de milioane de dolari SUA. Specialiștii NNN au colectat date despre tranzacții similare. S-a decis evaluarea valorii blocului de acțiuni în funcție de astfel de parametri, exprimați în milioane de dolari SUA, astfel:

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

În plus, se utilizează parametrul restanțe de salarii ale întreprinderii (V3 P) în mii de dolari SUA.

Soluție folosind foaia de calcul Excel

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

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

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

Obțineți analiza de regresie pentru problema dată.

Examinarea rezultatelor și concluziilor

„Colectăm” din datele rotunjite prezentate mai sus pe foaia de calcul Excel, ecuația de regresie:

SP \u003d 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

Într-o formă matematică mai familiară, poate fi scrisă astfel:

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

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

Înlocuindu-le în ecuația de regresie, ei obțin o cifră de 64,72 milioane de dolari SUA. Aceasta înseamnă că acțiunile JSC MMM nu ar trebui cumpărate, deoarece valoarea lor de 70 de milioane de dolari SUA este mai degrabă supraevaluată.

După cum puteți vedea, utilizarea foii de calcul Excel și a ecuației de regresie au făcut posibilă luarea unei decizii informate cu privire la fezabilitatea unei tranzacții foarte specifice.

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

Este cunoscut pentru că este util în diverse domenii de activitate, inclusiv într-o disciplină precum econometria, unde acest utilitar software este utilizat în lucrare. 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. Deci, 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 previziunea 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. Folosind regresia în Excel, puteți determina gradul de influență al fiecăruia dintre acești factori pe baza rezultatelor vânzărilor existente, apoi aplicați datele obținute pentru a prognoza vânzările 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 dependențele și puterea asocierii 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:

1. Selectarea variabilelor independente semnificative (Х1, Х2, …, Xk).
2. Selectarea tipului de funcție.
3. Construirea estimărilor pentru coeficienți.
4. Clădire intervale de încredereși funcții 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 modelului

Pentru a efectua o anumită construcție în Excel, trebuie să urmați instrucțiunile:

Pentru calcule suplimentare, ar trebui utilizată funcția "Linear()", specificând Valori Y, Valori X, Const și statistici. După aceea, 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.

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

Rezultatul analizei vă permite să stabiliți priorități. Și pe baza factorilor principali, să prezice, să planifice dezvoltarea zonelor prioritare, să ia decizii de management.

Are loc regresia:

liniară (y = a + bx);

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

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

Puterea (y = a*x^b);

hiperbolic (y = b/x + a);

logaritmică (y = b * 1n(x) + a);

exponențial (y = a * b^x).

Luați în considerare exemplul construirii unui model de regresie în Excel și interpretarea rezultatelor. Să luăm un tip liniar de regresie.

O sarcină. La 6 întreprinderi, media lunară salariuși numărul de angajați pensionari. Este necesar să se determine dependența numărului de salariați pensionari de salariul mediu.

Modelul de regresie liniară are următoarea formă:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Unde a sunt coeficienții de regresie, x sunt variabilele de influență și k este numărul de factori.

În exemplul nostru, Y este indicatorul lucrătorilor renunțați. Factorul de influență este salariul (x).

Excel are funcții încorporate care pot fi utilizate pentru a calcula parametrii unui model de regresie liniară. Dar programul de completare Analysis ToolPak o va face mai rapid.

Activați un instrument analitic puternic:

1. Faceți clic pe butonul „Office” și accesați fila „Opțiuni Excel”. „Suplimente”.

2. Mai jos, sub lista derulantă, în câmpul „Management” va apărea o inscripție „Excel Add-ins” (dacă nu este acolo, dați clic pe caseta de selectare din dreapta și selectați). Și un buton Go. Clic.

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

Odată activat, suplimentul va fi disponibil în fila Date.

Acum ne vom ocupa direct de analiza de regresie.

1. Deschideți meniul instrumentului de analiză a datelor. Selectați „Regresie”.

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

3. După ce faceți clic pe OK, programul va afișa calculele pe o nouă foaie (puteți selecta intervalul de afișat pe foaia curentă sau puteți atribui rezultatul unui nou registru de lucru).

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

R-pătratul este coeficientul de determinare. În exemplul nostru, este 0,755 sau 75,5%. Aceasta înseamnă că parametrii calculați ai modelului explică relația dintre parametrii studiați cu 75,5%. Cu cât coeficientul de determinare este mai mare, cu atât model mai bun. Bun - peste 0,8. Slab - mai puțin de 0,5 (o astfel de analiză nu poate fi considerată rezonabilă). În exemplul nostru - „nu e rău”.

Coeficientul 64,1428 arată ce va fi Y dacă toate variabilele din modelul luat în considerare sunt egale cu 0. Adică alți factori care nu sunt descriși în model afectează și valoarea parametrului analizat.

Coeficientul -0,16285 arată ponderea variabilei X pe Y. Adică salariul mediu lunar în cadrul acestui model afectează numărul de renunțați cu o pondere de -0,16285 (acesta este un grad mic de influență). Semnul „-” indică un impact negativ: cu cât salariul este mai mare, cu atât mai puține renunțe. Ceea ce este corect.

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

Din articolele propuse, selectați 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 la un set de observații folosind metoda celor mai mici pătrate. Regresia este utilizată pentru a analiza efectul asupra unei singure variabile dependente al valorilor uneia sau mai multor variabile independente. De exemplu, performanța atletică a unui atlet este influențată de mai mulți factori, inclusiv vârsta, înălțimea și greutatea. Este posibil să se calculeze gradul de influență a fiecăruia dintre acești trei factori asupra performanței unui sportiv și apoi să se utilizeze datele obținute pentru a prezice performanța altui sportiv.

Instrumentul de regresie folosește funcția LINEST.

Caseta de dialog REGRES

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

Nivel de fiabilitate 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 încredere pe care doriți să îl aplicați, în plus față de nivelul de încredere implicit de 95%.

Constant - zero Bifați caseta pentru ca linia de regresie să treacă prin origine.

Interval de 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 de rezultate, care va include: rezultatele analizei varianței, coeficienții, eroarea standard de calcul Y, abaterile standard, numărul de observații, erorile standard pentru coeficienți.

Foaie de lucru nouă Bifați această casetă pentru a deschide o nouă foaie de lucru în registrul de lucru și introduceți rezultatele analizei pornind de la celula A1. Dacă este necesar, introduceți un nume pentru noua foaie în câmpul opus poziției corespunzătoare a butonului radio.

Nou registru de lucru Bifați această casetă 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 validare pentru a include reziduurile standardizate în tabelul de ieșire.

Graficul rezidual Bifați caseta pentru a reprezenta grafic reziduurile pentru fiecare variabilă independentă.

Fit Plot Selectați caseta de validare pentru a reprezenta un grafic valorile prezise în raport cu valorile observate.

Graficul de probabilitate normală Bifați caseta pentru a reprezenta probabilitatea normală.

Funcţie LINEST

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

Funcţie LINEST calculează statistici pentru o serie folosind metoda celor mai mici pătrate pentru a calcula o linie dreaptă care aproximează cel mai bine datele disponibile și apoi returnează o matrice care descrie linia dreaptă rezultată. De asemenea, puteți combina funcția LINEST 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 pentru o dreaptă este:

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 independente x, iar b este o constantă. Rețineți că y, x și m pot fi vectori. Funcţie LINEST returnează o matrice (mn;mn-1;…;m 1 ;b). LINEST poate returna, de asemenea, statistici de regresie suplimentare.

LINEST(valori-y-cunoscute; valori-x-cunoscute; const; statistici)

Known_y values ​​​​- setul de valori y care sunt deja cunoscute pentru relația y=mx+b.

Dacă matricea cunoscută_y are o coloană, atunci fiecare coloană a matricei cunoscute_x este interpretată ca o variabilă separată.

Dacă tabloul cunoscut_y are un rând, atunci fiecare rând al matricei cunoscut_x este interpretat ca o variabilă separată.

Valori Known_x ​​- un set opțional de valori x care sunt deja cunoscute pentru relația y=mx+b.

Matricea cunoscută_x poate conține unul sau mai multe seturi de variabile. Dacă este utilizată o singură variabilă, atunci arrays_known_y_values ​​și cunoscute_x_values ​​pot fi de orice formă - atâta timp cât au aceeași dimensiune. Dacă este folosită mai mult de o variabilă, atunci cunoscutele_y trebuie să fie un vector (adică un rând înalt sau o lățime de coloană).

Dacă array_known_x este omis, atunci această matrice (1;2;3;...) se presupune că are aceeași dimensiune cu array_known_y.

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

Dacă argumentul „const” este TRUE sau omis, atunci constanta b este evaluată în mod normal.

Dacă argumentul „const” este FALS, atunci valoarea lui b se presupune a fi 0, iar 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ă trebuie returnate statistici suplimentare de 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).

Valoare	Descriere
se1,se2,...,sen	Valori de eroare standard pentru coeficienții m1,m2,...,mn.
seb	Valoare standard erori pentru constanta b (seb = #N/A dacă argumentul „const” este FALS).
r2	Coeficient de determinare. Valorile reale ale lui y sunt comparate cu valorile obținute din ecuația în linie dreaptă; 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 valorile reale și cele estimate ​de y. În caz contrar, dacă coeficientul de determinism este 0, nu are rost să folosim ecuația de regresie pentru a prezice valorile y. A primi informatii suplimentare pentru modalități de a calcula r2, consultați „Observații” la sfârșitul acestei secțiuni.
sey	Eroarea standard pentru estimarea y.
F	F-statistică sau F-valoare observată. Statistica F este utilizată pentru a determina dacă o relație 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 încredere al modelului, trebuie să comparați valorile din tabel cu statistica F returnată de LINEST. Consultați „Observații” la sfârșitul acestei secțiuni pentru mai multe informații despre calcularea df. Exemplul 4 de mai jos arată utilizarea lui F și df.
ssreg	Suma de regresie a pătratelor.
ssresid	Suma reziduală a pătratelor. Pentru mai multe informații despre calcularea ssreg și ssresid, consultați „Observații” la sfârșitul acestei secțiuni.

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

Note:

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

Panta (m): pentru a determina panta unei drepte, de obicei notată cu m, trebuie să luați două puncte pe linie (x 1 ,y 1) și (x 2 ,y 2); panta va fi egală cu (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 pentru punctul în care linia intersectează axa y.

Ecuația dreptei are forma y=mx+b. Dacă valorile lui m și b sunt cunoscute, atunci orice punct de pe linie poate fi calculat prin înlocuirea valorilor lui y sau x în 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(LINEST(y_cunoscute, x_cunoscute), 1)

Intersecție cu Y: INDEX(LINEST(y_cunoscute, x_cunoscute), 2)

Precizia aproximării folosind linia dreaptă calculată de funcția LINEST depinde de gradul de împrăștiere a datelor. Cu cât datele sunt mai aproape de o linie dreaptă, cu atât modelul utilizat de LINEST este mai precis. Funcția 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 mediile eșantionului, de exemplu x = MEDIE (x-uri cunoscute) și y = MEDIE (y_cunoscute).

Funcțiile de potrivire LINEST și LGRFPRIBL pot calcula o curbă dreaptă sau exponențială care se potrivește cel mai bine datelor. Cu toate acestea, ele nu răspund la întrebarea care dintre cele două rezultate este mai potrivită pentru rezolvarea problemei. De asemenea, puteți calcula funcția TREND(cunoscute_y-valori; cunoscute_x-valori) pentru o linie dreaptă sau funcția GROWTH(cunoscute_y-valori; cunoscute_x-valori) pentru o curbă exponențială. Aceste funcții, dacă sunt omise din argumentul new_x_values, returnează o matrice de valori y calculate pentru valorile efective x conform unei linii drepte sau curbe. Apoi puteți compara valorile calculate cu valorile reale. De asemenea, puteți crea diagrame pentru comparație vizuală.

La efectuarea unei analize 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 dacă acest argument nu este specificat, suma totală a pătratelor va fi egală cu suma diferențelor pătrate ale valorilor reale y și ale valorilor medii y. Dacă const = FALS, suma pătratelor va fi egală cu suma pătratelor valorilor reale ale y (fără a scădea media y din câtul y). După aceea, 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 indică cât de bine explică ecuația obținută prin analiza de regresie relațiile dintre variabile. Coeficientul r2 este egal cu ssreg/sstotal.

În unele cazuri, una sau mai multe coloane X (presupunând că valorile Y și X sunt în coloane) nu au o valoare predictivă suplimentară în alte coloane X. Cu alte cuvinte, ștergerea uneia sau mai multor coloane X poate duce la valori Y calculate cu aceeași precizie. În acest caz, coloanele X redundante vor fi excluse din modelul de regresie. Acest fenomen se numește „colinearitate” deoarece coloanele redundante ale lui X pot fi reprezentate ca suma mai multor coloane neredundante. LINEST verifică coliniaritatea și elimină orice coloane X redundante din modelul de regresie dacă găsește vreuna. Coloanele X eliminate pot fi identificate în ieșirea LINEST printr-un factor de 0 și o valoare a 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. Consultați exemplul 4 de mai jos pentru mai multe detalii despre calcularea df. Când df se modifică din cauza eliminării coloanelor redundante, se modifică și valorile lui sey și F. Adesea nu este recomandată utilizarea coliniarității. Cu toate acestea, ar trebui utilizat dacă unele coloane X conțin 0 sau 1 ca indicator care indică dacă subiectul experimentului se află într-un grup separat. Dacă const = TRUE sau dacă acest argument nu este specificat, LINEST inserează o coloană X suplimentară pentru a simula punctul de intersecție. Dacă există o coloană cu valorile 1 pentru bărbați și 0 pentru femei și există o coloană cu valorile 1 pentru femei și 0 pentru bărbați, atunci ultima coloană este eliminată deoarece valorile sale pot fi obținute din coloana „indicator masculin”.

Calculul df pentru cazurile în care X coloane nu sunt eliminate din model din cauza coliniarității este următorul: dacă există k cunoscute_x coloane și const = TRUE sau nespecificat, atunci df = n - k - 1. Dacă const = FALSE, 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 ca argument know_x_values, de exemplu, utilizați un punct și virgulă pentru a separa valorile pe aceeași linie și două puncte pentru a separa liniile. Caracterele de separare pot varia în funcție de setările din fereastra „Limbă și standarde” din panoul de control.

Rețineți că valorile y prezise de ecuația de regresie pot să nu fie corecte dacă sunt în afara intervalului de valori y care au fost utilizate pentru a defini ecuația.

Algoritmul principal utilizat în funcție LINEST, diferă de algoritmul principal al funcțiilor ÎNCLINAŢIEși SEGMENT DE LINIE. Diferențele dintre algoritmi pot duce la rezultate diferite pentru date incerte și coliniare. De exemplu, dacă punctele de date ale argumentului cunoscut_y sunt 0 și punctele de date ale argumentului cunoscut_x sunt 1, atunci:

Funcţie LINEST returnează o valoare egală cu 0. Algoritmul funcției LINEST este utilizat pentru a returna valori adecvate 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țiilor SLOPE și INTERCEPT este folosit pentru a găsi un singur răspuns, iar în acest caz pot fi 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 variabilelor x și y ca o serie de variabile x și y pentru LINEST. De exemplu, următoarea formulă:

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

funcționează cu o coloană de valori Y și o coloană de valori X pentru a calcula o aproximare a unui 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 calculele altor tipuri de regresie, dar în unele cazuri sunt necesare ajustări ale valorilor de ieșire și alte statistici.

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.

Necesită supliment pentru a funcționa Pachet de analize, care trebuie activat în elementul de meniu Service\Suplimente

În Excel 2007, pentru a activa pachetul de analiză, faceți clic pe Go to Block Opțiuni Excel, făcând clic pe butonul din colțul din stânga sus, 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 Date/Analiza datelor/Regresia). Va apărea o casetă de dialog care trebuie completată:

1) Intervalul de intrare Y¾ conține un link către celulele care conțin valorile atributului rezultat y. Valorile trebuie să fie într-o coloană;

2) Intervalul de intrare X¾ conține un link către celulele care conțin valorile factorilor. Valorile trebuie să fie în coloane;

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

4) Nivel de fiabilitate¾ 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ă Zero constantă este inclus dacă este necesar să se construiască o ecuație în care variabila liberă ;

6) Opțiuni de ieșire determina unde trebuie plasate rezultatele. Modul de construcție implicit Foaie de lucru nouă;

7) Blocare Rămășițe vă permite să includeți rezultatul reziduurilor și construcția graficelor acestora.

Rezultatul este o informație care conține toate informatie necesarași grupate în trei blocuri: Statistici de regresie, Analiza variatiei , Retragerea soldului. Să le luăm în considerare mai detaliat.

1. Statistici de regresie:

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

R (coeficient de determinare);

Normalizat R-patratul se calculeaza prin formula (este folosit pentru regresie multiplă);

eroare standard S calculate prin formula ;

Observații ¾ este cantitatea de date n.

2. Analiza variatiei, linie Regresia:

Parametru df egală 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 rezultat depășește , atunci ipoteza este acceptată (fără relație liniară), în caz contrar ipoteza este acceptată (există o relație liniară).

3. Analiza variatiei, linie Rest:

Parametru df este egal;

Parametru SS este determinat de formula ;

Parametru DOMNIȘOARĂ este determinată de formula .

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

5. Analiza variatiei, 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 STUDENT( t-statistici; ). Dacă P-valoarea depășește , atunci variabila corespunzătoare este nesemnificativă statistic și poate fi exclusă din model.

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

6. Analiza variatiei, rândurile conțin valorile coeficienților, erori standard, t-statistician, P-valori și intervale de încredere pentru corespunzătoare.

7. Blocare Retragerea soldului conţine valorile prognozate y(în notația noastră este ) și resturile .