Jak obliczyć średnie ruchome w programie Excel Analiza danych programu Excel dla manekinów, wydanie drugie Polecenie analizy danych dostarcza narzędzie do obliczania średnich ruchomej i wykładniczej w programie Excel. Załóżmy, że ze względu na ilustrację, że zbierasz dzienne informacje o temperaturze. Chcesz obliczyć średnią ruchu trzydniowego 8212 średnią z ostatnich trzech dni 8212 w ramach kilku prostych prognoz pogody. Aby obliczyć średnie ruchome dla tego zestawu danych, wykonaj następujące kroki. Aby obliczyć średnią ruchome, kliknij najpierw przycisk polecenia Data Analysis (Dane) tab8217s. Gdy program Excel wyświetli okno dialogowe analizy danych, wybierz z listy pozycję Średnia ruchomości, a następnie kliknij przycisk OK. Excel wyświetli okno dialogowe Ruchome Średnia. Zidentyfikuj dane, których chcesz użyć do obliczenia średniej ruchomej. Kliknij pole wyboru Zakres wejściowy w oknie dialogowym Średnia ruchoma. Następnie zidentyfikuj zakres wejściowy, wpisując adres zakresu arkusza roboczego lub użyj myszy, aby wybrać zakres arkusza roboczego. Odnośnik zakresu powinien używać adresów bezwzględnych komórek. Adres bezwzględnej komórki poprzedza numer kolumny i wiersza ze znakami, tak jak w A1: A10. Jeśli pierwsza komórka w Twoim zakresie wejściowym zawiera etykietę tekstową do identyfikowania lub opisywania danych, zaznacz pole wyboru Etykiety w pierwszym rzędzie. W polu tekstowym Interval (Powiadom) wpisz Excel, ile wartości należy uwzględnić w obliczeniach średniej ruchomej. Możesz obliczyć średnią ruchu za pomocą dowolnej liczby wartości. Domyślnie program Excel stosuje trzy ostatnie wartości do obliczania średniej ruchomej. Aby określić, że do obliczania średniej ruchomej użyta jest inna liczba wartości, wprowadź tę wartość w polu tekstowym Interval. Powiedz Excel, gdzie umieścić średnie ruchome dane. Skorzystaj z pola tekstowego Zakres wyjściowy, aby zidentyfikować zakres arkuszy, na który chcesz umieścić średnie ruchome dane. W przykładowym arkuszu danych średnie ruchome zostały umieszczone w obszarze arkusza B2: B10. (Opcjonalnie) Określ, czy chcesz wykresu. Jeśli chcesz, aby wykres zawierający średnie ruchome informacje, zaznacz pole wyboru Wyjście wykresu. (Opcjonalnie) Wskaż, czy chcesz obliczyć standardowe informacje o błędach. Jeśli chcesz obliczyć błędy standardowe dla danych, zaznacz pole wyboru Standardowe błędy. Excel umieszcza standardowe wartości błędów obok wartości średniej ruchomej. (Standardowa informacja o błędzie trafia do C2: C10). Po zakończeniu określania, jakie średnie ruchome informacje mają być obliczane i gdzie chcesz go umieścić, kliknij przycisk OK. Excel oblicza średnie ruchome informacje. Uwaga: jeśli program Excel doesn8217t ma wystarczające informacje do obliczenia średniej ruchomej dla standardowego błędu, umieszcza komunikat o błędzie w komórce. Możesz zobaczyć kilka komórek, które pokazują ten komunikat o błędzie jako wartość. Dodanie trendu lub średniej ruchomej do wykresu Dotyczy: Excel 2018 Word 2018 PowerPoint 2018 Excel 2017 Word 2017 Outlook 2017 PowerPoint 2017 Więcej. Mniej Aby wyświetlić wykresy danych lub średnie kroczące na utworzonym wykresie. możesz dodać linię trendu. Możesz także poszerzyć linię poza faktyczne dane, aby pomóc przewidzieć przyszłe wartości. Na przykład kolejna liniowa tendencja prognozuje dwa kwartały przed sobą i wyraźnie wskazuje na tendencję wzrostową, która wygląda obiecująco na przyszłą sprzedaż. Można dodać trend do wykresu 2-D, który nie jest układany w stos, w tym obszar, pasek, kolumna, linia, czas, rozproszenie i bańka. Nie można dodać trendu do ułożonych, 3-D, radarowych, kołowych, powierzchniowych lub donutowych. Dodawanie trendu Na wykresie kliknij serie danych, do których chcesz dodać linię trendu lub średnią ruchu. Linia trendu rozpoczyna się od pierwszego punktu danych wybranej serii danych. Zaznacz pole Trendline. Aby wybrać inny typ linii trendu, kliknij strzałkę obok linii Trendline. a następnie kliknij Wykład. Prognoza liniowa. lub dwie średnie ruchy okresowe. Aby uzyskać dodatkowe trendy, kliknij Więcej opcji. Jeśli wybierzesz opcję Więcej opcji. kliknij żądaną opcję w panelu Format trendline w opcji Trendline. Jeśli wybierzesz Wielomian. wprowadź najwyższą moc dla zmiennej niezależnej w polu Zamów. Jeśli wybierzesz Przeprowadzka Średnia. wprowadź liczbę okresów używanych do obliczania średniej ruchomej w polu Okres. Wskazówka: Linia trendu jest najbardziej dokładna, gdy jej wartość kwadratowa R (liczba od 0 do 1, która pokazuje przybliżone wartości dla trendu odpowiadają rzeczywistym danymi) jest równa lub zbliżona 1. Gdy dodasz linię odniesienia do swoich danych , Program Excel oblicza automatycznie wartość R kwadratową. Możesz wyświetlić tę wartość na wykresie, sprawdzając wartość kwadratową R w polu wykresu (panel Format Trendline, Opcje Trendline). Więcej informacji na temat wszystkich opcji linii trendu można znaleźć w poniższych sekcjach. Linia liniowa Linia ta wykorzystuje ten typ trendu, aby utworzyć linię prostą dopasowaną do prostych liniowych zestawów danych. Twoje dane są liniowe, jeśli wzorzec w punktach danych wygląda jak linia. Linia trendu zazwyczaj pokazuje, że coś rośnie lub maleje w stałym tempie. Linia liniowa używa tego równania do obliczania najmniejszych kwadratów dopasowanych do linii: gdzie m jest nachyleniem a b jest przecinkami. Następująca liniowa tendencja pokazuje, że sprzedaż lodówek konsekwentnie wzrosła w ciągu 8 lat. Zauważ, że wartość kwadratowa R (liczba od 0 do 1, która pokazuje, jak blisko szacowane wartości dla trendu odpowiadają Twoim rzeczywistym danymi) wynosi 0.9792, co jest dobrym dopasowaniem linii do danych. Pokazując linię zakrzywioną najlepiej dopasowaną, ta tendencja jest użyteczna, gdy szybkość i szybkość zwiększa się lub szybko maleje. Logarytmiczna linia może używać wartości ujemnych i pozytywnych. Linia logarytmiczna używa tego równania do obliczania najmniejszych kwadratów pasujących do punktów: gdzie c i b są stałymi, a ln jest naturalną funkcją logarytmu. Poniższa logarytmiczna tendencja przewiduje przewidywany wzrost populacji zwierząt na obszarze o stałej przestrzeni, gdzie liczba ludności wyrównała się w miarę zmniejszania się przestrzeni dla zwierząt. Warto zauważyć, że wartość kwadratowa R wynosi 0.933, co jest stosunkowo dobrym dopasowaniem linii do danych. Ta tendencja jest przydatna, gdy Twoje dane wahają się. Na przykład podczas analizowania zysków i strat w dużym zbiorze danych. Kolejność wielomianu może być określona liczbą fluktuacji danych lub liczbą zakrętów (wzgórz i dolin) pojawiających się na krzywej. Zwykle pojedyńcza linia Order 2 ma tylko jedno wzgórze lub dolinę, zlecenie 3 ma jedno lub dwa wzgórza lub doliny, a zlecenie 4 ma do trzech wzgórz lub dolin. Wielomianowa lub krzywoliniowa linia wykorzystuje to równanie do obliczania najmniejszych kwadratów pasujących do punktów: gdzie b i są stałymi. Następująca kolejność wielomianów zlecenia 2 (jeden wierzchołek) pokazuje zależność między prędkością jazdy a zużyciem paliwa. Zwróć uwagę, że wartość kwadratowa R wynosi 0.979, która jest zbliżona do 1, więc linie są dobrze dopasowane do danych. Pokazując zakrzywioną linię, ta linia jest użyteczna dla zestawów danych, które porównują pomiary zwiększające się w określonym tempie. Na przykład przyspieszenie samochodu wyścigowego w odstępach 1 sekundy. Jeśli dane zawierają zero lub ujemne wartości, nie można utworzyć linii trendu mocy. Linia mocy używa tego równania do obliczania najmniejszych kwadratów dopasowanych do punktów: gdzie c i b są stałymi. Uwaga: ta opcja nie jest dostępna, jeśli dane zawierają wartości ujemne lub zerowe. Poniższy wykres pomiaru odległości przedstawia odległość w milisekundach. Linia trendu wyraźnie wskazuje na rosnące przyspieszenie. Warto zauważyć, że wartość kwadratowa R wynosi 0.986, co jest niemal idealnym dopasowaniem linii do danych. Pokazując zakrzywioną linię, ta tendencja jest użyteczna, gdy wartości danych wzrastają lub maleją w stale rosnących stawkach. Nie można utworzyć wykładniczej linii trendu, jeśli dane zawierają zero lub ujemne wartości. Linia wykładnicza używa tego równania do obliczania najmniejszych kwadratów pasujących do punktów: gdzie c i b są stałymi, a e jest podstawą naturalnego logarytmu. Następująca uwypuklająca linia wskazuje na malejącą ilość węgla 14 w obiekcie w miarę jego upływu. Warto zauważyć, że wartość kwadratowa R wynosi 0.990, co oznacza, że linia idealnie pasuje do danych. Moving Average trendline Ten trend uniemożliwia fluktuacje danych w celu bardziej wyraźnego przedstawienia wzoru lub tendencji. Średnia ruchoma używa określonej liczby punktów danych (ustawionych przez opcję Okres), średnie ich i używa średniej wartości jako punktu w linii. Na przykład, jeśli okres jest ustawiony na 2, średnia średnich dwóch pierwszych punktów danych jest używana jako pierwszy punkt w ruchomym średnim zakresie. Średnia sekund i trzeciego punktu danych jest używana jako drugi punkt w linii trendu itp. Średniometr ruchomy używa tego równania: liczba punktów w ruchomym średnim zakresie jest równa łącznej liczbie punktów w serii, minus numer podany w danym okresie. Na wykresie rozproszonym trend jest oparty na kolejności wartości x na wykresie. Aby uzyskać lepszy wynik, posortuj x wartości przed dodaniem średniej ruchomej. Poniższa ruchomą średnią linię pokazuje wzór liczby domów sprzedanych w okresie 26 tygodni. Średnia Średnia W tym przykładzie dowiesz się, jak obliczyć średnią ruchową serii czasowej w programie Excel. Średnia ruchoma służy do wyrównywania nieprawidłowości (szczytów i dolin) w celu łatwego rozpoznania trendów. 1. Po pierwsze, spójrz na naszą serię czasową. 2. Na karcie Dane kliknij pozycję Analiza danych. Uwaga: nie można znaleźć przycisku analizy danych Kliknij tutaj, aby załadować dodatek Analysis ToolPak. 3. Wybierz opcję Moving Average i kliknij przycisk OK. 4. Kliknąć w polu Zakres wejściowy i wybrać zakres B2: M2. 5. Kliknij w polu Interwał i wpisz 6. 6. Kliknij w polu Zakres wyjściowy i wybierz komórkę B3. 8. Wykres wykresu tych wartości. Objaśnienie: ponieważ ustawiamy 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 tendencję wzrostową. Excel nie może obliczyć średniej ruchomej dla pierwszych 5 punktów danych, ponieważ nie ma wystarczająco dużo poprzednich punktów danych. 9. Powtórz kroki od 2 do 8 dla przedziału 2 i przedziału 4. Podsumowanie: Im większy odstęp, tym więcej szczytów i dolin są wygładzone. Im mniejsze odstępy, tym dokładniejsze są średnie ruchome do rzeczywistych punktów danych. Zredukowanie prostego przenoszenia Jest to jeden z następujących trzech artykułów dotyczących analizy serii czasowej w programie Excel Przeanalizuj średnią ruchu Średnia średnia ruchoma jest techniką statystyczną używaną do wygładzania wymienia krótkoterminowe wahania w szeregu danych w celu łatwiejszego rozpoznania długoterminowych trendów lub cykli. Średnia ruchoma jest czasami określana jako średnia krocząca lub średnia bieżąca. Średnia ruchoma to seria liczb, z których każda reprezentuje średnią z przedziału określonej liczby poprzednich okresów. Im większy odstęp, tym bardziej wygładza się. Im krótszy odstęp, tym bardziej, że średnia ruchoma przypomina rzeczywistą serię danych. Średnie ruchome spełniają trzy następujące funkcje: Wygładzanie danych, co oznacza poprawienie dopasowania danych do linii. Zmniejszenie wpływu tymczasowej zmienności i losowego szumu. Podkreślając wartości odstające powyżej lub poniżej trendu. Średnia ruchoma jest jedną z najszerzej używanych technik statystycznych w przemyśle w celu określenia trendów danych. Na przykład menedżerowie ds. Sprzedaży zazwyczaj widują średnie ruchome dane dotyczące sprzedaży sprzed trzy miesięcy. W artykule porównaj się dwa miesiące, trzy miesiące i sześć miesięcy proste średnie ruchome tych samych danych dotyczących sprzedaży. Średnia ruchoma jest stosowana dość często w technicznej analizie danych finansowych, takich jak zwrot z akcji i ekonomia w celu zlokalizowania trendów w szeregach czasowych makroekonomicznych, takich jak zatrudnienie. Istnieje wiele odmian średniej ruchomej. Najczęściej stosowaną jest prosta średnia ruchoma, ważona średnia ruchoma i wykładnicza średnia ruchoma. Wykonywanie każdej z tych technik w programie Excel będzie szczegółowo opisane w oddzielnych artykułach w tym blogu. Oto krótki przegląd każdej z tych trzech technik. Prosta średnia ruchoma Każdy punkt w prostej średniej ruchomej jest średnią określonej liczby poprzednich okresów. Ten artykuł na blogu dostarczy szczegółowe wyjaśnienie implementacji tej techniki w programie Excel. Średnia ważona średnia ruchoma w ważonej średniej ruchomej również przedstawia średnią z określonej liczby poprzednich okresów. Ważona średnia ruchoma stosuje różne wagi do pewnych poprzednich okresów, dość często w ostatnich okresach ważono większą wagę. Odnośnik do innego artykułu z tego blogu, który zawiera szczegółowe wyjaśnienie implementacji tej techniki w programie Excel, jest następujący: Wyższe średnie ruchy w punktach średniej ruchomej stanowią również średnią z określonej liczby poprzednich okresów. Wyrównywanie wykładnicze stosuje współczynniki ważenia do poprzednich okresów, które zmniejszają się wykładniczo, nigdy nie osiągając zera. W efekcie wyrównywanie wykładnicze uwzględnia wszystkie poprzednie okresy zamiast wyznaczonej liczby poprzednich okresów, w których ważona średnia ruchoma. Łącze do innego artykułu z tego blogu, które zawiera szczegółowe wyjaśnienie implementacji tej techniki w programie Excel jest następujące: Poniżej przedstawiono trzyetapowy proces tworzenia prostej średniej ruchomej danych z serii czasowych w programie Excel Krok 1 8211 Wykres oryginalne dane w wykresie z serii czasowej Wykres liniowy jest najczęściej stosowanym wykresem programu Excel w celu analizy danych z serii czasowych. Przykład takiego wykresu programu Excel służącego do wykreślania 13 okresów sprzedaży przedstawia się następująco: Krok 2 8211 Utwórz średnią ruchu w programie Excel Excel zawiera narzędzie Ruchome Średnie w menu Analiza danych. Narzędzie Ruchome Średnie tworzy prostą średnią ruchu z serii danych. Okno dialogu "Ruch średnia" powinno zostać wypełnione w następujący sposób, aby utworzyć średnią ruchomą z poprzednich dwóch okresów danych dla każdego punktu danych. Wydajność 2-dniowej średniej ruchomej jest przedstawiona w następujący sposób wraz ze wzorami, które wykorzystano do obliczania wartości każdego punktu w średniej ruchomej. Krok 3 8211 Dodawanie serii średnich ruchów do wykresu Dane te należy teraz dodać do wykresu zawierającego pierwotną linię czasu danych sprzedaży. Dane zostaną po prostu dodane jako kolejne serie danych na wykresie. Aby to zrobić, kliknij prawym przyciskiem myszy dowolne miejsce na wykresie i pojawi się menu. Wybierz Dane, aby dodać nową serię danych. Ruchome serie średnie zostaną dodane, uzupełniając okno dialogowe Edytuj serie w następujący sposób: Wykres zawierający oryginalne serie danych i data8217s 2-interwałowa prosta średnia ruchoma jest przedstawiona w następujący sposób. Zauważ, że średnia ruchoma jest dosyć gładsza i surowe dane odchylone powyżej i poniżej linii trendu są znacznie bardziej widoczne. Ogólny trend jest teraz znacznie bardziej widoczny. Dla wykresu można utworzyć i umieścić na wykresie 3-interwałową średnią, stosując tę samą procedurę, jak w poniższym przykładzie: Warto zauważyć, że średnia prosta ruchoma 2-interwałów tworzy gładszą wykres niż średnia średniej ruchomej3-interwału. W tym przypadku średnia szybkość ruchoma 2-interwałowa może być bardziej pożądana niż średnica ruchowa 3-interwałów. Dla porównania średnia arytmetyczna przebiegu 6-przedziału będzie obliczana i dodawana do wykresu w taki sam sposób, jak następuje: Zgodnie z oczekiwaniami, średnia szybkość ruchoma w skali 6 jest znacznie płynniejsza niż średnie ruchome średnie ruchy 2 lub 3-przerwowe. Gładszy wykres bardziej zbliża się do prostej. Analiza dokładności dokładności Dokładność można określić jako dobro dopasowania. Dwa składniki dokładności prognozy są następujące: Prognoza Bias 8211 Skłonność prognozy jest stale wyższa lub niższa od rzeczywistych wartości szeregu czasowego. Prognozowanie jest sumą wszystkich błędów podzielonych przez liczbę okresów w następujący sposób: Pozytywne nastawienie wskazuje tendencję do niedociągnięcia. Negatywne nastawienie wskazuje na tendencję do nadmiernej prognozy. Bias nie mierzy dokładności, ponieważ błędy dodatnie i ujemne wzajemnie się wycofują. Prognoza Błąd 8211 Różnica między rzeczywistymi wartościami serii czasowej a prognozowanymi wartościami prognozy. Najczęstsze pomiary błędu prognozy są następujące: MAD 8211 Średnia odchylenie bezwzględne MAD oblicza średnią wartość bezwzględną błędu i oblicza następującą formułę: Średnie wartości błędów eliminują efekt anulowania dodatnich i ujemnych błędów. Im mniejsza jest MAD, tym lepszy jest model. MSE 8211 Średni błąd kwadratowy MSE jest popularną miarą błędu, która eliminuje efekt anulowania dodatnich i ujemnych błędów przez sumowanie kwadratów błędu o następującym wzorze: Duże wyrażenia błędów mają tendencję do przesadzania MSE, ponieważ wszystkie wyrażenia błędów są kwadratowane. RMSE (Root Square Mean) zmniejsza ten problem, biorąc pierwiastek kwadratowy MSE. MAPE 8211 Średni błąd procentu bezwzględnego MAPE eliminuje również efekt anulowania dodatnich i ujemnych błędów przez zsumowanie wartości bezwzględnych błędów. MAPE oblicza sumę procentów błędów o następującym wzorze: Podsumowując procent błędów, MAPE można porównać do modeli prognozowania, które wykorzystują różne skalę pomiaru. Obliczanie stronniczości, MAD, MSE, RMSE i MAPE w programie Excel W programie Excel Excel obliczy się prostą ruchliwą średnią tendencję, MAD, MSE, RMSE i MAPE w celu oceny prostego przemieszczania 2-przedziału, 3-przedziału i 6-rzędu średnia prognoza uzyskana w tym artykule i pokazana w następujący sposób: Pierwszym krokiem jest wyliczenie t. E t 2. E t, E t Y t-act. a następnie sumować je w następujący sposób: Bias, MAD, MSE, MAPE i RMSE można obliczyć w następujący sposób: Te same obliczenia są teraz wykonywane w celu obliczenia Bias, MAD, MSE, MAPE i RMSE dla 3-interwałowej prostej średniej ruchomej. Te same obliczenia są teraz wykonywane w celu obliczenia wartości Bias, MAD, MSE, MAPE i RMSE dla średniej ruchomej w skali 6-przerwy. Bias, MAD, MSE, MAPE i RMSE podsumowano w przedziałach 2-przedziałowych, 3-przedziałowych i 6-progowych średnich ruchomej w następujący sposób. 3-interwałowa prosta średnia ruchoma jest modelem najbardziej zbliżonym do rzeczywistych danych. 160 Główne tematy programu Excel Master Blog Tematy statystyczne i artykuły w każdym temacie
Comments
Post a Comment