Wygładzanie danych na wykresie Excela za pomocą stałej średniej ruchomej działa, gdy masz doświadczenie z danymi i ich zmiennością. Ale jeśli dane się zmieniają lub są nowe, Ty lub kierownictwo możecie chcieć poeksperymentować z inną liczbą okresów w wygładzaniu średniej.

W tym przykładzie formuła OFFSET jest używana do tworzenia średniej w dynamicznym zakresie. Wpisujemy liczbę miesięcy, które chcemy użyć jako okres wygładzania, a formuły AVERAGE i OFFSET obliczają, ile komórek ma zostać uśrednionych.

Zanim uczynimy AVERAGE dynamiczną, zacznijmy od zrozumienia, jak działa OFFSET. Na poniższym rysunku komórka G26 zawiera formułę wygładzania danych w wierszu 20,

=AVERAGE(OFFSET(G20,0,0,1,-3))

Ta formuła jest krokiem pośrednim, który pomoże Ci zrozumieć, jak działa formuła dynamiczna. Funkcja OFFSET zwraca zakres, na którym będzie działać funkcja AVERAGE. Atrybutami OFFSET są,

OFFSET(referencja,wiersze,kolumny,,)

W tym przykładzie zakres, który oblicza OFFSET, zaczyna się od G20 i ma zerowe przesunięcie wiersza lub kolumny. Innymi słowy, lewy górny róg zakresu obliczanego przez OFFSET to właśnie G20. Nie będzie on przesunięty lub przesunięty o żaden wiersz lub kolumnę. Obliczony zakres będzie miał wysokość 1 wiersza i będzie zawierał bieżącą komórkę G26 oraz dwie kolejne po lewej stronie (to jest -3). Tak więc ten obliczony zakres będzie G20:E20.

Kopiuj tę formułę w G26 w prawo i zobaczysz, że uśrednia ona komórkę danych znajdującą się nad nią w wierszu 20 i dwie poprzednie komórki po lewej stronie.

Więc, oto sztuczka, która sprawi, że będzie to dynamiczna i średnia w wybranym zakresie. Co się stanie, jeśli zastąpisz odwołanie do komórki -3 w formule?

Na rysunku poniżej formuła w komórce G26 została zmodyfikowana tak, że jest określona przez liczbę w komórce E24,

AVERAGE(OFFSET(G20,0,0,1,-$E$24))

$E$24 jest komórką zacienioną na zielono. Liczba wpisana w komórce E24 określi szerokość komórek używanych do uśredniania.

W tym momencie możesz użyć spinnera lub rozwijanego menu, aby dać użytkownikowi wybór, jak szeroka ma być średnia wygładzania. Wynik ich wyboru powinien trafić do komórki E24.

Wygładzanie danych wykresu Excel ze stałym lub dynamicznym wygładzaniem

Jest jeden problem z tym układem. Jeśli użytkownik wpisze w E24 większą liczbę niż jest komórek po lewej stronie to OFFSET będzie zawierał puste komórki i etykietę. To da nieprawidłową średnią. Jeśli E24 jest tak duże, że OFFSET wychodzi poza arkusz, to AVERAGE daje błąd. Co robić? Potrzebujemy rozwiązania, które nie tylko zatrzyma błąd, jak ISERROR, potrzebujemy takiego, które zatrzyma błędne odpowiedzi.

Na tym następnym rysunku formuła w komórce G26 została dostosowana. Wiersz 25 ma serię liczb, które są granicami dla ważnych zakresów po lewej stronie. Nowa formuła w komórce G26 patrzy na wiersz 25 i jeśli liczba w komórce E24 jest większa niż limit w wierszu 25, to powstaje NA(). Ta nowa formuła korygująca błędy w komórce G26 ma postać,

=IF($E$24>G25,NA(),AVERAGE(OFFSET(G20,0,0,1,-$E$24)))

Gdy wykres Excela odwołuje się do NA() w komórce, nic nie jest wykreślane. To zapobiega obniżeniu się linii wygładzania do zera.

Problemy z wygładzaniem danych i jak je obejść

Teraz możesz pozwolić użytkownikowi wypróbować dowolny okres wygładzania. Jednak (wydaje się, że zawsze jest jakieś „jednak” lub „ale, w tym przypadku”) jest problem z wygładzaniem danych. Wygładzanie usuwa zmienność, ale oznacza to również, że nie zobaczysz zmian w kierunku, punktów zwrotnych lub nowych trendów aż do około połowy okresu wygładzania. Jesteś za krzywą.

Najlepszym rozwiązaniem z obu światów jest użycie funkcji wygładzania średniej ważonej. W ten sposób można nadać większą wagę danym krótkoterminowym, dzięki czemu można zobaczyć trendy, ale można wygładzić w oparciu o starsze dane, aby wyeliminować niektóre z niestabilności. Brzmi jak dobry przypadek dla innego artykułu, Wygładzanie danych wykresu Excela za pomocą średniej ważonej.

Pobierz plik przykładowy dla wygładzania danych wykresu Excela za pomocą wygładzania ruchomego lub dynamicznego

Powiązane artykuły,

• Wygładzanie danych wykresu Excela za pomocą średniej ruchomej
• Wygładzanie danych wykresu Excela za pomocą średniej ważonej

.