Vergelijking in Excel-grafiek. Een lineaire trend opbouwen

Wat moet ik doen als er geen timingmetingen zijn voor bepaalde volumes/groottes van producten? Of is het aantal metingen onvoldoende en kunnen er op korte termijn geen aanvullende waarnemingen worden gedaan? De beste manier om dit probleem op te lossen is door berekende afhankelijkheden (regressievergelijkingen) op te bouwen met behulp van trendlijnen in MS Excel.

Laten we eens een reële situatie bekijken: in een magazijn werden timingwaarnemingen uitgevoerd om het bedrag van de arbeidskosten voor het verzamelen van dozen van een bestelling vast te stellen. De resultaten van deze waarnemingen worden weergegeven in Tabel 1 hieronder.

Vervolgens ontstond de behoefte om de tijd te bepalen die nodig was om 0,6 en 0,9 m3 goederen/order te selecteren. Omdat het niet mogelijk was om aanvullende timingstudies uit te voeren, werd de tijd besteed aan het selecteren van deze ordervolumes berekend met behulp van regressievergelijkingen in MS Excel. Om dit te bereiken is Tabel 1 omgezet naar Tabel 2.

Een spreidingsdiagram selecteren, Afb. 1

Volgende stap: de muiscursor werd op een van de punten op de kaart geplaatst en met de rechtermuisknop werd het contextmenu opgeroepen, waarin het item werd geselecteerd: “trendlijn toevoegen” (Fig. 2).

Een trendlijn toevoegen, afb. 2

In het venster dat verschijnt voor het instellen van het trendlijnformaat (Afb. 3) werden opeenvolgend geselecteerd: lineair/vermogenslijntype en de volgende items werden gecontroleerd: “toon de vergelijking in het diagram” en “plaats de geschatte betrouwbaarheidswaarde (R^2) in het diagram” (determinatiecoëfficiënt).

Trendlijnformaat, afb. 3

Als resultaat werden de grafieken weergegeven in figuur 1 verkregen. 4 en 5.

Lineair berekende afhankelijkheid, Fig. 4

Machtswet berekende afhankelijkheid, Fig. 5

Een visuele analyse van de grafieken geeft duidelijk de nabijheid van de verkregen afhankelijkheden aan. Bovendien is de b(R^2), ook wel de determinatiecoëfficiënt genoemd, bij beide afhankelijkheden dezelfde waarde van 0,97. Het is bekend dat hoe dichter de determinatiecoëfficiënt bij 1 ligt, hoe meer de trendlijn overeenkomt met de werkelijkheid. Ook kan worden gesteld dat 97% van de verandering in de tijd besteed aan orderverwerking wordt verklaard door een verandering in de hoeveelheid goederen. Daarom is het in dit geval niet belangrijk: welke berekende afhankelijkheid moet worden gekozen als de belangrijkste voor de daaropvolgende berekening van tijdkosten.

Laten we de lineair berekende afhankelijkheid als belangrijkste nemen. Vervolgens worden de tijdskosten, afhankelijk van de hoeveelheid goederen, bepaald door de formule: y = 54,511x + 0,1489. De resultaten van deze berekeningen voor de hoeveelheid goederen waarvoor eerder timingwaarnemingen werden uitgevoerd, worden weergegeven in Tabel 3 hieronder.

Laten we de gemiddelde afwijking bepalen van de bestede tijd, berekend met behulp van de regressievergelijking, van de bestede tijd berekend op basis van tijdwaarnemingen: (-0,05+0,10-0,05+0,01)/4=0,0019. De tijdsbesteding berekend met behulp van de regressievergelijking verschilt dus slechts met de tijdsbesteding berekend op basis van tijdregistratiegegevens 0,19%. Het verschil tussen de gegevens is verwaarloosbaar.

Met behulp van de formule: y = 54,511x + 0,1489 bepalen we de tijdskosten voor de hoeveelheid goederen waarvoor nog niet eerder timingwaarnemingen zijn uitgevoerd (Tabel 4).

Dus berekende afhankelijkheden construeren met behulp van trendlijnen in MS Excel - Dit is een uitstekende manier om de tijd vast te stellen die is besteed aan handelingen die om verschillende redenen niet in de tijdwaarnemingen zijn opgenomen.

Meest voorkomend de trend wordt weergegeven door een lineair verband van het type dat wordt bestudeerd

