Hvordan legge til en trendlinje i Excel. Prognoseverktøy i Microsoft Excel

Grafer

Regresjonsanalyse

Regresjonsligning Y fra X kalt funksjonell avhengighet y=f(x), og grafen er en regresjonslinje.

Excel lar deg lage diagrammer og grafer av ganske akseptabel kvalitet. Excel tilgjengelig spesielt middel- Chart Wizard, som guider brukeren gjennom alle fire stadier av prosessen med å lage et diagram eller en graf.

Som regel begynner plotting med å velge et område som inneholder dataene det skal plottes på. Denne starten forenkler den videre fremdriften av plotting. Imidlertid kan rekkevidden med de originale dataene deles i andre fase av dialogen med DIAGRAMMESTER. I Excel 2003 DIAGRAMMESTER plassert i menyen som en knapp eller et diagram kan opprettes ved å klikke på fanen SETT INN og finn elementet i listen som åpnes DIAGRAM. I Excel 2007 finner vi også fanen SETT INN(Fig. 31).

Ris. 31. DIAGRAMMESTER i Excel 2007

Den enkleste måten er å velge rekkevidden av kildedata der disse dataene er plassert i tilstøtende rader (kolonner eller rader) - du må klikke til venstre øverste celle område og dra deretter musepekeren til cellen nederst til høyre i området. Når du velger data som ligger i ikke-tilstøtende rader, drar du musepekeren langs de valgte radene mens du trykker på Ctrl-tasten. Hvis en av dataseriene har en celle med en tittel, må de resterende valgte seriene også ha en tilsvarende celle, selv om den er tom.

Til regresjonsanalyse Det er best å bruke et punktdiagram (fig. 30). Med henne bygge Excel oppfatter den første raden i det valgte området med kildedata som et sett med argumentverdier for funksjonene hvis grafer må plottes (samme sett for alle funksjoner). Følgende rader oppfattes som sett med verdier for selve funksjonene (hver rad inneholder verdiene til en av funksjonene som tilsvarer de spesifiserte argumentverdiene plassert i den første raden i det valgte området).

I Excel 2007 er aksenavnene plassert i menyfanen OPPSETT(Fig. 32).

Ris. 32. Angi navn på grafakser i Excel 2007

For å få en matematisk modell er det nødvendig å tegne en trendlinje på grafen. I Excel 2003 og 2007 må du klikke Høyreklikk musen på grafen punkter. Så i Excel 2003 vises en fane med en liste over elementer som vi velger fra LEGG TIL TREND LINE(Fig. 33).

Ris. 33. LEGG TIL TREND LINE

Etter å ha klikket på elementet LEGG TIL TREND LINE et vindu vises TREND LINE(Fig. 34). I kategorien TYPE kan du velge følgende linjetyper: lineær, logaritmisk, eksponentiell, potens, polynom, lineær filtrering.

Ris. 34. Vindu TREND LINE i Excel 2003

I fanen ALTERNATIVER(Fig. 35) kryss av i boksen ved siden av elementene VIS LIGNING PÅ DIAGRAM, så vil den vises på grafen matematisk modell denne avhengigheten. Vi setter også en avmerkingsboks ved siden av varen VIS PÅ DIAGRAMET VERDEN AV PÅLITELIGHETEN AV TILNÆRMINGEN (R^2). Jo nærmere tilnærmingens konfidensverdi er 1, jo nærmere den valgte kurven nærmer seg punktene på grafen. Deretter klikker du på knappen OK. En trendlinje, den tilsvarende ligningen og tilnærmingens pålitelighetsverdi vil vises på grafen.

Ris. 35. Tab ALTERNATIVER

I Excel 2007, etter at vi høyreklikker på grafpunktene, vises en liste over menyelementer, hvorfra VELG LEGG TIL TRENDLINJE(Fig. 36).

Ris. 36. LEGG TIL TREND LINE

Ris. 37. Tab TRENDLINJEPARAMETRE

Kryss av i de nødvendige boksene og trykk på knappen LUKK.

En trendlinje, den tilsvarende ligningen og tilnærmingens pålitelighetsverdi vil vises på grafen.

For å forutsi en hendelse basert på eksisterende data, hvis det ikke er tid, kan du bruke en trendlinje. Ved å bruke den kan du visuelt forstå hvilken dynamikk dataene som grafen er bygget fra har. Microsoft-programvarepakken har en fantastisk Excel-evne, som vil hjelpe deg med å lage en ganske nøyaktig prognose ved å bruke dette verktøyet - trendlinjen i Excel. Å bygge dette analyseverktøyet er ganske enkelt, nedenfor er gitt Detaljert beskrivelse prosess og typer trendlinjer.

Trendlinje i Excel. Byggeprosess

En trendlinje er et av de viktigste dataanalyseverktøyene

For å danne en trendlinje, er det nødvendig å fullføre tre stadier, nemlig:
1. Lag en tabell;
2.
3. Velg type trendlinje.

Etter å ha samlet alt nødvendig informasjon, kan du gå direkte til trinnene for å oppnå det endelige resultatet.

Først bør du lage en tabell med kildedataene. Velg deretter ønsket område og gå til "Sett inn"-fanen og velg "Graph"-funksjonen. Etter bygging kan du søke på det endelige resultatet Tilleggsfunksjoner, i form av overskrifter og bildetekster. For å gjøre dette, venstreklikk ganske enkelt på diagrammet, velg fanen kalt "Designer" og velg "Layout". Det neste du trenger å gjøre er å skrive inn tittelen.

Det neste trinnet er å konstruere selve trendlinjen. Så for å gjøre dette, må du markere diagrammet igjen og velge "Layout" -fanen på oppgavebåndet. Neste inn denne menyen du må klikke på "Trendlinje"-knappen og velge "lineær tilnærming" eller "eksponentiell tilnærming".

Ulike l variasjonertrendlinjer

Avhengig av egenskapene til dataene som er lagt inn av brukeren, er det verdt å velge et av de presenterte alternativene; nedenfor er en beskrivelse av typene trendlinjer
Eksponentiell tilnærming. Hvis endringshastigheten i inngangsdataene øker, og kontinuerlig, så er den det denne linjen vil være mest nyttig. Men hvis dataene som ble lagt inn i tabellen inneholder null eller negative egenskaper, er denne typen uakseptabel.

Lineær tilnærming. Denne linjen er rett i naturen, og brukes vanligvis i elementære tilfeller når funksjonen øker eller reduseres med tilnærmet konstant hastighet.

Logaritmisk tilnærming. Hvis verdien i utgangspunktet vokser riktig og raskt, eller tvert imot, synker, men etter en viss verdi stabiliserer den seg, vil denne trendlinjen komme til nytte.

Polynom tilnærming. Variabel økning og reduksjon er egenskapene som er karakteristiske for denne linjen. Dessuten bestemmes graden av selve polynomene (polynomene) av antall maksima og minimum.

Strømtilnærming. Karakteriserer den monotone økningen og reduksjonen av en verdi, men anvendelsen er umulig hvis dataene har negative og nullverdier.

Glidende gjennomsnitt. Den brukes til å tydelig vise den direkte avhengigheten til den ene av den andre, ved å jevne ut alle oscillasjonspunkter. Dette oppnås ved å ta gjennomsnittsverdien mellom to tilstøtende punkter. Dermed beregnes gjennomsnittet av grafen, og antall poeng reduseres til verdien som ble valgt i "Poeng"-menyen av brukeren.

