Trendmodell i excel. Beregning av trendligningsparametere
Det som gjør det annerledes å utføre oppgaver for å konstruere en trendlinje, er at kildedataene kan være et sett med tall som ikke er relatert til hverandre.
Prognoser ved å bruke en vanlig tidsplan er umulig, siden dens determinismekoeffisient (R^2) vil være nær null.
Det er derfor spesielle funksjoner brukes.
Nå skal vi bygge, konfigurere og analysere dem.
Enkel byggeversjon
Prosessen med å konstruere en trendlinje består av tre stadier: legge inn innledende data i Excel, plotte en graf, velge en trendlinje og dens parametere.
La oss starte med dataregistrering.
1. Opprett inn Excel regneark med de originale dataene.
(Bilde 1)
2. Velg cellene B3:B17 og gå til "Sett inn"-fanen og velg "Graph".
(Figur 2)
3. Når grafen er opprettet, kan du legge til bildetekster og en tittel.
Først venstreklikker du på kanten av grafen for å velge den.
Gå deretter til "Design"-fanen og velg "Layout 1".
(Figur 3)
4. La oss gå videre til å bygge en trendlinje. For å gjøre dette, velg diagrammet igjen og gå til "Layout"-fanen.
(Figur 4)
5. Klikk på "Trendlinje"-knappen og velg "lineær tilpasning" eller "eksponentiell tilpasning".
(Figur 5)
Så vi bygde en primær trendlinje, som kanskje ikke samsvarer mye med virkeligheten.
Dette er vårt mellomresultat.
(Figur 6)
Og derfor må vi justere parametrene til trendlinjen vår eller velge en annen funksjon.
Profesjonell versjon: velge en trendlinje og angi parametere
6. Klikk på "Trendlinje"-knappen og velg "Ytterligere parametere og trendlinjer".
(Figur 7)
7. I "Trendlinjeformat"-vinduet merker vi av i boksen ved siden av "plasser den tilnærmede pålitelighetsverdien R^2 på diagrammet og klikk på "lukk"-knappen.
Vi ser i diagrammet koeffisienten R^2= 0,6442
(Figur 8)
8. Avbryt endringene. Velg diagrammet, klikk på fanen "Layout", "trendlinje"-knappen og velg "Nei".
9. Gå til "Trend Line Format"-vinduet, men for å velge "Polynomial" trendlinjen, endrer vi graden, og oppnår koeffisientindikatorer R^2 = 0,8321
(Figur 9)
Prognose
Hvis vi trenger å gjette hvilke data som kan oppnås i neste måling, i vinduet "Trendlinjeformat", angir vi antall perioder prognosen er laget for.
(Figur 10)
Basert på prognosen kan vi anta at 25. januar ville antall poeng vært mellom 60 og 70.
Konklusjon
Og til slutt, hvis du er interessert i formelen som trenden er konstruert etter, i delen "Trendlinjeformat", merk av i boksen ved siden av "vis ligningen på diagrammet."
Nå vet du hvordan du fullfører oppgaven og bygger en trendlinje, selv i et program som excel 2010.
Still spørsmål, ikke vær sjenert.
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 . I dette tilfellet, i en dynamikkserie med et oddetall nivåer, er serienummeret til nivået som ligger i midten av serien betegnet 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 av alle tidligere nivåer. For eksempel, når du angir tid vil det være: –2, –1, 0, +1, +2. Med et jevnt antall nivåer serienummer Den øvre halvdelen av raden (fra midten) er indikert med tallene: –1, –3, –5, og den nedre halvdelen av raden er indikert 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 av 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 funnene dine.
| 1990 | 1996 | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 |
| 1249 | 1133 | 1043 | 1030 | 1016 | 1005 | 996 | 985 | 975 | 968 |
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, for prognoser er det nødvendig å bruke en parabolsk ligning.
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- standard feil spådd indikator; 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)
Diagrammer og grafer brukes til å analysere numeriske data, for eksempel for å evaluere forholdet mellom to typer verdier. For dette formålet kan en trendlinje og dens ligning, prognoseverdier beregnet for flere perioder fremover eller bakover, legges til dataene i et diagram eller en graf.
Trendlinje representerer en rett eller buet linje som tilnærmer (bringer nærmere) de opprinnelige dataene basert på en regresjonsligning eller glidende gjennomsnitt. Tilnærmingen bestemmes ved hjelp av minste kvadraters metode. Avhengig av arten av oppførselen til kildedataene (minkende, økende osv.), velges en interpolasjonsmetode som skal brukes til å bygge en trend.
Det er flere alternativer for å danne en trendlinje.
Lineær funksjon: y=mx+b
der m er tangenten til helningsvinkelen til den rette linjen, b er forskyvningen.
Rett trendlinje ( lineær trend) den beste måten egnet for mengder som endres med en konstant hastighet. Brukes i tilfeller der datapunkter er plassert nær en rett linje.
Logaritmisk funksjon: y=c*lnx+b
hvor c og b er konstanter.
En logaritmisk trendlinje tilsvarer en dataserie hvis verdier først øker eller reduseres raskt og deretter gradvis stabiliseres. Kan brukes til positive og negative data.
Polynomfunksjon (opp til 6. grad inklusive): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6
hvor b, c 1, c 2, ... c 6 er konstanter.
En polynomtrendlinje brukes til å beskrive vekselvis økende og minkende data. Graden til polynomet velges slik at den er lik én mer mengde ekstrema (maksima og minima) av kurven.
Strømfunksjon: y = cxb
hvor c og b er konstanter.
Strømtrendlinjen gir gode resultater for positive data med konstant akselerasjon. For serier med null eller negative verdier er konstruksjonen av den angitte trendlinjen umulig.
Eksponentiell funksjon: y = cebx
der c og b er konstanter, er e basisen til den naturlige logaritmen.
Eksponentiell trend brukes når endringen i data øker kontinuerlig. Å konstruere den indikerte trenden er umulig hvis settet med verdier til seriemedlemmene inneholder null eller negative data.
Bruk av lineær filtrering i henhold til formelen: F t = (A t +A (t-1) +⋯+A (t-n+1))/n
hvor n - totalt antall medlemmer av serien, t er et gitt antall punkter (2 ≤ t< n).
En trend med lineær filtrering lar deg jevne ut datasvingninger, og tydelig demonstrere avhengighetenes natur. For å bygge den angitte trendlinjen, må brukeren spesifisere et tall - en filterparameter. Hvis tallet er 2, er det første punktet på trendlinjen definert som gjennomsnittet av de to første dataelementene, det andre punktet er definert som gjennomsnittet av det andre og tredje dataelementet, osv.
For noen typer diagrammer kan en trendlinje i prinsippet ikke konstrueres - stablede diagrammer, volumetriske diagrammer, radarkart, sektordiagram, overflatekart og smultringkart. Hvis mulig kan du legge til flere linjer med forskjellige parametere i diagrammet. Tilsvaret av trendlinjen til de faktiske verdiene til dataserien er etablert ved å bruke tilnærmingens pålitelighetskoeffisient:
Trendlinjen og dens parametere legges til diagramdataene ved å bruke følgende kommandoer:
Om nødvendig kan du endre linjeparametrene ved å klikke på en rad med diagramdata eller en trendlinje for å åpne vinduet Trendlinjeformat. Du kan legge til (eller slette) en regresjonsligning, en tilnærmingspålitelighetskoeffisient, bestemme retningen og prognosen for endringer i en dataserie, og også korrigere designelementene til trendlinjen. Den valgte trendlinjen kan også slettes.
Figuren viser en tabell med data om endringer i verdien av et verdipapir. Basert på disse betingede dataene ble det konstruert et spredningsplott, en tredjeordens polynomtrendlinje (definert med en stiplet linje) og noen andre parametere ble lagt til. Den oppnådde verdien av tilnæR 2 i diagrammet er nær enhet, noe som indikerer nærhet bosettingslinje trend med oppgavedata. Den forutsagte verdien av endringer i verdien av et verdipapir er rettet mot vekst.
Hva skal jeg gjøre hvis det ikke er noen tidsmålinger for visse volum/størrelser av produkter? Eller er antallet målinger utilstrekkelig, og ytterligere observasjoner kan ikke gjøres i nær fremtid? Den beste måten Løsningen på dette problemet er å bygge beregnede avhengigheter (regresjonsligninger) ved hjelp av trendlinjer i MS Excel.
La oss vurdere en reell situasjon: i et lager, for å fastslå mengden arbeidskostnader for boksplukking av en ordre, ble det utført tidsobservasjoner. Resultatene av disse observasjonene er presentert i tabell 1 nedenfor.
Deretter oppsto behovet for å fastsette tiden det tar å velge 0,6 og 0,9 m3 varer/ordre. På grunn av umuligheten av å gjennomføre ytterligere tidsstudier, ble tiden brukt på å velge disse ordrevolumene beregnet ved å bruke regresjonsligninger i MS Excel. For å oppnå dette ble tabell 1 konvertert til tabell 2.
Velge et spredningsplott, fig. 1
Neste trinn: musepekeren ble plassert på et av grafpunktene og vha høyre knapp mus ble forårsaket kontekstmenyen, der varen ble valgt: «legg til trendlinje» (Fig. 2).
Legge til en trendlinje, fig. 2
I vinduet som vises for å angi trendlinjeformatet (Fig. 3) ble sekvensielt valgt: lineær/kraftledningstype og sjekket følgende elementer: "vis ligningen på diagrammet" og "plasser tilnærmingens pålitelighetsverdi (R^2) på diagrammet" (bestemmelseskoeffisient).
Trendlinjeformat, fig. 3
Som et resultat ble grafene presentert i fig. 1 oppnådd. 4 og 5.
Lineær beregnet avhengighet, fig. 4
Maktlovsberegnet avhengighet, fig. 5
En visuell analyse av grafene indikerer tydelig nærhet til de oppnådde avhengighetene. I tillegg er approksimasjonspålitelighetsverdien (R^2), som også kalles bestemmelseskoeffisienten, i tilfelle av begge avhengigheter den samme verdien på 0,97. Det er kjent at jo nærmere bestemmelseskoeffisienten er 1, jo mer samsvarer trendlinjen med virkeligheten. Det kan også opplyses at 97 % av endringen i tidsbruk på ordrebehandling forklares med endring i varemengden. Derfor, i i dette tilfellet ikke viktig: hvilken beregnet avhengighet som bør velges som hoved for den etterfølgende beregningen av tidskostnader.
La oss ta den lineære beregnede avhengigheten som den viktigste. Da vil tidskostnadene avhengig av varemengden bli bestemt av formelen: y = 54,511x + 0,1489. Resultatene av disse beregningene for varemengden som det tidligere ble utført tidsobservasjoner for, er presentert i tabell 3 nedenfor.
La oss bestemme det gjennomsnittlige avviket for tidsbruken beregnet ved hjelp av regresjonsligningen fra tidsbruken beregnet fra tidsholdende observasjoner: (-0,05+0,10-0,05+0,01)/4=0,0019. Dermed skiller tidsforbruket beregnet ved bruk av regresjonsligningen seg fra tidsforbruket beregnet fra tidsregistreringsdata med kun 0,19%. Avviket mellom dataene er ubetydelig.
Ved å bruke formelen: y = 54,511x + 0,1489, vil vi fastslå tidskostnadene for varemengden som det ikke tidligere er utført tidsobservasjoner for (Tabell 4).
Konstruere beregnede avhengigheter ved å bruke trendlinjer i MS Excel - Dette er en utmerket måte å fastslå tidsbruken på operasjoner som av ulike årsaker ikke ble dekket av tidtakende observasjoner.