Meerdere r in excel. Correlatie- en regressieanalyse in Excel: uitvoeringsinstructies

Regressieanalyse is een van de meest populaire methoden statistisch onderzoek. Het kan worden gebruikt om de mate van invloed van onafhankelijke variabelen op de afhankelijke variabele vast te stellen. Op functionaliteit MicrosoftExcel Er zijn tools ontworpen om dit soort analyses uit te voeren. Laten we eens kijken naar wat ze zijn en hoe we ze kunnen gebruiken.

Het analysepakket aansluiten

Maar om de functie te kunnen gebruiken waarmee u regressieanalyses kunt uitvoeren, moet u eerst het Analysepakket activeren. Alleen dan verschijnen de hulpmiddelen die nodig zijn voor deze procedure op het Excel-lint.

Ga naar het tabblad "Bestand".
Ga naar het gedeelte 'Instellingen'.
Het venster Excel-opties wordt geopend. Ga naar de subsectie 'Add-ons'.
Helemaal onderaan het venster dat wordt geopend, verplaatst u de schakelaar in het blok 'Besturing' naar de positie ' Excel-invoegtoepassingen"Als het zich in een andere positie bevindt. Klik op de knop "Ga".
Er wordt een venster met beschikbare Excel-invoegtoepassingen geopend. Vink het vakje naast 'Analysepakket' aan. Klik op de knop "OK".

Als we nu naar het tabblad "Gegevens" gaan, zien we op het lint in het gereedschapsblok "Analyse" een nieuwe knop - "Gegevensanalyse".

Soorten regressieanalyse

Er zijn verschillende soorten regressies:

parabolisch;
kalmeren;
logaritmisch;
exponentieel;
demonstratief;
hyperbolisch;
lineaire regressie.

We zullen later meer in detail praten over het uitvoeren van het laatste type regressieanalyse in Excel.

Lineaire regressie in Excel

Hieronder ziet u als voorbeeld een tabel met de gemiddelde dagelijkse luchttemperatuur buiten en het aantal winkelklanten voor de bijbehorende werkdag. Laten we met behulp van regressieanalyse precies uitvinden hoe weersomstandigheden in de vorm van de luchttemperatuur kan de aanwezigheid in een winkel beïnvloeden.

De algemene lineaire regressievergelijking is als volgt: Y = a0 + a1x1 +...+akhk. In deze formule betekent Y de variabele waarop we de invloed van factoren proberen te bestuderen. In ons geval is dit het aantal kopers. De waarde van x zijn de verschillende factoren die de variabele beïnvloeden. De parameters a zijn de regressiecoëfficiënten. Dat wil zeggen, zij zijn degenen die de betekenis van een bepaalde factor bepalen. De index k geeft het totale aantal van dezelfde factoren aan.

Analyse resultaten analyse

De resultaten van de regressieanalyse worden weergegeven in de vorm van een tabel op de plaats die is opgegeven in de instellingen.

Een van de belangrijkste indicatoren is R-kwadraat. Het geeft de kwaliteit van het model aan. In ons geval is deze coëfficiënt 0,705 of ongeveer 70,5%. Dit is een acceptabel kwaliteitsniveau. Een afhankelijkheid van minder dan 0,5 is slecht.

Een andere belangrijke indicator gelegen in de cel op het snijpunt van de rij met het Y-snijpunt en de kolom Coëfficiënten. Dit geeft aan welke waarde Y zal hebben, en in ons geval is dit het aantal kopers, met alle andere factoren gelijk aan nul. In deze tabel gegeven waarde gelijk aan 58,04.

De waarde op het snijpunt van de kolommen “Variabele X1” en “Coëfficiënten” toont de mate van afhankelijkheid van Y van X. In ons geval is dit de mate van afhankelijkheid van het aantal winkelklanten van de temperatuur. Een coëfficiënt van 1,31 wordt als behoorlijk beschouwd hoog tarief invloed.

Zoals we kunnen zien, gebruiken Microsoft-programma's Excel is vrij eenvoudig om een regressieanalysetabel te maken. Maar alleen een getraind persoon kan met de uitvoergegevens werken en de essentie ervan begrijpen.

Wij zijn blij dat wij u hebben kunnen helpen het probleem op te lossen.

Stel uw vraag in de opmerkingen en beschrijf de essentie van het probleem in detail. Onze specialisten proberen zo snel mogelijk te antwoorden.