Hvordan brukes den? D For å forutsi økonomiske alternativer, er det nettopp polynom en linje hvis polynomgrad bestemmes basert på flere prinsipper: maksimering av bestemmelseskoeffisienten, samt den økonomiske dynamikken til indikatoren i perioden som prognosen er nødvendig.

Ved å følge alle stadier av dannelsen og forstå funksjonene, kan du bygge bare en primær trendlinje, som bare vagt tilsvarer virkelige prognoser. Men etter å ha satt parametrene, kan vi allerede snakke om et mer realistisk bilde av prognosen.

Trendlinje i Excel. Parameterinnstillingeri funksjonslinjen

Ved å klikke på "Trendlinje"-knappen, velg nødvendig meny har krav på " Ekstra alternativer" I vinduet som vises, klikk på "Trendlinjeformat", og merk deretter av i boksen ved siden av verdien "plasser tilnærmingspålitelighetsverdien R^2 på diagrammet". Etter det, lukk menyen ved å klikke på den tilsvarende knappen. På selve diagrammet vises koeffisienten R^2 = 0,6442.

Etter dette kansellerer vi endringene som er gjort. Ved å velge diagrammet og klikke på "Layout"-fanen, klikk deretter på "Trendlinje" og klikk på "Nei". Gå deretter til funksjonen "Formater trendlinje", klikk på polynom linje og prøv å oppnå verdien R^2= 0,8321 ved å endre graden.

For å se formler eller lage andre variasjoner av prognoser som skiller seg fra standard, er det nok å ikke være redd for å eksperimentere med verdier, og spesielt med polynomer. Dermed bruker du bare en Excel-program, kan du lage en ganske nøyaktig prognose basert på inndataene.

Formålet med tjenesten. Tjenesten brukes til å beregne trendparametrene til tidsserien y t online ved hjelp av minste kvadraters metode (LS) (se eksempelet for å finne trendligningen), samt metoden fra betinget null. For dette er et ligningssystem konstruert:
a 0 n + a 1 ∑t = ∑y
a 0 ∑t + a 1 ∑t 2 = ∑y t

og et bord som dette:

t	y	t 2	y 2	t y	y(t)
1
...	...	...	...	...	...
N
TOTAL	∑	∑	∑	∑	∑

Bruksanvisning. Spesifiser mengden data (antall rader). Den resulterende løsningen lagres i Word-fil og Excel.

Trenden til en tidsserie karakteriserer et sett med faktorer som har en langsiktig innflytelse og danner den generelle dynamikken til indikatoren som studeres.

Metode for å telle tid fra en betinget start

For å definere parametere matematisk funksjon Når man analyserer en trend i tidsserier, brukes metoden for å telle tid fra en betinget begynnelse. Den er basert på notasjonen i tidsserien slik at ∑t i . Dessuten i dynamikkserien med et odde antall nivåer serienummer nivået plassert i midten av raden er angitt med null verdi og ta det som den betingede starten på tid som teller med et intervall på +1 for alle påfølgende nivåer og –1 for alle tidligere nivåer. For eksempel, når du angir tid vil det være: –2, –1, 0, +1, +2. Med et partall av nivåer er serienumrene til den øvre halvdelen av raden (fra midten) utpekt med tallene: –1, –3, –5, og den nedre halvdelen av raden er betegnet med +1, + 3, +5.

Eksempel. Statistisk studie av populasjonsdynamikk.

Bruk kjede-, grunnleggende og gjennomsnittlig dynamikkindikatorer, evaluer endringen i tall og skriv ned konklusjonene dine.
Ved å bruke metoden for analytisk justering (rett linje og parabel, bestemme koeffisientene ved hjelp av OLS), identifisere hovedtrenden i utviklingen av fenomenet (befolkningen i Komi-republikken). Evaluer kvaliteten på de resulterende modellene ved å bruke feil og tilnærmingskoeffisienter.
Bestem lineære og parabolske trendkoeffisienter ved hjelp av diagramveiviseren. Gi prikk og intervall prognoser tall for 2010. Skriv ned konklusjonene dine.

1990	1996	2001	2002	2003	2004	2005	2006	2007	2008
1249	1133	1043	1030	1016	1005	996	985	975	968

Analytisk justering metode

EN) Lineær ligning trend har formen y = bt + a
1. Finn parametrene til ligningen ved å bruke minste kvadraters metode. Vi bruker metoden for å telle tid fra en betinget begynnelse.
Systemet med minste kvadraters ligninger for en lineær trend har formen:
a 0 n + a 1 ∑t = ∑y
a 0 ∑t + a 1 ∑t 2 = ∑y t

t	y	t 2	y 2	t y
-9	1249	81	1560001	-11241
-7	1133	49	1283689	-7931
-5	1043	25	1087849	-5215
-3	1030	9	1060900	-3090
-1	1016	1	1032256	-1016
1	1005	1	1010025	1005
3	996	9	992016	2988
5	985	25	970225	4925
7	975	49	950625	6825
9	968	81	937024	8712
0	10400	330	10884610	-4038

For våre data vil ligningssystemet ha formen:
10a 0 + 0a 1 = 10400
0a 0 + 330a 1 = -4038
Fra den første ligningen uttrykker vi en 0 og erstatter den med den andre ligningen
Vi får en 0 = -12,236, en 1 = 1040
Trendligning:
y = -12,236 t + 1040

La oss evaluere kvaliteten på trendligningen ved å bruke den absolutte tilnærmingsfeilen.

Tilnærmingsfeilen innenfor 5%-7% indikerer godt utvalg trendligninger til de opprinnelige dataene.

b) parabolsk justering
Trendligningen er y = ved 2 + bt + c
1. Finn parametrene til ligningen ved å bruke minste kvadraters metode.
System av ligninger av minste kvadrater:
a 0 n + a 1 ∑t + a 2 ∑t 2 = ∑y
a 0 ∑t + a 1 ∑t 2 + a 2 ∑t 3 = ∑yt
a 0 ∑t 2 + a 1 ∑t 3 + a 2 ∑t 4 = ∑yt 2

t	y	t 2	y 2	t y	t 3	t 4	t 2 år
-9	1249	81	1560001	-11241	-729	6561	101169
-7	1133	49	1283689	-7931	-343	2401	55517
-5	1043	25	1087849	-5215	-125	625	26075
-3	1030	9	1060900	-3090	-27	81	9270
-1	1016	1	1032256	-1016	-1	1	1016
1	1005	1	1010025	1005	1	1	1005
3	996	9	992016	2988	27	81	8964
5	985	25	970225	4925	125	625	24625
7	975	49	950625	6825	343	2401	47775
9	968	81	937024	8712	729	6561	78408
0	10400	330	10884610	-4038	0	19338	353824

For våre data har ligningssystemet formen
10a 0 + 0a 1 + 330a 2 = 10400
0a 0 + 330a 1 + 0a 2 = -4038
330a 0 + 0a 1 + 19338a 2 = 353824
Vi får en 0 = 1,258, a 1 = -12,236, a 2 = 998,5
Trendligning:
y = 1,258t2 -12,236t+998,5

Approksimasjonsfeil for den parabolske trendligningen.

Siden feilen er mindre enn 7 %, kan denne ligningen brukes som en trend.

Minimum tilnærmingsfeil for parabolsk justering. I tillegg er bestemmelseskoeffisienten R2 høyere enn ved lineær. Derfor er det nødvendig å bruke en parabolsk ligning for prognoser.

Intervallvarsel.
La oss bestemme rotmiddelkvadratfeilen til den predikerte indikatoren.

