Regressie in Excel: vergelyking voorbeelde. lineêre regressie

Regressie-analise - 'n statistiese studie metode om die afhanklikheid van 'n parameter van een of meer onafhanklike veranderlikes te toon. In die pre-rekenaar era, het die gebruik daarvan eerder moeilik, veral wanneer dit kom by groot volumes van data. Vandag, leer hoe om 'n regressie in Excel te bou, jy kan komplekse statistiese probleme in 'n paar minute op te los. Hier is 'n spesifieke voorbeelde van die ekonomie.

tipes regressie

Hierdie konsep is ingestel om wiskunde deur Francis Galton in 1886. Regressie is:

• lineêre;
• paraboliese;
• krag;
• eksponensiële;
• hiperboliese;
• eksponensiële;
• logaritmiese.

VOORBEELD 1

Kyk na die probleem van die bepaling van die afhanklikheid van die aantal bedankings van personeel van die gemiddelde loon in die 6 industriële ondernemings.

Taak. Ses maatskappye het die gemiddelde maandelikse salaris en die aantal werknemers wat vrywillig bedank ontleed. In tabelvorm het ons:

	A	B	C
1	X	Aantal bedankings	salaris
2		y	30000 roebels
3	1	60	35000 roebels
4	2	35	40000 roebels
5	3	20	45000 roebels
6	4	20	50.000 roebels
7	5	15	55000 roebels
8	6	15	60000 roebels

Vir die probleem van die bepaling van die afhanklikheid van die bedrag skeidings werkers van die gemiddelde salaris vir 6 ondernemings regressiemodel het die vorm van vergelyking y = a ₀ + 'n ₁ x ₁ + ... + 'n _k x _k, waar x _i - beïnvloed veranderlikes, 'n _i - regressiekoëffisiënte, ak - aantal faktore.

Y vir 'n gegewe taak - dit is 'n aanduiding om 'n werknemer te vuur, 'n bydraende faktor - die salaris, wat aangedui word deur X.

Benutting van die krag van "Excel" spreadsheet

Regressie-analise in Excel moet voorafgegaan word deur 'n aansoek om die bestaande tabel data ingeboude funksies. Maar vir hierdie doeleindes is dit beter om 'n baie nuttige add-in "pakkie analise" gebruik. Om dit moontlik te maak, moet jy:

• met die blad "File" gaan na "Settings";
• in die venster wat oopmaak, kies 'Byvoegings';
• Klik op die knoppie "Go", geleë aan die onderkant regs van die lyn "bestuur";
• sit 'n tjek merk langs "Ontleding ToolPak" en bevestig jou optrede deur te druk "OK".

As dit korrek is, die regterkant van die blad "Data", bo die werkkaart "Excel" geleë is, toon die gewenste knoppie.

Lineêre regressie in Excel

Nou dat jy het op die hand al die nodige virtuele instrumente vir ekonometriese berekeninge, kan ons begin om ons probleem aan te spreek. Om dit te doen:

• knoppie is gekliek die "Data Analysis";
• Klik op die knoppie "regressie" in die oop venster;
• 'n blad wat lyk na 'n verskeidenheid van waardes in te voer Y (die aantal skeidings werkers) en X (hul salaris);
• herbevestig hul optrede deur te druk op die «Ok» knoppie.

As gevolg hiervan, sal die program outomaties die nuwe blad spreadsheet data regressie-analise te vul. Aandag te gee! In Excel, daar is 'n geleentheid om die plek wat jy verkies vir hierdie doel opgestel. Byvoorbeeld, kan dit dieselfde blad, waar die waardes Y en X, of selfs 'n nuwe boek, wat spesifiek ontwerp is vir die berging van sulke data word.

Regressie-analise resultate vir R-kwadraat

Die Excel data verkry in die oorweeg voorbeeld data het die vorm:

In die eerste plek, moet ons aandag skenk aan die waarde van R-kwadraat. Dit verteenwoordig die bepaaldheidskoëffisiënt. In hierdie voorbeeld, R-kwadraat = 0,755 (75,5%), m. E. Die berekende parameters van die model om die verhouding tussen die parameters deur 75,5% beskou verduidelik. Hoe hoër die waarde van die bepaaldheidskoëffisiënt, is die gekose model beskou as meer nuttig vir spesifieke take te wees. Daar word geglo om die werklike situasie korrek beskryf by die R-kwadraat waarde bo 0.8. As die R-kwadraat <0.5, dan 'n regressie-analise in Excel kan nie redelik geag.

verhoudingsontleding

