Voorspellingsinstrumente in Microsoft Excel

Vooruitskatting is 'n baie belangrike element in byna elke vakgebied, van ekonomie tot ingenieurswese. Daar is 'n groot aantal sagteware wat op hierdie gebied spesialiseer. Ongelukkig weet nie alle gebruikers dat die gewone Excel-sigbladverwerker in sy arsenaalinstrumente het om voorspelling te doen nie, wat nie doeltreffender is as professionele programme nie. Kom ons kyk wat hierdie instrumente is en hoe u 'n voorspelling in die praktyk kan maak.

Voorspellingsprosedure

Die doel van enige voorspelling is om die huidige tendens te identifiseer en die verwagte resultaat te bepaal in verhouding tot die bestudeerde objek op 'n sekere tydstip in die toekoms.

Metode 1: neigingslyn

Een van die gewildste soorte grafiese voorspellings in Excel is ekstrapolasie deur 'n tendenslyn te bou.

Kom ons probeer die hoeveelheid wins van die onderneming oor drie jaar voorspel aan die hand van data oor hierdie aanwyser vir die afgelope 12 jaar.

1. Ons bou 'n afhanklikheidsgrafiek gebaseer op tabelgegevens wat bestaan ​​uit argumente en funksiewaardes. Om dit te doen, kies die tabelarea en wees dan op die oortjie "Insert", klik op die ikoon van die gewenste soort kaart wat in die blok geleë is "Diagramme". Dan kies ons die tipe wat geskik is vir 'n spesifieke situasie. Dit is die beste om 'n strooikaart te kies. U kan 'n ander aansig kies, maar dan, sodat die data korrek vertoon word, moet u redigering uitvoer, veral die lyn van die argument verwyder en 'n ander skaal van die horisontale as kies.



2. Nou moet ons 'n tendenslyn bou. Ons klik met die rechtermuisknop op enige van die punte in die diagram. Stop die keuse op die item in die geaktiveerde konteksmenu Voeg Trend Line by.



3. Die formateringsvenster vir die lynlyn word oopgemaak. Daarin kan u een van ses soorte benaderings kies:
   • lineêre;
   • logaritmiese;
   • eksponensiële;
   • grade;
   • polinoom;
   • Lineêre filter.

   Kom ons begin deur 'n lineêre benadering te kies.

   In die instellingsblok "Voorspelling" in die veld "Stuur na" stel die nommer in "3,0", aangesien ons drie jaar vooraf moet voorspel. Boonop kan u die kassie langs die instellings merk. "Toon vergelyking in diagram" en "Plaas die benaderingsvertrouenswaarde (R ^ 2) op die diagram". Die laaste aanwyser vertoon die kwaliteit van die tendenslyn. Nadat die instellings gemaak is, klik op die knoppie "Close".



4. Die tendenslyn is gebou en daaruit kan ons die benaderde hoeveelheid wins oor drie jaar bepaal. Soos ons sien, behoort dit teen daardie tyd meer as 4500 duisend roebels te wees. faktor R2Soos hierbo genoem, vertoon die kwaliteit van die tendenslyn. In ons geval, die waarde R2 maak op 0,89. Hoe hoër die koëffisiënt, hoe hoër is die betroubaarheid van die lyn. Die maksimum waarde daarvan kan gelyk wees 1. Dit word algemeen aanvaar met 'n koëffisiënt hierbo 0,85 die neigingslyn is betroubaar.



5. As die vertrouensvlak u nie pas nie, kan u terugkeer na die venster van die tendenslynformaat en enige ander benaderingsvorm kies. U kan al die beskikbare opsies probeer om die akkuraatste te vind.

   

   Daar moet op gelet word dat die voorspelling wat ekstrapolasie deur die tendenslyn gebruik, effektief kan wees as die voorspellingsperiode nie meer as 30% van die ontleed basis van periodes oorskry nie. Dit wil sê, as ons 'n periode van 12 jaar ontleed, kan ons nie meer as 3-4 jaar effektief voorspel nie. Maar selfs in hierdie geval sal dit betreklik betroubaar wees as daar gedurende hierdie tyd geen force majeure of, inteendeel, uiters gunstige omstandighede sal wees wat nie in vorige periodes was nie.