m = 1 - antall påvirkningsfaktorer i trendligningen.
Uy = y n+L ± K
Hvor

L - blyperiode; y n+L - punktprognose i henhold til modellen på det (n + L)-te tidspunktet; n er antall observasjoner i tidsserien; Sy er standardfeilen til den predikerte indikatoren; T-bord - tabellverdi Elevens t-test for signifikansnivået α og for antall frihetsgrader lik n-2.
Ved å bruke Elevens tabell finner vi Ttable
T-tabell (n-m-1; a/2) = (8; 0,025) = 2,306
Punktvarsel, t = 10: y(10) = 1,26*10 2 -12,24*10 + 998,5 = 1001,89 tusen mennesker.

1001.89 - 71.13 = 930.76 ; 1001.89 + 71.13 = 1073.02
Intervallvarsel:
t = 9+1 = 10: (930,76;1073,02)

kronblad;
sirkulær;
flate;
ringformet;
volum;
med akkumulering.

Lineær tilnærming

Vi får resultatet:

y = 4,503x + 6,1333

6.1333 – forskyvninger;
x – periodenummer.

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

For eksempel:

Periode	14	15	16	17	18	19	20
Prognose	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

En av de viktige komponentene i enhver analyse er å bestemme hovedtrenden for hendelser. Med disse dataene kan du lage en prognose videre utvikling situasjoner. Dette er spesielt tydelig sett i eksemplet med en trendlinje på et diagram. La oss finne ut hvordan Microsoft program Excel kan bygge det.

Trendlinje i Excel

Excel-applikasjonen gir muligheten til å plotte en trendlinje ved hjelp av en graf. I dette tilfellet er de første dataene for dannelsen hentet fra en forhåndsforberedt tabell.

Plotte en graf

For å lage en graf må du ha ferdiglaget bord, på grunnlag av hvilken den vil bli dannet. Som et eksempel, la oss ta data om verdien av dollaren i rubler pr viss periode tid.

Vi bygger en tabell der en kolonne vil inneholde tidsperioder (i vårt tilfelle, datoer), og den andre vil inneholde en verdi hvis dynamikk vil vises i en graf.
Vi fremhever dette bordet. Gå til fanen "Sett inn". Der, på båndet i "Diagrams"-verktøyblokken, klikker du på "Graph" -knappen. Fra listen som presenteres, velg det aller første alternativet.
Etter dette skal timeplanen bygges, men den må fortsatt ferdigstilles. La oss lage en tittel for grafen. For å gjøre dette, klikk på den. I fanegruppen "Arbeid med diagrammer" som vises, gå til fanen "Layout". I den klikker du på "Kartnavn" -knappen. I listen som åpnes, velg "Over diagrammet".
I feltet som vises over grafen, skriv inn navnet som vi anser som passende.
Så merker vi aksene. I den samme "Layout"-fanen klikker du på knappen på "Axes Titles"-båndet. Vi går sekvensielt gjennom elementene "Navn på den horisontale hovedaksen" og "Navn under aksen".
I feltet som vises, skriv inn navnet på den horisontale aksen, i henhold til konteksten til dataene på den.
For å tildele et navn til den vertikale aksen, bruker vi også fanen "Layout". Klikk på "Aksenavn"-knappen. Vi beveger oss sekvensielt gjennom popup-menyelementene "Navn på den vertikale hovedaksen" og "Rotert navn". Det er denne typen plassering av aksenavnet som vil være mest praktisk for vår type diagram.
I det vertikale aksenavnet som vises, skriv inn ønsket navn.

Lekse: Hvordan lage en graf i Excel

Lage en trendlinje

Nå må vi legge til en trendlinje direkte.

Mens du er i "Layout"-fanen, klikker du på "Trend Line"-knappen, som er plassert i "Analyse"-verktøyblokken. Fra listen som åpnes, velg "Eksponentiell tilnærming" eller "Lineær tilnærming".
Etter dette legges en trendlinje til diagrammet. Som standard er den svart.

Sette opp en trendlinje

Det er en mulighet tilleggsinnstillinger linjer.

I "Layout"-fanen går du sekvensielt til menyelementene "Analyse", "Trendlinje" og "Ytterligere trendlinjeparametere...".
Parametervinduet åpnes, du kan lage ulike innstillinger. Du kan for eksempel endre typen utjevning og tilnærming ved å velge ett av seks alternativer:
- Polynom;
- Lineær;
- Makt;
- Logaritmisk;
- Eksponentiell;
- Lineær filtrering.
For å bestemme påliteligheten til modellen vår, merk av i boksen ved siden av elementet "Plasser den tilnærmede pålitelighetsverdien på diagrammet." For å se resultatet, klikk på "Lukk"-knappen.

Hvis denne indikatoren er lik 1, er modellen så pålitelig som mulig. Jo lenger nivået er fra én, jo lavere er påliteligheten.

Hvis du ikke er fornøyd med tillitsnivået, kan du gå tilbake til parametrene igjen og endre type utjevning og tilnærming. Deretter danner du koeffisienten på nytt.

Prognoser

Hovedoppgaven til en trendlinje er muligheten til å lage en prognose for videre utvikling fra den.

La oss gå til parametrene igjen. I innstillingsblokken "Prognose" angir vi i de aktuelle feltene hvor mange perioder fremover eller bakover vi trenger for å fortsette trendlinjen for prognoser. Klikk på "Lukk"-knappen.
La oss gå tilbake til timeplanen igjen. Det viser at linjen er forlenget. Nå kan du bruke den til å bestemme hvilken omtrentlig indikator som er spådd for spesifikk dato samtidig som den nåværende trenden opprettholdes.

Som du kan se, er det ikke vanskelig å bygge en trendlinje i Excel. Programmet gir verktøy slik at det kan konfigureres til å vise indikatorer så korrekt som mulig. Basert på grafen kan du lage en prognose for en bestemt tidsperiode.

Vi er glade for at vi kunne hjelpe deg med å løse problemet.

Still spørsmålet ditt i kommentarene, og beskriv essensen av problemet i detalj. Våre spesialister vil prøve å svare så raskt som mulig.

Har denne artikkelen hjulpet deg?

Hvorfor trengs diagrammer? For å "gjøre det vakkert"? Ikke i det hele tatt - hovedoppgaven diagrammer lar deg presentere obskure tall på en lettfattelig måte grafisk form. Slik at tingenes tilstand er klar med et øyekast, og det er ingen grunn til å kaste bort tid på å studere tørr statistikk.

En annen stor fordel med diagrammer er at med deres hjelp er det mye lettere å vise trender, det vil si å lage en prognose for fremtiden. Faktisk, hvis det har gått bra i hele år, er det ingen grunn til å tro at bildet plutselig vil snu i neste kvartal.

Hvordan diagrammer og grafer bedrar oss

Imidlertid er diagrammer (spesielt når det gjelder visuelt å representere store datamengder), selv om de er ekstremt enkle å forstå, ikke alltid åpenbare.

Jeg vil illustrere mine ord med et enkelt eksempel:

Et diagram basert på en tabell i MS Excel

Denne tabellen viser gjennomsnittlig antall besøkende til et bestemt nettsted per dag for måned, samt antall sidevisninger per besøkende. Det er logisk at det alltid skal være flere sidevisninger enn besøkende, siden én bruker kan se flere sider samtidig.

Det er ikke mindre logisk at jo flere sider en besøkende ser på, jo bedre er nettstedet - det fanger brukerens oppmerksomhet og tvinger ham til å lese dypere.

