Hoe trendlijnen te tekenen in Excel. Een trendfunctie bouwen in Excel

Trend onmiddellijk na het invoeren van de beschikbare gegevens in de array. Om dit te doen, selecteert u op het blad met de gegevenstabel ten minste twee cellen van het bereik waarvoor de grafiek zal worden gebouwd, en voegt u onmiddellijk daarna het diagram in. U kunt diagrammen gebruiken zoals grafieken, spreidingsdiagrammen, histogrammen, bellendiagrammen en aandelen. Andere typen diagrammen ondersteunen de trendfunctie niet.

Selecteer Trendlijn toevoegen in het menu Grafiek. In het geopende venster selecteert u op het tabblad “Type” het gewenste type trendlijn, wat in wiskundig equivalent ook de methode voor gegevensbenadering betekent. Wanneer u de beschreven methode gebruikt, moet u dit "met het oog" doen, omdat U hebt geen wiskundige berekeningen uitgevoerd om de grafiek te construeren.

Zoek daarom gewoon uit met welk type functie de grafiek van de beschikbare gegevens het meest overeenkomt: lineair, logaritmisch, exponentieel of anders. Als u twijfelt over het kiezen van het type benadering, kunt u verschillende lijnen tekenen en voor een grotere nauwkeurigheid van de voorspelling kunt u op het tabblad 'Parameters' van hetzelfde venster het vakje 'plaats de betrouwbaarheidswaarde van de benadering (R^2) aanvinken'. het diagram.”

Door R^2-waarden voor verschillende lijnen te vergelijken, kunt u het type grafiek kiezen dat uw gegevens het nauwkeurigst karakteriseert en daardoor de meest betrouwbare voorspelling oplevert. Hoe dichter de R^2-waarde bij één ligt, hoe nauwkeuriger u het lijntype hebt gekozen. Hier moet u op het tabblad “Parameters” de periode opgeven waarvoor de prognose wordt gemaakt.

Deze methode om een trend te construeren is zeer bij benadering, dus het is beter om op zijn minst de meest primitieve statistische verwerking van de beschikbare gegevens uit te voeren. Hierdoor kunt u een nauwkeurigere voorspelling maken.

Als u ervan uitgaat dat de beschikbare gegevens worden beschreven door een lineaire vergelijking, selecteert u deze eenvoudigweg met de cursor en vult u deze automatisch in voor het vereiste aantal punten of aantal cellen. In dit geval is het niet nodig om de waarde van R^2 te vinden, omdat u hebt de voorspelling eerder aangepast aan de vergelijking van een rechte lijn.

Als u denkt dat de bekende waarden van een variabele het beste kunnen worden beschreven met behulp van een exponentiële vergelijking, selecteert u ook het oorspronkelijke bereik en vult u automatisch het vereiste aantal cellen in door de rechtermuisknop ingedrukt te houden. Met automatisch aanvullen kunt u geen andere typen lijnen maken dan de twee aangegeven.

Voor de grootste nauwkeurigheid bij het opstellen van een voorspelling moet u daarom een van de verschillende statistische functies gebruiken: “PREDICTION”, “TREND”, “GROWTH”, “LIJNSCH” of “LGRFPRIBL”. In dit geval moet u de waarde voor elke volgende prognoseperiode handmatig berekenen. Als u complexere regressieanalyses op uw gegevens moet uitvoeren, heeft u de Analysis Pack-invoegtoepassing nodig, die niet is opgenomen in de standaard MS Office-installatie.

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, om het bedrag van de arbeidskosten voor het verzamelen van dozen van een bestelling vast te stellen, timingwaarnemingen uitgevoerd. 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” (Afb. 2).

Een trendlijn toevoegen, afb. 2

