Как рассчитать скользящее среднее в Excel?

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

Что такое скользящее среднее?

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

Этот метод динамичен: он постоянно обновляется по мере поступления новых данных, что делает его особенно эффективным для сглаживания краткосрочных колебаний или аномалий. Например, возьмём ряд из 10 чисел. Если выбрать размер подмножества 3, вычисление скользящего среднего начнётся со среднего значения первых трёх чисел. Затем окно сдвигается на одну позицию вперёд: первое число исключается, а четвёртое добавляется, формируя новую группу для следующего расчёта среднего. Такой пошаговый сдвиг и усреднение продолжаются до конца ряда.

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

Расчёт скользящего среднего в Excel

В этом разделе мы рассмотрим два эффективных способа расчёта скользящего среднего в Excel. Пошагово проведём вас через каждый из них, чтобы вы могли уверенно применять эти методы в повседневных задачах анализа данных.

Расчёт скользящего среднего в Excel с помощью функции СРЗНАЧ

Функция «СРЗНАЧ» в Excel — это простой и эффективный способ рассчитать скользящее среднее. Допустим, у вас есть таблица с данными за 8 периодов, и вы хотите получить скользящее среднее за каждые 3 последовательных периода. Вот пошаговая инструкция:

1. Щёлкните по ячейке, в которую нужно поместить первое значение скользящего среднего. В нашем примере это ячейка C4, поскольку расчёт скользящего среднего ведётся с периодом 3.
2. Введите формулу СРЗНАЧ:

   =AVERAGE(B2:B4)

   Совет: Эта формула вычисляет среднее значение первых трёх точек данных (B2, B3, B4).
3. Щёлкните ячейку C4 и перетащите маркер заполнения вниз до ячейки, где должно отобразиться последнее значение скользящего среднего.

   

Расчёт скользящего среднего в Excel с помощью инструмента Анализ данных

Команда «Анализ данных» в Excel предлагает набор инструментов для анализа данных, включая «Скользящее среднее», с помощью которого можно легко рассчитать среднее значение за заданный диапазон данных и построить диаграмму скользящего среднего.

1. Выберите «Данные» → «Анализ данных».

   

2. В диалоговом окне «Анализ данных» выберите «Скользящее среднее» и нажмите «ОК».

   

3. В появившемся диалоговом окне «Скользящее среднее» выполните следующие действия:
   1. Входной диапазон: укажите диапазон данных для расчёта скользящих средних, например B2:B9.
   2. Интервал: укажите количество точек данных для расчёта каждого значения скользящего среднего. В данном случае введите 3.
   3. Область размещения списка: Укажите, где отображать результаты скользящих средних — например, диапазон C2:C9.
   4. Вывод диаграммы: отметьте этот параметр, если нужно графическое отображение.
   5. Нажмите «ОК».

      

Результат

После нажатия кнопки «ОК» Excel выведет значения скользящего среднего в указанную область размещения списка, а при включённой опции «Вывод графика» — также создаст соответствующую диаграмму.

Добавление линии скользящего среднего к существующей диаграмме

Если на вашем листе Excel уже есть диаграмма, как показано ниже, вы можете без труда добавить к ней линию скользящего среднего для анализа тенденций.

1. Щелкните диаграмму, на которую вы хотите добавить линию тренда.
2. Выберите «Конструктор диаграмм» (или просто «Конструктор» в более ранних версиях) → «Элементы диаграммы» → «Линия тренда» → «Другие параметры линии тренда».
   Совет: если стандартное значение «скользящее среднее за 2 периода» подходит для ваших задач, сразу выберите «Конструктор диаграмм» → «Элементы диаграммы» → «Линия тренда» → «Скользящее среднее». В этом случае на диаграмме автоматически появится линия тренда с меткой «2 пер. скольз. ср.».
3. Когда справа от листа откроется область «Формат линии тренда»:
   1. Выберите параметр «Скользящее среднее» и задайте интервал в поле «Период».
   2. (Необязательно) Чтобы изменить имя линии тренда, выберите «Другое» и укажите желаемое название.

      

Результат

После выполнения этих шагов на вашу диаграмму будет добавлена линия скользящего среднего, как показано ниже.

Выше представлено всё необходимое содержание, связанное со скользящим средним в Excel. Надеемся, что это руководство оказалось для вас полезным! Если хотите освоить ещё больше советов и приёмов работы в Excel, нажмите здесь, чтобы получить доступ к нашей обширной коллекции из нескольких тысяч обучающих материалов.

Видео: Расчёт скользящего среднего в Excel

Связанные статьи

• Как рассчитать средневзвешенное значение в Excel?
• Как рассчитать среднегодовой или сложный годовой темп роста в Excel?
• Как вычислить среднее значение диапазона с округлением в Excel?