Voorspellingen in Excel: 6 bewezen manieren

Prognoses is een zeer belangrijk element van bijna elk gebied van activiteit, variërend van economie en eindigend met engineering. Er zijn een groot aantal software gespecialiseerd in deze richting. Helaas weten niet alle gebruikers dat de gebruikelijke Excel-tabelprocessor in zijn arsenale gereedschappen heeft voor het voorspellen, wat in zijn effectiviteit weinig inferieur is aan professionele programma's. Laten we erachter komen wat voor soort gereedschappen zijn, en hoe je in de praktijk een prognose kunt maken.

Procedure-voorspelling

Het doel van elke voorspelling is om de huidige trend te identificeren, en de definitie van het beoogde resultaat in verband met het object dat op een bepaald punt in de toekomst wordt bestudeerd.

Methode 1: Trendlijn

Een van de meest populaire grafische voorspelling in Excele is een extrapolatie gemaakt door een trendlijn te bouwen.

Laten we proberen het bedrag van de winst van het bedrijf na 3 jaar te voorspellen op basis van gegevens over deze indicator voor de voorgaande 12 jaar.

1. We bouwen een schema op basis van tabelvormige gegevens bestaande uit argumenten en functiewaarden. Om dit te doen, selecteert u het tabelgebied en vervolgens, terwijl u in het tabblad "Invoegen" klikt, klikt u op het pictogram van het gewenste type diagram, dat zich in het "grafiek" -blok bevindt. Kies vervolgens het type dat geschikt is voor een bepaalde situatie. Het is het beste om een ​​puntschema te kiezen. U kunt een andere look kiezen, maar dan worden de gegevens correct weergegeven, moet u met name het argumentelijn bewerken en een andere schaal van de horizontale as selecteren.



2. Nu moeten we een trendlijn bouwen. We maken een klik met de rechtermuisknop op een van de punten van het diagram. In het geactiveerde contextmenu stopt u de selectie bij de paragraaf "Trendlijn toevoegen".



3. Het trendlijnopmaakvenster wordt geopend. Het kan een van de zes soorten benadering kiezen:
   • Lineair;
   • Logaritmic;
   • Exponentieel;
   • Stroom;
   • Polynoom;
   • Lineaire filtratie.

   Laten we eerst een lineaire benadering kiezen.

   In het veld "Voorspelling" instellingen in het veld "Forward to", stellen we het nummer "3.0" in, omdat we een voorspelling voor drie jaar later moeten maken. Bovendien kunt u tikken in de buurt van de instellingen installeren "Toon vergelijking op het diagram" en "Plaats de waarde van de waarde van benadering (R ^ 2)" op het diagram. De laatste indicator geeft de kwaliteit van de trendlijn weer. Nadat de instellingen worden gemaakt, klikt u op de knop "Sluiten".



4. De trendlijn is gebouwd en we kunnen de geschatte hoeveelheid winst in drie jaar bepalen. Zoals we zien, moet ze tegen die tijd vertalen in 4500 duizend roebel. De R2-coëfficiënt, zoals hierboven vermeld, geeft de kwaliteit van de trendlijn weer. In ons geval is de waarde van R2 0,89. Hoe hoger de coëfficiënt, hoe hoger de geloofwaardigheid van de lijn. De maximale waarde ervan kan gelijk zijn aan 1. Er wordt aangenomen dat met de coëfficiënt van meer dan 0,85 de trendlijn betrouwbaar is.



5. Als u niet tevreden bent met het niveau van betrouwbaarheid, kunt u terugkeren naar het venster TREND LIJN INFORMATIE en een ander type benadering selecteren. U kunt alle beschikbare opties proberen om het meest accuraat te vinden.

   

   Opgemerkt moet worden dat een effectieve prognose met behulp van extrapolatie via een trendlijn kan zijn als de voorspellingsperiode niet hoger is dan 30% van de geanalyseerde periode. Dat wil zeggen, bij het analyseren van een periode van 12 jaar, kunnen we geen effectieve voorspelling maken voor meer dan 3-4 jaar. Maar zelfs in dit geval zal het relatief betrouwbaar zijn als er gedurende deze tijd geen overmacht of het tegenovergestelde is van uiterst gunstige omstandigheden die niet in eerdere perioden waren.

