Obliczanie średniej ruchomej w Excelu (prosty, ważony i wykładniczy)

Podobnie jak wiele moich samouczków Excela, ten jest również zainspirowany jednym z zapytań, które otrzymałem od znajomego. Chciała obliczyć średnią ruchomą w programie Excel, a ja poprosiłem ją o wyszukanie jej w Internecie (lub obejrzenie filmu na YouTube o tym).

Ale potem postanowiłem sam napisać (to, że na studiach byłem trochę statystycznym frajerem, również odegrało niewielką rolę).

Teraz, zanim powiem ci, jak obliczyć średnią ruchomą w programie Excel, pozwól, że szybko przedstawię przegląd tego, co oznacza średnia ruchoma i jakie rodzaje średnich kroczących są dostępne.

Jeśli chcesz przejść do części, w której pokazuję, jak obliczyć średnią ruchomą w programie Excel, kliknij tutaj.

Uwaga: nie jestem ekspertem od statystyk i moim zamiarem w tym samouczku nie jest omówienie wszystkiego o średnich ruchomych. Chcę tylko pokazać, jak obliczyć średnie ruchome w Excelu (z krótkim wprowadzeniem, co oznaczają średnie ruchome).

Co to jest średnia krocząca?

Jestem pewien, że wiesz, jaka jest średnia wartość.

Jeśli mam dane o temperaturze dziennej z trzech dni, możesz łatwo podać mi średnią z ostatnich trzech dni (wskazówka: w tym celu możesz użyć funkcji ŚREDNIA w programie Excel).

Średnia ruchoma (nazywana również średnią kroczącą lub średnią kroczącą) ma miejsce, gdy utrzymujesz taki sam okres średniej, ale porusza się w miarę dodawania nowych danych.

Na przykład, w dniu 3, jeśli zapytam cię o 3-dniową średnią ruchomą, dasz mi średnią wartość temperatury z dnia 1, 2 i 3. A jeśli w dniu 4 zapytam cię o 3-dniową średnią ruchomą temperaturę , dasz mi średnią z dnia 2, 3 i 4.

W miarę dodawania nowych danych zachowujesz ten sam okres (3 dni), ale wykorzystujesz najnowsze dane do obliczania średniej ruchomej.

Średnia ruchoma jest intensywnie wykorzystywana do analizy technicznej i wiele banków i analityków giełdowych używa jej na co dzień (poniżej przykład, który otrzymałem ze strony Market Realist).

Jedną z korzyści płynących z używania średnich kroczących jest to, że daje ci trend, a także wygładza do pewnego stopnia fluktuacje. Na przykład, w przypadku naprawdę upalnego dnia, trzydniowa średnia ruchoma temperatury nadal zapewniłaby wygładzenie średniej wartości (zamiast pokazywać naprawdę wysoką wartość, która może być wartością odstającą - jedno- poza instancją).

Rodzaje średnich kroczących

Istnieją trzy rodzaje średnich kroczących:

• Prosta średnia ruchoma (SMA)
• Ważona średnia ruchoma (WMA)
• Wykładnicza średnia krocząca (EMA)

Prosta średnia krocząca (SMA)

Jest to prosta średnia punktów danych w danym czasie trwania.

W naszym przykładzie temperatury dziennej, gdy po prostu weźmiesz średnią z ostatnich 10 dni, otrzymasz prostą średnią kroczącą z 10 dni.

Można to osiągnąć poprzez uśrednienie punktów danych w danym czasie trwania. W programie Excel możesz to łatwo zrobić, korzystając z funkcji ŚREDNIA (jest to omówione w dalszej części tego samouczka).

Ważona średnia krocząca (WMA)

Załóżmy, że pogoda staje się coraz chłodniejsza z każdym mijającym dniem i używasz 10-dniowej średniej ruchomej, aby uzyskać trend temperatury.

Temperatura w dniu 10 będzie prawdopodobnie lepszym wskaźnikiem trendu w porównaniu z dniem 1 (ponieważ temperatura spada z każdym dniem).

