Regressie in Excel: vergelijking voorbeelden. lineaire regressie

Regressieanalyse - statistisch onderzoeken methode om de afhankelijkheid van een parameter van één of meer onafhankelijke variabelen. In de pre-computer tijdperk, heeft het gebruik ervan nogal moeilijk, vooral als het ging om grote hoeveelheden gegevens. Vandaag de dag, leren hoe je een regressie in Excel te bouwen, kun je complexe statistische problemen in slechts een paar minuten op te lossen. Hieronder zijn specifieke voorbeelden van de economie.

types regressie

Dit concept is ingevoerd om de wiskunde van Francis Galton in 1886. Regressie is:

• lineaire;
• parabolische;
• vermogen;
• exponentieel;
• hyperbolisch;
• exponentieel;
• logaritmische.

Voorbeeld 1

Denk aan het probleem van het bepalen van de afhankelijkheid van het aantal aftreden van leden van het personeel van het gemiddelde loon in de 6 industriële ondernemingen.

Taak. Zes bedrijven hebben de gemiddelde maandelijkse salaris en het aantal werknemers die vrijwillig stoppen geanalyseerd. In tabelvorm hebben we:

	Een	B	C
1	X	Aantal ontslagen	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 roebel
7	5	15	55.000 roebels
8	6	15	60000 roebels

Voor het probleem van het bepalen van de afhankelijkheid van de hoeveelheid scheidingen werknemers van het gemiddelde salaris 6 bedrijven regressiemodel de vorm van vergelijking Y = a ₀ + ₁ x ₁ + ... + _{ak xk,} waarbij _Xi - invloedgrootheden, _ai - regressiecoëfficiënten, ak - aantal factoren.

Y voor een bepaalde taak - het is een indicator aan een werknemer ontslaan, een bijdragende factor - het salaris, dat wordt aangeduid met X.

Benutten van de kracht van de "Excel" spreadsheet

Regressieanalyse Excel moet worden voorafgegaan door een verzoek aan de bestaande tabelgegevens ingebouwde functies. Echter, voor deze doeleinden is het beter om een zeer nuttige add-in "packet analyse" te gebruiken. Om het te activeren, moet u:

• met het tabblad "File" ga naar "Instellingen";
• in het venster dat opent, selecteer 'Add-ons';
• klik op de knop "Go", gelegen aan de onderkant rechts van de lijn "management";
• zet een vinkje naast "Analysis ToolPak" en bevestig uw actie door op "OK".

Wanneer correct gedaan, de rechterkant van het tabblad "Data", boven het werkblad "Excel" gelegen, toont de gewenste knop.

Lineaire regressie in Excel

Nu, wanneer de hand is alle virtuele instrumenten voor econometrische berekeningen, kunnen we beginnen met onze probleem aan te pakken. Om dit te doen:

• knop wordt geklikt op de "Data-analyse";
• klik op de knop "regressie" in het open raam;
• een tab die lijkt op een waardenbereik voeren Y (aantal scheidingen werknemers) en X (hun salaris);
• herbevestigen hun acties door te drukken op de «OK» knop.

Als gevolg daarvan zal het programma automatisch het nieuwe blad spreadsheet data regressie-analyse in te vullen. Let op! In Excel, er is een kans om de plaats die u liever voor dit doel in te stellen. Bijvoorbeeld kan hetzelfde vel, waarbij de waarden Y en X, of een boek, speciaal ontworpen voor de opslag van dergelijke gegevens.

Regressieanalyse voor R-square

De Excel-gegevens verkregen in het beschouwde voorbeeld gegevens hebben de vorm:

Allereerst moeten we aandacht besteden aan de waarde van R-kwadraat. Het vertegenwoordigt de determinatiecoëfficiënt. In dit voorbeeld, R-kwadraat = 0,755 (75,5%), smp. E. De berekende parameters van het model om de relatie tussen de parameters 75,5% beschouwd leggen. Hoe hoger de waarde van de determinatiecoëfficiënt, wordt het geselecteerde model beschouwd bruikbaar voor specifieke taken. Er wordt aangenomen dat de werkelijke situatie terecht te beschrijven op het R-kwadraat waarde boven 0,8. Als de R-square <0,5, dan is een regressieanalyse Excel niet als redelijk beschouwd.

ratioanalyse