waarbij y de relevante variabele is (bijvoorbeeld productiviteit) of de afhankelijke variabele;
x is een getal dat de positie (tweede, derde, etc.) van het jaar in de prognoseperiode bepaalt, of een onafhankelijke variabele.

Bij het lineair benaderen van de relatie tussen twee parameters wordt meestal de kleinste kwadratenmethode gebruikt om de empirische coëfficiënten van een lineaire functie te vinden. De essentie van de methode is dat de lineaire “best fit”-functie door de grafiekpunten gaat die overeenkomen met de minimale som van kwadratische afwijkingen van de gemeten parameter. Deze toestand ziet er als volgt uit:

waarbij n het volume is van de onderzochte populatie (het aantal observatie-eenheden).

Rijst. 5.3. Een trend opbouwen met behulp van de kleinste kwadratenmethode

De waarden van de constanten b en a of de coëfficiënt van de variabele X en de vrije term van de vergelijking worden bepaald door de formule:

In tafel 5.1 toont een voorbeeld van het berekenen van een lineaire trend op basis van gegevens.

Tabel 5.1. Lineaire trendberekening

Methoden voor het afvlakken van oscillaties.

Als er sterke discrepanties zijn tussen aangrenzende waarden, is de door de regressiemethode verkregen trend moeilijk te analyseren. Als een reeks bij het voorspellen gegevens bevat met een grote spreiding van fluctuaties in aangrenzende waarden, moet u deze volgens bepaalde regels gladstrijken en vervolgens zoeken naar de betekenis in de voorspelling. Naar de methode om oscillaties af te vlakken
omvatten: methode van voortschrijdend gemiddelde (n-puntsgemiddelde wordt berekend), exponentiële afvlakkingsmethode. Laten we ze bekijken.

Voortschrijdend gemiddelde methode (MAM).

Met MSS kunt u een reeks waarden gladstrijken om een ​​trend te benadrukken. Deze methode neemt het gemiddelde (meestal het rekenkundig gemiddelde) van een vast aantal waarden. Bijvoorbeeld een voortschrijdend gemiddelde van drie punten. De eerste drie waarden, samengesteld uit gegevens voor januari, februari en maart (10 + 12 + 13), worden genomen en het gemiddelde wordt vastgesteld op 35: 3 = 11,67.

De resulterende waarde van 11,67 wordt in het midden van het bereik geplaatst, d.w.z. volgens de februarilijn. Vervolgens “schuiven we met een maand” en nemen de tweede drie cijfers, beginnend van februari tot april (12 + 13 + 16), en berekenen het gemiddelde gelijk aan 41: 3 = 13,67, en op deze manier verwerken we de gegevens voor de hele serie. De resulterende gemiddelden vertegenwoordigen een nieuwe reeks gegevens voor het construeren van een trend en de benadering ervan. Hoe meer punten er worden gebruikt om het voortschrijdend gemiddelde te berekenen, hoe sterker de afvlakking van fluctuaties plaatsvindt. Een voorbeeld uit MBA van trendconstructie wordt gegeven in de tabel. 5.2 en in afb. 5.4.

Tabel 5.2 Trendberekening met behulp van de driepunts voortschrijdend gemiddelde methode

De aard van fluctuaties in de oorspronkelijke gegevens en gegevens verkregen door de methode van voortschrijdend gemiddelde wordt geïllustreerd in figuur 2. 5.4. Uit een vergelijking van de grafieken van de reeks initiële waarden (reeks 3) en driepunts voortschrijdende gemiddelden (reeks 4) wordt duidelijk dat de fluctuaties kunnen worden afgevlakt. Hoe groter het aantal punten in het rekenbereik van het voortschrijdend gemiddelde, des te duidelijker zal de trend naar voren komen (rij 1). Maar de procedure voor het vergroten van het bereik leidt tot een vermindering van het aantal eindwaarden en dit vermindert de nauwkeurigheid van de voorspelling.

Prognoses moeten worden gemaakt op basis van schattingen van de regressielijn op basis van de waarden van de initiële gegevens of voortschrijdende gemiddelden.

Rijst. 5.4. De aard van veranderingen in het verkoopvolume per maand van het jaar:
initiële gegevens (rij 3); voortschrijdende gemiddelden (rij 4); exponentiële afvlakking (rij 2); trend geconstrueerd door regressiemethode (rij 1)

Exponentiële afvlakkingsmethode.