Tak więc lepiej nam będzie, jeśli będziemy bardziej polegać na wartości dnia 10.

Aby odzwierciedlić to w naszej średniej ruchomej, możesz nadać większą wagę najnowszym danym, a mniejszą przeszłym. W ten sposób nadal otrzymujesz trend, ale z większym wpływem najnowszych danych.

Nazywa się to ważoną średnią ruchomą.

Wykładnicza średnia krocząca (EMA)

Wykładnicza średnia krocząca to rodzaj ważonej średniej ruchomej, w której najnowsze dane mają większą wagę i maleją wykładniczo dla starszych punktów danych.

Jest również nazywany wykładniczą ważoną średnią kroczącą (EWMA)

Różnica między WMA a EMA polega na tym, że w przypadku WMA możesz przypisywać wagi na podstawie dowolnych kryteriów. Na przykład w 3-punktowej średniej ruchomej możesz przypisać wiek 60% do ostatniego punktu danych, 30% do środkowego punktu danych i 10% do najstarszego punktu danych.

W EMA, wyższa waga jest przypisywana najnowszej wartości, a waga jest wykładniczo niższa dla wcześniejszych wartości.

Dość wykładu o statystyce.

Teraz zanurkujmy i zobaczmy, jak obliczyć średnie kroczące w programie Excel.

Obliczanie prostej średniej kroczącej (SMA) za pomocą pakietu narzędzi do analizy danych w programie Excel

Microsoft Excel ma już wbudowane narzędzie do obliczania prostych średnich kroczących.

Nazywa się Pakiet narzędzi do analizy danych.

Zanim zaczniesz korzystać z pakietu narzędzi do analizy danych, musisz najpierw sprawdzić, czy masz go na wstążce programu Excel, czy nie. Istnieje duża szansa, że ​​najpierw musisz wykonać kilka kroków, aby go włączyć.

Jeśli masz już opcję Analiza danych na karcie Dane, pomiń poniższe kroki i zobacz kroki dotyczące obliczania średnich kroczących.

Kliknij kartę Dane i sprawdź, czy widzisz opcję Analiza danych, czy nie. Jeśli go nie widzisz, wykonaj poniższe czynności, aby udostępnić go na wstążce.

1. Kliknij kartę Plik
2. Kliknij Opcje
3. W oknie dialogowym Opcje programu Excel kliknij Dodatki
4. W dolnej części okna dialogowego wybierz Dodatki programu Excel z listy rozwijanej, a następnie kliknij przycisk Przejdź.
5. W otwartym oknie dialogowym Dodatki zaznacz opcję Analysis Toolpak
6. Kliknij OK.

Powyższe kroki włączyłyby pakiet narzędzi do analizy danych, a teraz zobaczysz tę opcję na karcie Dane.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz obliczyć średnią ruchomą z ostatnich trzech interwałów.

Poniżej znajdują się kroki, aby użyć analizy danych do obliczenia prostej średniej ruchomej:

1. Kliknij kartę Dane
2. Kliknij opcję Analiza danych
3. W oknie dialogowym Analiza danych kliknij opcję Średnia ruchoma (być może będziesz musiał trochę przewinąć, aby do niej dotrzeć).
4. Kliknij OK. Spowoduje to otwarcie okna dialogowego „Średnia ruchoma”.
5. W Zakresie wejściowym wybierz dane, dla których chcesz obliczyć średnią ruchomą (w tym przykładzie B2:B11)
6. W opcji Interwał wpisz 3 (ponieważ obliczamy trzypunktową średnią ruchomą)
7. W zakresie danych wyjściowych wprowadź komórkę, w której chcesz uzyskać wyniki. W tym przykładzie używam C2 jako zakresu wyjściowego
8. Kliknij OK

Powyższe kroki dadzą ci wynik średniej ruchomej, jak pokazano poniżej.

