Regressioanalyysi on yksi suosituimmista menetelmistä tilastollinen tutkimus. Sitä voidaan käyttää riippumattomien muuttujien vaikutuksen asteen määrittämiseen riippuvaan muuttujaan. Toiminnallisuudessa Microsoft Excel On olemassa työkaluja, jotka on suunniteltu tämäntyyppisten analyysien suorittamiseen. Katsotaanpa, mitä ne ovat ja miten niitä käytetään.
Analyysipaketin yhdistäminen
Mutta jotta voit käyttää toimintoa, jonka avulla voit suorittaa regressioanalyysin, sinun on ensin aktivoitava analyysipaketti. Vasta sitten tähän toimenpiteeseen tarvittavat työkalut näkyvät Excel-nauhassa.
- Siirry "Tiedosto"-välilehdelle.
- Siirry "Asetukset" -osioon.
- Excelin asetukset -ikkuna avautuu. Siirry "Lisäosat" -alaosioon.
- Siirrä avautuvan ikkunan alareunassa oleva "Ohjaus"-lohkon kytkin asentoon " Excelin lisäosat"jos se on eri asennossa. Napsauta "Go"-painiketta.
- Ikkuna käytettävissä olevista Excel-apuohjelmista avautuu. Valitse "Analysis Package" -kohdan vieressä oleva valintaruutu. Napsauta "OK"-painiketta.
Nyt kun siirrymme "Data" -välilehteen, "Analysis" -työkalulohkon nauhassa näemme uuden painikkeen - "Data Analysis".
Regressioanalyysin tyypit
Regressioita on useita tyyppejä:
- parabolinen;
- rauhallinen;
- logaritminen;
- eksponentiaalinen;
- mielenosoitus;
- hyperbolinen;
- lineaarinen regressio.
Puhumme myöhemmin yksityiskohtaisemmin viimeisen tyyppisen regressioanalyysin suorittamisesta Excelissä.
Lineaarinen regressio Excelissä
Alla on esimerkkinä taulukko, jossa näkyy vuorokauden keskilämpötila ulkona ja myymälän asiakkaiden määrä vastaavana työpäivänä. Selvitetään regressioanalyysin avulla tarkalleen kuinka sääolosuhteet ilman lämpötila voi vaikuttaa vähittäiskaupan käyntiin.
Yleinen lineaarinen regressioyhtälö on seuraava: Y = a0 + a1x1 +…+ akhk. Tässä kaavassa Y tarkoittaa muuttujaa, johon yritämme tutkia tekijöiden vaikutusta. Meidän tapauksessamme tämä on ostajien määrä. X:n arvo on eri tekijät, jotka vaikuttavat muuttujaan. Parametrit a ovat regressiokertoimia. Toisin sanoen he ovat ne, jotka määrittävät tietyn tekijän merkityksen. Indeksi k ilmaisee näiden samojen tekijöiden kokonaismäärää.
Analyysitulosten analyysi
Regressioanalyysin tulokset näytetään taulukon muodossa asetuksissa määritetyssä paikassa.
Yksi tärkeimmistä indikaattoreista on R-neliö. Se kertoo mallin laadun. Meidän tapauksessamme tämä kerroin on 0,705 tai noin 70,5%. Tämä on hyväksyttävä laatutaso. Riippuvuus alle 0,5 on huono.
Toinen tärkeä indikaattori sijaitsee Y-leikkausrivin ja Kertoimet-sarakkeen leikkauskohdassa olevassa solussa. Tämä osoittaa, mikä arvo Y on, ja meidän tapauksessamme tämä on ostajien lukumäärä kaikkien muiden tekijöiden kanssa yhtä suuri kuin nolla. Tässä taulukossa annettu arvo on 58,04.
Sarakkeiden ”Muuttuja X1” ja ”Kertoimet” leikkauskohdassa oleva arvo osoittaa Y:n riippuvuuden tason X:stä. Meidän tapauksessamme tämä on kaupan asiakkaiden määrän riippuvuuden taso lämpötilasta. Kerrointa 1,31 pidetään melkoisena korkea korko vaikutus.
Kuten näemme, käyttämällä Microsoftin ohjelmat Excel on melko helppo luoda regressioanalyysitaulukko. Mutta vain koulutettu henkilö voi työskennellä lähtötietojen kanssa ja ymmärtää sen olemuksen.
Olemme iloisia, että pystyimme auttamaan sinua ratkaisemaan ongelman.
Esitä kysymyksesi kommenteissa ja kuvaile ongelman ydin yksityiskohtaisesti. Asiantuntijamme yrittävät vastata mahdollisimman nopeasti.
Auttoiko tämä artikkeli sinua?
Menetelmä lineaarinen regressio antaa meille mahdollisuuden kuvata suoraa viivaa, joka sopii parhaiten sarjaan järjestettyjä pareja (x, y). Suoran yhtälö, joka tunnetaan nimellä lineaarinen yhtälö, on annettu alla:
ŷ - y:n odotettu arvo at asetettu arvo X,
x - riippumaton muuttuja,
a - segmentti y-akselilla suoraa viivaa varten,
b on suoran kaltevuus.
Alla oleva kuva havainnollistaa tätä käsitettä graafisesti:
Yllä oleva kuva esittää yhtälön ŷ =2+0,5x kuvaamaa suoraa. Y-leikkauspiste on piste, jossa suora leikkaa y-akselin; meidän tapauksessamme a = 2. Viivan kaltevuus b, suoran nousun suhde viivan pituuteen, on arvo 0,5. Positiivinen kaltevuus tarkoittaa, että viiva nousee vasemmalta oikealle. Jos b = 0, viiva on vaakasuora, mikä tarkoittaa, että riippuvien ja riippumattomien muuttujien välillä ei ole suhdetta. Toisin sanoen x:n arvon muuttaminen ei vaikuta y:n arvoon.
ŷ ja y sekoitetaan usein. Kaaviossa on 6 järjestettyä pisteparia ja viiva annetun yhtälön mukaisesti
Tämä kuva näyttää pisteen, joka vastaa järjestettyä paria x = 2 ja y = 4. Huomaa, että y:n odotusarvo rivin mukaan X= 2 on ŷ. Voimme vahvistaa tämän seuraavalla yhtälöllä:
ŷ = 2 + 0,5х = 2 +0,5 (2) =3.
Y-arvo edustaa todellista pistettä ja ŷ-arvo on odotettu y-arvo käyttäen lineaarinen yhtälö tietylle x:n arvolle.
Seuraava askel on määrittää lineaarinen yhtälö, joka parhaiten vastaa järjestettävien parien joukkoa, puhuimme tästä edellisessä artikkelissa, jossa määritimme yhtälön muodon pienimmän neliösumman menetelmällä.
Excelin käyttäminen lineaarisen regression määrittämiseen
Jotta voit käyttää Exceliin sisäänrakennettua regressioanalyysityökalua, sinun on aktivoitava apuohjelma Analyysipaketti. Löydät sen napsauttamalla välilehteä Tiedosto -> Asetukset(2007+) avautuvassa valintaikkunassa VaihtoehdotExcel mene välilehdelle Lisäosat. Kentällä Ohjaus valita LisäosatExcel ja napsauta Mennä. Valitse avautuvassa ikkunassa vieressä oleva valintaruutu Analyysipaketti, napsauta OK.
Välilehdellä Data ryhmässä Analyysi tulee näkyviin uusi painike Tietojen analysointi.
Havainnollistaaksesi, miten apuohjelma toimii, käytetään tietoja edellisestä artikkelista, jossa kaveri ja tyttö jakavat pöydän kylpyhuoneessa. Syötä kylpyammeesimerkin tiedot tyhjän arkin sarakkeisiin A ja B.
Siirry välilehdelle Data, ryhmässä Analyysi napsauta Tietojen analysointi. Näyttöön tulevassa ikkunassa Tietojen analyysi valitse Regressio kuten kuvassa ja napsauta OK.
Aseta tarvittavat regressioparametrit ikkunaan Regressio kuten kuvassa näkyy:
Napsauta OK. Alla oleva kuva näyttää saadut tulokset:
Nämä tulokset ovat yhdenmukaisia niiden kanssa, jotka saimme tekemällä omia laskelmia edellisessä artikkelissa.
Regressioanalyysi on tilastollinen menetelmä tutkimus, jonka avulla voit osoittaa tietyn parametrin riippuvuuden yhdestä tai useammasta riippumattomasta muuttujasta. Ennen tietokonetta sen käyttö oli varsin vaikeaa, varsinkin kun se tuli suuria määriä tiedot. Tänään, kun olet oppinut rakentamaan regression Excelissä, voit ratkaista monimutkaisia tilastollisia ongelmia muutamassa minuutissa. Alla ovat konkreettisia esimerkkejä taloustieteen alalta.
Regression tyypit
Francis Galton esitteli itse tämän käsitteen matematiikassa vuonna 1886. Regressio tapahtuu:
- lineaarinen;
- parabolinen;
- rauhallinen;
- eksponentiaalinen;
- hyperbolinen;
- mielenosoitus;
- logaritminen.
Esimerkki 1
Tarkastellaan ongelmaa, joka liittyy eronneiden tiimin jäsenten lukumäärän riippuvuuden määrittämiseen 6 teollisuusyrityksen keskipalkkaan.
Tehtävä. Kuudessa yrityksessä keskimääräinen kuukausipalkka ja sen vuoksi irtisanoutuneiden määrä tahdon mukaan. Taulukkomuodossa meillä on:
Tehtävään määritettäessä lopettavien työntekijöiden lukumäärän riippuvuutta 6 yrityksen keskipalkasta regressiomallilla on yhtälö Y = a0 + a1×1 +…+аkxk, jossa хi ovat vaikuttavia muuttujia, ai ovat regressiokertoimia ja k on tekijöiden lukumäärä.
Tässä tehtävässä Y on irtisanoutuneiden työntekijöiden indikaattori ja vaikuttava tekijä on palkka, jota merkitsemme X:llä.
Excel-taulukkolaskentaprosessorin ominaisuuksien käyttäminen
Excelin regressioanalyysiä edeltää sisäänrakennettujen funktioiden soveltaminen olemassa oleviin taulukkotietoihin. Näihin tarkoituksiin on kuitenkin parempi käyttää erittäin hyödyllistä "Analysis Pack" -lisäosaa. Aktivoidaksesi sen tarvitset:
- siirry "Tiedosto"-välilehdeltä "Asetukset"-osioon;
- valitse avautuvasta ikkunasta rivi "Lisäosat";
- napsauta "Go"-painiketta, joka sijaitsee alla, "Hallinta"-rivin oikealla puolella;
- valitse "Analyysipaketti" -nimen vieressä oleva valintaruutu ja vahvista toimintasi napsauttamalla "Ok".
Jos kaikki on tehty oikein, "Data"-välilehden oikealla puolella, joka sijaitsee "Excel"-laskentataulukon yläpuolella, näet haluttu painike.
Lineaarinen regressio Excelissä
Nyt kun meillä on kaikki tarvittavat virtuaaliset työkalut käsillä ekonometristen laskelmien suorittamiseen, voimme alkaa ratkaista ongelmaamme. Voit tehdä tämän seuraavasti:
- napsauta "Data Analysis" -painiketta;
- napsauta avautuvassa ikkunassa "Regressio" -painiketta;
- kirjoita näkyviin tulevalle välilehdelle Y:n (irtisanovien työntekijöiden lukumäärä) ja X:n (heidän palkansa) arvoalue;
- Vahvistamme toimintamme painamalla "Ok"-painiketta.
Tämän seurauksena ohjelma täyttyy automaattisesti uusi lehti pöytäprosessori regressioanalyysin tiedot. Huomio! Excelin avulla voit määrittää manuaalisesti haluamasi sijainnin tätä tarkoitusta varten. Se voi olla esimerkiksi sama arkki, jossa Y- ja X-arvot sijaitsevat, tai jopa uusi kirja, joka on erityisesti suunniteltu tällaisten tietojen tallentamiseen.
R-neliön regressiotulosten analyysi
IN Excel-tiedot käsiteltävän esimerkin tietojen käsittelyn aikana saatujen tietojen muoto on:
Ensinnäkin sinun tulee kiinnittää huomiota R-neliöön. Se edustaa determinaatiokerrointa. IN tässä esimerkissä R-neliö = 0,755 (75,5 %), eli mallin lasketut parametrit selittävät tarkasteltavien parametrien välisen suhteen 75,5 %. Mitä suurempi determinaatiokertoimen arvo on, sitä soveltuvammaksi valittu malli katsotaan tietty tehtävä. Sen katsotaan kuvaavan oikein todellista tilannetta, kun R-neliöarvo on yli 0,8. Jos R-neliö on tcr, niin hypoteesi lineaarisen yhtälön vapaan termin merkityksettömyydestä hylätään.
Tarkasteltavassa vapaan termin tehtävässä saatiin Excel-työkaluilla, että t = 169,20903 ja p = 2,89E-12, eli meillä on nolla todennäköisyys, että oikea hypoteesi vapaan termin merkityksettömyydestä hylätään . Tuntemattoman kertoimelle t = 5,79405 ja p = 0,001158. Toisin sanoen todennäköisyys sille, että oikea hypoteesi kertoimen merkityksettömyydestä tuntemattomalle hylätään, on 0,12 %.
Siten voidaan väittää, että tuloksena oleva lineaarinen regressioyhtälö on riittävä.
Osakepaketin ostamisen toteutettavuusongelma
Excelissä suoritetaan useita regressioita käyttämällä samaa data-analyysityökalua. Tarkastellaan tiettyä sovellusongelmaa.
NNN-yhtiön johdon on päätettävä 20 %:n osuuden ostamisesta MMM JSC:stä. Paketin (SP) hinta on 70 miljoonaa Yhdysvaltain dollaria. NNN:n asiantuntijat ovat keränneet tietoja vastaavista tapahtumista. Osakkeen arvoa päätettiin arvioida seuraavien parametrien mukaan miljoonina Yhdysvaltain dollareina ilmaistuna:
- ostovelat (VK);
- vuotuinen liikevaihto (VO);
- myyntisaamiset (VD);
- käyttöomaisuuden kustannukset (COF).
Lisäksi käytetään parametria yrityksen palkkavelka (V3 P) tuhansissa Yhdysvaltain dollareissa.
Ratkaisu Excel-taulukkolaskentaohjelmalla
Ensinnäkin sinun on luotava taulukko lähdetiedoista. Se näyttää tältä:
- kutsu "Data Analysis" -ikkuna;
- valitse "Regressio"-osio;
- syötä "Syöteväli Y" -ruutuun riippuvien muuttujien arvoalue sarakkeesta G;
- napsauta punaista nuolikuvaketta "Input Range X" -ikkunan oikealla puolella ja korosta arkilla kaikkien arvojen alue sarakkeet B, C,D,F.
Merkitse "Uusi taulukko" -kohta ja napsauta "Ok".
Hanki regressioanalyysi tietylle ongelmalle.
Tulosten tutkiminen ja johtopäätökset
"Keräämme" edellä esitetyistä pyöristetyistä tiedoista taulukkoarkille Excel prosessori, regressioyhtälö:
SP = 0,103*SOF + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.
Tutussa matemaattisessa muodossa se voidaan kirjoittaa seuraavasti:
y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844
MMM JSC:n tiedot on esitetty taulukossa:
Kun ne korvataan regressioyhtälössä, saadaan luku 64,72 miljoonaa Yhdysvaltain dollaria. Tämä tarkoittaa, että MMM JSC:n osakkeita ei kannata ostaa, koska niiden 70 miljoonan Yhdysvaltain dollarin arvo on melkoinen.
Kuten näette, Excelin taulukkolaskentaprosessorin ja regressioyhtälön käyttö mahdollisti tietoisen päätöksen tekemisen tietyn tapahtuman toteutettavuudesta.
Nyt tiedät mitä regressio on. Yllä käsitellyt Excel-esimerkit auttavat sinua päättämään käytännön ongelmia ekonometiikan alalta.
Regressioanalyysi on tilastollinen tutkimusmenetelmä, jonka avulla voit osoittaa tietyn parametrin riippuvuuden yhdestä tai useammasta riippumattomasta muuttujasta. Ennen tietokonetta sen käyttö oli varsin vaikeaa, varsinkin kun oli kyse suurista tietomääristä. Tänään, kun olet oppinut rakentamaan regression Excelissä, voit ratkaista monimutkaisia tilastollisia ongelmia muutamassa minuutissa. Alla on konkreettisia esimerkkejä taloustieteen alalta.
Regression tyypit
Itse tämä käsite otettiin käyttöön matematiikassa vuonna 1886. Regressio tapahtuu:
- lineaarinen;
- parabolinen;
- rauhallinen;
- eksponentiaalinen;
- hyperbolinen;
- mielenosoitus;
- logaritminen.
Esimerkki 1
Tarkastellaan ongelmaa, joka liittyy eronneiden tiimin jäsenten lukumäärän riippuvuuden määrittämiseen 6 teollisuusyrityksen keskipalkkaan.
Tehtävä. Kuudessa yrityksessä analysoitiin keskimääräinen kuukausipalkka ja vapaaehtoisesti irtisanoutuneiden määrä. Taulukkomuodossa meillä on:
Lopetaneiden määrä | Palkka |
||
30 000 ruplaa |
|||
35 000 ruplaa |
|||
40 000 ruplaa |
|||
45 000 ruplaa |
|||
50 000 ruplaa |
|||
55 000 ruplaa |
|||
60 000 ruplaa |
Jotta voidaan määrittää irtisanoutuvien työntekijöiden lukumäärän riippuvuus 6 yrityksen keskipalkasta, regressiomallilla on yhtälö Y = a 0 + a 1 x 1 +...+a k x k, jossa x i on vaikuttavat muuttujat, a i ovat regressiokertoimet ja k on tekijöiden lukumäärä.
Tässä ongelmassa Y on työntekijöiden irtisanomisen indikaattori ja vaikuttava tekijä on palkka, jota merkitsemme X:llä.
Excel-taulukkolaskentaprosessorin ominaisuuksien käyttäminen
Excelin regressioanalyysiä edeltää sisäänrakennettujen funktioiden soveltaminen olemassa oleviin taulukkotietoihin. Näihin tarkoituksiin on kuitenkin parempi käyttää erittäin hyödyllistä "Analysis Pack" -lisäosaa. Aktivoidaksesi sen tarvitset:
- siirry "Tiedosto"-välilehdeltä "Asetukset"-osioon;
- valitse avautuvasta ikkunasta rivi "Lisäosat";
- napsauta "Go"-painiketta, joka sijaitsee alla, "Hallinta"-rivin oikealla puolella;
- valitse "Analyysipaketti" -nimen vieressä oleva valintaruutu ja vahvista toimintasi napsauttamalla "Ok".
Jos kaikki on tehty oikein, vaadittu painike tulee näkyviin "Data" -välilehden oikealle puolelle, joka sijaitsee Excel-laskentataulukon yläpuolella.
Excelissä
Nyt kun meillä on kaikki tarvittavat virtuaaliset työkalut käsillä ekonometristen laskelmien suorittamiseen, voimme alkaa ratkaista ongelmaamme. Voit tehdä tämän seuraavasti:
- napsauta "Data Analysis" -painiketta;
- napsauta avautuvassa ikkunassa "Regressio" -painiketta;
- kirjoita näkyviin tulevalle välilehdelle Y:n (irtisanovien työntekijöiden lukumäärä) ja X:n (heidän palkansa) arvoalue;
- Vahvistamme toimintamme painamalla "Ok"-painiketta.
Tämän seurauksena ohjelma täyttää automaattisesti uuden laskentataulukon regressioanalyysitiedoilla. Huomio! Excelin avulla voit määrittää manuaalisesti haluamasi sijainnin tätä tarkoitusta varten. Tämä voi olla esimerkiksi sama arkki, jossa Y- ja X-arvot sijaitsevat, tai jopa uusi työkirja, joka on erityisesti suunniteltu tällaisten tietojen tallentamiseen.
R-neliön regressiotulosten analyysi
Excelissä tarkasteltavan esimerkin tietojen käsittelyn aikana saadut tiedot ovat muotoa:
Ensinnäkin sinun tulee kiinnittää huomiota R-neliöön. Se edustaa determinaatiokerrointa. Tässä esimerkissä R-neliö = 0,755 (75,5 %), eli mallin lasketut parametrit selittävät tarkasteltavien parametrien välisen suhteen 75,5 %. Mitä suurempi determinaatiokertoimen arvo on, sitä paremmin valittu malli sopii tiettyyn tehtävään. Sen katsotaan kuvaavan oikein todellista tilannetta, kun R-neliöarvo on yli 0,8. Jos R-neliö<0,5, то такой анализа регрессии в Excel нельзя считать резонным.
Kerroinanalyysi
Numero 64.1428 osoittaa, mikä on Y:n arvo, jos kaikki tarkastelemamme mallin muuttujat xi nollataan. Toisin sanoen voidaan väittää, että analysoitavan parametrin arvoon vaikuttavat myös muut tekijät, joita ei ole kuvattu tietyssä mallissa.
Seuraava solussa B18 oleva kerroin -0,16285 osoittaa muuttujan X vaikutuksen painon Y:hen. Tämä tarkoittaa, että tarkasteltavan mallin työntekijöiden keskimääräinen kuukausipalkka vaikuttaa lopettaneiden määrään painolla -0,16285, ts. sen vaikutusaste on täysin pieni. "-"-merkki osoittaa, että kerroin on negatiivinen. Tämä on ilmeistä, sillä kaikki tietävät, että mitä korkeampi palkka yrityksessä, sitä harvempi on halunnut irtisanoa työsopimuksen tai irtisanoutua.
Moninkertainen regressio
Tämä termi viittaa suhdeyhtälöön, jossa on useita riippumattomia muuttujia muodossa:
y=f(x 1 +x 2 +…x m) + ε, missä y on resultanttiominaisuus (riippuvainen muuttuja) ja x 1, x 2,…x m ovat tekijäominaisuuksia (riippumattomia muuttujia).
Parametrien arvio
varten moninkertainen regressio(MR) se suoritetaan pienimmän neliösumman menetelmällä (OLS). Lineaarisille yhtälöille muotoa Y = a + b 1 x 1 +…+b m x m + ε rakennamme normaaliyhtälöjärjestelmän (katso alla)
Ymmärtääksesi menetelmän periaatteen, harkitse kaksitekijäistä tapausta. Sitten meillä on kaavalla kuvattu tilanne
Täältä saamme:
missä σ on indeksiin heijastuneen vastaavan ominaisuuden varianssi.
OLS soveltuu MR-yhtälöön standardoidulla asteikolla. Tässä tapauksessa saamme yhtälön:
joissa t y, t x 1, … t xm ovat standardoituja muuttujia, joiden keskiarvot ovat 0; β i ovat standardoituja regressiokertoimia, ja keskihajonna on 1.
Huomaa, että tässä tapauksessa kaikki β i määritellään normalisoiduiksi ja keskitetyiksi, joten niiden vertailua keskenään pidetään oikeana ja hyväksyttävänä. Lisäksi on tapana seuloa pois tekijät, joiden βi-arvot ovat alhaisimmat.
Ongelma käytettäessä lineaarista regressioyhtälöä
Oletetaan, että meillä on taulukko tietyn tuotteen N hintadynamiikasta viimeisten 8 kuukauden ajalta. On tarpeen tehdä päätös siitä, onko suositeltavaa ostaa se erä hintaan 1850 ruplaa / tonni.
kuukauden numero | kuukauden nimi | tuotteen hinta n |
|
1750 ruplaa tonnilta |
|||
1755 ruplaa tonnilta |
|||
1767 ruplaa tonnilta |
|||
1760 ruplaa tonnilta |
|||
1770 ruplaa tonnilta |
|||
1790 ruplaa tonnilta |
|||
1810 ruplaa tonnilta |
|||
1840 ruplaa tonnilta |
|||
Tämän ongelman ratkaisemiseksi Excel-laskentataulukkoprosessorissa sinun on käytettävä "Data Analysis" -työkalua, joka on jo tunnettu yllä esitetystä esimerkistä. Valitse seuraavaksi "Regressio"-osio ja aseta parametrit. On muistettava, että "Syöteväli Y" -kenttään on syötettävä arvoalue riippuvalle muuttujalle (tässä tapauksessa tavaroiden hinnat tiettyinä vuoden kuukausina) ja "Syöteväli X" -kenttään. - riippumattomalle muuttujalle (kuukauden numero). Vahvista toimenpide napsauttamalla "OK". Uudelta arkilta (jos niin on ilmoitettu) saamme tiedot regressiota varten.
Niiden avulla rakennetaan lineaarinen yhtälö muotoa y=ax+b, jossa parametrit a ja b ovat kuukauden numeron nimen sisältävän rivin kertoimet ja kertoimet ja suorat "Y-leikkaus" taulukosta, jossa on regressioanalyysin tulokset. Siten tehtävän 3 lineaarinen regressioyhtälö (LR) kirjoitetaan seuraavasti:
Tuotteen hinta N = 11.714* kk numero + 1727.54.
tai algebrallisella merkinnällä
y = 11,714 x + 1727,54
Tulosten analyysi
Sen päättämiseksi, onko tuloksena saatu lineaarinen regressioyhtälö riittävä, käytetään moninkertaisen korrelaation (MCC) ja determination kertoimia sekä Fisher-testiä ja Studentin t-testiä. Excel-laskentataulukossa, jossa on regressiotulokset, niitä kutsutaan vastaavasti useiksi R-, R-neliöiksi, F-tilastoiksi ja t-tilastoiksi.
KMC R mahdollistaa riippumattoman ja riippuvan muuttujan välisen todennäköisyyssuhteen läheisyyden arvioimisen. Sen korkea arvo viittaa melko vahvaan yhteyteen muuttujien ”Kuukauden lukumäärä” ja ”Tuotteen hinta N ruplina tonnilta” välillä. Tämän suhteen luonne on kuitenkin edelleen tuntematon.
Determinaatiokertoimen R2 (RI) neliö on kokonaissirontaosuuden numeerinen ominaisuus ja osoittaa, minkä osan sirontaa koeaineistosta, ts. riippuvan muuttujan arvot vastaavat lineaarista regressioyhtälöä. Tarkasteltavana olevassa ongelmassa tämä arvo on 84,8 %, ts. tuloksena oleva SD kuvaa tilastotietoja suurella tarkkuudella.
F-tilastoa, jota kutsutaan myös Fisherin testiksi, käytetään merkitsevyyden arvioimiseen lineaarinen riippuvuus, joka kumoaa tai vahvistaa hypoteesin sen olemassaolosta.
(Opiskelijakoe) auttaa arvioimaan kertoimen merkitsevyyttä lineaarisen suhteen tuntemattomalla tai vapaalla termillä. Jos t-testin arvo > tcr, niin hypoteesi lineaarisen yhtälön vapaan termin merkityksettömyydestä hylätään.
Tarkasteltavassa vapaan termin tehtävässä saatiin Excel-työkaluilla, että t = 169,20903 ja p = 2,89E-12, eli meillä on nolla todennäköisyys, että oikea hypoteesi vapaan termin merkityksettömyydestä hylätään . Tuntemattoman kertoimelle t = 5,79405 ja p = 0,001158. Toisin sanoen todennäköisyys sille, että oikea hypoteesi kertoimen merkityksettömyydestä tuntemattomalle hylätään, on 0,12 %.
Siten voidaan väittää, että tuloksena oleva lineaarinen regressioyhtälö on riittävä.
Osakepaketin ostamisen toteutettavuusongelma
Excelissä suoritetaan useita regressioita käyttämällä samaa data-analyysityökalua. Tarkastellaan tiettyä sovellusongelmaa.
NNN-yhtiön johdon on päätettävä 20 %:n osuuden ostamisesta MMM JSC:stä. Paketin (SP) hinta on 70 miljoonaa Yhdysvaltain dollaria. NNN:n asiantuntijat ovat keränneet tietoja vastaavista tapahtumista. Osakkeen arvoa päätettiin arvioida seuraavien parametrien mukaan miljoonina Yhdysvaltain dollareina ilmaistuna:
- ostovelat (VK);
- vuotuinen liikevaihto (VO);
- myyntisaamiset (VD);
- käyttöomaisuuden kustannukset (COF).
Lisäksi käytetään parametria yrityksen palkkavelka (V3 P) tuhansissa Yhdysvaltain dollareissa.
Ratkaisu Excel-taulukkolaskentaohjelmalla
Ensinnäkin sinun on luotava taulukko lähdetiedoista. Se näyttää tältä:
- kutsu "Data Analysis" -ikkuna;
- valitse "Regressio"-osio;
- syötä "Syöteväli Y" -ruutuun riippuvien muuttujien arvoalue sarakkeesta G;
- Napsauta "Input interval X" -ikkunan oikealla puolella olevaa kuvaketta punaisella nuolella ja korosta kaikkien arvojen alue arkin sarakkeista B, C, D, F.
Merkitse "Uusi taulukko" -kohta ja napsauta "Ok".
Hanki regressioanalyysi tietylle ongelmalle.
Tulosten tutkiminen ja johtopäätökset
"Keräämme" regressioyhtälön yllä esitetyistä pyöristetyistä tiedoista Excel-laskentataulukossa:
SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.
Tutussa matemaattisessa muodossa se voidaan kirjoittaa seuraavasti:
y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844
MMM JSC:n tiedot on esitetty taulukossa:
Kun ne korvataan regressioyhtälössä, saadaan luku 64,72 miljoonaa Yhdysvaltain dollaria. Tämä tarkoittaa, että MMM JSC:n osakkeita ei kannata ostaa, koska niiden 70 miljoonan Yhdysvaltain dollarin arvo on melkoinen.
Kuten näette, Excelin taulukkolaskentaprosessorin ja regressioyhtälön käyttö mahdollisti tietoisen päätöksen tekemisen tietyn tapahtuman toteutettavuudesta.
Nyt tiedät mitä regressio on. Yllä käsitellyt Excel-esimerkit auttavat sinua ratkaisemaan käytännön ongelmia ekonometriikan alalla.
MS Excel -paketin avulla voit tehdä suurimman osan työstä erittäin nopeasti, kun muodostat lineaarisen regressioyhtälön. On tärkeää ymmärtää, miten saatuja tuloksia tulkitaan.
Vaatii lisäosan toimiakseen Analyysipaketti, joka on otettava käyttöön valikkokohdassa Palvelu\Lisäosat
Jos haluat ottaa analyysipaketin käyttöön Excel 2007:ssä, sinun on napsautettava Siirry estämään Excel-asetukset painamalla vasemmalla olevaa painiketta yläkulma, ja sitten painike Excel-asetukset"ikkunan alareunassa:
Regressiomallin luomiseksi sinun on valittava kohde Palvelu\Tietojen analyysi\Regressio. (Excel 2007:ssä tämä tila on lohkossa Data/Tietojen analyysi/Regressio). Näyttöön tulee valintaikkuna, joka sinun on täytettävä:
1) Syöttöväli Y¾ sisältää linkin soluihin, jotka sisältävät tuloksena olevan ominaisuuden arvot y. Arvot on järjestettävä sarakkeeseen;
2) Syöttöväli X¾ sisältää linkin soluihin, jotka sisältävät tekijäarvoja. Arvot on järjestettävä sarakkeisiin;
3) Allekirjoita Tunnisteet on asetettu, jos ensimmäiset solut sisältävät selittävä teksti(tietojen allekirjoitukset);
4) Luotettavuustaso¾ on luottamustaso, jonka oletuksena pidetään 95 %. Jos tämä arvo ei sovi sinulle, sinun on otettava tämä lippu käyttöön ja syötettävä vaadittu arvo;
5) Allekirjoita Vakio-nolla sisällytetään, jos on tarpeen muodostaa yhtälö, jossa vapaa muuttuja on ;
6) Lähtöasetukset määrittää, mihin tulokset sijoitetaan. Oletuksena koontitila Uusi laskentataulukko;
7) Lohko Ylijäämät voit sisällyttää jäännöstulokset ja niiden kaavioiden rakentamisen.
Tuloksena tiedot, jotka sisältävät kaiken tarvittavat tiedot ja ryhmitelty kolmeen lohkoon: Regressiotilastot, Varianssianalyysi, Tasapainon poistaminen. Katsotaanpa niitä tarkemmin.
1. Regressiotilastot:
useita R määräytyy kaavalla ( Pearsonin korrelaatiokerroin);
R 
(determinaatiokerroin);
Normalisoitu R-neliö lasketaan kaavalla 
(käytetään moninkertaiseen regressioon);
Normaali virhe S lasketaan kaavalla 
;
Havainnot ¾ on tiedon määrä n.
2. Varianssianalyysi, rivi Regressio:
Parametri df on yhtä suuri m(tekijäjoukkojen määrä x);
Parametri SS määritetään kaavalla ;
Parametri MS määritetään kaavalla ;
Tilastot F määritetään kaavalla ;
Merkitys F. Jos saatu luku on suurempi kuin , hypoteesi hyväksytään (ei ole lineaarista suhdetta), muuten hypoteesi hyväksytään (on lineaarinen suhde).
3. Varianssianalyysi, rivi Loput:
Parametri df yhtä suuri kuin ;
Parametri SS määräytyy kaavan mukaan 
;
Parametri MS määräytyy kaavan mukaan.
4. Varianssianalyysi, rivi Kokonais sisältää kahden ensimmäisen sarakkeen summan.
5. Varianssianalyysi, rivi Y-risteys sisältää kertoimen, keskivirheen ja t-tilastot.
P-arvo ¾ on laskettua vastaavien merkitsevyystasojen arvo t- tilastotieteilijät. Määritetään funktiolla STUDIDIST( t-tilastot; ). Jos P-arvo ylittää, silloin vastaava muuttuja on tilastollisesti merkityksetön ja voidaan jättää mallin ulkopuolelle.
Pohja 95 % Ja Top 95 %¾ ovat teoreettisen lineaarisen regressioyhtälön kertoimien 95 prosentin luottamusvälin ala- ja ylärajat. Jos tietojen syöttölohkon luottamusarvo jätettiin oletusarvoonsa, kaksi viimeistä saraketta kopioivat edelliset. Jos käyttäjä on syöttänyt oman luottamusarvonsa, kahdessa viimeisessä sarakkeessa on määritetyn luottamustason ala- ja yläraja-arvot.
6. Varianssianalyysi, rivit sisältävät kertoimen arvot, vakiovirheet, t-tilastotieteilijä, P-arvot ja luottamusvälit vastaavalle .
7. Estä Tasapainon poistaminen sisältää ennustetut arvot y(käsityksessämme tämä on ) ja jäännökset .
Muutos tuloksena olevassa ominaisuudessa y johtuu tekijän ominaiskäyrän x vaihtelusta. Regressiolla selitetty varianssiosuus tuloksena olevan ominaisuuden kokonaisvarianssista luonnehtii determinaatiokerrointa R2. Lineaarisessa suhteessa determinaatiokerroin on yhtä suuri kuin korrelaatiokertoimen neliö:R 2 = r xy 2, missä r xy on korrelaatiokerroin.
Esimerkiksi arvo R 2 = 0,83 tarkoittaa, että 83 %:ssa tapauksista x:n muutokset johtavat y:n muutoksiin. Toisin sanoen regressioyhtälön valinnan tarkkuus on korkea.
Laskettu regressioyhtälön sovituksen laadun arvioimiseksi. Hyväksyttäville malleille ehdotetaan, että determinaatiokertoimen tulisi olla suurempi kuin 50 %. Malleja, joiden determinaatiokerroin on yli 80 %, voidaan pitää varsin hyvinä. Determinaatiokertoimen arvo R 2 = 1 tarkoittaa toiminnallinen riippuvuus muuttujien välillä.
Siinä tapauksessa epälineaarinen regressio determinaatiokerroin lasketaan tällä laskimella. Moninkertaisella regressiolla determinaatiokerroin löytyy Multiple Regression -palvelun kautta
Yleensä determinaatiokerroin löydetään kaavasta: tai
Varianssien lisäämissääntö:
,
missä on neliöityjen poikkeamien kokonaissumma;
- regressiosta johtuvien poikkeamien neliösumma ("selitetty" tai "tekijä");
- neliöityjen poikkeamien jäännössumma.
Tämän online-laskimen avulla voit laskea determinaatiokerroin ja sen merkitys tarkistetaan (esimerkkiratkaisu).
Ohjeet. Määritä syöttötietojen määrä. Saatu liuos varastoidaan Word-tiedosto. Malli luodaan automaattisesti myös ratkaisun testaamista varten Excelissä.
MS Excel -paketin avulla voit tehdä suurimman osan työstä erittäin nopeasti, kun muodostat lineaarisen regressioyhtälön. On tärkeää ymmärtää, miten saatuja tuloksia tulkitaan. Regressiomallin luomiseksi sinun on valittava Työkalut\Dataanalyysi\Regressio (Excel 2007:ssä tämä tila on Data/Data Analysis/Regression -lohkossa). Kopioi sitten tulokset lohkoon analysointia varten.
