Przenoszenie metody średniej w programie Microsoft Excel

Metoda średniej ruchomej jest narzędziem statystycznym, za pomocą którego można rozwiązywać różne zadania. W szczególności jest często wykorzystywany w prognozowaniu. W programie Excel można również użyć tego narzędzia do rozwiązywania wielu zadań. Zobaczmy, jak średnia ruchoma jest używana w programie Excel.

Zastosowanie średniej ruchomej

Znaczenie tej metody polega na tym, że przy jej pomocy następuje zmiana bezwzględnych wartości dynamicznych wybranej serii na średnią arytmetyczną w pewnym okresie przez wygładzenie danych. To narzędzie służy do obliczeń ekonomicznych, prognozowania, w procesie handlu na giełdzie itp. Stosowanie metody średniej ruchomej w Excelu najlepiej wykonać przy użyciu zaawansowanego narzędzia do analizy danych statystycznych o nazwie Analysis Pack . Ponadto w tym samym celu można użyć wbudowanej funkcji Excel AVERAGE .

Metoda 1: Pakiet analityczny

Pakiet analizy jest dodatkiem Excel, który jest domyślnie wyłączony. Dlatego przede wszystkim należy go uwzględnić.

1. Przechodzimy do zakładki "Plik" . Kliknij pozycję "Opcje" .
2. W oknie, które zostanie otwarte, przejdź do sekcji "Dodatki" . W dolnej części okna parametr "Dodatki do Excela" należy ustawić w polu "Zarządzaj" . Kliknij przycisk "Przejdź" .
3. Dostajemy się do okna dodatków. Zaznacz pole obok "Pakietu analizy" i kliknij przycisk "OK" .

Po tej czynności pakiet "Analiza danych" jest aktywowany, a odpowiedni przycisk pojawił się na wstążce w zakładce "Dane" .

A teraz przyjrzyjmy się, jak możesz bezpośrednio wykorzystać możliwości analizy danych dla metody średniej ruchomej. Zróbmy prognozę na dwunasty miesiąc na podstawie informacji o dochodach firmy za 11 poprzednich okresów. Aby to zrobić, używamy tabeli wypełnionej danymi, a także narzędzi narzędzi do analizy .

1. Przejdź do zakładki "Dane" i kliknij przycisk "Analiza danych" , który znajduje się na pasku narzędzi w bloku "Analiza" .
2. Zostanie otwarta lista narzędzi dostępnych w Pakiecie analizy . Wybierz z nich nazwę "średnia krocząca" i kliknij przycisk "OK" .
3. Zostanie uruchomione okno do wprowadzania danych do prognozowania za pomocą metody średniej ruchomej.

   W polu "Interwał wejściowy" podaj adres zakresu, w którym miesięczny przychód jest obliczany bez komórki, przy czym dane powinny być obliczane.

   W polu "Interval" określ interwał przetwarzania wartości za pomocą metody antyaliasingu. Najpierw ustawmy wartość antyaliasingu na trzy miesiące, dlatego wpisujemy liczbę "3" .

   W polu "Przedział wyjściowy" należy określić dowolny pusty zakres na arkuszu, w którym dane będą wyprowadzane po przetworzeniu, które muszą być jedną komórką większą niż interwał wejściowy.

   Sprawdź także pole "Błędy standardowe" .

   Jeśli to konieczne, możesz również zaznaczyć pole "Wykres wyjściowy", aby wyświetlić demonstrację wizualną, chociaż w naszym przypadku nie jest to konieczne.

   Po dokonaniu wszystkich ustawień kliknij przycisk "OK" .

4. Program wyświetli wynik przetwarzania.
5. Teraz wykonaj wygładzanie przez okres dwóch miesięcy, aby ustalić, który wynik jest bardziej poprawny. W tym celu ponownie uruchamiamy narzędzie "Średnia ruchoma" pakietu analitycznego .

   W polu "Interwał wejściowy" pozostawiamy takie same wartości jak w poprzednim przypadku.

   W polu "Interval" wpisujemy numer "2" .

   W polu "Przedział wyjściowy" określamy adres nowego pustego zakresu, który znowu powinien być jedną komórką większą niż interwał wejściowy.

   Pozostałe ustawienia są takie same. Następnie kliknij przycisk "OK" .

6. Następnie program oblicza i wyświetla wynik na ekranie. Aby ustalić, który z tych dwóch modeli jest dokładniejszy, musimy porównać błędy standardowe. Im mniejszy jest dany indeks, tym większe prawdopodobieństwo dokładności uzyskanego wyniku. Jak widać, dla wszystkich wartości standardowy błąd w obliczaniu dwumiesięcznego kroczącego jest mniejszy niż ten sam wskaźnik przez 3 miesiące. Tak więc przewidywaną wartość dla grudnia można uznać za wartość obliczoną metodą ślizgową dla ostatniego okresu. W naszym przypadku wartość ta wynosi 990,4 tysięcy rubli.

