Jak obliczyć średni błąd kwadratowy (MSE) w programie Microsoft Excel

Czasami zrozumienie, jak uzyskać MSE w Excelu, wydaje się dość przesadne, ale jest zaskakująco pomocne, jeśli chcesz porównać modele lub po prostu sprawdzić, jak dobrze sprawdzają się Twoje prognozy. Nie wiem dlaczego, ale te drobne zmiany w formułach mogą mieć ogromne znaczenie – zwłaszcza gdy masz do czynienia z rzeczywistymi danymi, a nie tylko liczbami na stronie. I hej, w niektórych konfiguracjach te sztuczki kalkulatora działają za pierwszym razem, a w innych trzeba powtórzyć kilka rzeczy. Bo oczywiście Excel czasami musi to utrudniać bardziej niż to konieczne.

Zasadniczo, jeśli próbujesz określić dokładność swoich prognoz – na przykład miesięcznych szacunków sprzedaży – chcesz obliczyć średnią kwadratową różnicę między oczekiwaniami a rzeczywistymi wynikami. W tym miejscu sprawdza się MSE (średni błąd kwadratowy).Jest on często używany do oceny, jak blisko linia regresji lub model są rzeczywiste dane.

Same formuły mogą wydawać się onieśmielające – mnóstwo sumowania, różnic podniesionych do kwadratu, średnich. Ale kiedy zrobisz to krok po kroku, nie jest tak źle. Oto, jak to zrobić w Excelu, na dwa główne sposoby, w zależności od tego, jak bardzo chcesz się pogubić w obliczeniach ręcznych lub szybkich funkcjach.

Jak obliczyć średni błąd kwadratowy w programie Excel

Metoda 1: Użycie funkcji SUMSQ (szybka i prosta)

To najszybsza droga, ale dość podatna na błędy, jeśli nie będziesz ostrożny z odwołaniami do komórek. Chodzi o to, żeby obliczyć różnicę między wartościami rzeczywistymi a przewidywanymi, podnieść te różnice do kwadratu, dodać wszystko, a następnie podzielić przez całkowitą liczbę obserwacji (n).Całkiem proste.

1. Utwórz nową kolumnę, powiedzmy Kolumnę D zatytułowaną „Różnica”.
2. W komórce D2 wpisz `=C2-B2` (zakładając, że C zawiera wartości rzeczywiste, a B przewidywane).Przeciągnij w dół, aby wypełnić wszystkie punkty danych.
3. Wybierz komórkę i wpisz: =SUMSQ(D2:D13)/COUNT(D2:D13). Zastąp D13 ostatnim wierszem i upewnij się, że zakres pasuje do Twoich danych.

Ta formuła sumuje kwadraty różnic i dzieli je przez liczbę całkowitą, dając wynik MSE. A co najlepsze? Wykonujesz kwadratowanie w funkcji SUMA. KWADRATÓW, więc nie ma potrzeby ręcznego kwadratowania. Uważaj tylko na błędy zakresu lub pominięcie wszystkich danych.

Metoda 2: Ręczne kwadratowanie i uśrednianie (większa kontrola, więcej kroków)

To podejście jest bardziej przejrzyste – ręcznie podnosisz różnice do kwadratu, a następnie wyciągasz średnią. Przydatne, jeśli chcesz sprawdzić swoją pracę lub rozwiązać problem.

1. Zakładając, że różnice znajdują się już w kolumnie D, nazwij kolumnę E „Kwadrat różnic”.
2. W komórce E2 wpisz `=D2^2`.Przeciągnij w dół, aby wyświetlić wszystkie wiersze.
3. W pustej komórce wpisz: =AVERAGE(E2:E13). Ponownie dostosuj zakres w razie potrzeby.

To wyrzuci ten sam kod MSE. Szczerze mówiąc, nie wiem, dlaczego nie skorzystasz z pierwszej metody, chyba że debugujesz lub chcesz zobaczyć każdy krok rozpisany na czynniki pierwsze. Czasami pocieszające jest widzieć te kwadratowe różnice, które na ciebie patrzą.

Tak czy inaczej, w niektórych konfiguracjach te formuły mogą wymagać pewnych modyfikacji – na przykład dodania odwołań bezwzględnych, poprawienia zakresów w przypadku przesunięcia danych lub ponownego uruchomienia obliczeń po aktualizacji danych. Upewnij się tylko, że zakresy się zgadzają, w przeciwnym razie otrzymasz dziwną liczbę, która nie spełnia oczekiwań.

A co najważniejsze, możliwości statystyczne Excela są całkiem solidne, gdy już opanuje się te podstawy. Miejmy nadzieję, że to skróci czas pracy o kilka godzin, a przynajmniej pozwoli uniknąć szarpania się z włosami podczas próby oceny błędów prognoz. Powodzenia i pamiętajcie – czasami samo „na oko” może wystarczyć, ale zawsze lepiej mieć dane liczbowe, które to potwierdzą.

Streszczenie

• Oblicz różnice między danymi rzeczywistymi i prognozowanymi w nowej kolumnie.
• Użyj funkcji SUMSQ w połączeniu z funkcją COUNT lub ręcznie podnieś różnice do kwadratu i uśrednij je.
• Dokładnie sprawdź zakresy i formuły, aby uniknąć błędów.

Podsumowanie

Korzystanie z MSE w Excelu może początkowo wydawać się nieco żmudne, ale kiedy już się w tym połapiesz, staje się proste. Niezależnie od tego, czy śledzisz dokładność modelu, czy po prostu pasjonujesz się prognozami, te metody powinny wystarczyć. Wystarczy pobawić się formułami, porównać wyniki, a przyzwyczaisz się. Trzymam kciuki, żeby to pomogło i życzę udanej analizy.

---

---