Een alternatieve benadering om de spreiding van reekswaarden te verminderen, is het gebruik van de exponentiële afvlakkingsmethode. De methode wordt “exponentiële afvlakking” genoemd vanwege het feit dat elke waarde van perioden die in het verleden gaan, wordt verminderd met een factor (1 – α).

Elke afgevlakte waarde wordt berekend met behulp van een formule in de vorm:

St =aYt +(1−α)St−1,

waarbij St de huidige afgevlakte waarde is;
Yt – huidige waarde van de tijdreeks; St – 1 – vorige afgevlakte waarde; α is een afvlakconstante, 0 ≤ α ≤ 1.

Hoe kleiner de waarde van de constante α, hoe minder gevoelig deze is voor trendveranderingen in een bepaalde tijdreeks.

Diagrammen en grafieken worden gebruikt om numerieke gegevens te analyseren, bijvoorbeeld om de relatie tussen twee soorten waarden te evalueren. Voor dit doel kunnen een trendlijn en de bijbehorende vergelijking, voorspelde waarden die voor meerdere perioden vooruit of achteruit zijn berekend, worden toegevoegd aan de gegevens in een diagram of grafiek.

Trendlijn vertegenwoordigt een rechte of gebogen lijn die de oorspronkelijke gegevens benadert (dichterbij brengt) op basis van een regressievergelijking of voortschrijdend gemiddelde. De benadering wordt bepaald met behulp van de kleinste kwadratenmethode. Afhankelijk van de aard van het gedrag van de brongegevens (dalend, stijgend, etc.) wordt een interpolatiemethode geselecteerd die gebruikt moet worden om een ​​trend op te bouwen.

Er zijn verschillende opties om een ​​trendlijn te vormen.

Lineaire functie: y=mx+b

waarbij m de raaklijn is van de hellingshoek van de rechte lijn, b de verplaatsing.

Een rechte trendlijn (lineaire trend) is het meest geschikt voor hoeveelheden die met een constante snelheid veranderen. Wordt gebruikt in gevallen waarin gegevenspunten zich dicht bij een rechte lijn bevinden.

Logaritmische functie: y=c*ln⁡x+b

waarbij c en b constanten zijn.

Een logaritmische trendlijn komt overeen met een gegevensreeks waarvan de waarden aanvankelijk snel stijgen of dalen en vervolgens geleidelijk stabiliseren. Kan worden gebruikt voor positieve en negatieve gegevens.

Polynoomfunctie (tot en met de 6e graad): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6

waarbij b, c 1, c 2, ... c 6 constanten zijn.

Een polynomiale trendlijn wordt gebruikt om afwisselend stijgende en dalende gegevens te beschrijven. De graad van de polynoom wordt zo gekozen dat deze één groter is dan het aantal extremen (maxima en minima) van de curve.

Power functie: y = cxb

waarbij c en b constanten zijn.

Een trendlijn volgens de machtswet geeft goede resultaten voor positieve gegevens met constante versnelling. Voor reeksen met nul- of negatieve waarden is de constructie van de opgegeven trendlijn onmogelijk.

Exponentiële functie: y = cebx

waar c en b constanten zijn, is e de basis van de natuurlijke logaritme.

Exponentiële trend wordt gebruikt wanneer de verandering in gegevens voortdurend toeneemt. Het construeren van de aangegeven trend is onmogelijk als de reeks waarden van de reeksleden nul of negatieve gegevens bevat.

Lineair filteren volgens de formule: F t = (A t +A (t-1) +⋯+A (t-n+1))/n

waarbij n het totale aantal leden van de reeks is, is t het gegeven aantal punten (2 ≤ t< n).

Met een trend met lineaire filtering kunt u gegevensfluctuaties gladstrijken, waardoor de aard van de afhankelijkheden duidelijk wordt aangetoond. Om de opgegeven trendlijn op te bouwen, moet de gebruiker een getal opgeven: een filterparameter. Als het getal 2 is, wordt het eerste punt van de trendlijn gedefinieerd als het gemiddelde van de eerste twee gegevensitems, het tweede punt als het gemiddelde van de tweede en derde gegevensitems, enz.

Voor sommige typen diagrammen kan in principe geen trendlijn worden geconstrueerd: gestapelde diagrammen, volumetrische diagrammen, radardiagrammen, cirkeldiagrammen, oppervlaktediagrammen en ringdiagrammen. Indien mogelijk kunt u meerdere regels met verschillende parameters aan het diagram toevoegen. De overeenkomst van de trendlijn met de werkelijke waarden van de gegevensreeksen wordt vastgesteld met behulp van de betrouwbaarheidscoëfficiënt van de benadering:

De trendlijn en de bijbehorende parameters worden aan de grafiekgegevens toegevoegd met behulp van de volgende opdrachten:

Indien nodig kunt u de lijnparameters wijzigen door op een rij met grafiekgegevens of een trendlijn te klikken om het venster Trendlijnopmaak te openen. U kunt een regressievergelijking en een betrouwbaarheidscoëfficiënt voor de benadering toevoegen (of verwijderen), de richting en voorspelling van veranderingen in een gegevensreeks bepalen en ook de ontwerpelementen van de trendlijn corrigeren. De geselecteerde trendlijn kan ook worden verwijderd.

De figuur toont een tabel met gegevens over veranderingen in de waarde van een effect. Op basis van deze voorwaardelijke gegevens werd een spreidingsdiagram gemaakt, een polynomiale trendlijn van de derde orde (gespecificeerd door een stippellijn) en enkele andere parameters toegevoegd. De verkregen waarde van de benaderingsbetrouwbaarheidscoëfficiënt R2 in het diagram ligt dicht bij de eenheid, wat aangeeft hoe dicht de berekende trendlijn bij de probleemgegevens ligt. De voorspelde waarde van veranderingen in de waarde van een effect is gericht op groei.

Een trend is een patroon dat de opkomst of ondergang van een indicator in de loop van de tijd beschrijft. Als je een tijdreeks (statistische gegevens die een lijst zijn van geregistreerde waarden van een variabele indicator in de loop van de tijd) in een grafiek weergeeft, wordt vaak een bepaalde hoek benadrukt - de curve neemt geleidelijk toe of af, in dergelijke gevallen is het gebruikelijk om te zeggen dat de tijdreeks de neiging heeft (respectievelijk te stijgen of te dalen).

Trend als model

Als je een model bouwt dat dit fenomeen beschrijft, krijg je een vrij eenvoudig en erg handig voorspellingsinstrument dat geen ingewikkelde berekeningen vereist of tijd besteedt aan het controleren van de significantie of geschiktheid van de beïnvloedende factoren.

Wat is een trend als model? Dit is een reeks berekende vergelijkingscoëfficiënten die de regressieafhankelijkheid van de indicator (Y) van veranderingen in de tijd (t) uitdrukken. Dat wil zeggen, dit is precies dezelfde regressie als die we eerder hebben overwogen, alleen de beïnvloedende factor hier is de tijdsindicator.

Belangrijk!

Bij berekeningen betekent t meestal niet het jaar-, maand- of weeknummer, maar eerder het serienummer van de periode in de statistische populatie die wordt bestudeerd: de tijdreeks. Als een tijdreeks bijvoorbeeld over meerdere jaren wordt bestudeerd en de gegevens maandelijks worden geregistreerd, dan is het gebruik van een op nul gebaseerde nummering van maanden, van 1 tot 12 en opnieuw vanaf het begin, fundamenteel verkeerd. Het is ook onjuist als de studie van een reeks bijvoorbeeld in maart begint met het gebruik van 3 (de derde maand van het jaar) als de waarde van t; als dit de eerste waarde is in de populatie die wordt bestudeerd, en dan de seriële waarde ervan getal moet 1 zijn.

Lineair trendmodel

Net als elke andere regressie kan een trend lineair zijn (de mate van de beïnvloedende factor t is gelijk aan 1) of niet-lineair (de mate is groter of kleiner dan één). Omdat lineaire regressie de eenvoudigste, maar niet altijd de meest nauwkeurige is, zullen we dit soort trends nader bekijken.

Algemene vorm van de lineaire trendvergelijking:

Y(t) = a 0 + a 1 *t + Ɛ

Waar een 0 een regressiecoëfficiënt van nul is, dat wil zeggen wat Y zal zijn als de beïnvloedende factor gelijk is aan nul, is een 1 een regressiecoëfficiënt die de mate van afhankelijkheid van de bestudeerde indicator Y van de beïnvloedende factor t uitdrukt, Ɛ is een willekeurige component of standaard, de fout is in wezen het verschil tussen de werkelijke Y-waarden en de berekende waarden. t is de enige beïnvloedende factor: tijd.

Hoe uitgesprokener de tendens van de indicator om te groeien of te dalen, hoe groter de coëfficiënt a 1 zal zijn. Dienovereenkomstig wordt aangenomen dat de constante a 0 samen met de willekeurige component Ɛ de resterende regressie-invloeden weerspiegelt, naast de tijd, dat wil zeggen alle andere mogelijke beïnvloedende factoren.