Metoda 2: Użyj funkcji ŚREDNIA

W programie Excel istnieje inny sposób zastosowania metody średniej ruchomej. Aby z niego skorzystać, należy zastosować szereg standardowych funkcji programu, których podstawowym celem jest ŚREDNIA . Na przykład użyjemy tej samej tabeli przychodów firmy, jak w pierwszym przypadku.

Podobnie jak w ostatnim czasie, musimy stworzyć wygładzoną serię czasową. Ale tym razem działania nie będą tak zautomatyzowane. Powinieneś obliczyć średnią wartość za każde dwa, a następnie za trzy miesiące, aby móc porównać wyniki.

Przede wszystkim obliczamy średnie wartości dla dwóch poprzednich okresów przy użyciu funkcji ŚREDNIA . Możemy to zrobić dopiero od marca, ponieważ dla późniejszych dat istnieje przerwa w wartościach.

1. Wybierz komórkę w pustej kolumnie w wierszu na marzec. Następnie kliknij ikonę "Wstaw funkcję" , która znajduje się w pobliżu wiersza formuły.
2. Okno Wizards zostanie aktywowane. W kategorii "Statystyczne" szukamy wartości "ŚREDNIA" , wybierz ją i kliknij przycisk "OK" .
3. Wyświetlane jest okno argumentów instrukcji ŚREDNIA . Składnia jest następująca:

   =СРЗНАЧ(число1;число2;…)

   Wymagany jest tylko jeden argument.

   W naszym przypadku w polu "Numer 1" musimy podać link do zakresu, w którym wskazany jest dochód za dwa poprzednie okresy (styczeń i luty). Ustaw kursor w polu i wybierz odpowiednie komórki na arkuszu w kolumnie "Przychody" . Następnie kliknij przycisk "OK" .

