Excel Bewegende Gemiddelde Trendline Formule

Bewegende gemiddelde Hierdie voorbeeld leer jy hoe om die bewegende gemiddelde van 'n tydreeks in Excel te bereken. 'N bewegende avearge gebruik te stryk onreëlmatighede (pieke en dale) om maklik tendense herken. 1. In die eerste plek kan 'n blik op ons tyd reeks. 2. Klik op die blad Data, kliek Data-analise. Nota: cant vind die Data-analise knoppie Klik hier om die analise ToolPak add-in te laai. 3. Kies bewegende gemiddelde en klik op OK. 4. Klik op die insette Range boks en kies die reeks B2: M2. 5. Klik op die boks interval en tik 6. 6. Klik in die uitset Range boks en kies sel B3. 8. Teken 'n grafiek van hierdie waardes. Verduideliking: omdat ons die interval stel om 6, die bewegende gemiddelde is die gemiddeld van die vorige 5 datapunte en die huidige data punt. As gevolg hiervan, is pieke en dale stryk uit. Die grafiek toon 'n toenemende tendens. Excel kan nie bereken die bewegende gemiddelde vir die eerste 5 datapunte, want daar is nie genoeg vorige datapunte. 9. Herhaal stappe 2 tot 8 vir interval 2 en interval 4. Gevolgtrekking: Hoe groter die interval, hoe meer die pieke en dale is glad nie. Hoe kleiner die interval, hoe nader die bewegende gemiddeldes is om die werklike data punte. Hou jy van hierdie gratis webwerf Deel asseblief hierdie bladsy op GoogleAdd, verandering, of verwyder 'n tendenslyn in 'n grafiek inligting oor vooruitskatting en wys tendense in kaarte trendlines word gebruik om grafies tendense in data voor te stel en te help probleme van voorspelling analiseer. Sulke analise is ook aangewys regressieanalise. Deur die gebruik van regressie-analise, kan jy 'n tendenslyn te brei in 'n grafiek as die werklike data om toekomstige waardes te voorspel. Byvoorbeeld, die volgende grafiek gebruik 'n eenvoudige lineêre tendenslyn wat voorspel twee kwartale voor 'n neiging in die rigting verhoging van inkomste toon duidelik. Wenke Jy kan ook 'n bewegende gemiddelde, wat glad uit skommelinge in data en toon die patroon of meer duidelik tendens skep. As jy 'n grafiek of data-reeks te verander sodat dit nie meer die verband tendenslyn kan ondersteun byvoorbeeld deur die verandering van die grafiek om 'n 3-D grafiek of deur die verandering van die lig van 'n PivotChart verslag of geassosieerde spilastabelverslag verslag die tendenslyn nie meer verskyn op die grafiek. Vir line data sonder 'n grafiek, kan jy gebruik outo-of een van die statistiese funksies, soos groei () of TREND (), om data vir die beste-pas lineêre of eksponensiële lyne te skep. Die keuse van die regte tendenslyn tipe vir jou data wanneer jy 'n tendenslyn te voeg tot 'n grafiek in Microsoft Office Excel, kan jy kies enige een van hierdie ses verskillende tendens of regressie tipes: lineêre trendlines, logaritmiese trendlines, polinoom trendlines, mag trendlines, eksponensiële trendlines, of bewegende gemiddelde trendlines. Die tipe data wat jy besluit watter tipe tendenslyn wat jy moet gebruik. A tendenslyn is mees akkurate wanneer sy R-kwadraat-waarde is by of naby 1. As jy 'n tendenslyn te pas om jou data, Excel bereken outomaties die R-kwadraat-waarde. As jy wil, kan jy hierdie waarde op jou grafiek vertoon. Lineêre trendlines 'n Lineêre tendenslyn is 'n beste-pas reguit lyn wat gebruik word met 'n eenvoudige lineêre datastelle. Jou data is lineêr as die patroon in sy datapunte 'n lyn lyk. 'N Lineêre tendenslyn toon gewoonlik dat daar iets is aan die toeneem of afneem teen 'n bestendige tempo. In die volgende voorbeeld, 'n lineêre tendenslyn illustreer dat yskas verkope konsekwent gestyg oor 'n tydperk van 13 jaar. Let daarop dat die R-kwadraat-waarde is 0,979, wat is 'n goeie passing van die lyn om die data. Logaritmiese trendlines n logaritmiese tendenslyn is 'n beste-pas geboë lyn wat gebruik word wanneer die tempo van verandering in die data toeneem of afneem vinnig en dan vlakke uit. 'N Logaritmiese tendenslyn kan beide negatiewe en positiewe waardes te gebruik. Die volgende voorbeeld gebruik van 'n logaritmiese tendenslyn te voorspel bevolkingsgroei van diere te illustreer in 'n vaste-ruimte gebied, waar die bevolking gelyk het as ruimte vir die diere afgeneem. Let daarop dat die R-kwadraat-waarde is 0,933, wat is 'n relatief goeie passing van die lyn om die data. Polinoom trendlines n polinoom tendenslyn is 'n geboë lyn wat gebruik word wanneer data skommel. Dit is nuttig, byvoorbeeld, vir die ontleding van winste en verliese oor 'n groot datastel. Die einde van die polinoom kan bepaal word deur die aantal skommelinge in die data of deur hoeveel draaie (heuwels en dale) verskyn in die kurwe. 'N Orde 2 polinoom tendenslyn het oor die algemeen net een koppie of vallei. Bestel 3 het oor die algemeen een of twee heuwels of dale. Bestel 4 het oor die algemeen tot drie heuwels of dale. Die volgende voorbeeld toon 'n Orde 2 polinoom tendenslyn (een koppie) om die verhouding tussen bestuur spoed en brandstofverbruik te illustreer. Let daarop dat die R-kwadraat-waarde is 0,979, wat is 'n goeie passing van die lyn om die data. Power trendlines n krag tendenslyn is 'n geboë lyn wat gebruik word met datastelle wat metings wat verhoog teen 'n spesifieke koers byvoorbeeld vergelyk, die versnelling van 'n renmotor met tussenposes 1 sekonde. Jy kan nie 'n krag tendenslyn as jou data bevat nul of negatiewe waardes. In die volgende voorbeeld word versnelling data getoon deur die plot afstand in meter deur sekondes. Die krag tendenslyn toon duidelik die toenemende versnelling. Let daarop dat die R-kwadraat-waarde is 0,986, wat is 'n byna perfekte pas van die lyn om die data. Eksponensiële trendlines 'n eksponensiële tendenslyn is 'n geboë lyn wat gebruik word wanneer data waardes styg of val by voortdurend die verhoging van belasting. Jy kan 'n eksponensiële tendenslyn skep as jou data bevat nul of negatiewe waardes. In die volgende voorbeeld word 'n eksponensiële tendenslyn wat gebruik word om die dalende hoeveelheid koolstof 14 illustreer in 'n voorwerp soos dit eeue. Let daarop dat die R-kwadraat-waarde is 0,990, wat beteken dat die lyn pas die data byna perfek. Bewegende gemiddelde trendlines n bewegende gemiddelde tendenslyn stryk uit skommelinge in die data om 'n patroon te wys of meer duidelik tendens. 'N bewegende gemiddelde gebruik van 'n spesifieke aantal datapunte (deur die opsie tydperk te stel), gemiddeldes, en maak gebruik van die gemiddelde waarde as 'n punt in die lyn. Byvoorbeeld, as Tydperk is ingestel op 2, die gemiddelde van die eerste twee datapunte word gebruik as die eerste punt in die bewegende gemiddelde tendenslyn. Die gemiddelde van die tweede en derde datapunte gebruik word as die tweede punt in die tendenslyn, ens .. In die volgende voorbeeld, 'n bewegende gemiddelde tendenslyn toon 'n patroon in die aantal huise wat verkoop meer as 'n 26-week periode. Voeg 'n tendenslyn Op 'n unstacked, 2-D, area, bar, kolom, lyn, voorraad, xy (strooi), of borrelkaart, kliek op die data reeks waaraan jy 'n tendenslyn of bewegende gemiddelde voeg, of doen die volgende om die data reeks uit 'n lys van grafiek elemente kies: Klik op enige plek in die grafiek. Dit vertoon die Chart tools. toevoeging van die ontwerp. Uitleg. en formaat oortjies. Op die blad Formaat, in die huidige seleksie groep, kliek op die pyltjie langs die boks Chart elemente en klik op die grafiek element wat jy wil. Let wel: As jy 'n grafiek wat meer as een datareeks het sonder die keuse van 'n data-reeks te kies, Excel vertoon die dialoog Trendline Voeg boks. In die lys boks, kliek op die data-reeks wat jy wil en klik op OK. Op die blad uitleg, in die analise groep, kliek Trendline. Doen een van die volgende: Klik op 'n vooraf gedefinieerde tendenslyn opsie wat jy wil gebruik. Let wel: Dit geld 'n tendenslyn sonder sodat jy spesifieke opsies te kies. Klik Meer Trendline Options. en dan in die kategorie Trendline Options, onder Trend / Regressie Tipe. Klik op die tipe tendenslyn wat jy wil use. Is daar 'n maklike manier om die tendens lyn formule toe te pas uit 'n tabel op enige gegewe X waarde in Excel Byvoorbeeld, ek wil die Y waarde vir 'n gegewe X 2,006.00 kry. Ive reeds die formule geneem en oorgetik dit uit te wees: -0.000000000008X3 - 0.00000001X2 0.0003X - 0,0029 ek voortdurend maak aanpassings aan die tendens lyn deur die toevoeging van meer inligting, en nie wil om te tik die formule elke keer. Ek don39t wil stem in die tendenslyn formule VBA antwoord, maar ek wil sê dat LINEST is baie makliker as die VBA benadering, omdat dit gebruik berekeninge direk, nie 'n formule wat nie kan geformateer tfor voldoende akkuraatheid (sien WWhalley39s vroeër kommentaar: gebruik 'n aantal formaat van 0.000000000000E00 te help verbeter die tendenslyn formula39s akkuraatheid). uitvoering maak Jon Peltier 27 November 12 aan 21:40 het ek 'n oplossing wat vir elke tipe trendlines (behalwe vir bewegende gemiddelde natuurlik). Wil jy dalk die akkuraatheid van die Datalabel stel om jou needs. In my onlangse boek Praktiese Tyd Reeks vooruitskatting te pas: 'n Praktiese Gids. Ek sluit 'n voorbeeld van die gebruik van Microsoft blink bewegende gemiddelde komplot om maandelikse seisoenaliteit onderdruk. Dit word gedoen deur die skep van 'n lyn plot van die reeks met verloop van tyd en dan Voeg Trendline GT bewegende gemiddelde (sien my post oor die onderdrukking van seisoenaliteit). Die doel van die toevoeging van die bewegende gemiddelde Trendline 'n tyd plot is 'n tendens beter te sien in die data, deur die onderdrukking van seisoenaliteit. 'N bewegende gemiddelde met venster breedte w middel van gemiddeld oor elke stel w agtereenvolgende waardes. Vir die verbeelding van 'n tydreeks, gebruik ons ​​gewoonlik 'n gesentreerde bewegende gemiddelde met w seisoen. In 'n gesentreerde bewegende gemiddelde, is die waarde van die bewegende gemiddelde op tydstip t (MA t) bereken deur sentrering die venster om tyd t en gemiddeld oor die w waardes binne die venster. Byvoorbeeld, as ons daaglikse data en ons vermoed dat 'n dag-tot-week effek, kan ons dit onderdruk deur 'n gesentreerde bewegende gemiddelde met W7, en dan plot die MA lyn. 'N oplettend deelnemer in my aanlyn kursus vooruitskatting ontdek dat uitblink bewegende gemiddelde produseer nie wat wed verwag: In plaas van gemiddeld meer as 'n venster wat gesentreer rondom 'n tydperk van belang is, is dit net neem die gemiddelde van die laaste w maande (bekend as 'n sleep bewegende gemiddelde). Terwyl sleep bewegende gemiddeldes is nuttig vir vooruitskatting, hulle is minderwaardig vir visualisering, veral wanneer die reeks het 'n tendens. Die rede hiervoor is dat die sleep bewegende gemiddelde loop agter. Kyk na die onderstaande figuur is en jy kan die verskil tussen blink sleep bewegende gemiddelde (swart) en 'n gesentreerde bewegende gemiddelde (rooi) te sien. Die feit dat Excel produseer 'n sleep bewegende gemiddelde in die menu Trendline is nogal ontstellend en misleidend. Selfs meer kommerwekkend is die dokumentasie. wat verkeerd beskryf die sleep MA wat geproduseer word: As Tydperk is ingestel op 2, byvoorbeeld, dan is die gemiddelde van die eerste twee datapunte word gebruik as die eerste punt in die bewegende gemiddelde tendenslyn. Die gemiddelde van die tweede en derde datapunte gebruik word as die tweede punt in die tendenslyn, en so aan. Vir meer inligting oor bewegende gemiddeldes, kyk hier: Die term tendense impliseer 'n verandering met verloop van tyd. Een tipe voorspelling is kwantitatiewe en behels die ontleding van tydreeksdata, en dan voorspel wat die toekoms kan wees. Byvoorbeeld, verkope by 'n roomys staan ​​by die dorp park in Junie van elk van die afgelope vyf jaar is goed, maar in Julie was dit sowat 20 meer as in Junie. As vanjaar het die stalletjie het in 10000 in Junie ( 'n nuwe rekord), hoeveel sou jy voorspel dit sal in Julie Wel neem, as ons reg in ons aanname is gebaseer op die historiese data, wed raming die figuur Julie sal wees 20 hoër, of 12.000. Microsoft Excel bied 'n paar ingeboude tools vir vooruitskatting. Een van hierdie kan jy 'n tendenslyn bestaande data punte by te voeg op 'n grafiek. Dit laat die gebruiker toe om interpoleer (dws 'n data punt tussen bestaande punte te vind) of om te ekstrapoleer (dws 'n data punt verby albei kante van die huidige data te vind, óf deur die voorspelling voorwoord, of quotbackcastingquot na 'n vroeëre tydperk.) Maar soos met templates, het die ontwikkelaars van hierdie instrumente paar besluite wat geneem is vir die gebruiker, en nie alle gebruikers sal saamstem met daardie besluite. As die wat deur Microsoft beperkings Excels funksies vir vooruitskatting is onvanpas vir 'n spesifieke voorspelling taak, is die leser in plaas aangemoedig om direkte numeriese manipulasie gebruik met behulp van bewese analitiese tegnieke soos beskryf in enige van verskeie tekste oor vooruitskatting (soos Makridakis, wielmaker amp Hyndman, 1998). Voordat jy begin hierdie bladsy veronderstel die gebruiker Microsoft Excel8482 2010 of 2007 met die analise ToolPak byvoeging In Microsoft geïnstalleer. Kom ons kyk na 'n paar inligting oor kompakte fluorescent lampe (CFLs) deur die volgende as 'n brondokument: Amerikaanse Departement van Energie. (2009). CFL Market Profiel - Maart 2009. Washington, DC: outeur. Ontsluit April 7, 2009 van www. energystar. gov/ia/products/downloads/CFLMarketProfile. pdf Die ontleding in hierdie verslag is wat uitgevoer word deur D amp R International, LTD (www. drintl /.) Op bladsy 2, daar is 'n bar grafiek (of staafgrafiek) dat die aantal besendings van CFLs lys van jaar tot 2007, en dan voorspel die aantal besendings in 2008, 2009 en 2010, wat gebaseer is op die data. Kom ons gebruik die data in hierdie tabel, en die krag van Microsoft Excel, om 'n soortgelyke voorspelling te maak. Die ideaal is, sal jy die werklike datawaardes het, maar in hierdie geval, is 'n skatting gemaak op grond van die bogenoemde grafiese en die volgende is in 'n Excel spreiblad geloop. Tabel 1. Rou data. Kom net kyk na die historiese data vir 2000 tot 2007 nie op die skattings of voorspellings vir 2008 tot 2010. Ons kan die kolomgrafiek wat in die brondokument herskep deur die kies van die historiese data in Excel en die skep van 'n kolomgrafiek: Figuur 2. rou data in 'n kolomgrafiek om oorspronklike wedstryd, maar in plaas daarvan, kan skep 'n spreidiagram van die waardes (sedert blink tendenslyn vergelyking funksie foute met staafgrafieke of lyngrafieke kan produseer.) Figuur 3. rou data in 'n spreidiagram. 'n Lineêre Trendline en regressievergelyking die toevoeging Nou, onthou, ons is net bekommerd oor die CFL data, en ons wil in staat wees om die komende jaar te voorspel. Ten einde 'n tendenslyn te voeg, kliek op een van die ikone wat 'n data punt vir CFLs, en dan regs-kliek en kies quotAdd Trendline. quot sal jy sien die volgende dialoog. In hierdie voorbeeld sal ons veronderstel dat die getal CFLs verskeep per jaar toeneem teen 'n bestendige of lineêre koers. Vir nou, in die gebied Trendline Options, kies die volgende Trend / Regressie type: Lineêre voorspelling - Stuur 3 periodes Wys vergelyking op grafiek Na die verskuiwing van die vergelyking wat ons het: Figuur 5. Rou data met 'n lineêre Trendline en regressievergelyking. Die vergelyking is 'n lineêre regressievergelyking. Dit beteken dit is dit die vergelyking van 'n reguit lyn wat die beste pas by die punte op die grafiek. Die metode Excel gebruik om hierdie vergelykings te bepaal behels die vind van die lyn wat die minste waarde produseer vir die som van die kwadrate van die vertikale verskil tussen datapunte en die lyn. Soos alle lyne, dit het 'n vergelyking in die vorm: y die aantal bereken moet word, die afhanklike veranderlike, of in hierdie geval, die aantal miljoene CFLs verskeep per jaar m die helling van die lyn, wat die verandering gelyk in die y waarde gedeel deur die verandering in die x waarde x is die gegewe data punt of die afhanklike veranderlike, in hierdie geval, is dit die jaar en b is die y - as afsnit van die lyn. y 388000000 CFLs verskeep Ons kan vervang ander waardes vir x, soos die jaar 2020, en sedertdien het ons nou 'n vergelyking, kan ons voorspel dat daar 793000000 CFLs in die jaar verskeep 2020. Natuurlik, dit is 'n baie aannames wat ons behoort nie te maak. In die besonder, ons neem aan dat die tendens is lineêre, en dat dit ver in die toekoms sal aanhou. Alternatiewe Metode. Jy kan direk uit te vind die vergelyking van die tafel gelê data, as jy wil. Kies twee selle soos G5 en G6 en dan tik in die formule: LINEST (reeks) vir die reeks, kies al die bekende y waardes, tik die sluiting hakies, maar moenie Enter druk nie. In plaas daarvan, getref beheer-Shift-Enter. Jy sal sien die helling en die afsnit verskyn in hierdie twee selle. Baie tendense is nie liniêr. Byvoorbeeld, menslike bevolking op die planeet was redelik lineêre, maar dan geskiet it up, soos geïllustreer deur die rooi lyn in die volgende figuur: Figuur 7. Nie-lineêre tendens van quotLong termyn World Population Growth. quot Dit grafiese is uit United nasies, 1999, p. 7. Daar is verskeie nie-lineêre voorspelbare vergelykings. Wel kyk na twee, eksponensiële vergelykings en polinoomvergelykings, maar jy word aangeraai om ander te verken. Kom ons neem dat dieselfde historiese CFL gestuur data wat ons hierbo gebruik en 'n paar nie-lineêre trendlines toe te pas. Hier is 'n eksponensiële tendenslyn. Dit maak gebruik van 'n vergelyking wat die x-waarde (die jaar) as 'n eksponent het. Ek het op die nuwe vergelyking 'n uitgesoekte quotformat tendenslyn labelquot om die vergelyking in wetenskaplike notasie met ses desimale punte vertoon, sedert die verstek my nie genoeg presisie gee nie vir die voorspelling. Figuur 8. Rou data met eksponensiële tendenslyn. Soos ons kan sien, is daar tendenslyn is geboë, nie heeltemal soveel soos aangedui deur die relatief hoë 2007 Datum punt, maar dit is nog steeds gebuig het. Die voorspellende vergelyking is: y 1.598767 E -279 e 3,226616 E -01 x Onthou dat die hoofstad E beteken quotTimes tien tot die mag ofquot en dat die laer geval e is 'n konstante ongeveer gelyk aan 2,71828. In Excel, kan ek dan tik die volgende formule in enige sel en deur quot2010quot te vervang met die jaar, kry 'n voorspelling vir die jaar. Die waarde vir 2010 is 733,000,000 CFLs, en die waarde vir 2012 is 1398000000 CFLs. Die voorspellende vergelyking kan 'n polinoom wees. Ons het gesien dat die lineêre regressievergelyking was 'n tweede-orde, of kwadratiese, polinoomvergelyking voeg 'n x 2 term, wat lei tot: Die grafiek van 'n kwadratiese vergelyking van hierdie vorm is tipies 'n parabool. Hier is die dieselfde data met 'n tweede orde polinoom tendenslyn: Figuur 9. Tweede orde polinoom tendens lyn met vergelyking. Dit is moontlik om die orde te verhoog, en voeg 'n X 3. X 4. of x 5 termyn, indien daar rede is om te glo soos 'n kurwe sal meer akkuraat wees. Soms, vermoed ons die data moet verander. In ons voorbeeld, sien hoe hoog die waarde van 400 was vir 2007. 'n ontleder kan rede om te glo dat hierdie punt was 'n uitskieter het, en as gevolg van 'n paar spesiale omstandighede, soos 'n one-time bemarking blitz, die hoë waarde van hierdie datum gooi af die toekoms te voorspel. Kom ons verander die data, die vermindering van die punt tot 300. Tabel 2. Hersiene data. Die gebruik van die hersiene data en tweede-orde polinoom vooruitskatting, kry ons: Figuur 10. Die waarde van 2007 is verander 400-300 in die oortuiging dat dit was 'n abnormale waarde. Let op hoe Figuur 10 is relatief naby aan die aanvanklike voorspelling wat in die oorspronklike Amerikaanse DOE brondokument. Daar is baie maniere om data te omskep en aan te pas, en in elke geval die ontleder n verdedigbare lyn van redenasie wat die transformasie regverdig moet hê. Soos met baie vorme van statistiese analise, kan tendenslyn ekstrapolasie onderhewig aan pogings om die data voor te stel die ontleders vooroordeel beraadslaag. Dit is onvanpas. Waar daar alternatiewe projeksies, is dit die beste om dit aan te bied met verduidelikings van elke. Byvoorbeeld, die volgende illustrasie toon verskillende paaie wêreldbevolking kon neem verskillende toestande verduidelik deur die skrywers. Soos gesien op blink Trendline Options dialoog, daar is ander vorme van trendlines wat bygevoeg kan word, insluitende 'n logaritmiese, krag, en bewegende gemiddelde tendenslyn. Die ontleding ToolPak Voeg-in vir Excel het ook verskeie vooruitskatting gereedskap. Om dit te bekom, kliek Data-analise in die blad Data. Jy sal bewegende gemiddelde, regressie, en eksponensiële gladstryking daar, wat almal kan gebruik word om te voorspel sien. Maar moenie daar stop, Excel, soos 'n paar ander programme vir numeriese manipulasie, die gebruiker toelaat om die formules wat gebruik word om waardes lei direk beheer. Ons hoef nie te skik vir die standaard instellings wat in die Add Trendline kenmerk van kaarte, maar ons kan plaas direk die nodige berekeninge op die data. Vir meer inligting oor die metodes wat in hierdie les, en ander, soos die Box-Jenkins metode, dinamiese regressie, meervoudige regressie, asseblief 'n teks te raadpleeg oor vooruitskatting, soos die een vir Makridakis, wielmaker, amp Hyndman (1998). Makridakis. S. wielmaker, S. amp Hyndman, R. (1998). Vooruitskatting: metodes en toepassings. 3rd ed. New York: Wiley amp Sons. Verenigde Nasies. (1998). Lang afstand Wêreld Bevolkingsprojeksies: Op grond van die Hersiening 1998. Uitvoerende Opsomming. Skrywer. Ontsluit 7 April, 2009 van www. un. org/esa/population/publications/longrange/longrangeExecSum. pdf Verenigde State se Departement van Energie. (2009). CFL Market Profiel - Maart 2009. Washington, DC: outeur. Ontsluit April 7, 2009 van www. energystar. gov/ia/products/downloads/CFLMarketProfile. pdf