Heeft dit artikel je geholpen?

Methode lineaire regressie stelt ons in staat een rechte lijn te beschrijven die het beste past bij een reeks geordende paren (x, y). De vergelijking voor een rechte lijn, bekend als de lineaire vergelijking, wordt hieronder gegeven:

ŷ - verwachte waarde van y bij ingestelde waarde X,

x - onafhankelijke variabele,

a - segment op de y-as voor een rechte lijn,

b is de helling van de rechte lijn.

Onderstaande figuur illustreert dit concept grafisch:

De afbeelding hierboven toont de lijn die wordt beschreven door de vergelijking ŷ =2+0,5x. Het y-snijpunt is het punt waar de lijn de y-as snijdt; in ons geval is a = 2. De helling van de lijn, b, de verhouding tussen de opkomst van de lijn en de lengte van de lijn, heeft een waarde van 0,5. Een positieve helling betekent dat de lijn van links naar rechts stijgt. Als b = 0, is de lijn horizontaal, wat betekent dat er geen verband bestaat tussen de afhankelijke en onafhankelijke variabelen. Met andere woorden: het veranderen van de waarde van x heeft geen invloed op de waarde van y.

ŷ en y worden vaak verward. De grafiek toont 6 geordende puntenparen en een lijn, volgens de gegeven vergelijking

Deze figuur toont het punt dat overeenkomt met het geordende paar x = 2 en y = 4. Merk op dat de verwachte waarde van y volgens de lijn op X= 2 is ŷ. We kunnen dit bevestigen met de volgende vergelijking:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

De y-waarde vertegenwoordigt het werkelijke punt en de ŷ-waarde is de verwachte y-waarde die wordt gebruikt lineaire vergelijking voor een gegeven waarde van x.

De volgende stap is het bepalen van de lineaire vergelijking die het beste past bij de reeks geordende paren. We hebben hierover gesproken in het vorige artikel, waar we de vorm van de vergelijking bepaalden met behulp van de kleinste kwadratenmethode.

Excel gebruiken om lineaire regressie te definiëren

Om de in Excel ingebouwde regressieanalysetool te kunnen gebruiken, moet u de invoegtoepassing activeren Analysepakket. Je vindt het door op het tabblad te klikken Bestand -> Opties(2007+), in het dialoogvenster dat verschijnt OptiesExcel ga naar het tabblad Add-ons. In het veld Controle kiezen Add-onsExcel en klik Gaan. Vink in het venster dat verschijnt het vakje naast aan Analysepakket, klik OK.

Op het tabblad Gegevens in de groep Analyse zal verschijnen nieuwe knop Gegevensanalyse.

Om te demonstreren hoe de invoegtoepassing werkt, gebruiken we gegevens uit een vorig artikel, waarin een man en een meisje een tafel delen in de badkamer. Voer de gegevens uit ons badkuipvoorbeeld in de kolommen A en B van het blanco vel in.

Ga naar het tabblad Gegevens, in de groep Analyse klik Gegevensanalyse. In het venster dat verschijnt Gegevensanalyse selecteren Regressie zoals weergegeven in de afbeelding en klik op OK.

Stel de noodzakelijke regressieparameters in het venster in Regressie zoals weergegeven in de afbeelding:

Klik OK. In onderstaande figuur zijn de verkregen resultaten weergegeven:

Deze resultaten komen overeen met de resultaten die we hebben verkregen door onze eigen berekeningen in het vorige artikel uit te voeren.

Regressieanalyse wel statistische methode onderzoek waarmee je de afhankelijkheid van een bepaalde parameter van een of meer onafhankelijke variabelen kunt aantonen. In het pre-computertijdperk was het gebruik ervan behoorlijk moeilijk, vooral als het om computers ging grote volumes gegevens. Nu u hebt geleerd hoe u regressie in Excel kunt opbouwen, kunt u complexe statistische problemen in slechts een paar minuten oplossen. Hieronder staan specifieke voorbeelden uit het vakgebied economie.

Soorten regressie

Dit concept zelf werd in 1886 door Francis Galton in de wiskunde geïntroduceerd. Regressie vindt plaats:

lineair;
parabolisch;
kalmeren;
exponentieel;
hyperbolisch;
demonstratief;
logaritmisch.