4. Jak widać, wynik obliczenia średniej dla dwóch poprzednich okresów był wyświetlany w komórce. Aby wykonać takie obliczenia dla wszystkich pozostałych miesięcy tego okresu, musimy skopiować tę formułę do innych komórek. Aby to zrobić, stajemy się kursorem w prawym dolnym rogu komórki zawierającej funkcję. Kursor zostanie przekonwertowany na znacznik wypełnienia, który wygląda jak krzyżyk. Zacisnąć lewy przycisk myszy i przeciągnąć go na sam koniec kolumny.
5. Otrzymujemy obliczenia wyników średniej dla dwóch poprzednich miesięcy przed końcem roku.
6. Teraz wybierz komórkę w następnej pustej kolumnie w rzędzie na kwiecień. Okno wywołań funkcji ŚREDNIA wywołujemy w taki sam sposób jak opisano wcześniej. W polu "Numer 1" wpisujemy współrzędne komórek w kolumnie "Przychody" od stycznia do marca. Następnie kliknij przycisk "OK" .
7. Za pomocą znacznika wypełnienia skopiuj formułę do komórek w tabeli poniżej.
8. Tak, obliczyliśmy wartości. Teraz, podobnie jak w poprzednim czasie, będziemy musieli dowiedzieć się, który rodzaj analizy jest lepszy: z wygładzaniem 2 lub 3 miesięcy. Aby to zrobić, obliczyć średnie odchylenie kwadratowe i kilka innych wskaźników. Najpierw obliczamy bezwzględne odchylenie za pomocą standardowej funkcji Excel ABS , która zamiast liczby dodatniej lub ujemnej zwraca ich moduł. Ta wartość będzie równa różnicy między rzeczywistą przychodami za wybrany miesiąc i prognozę. Ustaw kursor na następną pustą kolumnę w wierszu na maj. Zadzwoń do Kreatora funkcji .
9. W kategorii "Matematyczne" podświetlamy nazwę funkcji "ABS" . Kliknij przycisk "OK" .
10. Otworzy się okno argumentów funkcji ABS . W pojedynczym polu "Liczba" wskazujemy różnicę między zawartością komórek w kolumnach "Przychody" i "2 miesiące" w maju. Następnie kliknij przycisk "OK" .
11. Za pomocą wypełniacza skopiuj tę formułę do wszystkich wierszy tabeli do listopada włącznie.
12. Obliczamy średnią wartość absolutnego odchylenia dla całego okresu za pomocą znanej funkcji ŚREDNIA .
13. Podobną procedurę wykonujemy, aby obliczyć bezwzględne odchylenie dla ruchomego w ciągu 3 miesięcy. Najpierw zastosuj funkcję ABS . Tylko tym razem bierzemy pod uwagę różnicę między zawartością komórek a faktycznym dochodem i planowaną, obliczoną metodą średniej ruchomej przez 3 miesiące.
14. Następnie obliczyć średnią wszystkich danych bezwzględnego odchylenia za pomocą funkcji ŚREDNIA .
15. Następnym krokiem jest obliczenie względnego odchylenia. Jest równy stosunkowi bezwzględnego odchylenia do rzeczywistego wskaźnika. Aby uniknąć ujemnych wartości, ponownie wykorzystujemy możliwości oferowane przez operatora ABS . Tym razem za pomocą tej funkcji dzielimy bezwzględną wartość odchylenia, stosując metodę średniej ruchomej przez 2 miesiące dla rzeczywistego dochodu za wybrany miesiąc.
16. Ale odchylenie względne jest zwykle wyświetlane w procentach. Dlatego wybierz odpowiedni zakres na arkuszu, przejdź do zakładki "Strona główna" , gdzie w polu "Liczba" w polu formatu specjalnego ustaw format procentowy. Następnie wynik obliczenia odchylenia względnego jest wyświetlany w procentach.
17. Podobna operacja do obliczenia odchylenia względnego jest wykonywana z danymi za pomocą wygładzania przez 3 miesiące. Tylko w tym przypadku, aby obliczyć jako podzielną, używamy kolejnej kolumny tabeli, którą nazywamy "Abs. off (3m) " . Następnie przeliczamy wartości liczbowe na procenty.
18. Następnie obliczyć średnie wartości dla obu kolumn z odchyleniem względnym, tak jak poprzednio, używając funkcji ŚREDNIA . Ponieważ przyjmujemy wartości procentowe jako argumenty dla funkcji, nie musimy jej konwertować. Operator wyprowadza wynik w formacie procentowym.
19. Teraz doszliśmy do obliczenia średniej odchylenia kwadratowego. Ten wskaźnik pozwoli nam bezpośrednio porównać jakość obliczeń przy użyciu antyaliasingu przez dwa i trzy miesiące. W naszym przypadku odchylenie standardowe będzie równe pierwiastkowi kwadratowemu z sumy kwadratów różnic w rzeczywistym przychodzie i średniej ruchomej podzielonej przez liczbę miesięcy. Aby wykonać obliczenia w programie, musimy korzystać z wielu funkcji, w szczególności ROOT , SUMMKVRAZN i ACCOUNT . Na przykład, aby obliczyć średnie odchylenie kwadratowe podczas używania linii wygładzania przez dwa miesiące w maju, w naszym przypadku użyjemy następującej formuły:

    =КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))

    Skopiuj go do innych komórek w kolumnie, obliczając średnie odchylenie kwadratowe za pomocą znacznika wypełnienia.

20. Podobna operacja do obliczenia średniej odchyłki kwadratowej jest wykonywana dla średniej ruchomej przez 3 miesiące.
21. Następnie obliczamy średnią wartość dla całego okresu dla obu tych wskaźników, stosując funkcję ŚREDNIA .
22. Porównując obliczenia za pomocą metody średniej ruchomej z wygładzaniem po 2 i 3 miesiącach dla takich wskaźników, jak bezwzględne odchylenie, względne odchylenie i odchylenie od średniej kwadratowej, możemy z pewnością stwierdzić, że wygładzanie przez dwa miesiące daje bardziej wiarygodne wyniki niż stosowanie antyaliasingu przez trzy miesiące. Wskazuje na to fakt, że powyższe wskaźniki dla dwumiesięcznej średniej kroczącej wynoszą mniej niż trzy miesiące.
23. Tak więc przewidywany przychód spółki za grudzień wyniesie 990,4 tys. Zł. Jak widać, ta wartość całkowicie pokrywa się z tą, którą uzyskaliśmy, wykonując obliczenia za pomocą narzędzi pakietu analitycznego .

Lekcja: Kreator funkcji w programie Excel

Obliczyliśmy prognozę za pomocą metody średniej ruchomej na dwa sposoby. Jak widać, ta procedura jest znacznie łatwiejsza do wykonania przy użyciu narzędzi pakietu analitycznego . Niemniej jednak niektórzy użytkownicy nie zawsze ufają automatycznym obliczeniom i wolą używać funkcji ŚREDNIA i operatory towarzyszące do obliczeń w celu sprawdzenia najbardziej niezawodnej wersji. Chociaż, jeśli wszystko jest zrobione poprawnie, wynik obliczeń powinien być dokładnie taki sam na wyjściu.

• ← Obliczanie funkcji Laplace w Microsoft Excel
• Usuwanie linii w programie Microsoft Excel →