Przenoszenie średnia excel powerpivot

Średnia ruchoma Ten przykład pokazuje, jak obliczyć średnią ruchomą szeregu czasowego w Excelu. Średnia ruchoma służy do łagodzenia nieprawidłowości (szczytów i dolin) w celu łatwego rozpoznawania trendów. 1. Najpierw przyjrzyjmy się naszej serii czasowej. 2. Na karcie Dane kliknij Analiza danych. Uwaga: nie można znaleźć przycisku Analiza danych Kliknij tutaj, aby załadować dodatek Analysis ToolPak. 3. Wybierz średnią ruchomą i kliknij OK. 4. Kliknij pole Input Range i wybierz zakres B2: M2. 5. Kliknij w polu Interwał i wpisz 6. 6. Kliknij pole Zakres wyjściowy i wybierz komórkę B3. 8. Narysuj wykres tych wartości. Objaśnienie: ponieważ ustawiliśmy przedział na 6, średnia ruchoma jest średnią z poprzednich 5 punktów danych i bieżącego punktu danych. W rezultacie szczyty i doliny są wygładzone. Wykres pokazuje rosnący trend. Program Excel nie może obliczyć średniej ruchomej dla pierwszych 5 punktów danych, ponieważ nie ma wystarczającej liczby poprzednich punktów danych. 9. Powtórz kroki od 2 do 8 dla przedziału 2 i odstępu 4. Wniosek: Im większy przedział, tym bardziej wygładzone są szczyty i doliny. Im mniejszy przedział czasu, tym bardziej zbliżone są średnie ruchome do rzeczywistych punktów danych. Nawigacja nawigacyjna Obliczanie średniej ruchomej w PowerPivocie Dwa tygodnie temu obiecałem mówić o tym, jak wygenerować średnią ruchomą w PowerPivot, ale w zeszłym tygodniu zostałem zsynchronizowany przez informujące o świetnym sposobie wyświetlania filmów z YouTube na stronach SharePoint za pomocą części internetowej znalezionej w CodePlex, którą znaleźli niektórzy członkowie mojego zespołu roboczego. To było łatwe do wdrożenia, po prostu musiałem się z wami podzielić. Powracając jednak do tematu obliczania średniej kroczącej, pierwszym pytaniem może być średnia krocząca, a następnie, dlaczego miałbyś ją użyć. Średnia ruchoma to po prostu suma dwóch lub więcej wartości zależnych od czasu, w których suma jest następnie dzielona przez liczbę użytych wartości. Na przykład, jeśli mówię o cenach akcji, mogę chcieć użyć czegoś w rodzaju 7-dniowej średniej kroczącej, aby zneutralizować efekt indywidualnych skoków lub spadków cen akcji, które nie są wskaźnikiem ogólnego trendu giełdowego. (Niektórzy inwestorzy długoterminowi używają nawet dłuższych średnich kroczących.) Nie oznacza to, że jeśli akcja spadnie lub wzrośnie, to usiądę, dopóki średnia ruchoma nie zmusi mnie do działania. Każdy dobry inwestor giełdowy powie, że istnieje wiele innych czynników, zarówno wewnętrznych, jak i zewnętrznych, w stosunku do firmy, która mogłaby zmusić twoją rękę do sprzedaży lub zakupu określonego towaru. Ale chodzi o to, i jest to odpowiedź na drugie pytanie, średnia ruchoma tłumi przypadkowość, dzięki czemu łatwiej widzę ogólny wzór liczb, które śledzę. Ok, więc przypuśćmy, że pracuję dla Contoso i chciałbym wiedzieć, czy sprzedaż rośnie, spada, czy generalnie płaska. Jeśli spojrzę na dzienną sprzedaż, liczba ta może wahać się w górę iw dół bez szczególnego wzorca, co uniemożliwia mi dostrzeżenie ogólnego trendu. Poniższy rysunek pokazuje codzienną sprzedaż Contoso przez okres 3 miesięcy w lecie 2008 roku. Wybrałem wyświetlanie danych jako wykres, aby pokazać, jak sprzedaż zmienia się w ciągu dnia, ujawniając informacje, których nie byłbym w stanie zobaczyć tak łatwo, gdyby stworzył tabelę o tych samych wartościach. Oczywiście mógłbym sporządzić wykres przez cały rok lub więcej, ale aby zobaczyć poszczególne dni, musiałbym znacznie poszerzyć wykres. Jednak nawet w tym mniejszym okresie czasu widzę, że sprzedaż dość się zmienia. Ale mogę zapytać, czy sprzedaż rośnie, maleje, czy pozostaje taka sama. Jeśli mam dobry wzrok, mogę powiedzieć, że szczyt sprzedaży nastąpi pod koniec lipca, a następnie nieco spadnie wraz z przesunięciem wykresu do sierpnia. Ale nie jest to tak oczywiste, jak fakt, że występuje duża dzienna fluktuacja. Jak więc wizualnie wyświetlić trendy z ruchomymi przeciętnymi sprzedażami. Teraz na potrzeby tej ilustracji zamierzam stworzyć czterodniową średnią ruchową, ale szczerze mówiąc, nie ma jednej właściwej liczby okresów w średniej ruchomej. Właściwie powinienem eksperymentować z różnymi okresami czasu, aby sprawdzić, który okres pozwala mi dostrzec nie tylko ogólne trendy, ale także w tym przypadku, w którym wyświetlam sprzedaż w sklepie, w sezonowych zmianach. Już wiem, że jeśli wyświetlam dane według dnia, mogę użyć następującej formuły do ​​obliczenia dziennej sprzedaży tylko naszego kanału sklepu. (Tak, mógłbym po prostu użyć SalesAmount i zastosować selektor kanałów, aby używać tylko sprzedaży w sklepie, ale pozostańmy przy przykładzie.) Następnie mogę użyć tej wyliczonej miary, aby obliczyć sprzedaż z poprzedniego dnia dla dowolnego dnia, tworząc następującą miarę. StoreSales1DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -1, day)) Możesz być w stanie odgadnąć, że formuła obliczania sprzedaży sprzed dwóch dni i trzech dni odpowiednio to: StoreSales2DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, - 2, dzień)) StoreSales3DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -3, day)) Przy tych czterech wartościach obliczanych dla każdego dnia, mogę obliczyć sumę tych wartości i podzielić przez 4, aby uzyskać 4-dniową średnią ruchomą przy użyciu następująca wyliczona wartość: FourDayAverage: (StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo) 4.0 Teraz, gdy przełączę się z powrotem na moją stronę wykresu, powinienem zobaczyć, że Excel aktualizuje listę pól, aby uwzględnić nowe obliczone miary. Jeśli następnie dodaję pole FourDayAverage do pola Wartości, tworząc drugą serię na wykresie, teraz na tej samej wykresie wyświetlana jest zarówno faktyczna dzienna sprzedaż, jak i czterodniowa średnia ruchoma. Jedynym problemem jest to, że chciałbym zmienić format wykresu tak, aby wyświetlał dzienną sprzedaż (moją pierwszą serię danych) jako kolumny i moją średnią ruchomą (moją drugą serię danych) jako linię. Kiedy kliknę prawym przyciskiem myszy na wykresie i wybiorę Zmień typ wykresu, mogę wybrać Combo jako typ wykresu, jak pokazano na poniższym rysunku. W takim przypadku wykres kolumn w kolumnie klastrowej jest dokładnie tym, czego chcę. Ponieważ ostatnio dodałem serię ruchomych średnich do obszaru Wartości, domyślnie linia jest Line i wszystkie inne serie danych pojawiają się jako kolumny w klastrze. Ponieważ mam tylko jedną wartość na każdy dzień, wykres pokazuje indywidualną kolumnę dziennie. Gdybym wprowadził serie danych do obszaru Wartości w niewłaściwej kolejności, mógłbym po prostu użyć tego okna dialogowego, aby wybrać typ wykresu dla każdej serii. Kiedy klikam OK w tym oknie dialogowym, mój wykres wygląda teraz tak, jak poniżej, który bardziej wyraźnie pokazuje więcej ogólnego trendu i mniej codziennych fluktuacji. Ale czekaj, czy istnieje łatwiejszy sposób na zrobienie tego Dlaczego tak jest. Ale aby nauczyć się, jak to zrobić, musisz poczekać do przyszłego tygodnia. Post navigation Moje archiwum Tematy subskrypcji wiadomości e-mail Mówię o odświeżaniu 12-miesięcznej średniej w języku DAX Obliczanie toczącej się średniej 12-miesięcznej w języku DAX wygląda na proste zadanie, ale kryje w sobie pewną złożoność. W tym artykule wyjaśniono, jak napisać najlepszą formułę, omijając typowe pułapki za pomocą funkcji analizy czasu. Zaczynamy od zwykłego modelu danych AdventureWorks z tabelą produktów, sprzedaży i kalendarza. Kalendarz został oznaczony jako tabela kalendarza (konieczna jest praca z dowolną funkcją analizy czasu) i zbudowaliśmy prostą hierarchię rok-miesiąc-data. Dzięki tej konfiguracji bardzo łatwo jest utworzyć pierwszą tabelę przestawną pokazującą sprzedaż w czasie: podczas analizy trendów, jeśli sprzedaż podlega sezonowości lub, bardziej ogólnie, jeśli chcesz usunąć efekt szczytów i spadków sprzedaży, powszechną techniką jest obliczanie wartości w danym okresie, zwykle 12 miesięcy i jej średnia. Średnia krocząca z okresu 12 miesięcy zapewnia sprawny wskaźnik tendencji i jest bardzo przydatna na wykresach. Biorąc pod uwagę datę, możemy obliczyć 12-miesięczną średnią kroczącą z tą formułą, która nadal ma pewne problemy, które rozwiążemy później: zachowanie formuły jest proste: oblicza wartość Sales po utworzeniu filtru w kalendarzu, pokazuje dokładnie jeden pełny rok danych. Rdzeniem tej formuły jest DATESBETWEEN, która zwraca kompletny zbiór dat między dwiema granicami. Niższy z nich to: Czytanie go od środka: jeśli wyświetlamy dane przez miesiąc, powiedzmy, lipiec 2007, to bierzemy ostatnią widoczną datę za pomocą LASTDATE, która zwraca ostatni dzień w lipcu 2007. Następnie używamy NEXTDAY, aby zająć 1 miejsce. sierpnia 2007 r. i ostatecznie używamy SAMEPERIODLASTYEAR, aby przesunąć go z powrotem o jeden rok, otrzymując datę 1 sierpnia 2006 r. Górna granica jest po prostu OSTATECZNA, tj. do końca lipca 2007 r. Jeśli użyjemy tej formuły w tabeli przestawnej, wynik wygląda dobrze, ale mają problem z ostatnią datą: w rzeczywistości, jak widać na rysunku, wartość jest prawidłowo obliczana do 2008 r. Wtedy nie ma żadnej wartości w 2009 r. (co jest poprawne, nie mamy sprzedaży w 2009 r.), ale istnieje zaskakująca wartość w grudniu 2017 r., gdzie nasza formuła pokazuje sumę całkowitą zamiast wartości pustej, jak można się było spodziewać. W rzeczywistości, w grudniu, LASTDATE zwraca ostatni dzień roku, a NEXTDAY powinien zwrócić 1 stycznia 2017. Ale NEXTDAY jest funkcją wywiadu czasowego i oczekuje się, że zwróci zestawy istniejących dat. Ten fakt nie jest bardzo oczywisty i warto kilka słów więcej. Funkcje wywiadu czasowego nie wykonują matematyki na datach. Jeśli chcesz wziąć dzień po danej dacie, możesz po prostu dodać 1 do dowolnej kolumny daty, a wynik będzie następnego dnia. Zamiast tego funkcje inteligentnej analizy czasu przesuwają zestaw daty i wstecz w czasie. Tak więc NEXTDAY przyjmuje swój wkład (w naszym przypadku tabelę jednorzędową z 31 grudnia 2017 r.) I przesuwa ją dzień później. Problem polega na tym, że wynikiem powinien być 1 stycznia 2017 r., Ale ponieważ tabela kalendarza nie zawiera tej daty, wynikiem jest BLANK. W ten sposób nasze wyrażenie wylicza wartość Sprzedaży z pustą dolną granicą, co oznacza początek czasu, co daje w efekcie wielką sumę sprzedaży. Aby poprawić formułę wystarczy zmienić kolejność oceny dolnej granicy: Jak widać, teraz NEXTDAY jest wywoływana po przesunięciu rocznego z powrotem. W ten sposób bierzemy 31 grudnia 2017 r., Przenosimy go do 31 grudnia 2009 r. I przyjmujemy następnego dnia, czyli 1 stycznia 2017 r .: istniejącą datę w tabeli kalendarza. Wynik jest teraz oczekiwany: w tym momencie wystarczy podzielić tę liczbę przez 12, aby uzyskać średnią kroczącą. Ale, jak łatwo sobie wyobrazić, nie zawsze możemy go podzielić przez 12. W rzeczywistości na początku okresu nie ma 12 miesięcy do zsumowania, ale mniejszej liczby. Musimy obliczyć liczbę miesięcy, dla których istnieje sprzedaż. Można to osiągnąć za pomocą filtrowania krzyżowego tabeli kalendarza z tabelą sprzedaży po zastosowaniu nowego 12-miesięcznego kontekstu. Definiujemy nową miarę, która oblicza liczbę istniejących miesięcy w okresie 12 miesięcy: Na następnej ilustracji widać, że miara Months12M oblicza poprawną wartość: Warto zauważyć, że formuła nie działa, jeśli wybierzesz okres dłużej niż 12 miesięcy, ponieważ parametr CalendarMonthName ma tylko 12 wartości. Jeśli potrzebujesz dłuższych okresów, musisz użyć kolumny RRRRMM, aby móc policzyć więcej niż 12. Ciekawą częścią tej formuły, która korzysta z filtrowania krzyżowego, jest fakt, że oblicza liczbę dostępnych miesięcy nawet podczas filtrowania za pomocą innych atrybuty. Jeśli na przykład wybierzesz kolor niebieski za pomocą krajarki, sprzedaż rozpocznie się w lipcu 2007 r. (Nie w 2005 r., Jak to się dzieje w przypadku wielu innych kolorów). Korzystając z filtra krzyżowego w obszarze Sprzedaż, formuła prawidłowo obliczyła, że ​​w lipcu 2007 roku dostępny jest jeden miesiąc dostępnych sprzedaży dla produktu Blue: W tym momencie średnia krocząca jest tylko ODDYCHAJ: gdy używamy go w tabeli przestawnej, nadal mały problem: w rzeczywistości wartość jest obliczana również dla miesięcy, dla których nie ma sprzedaży (tj. przyszłe miesiące): Można to rozwiązać za pomocą instrukcji JEŻELI, aby zapobiec wyświetlaniu wartości formuły w przypadku braku sprzedaży. Nie mam nic przeciwko IF, ale dla uzależnionych od wyników wśród was, zawsze warto pamiętać, że IF może być zabójcą wydajności, ponieważ może wymusić uruchomienie silnika formuły DAX. W tym konkretnym przypadku różnica jest znikoma, ale , co do zasady, najlepszym sposobem na usunięcie wartości, gdy nie ma sprzedaży, jest korzystanie z czystych formuł silnika do przechowywania danych, takich jak ta: Porównywanie wykresu za pomocą Avg12M z innym, który pokazuje Sprzedaż, można łatwo ocenić, jak średnia krocząca przedstawia trendy w znacznie bardziej przejrzysty sposób: informuj mnie o nadchodzących artykułach (biuletyn). Usuń zaznaczenie, aby swobodnie pobrać plik.