Les: Hoe een trendlijn in Excel te bouwen

Methode 2: Voorspellingsoperator

Extrapolatie voor tabelgegevens kan worden gemaakt via het standaardkenmerk van de Excel-voorspelling. Dit argument verwijst naar de categorie Statistische instrumenten en heeft de volgende syntaxis:

= Voorspellen (x; bekende_stations_y; bekende waarden_x)

"X" is een argument, de waarde van de functie waarvoor u moet definiëren. In ons geval, als een argument is het jaar waarop prognoses moeten worden gemaakt.

"Bekende waarden y" - de basis van de bekende functiewaarden. In ons geval, in haar rol, de grootte van de winsten voor vorige periodes.

"Bekende waarden X" zijn argumenten die overeenkomen met de bekende waarden van de functie. In hun rol hebben we de nummering van de jaren, waarvoor informatie over de winsten van voorgaande jaren werd verzameld.

Natuurlijk is als een argument niet noodzakelijk een tijdelijk segment. Ze kunnen bijvoorbeeld een temperatuur zijn en de functie van de functie kan het niveau van wateruitbreiding zijn tijdens verwarming.

Bij het berekenen van deze methode wordt een lineaire regressiemethode gebruikt.

Laten we de nuances van de toepassing van de exploitant analyseren voorspelt op een specifiek voorbeeld. Neem dezelfde tafel. We zullen de winstvoorspelling voor 2018 moeten achterhalen.

1. We markeren een lege cel op het vel waar het verwerkingsresultaat gepland is. Klik op de knop "FUNCTION".



2. Wizard wordt geopend. In de categorie "Statistical" wijst de naam "voorspelt" toe en klik vervolgens op de knop "OK".



3. Het argumentenvenster begint. In het veld "X" specificeren we de waarde van het argument waaraan de functiewaarde moet worden gevonden. In ons geval is het 2018. Daarom introduceren we het record "2018". Maar het is beter om deze indicator in de cel op het vel te specificeren, en in het veld "X" gewoon een link hiernaar geven. Dit zal in de toekomst toestaan ​​om de berekeningen te automatiseren en het jaar gemakkelijk te veranderen.

   In het veld "Bekende waarden Y" specificeren we de coördinaten van de kolom "Winst Enterprise". Dit kan worden gedaan door de cursor in het veld te installeren en vervolgens de linkermuisknop te klimmen en de juiste kolom op het vel te markeren.

   Evenzo introduceren we in het veld "Bekend X-waarden" het adres van het jaar van het jaar met de gegevens voor de afgelopen periode.

   Nadat alle informatie wordt gedaan, klikt u op de knop "OK".



4. De bediener berekent op basis van de ingevoerde gegevens en geeft het resultaat op het scherm weer. Voor 2018 is het gepland om ongeveer 4564,7 duizend roebel te profiteren. Op basis van de resulterende tabel kunnen we een schema bouwen met behulp van de gidscreatie-tools die hierboven zijn besproken.



5. Als u het jaar in de cel verandert dat is gebruikt om het argument in te voeren, zal het resultaat dienovereenkomstig veranderen en wordt het schema automatisch bijgewerkt. Bijvoorbeeld, volgens prognoses in 2019, is de hoeveelheid winst bijvoorbeeld 4637.8 duizend roebel.

Maar vergeet niet dat, zoals in de constructie van de trendlijn, de tijdsduur totdat de voorspelde periode niet langer mag zijn dan 30% van de totale periode waarvoor de database is geaccumuleerd.

Les: Extrapolatie in Excel

Methode 3: Operator-trend

Om te voorspellen, kunt u een andere functie gebruiken - de trend. Het verwijst ook naar de categorie Statistische operators. De syntaxis lijkt grotendeels op de syntaxis van de predic-tool en ziet er als volgt uit:

= Tendens (bekende waarden_y; bekende waarden_x; new_dation_x; [const])

Zoals we zien, komen de argumenten "bekende y-waarden" en "bekende X-waarden" volledig overeen met de vergelijkbare elementen van de Predspot-operator, en het argument "Nieuwe waarden x" komt overeen met het "X" -argument van de vorige tool. Bovendien heeft de trend een extra argument van de constante, maar het is niet verplicht en alleen gebruikt als er permanente factoren zijn.