De modelcoëfficiënten kunnen worden berekend met behulp van de standaard kleinste kwadratenmethode (LSM). Microsoft Excel kan al deze berekeningen met een knal aan, en om een ​​lineair trendmodel of een kant-en-klare voorspelling te krijgen, zijn er maar liefst vijf methoden, die we hieronder afzonderlijk zullen bespreken.

Grafische methode voor het verkrijgen van een lineaire trend

In dit en alle verdere voorbeelden zullen we dezelfde dynamische reeks gebruiken: het niveau van het bbp, dat in ons geval jaarlijks wordt berekend en geregistreerd; het onderzoek zal plaatsvinden in de periode van 2004 tot 2012;

Laten we nog een kolom toevoegen aan de oorspronkelijke gegevens, die we t zullen noemen en in oplopende getallen de serienummers van alle geregistreerde BBP-waarden voor de opgegeven periode van 2004 tot 2012 markeren. – 9 jaar of 9 periodes.

Excel voegt een leeg veld toe - markup voor de toekomstige grafiek, selecteer deze grafiek en activeer het tabblad dat in de menubalk verschijnt - Constructeur, op zoek naar een knop Selecteer gegevens, druk in het geopende venster op de knop Toevoegen. Er verschijnt een pop-upvenster waarin u wordt gevraagd gegevens te selecteren om een ​​diagram te maken. Als veldwaarde Serienaam selecteer de cel die de tekst bevat die het beste overeenkomt met de naam van de grafiek. In het veld X-waarden geef het interval van cellen in kolom t aan – de beïnvloedende factor. In het veld Y-waarden We geven het interval aan van kolomcellen met bekende waarden van het BBP (Y) - de indicator die wordt bestudeerd.

Nadat u de opgegeven velden heeft ingevuld, drukt u meerdere keren op de OK-knop en krijgt u een kant-en-klare dynamiekgrafiek. Selecteer nu de grafieklijn zelf met de rechtermuisknop en selecteer het item uit het contextmenu dat verschijnt Voeg een trendlijn toe

Er wordt een venster geopend om de parameters te configureren voor het construeren van een trendlijn, waar we een van de modeltypen selecteren Lineair, zet een vinkje bij items P een vergelijking in een diagram weergeven En Plaats de geschatte betrouwbaarheidswaarde R2 in het diagram, zal dit voldoende zijn om de reeds geconstrueerde trendlijn in de grafiek weer te geven, evenals een wiskundige versie van de weergave van het model in de vorm van een kant-en-klare vergelijking en een indicator voor de kwaliteit van het model R2. Als u geïnteresseerd bent in het weergeven van de voorspelling in een grafiek om de kloof tussen de onderzochte indicator visueel te beoordelen, geef dit dan in het veld aan Voorspelling vooruit voor aantal renteperioden.

Eigenlijk draait het allemaal om deze methode, je kunt er natuurlijk aan toevoegen dat de weergegeven lineaire trendvergelijking het model zelf is, dat kan worden gebruikt als formule om berekende waarden uit het model te verkrijgen en dienovereenkomstig nauwkeurige voorspelde waarden ​(de voorspelling die in de grafiek wordt weergegeven, kan slechts bij benadering worden geschat), wat we hebben gedaan in het voorbeeld bij het artikel.

Een lineaire trend opbouwen met behulp van de LIJNSCH-formule

De essentie van deze methode komt neer op het zoeken naar lineaire trendcoëfficiënten met behulp van de functie LIJNSCH Door deze beïnvloedende coëfficiënten in de vergelijking te vervangen, verkrijgen we een voorspellend model.

