Hoe een regressievergelijking in Excel te vinden Regressieanalyse in excel

Toont de invloed van sommige waarden (onafhankelijk, onafhankelijk) op de afhankelijke variabele. Hoe hangt het aantal economisch actieve bevolkingen bijvoorbeeld af van het aantal ondernemingen, lonen en andere parameters? Of: welke invloed hebben buitenlandse investeringen, energieprijzen etc. op de hoogte van het bbp?

Met het resultaat van de analyse kunt u prioriteiten benadrukken. En op basis van de belangrijkste factoren de ontwikkeling voorspellen en plannen prioritaire gebieden, managementbeslissingen nemen.

Regressie vindt plaats:

lineair (y = a + bx);

· parabolisch (y = a + bx + cx 2);

· exponentieel (y = a * exp(bx));

· macht (y = a*x^b);

· hyperbolisch (y = b/x + a);

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

· exponentieel (y = a * b^x).

Laten we een voorbeeld bekijken van het bouwen van een regressiemodel in Excel en het interpreteren van de resultaten. Laten we het lineaire type regressie nemen.

Taak. Bij 6 ondernemingen zijn het gemiddelde maandsalaris en het aantal uittredende werknemers geanalyseerd. Het is noodzakelijk om de afhankelijkheid van het aantal vertrekkende werknemers van het gemiddelde salaris te bepalen.

Model lineaire regressie Het heeft volgende weergave:

Y = a 0 + a 1 x 1 +…+a k x k.

Waar a regressiecoëfficiënten zijn, zijn x beïnvloedende variabelen, en is k het aantal factoren.

In ons voorbeeld is Y de indicator voor het verlaten van werknemers. De beïnvloedende factor is het loon (x).

Excel heeft ingebouwde functies waarmee u de parameters van een lineair regressiemodel kunt berekenen. Maar de add-on “Analysis Package” zal dit sneller doen.

We activeren een krachtig analytisch hulpmiddel:

1. Klik op de knop "Office" en ga naar het tabblad "Excel-opties". "Add-ons".

2. Onderaan, onder de vervolgkeuzelijst, in het veld "Beheer" staat de inscriptie " Excel-invoegtoepassingen» (als dit er niet is, klikt u op het selectievakje aan de rechterkant en selecteert u). En de ‘Go’-knop. Klik.

3. Er wordt een lijst met beschikbare add-ons geopend. Selecteer “Analysepakket” en klik op OK.

Eenmaal geactiveerd, is de add-on beschikbaar op het tabblad Gegevens.

Laten we nu de regressieanalyse zelf uitvoeren.

1. Open het menu van de tool “Data-analyse”. Selecteer "Regressie".

2. Er wordt een menu geopend waarin u invoerwaarden en uitvoeropties kunt selecteren (waar het resultaat moet worden weergegeven). In de velden voor de initiële gegevens geven we het bereik aan van de beschreven parameter (Y) en de factor die deze beïnvloedt (X). De rest hoeft niet ingevuld te worden.

3. Nadat u op OK hebt geklikt, geeft het programma de berekeningen op een nieuw blad weer (u kunt een interval selecteren om op het huidige blad weer te geven of de uitvoer aan een nieuwe werkmap toewijzen).

Allereerst besteden we aandacht aan R-kwadraat en coëfficiënten.

R-kwadraat is de determinatiecoëfficiënt. In ons voorbeeld – 0,755 of 75,5%. Dit betekent dat de berekende parameters van het model 75,5% van de relatie tussen de bestudeerde parameters verklaren. Hoe hoger de determinatiecoëfficiënt, des te hoger beter kwaliteitsmodel. Goed - boven 0,8. Slecht – minder dan 0,5 (een dergelijke analyse kan nauwelijks als redelijk worden beschouwd). In ons voorbeeld – “niet slecht”.

De coëfficiënt 64,1428 laat zien wat Y zal zijn als alle variabelen in het beschouwde model gelijk zijn aan 0. Dat wil zeggen dat de waarde van de geanalyseerde parameter ook wordt beïnvloed door andere factoren die niet in het model zijn beschreven.