Aantal 64,1428 laat zien wat zal de waarde van de Y zijn, als alle variabelen xi in ons model gereset. Met andere woorden kan worden gesteld dat de waarde van de geanalyseerde parameter wordt beïnvloed door andere factoren dan in het specifieke model beschreven.

De volgende factor -0,16285 in cel B18, toont de grote invloed van de variabele X Y. Dit betekent dat de gemiddelde salaris van werknemers in het model invloed op het aantal ontslagen uit het gewicht van -0,16285, t. E. De mate van het effect helemaal klein. Het teken "-" aan dat de coëfficiënt negatief is. Het ligt voor de hand, want we weten allemaal dat hoe meer salaris in de onderneming, hoe minder mensen hebben een verlangen om de arbeidsovereenkomst of ontslagen te beëindigen uitgedrukt.

meervoudige regressie

Onder dit begrip verwijst naar de communicatie vergelijking met verscheidene onafhankelijke variabelen van de vorm:

y = f (x ₁ + _X2 + ... x _m) + ε, waarbij y - deze functie score (afhankelijke variabele), en x _1, x _2, ... x _m - tekenen factoren (onafhankelijke variabele).

parameterschatting

Voor meervoudige regressie (MR) wordt uitgevoerd met behulp van een kleinste kwadraten methode (LSM). Voor lineaire vergelijkingen van de vorm y = a + b ₁ x _{1 + ... + b m x m +} ε een systeem op normale vergelijkingen (cm. Hieronder)

Om het principe van de methode te begrijpen, beschouwen we de twee-factor geval. Dan hebben we de situatie beschreven door de formule

Vandaar, krijgen we:

waarin σ - de variantie van de respectievelijke functie, weerspiegeld in de index.

MNC geldt voor de vergelijking MR standartiziruemom schaal. In dit geval krijgen we de vergelijking:

waarbij _{ty, tx 1, ...} t _xm - standartiziruemye variabelen waarvoor waarden zijn 0; _Pi - gestandaardiseerde regressiecoëfficiënten en standaardafwijking - 1.

Houdt u er rekening mee dat alle _Pi in dit geval gedefinieerd als de genormaliseerde en tsentraliziruemye derhalve een vergelijking tussen een als geldig en aanvaardbaar. Bovendien wordt aangenomen screening factoren uit te voeren weggooien die welke de laagste waarden van βi hebben.

Het probleem met lineaire regressievergelijking

Stel dat u een tabel van de dynamiek van de prijs van een bepaald product N voor de laatste 8 maanden. Het is noodzakelijk om te beslissen of de overname van zijn partij tegen de prijs van 1850 roebel. / T.

	Een	B	C
1	de maand	de naam van de maand	Prijs N
2	1	januari	1.750 RUR per ton
3	2	februari	1755 roebel per ton
4	3	maart	1767 roebel per ton
5	4	april	1760 roebel per ton
6	5	mei	1770 roebel per ton
7	6	juni	1790 roebel per ton
8	7	juli	1810 roebel per ton
9	8	augustus	1840 roebel per ton

Om dit probleem op de tabulaire processor "Excel" moeten gebruiken reeds bekend bijvoorbeeld functie "Data Analysis" bovengenoemde zin. Kies vervolgens de sectie "Regression" en stel parameters. We moeten niet vergeten dat in de "Input range Y» moeten worden ingevoerd om een bereik van waarden van de afhankelijke variabele (in dit geval de prijs van de goederen in bepaalde maanden van het jaar) en in de "Input interval X» - voor een onafhankelijke (de maand). Wij bevestigen de actie door te klikken op «OK». In een nieuw werkblad (tenzij zo aangegeven), krijgen we de gegevens voor de regressie.

Voortbouwend op deze lineaire vergelijking van de vorm y = ax + b, waarbij de parameters a en b zijn de coëfficiënten van de lijn met het nummer en de naam van de maand en de coëfficiënten «Y-kruising" regel van het blad met de resultaten van de regressieanalyse. Aldus kan de lineaire regressievergelijking (EQ) 3 voor het probleem worden geschreven als:

De prijs van de goederen N = 11.714 * 1727,54 maandnummer +.

of in de algebraïsche notatie

y = 11.714 x + 1727,54

analyse van de resultaten

Te bepalen of de ontvangen voldoende lineaire regressievergelijking De meerdere correlatiecoëfficiënten (CMC) en bepaling en testen t-toets van Fisher. In de tabel "Excel" regressie met de resultaten ze handelen onder de namen meerdere R, R-kwadraat, F-t-statistieken en statistieken, respectievelijk.