We zullen twee aangrenzende cellen moeten selecteren (in de schermafbeelding zijn dit de cellen A38 en B38), en vervolgens in de formulebalk bovenaan (rood gemarkeerd in de schermafdruk hierboven) roepen we de functie aan door “=LIJNSCH(”, achter welke Excel hints zal weergeven over wat nodig is voor deze functies, namelijk:

1. selecteer een bereik met bekende waarden van de beschreven indicator Y (in ons geval het bbp, het bereik is blauw gemarkeerd in de schermafbeelding) en plaats een puntkomma
2. geef het bereik van beïnvloedende factoren X aan (in ons geval is dit de t-indicator, het serienummer van perioden, groen gemarkeerd in de schermafbeelding) en plaats een puntkomma
3. de volgende vereiste parameter voor de functie is het bepalen of de constante moet worden berekend, aangezien we in eerste instantie een model beschouwen met een constante (coëfficiënt een 0 ), typ dan 'TRUE' of '1' en een puntkomma
4. Vervolgens moeten we aangeven of de berekening van statistische parameters vereist is (als we deze optie zouden overwegen, zouden we in eerste instantie een bereik “voor de formule” een paar regels hieronder moeten toewijzen). Geef de noodzaak aan om statistische parameters te berekenen, namelijk standaardfoutwaarde voor coëfficiënten, determinismecoëfficiënt, standaardfout voor Y, Fisher-criterium, vrijheidsgraden, enz., hebben ze alleen zin als je begrijpt wat ze betekenen. In dat geval stellen we 'WAAR' of '1' in. In het geval van vereenvoudigde modellering, die we proberen te leren, stelt u in deze fase van het schrijven van de formule “FALSE” of “0” in en voegt u na het afsluitende haakje “)” toe.
5. om de formule "nieuw leven in te blazen", dat wil zeggen om hem te laten werken nadat alle benodigde parameters zijn opgegeven, is het niet voldoende om op de Enter-knop te drukken, u moet achtereenvolgens drie toetsen indrukken: Ctrl, Shift, Enter

Zoals je in de bovenstaande schermafbeelding kunt zien, zijn de cellen die we voor de formule hebben geselecteerd, gevuld met de berekende waarden van de regressiecoëfficiënten voor de lineaire trend, in de cel B38 de coëfficiënt wordt gevonden een 0 en in de cel A38- afhankelijkheidscoëfficiënt van de parameter T (of X ), dat is een 1 . We vervangen de verkregen waarden in de vergelijking van de lineaire functie en krijgen het voltooide model in wiskundige uitdrukking - y = 169.572,2+138.454,3*t

Om berekende waarden te krijgen Y volgens het model en dienovereenkomstig, om een ​​voorspelling te krijgen, hoeft u alleen maar de formule in een Excel-cel te vervangen, en in plaats daarvan T geef een link aan naar de cel met het gewenste periodenummer (zie cel in de schermafbeelding). D25).

Om het resulterende model met echte gegevens te vergelijken, kunt u twee grafieken maken, waarbij u als X het serienummer van de periode aangeeft, en als Y in het ene geval het reële bbp en in het andere geval het berekende bbp (in de schermafbeelding het diagram rechts).

Een lineaire trend opbouwen met behulp van de Regressietool in het Analysepakket

Het artikel beschrijft deze methode in feite volledig, het enige verschil is dat er in onze initiële gegevens slechts één beïnvloedende factor is X (periodenummer – T ).

Zoals je op de foto hierboven kunt zien, reeks gegevens met bekende bbp-waarden gemarkeerd als invoerinterval Y en de bijbehorende bereik met periodenummers t – als invoerinterval X. De resultaten van berekeningen door het Analysepakket worden op een apart blad weergegeven en zien eruit als een reeks tabellen (zie onderstaande afbeelding) waarin we geïnteresseerd zijn in de cellen die ik in geel en groen heb geschilderd. Naar analogie met de procedure beschreven in het bovenstaande artikel wordt uit de verkregen coëfficiënten een lineair trendmodel samengesteld y=169 572,2+138 454,3*t, op basis waarvan voorspellingen worden gedaan.

Voorspelling met behulp van een lineaire trend via de TREND-functie

Deze methode verschilt van de voorgaande doordat het de eerder noodzakelijke stappen overslaat van het berekenen van de modelparameters en het handmatig vervangen van de verkregen coëfficiënten als een formule in een cel om een ​​voorspelling te verkrijgen. Deze functie produceert een kant-en-klare berekende voorspellingswaarde op basis van bekend data bron.