Zauważ, że pierwsze dwie komórki w kolumnie C mają wynik jako błąd #N/D. Dzieje się tak, ponieważ jest to trzypunktowa średnia krocząca i potrzebuje co najmniej trzech punktów danych, aby uzyskać pierwszy wynik. Tak więc rzeczywiste wartości średniej ruchomej zaczynają się po trzecim punkcie danych.

Zauważysz również, że wszystko, co zrobił ten pakiet narzędzi do analizy danych, polega na zastosowaniu do komórek formuły ŚREDNIA. Więc jeśli chcesz to zrobić ręcznie bez pakietu narzędzi do analizy danych, z pewnością możesz to zrobić.

Jest jednak kilka rzeczy, które są łatwiejsze do zrobienia dzięki pakietowi narzędzi do analizy danych. Na przykład, jeśli chcesz uzyskać wartość błędu standardowego, a także wykres średniej kroczącej, wystarczy zaznaczyć pole i będzie to część wyniku.

Obliczanie średnich kroczących (SMA, WMA, EMA) za pomocą formuł w Excelu

Możesz również obliczyć średnie kroczące za pomocą formuły ŚREDNIA.

W rzeczywistości, jeśli wszystko, czego potrzebujesz, to średnia ruchoma (a nie błąd standardowy lub wykres), użycie formuły może być lepszą (i szybszą) opcją niż użycie pakietu narzędzi do analizy danych.

Ponadto Toolpak do analizy danych podaje tylko prostą średnią kroczącą (SMA), ale jeśli chcesz obliczyć WMA lub EMA, musisz polegać tylko na formułach.

Obliczanie prostej średniej kroczącej za pomocą formuł

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz obliczyć 3-punktową SMA:

W komórce C4 wprowadź następującą formułę:

=ŚREDNIA(B2:B4)

Skopiuj tę formułę dla wszystkich komórek, a otrzymasz SMA na każdy dzień.

Pamiętaj: obliczając SMA za pomocą formuł, musisz upewnić się, że odniesienia w formule są względne. Oznacza to, że formuła może być = ŚREDNIA (B2: B4) lub = ŚREDNIA ($ B2: $ B4), ale nie może być = ŚREDNIA ($ B $ 2: $ B $ 4) lub = ŚREDNIA (B $ 2: B $ 4 ). Część z numerem wiersza w odwołaniu musi być bez znaku dolara. Możesz przeczytać więcej o odniesieniach bezwzględnych i względnych tutaj.

Ponieważ obliczamy 3-punktową prostą średnią kroczącą (SMA), pierwsze dwie komórki (przez pierwsze dwa dni) są puste i zaczynamy używać formuły od trzeciego dnia. Jeśli chcesz, możesz użyć pierwszych dwóch wartości bez zmian i użyć wartości SMA od trzeciej.

Obliczanie ważonej średniej kroczącej za pomocą formuł

W przypadku WMA musisz znać wagi, które zostaną przypisane do wartości.

Załóżmy na przykład, że musisz obliczyć 3-punktowy WMA dla poniższego zestawu danych, gdzie 60% wagi jest przypisane do ostatniej wartości, 30% do poprzedniej i 10% do poprzedniej.

Aby to zrobić, wprowadź następującą formułę w komórce C4 i skopiuj dla wszystkich komórek.

=0,6*B4+0,3*B3+0,1*B2

Ponieważ obliczamy 3-punktową ważoną średnią kroczącą (WMA), pierwsze dwie komórki (przez pierwsze dwa dni) są puste i zaczynamy używać formuły od trzeciego dnia. Jeśli chcesz, możesz użyć pierwszych dwóch wartości bez zmian i użyć wartości WMA od trzeciej.

Obliczanie wykładniczej średniej kroczącej za pomocą formuł

Wykładnicza średnia krocząca (EMA) nadaje wyższą wagę najnowszej wartości, a wagi maleją wykładniczo dla wcześniejszych wartości.