KMC R staat stelt de mate probabilistische relatie tussen onafhankelijke en afhankelijke variabelen te schatten. De hoge waarde geeft een voldoende sterke verbinding tussen de variabele "Aantal van de maand" en "N Productprijs in roebels per 1 ton." Echter, de aard van deze relatie is onbekend.

Het kwadraat van de determinatiecoëfficiënt ^R2 (RI) is een numeriek kenmerk van het aandeel van de verstrooiing en toont een spreiding van de experimentele gegevensgedeelte, d.w.z. waarden van de afhankelijke variabele overeenkomend met een lineaire regressievergelijking. In deze opgave is deze waarde 84,8%, smp. E. Statistiek met een grote nauwkeurigheid verkregen worden beschreven SD.

F-statistiek, ook wel bekend als Fisher criterium gebruikt om de betekenis van de lineaire afhankelijkheid of weerleggen hypothese te bevestigen het bestaan ervan te beoordelen.

De waarde van de t-statistiek (Student's t-test) helpt evalueren van de betekenis van de coëfficiënt op elk vrij onbekend lineaire afhankelijkheid lid. Indien de waarde van t-test> T _cr, is de hypothese van een lineaire vergelijking geringe aandeel van vrije term verworpen.

In dit probleem voor een gratis termijn door instrumenten "Excel" werd vastgesteld dat t = 169,20903, en p = 2,89E-12, t. E. Heeft u een nul kans dat de gelovigen de hypothese van de nietigheid van de vrije termijn zal worden afgewezen. Om onbekende coëfficiënt op t = 5,79405, en p = 0,001158. Met andere woorden, de kans dat een afgewezen juiste hypothese geringe belang van de coëfficiënt voor het onbekende, is 0,12%.

Derhalve kan worden gesteld dat de verkregen lineaire regressievergelijking adequaat.

Het probleem van de wenselijkheid van het kopen van aandelen

Multiple regressie werd uitgevoerd in Excel met behulp van dezelfde "Data Analysis" tool. Denk aan de specifieke toepassing.

Guide bedrijf «NNN» moet beslissen of te kopen 20% van de aandelen van JSC «MMM». Pakket prijs (SP) is 70 miljoen dollar. Specialisten van «NNN» verzamelde gegevens over soortgelijke transacties. Er werd besloten om de waarde van de aandelen op dergelijke parameters te beoordelen, uitgedrukt in miljoenen US dollars, zoals:

• verplichtingen (VK);
• omzet volume (VO);
• vorderingen (VD);
• waarde van vaste activa (SOF).

Daarnaast gebruiken het loon schulden van ondernemingen (V3 U) in duizenden US dollars.

De beslistabel processor middelen Excel

Eerst moet je een tabel van de input data te creëren. Het is als volgt:

Volgende:

• telefooncel "data-analyse";
• geselecteerde sectie "Regressie";
• Venster "Input interval Y» toegediend bereik afhankelijke variabelen in kolom G;
• klik op het pictogram met een rode pijl aan de rechterkant van het venster "Input interval X» en geïsoleerd op een vel bereik van alle waarden van de kolom B, C, D, F.

Markeer het punt "Nieuwe werkblad" en klik op "Ok".

Hier krijg je een regressie-analyse voor deze taak.

De studie resultaten en conclusies

"Verzamel" afgerond van de hierboven beschreven op het vel Excel processor regressievergelijking gegevens:

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

In de meer gebruikelijke wiskundige vorm kan worden geschreven als:

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

Gegevens voor «MMM» JSC weergegeven in de onderstaande tabel:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535.5	45.2	41.5	21.55	64.72

Het vervangen van hen in de regressievergelijking leverde een bedrag van 64.720.000 dollar. Dit betekent dat de aandelen van JSC «MMM» niet moet kopen, omdat de kosten vrij is te duur op 70 miljoen dollar.

Zoals u kunt zien, het gebruik van spreadsheet "Excel" en de regressievergelijking toegestaan om een weloverwogen beslissing over de geschiktheid heel specifieke transactie te maken.

Nu weet je wat een regressie. Voorbeelden naar Excel, hierboven besproken, zal u helpen bij het oplossen van praktische problemen van de econometrie.