les: Hoe om 'n tendenslyn in Excel te bou

Metode 2: die voorspellingsoperateur

Ekstrapolasie vir tabelgegevens kan gedoen word deur die standaard Excel-funksie VOORUITSIGTE. Hierdie argument behoort tot die kategorie statistiese instrumente en het die volgende sintaksis:

= PREDICT (X; bekend_y_waardes; bekende_x_waardes)

"X" is 'n argument waarvoor die funksiewaarde bepaal moet word. In ons geval is die argument die jaar waarvoor voorspelling gedoen moet word.

Bekende y waardes - basis van bekende funksiewaardes. In ons geval word die rol daarvan gespeel deur die hoeveelheid wins vir vorige periodes.

Bekend x waardes is die argumente waarop die bekende waardes van die funksie ooreenstem. In hul rol het ons die nommering van die jare waarvoor inligting oor die wins van vorige jare versamel is.

Uiteraard hoef die argument nie 'n tydsduur te wees nie. Dit kan byvoorbeeld temperatuur wees, en die waarde van die funksie kan die vlak van uitbreiding van water wees as dit verhit word.

By die berekening van hierdie metode word die lineêre regressie-metode gebruik.

Kom ons kyk na die nuanses van die gebruik van die operateur VOORUITSIGTE op 'n konkrete voorbeeld. Neem die hele tafel. Ons sal moet weet wat die winsvoorspelling vir 2018 is.

1. Kies 'n leë sel op die blad waar u beplan om die verwerkingsresultaat te vertoon. Klik op die knoppie "Voeg funksie in".



2. Open Funksie-towenaar. In kategorie "Statistiek" kies die naam "VOORSPELLING"en klik dan op die knoppie "OK".



3. Die argumentvenster begin. In die veld "X" dui die waarde aan van die argument waarheen u die waarde van die funksie wil vind. In ons geval is dit 2018. Daarom skryf ons "2018". Maar dit is beter om hierdie aanwyser aan te dui in 'n sel op die blad en in die veld "X" gee dit net 'n skakel. Dit sal in die toekoms die outomatisering van die berekeninge moontlik maak en, indien nodig, die jaar maklik verander.

   In die veld Bekende y waardes spesifiseer die koördinate van die kolom "Wins van die onderneming". Dit kan gedoen word deur die muis in die veld te plaas en dan die linkermuisknop in te hou en die ooreenstemmende kolom op die vel te merk.

   Net so in die veld Bekend x waardes voer die kolomadres in "Jaar" met data vir die afgelope tydperk.

   Nadat al die inligting ingevoer is, klik op die knoppie "OK".



4. Die operateur bereken op grond van die ingevoerde data en vertoon die resultaat op die skerm. Vir 2018 word beoog om wins te maak in die omgewing van 4.564.7 duisend roebels. Op grond van die resulterende tabel, kan ons 'n grafiek bou met behulp van die kaarttoerusting wat hierbo bespreek is.



5. As u die jaar verander in die sel wat gebruik is om die argument in te voer, sal die resultaat dienooreenkomstig verander en die skedule word outomaties opgedateer. Byvoorbeeld, volgens voorspellings in 2019, sal die hoeveelheid wins 4637.8 duisend roebels wees.

Maar moenie vergeet dat die periode voor die voorspelde periode, soos met die konstruksie van die tendenslyn, nie meer as 30% van die hele periode waarvoor die databasis opgehoop is, moet oorskry nie.

les: Extrapolation in Excel

Metode 3: TREND-operateur

Vir voorspelling kan u 'n ander funksie gebruik - TREND. Dit behoort ook tot die kategorie statistiese operatore. Die sintaksis daarvan is baie soos gereedskap-sintaksis VOORUITSIGTE en lyk so:

= TREND (Bekende waardes_j; bekende waardes_x; nuut_waardes_x; [const])

Soos u kan sien, die argumente Bekende y waardes en Bekend x waardes stem volledig ooreen met soortgelyke elemente van die operateur VOORUITSIGTE, en die argument "Nuwe x waardes" stem ooreen met argument "X" vorige instrument. Daarbenewens TREND daar is 'n addisionele argument "Konstante", maar dit is opsioneel en word slegs gebruik as daar konstante faktore is.