Deze operator wordt het meest effectief gebruikt in de aanwezigheid van een lineaire afhankelijkheidsafhankelijkheid.

Laten we eens kijken hoe deze tool alles werkt met dezelfde data-array. Om de verkregen resultaten te vergelijken, bepaalt het prognosepunt 2019.

1. Wij produceren de aanwijzing van de cel om het resultaat weer te geven en de functies Master op de gebruikelijke manier uit te voeren. In de categorie "Statistical" vinden we de naam "Trend". Klik op de knop "OK".



2. Operator-argumentenvenster De neiging opent. In de "bekende waarden van Y" -veld is de wijze van coördinaten van de winsten van het bedrijf al hierboven beschreven. Voer in het veld "Bekende waarden x" het adres in van de kolom van het jaar in. In het veld "Nieuwe waarden X" voeren we een link naar de cel in, waar het nummer van het jaar waarop de prognose moet worden opgegeven. In ons geval is het 2019. Het veld "Constant" laat leeg. Klik op de knop "OK".



3. De operator verwerkt de gegevens en geeft het resultaat weer op het scherm. Zoals u kunt zien, zal het bedrag aan voorspelde winsten voor 2019, berekend door de methode van lineaire afhankelijkheid, net als bij de vorige berekeningsmethode, 4637.8 duizend roebel.

Methode 4: Exploitantgroei

Een andere functie waarmee u in Excele kunt voorspellen, is een exploitantgroei. Het verwijst ook naar de statistische groep instrumenten, maar in tegenstelling tot de vorige, past het geen lineaire afhankelijkheidsmethode toe, maar exponentieel. De syntaxis van deze tool ziet eruit als:

= Groei (bekende waarden_y; bekende waarden_x; new_stations_x; [const])

Zoals we zien, worden de argumenten voor deze functie precies herhaald aan de argumententrend van de operator, dus we stoppen niet de tweede keer op hun beschrijving, maar we gaan onmiddellijk door naar het gebruik van deze tool in de praktijk.

1. We weken de uitvoercel toe en veroorzaakt al de functies van functies. In de lijst met statistische operatoren zijn we op zoek naar een "groei" -clausule, wij weken het toe en klik op de knop "OK".



2. Het argumentvenster van de opgegeven functie is geactiveerd. We komen in het veld van dit venster in de gegevens is volledig vergelijkbaar met hoe we ze in de argumenten van de operator de trend hebben ingevoerd. Nadat de informatie wordt gedaan, klikt u op de knop "OK".



3. Het resultaat van gegevensverwerking wordt weergegeven op de monitor in de eerder opgegeven cel. Zoals we zien, is dit keer het resultaat 4682.1 duizend roebel. Verschillen uit de resultaten van gegevensverwerking door de tendens van de operator is onbeduidend, maar ze zijn beschikbaar. Dit komt door het feit dat deze tools verschillende berekeningsmethoden toepassen: de lineaire afhankelijkheidsmethode en de methode van exponentiële verslaving.

Methode 5: Operator Linene

De lineaire exploitant bij het berekenen van de lineaire benzinemethode. Het is niet nodig om in de war te komen met de methode van lineaire afhankelijkheid die wordt gebruikt door het Tendency-tool. De syntaxis heeft dit soort:

= Linene (bekende waarden_y; bekende waarden_x; new_stations_x; [const]; [statistieken])

De laatste twee argumenten zijn optioneel. Met de eerste twee zijn we op de vorige manieren bekend. Maar je hebt waarschijnlijk gemerkt dat er geen argument in deze functie is, wat aangeeft dat nieuwe waarden. Het feit is dat deze tool alleen de verandering in de hoeveelheid inkomsten per eenheid van de periode bepaalt, die in ons geval een jaar is, maar het algemene uitkomst afzonderlijk wordt berekend, waardoor het resultaat is van de berekening van de operator, vermenigvuldigd met het aantal jaren.

