Om het “mooi te maken”? Helemaal niet - de hoofdtaak Met grafieken kunt u obscure cijfers op een gemakkelijk te begrijpen manier presenteren grafische vorm. 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 een voorspelling te doen voor de toekomst. 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

Echter, grafieken (vooral als het gaat om visuele representatie grote hoeveelheid gegevens), hoewel uiterst handig voor perceptie, zijn niet altijd duidelijk.

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 niet minder logisch dat dan meer pagina's Hoe de bezoeker bekijkt, hoe beter de site. Het trekt de aandacht van de gebruiker en dwingt hem om dieper in het lezen te gaan.

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 “in eenvoudige bewoordingen” is een ononderbroken lijn die wordt opgesteld 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’, krijgen we uiteindelijk bijna rechte lijn: De “voordelen” van de situatie wegen uiteraard op tegen de “nadelen”.

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 klik met de rechtermuisknop muis op een van de “blauwe” kolommen, en in contextmenu selecteer item "Trendlijn toevoegen".

Het kaartblad wordt nu weergegeven stippellijn trend. Zoals u kunt zien, valt het niet 100% samen met de waarden van de grafiek - opgebouwd met behulp van gewogen gemiddelden, is het slechts een klein beetje algemeen overzicht herhaalt zijn richting. Dit belet ons echter niet om een ​​gestage toename van het aantal bezoeken aan de site te zien eindresultaat Zelfs de “zomer”-daling heeft geen effect.

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 zich dringend herinneren wat hij in de zomer verkeerd heeft gedaan ("in de lente" was alles heel normaal, te oordelen naar het schema) en dringend maatregelen nemen om de situatie te corrigeren.

Meest voorkomend tendens lijkt lineaire afhankelijkheid 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, enz.) 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 lineaire functie“best fit” loopt door de punten van de grafiek die overeenkomen met het minimum van de som van de 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 uit 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 eens 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 nieuwe rij 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 vergelijking van reeksgrafieken initiële waarden(rij 3) en driepunts voortschrijdende gemiddelden (rij 4), is het duidelijk dat de fluctuaties kunnen worden afgevlakt. Hoe groter aantal Er zullen punten betrokken zijn bij het berekeningsbereik 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 minder waarde constante α, hoe minder gevoelig het is voor trendveranderingen in een bepaalde tijdreeks.

Als u een gebeurtenis wilt voorspellen op basis van bestaande gegevens, kunt u, als er geen tijd voor is, een trendlijn gebruiken. Hiermee kunt u visueel begrijpen welke dynamiek de gegevens hebben waaruit de grafiek is opgebouwd. Het Microsoft-softwarepakket heeft een prachtig Excel-mogelijkheden, waarmee u met deze tool een redelijk nauwkeurige voorspelling kunt maken: trendlijn in Excel. Het bouwen van deze analysetool is vrij eenvoudig, hieronder wordt gegeven gedetailleerde beschrijving proces en soorten trendlijnen.

Trendlijn in Excel. Constructie proces

Een trendlijn is een van de belangrijkste hulpmiddelen voor gegevensanalyse

Om een ​​trendlijn te vormen, is het noodzakelijk om drie fasen te voltooien, namelijk:
1. Maak een tabel;
2.
3. Selecteer het type trendlijn.

Na alles verzameld te hebben Nodige informatie, kunt u direct doorgaan met de stappen om het eindresultaat te verkrijgen.

Eerst moet u een tabel maken met de brongegevens. Selecteer vervolgens het gewenste bereik en ga naar het tabblad "Invoegen" en selecteer de functie "Grafiek". Na de bouw kunt u aan de slag met het eindresultaat Extra functies, in de vorm van kopjes en bijschriften. Om dit te doen, klikt u eenvoudig met de linkermuisknop op het diagram, selecteert u het tabblad met de naam “Ontwerper” en selecteert u “Lay-out”. Het volgende dat u hoeft te doen, is eenvoudigweg de titel invoeren.

De volgende stap is het construeren van de trendlijn zelf. Om dit te doen, moet u het diagram opnieuw markeren en het tabblad “Lay-out” op het taaklint selecteren. Volgende binnen dit menu u moet op de knop "Trendlijn" klikken en "lineaire benadering" of "exponentiële benadering" selecteren.

