Формула сглаживания данных методом скользящей средней в Excel

Сглаживание данных заключается в элиминации или в уменьшении отдельных значений содержащие статистические аномалии. Скользящая средняя может быть использована для уменьшения влияния отдельных необычно больших или малых значений на результат вычисления. Благодаря скользящей средней вырисовывается весьма информативный образ общего тренда тенденции для всех статистических значений. Средняя скользящая особенно полезна для фильтрации отдельных ошибочных значений.

Как найти скользящую среднюю в Excel

Ниже на рисунке представлена часть списка результатов матча гольфа. Каждый, кто играет в этою игру знает – что ошибочные результаты могут быть перенесены из годно раунда в следующий.

На следующем рисунке изображен график результатов во времени:

Сложно определить тренд изменения игры на основе значений данного графика по причине резких взлетов и падений кривой на графике.



Как построить скользящую среднюю на графике в Excel

Допустим, что нам необходимо построить график, на котором благодаря сглаживанию резких взлетов и падений кривой будет наглядно отображаться изменение тренда результатов матчей. С целью сглаживания графика необходимо рассчитать скользящую среднюю для результатов и поместить ее на график.

Ниже на рисунке изображен график, на котором расположена кривая скользящей средней. Она основана на основе значений в третьем столбце таблицы, где находятся формулы ее вычисления. Исходные показатели матчей отображены тонкой синей линией:

Как видно на рисунке благодаря скользящей средней мы можем не только определить текущий нисходящий тренд, но и наблюдать его относительно низкую динамику в пределах 5-ти пунктов по оси Y.

Для создания нового набора данных содержащих скользящее среднее значение для всех исходных результатов использована следующая формула:

Данная формула использует несколько функций Excel. Сначала используется функция ЕСЛИ, которая для нескольких первых результатов возвращает ошибку #Н/Д! Функция строка без аргументов возвращает номер строки листа для текущей ячейки. Вычисление средней скользящей невозможно если нет достаточного количества данных, поэтому для первых девяти значений формула возвращает ошибку #Н/Д!

Примечание. Ошибка #Н/Д! не отображается на графике. При построении графиков если Excel натыкается на ошибки в начале данных он ничего не рисует и для этого можно использовать функцию НД, которая возвращает ошибку недоступного значения #Н/Д! Но в середине кривой он просто игнорирует ошибки образуя неразрывную линию. Чтобы ее сделать разрывной в нужных местах нужно вручную удалить в соответственных местах значения или макросом. С мощу формулы решить данную задачу не получиться, так как любая формула в любом случае возвращает какой-то результат. И даже пустая строка ("") не является пустым значением для ячейки. На графике она будет отображена линией, как и при значении 0.

Для вычисления очередных результатов была использована функция СРЗНАЧ, которая возвращает среднее арифметическое число для предыдущих 10-ти результатов игры. Данная функция может содержать максимально 255 аргументов, однако исходные значения в данном примере образуют целый диапазон данных, а значит достаточно заполнить только лишь один аргумент, указав в нем ссылку на исходный диапазон ячеек.

Функция СМЕЩ каждый раз возвращает выбранный диапазон ячеек со смещением на 10 ячеек от исходного диапазона. Данная функция содержит следующие аргументы:

1. Ссылка – адрес ячейки, с учетом которой функция начинает свое вычисление для смещения.
2. Смещение по строкам – указывается число строк на расстоянии между начальной ячейкой, от которой будет начинаться возвращаемый диапазон. Отрицательное число в данном аргументе значит, что возвращаемый диапазон находиться выше начальной ячейки, а положительное – ниже.
3. Смещение по столбцам – указывается число столбцов на расстоянии между начальной ячейкой, от которой будет начинаться возвращаемый диапазон. Отрицательное число в данном аргументе значит, что возвращаемый диапазон находиться по левой стороне от начальной ячейки, а положительное – по правой.
4. Высота – количество строк в возвращаемом диапазоне ячеек (указывается только положительное число).
5. Ширина - количество столбцов в возвращаемом диапазоне ячеек (указывается только положительное числовое значение).

Если в первом аргументе «Ссылка» для функции СМЕЩ будет указан адрес на ячейку C11, тогда это будет место, от которого функция начнет отсчет количество строк и столбцов листа Excel. Отрицательное число -9 указано как второй аргумент «Смещение по строкам» приведет к тому, что функция отсчитает вверх 9 строк и дойдет к ячейке C2. Число 0 в третьем аргументе «Смещение по столбцам» значит, что функция СМЕЩ не будет отсчитывать столбцов. При учете первых двух аргументов функция вычислить, что начало возвращаемого диапазона ячеек начинается от C2.

4-й аргумент «Высота» содержит число 10 – это означит, что возвращаемый диапазон ячеек имеет высоту в 10 строк листа, то есть охватывает ячейки С2:С11. 5-й аргумент «Ширина» имеет значение 1, то есть возвращаемый диапазон ячеек будет состоять из 1-го столбца. В результате своих вычислений функция СМЕЩ возвращает ссылку на новый диапазон ячеек С2:С11, который передается в качестве аргумента для функции СРЗНАЧ. В процессе копирования формулы в очередные нижние ячейки, предыдущие результаты 10-ти матчей усредняются.

Таким образом была вычислена линия скользящей средней для данного графика результатов матчей. Скользящая средняя, которая рассчитывается на основе среднего арифметического значения для 10 предыдущих результатов матчей сделала график более читабельным. Теперь хорошо прослеживается направление развития тренда на графике розыгрышей матчей игры в гольф.

Количество значений, которые учитывается в средней скользящей может быть разным – все зависит от количества исходных данных и поставленных задачах. Можно усреднять значения за последние 12 месяцев или 5 лет либо же за любой другой период соответственный исходным данным.