Hva ser nettstedeieren fra diagrammet vårt? At det går bra for ham! Det var en sesongmessig nedgang i interessen i sommermånedene, men om høsten kom tallene tilbake og oversteg til og med vårens. Konklusjoner? Vi fortsetter i samme ånd og vil snart oppnå suksess!

Er diagrammet klart? Ganske. Men er det åpenbart? La oss finne ut av det.

Forstå trender i MS Excel

En stor feil fra nettstedeierens side ville være å ta diagrammet som det er. Ja, det blotte øye kan se at de blå og oransje søylene av "høst" har vokst sammenlignet med "vår" og enda mer "sommer". Imidlertid er ikke bare tallene og størrelsen på stolpene viktige, men også forholdet mellom dem. Det vil si, ideelt sett, med generell vekst, bør de "oransje" kolonnene med visninger vokse seg mye sterkere enn de "blå", noe som vil bety at nettstedet ikke bare tiltrekker seg flere lesere, men også blir større og mer interessant.

Hva ser vi på grafen? De oransje søylene av "høst" er i det minste ikke mer enn "vår", om ikke mindre. Dette indikerer ikke suksess, men snarere det motsatte - besøkende kommer, men leser mindre i gjennomsnitt og blir ikke på siden!

Det er på tide å slå alarm og... bli kjent med noe slikt som trendlinje.

Hvorfor trengs en trendlinje?

En trendlinje "i enkle termer" er en kontinuerlig linje trukket opp basert på gjennomsnittsverdier basert på spesielle algoritmer som diagrammet vårt er konstruert fra. Med andre ord, hvis dataene våre "hopper" over tre rapporteringspunkter fra "-5" til "0", og deretter til "+5", vil vi ende opp med en nesten flat linje: "proffene" i situasjonen åpenbart balansere "ulemper".

Basert på retningen til trendlinjen, er det mye lettere å se den virkelige tilstanden og se de samme trendene, og derfor lage prognoser for fremtiden. Vel, nå, la oss sette i gang!

Hvordan tegne en trendlinje i MS Excel

Legger til diagrammet i MS Excel-linje trend

Høyreklikk på en av de "blå" kolonnene og velg elementet fra hurtigmenyen "Legg til trendlinje".

Diagramarket viser nå en stiplet trendlinje. Som du kan se, faller det ikke 100% sammen med verdiene i diagrammet - bygget ved hjelp av vektede gjennomsnitt, det er bare generell disposisjon gjentar sin retning. Dette forhindrer oss imidlertid ikke i å se en jevn økning i antall besøk på nettstedet - selv "sommer"-nedtrekket påvirker ikke det samlede resultatet.

Trendlinje for Besøkende-kolonnen

La oss nå gjenta det samme trikset med de "oransje" kolonnene og bygge en andre trendlinje. Som jeg sa før: Situasjonen her er ikke så god. Trenden viser tydelig at i løpet av beregningsperioden økte ikke antallet visninger bare, men begynte til og med å falle - sakte men jevnt.

En annen trendlinje bidrar til å avklare situasjonen

Hvis vi mentalt fortsetter trendlinjen for fremtidige måneder, vil vi komme til en skuffende konklusjon - antallet interesserte besøkende vil fortsette å synke. Siden brukere ikke blir her, vil et fall i interessen for nettstedet i nær fremtid uunngåelig føre til et fall i trafikken.

Følgelig må prosjekteieren snarest huske hva han gjorde galt om sommeren (“på våren” var alt ganske normalt, å dømme etter tidsplanen), og snarest iverksette tiltak for å rette opp situasjonen.

For å forutsi en hendelse basert på eksisterende data, hvis det ikke er tid, kan du bruke en trendlinje. Ved å bruke den kan du visuelt forstå hvilken dynamikk dataene som grafen er bygget fra har. Microsoft-programvarepakken har en fantastisk Excel-funksjon som vil hjelpe deg med å lage en ganske nøyaktig prognose ved å bruke dette verktøyet - en trendlinje i Excel. Å bygge dette analyseverktøyet er ganske enkelt; nedenfor er en detaljert beskrivelse av prosessen og typene av trendlinjer.

Trendlinje i Excel. Byggeprosess

Trendlinje er et av de viktigste dataanalyseverktøyene

For å danne en trendlinje, er det nødvendig å fullføre tre stadier, nemlig:
1. Lag en tabell;
2. Konstruer et diagram;
3. Velg type trendlinje.

Etter å ha samlet inn all nødvendig informasjon, kan du fortsette direkte til å fullføre trinnene for å oppnå det endelige resultatet.

Først bør du lage en tabell med kildedataene. Velg deretter ønsket område og gå til "Sett inn"-fanen og velg "Graph"-funksjonen. Etter konstruksjon kan tilleggsfunksjoner brukes på det endelige resultatet i form av overskrifter og signaturer. For å gjøre dette, venstreklikk ganske enkelt på diagrammet, velg fanen kalt "Designer" og velg "Layout". Det neste du trenger å gjøre er å skrive inn tittelen.

Det neste trinnet er å konstruere selve trendlinjen. Så for å gjøre dette, må du markere diagrammet igjen og velge "Layout" -fanen på oppgavebåndet. Neste i denne menyen må du klikke på "Trendlinje"-knappen og velge "lineær tilnærming" eller "eksponentiell tilnærming".

Ulike l variasjonertrendlinjer

Avhengig av egenskapene til dataene som er lagt inn av brukeren, er det verdt å velge et av de presenterte alternativene; nedenfor er en beskrivelse av typene trendlinjer
Eksponentiell tilnærming. Hvis endringshastigheten i inngangsdataene øker, og kontinuerlig, vil denne linjen være mest nyttig. Men hvis dataene som ble lagt inn i tabellen inneholder null eller negative egenskaper, er denne typen uakseptabel.

Lineær tilnærming. Denne linjen er rett i naturen, og brukes vanligvis i elementære tilfeller når funksjonen øker eller reduseres med tilnærmet konstant hastighet.

Logaritmisk tilnærming. Hvis verdien i utgangspunktet vokser riktig og raskt, eller tvert imot, synker, men etter noen verdier stabiliserer den seg, vil denne trendlinjen komme til nytte.

Polynom tilnærming. Variabel økning og reduksjon er egenskapene som er karakteristiske for denne linjen. Dessuten bestemmes graden av selve polynomene (polynomene) av antall maksima og minimum.

Strømtilnærming. Karakteriserer den monotone økningen og reduksjonen av en verdi, men anvendelsen er umulig hvis dataene har negative og nullverdier.

Glidende gjennomsnitt. Den brukes til å tydelig vise den direkte avhengigheten til den ene av den andre, ved å jevne ut alle oscillasjonspunkter. Dette oppnås ved å ta gjennomsnittsverdien mellom to tilstøtende punkter. Dermed beregnes gjennomsnittet av grafen, og antall poeng reduseres til verdien som ble valgt i "Poeng"-menyen av brukeren.

Hvordan brukes den? For å forutsi økonomiske alternativer, er det en polynomlinje som brukes, hvis grad av polynomet bestemmes på grunnlag av flere prinsipper: maksimering av bestemmelseskoeffisienten, samt den økonomiske dynamikken til indikatoren i løpet av perioden som prognosen er nødvendig.

Trendlinje i Excel. Parameterinnstillingeri funksjonslinjen

