Расчет скользящей средней в Excel: формулы и графики
В этом кратком руководстве вы узнаете, как быстро рассчитать простое скользящее среднее в Excel, какие функции использовать для получения скользящего среднего за последние N дней, недель, месяцев или лет и как добавить линию тренда скользящего среднего в Excel. диаграмма.
В нескольких недавних статьях мы подробно рассмотрели расчет среднего значения в Excel. Если вы следите за нашим блогом, вы уже знаете, как рассчитать нормальное среднее значение и какие функции использовать для нахождения средневзвешенного значения. В сегодняшнем уроке мы обсудим два основных метода расчета скользящего среднего в Excel.
Что такое скользящая средняя?
Вообще говоря, скользящее среднее (также называемое скользящая средняя, скользящее среднее или же движущееся средство) можно определить как серию средних значений для разных подмножеств одного и того же набора данных.
Он часто используется в статистике, экономическом прогнозировании с учетом сезонных колебаний и прогнозировании погоды для понимания основных тенденций. В биржевой торговле скользящая средняя — это индикатор, который показывает среднюю стоимость ценной бумаги за определенный период времени. В бизнесе принято вычислять скользящее среднее продаж за последние 3 месяца, чтобы определить недавнюю тенденцию.
Например, скользящее среднее трехмесячных температур можно рассчитать, взяв среднее значение температуры с января по март, затем среднее значение температуры с февраля по апрель, затем с марта по май и так далее.
Существуют различные типы скользящего среднего, такие как простое (также известное как арифметическое), экспоненциальное, переменное, треугольное и взвешенное. В этом уроке мы рассмотрим наиболее часто используемую простую скользящую среднюю.
Вычисление простой скользящей средней в Excel
В целом, есть два способа получить простую скользящую среднюю в Excel — с помощью формул и опций линии тренда. В следующих примерах демонстрируются оба метода.
Рассчитать скользящую среднюю за определенный период времени
Простое скользящее среднее можно рассчитать в кратчайшие сроки с помощью функции СРЗНАЧ. Предположим, у вас есть список среднемесячных температур в столбце B, и вы хотите найти скользящее среднее значение за 3 месяца (как показано на рисунке выше).
Напишите обычную формулу СРЗНАЧ для первых 3 значений и введите ее в строку, соответствующую 3-му значению сверху (ячейка C4 в этом примере), а затем скопируйте формулу вниз в другие ячейки столбца:
=СРЕДНЕЕ(B2:B4)
Вы можете исправить столбец с абсолютной ссылкой (например, $B2), если хотите, но обязательно используйте относительные ссылки на строки (без знака $), чтобы формула правильно настраивалась для других ячеек.
Помня, что среднее значение вычисляется путем сложения значений и последующего деления суммы на количество значений, которые необходимо усреднить, вы можете проверить результат, используя формулу СУММ:
=СУММ(B2:B4)/3
Получить скользящее среднее за последние N дней/недель/месяцев/лет в столбце
Предположим, у вас есть список данных, например данные о продажах или биржевые котировки, и вы хотите узнать среднее значение за последние 3 месяца в любой момент времени. Для этого вам нужна формула, которая будет пересчитывать среднее значение, как только вы введете значение за следующий месяц. Какая функция Excel способна это сделать? Старый добрый СРЗНАЧ в сочетании со СМЕЩЕНИЕМ и СЧЕТОМ.
=СРЕДНЕЕ(СМЕЩЕНИЕ(первая ячейкаСЧИТАТЬ(Весь спектр)-Н,0,Н,1))
Где Н это количество последних дней/недель/месяцев/лет, которые необходимо включить в среднее значение.
Не знаете, как использовать эту формулу скользящего среднего на листах Excel? Следующий пример прояснит ситуацию.
Предполагая, что значения для усреднения находятся в столбце B, начиная со строки 2, формула будет выглядеть следующим образом:
=СРЗНАЧ(СМЕЩЕНИЕ(B2,СЧЁТ(B2:B100)-3,0,3,1))
А теперь давайте попробуем понять, что на самом деле делает эта формула скользящего среднего Excel.
- Функция COUNT COUNT(B2:B100) подсчитывает, сколько значений уже введено в столбец B. Мы начинаем подсчет в B2, поскольку строка 1 является заголовком столбца.
- Функция СМЕЩ берет ячейку B2 (1-й аргумент) в качестве отправной точки и смещает счетчик (значение, возвращаемое функцией СЧЁТ), перемещаясь на 3 строки вверх (-3 во 2-м аргументе). В результате он возвращает сумму значений в диапазоне, состоящем из 3 строк (3 в 4-м аргументе) и 1 столбца (1 в последнем аргументе), что соответствует последним 3 месяцам, которые нам нужны.
- Наконец, возвращенная сумма передается функции AVERAGE для расчета скользящего среднего.
Кончик. Если вы работаете с постоянно обновляемыми рабочими листами, в которые в будущем могут быть добавлены новые строки, обязательно предоставьте функции COUNT достаточное количество строк для размещения потенциальных новых записей. Это не проблема, если вы включаете больше строк, чем на самом деле необходимо, если у вас есть первая ячейка правильно, функция COUNT в любом случае отбросит все пустые строки.
Как вы, наверное, заметили, таблица в этом примере содержит данные только за 12 месяцев, а диапазон B2:B100 передается в COUNT, просто на всякий случай 🙂
Найти скользящее среднее для последних N значений подряд
Если вы хотите рассчитать скользящее среднее за последние N дней, месяцев, лет и т. д. в той же строке, вы можете настроить формулу смещения следующим образом:
=СРЕДНЕЕ(СМЕЩЕНИЕ(первая ячейка,0,СЧЕТ(диапазон)-Н,1,Н,))
Предположим, что B2 является первым числом в строке, и вы хотите включить в среднее значение последние 3 числа, формула примет следующий вид:
=СРЗНАЧ(СМЕЩЕНИЕ(B2,0,СЧЁТ(B2:N2)-3,1,3))
Создание графика скользящих средних в Excel
Если вы уже создали диаграмму для своих данных, добавление линии тренда скользящего среднего значения для этой диаграммы занимает несколько секунд. Для этого мы собираемся использовать функцию Excel Trendline, и подробные шаги приведены ниже.
Для этого примера я создал двухмерную столбчатую диаграмму (Вкладка «Вставка» > Группа графиков) для наших данных о продажах:
А теперь мы хотим «визуализировать» скользящую среднюю за 3 месяца.
- В Excel 2013 выберите диаграмму, перейдите к Вкладка “Дизайн” > Макеты диаграмм группу и нажмите Добавить элемент диаграммы > линия тренда > Дополнительные параметры линии тренда…
В Excel 2010 и Excel 2007 перейдите к Макет > линия тренда > Дополнительные параметры линии тренда.
Кончик. Если вам не нужно указывать детали, такие как интервал скользящего среднего или имена, вы можете нажать Дизайн > Добавить элемент диаграммы > линия тренда > Скользящее среднее для немедленного результата.
- Формат линии тренда Панель откроется в правой части листа в Excel 2013, а соответствующее диалоговое окно появится в Excel 2010 и 2007.
На Формат линии тренда панели, щелкните значок «Параметры линии тренда», выберите параметр «Скользящее среднее» и укажите интервал скользящего среднего в поле «Период»:
- Закройте панель «Линия тренда», и вы увидите линию тренда скользящей средней, добавленную на ваш график:
Чтобы улучшить чат, вы можете переключиться на Заполнение и линия или же Последствия вкладка на Формат линии тренда панель и поэкспериментируйте с различными параметрами, такими как тип линии, цвет, ширина и т. д.
Для мощного анализа данных вы можете добавить несколько линий тренда скользящих средних с разными временными интервалами, чтобы увидеть, как развивается тренд. На следующем снимке экрана показаны 2-месячные (зеленые) и 3-месячные (кирпично-красные) линии тренда скользящих средних:
Вот и все, что касается расчета скользящего среднего в Excel. Образец рабочего листа с формулами скользящего среднего и линией тренда доступен для скачивания в конце этого поста. Я благодарю вас за чтение и с нетерпением жду встречи с вами на следующей неделе!
Практическая рабочая тетрадь
Расчет скользящей средней — примеры (файл .xlsx)