Diverse l-variatiestrendlijnen

Afhankelijk van de kenmerken van de door de gebruiker ingevoerde gegevens, is het de moeite waard om een ​​van de gepresenteerde opties te kiezen; hieronder vindt u een beschrijving van de soorten trendlijnen
Exponentiële benadering. Als de veranderingssnelheid in de invoergegevens toeneemt, en continu, dan is dat het geval deze lijn zal het nuttigst zijn. Als de gegevens die in de tabel zijn ingevoerd echter nul of negatieve kenmerken bevatten, dit type onaanvaardbaar.

Lineaire benadering. Deze lijn is recht van aard en wordt meestal gebruikt in elementaire gevallen waarin de functie met ongeveer constante snelheid toeneemt of afneemt.

Logaritmische benadering. Als de waarde aanvankelijk correct en snel groeit, of juist daalt, maar na een bepaalde waarde stabiliseert, dan zal deze trendlijn van pas komen.

Polynomiale benadering. Variabele stijging en daling zijn de kenmerken die kenmerkend zijn voor deze lijn. Bovendien wordt de graad van de polynomen (polynomen) zelf bepaald door het aantal maxima en minima.

Vermogensbenadering. Karakteriseert de monotone stijging en daling van een waarde, maar de toepassing ervan is onmogelijk als de gegevens negatieve en nulwaarden hebben.

voortschrijdend gemiddelde. Het wordt gebruikt om duidelijk de directe afhankelijkheid van elkaar aan te tonen, door alle oscillatiepunten af ​​te vlakken. Dit wordt bereikt door de gemiddelde waarde tussen twee aangrenzende punten te nemen. Zo wordt de grafiek gemiddeld en wordt het aantal punten teruggebracht tot de waarde die door de gebruiker in het menu “Punten” is geselecteerd.

Hoe gebruik je het? D Om economische opties te voorspellen, is het precies zo polynoom een lijn waarvan de polynomiale graad wordt bepaald op basis van verschillende principes: het maximaliseren van de determinatiecoëfficiënt, evenals de economische dynamiek van de indicator gedurende de periode waarvoor de voorspelling vereist is.

Door alle stadia van de vorming te volgen en de kenmerken te begrijpen, kunt u slechts een primaire trendlijn opbouwen, die slechts vaag overeenkomt met echte voorspellingen. Maar na het instellen van de parameters kunnen we al over meer praten echte foto voorspelling.

Trendlijn in Excel. Parameterinstellingenin de functionele lijn

Door op de knop “Trendlijn” te klikken, selecteert u het gewenste menu genaamd “ Extra opties" In het venster dat verschijnt, klikt u op “Trendlijnformaat” en vinkt u vervolgens het vakje aan naast de waarde “plaats de geschatte betrouwbaarheidswaarde R^2 in het diagram”. Sluit daarna het menu door op de bijbehorende knop te klikken. Op het diagram zelf verschijnt de coëfficiënt R^2 = 0,6442.

Hierna annuleren wij de aangebrachte wijzigingen. Door het diagram te selecteren en op het tabblad “Lay-out” te klikken, klik vervolgens op “Trendlijn” en klik op “Nee”. Ga vervolgens naar de functie “Trendlijn opmaken” en klik op polynoom lijn en probeer de waarde R^2= 0,8321 te bereiken door de graad te veranderen.

Om formules te bekijken of andere varianten van voorspellingen te maken die verschillen van de standaardvoorspellingen, volstaat het om niet bang te zijn om met waarden te experimenteren, en vooral met polynomen. Dus slechts één gebruiken Excel-programma, kunt u op basis van de invoergegevens een redelijk nauwkeurige voorspelling maken.

Grafieken

Regressie analyse

Regressievergelijking Y van X genaamd functionele afhankelijkheid y=f(x), en de grafiek is een regressielijn.

Met Excel kunt u heel eenvoudig diagrammen en grafieken maken aanvaardbare kwaliteit. Excel beschikbaar speciaal middel- Grafiekwizard, die de gebruiker door alle vier fasen van het proces van het maken van een diagram of grafiek leidt.