Voorbeeld 1

Laten we eens kijken naar het probleem van het bepalen van de afhankelijkheid van het aantal teamleden dat stopt met het gemiddelde salaris bij zes industriële ondernemingen.

Taak. Bij zes ondernemingen is het gemiddelde maandsalaris en het aantal werknemers dat als gevolg daarvan ontslag neemt naar believen. In tabelvorm hebben we:

Voor de taak om de afhankelijkheid van het aantal vertrekkende werknemers van het gemiddelde salaris bij zes ondernemingen te bepalen, heeft het regressiemodel de vorm van de vergelijking Y = a0 + a1×1 +…+аkxk, waarbij хi de beïnvloedende variabelen zijn, ai zijn de regressiecoëfficiënten, en k is het aantal factoren.

Voor deze taak is Y de indicator van werknemers die stoppen, en de beïnvloedende factor is het salaris, dat we aangeven met X.

De mogelijkheden van de Excel-spreadsheetprocessor gebruiken

Regressieanalyse in Excel moet worden voorafgegaan door ingebouwde functies toe te passen op bestaande tabelgegevens. Voor deze doeleinden is het echter beter om de zeer nuttige add-on “Analysis Pack” te gebruiken. Om het te activeren heb je nodig:

ga vanaf het tabblad “Bestand” naar het gedeelte “Opties”;
selecteer in het geopende venster de regel “Add-ons”;
klik op de knop ‘Ga’ hieronder, rechts van de regel ‘Beheer’;
vink het vakje naast de naam “Analysepakket” aan en bevestig uw acties door op “Ok” te klikken.

Als alles correct is gedaan, ziet u aan de rechterkant van het tabblad 'Gegevens', boven het werkblad 'Excel' gewenste knop.

Lineaire regressie in Excel

Nu we alle noodzakelijke virtuele hulpmiddelen bij de hand hebben om econometrische berekeningen uit te voeren, kunnen we beginnen met het oplossen van ons probleem. Om dit te doen:

Klik op de knop “Gegevensanalyse”;
klik in het geopende venster op de knop "Regressie";
voer op het tabblad dat verschijnt het waardenbereik in voor Y (het aantal vertrekkende werknemers) en voor X (hun salarissen);
We bevestigen onze acties door op de knop “Ok” te drukken.

Hierdoor vult het programma zich automatisch in nieuw blad tafelverwerker regressieanalysegegevens. Let op! In Excel kunt u hiervoor handmatig de gewenste locatie instellen. Het kan bijvoorbeeld hetzelfde blad zijn waar de Y- en X-waarden zich bevinden, of zelfs nieuw boek, speciaal ontworpen voor het opslaan van dergelijke gegevens.

Analyse van regressieresultaten voor R-kwadraat

IN Excel-gegevens verkregen tijdens de verwerking van de gegevens van het beschouwde voorbeeld hebben de vorm:

Allereerst moet u letten op de R-kwadraatwaarde. Het vertegenwoordigt de determinatiecoëfficiënt. IN in dit voorbeeld R-kwadraat = 0,755 (75,5%), dat wil zeggen dat de berekende parameters van het model de relatie tussen de beschouwde parameters met 75,5% verklaren. Hoe hoger de waarde van de determinatiecoëfficiënt, hoe geschikter het geselecteerde model geacht wordt specifieke taak. Er wordt aangenomen dat het de werkelijke situatie correct beschrijft wanneer de R-kwadraatwaarde hoger is dan 0,8. Als R-kwadraat tcr is, wordt de hypothese over de onbeduidendheid van de vrije term van de lineaire vergelijking verworpen.

In het probleem dat voor de vrije term wordt overwogen, werd met behulp van Excel-hulpmiddelen verkregen dat t = 169,20903 en p = 2,89E-12, dat wil zeggen dat we geen enkele kans hebben dat de juiste hypothese over de onbeduidendheid van de vrije term zal worden verworpen. . Voor de coëfficiënt voor het onbekende t=5,79405 en p=0,001158. Met andere woorden: de kans dat de juiste hypothese over de onbeduidendheid van de coëfficiënt voor een onbekende wordt verworpen, is 0,12%.

Er kan dus worden beargumenteerd dat de resulterende lineaire regressievergelijking adequaat is.

Het probleem van de haalbaarheid van de aankoop van een blok aandelen