In de doelcel (de cel waar we het resultaat willen zien) plaatsen we een bordje gelijk aan en roep de magische functie aan door “ TREND(", dan moet je markeren, dat wil zeggen, nadat we een puntkomma en hebben geplaatst selecteer een bereik met bekende X-waarden, dat wil zeggen met periodenummers t, die overeenkomen met een kolom met bekende BBP-waarden, plaatst u opnieuw een puntkomma en selecteert u de cel met het nummer van de periode waarvoor we een voorspelling doen (in ons geval kan het periodenummer echter niet worden aangegeven door een link naar de cel, maar gewoon door een getal direct in de formule), plaats dan nog een puntkomma en geef aan WAAR of 1 , als bevestiging voor het berekenen van de coëfficiënt een 0 eindelijk zetten we haakje sluiten en druk op de toets Binnenkomen.

Het nadeel van deze methode is dat deze noch de modelvergelijking, noch de coëfficiënten ervan toont. Daarom kunnen we niet zeggen dat we op basis van dat en dat model een bepaalde voorspelling hebben ontvangen, net zoals er geen weerspiegeling is van de kwaliteit parameters van het model, de determinatiecoëfficiënt waarmee men zou kunnen zeggen of het zinvol is om rekening te houden met de resulterende voorspelling of niet.

Voorspelling met behulp van een lineaire trend met behulp van de FORECAST-functie

De essentie van deze functie is volledig identiek aan de vorige, het enige verschil zit in de volgorde waarin de initiële gegevens in de formule worden geschreven en in het feit dat er geen instelling is voor de aan- of afwezigheid van een coëfficiënt een 0 (dat wil zeggen, de functie impliceert dat deze coëfficiënt in ieder geval bestaat)

Zoals je in de bovenstaande figuur kunt zien, schrijven we “ =VOORSPELLING("en dan aangeven cel met periodenummer, waarvoor het nodig is om de waarde te berekenen volgens een lineaire trend, dat wil zeggen een voorspelling, waarna we een puntkomma plaatsen en vervolgens selecteren bereik van bekende Y-waarden, dat is kolom met bekende BBP-waarden, plaats dan een puntkomma en markeer bereik met bekende X-waarden, dat is met periodenummers t, die overeenkomen met de kolom met bekende BBP-waarden en ten slotte stellen we deze in haakje sluiten en druk op de toets Binnenkomen.

De verkregen resultaten zijn, net als bij de bovenstaande methode, slechts het eindresultaat van het berekenen van de voorspelde waarde met behulp van een lineair trendmodel. Het vertoont geen fouten of het model zelf in wiskundige termen;

Om het artikel samen te vatten

We kunnen zeggen dat elk van de methoden onder andere de meest acceptabele kan zijn, afhankelijk van het huidige doel dat we onszelf stellen. De eerste drie methoden kruisen elkaar zowel in betekenis als in resultaten, en zijn geschikt voor elk min of meer serieus werk waarbij een beschrijving van het model en de kwaliteit ervan noodzakelijk is. De laatste twee methoden zijn op hun beurt ook identiek aan elkaar en geven u zo snel mogelijk antwoord op bijvoorbeeld de vraag: “Wat is de verkoopvoorspelling voor volgend jaar?”

Om prijsontwikkelingen visueel te illustreren, wordt een trendlijn gebruikt. Het element van technische analyse is een geometrisch beeld van de gemiddelde waarden van de geanalyseerde indicator.

Laten we eens kijken hoe u een trendlijn aan een diagram in Excel kunt toevoegen.

Een trendlijn aan een diagram toevoegen

Laten we bijvoorbeeld de gemiddelde olieprijzen sinds 2000 uit open bronnen nemen. Laten we de gegevens voor analyse in de tabel invoeren:

Een trendlijn in Excel is een grafiek van een passende functie. Waarom is het nodig: voorspellingen maken op basis van statistische gegevens. Voor dit doel is het noodzakelijk om de lijn uit te breiden en de waarden ervan te bepalen.

Als R2 = 1, dan is de benaderingsfout nul. In ons voorbeeld gaf de keuze voor lineaire benadering een lage betrouwbaarheid en slechte resultaten. De voorspelling zal onnauwkeurig zijn.

Aandacht!!! U kunt geen trendlijn toevoegen aan de volgende typen grafieken en diagrammen:

• bloemblad;
• circulaire;
• oppervlak;
• ringvormig;
• volume;
• met accumulatie.

Trendlijnvergelijking in Excel

In het bovenstaande voorbeeld werd alleen voor lineaire benadering gekozen om het algoritme te illustreren. Zoals uit de betrouwbaarheidswaarde bleek, was de keuze niet geheel succesvol.

U moet het weergavetype kiezen dat de trend in gebruikersinvoer het meest nauwkeurig illustreert. Laten we eens kijken naar de opties.

Lineaire benadering

Het geometrische beeld is een rechte lijn. Daarom wordt lineaire benadering gebruikt om een ​​indicator te illustreren die met een constante snelheid toeneemt of afneemt.

Laten we eens kijken naar het voorwaardelijke aantal contracten dat de manager over een periode van 10 maanden heeft gesloten:

Op basis van de gegevens in de Excel-tabel zullen we een spreidingsdiagram maken (dit zal het lineaire type helpen illustreren):

Selecteer de grafiek - "trendlijn toevoegen". Selecteer lineair type in de parameters. Voeg de betrouwbaarheidswaarde van de benadering en de trendlijnvergelijking toe in Excel (vink gewoon de vakjes onder aan het venster "Parameters" aan).

We krijgen het resultaat:

Opmerking! Bij het lineaire benaderingstype bevinden de gegevenspunten zich zo dicht mogelijk bij de rechte lijn. Deze weergave gebruikt de volgende vergelijking:

y = 4,503x + 6,1333

• waarbij 4,503 de hellingsindex is;
• 6.1333 – verplaatsingen;
• y – reeks waarden,
• x – periodenummer.

De rechte lijn in de grafiek laat een gestage toename zien in de kwaliteit van het werk van de manager. De betrouwbaarheidswaarde van de benadering is 0,9929, wat duidt op een goede overeenkomst tussen de berekende lijn en de originele gegevens. De voorspellingen moeten accuraat zijn.

Om het aantal afgesloten contracten te voorspellen, bijvoorbeeld in periode 11, moet u het getal 11 in plaats van x in de vergelijking invullen. Tijdens de berekeningen komen we erachter dat deze manager in de 11e periode 55-56 contracten zal afsluiten.

Exponentiële trendlijn

Dit type is handig als de invoerwaarden steeds sneller veranderen. Exponentiële aanpassing wordt niet gebruikt als er nul of negatieve kenmerken zijn.

Laten we een exponentiële trendlijn in Excel bouwen. Laten we bijvoorbeeld de voorwaardelijke waarden van de productieve elektriciteitsvoorziening in regio X nemen:

We zijn een schema aan het maken. Voeg een exponentiële lijn toe.

De vergelijking ziet er als volgt uit:

y = 7,6403е^-0,084x

• waarbij 7,6403 en -0,084 constanten zijn;
• e is de basis van de natuurlijke logaritme.

De betrouwbaarheidsindicator voor de benadering was 0,938 – de curve komt overeen met de gegevens, de fout is minimaal, de voorspellingen zullen nauwkeurig zijn.

Logaritmische trendlijn in Excel

Het wordt gebruikt voor de volgende veranderingen in de indicator: eerst snelle groei of afname, daarna relatieve stabiliteit. De geoptimaliseerde curve past zich goed aan dit ‘gedrag’ van de hoeveelheid aan. De logaritmische trend is geschikt voor het voorspellen van de verkoop van een nieuw product dat net op de markt is geïntroduceerd.

In de beginfase is het de taak van de fabrikant om het klantenbestand te vergroten. Wanneer een product een eigen koper heeft, moet het worden behouden en geserveerd.

Laten we een grafiek maken en een logaritmische trendlijn toevoegen om de verkoop van een voorwaardelijk product te voorspellen:

R2 ligt qua waarde dicht bij 1 (0,9633), wat een minimale benaderingsfout aangeeft. Laten we de verkoopvolumes in de daaropvolgende perioden voorspellen. Om dit te doen, moet u het periodenummer in de vergelijking vervangen in plaats van x.

Bijvoorbeeld:

Om de prognosecijfers te berekenen werd een formule gebruikt in de vorm: =272,14*LN(B18)+287,21. Waarbij B18 het periodenummer is.

Polynomiale trendlijn in Excel

Deze curve wordt gekenmerkt door variabele stijgingen en dalingen. Voor polynomen (polynomen) wordt de graad bepaald (door het aantal maximale en minimale waarden). Eén extremum (minimum en maximum) is bijvoorbeeld de tweede graad, twee extrema zijn de derde graad, drie zijn de vierde.

Polynomiale trend in Excel wordt gebruikt om een ​​grote set gegevens over een onstabiele hoeveelheid te analyseren. Laten we eens kijken naar het voorbeeld van de eerste reeks waarden (olieprijzen).

Om een ​​dergelijke waarde voor de betrouwbaarheid van de benadering (0,9256) te verkrijgen, was het noodzakelijk deze op graad 6 in te stellen.

Maar deze trend stelt ons in staat min of meer nauwkeurige voorspellingen te doen.