In de regel begint het plotten met het selecteren van een bereik dat de gegevens bevat waarop het moet worden geplot. Deze start vereenvoudigt het verdere verloop van het plotten. Het bereik met de originele gegevens kan echter in de tweede fase van de dialoog worden verdeeld DIAGRAMMEESTER. In Excel2003 DIAGRAMMEESTER bevindt zich in het menu als een knop of er kan een diagram worden gemaakt door op het tabblad te klikken INVOEGEN en zoek het item in de lijst die wordt geopend DIAGRAM. In Excel 2007 vinden we het tabblad ook INVOEGEN(Afb. 31).

Rijst. 31. DIAGRAMMEESTER in Excel2007

De eenvoudigste manier is om een ​​reeks brongegevens te selecteren waarin deze gegevens zich in aangrenzende rijen (kolommen of rijen) bevinden. U moet op de cel linksboven van het bereik klikken en vervolgens de muisaanwijzer naar de cel rechtsonder slepen. het bereik. Wanneer u gegevens selecteert die zich in niet-aangrenzende rijen bevinden, sleept u de muisaanwijzer langs de geselecteerde rijen terwijl u ingedrukt houdt Ctrl-toets. Als een van de gegevensreeksen een cel met een titel heeft, moeten de overige geselecteerde reeksen ook een overeenkomstige cel hebben, zelfs als deze leeg is.

Voor regressie analyse U kunt het beste een spreidingsdiagram gebruiken (Fig. 30). Met haar Excel bouwen beschouwt de eerste rij van het geselecteerde bereik met brongegevens als een set argumentwaarden van de functies waarvan de grafieken moeten worden geplot (dezelfde set voor alle functies). De volgende rijen worden gezien als sets waarden van de functies zelf (elke rij bevat de waarden van een van de corresponderende functies gegeven waarden argument in de eerste rij van het geselecteerde bereik).

In Excel 2007 worden de asnamen op het menutabblad geplaatst INDELING(Afb. 32).

Rijst. 32. De namen van grafiekassen instellen in Excel 2007

Om een ​​wiskundig model te verkrijgen, is het noodzakelijk om een ​​trendlijn in de grafiek te tekenen. In Excel 2003 en 2007 moet u met de rechtermuisknop op de grafiekpunten klikken. Vervolgens verschijnt in Excel 2003 een tabblad met een lijst met items waaruit we selecteren TRENDLIJN TOEVOEGEN(Afb. 33).

Rijst. 33. TRENDLIJN TOEVOEGEN

Nadat u op het artikel heeft geklikt TRENDLIJN TOEVOEGEN er verschijnt een venster TRENDLIJN(Afb. 34). Op het tabblad TYPE kunt u selecteren volgende typen lijnen: lineair, logaritmisch, exponentieel, macht, polynoom, lineaire filtering.

Rijst. 34. Venster TRENDLIJN in Excel2003

Op het tabblad OPTIES(Fig. 35) vink het vakje aan naast de items TOON VERGELIJKING OP DIAGRAM, dan zal het in de grafiek verschijnen wiskundig model deze afhankelijkheid. We plaatsen ook een selectievakje naast het item TOON IN HET DIAGRAM DE WAARDE VAN DE BETROUWBAARHEID VAN DE BENADERING (R^2). Hoe dichter de betrouwbaarheidswaarde bij benadering bij 1 ligt, hoe dichter de geselecteerde curve de punten in de grafiek benadert. Klik vervolgens op de knop OK. Een trendlijn, de bijbehorende vergelijking en de betrouwbaarheidswaarde van de benadering verschijnen in de grafiek.

Rijst. 35. Tab OPTIES

Nadat we in Excel 2007 met de rechtermuisknop op de grafiekpunten hebben geklikt, verschijnt er een lijst met menu-items waaruit SELECTEER TRENDLIJN TOEVOEGEN(Afb. 36).

Rijst. 36. TRENDLIJN TOEVOEGEN

Rijst. 37. Tab TRENDLIJNPARAMETERS