De coëfficiënt -0,16285 toont het gewicht van variabele X op Y. Dat wil zeggen dat het gemiddelde maandsalaris binnen dit model invloed heeft op het aantal opgevers met een gewicht van -0,16285 (dit is een kleine mate van invloed). Het teken "-" geeft dit aan slechte invloed: hoe hoger het salaris, hoe minder mensen stoppen. Dat is eerlijk.

Met de lineaire regressiemethode kunnen we een rechte lijn 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:

ŷ is de verwachte waarde van y at ingestelde waarde X,

x is een onafhankelijke variabele,

a is een 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 waarop 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 set geordende paren. We hebben hierover gesproken in het vorige artikel, waar we het type vergelijking bepaalden door .

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 groep Analyse zal verschijnen nieuwe knop Gegevensanalyse.

Om het werk van de invoegtoepassing te demonstreren, zullen we gegevens gebruiken waarbij 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 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 op de foto:

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 uit te voeren in .

Regressie analyse is een van de meest populaire methoden van statistisch onderzoek. Het kan worden gebruikt om de mate van invloed van onafhankelijke variabelen op de afhankelijke variabele vast te stellen. Op functionaliteit Microsoft Excel 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.

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.

Als we nu naar het tabblad gaan "Gegevens", op het lint in de gereedschapskist "Analyse" we zullen een nieuwe knop zien - "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 weer 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 Y betekent een variabele, de invloed van factoren waarop we proberen te studeren. In ons geval is dit het aantal kopers. Betekenis X zijn de verschillende factoren die een variabele beïnvloeden. Opties A zijn regressiecoëfficiënten. Dat wil zeggen, zij zijn degenen die de betekenis van een bepaalde factor bepalen. Inhoudsopgave k geeft het totale aantal van deze 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-vierkant. 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 in de cel op het snijpunt van de lijn "Y-kruising" en kolom "Kansen". 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.

Waarde op het snijpunt van de grafiek "Variabele X1" En "Kansen" 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.

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.

Regressieanalyse is een van de meest populaire methoden van statistisch onderzoek. Het kan worden gebruikt om de mate van invloed van onafhankelijke variabelen op de afhankelijke variabele vast te stellen. Microsoft Excel beschikt over hulpmiddelen die zijn 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

Ga naar het tabblad "Bestand".
Ga naar het gedeelte 'Instellingen'.
Er gaat een venster open Excel-instellingen. Ga naar de subsectie 'Add-ons'.
Helemaal onderaan het venster dat wordt geopend, verplaatst u de schakelaar in het blok ‘Beheer’ naar de positie ‘Excel Add-ins’, als deze 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 ontdekken hoe weersomstandigheden in de vorm van luchttemperatuur de aanwezigheid van een winkelzaak kunnen 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 deze 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 bevindt zich in de cel op het snijpunt van de rij "Y-kruising" en de kolom "Coëfficiënten". Dit geeft aan welke waarde Y zal hebben, en in ons geval is dit het aantal kopers, terwijl alle andere factoren gelijk zijn aan nul. In deze tabel is deze waarde 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 beschouwd als een vrij hoge invloedsindicator.

Zoals u kunt zien, is het met Microsoft Excel 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?

ŷ - verwachte waarde van y voor een gegeven waarde van 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:

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

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

De y-waarde vertegenwoordigt het werkelijke punt en de ŷ-waarde is de verwachte waarde van y met behulp van een 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

Op het tabblad Gegevens in groep Analyse er verschijnt een 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.

Stel de noodzakelijke regressieparameters in het venster in Regressie, zoals weergegeven op de foto:

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 aan naast de naam “Analysepakket” 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.

Lineaire regressie in Excel

Nu we alle benodigde virtuele hulpmiddelen bij de hand hebben om econometrische berekeningen uit te voeren, kunnen we beginnen met het oplossen van ons probleem. Voor deze:

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. Opmerking! Met 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 Een 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 afhankelijkheid 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 aandelenpakket 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 in 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 het Excel-spreadsheet 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.