Meervoudige regressie in Excel wordt uitgevoerd met dezelfde tool voor gegevensanalyse. Laten we een specifiek toepassingsprobleem bekijken.

Het management van het NNN-bedrijf moet beslissen of het opportuun is om een belang van 20% in MMM JSC te kopen. De kosten van het pakket (SP) bedragen 70 miljoen dollar. NNN-specialisten hebben gegevens verzameld over soortgelijke transacties. Er werd besloten om de waarde van het aandeelhouderschap te evalueren op basis van parameters, uitgedrukt in miljoenen Amerikaanse dollars, als:

crediteuren (VK);
jaarlijks omzetvolume (VO);
debiteuren (VD);
kosten van vaste activa (COF).

Daarnaast wordt de parameter van de loonachterstanden van de onderneming (V3 P) in duizenden Amerikaanse dollars gebruikt.

Oplossing met behulp van Excel-spreadsheetprocessor

Allereerst moet u een tabel met brongegevens maken. Het ziet er zo uit:

roep het venster "Gegevensanalyse" op;
selecteer het gedeelte "Regressie";
Voer in het vak “Invoerinterval Y” het bereik van waarden in van de afhankelijke variabelen uit kolom G;
klik op het rode pijlpictogram rechts van het venster "Invoerbereik X" en markeer op het blad het bereik van alle waarden vanaf kolommen B,C,D,F.

Markeer het item "Nieuw werkblad" en klik op "Ok".

Verkrijg een regressieanalyse voor een bepaald probleem.

Studie van resultaten en conclusies

We “verzamelen” de afgeronde gegevens die hierboven op het tabelblad zijn weergegeven Excel-processor, regressievergelijking:

SP = 0,103*SOF + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.

In een meer bekende wiskundige vorm kan het worden geschreven als:

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

Gegevens voor MMM JSC worden weergegeven in de tabel:

Als we ze in de regressievergelijking invullen, krijgen we een bedrag van 64,72 miljoen dollar. Dit betekent dat de aandelen van MMM JSC niet de moeite waard zijn om te kopen, aangezien hun waarde van 70 miljoen dollar behoorlijk overdreven is.

Zoals u kunt zien, maakte het gebruik van de Excel-spreadsheetprocessor en de regressievergelijking het mogelijk om een weloverwogen beslissing te nemen over de haalbaarheid van een zeer specifieke transactie.

Nu weet je wat regressie is. De hierboven besproken Excel-voorbeelden helpen u bij het beslissen praktische problemen uit het vakgebied van de econometrie.

Regressieanalyse is een statistische onderzoeksmethode waarmee je de afhankelijkheid van een bepaalde parameter van een of meer onafhankelijke variabelen kunt aantonen. In het pre-computertijdperk was het gebruik ervan behoorlijk moeilijk, vooral als het om grote hoeveelheden gegevens ging. Nu u hebt geleerd hoe u regressie in Excel kunt opbouwen, kunt u complexe statistische problemen in slechts een paar minuten oplossen. Hieronder staan specifieke voorbeelden uit het vakgebied economie.

Soorten regressie

Dit concept zelf werd in 1886 in de wiskunde geïntroduceerd. Regressie vindt plaats:

lineair;
parabolisch;
kalmeren;
exponentieel;
hyperbolisch;
demonstratief;
logaritmisch.

Voorbeeld 1

Laten we eens kijken naar het probleem van het bepalen van de afhankelijkheid van het aantal teamleden dat stopt met het gemiddelde salaris bij zes industriële ondernemingen.

Taak. Bij zes ondernemingen werd het gemiddelde maandsalaris en het aantal werknemers dat vrijwillig stopte geanalyseerd. In tabelvorm hebben we:


		Aantal mensen dat stopt	Salaris
			30.000 roebel
			35.000 roebel
			40.000 roebel
			45.000 roebel
			50.000 roebel
			55.000 roebel
			60.000 roebel

Voor de taak om de afhankelijkheid van het aantal vertrekkende werknemers van het gemiddelde salaris bij zes ondernemingen te bepalen, heeft het regressiemodel de vorm van de vergelijking Y = a 0 + a 1 x 1 +...+a k x k, waarbij x i de beïnvloedende variabelen, a i zijn de regressiecoëfficiënten, en k is het aantal factoren.

