Flyttande medelvärde I det här exemplet lär du dig hur du beräknar glidande medelvärdet för en tidsserie i Excel. Ett glidande medel används för att jämna ut oegentligheter (toppar och dalar) för att enkelt kunna känna igen trender. 1. Låt oss först titta på våra tidsserier. 2. Klicka på Dataanalys på fliken Data. Obs! Kan inte hitta knappen Data Analysis Klicka här för att ladda verktyget Analysis ToolPak. 3. Välj Flytta genomsnitt och klicka på OK. 4. Klicka i rutan Inmatningsområde och välj intervallet B2: M2. 5. Klicka i rutan Intervall och skriv 6. 6. Klicka i rutan Utmatningsområde och välj cell B3. 8. Skriv ett diagram över dessa värden. Förklaring: Eftersom vi ställer intervallet till 6 är det rörliga genomsnittet genomsnittet för de föregående 5 datapunkterna och den aktuella datapunkten. Som ett resultat utjämnas toppar och dalar. Diagrammet visar en ökande trend. Excel kan inte beräkna det rörliga genomsnittet för de första 5 datapunkterna, eftersom det inte finns tillräckligt med tidigare datapunkter. 9. Upprepa steg 2 till 8 för intervall 2 och intervall 4. Slutsats: Ju större intervall desto mer toppar och dalar släpper ut. Ju mindre intervallet desto närmare rörliga medelvärden är till de faktiska datapunkterna. Stegnavigering Beräkning av ett rörligt medelvärde i PowerPivot För två veckor sedan lovade jag att prata om hur man genererar ett glidande medelvärde i PowerPivot, men då förra veckan blev jag sidospårad av berättar om ett coolt sätt att visa YouTube-videor på dina SharePoint-sidor med hjälp av en webbdel som finns på CodePlex som några av mina arbetsgruppsledamöter hittat. Det var så lätt att implementera, jag var tvungen att dela den med er alla. Men tillbaka till ämnet för att beräkna ett glidande medelvärde kan den första frågan vara vad som är ett glidande medelvärde och varför skulle du vilja använda en. Ett rörligt medelvärde är helt enkelt summan av två eller flera tidsberoende värden, där summan divideras med antalet värden som används. Om jag till exempel pratade om aktiekurser kanske jag vill använda något som ett 7-dagars glidande medelvärde för att dämpa effekten av enskilda dagspikar eller droppar i aktiekursen som inte indikerar den totala aktiestrenden. (Några långsiktiga investerare använder ännu längre period glidande medelvärden.) Det betyder inte att om ett lager plummets eller svimmar att jag skulle luta mig tillbaka tills det glidande genomsnittet säger att jag ska agera. Någon bra aktie investerare kommer att berätta att det finns många andra faktorer både internt och externt för ett företag som kan tvinga din hand att sälja eller köpa någon viss aktie. Men poängen är, och det här är svaret på den andra frågan, ett rörligt medel dämpar slumpen så att jag lättare kan se det övergripande mönstret av de nummer som jag spårar. Ok, så antar jag att jag jobbar för Contoso och ville veta om försäljningen stiger, faller eller vanligtvis platt. Om jag tittar på den dagliga försäljningen, kommer siffrorna sannolikt att fluktuera upp och ner i inget särskilt mönster som hindrar mig från att upptäcka en övergripande trend. Följande bild visar Contosos dagliga Contoso-försäljning under en 3 månadersperiod under sommaren 2008. Jag valde att visa data som ett diagram för att visa hur försäljningen fluktuerar om dagen och avslöjar information som jag kanske inte kan se så lätt hade jag skapade en tabell med samma värden. Självklart kunde jag kartlägga ett helt år eller mer, men för att se enskilda dagar skulle jag behöva bredda tabellen väsentligt. Men även med denna mindre tidsperiod kan jag se att försäljningen fluktuerar ganska snällt. Men jag kanske frågar är att försäljningen ökar, minskar eller blir densamma. Om jag har ett bra öga kanske jag säger att försäljningen spetsar mot slutet av juli och sedan faller lite tillbaka när diagrammet flyttas till augusti. Men det är inte så uppenbart som det faktum att det finns en hel del dagliga fluktuationer. Så hur kan jag visuellt visa trender med Moving Average Sales. Nu för syftet med denna illustration, Im ska skapa ett fyra dagars glidande medelvärde, men ärligt talat finns det inget rätt antal perioder i ett glidande medelvärde. Faktum är att jag ska experimentera med olika tidsperioder för att se vilken tidsperiod som gör att jag inte bara kan upptäcka övergripande trender, men även i det här fallet där jag visar butiksförsäljning vid säsongsmässiga förändringar. Jag vet redan att om jag visar data om dagen kan jag använda följande formel för att beräkna den dagliga försäljningen på bara vår butikskanal. (Ja, jag kunde helt enkelt använda SalesAmount och tillämpa en kanalskärare för att bara använda butiksförsäljning, men klibbar med exemplet.) Jag kan sedan använda denna beräknade åtgärd för att beräkna försäljningen för tidigare dagar för vilken dag som helst genom att skapa följande åtgärd. StoreSales1DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -1, dag)) Du kanske kan gissa att formeln för beräkning av försäljningen för två dagar sedan respektive tre dagar sedan är: StoreSales2DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, - 2, dag)) StoreSales3DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -3, dag)) Med dessa fyra värden som beräknas för varje dag kan jag beräkna summan av dessa värden och dela med 4 för att få ett 4 dagars glidande medelvärde med Följande beräknade värde: FourDayAverage: (StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo) 4.0 Nu om jag byter tillbaka till min kartsida, ska jag se att Excel uppdaterar fältlistan för att inkludera de nya beräknade åtgärderna. Om jag sedan lägger till fältet FourDayAverage i rutan Värden skapar en andra serie i diagrammet, har jag nu både den faktiska dagliga försäljningen och det fyra dagars glidande genomsnittet som visas i samma diagram. Det enda problemet är att jag också vill ändra diagramformatet för att visa den dagliga försäljningen (min första dataserie) som kolumner och mitt glidande medelvärde (min andra dataserie) som en linje. När jag högerklickar på diagrammet och väljer Ändra diagramtyp, kan jag välja Combo som diagramtyp som visas i följande bild. I det här fallet är kolumnlinjediagrammet precis vad jag vill ha. Eftersom jag lagt till den rörliga genomsnittsserien till värdena sist, blir det som regel linjen och alla andra dataserier visas som grupperade kolumner. Eftersom jag bara har ett värde för varje dag, visar diagrammet en enskild kolumn per dag. Om jag hade skrivit in min dataserie i Values-området i fel ordning kunde jag bara använda den här dialogrutan för att välja diagramtyp för varje serie. När jag klickar på OK i den här dialogrutan ser mitt diagram nu ut som följande tydligare visar mer av den övergripande trenden och mindre dagliga fluktuationer. Men vänta, finns det ett enklare sätt att göra detta varför ja det finns. Men för att lära sig att göra det, måste du vänta tills nästa vecka. Postnavigering Mina arkiv Epost Abonnemang Ämnen Jag pratar om att tolka 12 månader i DAX Computing. Det rullande 12-månadersmedlet i DAX ser ut som en enkel uppgift, men det döljer viss komplexitet. I den här artikeln beskrivs hur man skriver den bästa formeln för att undvika vanliga fallgropar med hjälp av tidsinlysningsfunktioner. Vi börjar med den vanliga AdventureWorks datamodellen, med produkter, försäljning och kalender. Kalenderen har markerats som en kalendertabell (det är nödvändigt att arbeta med vilken som helst intelligensfunktion) och vi byggde en enkel hierarkisk årmånadsdatum. Med denna uppställning är det väldigt enkelt att skapa en första PivotTable som visar försäljning över tid: När man gör trendanalys, om försäljningen är utsatt för säsongsmässighet eller, mer generellt, om du vill ta bort effekten av toppar och droppar i försäljningen, vanlig teknik är att beräkna värdet under en viss period, vanligtvis 12 månader och genomsnittlig det. Det rullande genomsnittet över 12 månader ger en jämn indikator på trenden och det är mycket användbart i diagram. Med tanke på ett datum kan vi beräkna det 12-månaders rullande genomsnittet med denna formel, som fortfarande har några problem som vi kommer att lösa senare: Formulans beteende är enkelt: det beräknar värdet av Försäljningen efter att ha skapat ett filter i kalendern som visar exakt ett helt år med data. Kärnan i formeln är DATESBETWEEN, som returnerar en inkluderande uppsättning datum mellan de två gränserna. Den lägsta är: Läsning från det innersta: Om vi visar data i en månad, säg juli 2007 tar vi det sista synliga datumet med LASTDATE, som returnerar den sista dagen i juli 2007. Sedan använder vi nästa dag för att ta 1: a av augusti 2007 och vi använder slutligen SAMEPERIODLASTYEAR för att flytta den tillbaka ett år, vilket ger 1 augusti 2006. Övre gränsen är helt enkelt LASTDATE, dvs slutet av juli 2007. Om vi använder denna formel i en PivotTable ser resultatet bra ut, men vi har ett problem för det sista datumet: Som du kan se i figuren är värdet korrekt beräknat till 2008. Då finns det inget värde under 2009 (vilket är korrekt, vi har inte försäljning under 2009) men det finns ett överraskande värde i december 2010, där vår formel visar totalvärdet istället för ett tomt värde, som vi förväntar oss. Faktum är att LASTDATE i december returnerar sista dagen på året och nästa dag ska returnera den 1 januari 2011. Men NEXTDAY är en tidsinlysningsfunktion och det förväntas returnera uppsättningar av befintliga datum. Detta faktum är inte mycket uppenbart och det är värt ett par ord mer. Tidsintelligensfunktioner utför inte matte på datum. Om du vill ta dagen efter ett visst datum kan du helt enkelt lägga till 1 till en datumkolumn och resultatet blir nästa dag. Istället fungerar tidsintelligens skiftuppsättningar med datum fram och tillbaka över tiden. Således tar NEXTDAY sin inmatning (i vårt fall ett enda radbord med 31 december 2010) och ändras det en dag senare. Problemet är att resultatet ska vara 1 januari 2011 men eftersom kalendertabellen inte innehåller det datumet är resultatet BLANK. Vårt uttryck beräknar sålunda Försäljningen med en tom nedre gräns, vilket innebär början av tiden, vilket resulterar i försäljningsomgångens totala summa. För att rätta till formeln räcker det med att ändra utvärderingsordern för den nedre gränsen: Som du kan se, kallas nu NÄSTA DAG efter ett års tillbaka skift. På så sätt tar vi 31 december 2010, flyttar den till 31 december 2009 och tar nästa dag, den 1 januari 2010: ett befintligt datum i kalendern. Resultatet är nu den förväntade: Vid denna tidpunkt behöver vi bara dela upp det numret med 12 för att få det rullande genomsnittet. Men, som du lätt kan föreställa dig, kan vi inte alltid dela upp den med 12. Faktum är att i början av perioden är det inte 12 månader att samla men ett lägre antal. Vi måste beräkna antalet månader för vilka det finns försäljning. Detta kan åstadkommas genom att använda korsfiltrering av kalenderbordet med försäljningsbordet efter att vi tillämpat det nya 12-månaders kontextet. Vi definierar en ny åtgärd som beräknar antalet befintliga månader under 12 månader: Du kan se i nästa bild att Months12M-mätningen beräknar ett korrekt värde: Det är värt att notera att formeln inte fungerar om du väljer en period längre än 12 månader, eftersom CalendarMonthName har bara 12 värden. Om du behöver längre perioder måste du använda en YYYYMM-kolumn för att kunna räkna mer än 12. Den intressanta delen av denna formel som använder kryssfiltrering är det faktum att det beräknar antalet tillgängliga månader, även när du filtrerar med andra attribut. Om du till exempel väljer den blå färgen med en snittare börjar försäljningen i juli 2007 (inte 2005, vilket händer för många andra färger). Med hjälp av kryssfiltret vid försäljning beräknar formeln korrekt att i juli 2007 finns en enda månad tillgänglig försäljning för Blå: Vid den här tiden är det rullande genomsnittet bara en DIVIDE away: När vi använder den i ett pivottabell, stannar vi fortfarande har ett litet problem: faktum beräknas värdet även i månader för vilka det inte finns några försäljningar (dvs. kommande månader): Detta kan lösas med ett IF-uttalande för att förhindra att formeln visar värden när det inte finns någon försäljning. Jag har inget emot IF men för prestanda-beroende av dig är det alltid värt att komma ihåg att IF kan vara en prestationsdödare, eftersom det skulle kunna tvinga DAX-formelmotorn att sparka in. I detta specifika fall är skillnaden försumbar, men som en allmän regel är det bästa sättet att ta bort värdet när det inte finns några försäljningar att förlita sig på rena lagringsmotorformler som den här: Jämförelse av ett diagram med hjälp av Avg12M med en annan som visar Försäljning kan enkelt uppskatta hur det rullande genomsnittet skisserar trender på ett mycket renare sätt: Håll mig informerad om kommande artiklar (nyhetsbrev). Avmarkera för att ladda ner filen fritt.
Comments
Post a Comment