Poniżej znajduje się wzór do obliczenia EMA dla trzypunktowej średniej ruchomej:

EMA = [Najnowsza wartość - Poprzednia wartość EMA] * (2 / N+1) + Poprzednia EMA

… gdzie N byłoby 3 w tym przykładzie (ponieważ obliczamy trzypunktową EMA)

Uwaga: dla pierwszej wartości EMA (gdy nie masz żadnej poprzedniej wartości do obliczenia EMA), po prostu weź wartość taką, jaka jest i uznaj ją za wartość EMA. Następnie możesz użyć tej wartości w przyszłości.

Załóżmy, że masz poniższy zestaw danych i chcesz obliczyć trzyokresową EMA:

W komórce C2 wprowadź tę samą wartość, co w B2. Dzieje się tak, ponieważ nie ma poprzedniej wartości do obliczenia EMA.

W komórce C3 wprowadź poniższą formułę i skopiuj dla wszystkich komórek:

=(B3-C2)*(2/4)+C2

W tym przykładzie zachowałem prostotę i użyłem ostatniej wartości i poprzedniej wartości EMA do obliczenia bieżącej EMA.

Innym popularnym sposobem na zrobienie tego jest najpierw obliczenie prostej średniej kroczącej, a następnie użycie jej zamiast aktualnej ostatniej wartości.

Dodawanie ruchomej średniej linii trendu do wykresu kolumnowego

Jeśli masz zestaw danych i tworzysz z niego wykres słupkowy, możesz również kilkoma kliknięciami dodać linię trendu średniej ruchomej.

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Poniżej znajdują się kroki, aby utworzyć wykres słupkowy na podstawie tych danych i dodać do niego trzyczęściową linię trendu średniej ruchomej:

1. Wybierz zbiór danych (w tym nagłówki)
2. Kliknij kartę Wstaw
3. W grupie Wykres kliknij ikonę „Wstaw wykres kolumnowy lub słupkowy”.
4. Kliknij opcję Wykres kolumnowy grupowany. Spowoduje to wstawienie wykresu do arkusza roboczego.
5. Po wybraniu wykresu kliknij kartę Projekt (ta karta pojawia się tylko po zaznaczeniu wykresu)
6. W grupie Układy wykresów kliknij „Dodaj element wykresu”.
7. Najedź kursorem na opcję „Linia trendu”, a następnie kliknij „Więcej opcji linii trendu”
8. W okienku Formatuj linię trendu wybierz opcję „Średnia ruchoma” i ustaw liczbę okresów.

Otóż ​​to! Powyższe kroki dodałyby ruchomą linię trendu do wykresu kolumnowego.

Jeśli chcesz wstawić więcej niż jedną linię trendu średniej ruchomej (na przykład jedną dla 2 okresów i jedną dla 3 okresów), powtórz kroki od 5 do 8).

Możesz użyć tych samych kroków, aby wstawić linię trendu średniej ruchomej do wykresu liniowego.

Formatowanie linii trendu średniej ruchomej

W przeciwieństwie do zwykłego wykresu liniowego, linia trendu średniej ruchomej nie pozwala na duże formatowanie. Na przykład, jeśli chcesz wyróżnić określony punkt danych na linii trendu, nie będziesz w stanie tego zrobić.

Oto kilka rzeczy, które możesz sformatować w linii trendu:

• Kolor linii. Możesz użyć tego, aby podświetlić jedną z linii trendu, ustawiając wszystko na wykresie w jasnym kolorze i wyskakując z linii trendu w jasnym kolorze
• ten grubość linii
• ten przezroczystość linii

Aby sformatować linię trendu średniej ruchomej, kliknij ją prawym przyciskiem myszy, a następnie wybierz opcję Formatuj linię trendu.

Spowoduje to otwarcie okienka Formatuj linię trendu po prawej stronie. To okienko zawiera wszystkie opcje formatowania (w różnych sekcjach - Wypełnienie i linia, Efekty i Opcje linii trendu).