Regressie is een Analyse Tool, die we gebruiken voor het analyseren van grote hoeveelheden data en het maken van prognoses en voorspellingen in Microsoft Excel.
Wil je de toekomst voorspellen? Nee, we gaan geen astrologie leren. We houden van cijfers en we zullen vandaag regressieanalyse in Excel leren.
Om toekomstige schattingen te voorspellen, bestuderen we:
- REGRESSIE-ANALYSE MET BEHULP VAN EXCEL-FUNCTIES (HANDMATIGE REGRESSIE VINDEN)
- REGRESSIE-ANALYSE MET BEHULP VAN EXCEL'S ANALYSE TOOLPAK ADD-IN
- REGRESSIEKAART IN EXCEL
Laten we het doen…
Scenario:
Stel dat u frisdrank verkoopt. Hoe gaaf is het als je kunt voorspellen:
- Hoeveel frisdranken zullen er volgend jaar worden verkocht op basis van de gegevens van vorig jaar?
- Welke velden moeten worden gefocust?
- En hoe kunt u uw omzet verhogen door uw strategie te wijzigen?
Het zal winstgevend geweldig zijn. Toch?… Ik weet het. Dus laten we beginnen.
Je hebt 11 records van verkopers en verkochte frisdranken.
Op basis van deze gegevens wilt u nu het aantal verkopers voorspellen dat nodig is om 2000 verkopen van frisdranken te realiseren.
De regressievergelijking is een hulpmiddel om zulke nauwkeurige schattingen te maken. Om dit te doen, moeten we eerst Regressie kennen.
REGRESSIE-ANALYSE MET BEHULP VAN EXCEL-FUNCTIES (HANDMATIGE REGRESSIE VINDEN)
Dit deel zorgt ervoor dat u regressie beter begrijpt dan alleen de Excel-regressieprocedure te vertellen.
Invoering:
Eenvoudige lineaire regressie:
De studie van de relatie tussen twee variabelen wordt Simple Linear Regression genoemd. Waar de ene variabele afhangt van de andere onafhankelijke variabele. De afhankelijke variabele wordt vaak genoemd met namen als Driven, Response en Target-variabele. En de onafhankelijke variabele wordt vaak uitgesproken als een Driving, Predictor of gewoon Independent variabele. Deze namen beschrijven ze duidelijk.
Laten we dit nu vergelijken met uw scenario. U wilt het aantal verkopers weten dat nodig is om te bereiken 2000 verkopen. Dus hier is de afhankelijke variabele het aantal verkopers en de onafhankelijke variabele is frisdrank.
De onafhankelijke variabele wordt meestal aangeduid als x en afhankelijke variabele als j.
In ons geval worden frisdranken verkocht x en het aantal verkopers is j.
Als we willen weten hoeveel frisdranken er worden verkocht als we aanwijzen 200 verkopers, dan is het scenario omgekeerd.
Verder gaan.
De "eenvoudige" wiskunde van lineaire regressievergelijking:
Nou, het is niet eenvoudig. Maar Excel maakte het eenvoudig om te doen.
We moeten het vereiste aantal verkopers voor alle 11 gevallen voorspellen om de 12e voorspelling te krijgen.
Laten we zeggen:
Frisdrank Verkocht is x
Het nummer van verkopers is ja
de voorspelde ja (aantal verkopers) ook wel genoemd Regressievergelijking, zou zijn
| x*Helling+Intercept (ontspan, ik heb het gedekt) |
Nu vraag je je vast af waar de stat krijg je de helling en onderschep je. Maak je geen zorgen, Excel heeft functies voor hen. U hoeft niet te leren hoe u de helling kunt vinden en handmatig kunt onderscheppen.
Als je wilt, maak ik daar een aparte tutorial voor. Laat het me weten in het opmerkingengedeelte. Dit zijn enkele belangrijke tools voor gegevensanalyse.
Laten we nu in onze berekening stappen:
Stap 1: Bereid deze kleine tafel voor
Stap 2: Vind de helling van de regressielijn
Excel-functie voor hellingen is
| =HELLING(bekende_y's,bekende_x's) |
Je bekende_y's zijn binnen bereik B2:B12 en bekende_x's zijn binnen bereik C2:C12
in cel B16, schrijf de formule hieronder
| =HELLING(B2:B12, C2:C12) |
(Opmerking: helling wordt ook wel coëfficiënt van x genoemd in de regressievergelijking)
Je zult krijgen 0.058409. Rond af op 2 decimalen en je krijgt 0.06.
Stap 3: Vind het snijpunt van de regressielijn
Excel-functie voor het onderscheppen is
| =INTERCEPT(bekende_y's, bekende_x's) |
We weten wat onze bekende x's en y's
in cel B17, schrijf deze formule op
| =INTERCEPT(B2:B12, C2:C12) |
U krijgt een waarde van -1.1118969. Afronden op 2 decimale cijfers. Je zult krijgen -1.11.
Onze lineaire regressievergelijking is = x*0,06 + (-1,11). Nu kunnen we mogelijke y gemakkelijk voorspellen, afhankelijk van het doel x.
Stap 4: Schrijf in D2 de onderstaande formule
| =C2*$B$16+$B$17(Regressievergelijking) |
U krijgt een waarde van 13.55.
Selecteer D2 tot D13 en druk op CTRL+D om de formule in het bereik in te vullen D2:D13
in cel D13 je hebt je vereiste aantal verkopers.
| Om de doelstelling van 2000 Frisdrankverkoop, je hebt een schatting nodig van 115,71 verkopers of zeg maar 116 omdat het illegaal is om mensen in stukken te snijden. |
Als u dit nu gebruikt, kunt u eenvoudig What-If-analyses uitvoeren in Excel. Verander gewoon het aantal verkopen en u zult zien dat veel verkopers nodig zullen zijn om dat verkoopdoel te bereiken.
Speel eromheen om erachter te komen:
Hoeveel personeel heb je nodig om de verkoop te verhogen?
Hoeveel verkopen zullen toenemen als u uw verkopers verhoogt?
Maak uw schatting betrouwbaarder:
Nu weet je dat je 116 verkopers nodig hebt om 2000 verkopen gedaan te krijgen.
In analytics wordt niets zomaar gezegd en geloofd. U moet een betrouwbaarheidspercentage op uw schatting geven. Het is als het geven van een certificaat van uw vergelijking.
Correlatiecoëfficiënt formule:
Het volgende dat u wordt gevraagd, is in hoeverre deze twee variabelen gerelateerd zijn. In statische termen moet u de correlatiecoëfficiënt vertellen.
Excel-functie voor correlatie is
| =CORREL(matrix1, matrix2) |
In uw geval zijn de bekende_x's en Know_y's respectievelijk array1 en array2.
Voer in B18 deze formule in
| =CORREL((B2:B12, C2:C12) |
je zult hebben 0.919090. Formatteer cel B2 in het percentage. Nu hebben 92% van correlatie.
Nu, wat dit? 92% middelen. Het betekent, daar 92% van verkoopkansen neemt toe als u het aantal verkopers verhoogt en 92% van de omzet daalt als u het aantal verkopers vermindert. Het heet Positieve correlatiecoëfficiënt.
R schildknaap (R^2) :
R Squire-waarde vertelt u met welk percentage uw regressievergelijking geen toevalstreffer is. Hoeveel het juist is op basis van de verstrekte gegevens.
De Excel-functie voor R schildknaap is RSQ.
| RSQ(bekende_y's, Bekende_x's) |
In ons geval krijgen we de R-squire-waarde in cel B19.
Voer in B19 deze formule in
| =RSQ(B2:B12, C2:C12) |
Dus we hebben 84% van de r Square-waarde. Dat is een zeer goede verklaring voor onze regressie. Er staat dat 84% van onze data niet toevallig is. Y (aantal verkopers) is sterk afhankelijk van X (verkoop van frisdranken).
Er zijn veel andere tests die we op deze gegevens kunnen doen om onze regressie te verzekeren. Maar handmatig wordt het een complexe en langdurige procedure. Daarom biedt Excel Analysis Toolpak aan. Met behulp van deze tool kunnen we deze regressieanalyse in seconden uitvoeren.
REGRESSIE IN EXCEL MET BEHULP VAN EXCEL'S ANALYSE TOOLPAK ADD-IN
Als je al weet wat regressievergelijkingen zijn, en je wilt gewoon snel je resultaten, dan is dit deel iets voor jou. Maar als u regressievergelijkingen gemakkelijk wilt begrijpen, scrol dan omhoog naar REGRESSIE-ANALYSE MET EXCEL-FUNCTIES (HANDMATIGE REGRESSIE VINDEN).
Excel biedt een hele reeks tools voor analyse in zijn Analysis Toolpak. Standaard is deze niet beschikbaar op het tabblad Gegevens. Je moet het toevoegen. Dus laten we het eerst toevoegen.
Analyse Toolpak toevoegen aan Excel 2016
Als u niet weet waar de gegevensanalyse in Excel is, volgt u deze stappen:
Stap 1: Ga naar Excel-opties: Bestand? Opties? Invoegtoepassingen
Stap 2: Klik op Invoegtoepassingen. U ziet een lijst met beschikbare invoegtoepassingen.
Selecteer Analysis ToolPak en zoek onder in het venster naar beheren. Selecteer in beheren Excel-invoegtoepassingen en klik op GO.
Het venster met invoegtoepassingen wordt geopend. Selecteer hier Analyse ToolPak. Klik vervolgens op de ok-knop.
U hebt nu toegang tot alle functies van ToolPak voor gegevensanalyse vanaf het tabblad Gegevens.
Analyse ToolPak gebruiken voor regressie
Stap 1: Ga naar het tabblad Gegevens, Zoek gegevensanalyse. Klik er dan op.
Er verschijnt een dialoogvenster.
Stap 2: Zoek 'Regressie' in de lijst met analysehulpmiddelen en druk op de knop OK.
de regressie invoervenster verschijnt. U ziet een aantal beschikbare invoeropties. Maar voor nu zullen we ons concentreren op Y Range en X Range, terwijl we al het andere standaard laten.
Stap 4: Voer invoer in:
Aantal verkopers is Y
De verkoop van frisdranken is x
Vandaar
- Y-bereik = B2:B11
En
- X-bereik = C2:C11
Voor het uitvoerbereik heb ik E4 op hetzelfde blad geselecteerd. U kunt een nieuw werkblad selecteren om resultaten te krijgen op een nieuw werkblad in dezelfde werkmap of een compleet nieuwe werkmap. Wanneer u klaar bent met uw invoer, drukt u op de knop OK.
Resultaten:
U krijgt een verscheidenheid aan informatie uit uw gegevens. Raak niet overweldigd. Je hoeft niet alle gerechten op te eten.
We zullen alleen die resultaten behandelen die ons zullen helpen om het vereiste aantal verkopers in te schatten
Stap 5: We kennen de regressievergelijking voor het schatten van ja, dat is
x*Helling+Intercept
We moeten alleen lokaliseren Helling en Onderscheppen bij resultaten.
En hier zijn ze.
De onderscheppingscoëfficiënt wordt duidelijk vermeld.
De helling wordt geschreven als ‘X-variabele 1’, soms ook genoemd als de coëfficiënt van X. Rond ze af en we krijgen -1.11 als onderscheppen en 0,06 als helling.
Stap 6: Uit de resultaten kunnen we de regressievergelijking sturen. En dat zou zijn
=x*(0,06) + (-1,11)
Maak deze tabel in Excel.
Voor nu, x is 2000, dat zich in cel E2 bevindt.
Voer in cel F2 deze formule in
=E2*F21+F20
Je krijgt een resultaat van 115.7052757.
Het naar boven afronden geeft ons 116 van vereiste verkopers.
We hebben dus geleerd hoe we de regressievergelijking handmatig kunnen vormen en met behulp van Analysis ToolPak. Hoe kun je deze vergelijking gebruiken om toekomstige statistieken te schatten?
Laten we nu eens kijken naar de regressie-output die wordt gegeven door Analysis Toolpak.
De regressie-output begrijpen:
Er is geen voordeel als u regressieanalyse uitvoert met behulp van een analysetoolpakket in Excel en de betekenis ervan niet kunt interpreteren.
Samenvatting Sectie:
Zoals de naam al doet vermoeden, is het een samenvatting van de gegevens.
-
- Multiple R: het vertelt hoe de regressievergelijking past bij de gegevens. Dit wordt ook wel de correlatiecoëfficiënt genoemd.
In ons geval is dat 0.919090619 of 0.92 (naar boven afronden). Dit betekent dat er een kans van 92% is op een omzetstijging als we ons aantal verkopers vergroten.
-
- R-kwadraat: het vertelt de betrouwbaarheid van gevonden regressie. Het vertelt ons hoeveel waarnemingen deel uitmaken van onze regressielijn. In ons geval is dit 0,844727566 of 0,85. Het betekent dat onze regressie voor 85% geschikt is.
- Aangepast R-vierkant: Het aangepaste vierkant is gewoon een meer getuigde versie van het R-vierkant. Vooral nuttig bij meervoudige regressieanalyse.
- Standaardfout: Terwijl R. Squire u vertelt hoeveel gegevenspunten in de buurt van de regressielijn vallen, vertelt de standaardfout u hoe ver een gegevenspunt van de regressielijn kan gaan.
In ons geval is dat 6.74.
- Observatie: Dit is gewoon het aantal observaties, dat is 11 in ons voorbeeld.
Anova-sectie:
Deze sectie wordt nauwelijks gebruikt in lineaire regressie.
- ff. Het is een vrijheidsgraad. Het wordt gebruikt bij het handmatig berekenen van regressie.
- SS. Som van de kwadraten. Het is gewoon een som van kwadraten van varianties. Wordt gebruikt om R-squire-waarden te vinden.
- MEVROUW. Dit betekent kwadratische waarde.
- En 5. F en significantie van F. Als de significantie van F (p-waarde van de helling) kleiner is dan de F-test, dan kun je de nulhypothese verwerpen en je hypothese bewijzen. In eenvoudige taal kun je concluderen dat er enig effect is van x op y wanneer het wordt gewijzigd.
In ons geval is F 48,96264 en is de significantie van F 0,000063. Het betekent dat onze regressie bij de gegevens past.
Regressie Sectie:
In deze sectie hebben we de twee belangrijkste waarden voor onze regressievergelijking.
- Onderscheppen: We hebben hier een onderschepping die aangeeft waar x op Y onderschept. Dit is een belangrijk onderdeel van de regressievergelijking. In ons geval is het -1,11.
- X variabele 1 (Helling). Ook wel de coëfficiënt van x genoemd. Het definieert de raaklijn van de regressielijn.
REGRESSIEKAART IN EXCEL
In Excel is het eenvoudig om een regressiegrafiek te plotten. Volg gewoon deze stappen. Volg deze eenvoudige stappen om een regressiegrafiek toe te voegen in Excel 2016, 2013 en 2010.
Stap 1. Zorg dat je bekende x'en in de eerste kolom staan en y's in de tweede.
In ons geval weten we dat Known_x's frisdranken zijn die worden verkocht. En bekende y's zijn verkopers.
Stap 2. Selecteer uw bekende x- en y-bereik.
Stap 3: Ga naar het tabblad Invoegen en klik op het spreidingsdiagram.
U krijgt een grafiek die er als volgt uitziet.
Stap 4. Voeg de trendlijn toe: Ga naar lay-out en zoek de trendlijnoptie in de analysesectie.
Klik onder de optie Trendlijn op Lineaire trendlijn.
Je grafiek ziet er dan zo uit.
Dit is je regressiegrafiek.
Als u nu de onderstaande gegevens toevoegt en de geselecteerde gegevens uitbreidt. U ziet een verandering in uw grafiek.
Voor ons voorbeeld hebben we 2000 toegevoegd aan de Verkochte frisdrank en hebben we de verkopers leeg gelaten. En als we het bereik van de grafiek vergroten, krijgen we dit.
Het zal het vereiste aantal verkopers opleveren voor 2000 verkopen van frisdranken in grafische vorm. Dat is iets minder dan 120 in de grafiek. En uit onze regressievergelijking weten we dat het 116 is.
In dit artikel heb ik geprobeerd alles onder Excel-regressieanalyse te behandelen. Ik heb regressie uitgelegd in Excel 2016. Regressie in Excel 2010 en Excel 2013 is hetzelfde als in Excel 2016.
Gebruik de sectie Opmerkingen voor verdere vragen over dit onderwerp. Stel een vraag, geef een mening of vermeld gewoon mijn grammaticale fouten. Alles is welkom. Aarzel niet om het commentaargedeelte te gebruiken.
Hoe de MODE-functie in Excel te berekenen
Hoe de gemiddelde functie in Excel te berekenen
Een standaarddeviatiegrafiek maken
Beschrijvende statistieken in Microsoft Excel 2016
Hoe Excel NORM.VERD-functie te gebruiken
Hoe de Pareto-grafiek en -analyse te gebruiken?
Populaire artikels:
50 Excel-snelkoppeling om uw productiviteit te verhogen
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken?
Hoe de AANTAL.ALS-functie in Excel 2016 te gebruiken?
Hoe de SUMIF-functie in Excel te gebruiken?