In het venster dat verschijnt voor het instellen van het trendlijnformaat (Afb. 3) werden achtereenvolgens 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 betrouwbaarheidswaarde van de benadering (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 waarden van de bestede tijd, 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.

Een trend is een patroon dat de opkomst of ondergang van een indicator in de loop van de tijd beschrijft. Als je een dynamische reeks (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 dynamische reeks neigt (respectievelijk naar stijging of daling).

Trend als model

Als je een model bouwt dat dit fenomeen beschrijft, krijg je een vrij eenvoudig en zeer 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 de verandering in 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. Een fout is in wezen het verschil tussen de werkelijke Y-waarden en de berekende waarden. t – de enige beïnvloedende factor is 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 op zichzelf prima 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:

selecteer een bereik met bekende waarden van de beschreven indicator Y (in ons geval het bbp, in de schermafbeelding is het bereik blauw gemarkeerd) en plaats een puntkomma
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
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
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.
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 op drie toetsen drukken: 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 wil zeggen 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 nauwkeurig een kant-en-klare berekende voorspellingswaarde op basis van bekende brongegevens.

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 te verwijzen 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 wil zeggen kolom met bekende BBP-waarden, plaats dan een puntkomma en markeer bereik met bekende X-waarden, dat wil zeggen 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 het “mooi te maken”? Helemaal niet - de hoofdtaak van het diagram is om u in staat te stellen obscure getallen in een gemakkelijk te verteren grafische vorm te presenteren. Zodat de stand van zaken in één oogopslag duidelijk is en u geen tijd hoeft te verspillen aan het bestuderen van droge statistieken.

Een ander groot voordeel van diagrammen is dat het met hun hulp veel gemakkelijker is om trends weer te geven, dat wil zeggen om een voorspelling voor de toekomst te maken. Als het het hele jaar goed gaat, is er eigenlijk geen reden om aan te nemen dat het beeld in het volgende kwartaal plotseling zal omkeren.

Hoe grafieken en grafieken ons misleiden

Diagrammen (vooral als het gaat om het visueel weergeven van grote hoeveelheden gegevens), hoewel ze uiterst gemakkelijk te begrijpen zijn, zijn echter niet altijd voor de hand liggend.

Ik zal mijn woorden illustreren met een eenvoudig voorbeeld:

Deze tabel toont het gemiddelde aantal bezoekers op een bepaalde site per dag per maand, evenals het aantal pageviews per bezoeker. Het is logisch dat er altijd meer paginaweergaven dan bezoekers moeten zijn, aangezien één gebruiker meerdere pagina's tegelijk kan bekijken.

Het is even logisch dat hoe meer pagina's een bezoeker bekijkt, hoe beter de site is: het trekt de aandacht van de gebruiker en dwingt hem om dieper te lezen.

Wat ziet de site-eigenaar in ons diagram? Dat het goed met hem gaat! In de zomermaanden was er sprake van een seizoensgebonden daling van de belangstelling, maar in de herfst keerden de aantallen terug en overtroffen zelfs die van de lente. Conclusies? We gaan door in dezelfde geest en zullen snel succes boeken!

Is het schema duidelijk? Nogal. Maar is het duidelijk? Laten we het uitzoeken.

Trends begrijpen in MS Excel

Een grote fout van de site-eigenaar zou zijn om het diagram te nemen zoals het is. Ja, het blote oog kan zien dat de blauwe en oranje kolommen van “herfst” zijn gegroeid in vergelijking met “lente” en nog meer “zomer”. Niet alleen de aantallen en de grootte van de staven zijn echter belangrijk, maar ook de relatie daartussen. Dat wil zeggen dat idealiter met de algehele groei de ‘oranje’ kolommen met opvattingen veel sterker zouden moeten worden dan de ‘blauwe’, wat zou betekenen dat de site niet alleen meer lezers trekt, maar ook groter en interessanter wordt.

Wat zien we in de grafiek? De oranje kolommen van ‘herfst’ zijn in ieder geval niet meer dan die van ‘lente’, zo niet minder. Dit duidt niet op succes, maar eerder het tegenovergestelde: bezoekers komen aan, maar lezen gemiddeld minder en blijven niet op de site!

Het is tijd om alarm te slaan en... kennis te maken met zoiets als trendlijn.

Waarom is een trendlijn nodig?

Een trendlijn “op een eenvoudige manier” is een doorlopende lijn die wordt samengesteld op basis van waarden die gemiddeld zijn op basis van speciale algoritmen waaruit onze grafiek is opgebouwd. Met andere woorden, als onze gegevens over drie rapportagepunten ‘springen’ van ‘-5’ naar ‘0’ en vervolgens naar ‘+5’, zullen we eindigen met een vrijwel vlakke lijn: de ‘voordelen’ van de situatie zijn duidelijk breng de “nadelen” in evenwicht.

Op basis van de richting van de trendlijn is het veel gemakkelijker om de werkelijke stand van zaken te zien en diezelfde trends te zien, en dus voorspellingen te doen voor de toekomst. Nou, laten we dan aan het werk gaan!

Hoe een trendlijn te tekenen in MS Excel

Klik met de rechtermuisknop op een van de “blauwe” kolommen en selecteer het item in het contextmenu "Trendlijn toevoegen".

Het grafiekblad geeft nu een gestippelde trendlijn weer. Zoals u kunt zien, valt het niet 100% samen met de waarden van het diagram - opgebouwd met behulp van gewogen gemiddelden, herhaalt het slechts grofweg zijn richting. Dit belet ons echter niet om een gestage toename van het aantal bezoeken aan de site te zien - zelfs de 'zomer'-daling heeft geen invloed op het algehele resultaat.

Trendlijn voor de kolom Bezoekers

Laten we nu dezelfde truc herhalen met de “oranje” kolommen en een tweede trendlijn bouwen. Zoals ik al eerder zei: de situatie hier is niet zo goed. De trend laat duidelijk zien dat tijdens de berekeningsperiode het aantal views niet alleen niet toenam, maar zelfs begon te dalen – langzaam maar zeker.

Een andere trendlijn helpt de situatie te verduidelijken

Als we de trendlijn voor de komende maanden mentaal voortzetten, zullen we tot een teleurstellende conclusie komen: het aantal geïnteresseerde bezoekers zal blijven dalen. Omdat gebruikers hier niet blijven, zal een afname van de belangstelling voor de site in de nabije toekomst onvermijdelijk leiden tot een afname van het verkeer.

Daarom moet de projecteigenaar dringend onthouden wat hij in de zomer heeft gedaan ("in de lente" was alles heel normaal, te oordelen naar het schema) en dringend maatregelen nemen om de situatie te corrigeren.

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 (afnemend, stijgend, etc.) wordt een interpolatiemethode geselecteerd die moet worden gebruikt 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.

Vermogensfunctie: 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 geconstrueerd, een polynomiale trendlijn van de derde orde (gedefinieerd 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.