Voor dit probleem is Y de indicator van werknemers die stoppen, en de beïnvloedende factor is het salaris, dat we aangeven met X.

De mogelijkheden van de Excel-spreadsheetprocessor gebruiken

ga vanaf het tabblad “Bestand” naar het gedeelte “Opties”;
selecteer in het geopende venster de regel “Add-ons”;
klik op de knop ‘Ga’ hieronder, rechts van de regel ‘Beheer’;
vink het vakje naast de naam “Analysepakket” aan en bevestig uw acties door op “Ok” te klikken.

Als alles correct is gedaan, verschijnt de vereiste knop aan de rechterkant van het tabblad 'Gegevens', boven het Excel-werkblad.

in Excel

Nu we alle noodzakelijke virtuele hulpmiddelen bij de hand hebben om econometrische berekeningen uit te voeren, kunnen we beginnen met het oplossen van ons probleem. Om dit te doen:

Klik op de knop “Gegevensanalyse”;
klik in het geopende venster op de knop "Regressie";
voer op het tabblad dat verschijnt het waardenbereik in voor Y (het aantal vertrekkende werknemers) en voor X (hun salarissen);
We bevestigen onze acties door op de knop “Ok” te drukken.

Als gevolg hiervan vult het programma automatisch een nieuw spreadsheet met regressieanalysegegevens. Let op! In Excel kunt u hiervoor handmatig de gewenste locatie instellen. Dit kan bijvoorbeeld hetzelfde blad zijn waar de Y- en X-waarden zich bevinden, of zelfs een nieuwe werkmap die speciaal is ontworpen om dergelijke gegevens op te slaan.

Analyse van regressieresultaten voor R-kwadraat

In Excel hebben de gegevens die zijn verkregen tijdens de verwerking van de gegevens in het beschouwde voorbeeld de vorm:

Allereerst moet u letten op de R-kwadraatwaarde. Het vertegenwoordigt de determinatiecoëfficiënt. In dit voorbeeld is R-kwadraat = 0,755 (75,5%), dat wil zeggen dat de berekende parameters van het model de relatie tussen de beschouwde parameters met 75,5% verklaren. Hoe hoger de waarde van de determinatiecoëfficiënt, hoe geschikter het geselecteerde model is voor een specifieke taak. Er wordt aangenomen dat het de werkelijke situatie correct beschrijft wanneer de R-kwadraatwaarde hoger is dan 0,8. Als R-kwadraat<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Kansanalyse

Het getal 64,1428 laat zien wat de waarde van Y zal zijn als alle variabelen xi in het model dat we overwegen op nul worden gezet. Met andere woorden, er kan worden beargumenteerd dat de waarde van de geanalyseerde parameter ook wordt beïnvloed door andere factoren die niet in een specifiek model worden beschreven.

De volgende coëfficiënt -0,16285, gelegen in cel B18, toont het gewicht van de invloed van variabele X op Y. Dit betekent dat het gemiddelde maandsalaris van werknemers binnen het beschouwde model van invloed is op het aantal stoppers met een gewicht van -0,16285, d.w.z. de mate van zijn invloed is volkomen klein. Het teken "-" geeft aan dat de coëfficiënt negatief is. Dit ligt voor de hand, aangezien iedereen weet dat hoe hoger het salaris bij de onderneming, hoe minder mensen de wens uiten om de arbeidsovereenkomst te beëindigen of te stoppen.

Meerdere regressie

Deze term verwijst naar een relatievergelijking met verschillende onafhankelijke variabelen van de vorm:

y=f(x 1 +x 2 +…x m) + ε, waarbij y het resulterende kenmerk is (afhankelijke variabele), en x 1, x 2,…x m factorkenmerken zijn (onafhankelijke variabelen).

Parameterschatting

Voor meerdere regressie(MR) het wordt uitgevoerd met behulp van de kleinste kwadratenmethode (OLS). Voor lineaire vergelijkingen van de vorm Y = a + b 1 x 1 +…+b m x m + ε construeren we een stelsel van normaalvergelijkingen (zie hieronder)

Om het principe van de methode te begrijpen, moeten we een geval met twee factoren overwegen. Dan hebben we een situatie beschreven door de formule

Vanaf hier krijgen we:

waarbij σ de variantie is van het overeenkomstige kenmerk dat in de index wordt weerspiegeld.

