Centered 12 miesięczny ruchomy średni excel

Średnia ruchoma Ten przykład pokazuje, w jaki sposób 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. Podczas obliczania ruchomej średniej ruchomej, ustawienie średniej w okresie środkowym ma sens W poprzednim przykładzie obliczyliśmy średnią z pierwszych 3 przedziałów czasowych i umieściliśmy to obok okresu 3. Moglibyśmy umieścić średnią w środku przedziału czasowego trzech okresów, to znaczy obok okresu 2. Działa to dobrze w okresach nieparzystych, ale nie tak dobre dla nawet okresów czasu. Więc gdzie ustawilibyśmy pierwszą średnią ruchomą, gdy M 4 Technicznie, średnia ruchoma spadłaby o t 2,5, 3,5. Aby uniknąć tego problemu, wygładzamy MA za pomocą M 2. W ten sposób wygładzamy wygładzone wartości. Jeśli uśredniamy parzystą liczbę terminów, musimy wygładzić wygładzone wartości. Poniższa tabela pokazuje wyniki przy użyciu M 4.Wyciąg w arkuszu kalkulacyjnym z korektą sezonową i wygładzanie wykładnicze Proste jest wykonywanie korekt sezonowych i dopasowywanie modeli wygładzania wykładniczego za pomocą Excela. Poniższe obrazy ekranów i wykresy są pobierane z arkusza kalkulacyjnego, który został skonfigurowany w celu zilustrowania mnożnikowej korekty sezonowej i liniowego wygładzania wykładniczego w kolejnych kwartalnych danych sprzedaży z programu Outboard Marine: Aby uzyskać kopię samego pliku arkusza kalkulacyjnego, kliknij tutaj. Wersja liniowego wygładzania wykładniczego, która będzie tu używana do celów demonstracyjnych, jest wersją Brown8217s, tylko dlatego, że może być zaimplementowana za pomocą pojedynczej kolumny formuł i istnieje tylko jedna stała wygładzająca do optymalizacji. Zwykle lepiej jest użyć wersji Holt8217s, która ma oddzielne stałe wygładzania dla poziomu i trendu. Proces prognozowania przebiega w następujący sposób: (i) najpierw dane są dostosowane sezonowo (ii) następnie generowane są prognozy dla danych dostosowanych sezonowo za pomocą liniowego wygładzania wykładniczego oraz (iii) ostatecznie sezonowo dostosowane prognozy są cytowane za pomocąsezonów, aby uzyskać prognozy dla pierwotnej serii . Proces regulacji sezonowej jest przeprowadzany w kolumnach od D do G. Pierwszym krokiem w regulacji sezonowej jest obliczenie środkowej średniej ruchomej (wykonanej tutaj w kolumnie D). Można to zrobić, biorąc średnią z dwóch średnich rocznych, które są przesunięte o jeden okres względem siebie. (W celu centrowania potrzebna jest kombinacja dwóch średnich zrównowaŜonych zamiast jednej średniej). Kolejnym krokiem jest obliczenie stosunku do średniej ruchomej - it. oryginalne dane podzielone przez średnią ruchomą w każdym okresie - co zostało wykonane tutaj w kolumnie E. (Jest to również nazywane elementem quottrend-cyclequot wzoru, o ile trend i efekty cyklu koniunkturalnego mogą być uważane za wszystko, pozostaje po uśrednieniu danych o wartości całorocznej, oczywiście, zmiany z miesiąca na miesiąc, które nie wynikają z sezonowości, można określić za pomocą wielu innych czynników, ale średnia z 12 miesięcy wygładza je w dużym stopniu.) szacunkowy wskaźnik sezonowy dla każdego sezonu jest obliczany przez pierwsze uśrednienie wszystkich wskaźników dla danego sezonu, co jest wykonywane w komórkach G3-G6 przy użyciu formuły AVERAGEIF. Średnie wskaźniki są następnie przeskalowane, tak aby sumowały się dokładnie dokładnie 100 razy w stosunku do liczby okresów w sezonie, lub 400 w tym przypadku, co jest wykonywane w komórkach H3-H6. Poniżej w kolumnie F formuły VLOOKUP służą do wstawiania odpowiedniej wartości indeksu sezonowego w każdym wierszu tabeli danych, zgodnie z kwartałem roku, który reprezentuje. Wyśrodkowana średnia ruchoma i dane dostosowane sezonowo kończą się w następujący sposób: Zwróć uwagę, że średnia ruchoma wygląda zazwyczaj na bardziej płynną wersję wyrównanej sezonowo serii, a na obu końcach jest krótsza. Inny arkusz roboczy w tym samym pliku Excel pokazuje zastosowanie liniowego modelu wygładzania wykładniczego do danych dostosowywanych sezonowo, zaczynając od kolumny G. Wartość stałej wygładzania (alfa) jest wprowadzana powyżej kolumny prognozy (tutaj, w komórce H9) i dla wygody jest on przypisany do nazwy zakresu quotAlpha. quot (Nazwa jest przypisana za pomocą polecenia quotInsertNameCreatequot.) Model LES jest inicjalizowany przez ustawienie pierwszych dwóch prognoz równych pierwszej rzeczywistej wartości odseparowanej sezonowo serii. Formuła użyta tutaj dla prognozy LES to jednokwatowa postać rekurencyjna modelu Brown8217s: Ta formuła jest wprowadzana do komórki odpowiadającej trzeciemu okresowi (tutaj, komórka H15) i kopiowana z tego miejsca. Należy zauważyć, że prognoza LES dla bieżącego okresu odnosi się do dwóch poprzednich obserwacji i dwóch poprzednich błędów prognozy, a także do wartości alpha. Tak więc formuła prognozowania w wierszu 15 odnosi się tylko do danych, które były dostępne w wierszu 14 i wcześniejszych. (Oczywiście, gdybyśmy chcieli użyć prostego zamiast liniowego wygładzania wykładniczego, moglibyśmy zamiast tego zastąpić formułę SES, moglibyśmy również użyć Holt8217s zamiast modelu LES Brown8217s, który wymagałby dwóch dodatkowych kolumn formuł do obliczenia poziomu i trendu które są używane w prognozie.) Błędy są obliczane w następnej kolumnie (tutaj, w kolumnie J) przez odjęcie prognoz od rzeczywistych wartości. Błąd średniokwadratowego kwadratu jest obliczany jako pierwiastek kwadratowy z wariancji błędów plus kwadrat średniej. (Wynika to z matematycznej tożsamości: MSE VARIANCE (błędy) (AVERAGE (błędy)). 2) Przy obliczaniu średniej i wariancji błędów w tym wzorze, pierwsze dwa okresy są wykluczone, ponieważ model faktycznie nie zaczyna prognozowania dopóki trzeci okres (wiersz 15 w arkuszu kalkulacyjnym). Optymalną wartość alfa można znaleźć albo ręcznie zmieniając alfa, aż zostanie znaleziony minimalny RMSE, albo też można użyć quotSolverquot, aby wykonać dokładną minimalizację. Wartość alpha pokazana tutaj przez Solver (alpha0.471). Zazwyczaj dobrym pomysłem jest wykreślenie błędów modelu (w przekształconych jednostkach), a także obliczenie i wykreślenie ich autokorelacji w czasie opóźnienia do jednego sezonu. Oto wykres szeregów czasowych błędów (wyrównanych sezonowo): Autokorelacje błędów są obliczane za pomocą funkcji CORREL () w celu obliczenia korelacji błędów ze sobą opóźnionych o jeden lub więcej okresów - szczegóły są pokazane w modelu arkusza kalkulacyjnego . Oto wykres autokorelacji błędów w pierwszych pięciu opóźnieniach: Autokorelacje na opóźnieniach od 1 do 3 są bardzo bliskie zeru, ale skok w 4 oporze (którego wartość wynosi 0,35) jest nieco uciążliwy - sugeruje to, że proces dostosowania sezonowego nie zakończył się pełnym sukcesem. W rzeczywistości jest to jednak marginalnie znaczące. 95 pasm istotności do testowania, czy autokorelacje różnią się znacznie od zera, są z grubsza dodatnie lub ujemne 2SQRT (n-k), gdzie n jest wielkością próbki, a k jest opóźnieniem. Tutaj n wynosi 38, a k zmienia się od 1 do 5, więc pierwiastek kwadratowy z-n-minus-k wynosi około 6 dla wszystkich z nich, a zatem ograniczenia do testowania statystycznej istotności odchyleń od zera są z grubsza lub-minus 26 lub 0,33. Jeśli ręcznie zmieniasz wartość alpha w tym modelu programu Excel, możesz zaobserwować wpływ na wykresy czasowe i wykresy autokorelacji błędów, a także na błąd średniokwadratowy, który zostanie zilustrowany poniżej. W dolnej części arkusza kalkulacyjnego formuła prognozowania jest cytowana w przyszłości, po prostu zastępując prognozy rzeczywistymi wartościami w punkcie, w którym wyczerpują się rzeczywiste dane - tj. gdzie zaczyna się quotthe futurequot. (Innymi słowy, w każdej komórce, w której wystąpi wartość danych w przyszłości, wstawiane jest odwołanie do komórki, które wskazuje na prognozę dla tego okresu.) Wszystkie inne formuły są po prostu kopiowane z góry: Zauważ, że błędy dla prognoz Przyszłość obliczana jest na zero. Nie oznacza to, że rzeczywiste błędy będą zerowe, ale raczej będą odzwierciedlać fakt, że dla celów prognozowania zakładamy, że przyszłe dane będą średnio równe prognozom. Uzyskane prognozy LES dla danych wyrównanych sezonowo wyglądają następująco: przy tej wartości alpha, która jest optymalna dla prognoz z wyprzedzeniem jednokresowym, przewidywany trend jest nieznacznie wyższy, odzwierciedlając lokalny trend zaobserwowany w ciągu ostatnich 2 lat albo tak. Dla innych wartości alfa można uzyskać bardzo różne projekcje trendów. Zazwyczaj dobrze jest zobaczyć, co dzieje się z długoterminową projekcją trendu, gdy alfa jest zmienna, ponieważ wartość, która jest najlepsza dla krótkoterminowego prognozowania, niekoniecznie będzie najlepszą wartością do przewidywania bardziej odległej przyszłości. Na przykład, tutaj jest wynik, który jest uzyskiwany, jeśli wartość alfa jest ręcznie ustawiona na 0,25: przewidywany długoterminowy trend jest teraz ujemny, a nie pozytywny. Przy mniejszej wartości alfa model przykłada większą wagę do starszych danych w oszacowanie obecnego poziomu i trendu oraz jego prognozy długoterminowe odzwierciedlają tendencję spadkową obserwowaną w ciągu ostatnich 5 lat, a nie ostatnią tendencję wzrostową. Ten wykres wyraźnie pokazuje również, że model o mniejszej wartości alfa wolniej reaguje na kwantowanie w danych i dlatego popełnia błąd tego samego znaku przez wiele okresów z rzędu. Jego błędy prognozy 1-krokowej są większe średnio niż te otrzymane wcześniej (RMSE 34,4 zamiast 27,4) i silnie dodatnio autokorelowane. Autokorelacja opóźnienia-1 wynosząca 0,56 znacznie przekracza wartość 0,33 obliczoną powyżej dla statystycznie istotnego odchylenia od zera. Alternatywą dla obniżania wartości alpha w celu wprowadzenia większej konserwatyzmu w prognozy długoterminowe jest czasem dodanie do modelu dodatkowego współczynnika tłumienia w celu spłaszczenia trendu po kilku okresach. Ostatnim krokiem w budowaniu modelu prognostycznego jest przytoczenie prognoz dotyczących prognoz LES poprzez pomnożenie ich przez odpowiednie wskaźniki sezonowe. Tak więc, zoptymalizowane prognozy w kolumnie I są po prostu produktem indeksów sezonowych w kolumnie F i sezonowo dostosowanych prognoz LES w kolumnie H. Obliczanie przedziałów ufności dla prognoz wyprzedzających o jeden krok z wyprzedzeniem przez ten model jest stosunkowo łatwe: najpierw obliczyć RMSE (błąd średniokwadratowy, który jest tylko pierwiastkiem kwadratowym z MSE), a następnie obliczyć przedział ufności dla prognozy skorygowanej sezonowo, dodając i odejmując dwukrotność RMSE. (Zasadniczo, 95 przedział ufności dla prognozy jednokresowej jest z grubsza równy prognozie punktowej plus lub minus - dwukrotność szacowanego odchylenia standardowego błędów prognozy, przy założeniu, że rozkład błędów jest w przybliżeniu normalny, a wielkość próby jest wystarczająco duży, powiedzmy, 20 lub więcej Tutaj RMSE, a nie standardowe odchylenie standardowe błędów, jest najlepszym oszacowaniem odchylenia standardowego przyszłych błędów prognozy, ponieważ uwzględnia odchylenia i zmienne losowe. dla sezonowo skorygowanej prognozy są następnie spontanicznie. wraz z prognozą poprzez pomnożenie ich przez odpowiednie wskaźniki sezonowe. W tym przypadku RMSE wynosi 27,4, a prognoza dostosowana sezonowo dla pierwszego przyszłego okresu (grudzień-93) wynosi 273,2. więc wyrównany sezonowo 95 przedział ufności wynosi od 273,2-227.4 218,4 do 273,2227.4 328,0. Pomnożenie tych limitów przez uwzględnienie wskaźnika sezonowego 68,61. uzyskujemy dolną i górną granicę ufności 149,8 i 225,0 wokół prognozy punktu Dec-93 na poziomie 187,4. Limity ufności dla prognoz dłuższych niż jeden okres będą generalnie poszerzać się wraz ze wzrostem horyzontu prognozy, ze względu na niepewność co do poziomu i trendu oraz czynników sezonowych, ale trudno jest je ogólnie obliczyć metodami analitycznymi. (Odpowiednim sposobem obliczania limitów ufności dla prognozy LES jest zastosowanie teorii ARIMA, ale niepewność w indeksach sezonowych to inna sprawa.) Jeśli chcesz realistyczny przedział ufności dla prognozy z więcej niż jednym okresem, biorąc pod uwagę wszystkie źródła błąd pod uwagę, najlepiej jest użyć metod empirycznych: na przykład, aby uzyskać przedział ufności dla dwuetapowej prognozy wyprzedzającej, możesz utworzyć kolejną kolumnę w arkuszu kalkulacyjnym, aby obliczyć prognozę dwuetapową dla każdego okresu ( przez ładowanie prognozy jednoetapowej). Następnie obliczyć RMSE błędów prognozy 2-etapowej i zastosować ją jako podstawę dla dwuetapowego przedziału ufności.