Ved å klikke på "Trendlinje"-knappen, velg ønsket meny kalt "Avanserte alternativer". I vinduet som vises, klikk på "Formater trendlinje", og merk deretter av i boksen ved siden av verdien "plasser tilnærmingspålitelighetsverdien R^2 på diagrammet". Etter det, lukk menyen ved å klikke på den tilsvarende knappen. På selve diagrammet vises koeffisienten R^2 = 0,6442.

Etter dette kansellerer vi endringene som er gjort. Ved å velge diagrammet og klikke på "Layout"-fanen, klikk deretter på "Trend Line" og klikk på "Nei". Gå deretter til funksjonen "Trendlinjeformat", klikk på polynomlinjen og prøv å oppnå verdien R^2 = 0,8321 ved å endre graden.

For å se formler eller lage andre variasjoner av prognoser som skiller seg fra standard, er det nok å ikke være redd for å eksperimentere med verdier, og spesielt med polynomer. Dermed kan du ved å bruke bare ett Excel-program lage en ganske nøyaktig prognose basert på inndataene.

(Besøkt 10 510 ganger, 27 besøk i dag)

Trendlinje i Excel på forskjellige diagrammer

For å visuelt illustrere pristrender, brukes en trendlinje. Element teknisk analyse representerer geometrisk bilde gjennomsnittsverdier for den analyserte indikatoren.

La oss se på hvordan du legger til en trendlinje i et diagram i Excel.

Legge til en trendlinje i et diagram

La oss for eksempel ta gjennomsnittlig oljepris siden 2000 fra åpne kilder. La oss legge inn dataene for analyse i tabellen:

La oss bygge en graf basert på tabellen. Velg området og gå til "Sett inn"-fanen. Velg en enkel graf fra de foreslåtte diagramtypene. Horisontal – år, vertikal – pris.
Høyreklikk på selve diagrammet. Klikk på "Legg til trendlinje".
Et vindu åpnes for å konfigurere linjeparametere. La oss velge den lineære typen og plassere den tilnærmede pålitelighetsverdien på grafen.
En skrå linje vises på grafen.

En trendlinje i Excel er en graf av en passende funksjon. Hvorfor er det nødvendig - å lage prognoser basert på statistiske data. For dette formålet er det nødvendig å utvide linjen og bestemme verdiene.

Hvis R2 = 1, er tilnærmingsfeilen null. I vårt eksempel ga valget av lineær tilnærming lav pålitelighet og dårlige resultater. Prognosen vil være unøyaktig.

Merk følgende!!! Du kan ikke legge til en trendlinje i følgende typer grafer og diagrammer:

kronblad;
sirkulær;
flate;
ringformet;
volum;
med akkumulering.

Trendlinjeligning i Excel

I eksemplet ovenfor ble lineær tilnærming valgt kun for å illustrere algoritmen. Som pålitelighetsverdien viste, var ikke valget helt vellykket.

Du bør velge den visningstypen som mest nøyaktig illustrerer trenden i brukerinndata. La oss se på alternativene.

Lineær tilnærming

Det geometriske bildet er en rett linje. Derfor brukes lineær tilnærming for å illustrere en indikator som øker eller minker med en konstant hastighet.

La oss vurdere det betingede antallet kontrakter inngått av lederen over 10 måneder:

Basert på data i Excel regneark La oss bygge et spredningsplott (det vil bidra til å illustrere den lineære typen):

Velg diagrammet - "legg til trendlinje". I parametrene velger du lineær type. Vi legger til tilnærmingens konfidensverdi og trendlinjeligningen i Excel (bare merk av i boksene nederst i "Parameters"-vinduet).

Vi får resultatet:

Merk! På lineær type tilnærminger til datapunktene er plassert så nær den rette linjen som mulig. Denne typen bruker følgende ligning:

y = 4,503x + 6,1333

hvor 4,503 er helningsindeksen;
6.1333 – forskyvninger;
y – sekvens av verdier,
x – periodenummer.

Den rette linjen på grafen viser en jevn økning i kvaliteten på lederens arbeid. Reliabilitetsverdien til tilnærmingen er 0,9929, noe som indikerer en god overensstemmelse mellom den beregnede linjen og de opprinnelige dataene. Prognoser må være nøyaktige.

For å forutsi antall kontrakter inngått, for eksempel i periode 11, må du erstatte tallet 11 i stedet for x i ligningen. Under beregningene finner vi ut at i den 11. perioden vil denne lederen inngå 55-56 kontrakter.

Eksponentiell trendlinje

Denne typen er nyttig hvis inngangsverdiene endres med en kontinuerlig økende hastighet. Eksponentiell tilpasning brukes ikke når det er null eller negative egenskaper.

La oss bygge en eksponentiell trendlinje i Excel. La oss for eksempel ta de betingede verdiene for produktiv strømforsyning i region X:

Vi bygger en tidsplan. Legg til en eksponentiell linje.

Ligningen ser slik ut:

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

hvor 7,6403 og -0,084 er konstanter;
e er grunnlaget for den naturlige logaritmen.

Tilnærmingspålitelighetsindikatoren var 0,938 - kurven tilsvarer dataene, feilen er minimal, prognosene vil være nøyaktige.

Logaritmisk trendlinje i Excel

Den brukes til følgende endringer i indikatoren: først rask vekst eller reduksjon, deretter relativ stabilitet. Den optimaliserte kurven tilpasser seg godt til denne "oppførselen" til mengden. Den logaritmiske trenden er egnet for å forutsi salg av et nytt produkt som nettopp er introdusert på markedet.

På det første stadiet Produsentens oppgave er å øke kundebase. Når et produkt har sin egen kjøper, må det beholdes og serveres.

La oss bygge en graf og legge til en logaritmisk trendlinje for å forutsi salg av et betinget produkt:

R2 er nær i verdi 1 (0,9633), noe som indikerer en minimal tilnærmingsfeil. La oss forutsi salgsvolum i påfølgende perioder. For å gjøre dette, må du erstatte periodenummeret i ligningen i stedet for x.

For eksempel:

Periode	14	15	16	17	18	19	20
Prognose	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

For å beregne prognosetallene ble en formel av skjemaet brukt: =272,14*LN(B18)+287,21. Hvor B18 er periodenummeret.

Polynomtrendlinje i Excel

Denne kurven er preget av variable økninger og reduksjoner. For polynomer (polynomer) bestemmes graden (av antall maksimums- og minimumsverdier). For eksempel er ett ekstremum (minimum og maksimum) den andre graden, to ekstremer er den tredje graden, tre er den fjerde.

Polynomtrend i Excel brukes til analyse stort sett data om en ustabil mengde. La oss se på eksemplet med det første settet med verdier (oljepriser).

For å oppnå en slik verdi av tilnærmingspålitelighet (0,9256), var det nødvendig å sette den til grad 6.

Last ned eksempler på diagrammer med en trendlinje

Men denne trenden gjør at vi kan lage mer eller mindre nøyaktige prognoser.

Hilsen, kjære kamerater! I dag skal vi se på en av de subjektive handelsmetodene - handel ved hjelp av trendlinjer. La oss se på følgende spørsmål:

1) Hva er en trend (dette er viktig som utgangspunkt)
2) Tegning av trendlinjer
3) Bruk i praktisk handel
4) Subjektivitet av metoden

1) Hva er en trend
_________________
Før du går videre til å konstruere en trendlinje, må du forstå selve trenden. Vi vil ikke gå inn i akademiske tvister og for enkelhets skyld godtar vi følgende formel:

En trend (opover) er en sekvens av økende høyder og nedturer, hvor hver påfølgende høy (og lav) er høyere enn de forrige.

En trend (nedover) er en sekvens av fallende (minkende) høyder og nedturer, der hver påfølgende lav (og høy) er LAVERE enn den forrige.