OLS is toepasbaar op de MR-vergelijking op een gestandaardiseerde schaal. In dit geval krijgen we de vergelijking:

waarin t y, t x 1, … t xm gestandaardiseerde variabelen zijn, waarvoor de gemiddelde waarden gelijk zijn aan 0; β i zijn de gestandaardiseerde regressiecoëfficiënten en de standaardafwijking is 1.

Houd er rekening mee dat alle β i in dit geval zijn gespecificeerd als genormaliseerd en gecentraliseerd, daarom wordt hun vergelijking met elkaar als correct en acceptabel beschouwd. Bovendien is het gebruikelijk om factoren uit te sluiten door de factoren met de laagste βi-waarden weg te laten.

Probleem met het gebruik van lineaire regressievergelijking

Stel dat we een tabel hebben met de prijsdynamiek voor een specifiek product N van de afgelopen acht maanden. Het is noodzakelijk om een beslissing te nemen over de wenselijkheid van de aankoop van een partij ervan tegen een prijs van 1850 roebel/ton.


maand nummer	maand naam	productprijs N
		1750 roebel per ton
		1755 roebel per ton
		1767 roebel per ton
		1760 roebel per ton
		1770 roebel per ton
		1790 roebel per ton
		1810 roebel per ton
		1840 roebel per ton

Om dit probleem in de Excel-spreadsheetprocessor op te lossen, moet u de tool "Data-analyse" gebruiken, al bekend uit het bovenstaande voorbeeld. Selecteer vervolgens het gedeelte 'Regressie' en stel de parameters in. Houd er rekening mee dat in het veld "Invoerinterval Y" een reeks waarden moet worden ingevoerd voor de afhankelijke variabele (in dit geval de prijzen voor goederen in specifieke maanden van het jaar), en in het veld "Invoerinterval X". - voor de onafhankelijke variabele (maandnummer). Bevestig de actie door op “Ok” te klikken. Op een nieuw blad (indien aangegeven) verkrijgen we gegevens voor regressie.

Met behulp daarvan construeren we een lineaire vergelijking van de vorm y=ax+b, waarbij de parameters a en b de coëfficiënten zijn van de lijn met de naam van het maandnummer en de coëfficiënten en lijnen “Y-kruispunt” van het blad met de resultaten van de regressieanalyse. De lineaire regressievergelijking (LR) voor taak 3 wordt dus geschreven als:

Productprijs N = 11.714* maandnummer + 1727.54.

of in algebraïsche notatie

y = 11,714 x + 1727,54

Analyse van resultaten

Om te beslissen of de resulterende lineaire regressievergelijking adequaat is, worden de coëfficiënten van meervoudige correlatie (MCC) en bepaling gebruikt, evenals de Fisher-test en de Student t-test. In het Excel-spreadsheet met regressieresultaten worden ze respectievelijk meerdere R-, R-kwadraat-, F-statistiek en t-statistiek genoemd.

KMC R maakt het mogelijk om de nauwheid van de probabilistische relatie tussen de onafhankelijke en afhankelijke variabelen te beoordelen. De hoge waarde duidt op een vrij sterk verband tussen de variabelen "Aantal maanden" en "Prijs van product N in roebel per 1 ton". De aard van deze relatie blijft echter onbekend.

Het kwadraat van de determinatiecoëfficiënt R2 (RI) is een numeriek kenmerk van het aandeel van de totale spreiding en toont de spreiding van welk deel van de experimentele gegevens, d.w.z. waarden van de afhankelijke variabele komen overeen met de lineaire regressievergelijking. In het onderhavige probleem is deze waarde gelijk aan 84,8%, dat wil zeggen dat statistische gegevens met een hoge mate van nauwkeurigheid worden beschreven door de resulterende SD.

De F-statistiek, ook wel Fisher's test genoemd, wordt gebruikt om de significantie te evalueren lineaire afhankelijkheid, die de hypothese van het bestaan ervan weerlegt of bevestigt.

(Studententest) helpt bij het evalueren van de significantie van de coëfficiënt met een onbekende of vrije term van de lineaire relatie. Als de waarde van de t-toets > tcr, wordt de hypothese over de onbeduidendheid van de vrije term van de lineaire vergelijking verworpen.

Er kan dus worden beargumenteerd dat de resulterende lineaire regressievergelijking adequaat is.