Aantal 64,1428 toon wat sal die waarde van die Y wees, as al die veranderlikes xi in ons model herstel sal wees. Met ander woorde, kan dit aangevoer word dat die waarde van die ontleed parameter word beïnvloed deur ander faktore as dié in die spesifieke model beskryf.

Die volgende faktor -,16285 geleë in sel B18, toon die belangrike invloed van veranderlike X aan Y Dit beteken dat die gemiddelde salaris van werknemers binne die model beïnvloed die aantal bedankings uit die gewig van -,16285, t. E. Die graad van die impak daarvan op alle klein. Die teken "-" dui daarop dat die koëffisiënt is negatief. Dit is voor die hand liggend, want ons almal weet dat die meer salaris in die onderneming, hoe minder mense het 'n begeerte om die dienskontrak of afgedank te beëindig uitgespreek.

meervoudige regressie

Onder hierdie term verwys na die kommunikasie vergelyking met verskeie onafhanklike veranderlikes van die vorm:

y = f (x ₁ + x ₂ + ... x _m) + ε, waar y - is 'n kenmerk telling (die afhanklike veranderlike) en x _1, x _2, ... x _m - is tekens faktore (onafhanklike veranderlikes).

parameter beraming

Vir meervoudige regressie (MR) dit uitgevoer word met behulp van 'n kleinste kwadrate metode (LSM). Vir lineêre vergelykings van die vorm y = a + b ₁ x _{1 + ... + b m x m +} ε bou van 'n stelsel van normale vergelykings (cm. Hieronder)

Om die beginsel van die metode verstaan, ons kyk na die twee-faktor geval. Toe het ons die situasie beskryf deur die formule

Dus, ons kry:

waar σ - is die variansie van die onderskeie funksie, weerspieël in die indeks.

MNC is van toepassing op die vergelyking MR om standartiziruemom skaal. In hierdie geval, kry ons die vergelyking:

waarin t _y, t _{x 1, ...} t _xm - standartiziruemye veranderlikes waarvoor gemiddelde waardes 0; beta _i - gestandaardiseerde regressiekoëffisiënte en standaardafwyking - 1.

Neem asseblief kennis dat alle beta _i in hierdie geval gedefinieer as die genormaliseerde en tsentraliziruemye, dus 'n vergelyking tussen 'n oorweeg geldig en aanvaarbaar. Daarbenewens is dit aanvaar om keuring van faktore uit te voer, wegdoen diegene wat die laagste waardes van βi het.

Die probleem met die gebruik van lineêre regressievergelyking

Veronderstel jy het 'n tafel van die dinamika van die prys van 'n bepaalde produk N vir die laaste 8 maande. Dit is nodig om te besluit of die verkryging van sy party by die prys van 1850 roebels. / T.

	A	B	C
1	die maand	Naam van die maand	prys N
2	1	Januarie	1750 roebels per ton
3	2	Februarie	1755 roebels per ton
4	3	Maart	1767 roebels per ton
5	4	April	1760 roebels per ton
6	5	Mei	1770 roebels per ton
7	6	Junie	1790 roebels per ton
8	7	Julie	1810 roebels per ton
9	8	Augustus	1840 roebels per ton

Om hierdie probleem op te los in die tabel verwerker "Excel" wat nodig is om te gebruik reeds bekend byvoorbeeld instrument "Data Analysis" hierbo aangebied. Volgende, kies artikel "Regressie" en stel parameters. Ons moet onthou dat in die "Input reeks Y» ingestel moet word om 'n verskeidenheid van waardes van die afhanklike veranderlike (in hierdie geval die prys van die goedere in spesifieke maande van die jaar) en in die "Input interval X» - vir 'n onafhanklike (die maand). Ons bevestig die aksie deur te kliek «Ok». In 'n nuwe werkblad (indien so aangedui), ons kry die data vir die regressie.

Ons is besig om op hulle lineêre vergelyking van die vorm y = ax + b, waar as die parameters a en b is die koëffisiënte van die lyn nommer van die maand en naam van die koëffisiënte en «Y-kruising" lyn van die vel met die resultate van die regressie-analise. Dus, kan die lineêre regressievergelyking (EQ) 3 vir die probleem geskryf word as:

Die prys van goedere N = 11.714 * 1727,54 maand nommer +.

of in algebraïese notasie

y = 11.714 x + 1727,54

ontleding van resultate

Om te besluit of die ontvang voldoende lineêre regressievergelyking gebruik van die verskeie korrelasiekoëffisiënte (CMC) en vasberadenheid asook toets en t-toets Fisher se. In die tafel "Excel" regressie met die resultate wat hulle optree onder die name van verskeie R, R-Square, F-t-statistiek en statistieke, onderskeidelik.