En trendlinje er en linje trukket mellom to høydepunkter (hvis trenden er nedadgående) eller to lavpunkter (hvis trenden er oppadgående). Det vil si at trendlinjen viser oss at det er en trend på diagrammet! Men den eksisterer kanskje ikke (når det gjelder en leilighet).

2) Tegning av trendlinjer
____________________________

Dette er det vanskeligste spørsmålet! Jeg har sett diskusjoner som varte på mange sider bare om HVORDAN DU Tegner en trendlinje RIKTIG! Men vi må ikke bare bygge, men også handle på det...

For å bygge en trendlinje må du ha minst to maksimum (nedadgående trend) eller to minimumsverdier (opadgående trend). Vi må koble disse ekstremene med en linje.

Det er viktig å følge følgende regler når du bygger linjer:

Vinkelen på trendlinjen er viktig. Jo brattere helningsvinkelen er, jo mindre pålitelig er den.
– Det er optimalt å bygge en linje ved hjelp av to punkter. Hvis du bygger på tre eller flere punkter, reduseres påliteligheten til trendlinjen (dens sammenbrudd er sannsynlig).
– Ikke prøv å bygge en linje under noen forhold. Hvis du ikke kan tegne det, er det mest sannsynlig ingen trend. Derfor, dette verktøyet ikke egnet for bruk under gjeldende markedsforhold.

Disse reglene vil hjelpe deg med å bygge trendlinjer riktig!

3) Handel langs trendlinjer
____________________________

Vi har to grunnleggende ulike muligheter:
A) Bruk linjen som et støttenivå (motstand) for å gå inn langs den i retning av trenden
B) Bruk Forex trendlinjen for å spille for en breakout (reversering) av trenden.

Begge metodene er gode hvis du vet hvordan du "koker dem riktig."

Så vi har bygget en linje med to punkter. Så snart prisen berører linjen, må vi inn i markedet i retning av den eksisterende trenden. For å delta bruker vi bestillinger av typen "kjøpsgrense eller salgsgrense".

Alt her er enkelt og oversiktlig. Det eneste du trenger å huske er at jo oftere prisen tester trendlinjen, med utgangspunkt i den, jo større er sannsynligheten for at neste berøring vil være et utbrudd av linjen!

Hvis vi vil spille for å bryte trendlinjen, må vi handle litt annerledes:
1) Vent til linjen berører
2) Venter på en retur
3) Plasser en kjøpsstoppordre (eller salgsstopp) på den resulterende avmerkingsboksen.
Vær oppmerksom på bildet.

Vi ventet på at haken skulle vises og la inn en stoppordre for kjøp på maksimum.

Etter en tid ble ordren utløst og vi kom inn på markedet.
Oppstår logisk spørsmål– hvorfor kunne du ikke gå inn på markedet med en gang?
Poenget er at vi ikke vet om testing av trendlinjen vil være vellykket eller ikke. Og ved å vente på "haken" øker vi sjansene våre for suksess dramatisk (vi luker ut falske signaler).

4) Subjektivitet av metoden
_________________________

Virker alt enkelt? Faktisk, ved å bruke denne metoden, vil vi møte følgende vanskeligheter:
A) Linjehellingsvinkel (du kan alltid konstruere trendlinjer med forskjellige helninger.
B) Hva anses som et utbrudd av en trendlinje (hvor mange poeng eller prosentpoeng skal prisen "bryte" linjen for å betrakte det som et utbrudd)?
Sp) Når regnes en linje som "foreldet" og en ny bygget?

Vær oppmerksom på bildet.

Den røde linjen indikerer et av designalternativene. En uerfaren handelsmann kan trekke grensen på denne måten (og betale for det).

Praktisk erfaring er viktig i denne saken. Det vil si at det ikke er mulig å redusere alt til noen få enkle regler konstruksjon. Dette er grunnen til at det ikke er noen trendlinjeindikator. Mer presist kan det eksistere, men det bygger dem "skjevt" og feil. Denne teknikken ble i utgangspunktet "skreddersydd" til erfaringen og ferdighetene til handelsmannen.

Personlig bruker jeg sjelden trendlinjer som uavhengig instrument. Men ikke desto mindre snakker jeg om dem av en enkel grunn. Faktum er at mange andre handelsmenn bruker dem. Derfor må vi (du og jeg) være klar over våre konkurrenters teknikker.

Hvorvidt dette verktøyet er nødvendig i din handel er opp til deg å avgjøre!

Lykke til og god handel. Arthur.
blog-forex.org

Relaterte innlegg:

Trendhandelskonsept (video)

Trendende modeller (figurer)

Video om dette emnet:

Del 10. Valg av formler i henhold til timeplanen. Trendlinje

For problemene diskutert ovenfor var det mulig å konstruere en ligning eller et system av ligninger.

Men i mange tilfeller når man bestemmer seg praktiske problemer Det er bare eksperimentelle (måleresultater, statistiske, referanse, eksperimentelle) data. Ved å bruke dem, med en viss grad av nærhet, prøver de å rekonstruere en empirisk formel (ligning), som kan brukes til å finne en løsning, modellere, vurdere løsninger og lage prognoser.

Prosessen med å velge en empirisk formel P(x) for erfaren avhengighet F(x) kalt tilnærming(utjevning). For avhengigheter med én ukjent, bruker Excel grafer, og for avhengigheter med mange ukjente, funksjonspar fra gruppen Statistisk LINEÆR og TREND, LGRFPRIBL og VEKST.

Denne delen diskuterer tilnærming av eksperimentelle data ved hjelp av Excel-diagrammer: basert på dataene opprettes og velges en tidsplan trendlinje , dvs. tilnærmet funksjon, som kommer nærmest opplevd avhengighet.

Graden av likhet for den valgte funksjonen estimeres bestemmelseskoeffisient R2 . Hvis det ikke er andre teoretiske betraktninger, velg en funksjon med en koeffisient R2 har en tendens til 1. Merk at utvalget av formler ved hjelp av trendlinjen lar oss etablere både typen av den empiriske formelen og bestemme de numeriske verdiene til de ukjente parameterne.

Excel har 5 typer tilnærmingsfunksjoner:

1. Lineær – y=cx+b. Dette enkleste funksjonen, som gjenspeiler veksten og nedgangen av data med en konstant hastighet.

2. Polynom – y=c0+c1x+c2x2+…+c6x6. Funksjonen beskriver vekselvis økende og minkende data. Et polynom av 2. grad kan ha ett ekstremum (min eller maks), et polynom av 3. grad - opp til 2 ekstremer, et polynom av 4. grad - opptil 3, etc.

3. Logaritmisk – y=c ln x+b. Denne funksjonen beskriver raskt økende (minkende) data som deretter stabiliserer seg.

4. Effekt – y=cxb, (X>0i y>0). Funksjonen reflekterer data med en stadig økende (minkende) veksthastighet.

5. Eksponentiell – y=cebx, (e– grunnlaget for den naturlige logaritmen). Funksjonen beskriver raskt voksende (minkende) data, som deretter stabiliserer seg.

For alle 5 funksjonstypene brukes datatilnærming ved bruk av minste kvadraters metode (se hjelp på F1 "trendlinje").

Som eksempel Vurder avhengigheten av salg av reklame, gitt av følgende statistiske data for et bestemt selskap:

(tusen rubler.)	1,5	2,5	3,5	4,5	5,5
Salg (tusen rubler)