Het probleem van de haalbaarheid van de aankoop van een blok aandelen

Meervoudige regressie in Excel wordt uitgevoerd met dezelfde tool voor gegevensanalyse. Laten we een specifiek toepassingsprobleem bekijken.

crediteuren (VK);
jaarlijks omzetvolume (VO);
debiteuren (VD);
kosten van vaste activa (COF).

Daarnaast wordt de parameter van de loonachterstanden van de onderneming (V3 P) in duizenden Amerikaanse dollars gebruikt.

Oplossing met behulp van Excel-spreadsheetprocessor

Allereerst moet u een tabel met brongegevens maken. Het ziet er zo uit:

roep het venster "Gegevensanalyse" op;
selecteer het gedeelte "Regressie";
Voer in het vak “Invoerinterval Y” het bereik van waarden in van de afhankelijke variabelen uit kolom G;
Klik op het pictogram met een rode pijl rechts van het venster "Invoerinterval X" en markeer het bereik van alle waarden uit de kolommen B, C, D en F op het blad.

Markeer het item "Nieuw werkblad" en klik op "Ok".

Verkrijg een regressieanalyse voor een bepaald probleem.

Studie van resultaten en conclusies

We “verzamelen” de regressievergelijking uit de afgeronde gegevens die hierboven in het Excel-spreadsheet zijn weergegeven:

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

In een meer bekende wiskundige vorm kan het worden geschreven als:

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

Gegevens voor MMM JSC worden weergegeven in de tabel:

Nu weet je wat regressie is. Met de hierboven besproken Excel-voorbeelden kunt u praktische problemen op het gebied van de econometrie oplossen.

Vereist een add-on om te werken Analysepakket, die moet worden ingeschakeld in het menu-item Service\Add-ons

Om het analysepakket in Excel 2007 in te schakelen, klikt u op Ga naar blokkeren Excel-opties door op de knop aan de linkerkant te drukken bovenste hoek en vervolgens de knop Excel-opties"onderaan het venster:

Om een regressiemodel te bouwen, moet u het item selecteren Service\Gegevensanalyse\Regressie. (In Excel 2007 bevindt deze modus zich in het blok Gegevens/gegevensanalyse/regressie). Er verschijnt een dialoogvenster dat u moet invullen:

1) Invoerinterval Y¾ bevat een link naar cellen die de waarden van het resulterende kenmerk bevatten j. De waarden moeten in een kolom worden gerangschikt;

2) Invoerinterval X¾ bevat een link naar cellen die factorwaarden bevatten. De waarden moeten in kolommen worden gerangschikt;

3) Teken Labels wordt ingesteld als de eerste cellen bevatten verklarende tekst(gegevenshandtekeningen);

4) Betrouwbaarheidsniveau¾ is het betrouwbaarheidsniveau, dat standaard als 95% wordt beschouwd. Als u niet tevreden bent met deze waarde, moet u deze vlag inschakelen en de vereiste waarde invoeren;

5) Teken Constant-nul wordt opgenomen als het nodig is een vergelijking te construeren waarin de vrije variabele is;

6) Uitvoeropties bepalen waar de resultaten moeten worden geplaatst. Standaard bouwmodus Nieuw werkblad;

7) Blokkeren Restjes Hiermee kunt u de uitvoer van residuen en de constructie van hun grafieken opnemen.

Het gevolg is dat informatie alles bevat noodzakelijke informatie en gegroepeerd in drie blokken: Regressiestatistieken, Analyse van variantie, Opname van het saldo. Laten we ze eens nader bekijken.

1. Regressiestatistieken:

meerdere R wordt bepaald door de formule ( Pearson-correlatiecoëfficiënt);

R (determinatiecoëfficiënt);

Genormaliseerd R-vierkant wordt berekend met behulp van de formule (gebruikt voor meervoudige regressie);

Standaard fout S berekend met de formule ;

Waarnemingen ¾ is de hoeveelheid gegevens N.

2. Analyse van variantie, lijn Regressie:

Parameter df gelijk aan M(aantal factorensets X);

Parameter SS wordt bepaald door de formule;

Parameter MEVROUW wordt bepaald door de formule;

Statistieken F wordt bepaald door de formule;