1. Wij produceren de selectie van de cel waarin de berekening wordt gemaakt en voert de functies van de functies uit. We weken de naam "Linene" in de categorie "Statistical" toe en klik op de knop "OK".



2. In het veld "Bekende R-waarden", dat het venster van de argumenten heeft geopend, introduceer de coördinaten van de kolom "Winst Enterprise". In het veld "Beroemde X-waarden" introduceren we het adres van de kolom "Jaar". De resterende velden worden leeg gelaten. Klik vervolgens op de knop "OK".



3. Het programma berekent en geeft de waarde van de lineaire trend weer op de geselecteerde cel.



4. Nu moeten we de grootte van de geprojecteerde winst voor 2019 achterhalen. Installeer het "=" aanmelden in een lege cel op het vel. Klik op een cel, die de werkelijke waarde van de winsten voor het laatste jaar van het jaar (2016) bevat. Plaats het bord "+". Klik verder op de cel, die een eerder berekende lineaire trend bevat. We plaatsen het bord "*". Sinds tussen het laatste jaar van de bestudeerde periode (2016) en het jaar om een ​​voorspelling (2019) te maken, ligt een termijn van drie jaar, we zetten het nummer "3" in de cel in. Om een ​​berekening te maken door op de knop Enter te klikken.

Zoals u kunt zien, zal de geprojecteerde winstwaarde berekend door de lineaire benzinemethode, in 2019 4614,9 duizend roebel.

Methode 6: Operator LGRFPRIBL

De laatste tool waar we naar zullen kijken, zal LGRFPRIBL zijn. Deze operator maakt berekeningen op basis van de exponentiële benzinemethode. De syntaxis heeft de volgende structuur:

= LGRFPRIBLIN (bekende waarden_y; bekende waarden_x; new_stations_x; [const]; [statistieken])

Zoals u kunt zien, herhalen alle argumenten de bijbehorende elementen van de vorige functie volledig. Het algoritme van de voorspelde berekening zal een beetje veranderen. De functie berekent de exponentiële trend die zal laten zien hoe vaak het bedrag aan de omzet in één periode wordt gewijzigd, dat is voor het jaar. We moeten het verschil in winsten vinden tussen de laatste daadwerkelijke periode en de eerste planning, vermenigvuldig het door het aantal geplande perioden (3) en voegt u toe aan het resultaat van de som van de laatste daadwerkelijke periode.

1. In de lijst met exploitanten van de Master of Functies, wijzen we de naam "LGRFPRIBL" toe. We maken een klik op de knop "OK".



2. Het argumentenvenster begint. Daarin introduceren we de gegevens precies zoals ze deden bij het gebruik van de lineaire functie. Klik op de knop "OK".



3. Het resultaat van de exponentiële trend wordt berekend en verlengd naar de aangewezen cel.



4. We plaatsen het bord "=" in een lege cel. We openen haakjes en selecteren de cel die de waarde van de omzet voor de laatste daadwerkelijke periode bevat. We plaatsen het bord "*" en markeer een cel met een exponentiële trend. We plaatsen een teken van minus en klik opnieuw op het element waarin het bedrag aan de inkomsten in de laatste periode is. We sluiten de beugel en besturen met de tekens "* 3 +" zonder aanhalingstekens. Nogmaals, klik op dezelfde cel, die voor de laatste keer is toegewezen. Om de berekening uit te voeren, drukt u op de knop ENTER.

Het voorspelde bedrag van de winsten in 2019, dat werd berekend door de methode van exponentiële benadering, zal 4639.2 duizend roebels zijn, die opnieuw niet veel anders is dan de verkregen resultaten bij het berekenen van eerdere methoden.

Les: Andere statistische functies in Excel

We ontdekten wat methoden kunnen voorspellen in het Excel-programma. Het kan grafisch worden gedaan door het gebruik van een trendlijn en analytisch - met behulp van een aantal ingebouwde statistische functies. Als gevolg van het verwerken van identieke gegevens, kunnen deze operators een ander resultaat blijken te zijn. Maar het is niet verrassend, omdat ze allemaal verschillende berekeningsmethoden gebruiken. Als de oscillatie klein is, kunnen al deze opties die van toepassing zijn op een specifiek geval als relatief betrouwbaar worden beschouwd.