Hierdie operateur word die beste gebruik in die teenwoordigheid van 'n lineêre afhanklikheid van die funksie.

Kom ons kyk hoe hierdie instrument met dieselfde datastelsel sal werk. Om die resultate te vergelyk, definieer ons die voorspellingspunt as 2019.

1. Ons wys die sel aan om die resultaat te wys en te laat loop Funksie-towenaar op die gewone manier. In kategorie "Statistiek" vind en merk die naam "TREND". Klik op die knoppie "OK".



2. Operateurargumentvenster word oopgemaak TREND. In die veld Bekende y waardes volgens die metode hierbo beskryf, voer ons die koördinate van die kolom in "Wins van die onderneming". In die veld Bekend x waardes voer die kolomadres in "Jaar". In die veld "Nuwe x waardes" ons voer die skakel in na die sel waar die jaarnommer geleë is, waarvoor die voorspelling aangedui moet word. In ons geval is dit 2019. Die veld "Konstante" laat dit leeg. Klik op die knoppie "OK".



3. Die operateur verwerk die data en vertoon die resultaat op die skerm. Soos u kan sien, sal die bedrag van die geprojekteerde wins vir 2019, bereken deur die lineêre afhanklikheidsmetode, net soos in die vorige berekeningsmetode 4637,8 duisend roebels wees.

Metode 4: GROEI-operateur

'N Ander funksie wat gebruik kan word vir voorspelling in Excel, is die GROEI-operateur. Dit behoort ook tot die statistiese groep gereedskap, maar anders as die vorige, gebruik dit nie die lineêre afhanklikheidsmetode nie, maar die eksponensiële metode. Die sintaksis van hierdie instrument is soos volg:

= GROEI (Bekende waardes_j; bekende waardes_x; nuut_waardes_x; [const])

Soos u kan sien, herhaal die argumente van hierdie funksie die argumente van hierdie funksie TREND, dus sal ons nie vir die tweede keer op die beskrywing daarvan let nie, maar dadelik voortgaan met die praktiese toepassing van hierdie instrument.

1. Ons kies die sel om die resultaat te lewer en noem dit op die gewone manier Funksie-towenaar. Soek die item in die lys met statistiese operatore "Groei", kies dit en klik op die knoppie "OK".



2. Die argumentvenster van die bogenoemde funksie is geaktiveer. Voer die data in die velde van hierdie venster in op dieselfde manier as wat ons hulle in die venster van die operateurargument ingevoer het TREND. Nadat die inligting ingevoer is, klik op die knoppie "OK".



3. Die resultaat van dataverwerking word op die monitor in die voorheen aangeduide sel vertoon. Soos u kan sien, is hierdie keer die resultaat 4682.1 duisend roebels. Verskille van die resultate van die operateurgegevensverwerking TREND onbeduidend, maar dit is beskikbaar. Dit is te danke aan die feit dat hierdie instrumente verskillende berekeningsmetodes gebruik: die lineêre afhanklikheidsmetode en die eksponensiële afhanklikheidsmetode.

Metode 5: LINEÊRE operateur

operateur LINEAR in die berekening gebruik die lineêre benaderingsmetode. Dit moet nie verwar word met die lineêre afhanklikheidsmetode wat deur die instrument gebruik word nie. TREND. Die sintaksis daarvan is soos volg:

= LINE (Bekende waardes_j; bekende waardes_x; nuut_waardes_x; [const]; [statistiek])

Die laaste twee argumente is opsioneel. Met die eerste twee is ons vertroud met die vorige metodes. Maar u het waarskynlik opgemerk dat daar geen argument in hierdie funksie is wat op nuwe waardes dui nie. Die feit is dat hierdie instrument slegs die verandering in inkomste per eenheidseenheid bepaal, wat in ons geval gelyk is aan een jaar, maar ons moet die totale resultaat afsonderlik bereken, en die resultaat van die operateur se berekening by die laaste werklike winswaarde voeg LINEARkeer die aantal jare.

