Flytende gjennomsnitt Dette eksemplet lærer deg hvordan du beregner det bevegelige gjennomsnittet av en tidsserie i Excel. Et glidende gjennomsnitt brukes til å utjevne uregelmessigheter (topper og daler) for enkelt å gjenkjenne trender. 1. Først, ta en titt på vår tidsserie. 2. På Data-fanen klikker du Dataanalyse. Merk: kan ikke finne dataanalyseknappen Klikk her for å laste inn add-in for Analysis ToolPak. 3. Velg Flytt gjennomsnitt og klikk OK. 4. Klikk i feltet Inngangsområde og velg området B2: M2. 5. Klikk i intervallboksen og skriv inn 6. 6. Klikk i feltet Utmatingsområde og velg celle B3. 8. Skriv en graf av disse verdiene. Forklaring: fordi vi angir intervallet til 6, er glidende gjennomsnitt gjennomsnittet for de forrige 5 datapunktene og det nåværende datapunktet. Som et resultat blir tinder og daler utjevnet. Grafen viser en økende trend. Excel kan ikke beregne det bevegelige gjennomsnittet for de første 5 datapunktene fordi det ikke er nok tidligere datapunkter. 9. Gjenta trinn 2 til 8 for intervall 2 og intervall 4. Konklusjon: Jo større intervallet jo flere tinder og daler utjevnes. Jo mindre intervallet, desto nærmere beveger gjennomsnittet seg til de faktiske datapunktene. Gjennomgang av gjennomsnittlig prognoseinnledning. Som du kanskje tror vi ser på noen av de mest primitive tilnærmingene til prognoser. Men forhåpentligvis er disse minst en verdig innføring i noen av databehandlingsproblemene knyttet til implementering av prognoser i regneark. I denne veinen vil vi fortsette med å starte i begynnelsen og begynne å jobbe med Moving Average prognoser. Flytte gjennomsnittlige prognoser. Alle er kjent med å flytte gjennomsnittlige prognoser, uansett om de tror de er. Alle studenter gjør dem hele tiden. Tenk på testresultatene dine i et kurs der du skal ha fire tester i løpet av semesteret. La oss anta at du fikk en 85 på din første test. Hva vil du forutsi for din andre testscore Hva tror du at læreren din ville forutsi for din neste testscore Hva tror du dine venner kan forutsi for din neste testscore Hva tror du at foreldrene dine kan forutsi for neste testresultat uansett om alt det du kan gjøre med dine venner og foreldre, de og din lærer er veldig sannsynlig å forvente deg å få noe i området av 85 du nettopp har fått. Vel, nå kan vi anta at til tross for selvforfremmelse til vennene dine, overestimerer du deg selv og figurerer du kan studere mindre for den andre testen, og så får du en 73. Nå er det alle de bekymrede og ubekymrede går til Forvent deg at du kommer på den tredje testen. Det er to svært sannsynlige tilnærminger for dem å utvikle et estimat, uansett om de vil dele det med deg. De kan si til seg selv, at denne fyren alltid blåser røyk om hans smarts. Hes kommer til å få en annen 73 hvis han er heldig. Kanskje foreldrene vil prøve å være mer støttende og si, quote, så langt har du fått en 85 og en 73, så kanskje du burde finne på å få en (85 73) 2 79. Jeg vet ikke, kanskje hvis du gjorde mindre fest og werent vevet vasselen over alt, og hvis du begynte å gjøre mye mer å studere, kan du få en høyere score. quot Begge disse estimatene flytter faktisk gjennomsnittlige prognoser. Den første bruker bare din siste poengsum for å prognose din fremtidige ytelse. Dette kalles en flytende gjennomsnittlig prognose ved hjelp av en periode med data. Den andre er også en flytende gjennomsnittlig prognose, men bruker to perioder med data. La oss anta at alle disse menneskene bråser på ditt store sinn, har slags pisset deg av og du bestemmer deg for å gjøre det bra på den tredje testen av dine egne grunner og for å sette en høyere poengsum foran din quotalliesquot. Du tar testen og poengsummen din er faktisk en 89 Alle, inkludert deg selv, er imponert. Så nå har du den endelige testen av semesteret som kommer opp, og som vanlig føler du behovet for å få alle til å gjøre sine spådommer om hvordan du skal gjøre på den siste testen. Vel, forhåpentligvis ser du mønsteret. Nå, forhåpentligvis kan du se mønsteret. Hvilke tror du er den mest nøyaktige fløyten mens vi jobber. Nå går vi tilbake til vårt nye rengjøringsfirma som startes av din fremmedgjorte halv søster, kalt Whistle While We Work. Du har noen tidligere salgsdata som er representert av følgende del fra et regneark. Vi presenterer først dataene for en tre-års glidende gjennomsnittlig prognose. Oppføringen for celle C6 skal være Nå kan du kopiere denne celleformelen ned til de andre cellene C7 til C11. Legg merke til hvordan gjennomsnittet beveger seg over de nyeste historiske dataene, men bruker nøyaktig de tre siste perioder som er tilgjengelige for hver prediksjon. Du bør også legge merke til at vi ikke virkelig trenger å gjøre spådommene for de siste perioder for å utvikle vår siste prediksjon. Dette er definitivt forskjellig fra eksponentiell utjevningsmodell. Ive inkluderte quotpast predictionsquot fordi vi vil bruke dem på neste nettside for å måle prediksjonens gyldighet. Nå vil jeg presentere de analoge resultatene for en to-års glidende gjennomsnittlig prognose. Oppføringen for celle C5 skal være Nå kan du kopiere denne celleformelen ned til de andre cellene C6 til C11. Legg merke til hvordan nå bare de to siste stykkene av historiske data blir brukt for hver prediksjon. Igjen har jeg tatt med quotpast predictionsquot for illustrative formål og for senere bruk i prognose validering. Noen andre ting som er viktig å legge merke til. For en m-periode som beveger gjennomsnittlig prognose, brukes bare de nyeste dataverdiene for å gjøre prognosen. Ingenting annet er nødvendig. For en m-periode som beveger gjennomsnittlig prognose, legger du merke til at den første prediksjonen forekommer i periode m 1. Begge disse problemene vil være svært viktige når vi utvikler koden vår. Utvikle den bevegelige gjennomsnittsfunksjonen. Nå må vi utvikle koden for den bevegelige gjennomsnittlige prognosen som kan brukes mer fleksibelt. Koden følger. Legg merke til at inngangene er for antall perioder du vil bruke i prognosen og rekke historiske verdier. Du kan lagre den i hvilken arbeidsbok du vil ha. Funksjon MovingAverage (Historical, NumberOfPeriods) Som Single Deklarering og Initialisering av variabler Dim Item Som Variant Dim Counter Som Integer Dim Akkumulering Som Single Dim HistoricalSize Som Integer Initialiserende variabler Teller 1 Akkumulering 0 Bestemme størrelsen på Historical array HistoricalSize Historical. Count For Counter 1 To NumberOfPeriods Akkumulere riktig antall siste tidligere observerte verdier Akkumulasjonsakkumulering Historisk (HistoricalSize - NumberOfPeriods Counter) MovingAverage AkkumuleringsnummerOfPeriods Koden vil bli forklart i klassen. Du vil plassere funksjonen på regnearket slik at resultatet av beregningen vises der det vil like følgende. DAX inneholder noen statistiske aggregasjonsfunksjoner, for eksempel gjennomsnitt, varians og standardavvik. Andre typiske statistiske beregninger krever at du skriver lengre DAX-uttrykk. Excel, fra dette synspunktet, har et mye rikere språk. De statistiske mønstrene er en samling av vanlige statistiske beregninger: median, modus, glidende gjennomsnitt, prosentil og kvartil. Vi vil gjerne takke Colin Banfield, Gerard Brueckl og Javier Guilln, hvis blogger inspirerte noen av de følgende mønstrene. Grunnmønster Eksempel Formlene i dette mønsteret er løsningen på spesifikke statistiske beregninger. Du kan bruke standard DAX-funksjoner til å beregne gjennomsnittet (aritmetisk gjennomsnitt) av et sett med verdier. GJENNOMSNITT . returnerer gjennomsnittet av alle tallene i en numerisk kolonne. AVERAGEA. returnerer gjennomsnittet av alle tallene i en kolonne, og håndterer både tekst og ikke-numeriske verdier (ikke-numeriske og tomme tekstverdier teller som 0). AVERAGEX. beregne gjennomsnittet på et uttrykk evaluert over et bord. Flytende gjennomsnitt Det glidende gjennomsnittet er en beregning for å analysere datapunkter ved å opprette en rekke gjennomsnitt av forskjellige delsett av hele datasettet. Du kan bruke mange DAX teknikker til å gjennomføre denne beregningen. Den enkleste teknikken bruker AVERAGEX, itererer et bord med ønsket granularitet og beregner for hver iterasjon uttrykket som genererer det enkle datapunktet som skal brukes i gjennomsnittet. For eksempel beregner følgende formel det bevegelige gjennomsnittet for de siste 7 dagene, forutsatt at du bruker en datatabell i datamodellen din. Ved å bruke AVERAGEX beregner du automatisk målingene på hvert granularitetsnivå. Når du bruker et mål som kan aggregeres (for eksempel SUM), kan en annen tilgang basert på CALCULATE være raskere. Du finner denne alternative tilnærmingen i det komplette mønsteret av Moving Average. Du kan bruke standard DAX-funksjoner til å beregne variansen av et sett med verdier. VAR. S. returnerer variansen av verdier i en kolonne som representerer en prøvepopulasjon. VAR. P. returnerer variansen av verdier i en kolonne som representerer hele befolkningen. VARX. S. returnerer variansen til et uttrykk evaluert over et tabell som representerer en prøvepopulasjon. VARX. P. returnerer variansen til et uttrykk evaluert over et bord som representerer hele befolkningen. Standardavvik Du kan bruke standard DAX-funksjoner til å beregne standardavviket for et sett med verdier. STDEV. S. returnerer standardavviket til verdier i en kolonne som representerer en prøvepopulasjon. STDEV. P. returnerer standardavviket til verdier i en kolonne som representerer hele befolkningen. STDEVX. S. returnerer standardavviket til et uttrykk evaluert over en tabell som representerer en prøvepopulasjon. STDEVX. P. returnerer standardavviket til et uttrykk evaluert over en tabell som representerer hele befolkningen. Medianen er den numeriske verdien som skiller den øvre halvdelen av en befolkning fra den nedre halvdelen. Hvis det er et merkelig antall rader, er medianen middelverdien (sortering av rader fra laveste verdi til høyeste verdi). Hvis det er et jevnt antall rader, er det gjennomsnittet av de to middelverdiene. Formelen ignorerer blanke verdier, som ikke anses som en del av befolkningen. Resultatet er identisk med MEDIAN-funksjonen i Excel. Figur 1 viser en sammenligning mellom resultatet returnert av Excel og den tilsvarende DAX-formelen for medianberegningen. Figur 1 Eksempel på medianberegning i Excel og DAX. Modusen er verdien som vises oftest i et sett med data. Formelen ignorerer blanke verdier, som ikke anses som en del av befolkningen. Resultatet er identisk med MODE og MODE. SNGL-funksjonene i Excel, som bare returnerer minimumsverdien når det er flere moduser i settet av verdier som vurderes. Excel-funksjonen MODE. MULT ville returnere alle modi, men du kan ikke implementere det som et mål i DAX. Figur 2 sammenligner resultatet som returneres av Excel med den tilsvarende DAX-formelen for modusberegningen. Figur 2 Eksempel på modusberegning i Excel og DAX. Percentil percentilen er verdien under hvilken en gitt prosentandel av verdiene i en gruppe faller. Formelen ignorerer blanke verdier, som ikke anses som en del av befolkningen. Beregningen i DAX krever flere trinn, beskrevet i Fullmønster-delen, som viser hvordan man får de samme resultatene av Excel-funksjonene PERCENTILE, PERCENTILE. INC og PERCENTILE. EXC. Kvartilene er tre poeng som deler et sett med verdier i fire like grupper, hver gruppe består av en fjerdedel av dataene. Du kan beregne kvartilene ved hjelp av Percentilmønsteret ved å følge disse korrespondansene: Første kvartil lavere kvartil 25. prosentandel Andre kvartilmedian 50. prosentil Tredje kvartil øvre kvartil 75. prosentil Fullstendig mønster Noen få statistiske beregninger har en lengre beskrivelse av det komplette mønsteret fordi Du kan ha forskjellige implementeringer avhengig av datamodeller og andre krav. Flytende gjennomsnitt Vanligvis vurderer du det bevegelige gjennomsnittet ved å referere til granularitetsnivået for dagen. Den generelle malen med følgende formel har disse markørene: ltnumberofdaysgt er antall dager for glidende gjennomsnitt. ltdatecolumngt er datakolonnen i datortabellen hvis du har en eller datakolonnen i tabellen som inneholder verdier hvis det ikke finnes en egen datatabell. ltmeasuregt er målet til å beregne som det bevegelige gjennomsnittet. Det enkleste mønsteret bruker AVERAGEX-funksjonen i DAX, som automatisk bare vurderer de dagene som det er en verdi for. Som et alternativ kan du bruke følgende mal i datamodeller uten datatabell og med et mål som kan aggregeres (for eksempel SUM) over hele perioden som vurderes. Den forrige formelen vurderer en dag uten tilsvarende data som et mål som har 0 verdi. Dette kan bare skje når du har et eget datatabell, som kan inneholde dager for hvilke det ikke er noen tilsvarende transaksjoner. Du kan fikse nevneren for gjennomsnittet ved å bruke kun antall dager for hvilke transaksjoner bruker følgende mønster, hvor: ltfacttablegt er tabellen relatert til datatabellen og inneholder verdier beregnet av målingen. Du kan bruke funksjonene DATESBETWEEN eller DATESINPERIOD i stedet for FILTER, men disse fungerer bare i en vanlig datatabell, mens du kan bruke det ovenfor beskrevne mønsteret også til ikke-vanlige datatabeller og til modeller som ikke har datortabell. For eksempel vurdere de forskjellige resultatene som produseres av følgende to tiltak. I figur 3 kan du se at det ikke er salg på 11. september 2005. Denne datoen er imidlertid inkludert i datortabellen, slik at det er 7 dager (fra 11. september til 17. september) som bare har 6 dager med data. Figur 3 Eksempel på en flytende gjennomsnittlig beregning vurderer og ignorerer datoer uten salg. Tiltaket Moving Average 7 Days har et lavere nummer mellom 11. september og 17. september fordi det vurderer 11. september som en dag med 0 salg. Hvis du vil ignorere dager uten salg, må du bruke målet Moving Average 7 Days No Zero. Dette kan være riktig tilnærming når du har en komplett datortabell, men du vil ignorere dager uten transaksjoner. Ved hjelp av den flytende gjennomsnittlige 7-dagers formel, er resultatet riktig, fordi AVERAGEX bare vurderer bare ikke-tomme verdier. Husk at du kan forbedre ytelsen til et bevegelig gjennomsnitt ved å fortsette verdien i en beregnet kolonne i et bord med ønsket granularitet, for eksempel dato eller dato og produkt. Den dynamiske beregningsmetoden med et mål gir imidlertid muligheten til å bruke en parameter for antall dager i glidende gjennomsnitt (for eksempel erstatte ltnumberofdaysgt med et mål som implementerer Parameter Tabellmønsteret). Medianen tilsvarer den 50. prosentpoeng, som du kan beregne ved hjelp av Persentilmønsteret. Medianmønsteret gjør det imidlertid mulig å optimalisere og forenkle medianberegningen ved hjelp av et enkelt mål, i stedet for de flere tiltakene som kreves av Percentil-mønsteret. Du kan bruke denne tilnærmingen når du beregner medianen for verdier som er inkludert i ltvaluecolumngt, som vist nedenfor: For å forbedre ytelsen, vil du kanskje fortsette verdien av et mål i en beregnet kolonne hvis du vil oppnå medianen for resultatene av et mål i datamodellen. Men før du gjør denne optimaliseringen, bør du implementere MedianX-beregningen basert på følgende mal, ved å bruke disse markørene: ltgranularitytablegt er tabellen som definerer beregningens granularitet. For eksempel kan det være datatabellen hvis du vil beregne medianen til et mål beregnet på dagnivå, eller det kan være VALUES (8216DateYearMonth) hvis du vil beregne medianen til et mål beregnet på månedenivå. ltmeasuregt er målet for å beregne for hver rad av ltgranularitytablegt for medianberegningen. ltmeasuretablegt er tabellen som inneholder data som brukes av ltmeasuregt. For eksempel, hvis ltgranularitytablegt er en dimensjon som 8216Date8217, vil ltmeasuretablegt være 8216Internet Sales8217 som inneholder kolonnen for Internett-salgsmengde summet av Internett Total Sales-måleen. For eksempel kan du skrive medianen av Internett Total Salg for alle Kunder i Eventyrverker som følger: Tips Følgende mønster: brukes til å fjerne rader fra ltgranularitytablegt som ikke har tilsvarende data i gjeldende utvalg. Det er en raskere måte enn å bruke følgende uttrykk: Du kan imidlertid erstatte hele CALCULATETABLE uttrykket med bare ltgranularitytablegt hvis du vil vurdere tomme verdier av ltmeasuregt som 0. Utførelsen av MedianX-formel avhenger av antall rader i tabell iterated og på kompleksiteten av tiltaket. Hvis ytelsen er dårlig, kan du fortsette med det lette resultatet i en beregnet kolonne av lttablegt, men dette vil fjerne muligheten til å bruke filtre til medianberegningen på spørringstidspunktet. Percentile Excel har to forskjellige implementeringer av percentilberegning med tre funksjoner: PERCENTILE, PERCENTILE. INC og PERCENTILE. EXC. De returnerer alle K-th-prosentverdien, hvor K er i området 0 til 1. Forskjellen er at PERCENTILE og PERCENTILE. INC anser K som et inkluderende utvalg, mens PERCENTILE. EXC anser K-området 0 til 1 som eksklusiv . Alle disse funksjonene og deres DAX-implementeringer mottar en prosentilverdi som parameter, som vi kaller K. ltKgt-prosentilverdien ligger i området 0 til 1. De to DAX-implementeringene av percentil krever noen få tiltak som er liknende, men forskjellige nok til å kreve to forskjellige sett med formler. Tiltakene som er definert i hvert mønster er: KPerc. Den prosentile verdien det tilsvarer ltKgt. PercPos. Punktilstandens posisjon i det sorterte sett av verdier. ValueLow. Verdien under prosentilstanden. ValueHigh. Verdien over prosentilposisjonen. Percentil. Den endelige beregningen av percentilen. Du trenger ValueLow og ValueHigh-målene hvis PercPos inneholder en desimaldel, fordi du må interpolere mellom ValueLow og ValueHigh for å returnere riktig prosentilverdi. Figur 4 viser et eksempel på beregningene som ble gjort med Excel - og DAX-formler, ved hjelp av begge algoritmer av prosentil (inklusiv og eksklusiv). Figur 4 Percentile beregninger ved hjelp av Excel-formler og tilsvarende DAX-beregning. I de følgende avsnittene utfører Percentile-formlene beregningen på verdier lagret i en tabellkolonne, DataValue, mens PercentileX-formlene utfører beregningen på verdier returnert av et mål beregnet ved en gitt granularitet. Percentil Inklusiv Percentil Inklusiv implementering er følgende. Percentil Eksklusiv Percentil Eksklusiv implementering er følgende. PercentileX Inklusive PercentileX Inklusiv implementering er basert på følgende mal, ved hjelp av disse markørene: ltgranularitytablegt er tabellen som definerer beregningens granularitet. For eksempel kan det være datatabellen hvis du vil beregne percentilen til et mål på dagens nivå, eller det kan være VALUES (8216DateYearMonth) hvis du vil beregne percentilen av et mål på månedenivået. ltmeasuregt er målet for å beregne for hver rad av ltgranularitytablegt for percentilberegning. ltmeasuretablegt er tabellen som inneholder data som brukes av ltmeasuregt. For eksempel, hvis ltgranularitytablegt er en dimensjon som 8216Date, 8217, så vil ltmeasuretablegt være 8216Sales8217 som inneholder summekolonnen summert av totalbeløpsmålet. For eksempel kan du skrive PercentileXInc av Total Salg for alle datoene i datatabellen som følger: PercentileX Exclusive PercentileX Exclusive implementeringen er basert på følgende mal, med samme markører som brukes i PercentileX Inclusive: For eksempel, du kan skrive PercentileXExc av Total Salg for alle datoene i datatabellen som følger: Hold meg informert om kommende mønstre (nyhetsbrev). Fjern merket for å laste ned filen fritt. Publisert 17. mars 2014 av
No comments:
Post a Comment