Betekenis F. Als het resulterende getal groter is dan , wordt de hypothese geaccepteerd (er is geen lineair verband), anders wordt de hypothese geaccepteerd (er is een lineair verband).

3. Analyse van variantie, lijn Rest:

Parameter df gelijk aan ;

Parameter SS wordt bepaald door de formule ;

Parameter MEVROUW wordt bepaald door de formule.

4. Analyse van variantie, lijn Totaal bevat de som van de eerste twee kolommen.

5. Analyse van variantie, lijn Y-kruising bevat de coëfficiënt, standaardfout en T-statistieken.

P-waarde ¾ is de waarde van de significantieniveaus die overeenkomen met de berekende T-statistici. Bepaald door de functie STUDIST( T-statistieken; ). Als P-waarde groter is dan , dan is de corresponderende variabele statistisch niet significant en kan deze uit het model worden uitgesloten.

Bodem 95% En Top 95%¾ zijn de onder- en bovengrenzen van de 95 procent betrouwbaarheidsintervallen voor de coëfficiënten van de theoretische lineaire regressievergelijking. Als de betrouwbain het gegevensinvoerblok op de standaardwaarde werd gelaten, zullen de laatste twee kolommen de vorige dupliceren. Als de gebruiker zijn eigen betrouwbaarheidswaarde heeft ingevoerd, bevatten de laatste twee kolommen de onder- en bovengrenswaarden voor het opgegeven betrouwbaarheidsniveau.

6. Analyse van variantie, de lijnen bevatten de coëfficiëntwaarden, standaard fouten, T-statisticus, P-waarden en betrouwbaarheidsintervallen voor de overeenkomstige .

7. Blokkeren Opname van het saldo bevat de voorspelde waarden j(in onze notatie is dit ) en residuen .

De verandering in de resulterende karakteristiek y is het gevolg van de variatie in de factorkarakteristiek x. Het aandeel van de variantie dat wordt verklaard door regressie in de totale variantie van het resulterende kenmerk karakteriseert de determinatiecoëfficiënt R 2. Voor een lineair verband is de determinatiecoëfficiënt gelijk aan het kwadraat van de correlatiecoëfficiënt:

R 2 = r xy 2, waarbij r xy de correlatiecoëfficiënt is.

De waarde van R 2 = 0,83 betekent bijvoorbeeld dat in 83% van de gevallen veranderingen in x leiden tot veranderingen in y. Met andere woorden: de nauwkeurigheid van het selecteren van de regressievergelijking is hoog.

Berekend om de kwaliteit van het passen van de regressievergelijking te beoordelen. Voor acceptabele modellen wordt aangenomen dat de determinatiecoëfficiënt groter moet zijn dan 50%. Modellen met een determinatiecoëfficiënt boven de 80% kunnen als redelijk goed worden beschouwd. De waarde van de determinatiecoëfficiënt R 2 = 1 betekent functionele afhankelijkheid tussen variabelen.

In het geval dat niet-lineaire regressie met deze rekenmachine wordt de determinatiecoëfficiënt berekend. Bij meervoudige regressie kan de determinatiecoëfficiënt worden gevonden via de dienst Meervoudige regressie
Over het algemeen wordt de determinatiecoëfficiënt gevonden met de formule: of
Regel voor het optellen van varianties:
,
waar is de totale som van de gekwadrateerde afwijkingen;
- de som van de gekwadrateerde afwijkingen als gevolg van regressie (“verklaard” of “factorieel”);
- resterende som van gekwadrateerde afwijkingen.

Met deze online calculator kunt u berekenen determinatiecoëfficiënt en de betekenis ervan wordt gecontroleerd (voorbeeldoplossing).

Instructies. Geef de hoeveelheid invoergegevens op. De resulterende oplossing wordt opgeslagen in Word-bestand. Er wordt ook automatisch een sjabloon gemaakt voor het testen van de oplossing in Excel.

Met het MS Excel-pakket kunt u het meeste werk zeer snel doen bij het opstellen van een lineaire regressievergelijking. Het is belangrijk om te begrijpen hoe de verkregen resultaten moeten worden geïnterpreteerd. Om een regressiemodel te bouwen, moet u Extra\Gegevensanalyse\Regressie selecteren (in Excel 2007 bevindt deze modus zich in het blok Gegevens/Gegevensanalyse/Regressie). Kopieer vervolgens de resultaten naar een blok voor analyse.