KMC R in staat stel om die nabyheid probabilistiese verhouding tussen onafhanklike en afhanklike veranderlikes te bepaal. Sy hoë waarde dui op 'n sterk genoeg verband tussen die veranderlike "nommer van die maand" en "N Produk prys in roebels per 1 ton." Maar die aard van hierdie verhouding is onbekend.

Die vierkant van die bepaaldheidskoëffisiënt R ² (RI) is 'n numeriese eienskap van die persentasie van die totale strooi en toon 'n strooi van eksperimentele data gedeelte, dit wil sê, waardes van die afhanklike veranderlike wat ooreenstem met 'n lineêre regressievergelyking. In hierdie probleem, hierdie waarde is 84,8%, LP. E. Statistiek met 'n hoë graad van akkuraatheid verkry word beskryf SD.

F-statistiek, ook bekend as Fisher maatstaf gebruik word om die betekenis van die lineêre afhanklikheid of disproving hipotese bevestig sy bestaan te evalueer.

Die waarde van t-statistiek (Student se t-toets) help evalueer die betekenis van die koëffisiënt te eniger gratis onbekende lineêre afhanklikheid lid. As die waarde van t-toets> t _cr, is die hipotese van 'n lineêre vergelyking nietigheid van gratis term verwerp.

In hierdie probleem vir 'n gratis term deur instrumente "Excel" is bevind dat t = 169,20903, en p = 2,89E-12, t. E. Het jy 'n nul waarskynlikheid dat die getroue die hipotese van die nietigheid van die vrye term sal verwerp word. Vir onbekende koëffisiënt by t = 5,79405, en p = 0,001158. Met ander woorde, die waarskynlikheid dat 'n verwerp korrekte hipotese sal nietigheid van die koëffisiënt vir die onbekende, is 0,12%.

So, kan dit aangevoer word dat die verkry lineêre regressievergelyking voldoende.

Die probleem van die wenslikheid van die koop van aandele

Meervoudige regressie is uitgevoer in Excel met behulp van dieselfde "Data Analysis" hulpmiddel. Kyk na die spesifieke toepassing.

Guide maatskappy «NNN» moet besluit of te koop 20% van die aandele van die RDK «MMM». Pakket prys (SP) 70 miljoen Amerikaanse dollars. Spesialiste van «NNN» ingesamelde data op soortgelyke transaksies. Daar is besluit om die waarde van die aandele op sulke parameters te bepaal, uitgedruk in miljoene Amerikaanse dollars, soos:

• krediteure (VK);
• jaarlikse omset volume (VO);
• debiteure (VD);
• waarde van vaste bates (SOF).

Daarbenewens gebruik die loon skuld van ondernemings (V3 U) in duisende Amerikaanse dollars.

Die besluit tafel verwerker Excel middel

Eerstens moet jy 'n tafel van insette data te skep. Dit is soos volg:

volgende:

• oproep boks "data-analise";
• gekies afdeling "Regressie";
• die venster "Input interval Y» geadministreer reeks afhanklike veranderlike waardes in kolom G;
• Klik op die ikoon met 'n rooi pyl aan die regterkant van die venster "Input interval X» en geïsoleerd op 'n vel reeks van alle waardes van kolom B, C, D, F.

Mark die punt "Nuwe werkblad" en kliek op "OK".

Kry 'n regressie-ontleding vir hierdie taak.

Die studie resultate en gevolgtrekkings

"Versamel" afgerond uit bogenoemde aangebied op die blad tafel Excel verwerker regressievergelyking data:

SD = 0,103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265844.

In die meer gebruiklike wiskundige vorm kan dit geskryf word as:

y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * X5 - 265844

Data vir «MMM» JSC aangebied in die tabel hieronder:

SOF, dollar	VO, dollar	VK, dollar	VD, dollar	VZP, dollar	JV, dollar
102,5	535,5	45.2	41.5	21,55	64,72

Vervang hulle in die regressievergelyking, verkry 'n figuur van 64.720.000 Amerikaanse dollars. Dit beteken dat die aandele van die RDK «MMM» nie moet koop, want hulle koste redelik is hoë kant teen 70 miljoen Amerikaanse dollars.

Soos jy kan sien, die gebruik van spreadsheet "Excel" en die regressievergelyking toegelaat word om 'n ingeligte besluit oor die wenslikheid baie spesifieke transaksie te maak.

Nou weet jy wat 'n regressie. Voorbeelde na Excel, wat hierbo bespreek is, sal jou help in die oplossing van praktiese probleme van ekonometrie.