Du må bygge en funksjon den beste måten som gjenspeiler denne avhengigheten. I tillegg er det nødvendig å estimere salg for reklameinvesteringer til 6 tusen rubler.

La oss komme til løsningen. Først av alt, skriv inn disse dataene i Excel og bygg en graf, som i fig. 38. Som du kan se, er grafen basert på området B2:J2. Deretter høyreklikker du på diagrammet og legger til en trendlinje som vist i fig. 38.

For å merke X-aksen med de tilsvarende reklameverdiene (som i fig. 38), velg elementet i rullegardinmenyen (fig. 38) OG lignende data. I vinduet med samme navn som åpnes, i fanen Rad, i felt P X-akseetiketter, angi celleområdet der X-verdiene er skrevet (her $B$1:$K$1):

I innstillingsvinduet som åpnes (fig. 39), på fanen Type velg å tilnærme logaritmisk trendlinje (i henhold til type graf). På bokmerket Alternativer Merk av i avmerkingsboksene som viser ligningen og bestemmelseskoeffisienten på grafen.

Etter å ha klikket OK Du vil få resultatet som i fig. 40. Bestemmelseskoeffisient R2= 0,9846, som er en god grad av nærhet. For å bekrefte riktigheten av den valgte funksjonen (siden det ikke er andre teoretiske hensyn), forutsi salgsutviklingen i 10 perioder på forhånd. For å gjøre dette, høyreklikk på trendlinjen - endre formatet - deretter i feltet Værvarsel: videresend av: satt til 10 (fig.

Etter å ha satt prognosen vil du se en endring i grafkurven for 10 observasjonsperioder fremover, som i fig. 42. Det reflekterer mest sannsynlig en ytterligere økning i salget med økte annonseinvesteringer.

Beregning med den resulterende formelen =237,96*LN(6)+5,9606 i Excel gir en verdi på 432 tusen rubler.

Excel har en FORECAST()-funksjon som beregner en fremtidig Y-verdi fra eksisterende par av X- og Y-verdier ved hjelp av lineær regresjon. Y-funksjonen bør være lineær hvis mulig, dvs. beskrives med en ligning som c+bx.

Prediksjonsfunksjonen for vårt eksempel vil bli skrevet som følger: =PREDICTION(K1,B2:J2,B1:J1). Skriv det ned - verdien skal være 643,6 tusen rubler.

Del 11. Testoppgaver

Forrige12345678910111213141516Neste

En trend er et mønster som beskriver økningen eller fallet til en indikator over tid. Hvis du skildrer noen tidsserier (statistiske data som er en liste over registrerte verdier av en variabel indikator over tid) på en graf, fremheves ofte en viss vinkel - kurven enten øker eller reduseres gradvis, i slike tilfeller er det vanlig å si at tidsserien har en tendens (henholdsvis å stige eller falle).

Trend som modell

Hvis vi bygger en modell som beskriver dette fenomenet, viser det seg å være ganske enkelt og veldig hendig verktøy for prognoser som ikke krever noen komplekse beregninger eller tid brukt på å kontrollere betydningen eller tilstrekkeligheten av påvirkningsfaktorer.

Så, hva er en trend som modell? Dette er et sett med beregnede ligningskoeffisienter som uttrykker regresjonsavhengighet indikator (Y) fra endringer i tid (t). Det vil si at dette er nøyaktig samme regresjon som de vi vurderte tidligere, bare den påvirkningsfaktoren her er tidsindikatoren.

Viktig!

I beregninger betyr t vanligvis ikke år, måned eller ukenummer, men heller serienummeret til perioden i den statistiske populasjonen som studeres - tidsserien. For eksempel, hvis en tidsserie studeres over flere år, og dataene ble registrert månedlig, er det fundamentalt feil å bruke en nullbasert nummerering av måneder, fra 1 til 12 og igjen fra begynnelsen. Det er også feil hvis studien av en serie begynner, for eksempel i mars, å bruke 3 (årets tredje måned) som verdien av t; hvis dette er den første verdien i populasjonen som studeres, så er dens serienummer. tallet skal være 1.

Lineær trendmodell

Som enhver annen regresjon kan en trend enten være lineær (graden av påvirkningsfaktoren t er lik 1) eller ikke-lineær (graden er større enn eller mindre enn én). Fordi lineær regresjon er den enkleste, men ikke alltid den mest nøyaktige, vil vi vurdere denne typen trend mer detaljert.

Generell form for lineær trendligning:

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

Der a 0 er null regresjonskoeffisienten, det vil si hva vil Y være hvis påvirkningsfaktoren er lik null, en 1 - regresjonskoeffisient, som uttrykker graden av avhengighet av den studerte indikatoren Y på påvirkningsfaktoren t, Ɛ - tilfeldig komponent eller standardfeil, i hovedsak forskjellen mellom de faktiske verdiene til Y og de beregnede. t er den eneste faktoren som påvirker tiden.

Jo mer uttalt tendensen er for indikatoren til å vokse eller falle, jo større vil koeffisienten a 1 være. Følgelig antas det at konstanten a 0 sammen med den tilfeldige komponenten Ɛ reflekterer de gjenværende regresjonspåvirkningene, i tillegg til tid, det vil si alle andre mulige påvirkningsfaktorer.

Du kan beregne modellkoeffisientene standard metode minste kvadrater (LSS). Med alle disse beregningene Microsoft Excel takler et smell på egen hånd, og for å få en lineær trendmodell eller en ferdig prognose finnes det hele fem metoder, som vi skal diskutere separat nedenfor.

Grafisk metode for å oppnå en lineær trend

I dette og alle andre eksempler vil vi bruke den samme dynamiske serien - nivået på BNP, som beregnes og registreres årlig; i vårt tilfelle vil studien finne sted i perioden fra 2004 til 2012.

La oss legge til en kolonne til til de originale dataene, som vi vil kalle t og merke i stigende tall serienumrene til alle registrerte BNP-verdier for den angitte perioden fra 2004 til 2012. – 9 år eller 9 perioder.

Excel vil legge til et tomt felt - markering for den fremtidige grafen, velg denne grafen og aktiver fanen som vises i menylinjen - Konstruktør, på jakt etter en knapp Velg data, i vinduet som åpnes, trykk på knappen Legg til. Et popup-vindu vil be deg velge data for å lage et diagram. Som feltverdi Serienavn velg cellen som inneholder teksten som passer best til navnet på grafen. I felt X-verdier angi intervallet av celler i kolonne t – påvirkningsfaktoren. I felt Y-verdier Vi indikerer intervallet til kolonneceller med kjente verdier av BNP (Y) - indikatoren som studeres.

Etter å ha fylt ut de angitte feltene, trykk på OK-knappen flere ganger og få klar tidsplan høyttalere. Velg nå selve graflinjen med høyre museknapp og fra den dukket opp kontekstmenyen Velg en gjenstand Legg til en trendlinje

Et vindu åpnes for å konfigurere parametrene for å konstruere en trendlinje, hvor blant modelltypene vi velger Lineær, sett en hake ved siden av elementene P gjengi en ligning på et diagram Og Plasser tilnærmet pålitelighetsverdi R2 på diagrammet, vil dette være nok til at den allerede konstruerte trendlinjen vises på grafen, samt en matematisk versjon av visning av modellen i form av en ferdig likning og en indikator for kvaliteten på modellen R 2. Hvis du er interessert i å vise prognosen på en graf for å visuelt vurdere gapet mellom indikatoren som studeres, angi i feltet Prognose fremover for antall interesseperioder.

Faktisk, det handler om denne metoden, du kan selvfølgelig legge til at den viste lineære trendligningen er selve modellen, som kan brukes som en formel for å få beregnede verdier fra modellen og følgelig eksakte verdier prognose (prognosen som vises på grafen kan bare estimeres omtrentlig), som er det vi gjorde i eksemplet vedlagt artikkelen.

Bygge en lineær trend ved å bruke LINEST-formelen

Essensen av denne metoden kommer ned til å søke etter lineære trendkoeffisienter ved å bruke funksjonen LINJEST Ved å erstatte disse påvirkningskoeffisientene i ligningen får vi en prediktiv modell.

Vi må velge to tilstøtende celler (i skjermbildet er disse cellene A38 og B38), deretter kaller vi funksjonen i formellinjen øverst (uthevet med rødt i skjermbildet ovenfor) ved å skrive "=LINEST(", etter som Excel vil vise tips om hva som kreves for denne funksjonen, nemlig:

velg et område med kjente verdier for den beskrevne indikatoren Y (i vårt tilfelle, BNP, er området uthevet i blått i skjermbildet) og sett et semikolon
angi utvalget av påvirkningsfaktorer X (i vårt tilfelle er dette t-indikatoren, serienummeret på perioder, uthevet i grønt i skjermbildet) og sett semikolon
den neste nødvendige parameteren for funksjonen er å bestemme om konstanten må beregnes, siden vi først vurderer en modell med en konstant (koeffisient en 0 ), og sett deretter enten "TRUE" eller "1" og et semikolon
Deretter må vi indikere om beregning av statistikkparametere er nødvendig (hvis vi vurderte dette alternativet, ville vi i utgangspunktet måtte allokere et område "for formelen" noen få linjer nedenfor). Angi behovet for å beregne statistiske parametere, nemlig standardverdi feil for koeffisienter, koeffisient for determinisme, standard feil for Y, Fisher-kriterium, frihetsgrader osv., de gir bare mening når du forstår hva de betyr, i så fall setter vi enten «TRUE» eller «1». Når det gjelder forenklet modellering, som vi prøver å lære, på dette stadiet av å skrive formelen, sett "FALSE" eller "0" og legg til etter den avsluttende parentesen ")"
for å "gjenopplive" formelen, det vil si for å få den til å fungere etter å ha spesifisert alle nødvendige parametere, er det ikke nok å trykke på Enter-knappen, må du trykke tre taster i rekkefølge: Ctrl, Shift, Enter

Som du kan se i skjermbildet ovenfor, ble cellene vi valgte for formelen fylt med de beregnede verdiene av regresjonskoeffisientene for den lineære trenden, i cellen B38 koeffisienten er funnet en 0 , og i cellen A38- avhengighetskoeffisient av parameteren t (eller x ), det er en 1 . Vi erstatter de oppnådde verdiene i ligningen lineær funksjon og vi får den ferdige modellen i matematisk uttrykk - y = 169.572,2+138.454,3*t

For å få beregnede verdier Y i henhold til modellen og følgelig for å få en prognose, trenger du bare å erstatte formelen i en Excel-celle, og i stedet t angi en lenke til cellen med det nødvendige periodenummeret (se cellen i skjermbildet D25).

For å sammenligne den resulterende modellen med reelle data, kan du konstruere to grafer, der du som X indikerer serienummeret til perioden, og som Y, i ett tilfelle - reelt BNP, og i det andre - beregnet (i skjermbildet, diagram til høyre).

Bygge en lineær trend ved å bruke regresjonsverktøyet i analysepakken

Artikkelen beskriver faktisk denne metoden fullstendig, den eneste forskjellen er at i våre første data er det bare en påvirkningsfaktor X (periodenummer – t ).

Som du kan se på bildet ovenfor, dataområde med kjente BNP-verdier uthevet som inndataintervall Y, og den tilsvarende område med periodetall t – som inndataintervall X. Resultatene av beregninger ved bruk av Analysepakken presenteres for eget ark og ser ut som et sett med tabeller (se figuren nedenfor) der vi er interessert i cellene som jeg malte i gult og grønne farger. I analogi med prosedyren beskrevet i artikkelen ovenfor, er en lineær trendmodell satt sammen fra de oppnådde koeffisientene y=169 572,2+138 454,3*t, på grunnlag av hvilke prognoser er laget.

Prognose ved hjelp av en lineær trend via TREND-funksjonen

Denne metoden skiller seg fra de foregående ved at den hopper over de tidligere nødvendige trinnene med å beregne modellparametrene og manuelt erstatte de oppnådde koeffisientene som en formel i en celle for å få en prognose; denne funksjonen produserer en ferdig beregnet prognoseverdi basert på kjente kildedata.

I målcellen (cellen der vi ønsker å se resultatet) setter vi et tegn er lik og kall den magiske funksjonen ved å skrive " TREND(", så må du markere , det vil si etter at vi har satt et semikolon og velg et område med kjente X-verdier, det vil si med periodetall t, som tilsvarer en kolonne med kjente BNP-verdier, sett igjen et semikolon og velg cellen med nummeret til perioden vi lager en prognose for (i vårt tilfelle kan imidlertid periodenummeret ikke angis ved referanse til celle, men ganske enkelt ved et tall direkte i formelen), sett deretter et nytt semikolon og indiker EKTE eller 1 , som bekreftelse for beregning av koeffisienten en 0 til slutt setter vi avsluttende parentes og trykk på tasten Tast inn.

Minus denne metoden er at den ikke viser verken modellligningen eller koeffisientene, og det er derfor vi ikke kan si at basert på en slik og en modell har vi mottatt en slik og en prognose, akkurat som det ikke er noen refleksjon av kvalitetsparametrene til modellen, imidlertid bestemmelseskoeffisient, som man kan si om det er fornuftig å ta hensyn til den resulterende prognosen eller ikke.

Prognose ved hjelp av en lineær trend ved hjelp av PROGNOSE-funksjonen

Essensen av denne funksjonen er helt identisk med den forrige, den eneste forskjellen er i rekkefølgen de første dataene er skrevet i formelen og i det faktum at det ikke er noen innstilling for tilstedeværelse eller fravær av en koeffisient en 0 (det vil si at funksjonen antyder at denne koeffisienten eksisterer i alle fall)

Som du kan se fra figuren ovenfor, skriver vi " =PREDIKSJON("og deretter angi celle med periodenummer, som det er nødvendig å beregne verdien etter lineær trend, det vil si prognosen, etter at vi har satt et semikolon, så markerer vi rekke kjente Y-verdier, det er kolonne med kjente BNP-verdier, sett deretter semikolon og marker område med kjente X-verdier, det er med periodetall t, som tilsvarer kolonnen med kjente BNP-verdier og til slutt setter vi avsluttende parentes og trykk på tasten Tast inn.

Resultatene oppnådd, som i metoden ovenfor, er kun det ferdige resultatet av å beregne den predikerte verdien ved bruk av en lineær trendmodell; den viser ingen feil eller selve modellen i matematiske termer.

For å oppsummere artikkelen

Vi kan si at hver av metodene kan være den mest akseptable blant andre, avhengig av det nåværende målet vi har satt for oss selv. De tre første metodene krysser hverandre både i mening og resultat, og egner seg for ethvert mer eller mindre seriøst arbeid der en beskrivelse av modellen og dens kvalitet er nødvendig. På sin side er de to siste metodene også identiske med hverandre og vil gi deg svar så raskt som mulig, for eksempel på spørsmålet: "Hva er salgsprognosen for neste år?"