Vink de gewenste vakjes aan en druk op de knop DICHTBIJ.

Een trendlijn, de bijbehorende vergelijking en de betrouwbaarheidswaarde van de benadering verschijnen in de grafiek.

Als we kijken naar een reeks gegevens die in de loop van de tijd zijn verdeeld ( tijdreeksen), kunnen we de dieptepunten en hoogtepunten van de indicatoren die het bevat visueel identificeren. Een patroon van stijgingen en dalingen wordt een trend genoemd en kan ons vertellen of onze gegevens stijgen of dalen.

Misschien begin ik de serie artikelen over prognoses met het eenvoudigste: het construeren van een trendfunctie. Laten we bijvoorbeeld verkoopgegevens nemen en een model bouwen dat de afhankelijkheid van verkopen op tijd beschrijft.

Basisconcepten

Ik denk dat iedereen al sinds schooltijd bekend is met de lineaire functie; het is precies wat ten grondslag ligt aan de trend:

Y(t) = a0 + a1*t + E

Y is het verkoopvolume, de variabele die we zullen verklaren aan de hand van de tijd en waarvan deze afhangt, dat wil zeggen Y(t);

t — periodenummer ( serienummer maand), waarin het verkoopplan Y wordt uitgelegd;

a0 is de nulregressiecoëfficiënt, die de waarde van Y(t) weergeeft, bij afwezigheid van de invloed van de verklarende factor (t=0);

a1 is de regressiecoëfficiënt, die aangeeft hoeveel de bestudeerde verkoopindicator Y afhangt van de beïnvloedende factor t;

E zijn willekeurige verstoringen die de invloed weerspiegelen van andere factoren waarmee geen rekening is gehouden in het model, behalve tijd t.

Model gebouw

We kennen dus het verkoopvolume van de afgelopen 9 maanden. Zo ziet ons bord eruit:

Het volgende dat we moeten doen is de coëfficiënten bepalen a0 En een1 om het verkoopvolume voor de 10e maand te voorspellen.

Modelcoëfficiënten bepalen

We zijn een schema aan het maken. Horizontaal zien we de uitgestelde maanden, verticaal het verkoopvolume:

In Google Spreadsheets selecteren we Grafiek-editor -> Aanvullend en zet een vinkje naast Trendlijnen. In de instellingen selecteren we Etiket — De vergelijking En Toon R^2.

Als u alles in MS Excel doet, klikt u met de rechtermuisknop op de grafiek en selecteert u 'Trendlijn toevoegen' in het vervolgkeuzemenu.

Standaard wordt een lineaire functie gebouwd. Selecteer aan de rechterkant 'Vergelijking in diagram weergeven' en 'Waarde van de betrouwbaarheid van de benadering R^2'.

Dit is wat er gebeurde:

In de grafiek zien we de vergelijking van de functie:

y = 4856*x + 105104

Het beschrijft het verkoopvolume afhankelijk van het maandnummer waarvoor we deze verkopen willen voorspellen. Vlakbij zien we de determinatiecoëfficiënt R^2, die de kwaliteit van het model aangeeft en hoe goed het onze verkopen beschrijft (Y). Hoe dichter bij 1, hoe beter.

Ik heb R^2 = 0,75. Dit is een gemiddelde indicator. Het geeft aan dat het model naast de tijd t geen rekening houdt met andere significante factoren. Het kan bijvoorbeeld seizoensinvloeden zijn.

Wij voorspellen

j = 4856*10 + 105104

We krijgen 153664 verkopen binnen volgende maand. Als wij toevoegen nieuw punt in de grafiek zien we meteen dat R^2 is verbeterd.

Op deze manier kunt u gegevens enkele maanden van tevoren voorspellen, maar zonder rekening te houden met andere factoren zal uw voorspelling op de trendlijn liggen en niet zo informatief zijn als u zou willen. Bovendien zal een op deze manier gemaakte langetermijnvoorspelling zeer bij benadering zijn.

U kunt de nauwkeurigheid van het model verbeteren door seizoensinvloeden toe te voegen aan de trendfunctie, wat we in het volgende artikel zullen doen.