Резюме
Чтобы вычислить скользящее или скользящее среднее, вы можете использовать простую формулу, основанную на функции СРЕДНЕЕ с относительными ссылками. В показанном примере формула E7 имеет следующий вид:
=AVERAGE(C5:C7)
Когда формула копируется, она вычисляет 3-дневное скользящее среднее на основе стоимости продаж за текущий день и два предыдущих дня.
Ниже представлен более гибкий вариант, основанный на функции СМЕЩЕНИЕ, которая обрабатывает переменные периоды.
О скользящих средних
Скользящее среднее (также называемое скользящим средним) - это среднее значение, основанное на подмножествах данных с заданными интервалами. Вычисление среднего значения через определенные интервалы сглаживает данные за счет уменьшения влияния случайных колебаний. Это упрощает просмотр общих тенденций, особенно на графике. Чем больше интервал, используемый для вычисления скользящего среднего, тем больше происходит сглаживание, поскольку в каждое вычисленное среднее включается больше точек данных.
Объяснение
Все формулы, показанные в примере, используют функцию СРЕДНЕЕ с относительной ссылкой, установленной для каждого конкретного интервала. 3-дневная скользящая средняя в E7 рассчитывается путем подачи СРЕДНЕГО диапазона, который включает текущий день и два предыдущих дня следующим образом:
=AVERAGE(C5:C7) // 3-day average
Аналогичным образом рассчитываются 5-дневные и 7-дневные средние значения. В каждом случае диапазон, предоставляемый для СРЕДНЕГО, расширяется, чтобы включить необходимое количество дней:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Во всех формулах используется относительная ссылка на диапазон, передаваемый в функцию СРЕДНЕЕ. Поскольку формулы копируются в столбец, диапазон изменяется в каждой строке, чтобы включить значения, необходимые для каждого среднего.
Когда значения нанесены на линейную диаграмму, эффект сглаживания очевиден:
Недостаточные данные
Если вы запустите формулы в первой строке таблицы, в первых нескольких формулах не будет достаточно данных для расчета полного среднего, потому что диапазон будет расширяться над первой строкой данных:
Это может быть или не быть проблемой, в зависимости от структуры рабочего листа и от того, важно ли, чтобы все средние значения основывались на одном и том же количестве значений. Функция AVERAGE автоматически игнорирует текстовые значения и пустые ячейки, поэтому она продолжит вычислять среднее значение с меньшим количеством значений. Поэтому он «работает» в E5 и E6.
Один из способов четко указать на недостаточность данных - это проверить текущий номер строки и прервать выполнение с помощью #NA, если имеется менее n значений. Например, для 3-дневного среднего вы можете использовать:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Первая часть формулы просто генерирует "нормализованный" номер строки, начиная с 1:
ROW()-ROW($C$5)+1 // relative row number
В строке 5 результат 1, в строке 6 результат 2 и так далее.
Если номер текущей строки меньше 3, формула возвращает значение # N / A. В противном случае формула, как и раньше, возвращает скользящее среднее. Это имитирует поведение версии Moving Average из пакета Analysis Toolpak, которая выводит # N / A, пока не будет достигнут первый полный период.
Однако по мере увеличения количества периодов у вас в конечном итоге закончатся строки над данными, и вы не сможете ввести требуемый диапазон в СРЕДНЕМ. Например, вы не можете установить скользящее 7-дневное среднее с помощью таблицы, как показано, поскольку вы не можете ввести диапазон, который простирается на 6 строк выше C5.
Переменные периоды со смещением
Более гибкий способ расчета скользящей средней - функция СМЕЩЕНИЕ. СМЕЩЕНИЕ может создавать динамический диапазон, что означает, что мы можем установить формулу, в которой количество периодов является переменным. Общая форма:
=AVERAGE(OFFSET(A1,0,0,-n,1))
где n - количество периодов, включаемых в каждое среднее значение. Как и выше, OFFSET возвращает диапазон, который передается в функцию AVERAGE. Ниже вы можете увидеть эту формулу в действии, где «n» - это именованный диапазон E2. Начиная с ячейки C5, OFFSET создает диапазон, который простирается до предыдущих строк. Это достигается за счет использования высоты, равной отрицательному n. Когда E5 изменяется на другое число, скользящее среднее пересчитывается для всех строк:
Копируемая формула в E5:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Как и в исходной формуле, приведенной выше, версия со смещением также будет иметь проблему недостаточного количества данных в первых нескольких строках, в зависимости от того, сколько периодов указано в E5.
В показанном примере средние значения вычисляются успешно, потому что функция AVERAGE автоматически игнорирует текстовые значения и пустые ячейки, а других числовых значений выше C5 нет. Таким образом, хотя диапазон, переданный в AVERAGE в E5, равен C1: C5, есть только одно значение для усреднения, 100. Однако по мере увеличения периодов OFFSET будет продолжать создавать диапазон, который простирается выше начала данных, в конечном итоге достигая вверху листа и возвращает ошибку #REF.
Одно из решений - «ограничить» размер диапазона количеством доступных точек данных. Это можно сделать с помощью функции MIN, чтобы ограничить число, используемое для высоты, как показано ниже:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Это выглядит довольно устрашающе, но на самом деле довольно просто. Мы ограничиваем высоту, передаваемую в OFFSET с помощью функции MIN:
MIN(ROW()-ROW($C$5)+1,n)
Внутри MIN первое значение - это относительный номер строки, вычисляемый с помощью:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Второе значение, присвоенное MIN, - это количество периодов n. Когда относительный номер строки меньше n, МИН возвращает текущий номер строки как СМЕЩЕНИЕ для высоты. Когда номер строки больше n, MIN возвращает n. Другими словами, MIN просто возвращает меньшее из двух значений.
Приятной особенностью опции OFFSET является то, что n можно легко изменить. Если мы изменим n на 7 и построим график результатов, мы получим такую диаграмму:
Примечание. Особенность приведенных выше формул СМЕЩЕНИЯ заключается в том, что они не будут работать в Google Таблицах, потому что функция СМЕЩЕНИЕ в Таблицах не допускает отрицательного значения для высоты или ширины. В прилагаемой таблице есть формулы обхода для таблиц Google.