1. Ons kies die sel waarin die berekening uitgevoer sal word en voer die Funksie-assistent uit. Kies die naam "Lineêre" in kategorie "Statistiek" en klik op die knoppie "OK".



2. In die veld Bekende y waardes, die oop venster van argumente, voer die koördinate van die kolom in "Wins van die onderneming". In die veld Bekend x waardes voer die kolomadres in "Jaar". Die oorblywende velde word leeg gelaat. Klik dan op die knoppie "OK".



3. Die program bereken en vertoon die lineêre tendenswaarde in die geselekteerde sel.



4. Nou moet ons die grootte van die geprojekteerde wins vir 2019 uitvind. Stel die teken op "=" na enige leë sel op die blad. Ons klik op die sel wat die werklike hoeveelheid wins vir die laaste studiejaar (2016) bevat. Ons sit 'n teken "+". Klik dan op die sel wat die voorheen berekende lineêre neiging bevat. Ons sit 'n teken "*". Aangesien daar tussen die laaste jaar van die studietydperk (2016) en die jaar waarvoor u 'n voorspelling wil maak (2019), 'n periode van drie jaar lê, stel ons die nommer in die sel "3". Klik op die knoppie om 'n berekening te maak Tik.

Soos u kan sien, sal die geprojekteerde winsmarge bereken deur die lineêre benaderingsmetode in 2019 4.614.9 duisend roebels beloop.

Metode 6: LGRFPPRIBLE operateur

Die laaste instrument waarna ons sal kyk, sal wees LOGEST. Hierdie operateur doen berekeninge gebaseer op die eksponensiële benaderingsmetode. Die sintaksis daarvan het die volgende struktuur:

= LGRFPRIBLE (Bekende waardes_y; bekende waardes_x; nuut_waardes_x; [const]; [statistiek])

Soos u kan sien, herhaal alle argumente die ooreenstemmende elemente van die vorige funksie volledig. Die voorspellingsberekeningalgoritme sal effens verander. Die funksie bereken die eksponensiële neiging, wat toon hoeveel keer die omset gedurende een periode, dit wil sê vir 'n jaar, sal verander. Ons sal die verskil in wins tussen die laaste werklike periode en die eerste beplande periode moet vind, en dit vermenigvuldig met die aantal beplande periodes (3) en voeg die som van die laaste werklike periode by die resultaat.

1. Kies die naam in die lys van die operators van die Funksie-assistent "LOGEST". Klik op die knoppie "OK".



2. Die argumentvenster begin. Hierin voer ons die data presies in soos ons het met behulp van die funksie LINEAR. Klik op die knoppie "OK".



3. Die resultaat van die eksponensiële neiging word bereken en in die aangewese sel vertoon.



4. Ons sit 'n teken "=" in 'n leë sel. Maak die hakies oop en kies die sel wat die omsetwaarde vir die laaste werklike periode bevat. Ons sit 'n teken "*" en kies die sel wat die eksponensiële neiging bevat. Ons plaas 'n minusteken en klik weer op die element waarin die waarde van die inkomste vir die laaste periode geleë is. Maak die hakie toe en ry die karakters in "*3+" sonder aanhalings. Klik weer op dieselfde sel wat die laaste keer gekies is. Klik op die knoppie om die berekening uit te voer Tik.

Die geprojekteerde hoeveelheid wins in 2019, wat volgens die metode van eksponensiële benadering bereken is, sal 4.639.2 duisend roebels beloop, wat weereens nie veel verskil van die resultate wat in die vorige berekening verkry is nie.

les: Ander statistiese funksies in Excel

Ons het uitgevind hoe u voorspellings kan maak in die Excel-program. Dit kan grafies gedoen word deur gebruik te maak van 'n tendenslyn, en analities met behulp van 'n aantal ingeboude statistiese funksies. As gevolg van die verwerking van identiese data deur hierdie operateurs, kan 'n ander resultaat verkry word. Maar dit is nie verbasend nie, aangesien hulle almal verskillende berekeningsmetodes gebruik. As die skommeling klein is, kan al hierdie opsies wat op 'n spesifieke geval van toepassing is, as betroubaar beskou word.