Метод к средних excel
Обновлено: 08.07.2024
Практическое моделирование экономических ситуаций подразумевает разработку прогнозов. С помощью средств Excel можно реализовать такие эффективные способы прогнозирования, как: экспоненциальное сглаживание, построение регрессий, скользящее среднее. Рассмотрим подробнее использование метода скользящего среднего.
Использование скользящих средних в Excel
Метод скользящей средней – один из эмпирических методов для сглаживания и прогнозирования временных рядов. Суть: абсолютные значения ряда динамики меняются на средние арифметические значения в определенные интервалы. Выбор интервалов осуществляется способом скольжения: первые уровни постепенно убираются, последующие – включаются. В результате получается сглаженный динамический ряд значений, позволяющий четко проследить тенденцию изменений исследуемого параметра.
Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.
Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.
Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.
Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.
- По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. Формула скользящей средней в Excel. Используя маркер автозаполнения, копируем формулу на диапазон ячеек С6:С14.
- Аналогично строим ряд значений трехмесячного скользящего среднего. Формула:
- По такому же принципу формируем ряд значений четырехмесячного скользящего среднего.
- Построим график заданного временного ряда и рассчитанные относительно его значений прогнозы по данному методу. На рисунке видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. Ведь расчеты базировались на данных предыдущих наблюдений.
- Рассчитаем абсолютные, относительные и средние квадратичные отклонения по сглаженным временным рядам. Абсолютные отклонения:
Средние квадратичные отклонения:
При расчете отклонений брали одинаковое число наблюдений. Это необходимо для того, чтобы провести сравнительный анализ погрешностей.
После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).
Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Применение надстройки «Пакет анализа»
Для примера возьмем ту же задачу.
На вкладке «Данные» находим команду «Анализ данных». В открывшемся диалоговом окне выбираем «Скользящее среднее»:
Установив флажок в поле «Стандартные погрешности», мы автоматически добавляем в таблицу столбец со статистической оценкой погрешности.
Точно так же находим скользящее среднее по трем месяцам. Меняется только интервал (3) и выходной диапазон.
Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Составлять прогнозы по методу скользящего среднего просто и эффективно. Инструмент точно отражает изменения основных параметров предыдущего периода. Но выйти за пределы известных данных нельзя. Поэтому для долгосрочного прогнозирования применяются другие способы.
Сам метод скользящего среднего рассмотрен в статье Скользящее среднее в MS EXCEL, в которой показано как для этого использовать инструмент MS EXCEL Пакет анализа, а также линию тренда и формулы.
В этой статье рассмотрим не сам метод сглаживания, а его применение для прогнозирования. Как было сказано во вводной статье про прогнозирование, метод прогнозирования подбирается в соответствии с процессом, который генерирует значения временного ряда. Поэтому в файле примера используется как стационарные процессы, будем называть их постоянными, т.к. у них среднее и дисперсия постоянные (хотя фактически это белый шум со смещенным средним), и растущий тренд. Для оценки точности прогнозирования рассчитываются ошибки модели, строится интервал прогнозирования (на самом деле не интервал прогнозирования, а некий доверительный интервал на основе вычисленной ошибки). Так же оценивается адекватность модели.
Примечание: Конечно, прогнозировать процессы типа белого шума, бесперспективное занятие, но, во-первых в файле примера демонстрируются характеристики этого процесса (строится диаграмма рассеяния, функция автокорреляции, диаграмма разброса ошибок и пр.), а во-вторых таблицу с исходными значениями можно заменить и все характеристики будут пересчитаны в файле примера автоматически.
Для построения рядов можно использовать диаграмму типа График или Точечная. Выберем последний тип – Точечная (ниже будет пояснено почему Точечная в данном случае удобнее).
Для исходных рядов нам понадобится 4 столбца с данными (2 «постоянных» процесса, ряд с цикличностью и тренд). В файле примера на листе Исходный и сглаженный ряд это столбцы T:W.
Один из исходных рядов – динамический (столбец U, назовем его «постоянный» процесс с изменениями), т.е. его значения пересчитываются при любом изменении данных листа или после нажатии клавиши F9. Это сделано с помощью формулы = СЛУЧМЕЖДУ($T$10-2*$T$9;$T$10+2*$T$9)
За среднее значение этого ряда взято среднее значение ряда из столбца T = СРЗНАЧ(T13:T111) , а диапазон изменения – 2 стандартных отклонения того же ряда = СТАНДОТКЛОН.В(T13:T112) .
Такой автоматически генерирующийся ряд удобен для оценки модели – можно получить целый набор прогнозных значений, ошибок и доверительных интервалов. Фактически, конечно, функция СЛУЧМЕЖДУ() генерирует белый шум (с заданным смещением среднего относительно 0).
Примечание: Про функцию СЛУЧМЕЖДУ() можно почитать здесь. Эта функция генерирует непрерывное равномерное распределение, чтобы сгенерировать выборку из нормального или любого другого распределения см. эту статью.
Выбор нужно типа процесса организован с помощью группы переключателей, которая связана с ячейкой I11.
Значения выбранного исходного ряда подставляются в столбце В с помощью формулы = СМЕЩ(T13;;$I$11-1) . Подробнее про функцию СМЕЩ() см. здесь.
Сглаженный ряд разместим рядом в столбце С, этот ряд будет формироваться для заданного периода усреднения (ячейка A7) с помощью формулы = ЕСЛИ(A13<$A$7;НД();СРЗНАЧ(СМЕЩ(B13;-$A$7+1;;$A$7)))
Примечание: Про построение сглаженного ряда см. Скользящее среднее в MS EXCEL.
Период усреднения для удобства задается с помощью элемента управления счетчик.
Осталось сформировать данные для линии среднего значений исходного ряда. Для этого понадобится только 2 точки (см. диапазон F43:G44).
Теперь все готово для построения диаграммы.
Примечание: для тех, кто не имеет большого опыта в построении диаграмм MS EXCEL предлагается прочитать эту статью.
Для тренда сглаженный ряд будет выглядеть так:
Напомним, что метод скользящего среднего состоит в вычислении средних значений на основе предшествующих значений исследуемого числового ряда. Пусть последнее значение ряда произошло в момент i.
В случае усреднения за 3 периода скользящее среднее в момент i равно:
Yскол.i=(Yi+ Yi-1+ Yi-2)/3
Именно так считает инструмент Пакета Анализа «Скользящее среднее». Понятно, что нас интересует прогноз в будущий момент времени i+1. Положим, что прогнозное значение ряда в момент i+1 равно Yпрогнозн.i+1= Yскол.i
В итоге получаем эквивалентную формулу
Yпрогнозн.i+1=(Yi+ Yi-1+ Yi-2)/3
Для наглядности прогнозное значение на диаграмме изобразим в виде горизонтальной линии зеленого цвета (длина линии ничего не значит). Для этого понадобится только 2 точки (см. диапазон F8:G9).
Хотя использование метода скользящего среднего для прогнозирования носит скорее академический, нежели практический интерес, все же покажем как построить что-то типа «интервала предсказания». Для построения интервала воспользуемся ошибкой, которая вычисляется в надстройке Пакет анализа по формуле:
= КОРЕНЬ(СУММКВРАЗН(ИР;СР)/m)
Где m – количество периодов усреднения
ИР - m последних значений Исходного Ряда (ИР)
СР - m последних значений Сглаженного Ряда (СР)
Т.е. данная стандартная ошибка вычисляется по формуле:
RMSE – это Root Mean Squared Error (среднеквадратическая ошибка).
В файле примера на листе «Прогнозное значение» эта ошибка вычислена по формуле
=КОРЕНЬ(СУММКВРАЗН(СМЕЩ($B$112;-A7;;A7);СМЕЩ($C$112;-A7;;A7))/$A$7)
A7 содержит количество периодов усреднения
СМЕЩ($B$112;-A7;;A7) – это ссылка на диапазон исходного ряда (последние m значений)
СМЕЩ($C$112;-A7;;A7) – это ссылка на диапазон сглаженного ряда
СУММКВРАЗН() вычисляет сумму квадратов разностей
Если вычислить ошибки прогнозирования в отдельном столбце D, то формула для RMSE упростится:
= КОРЕНЬ(СУММКВ(СМЕЩ($D$112;-A7;;A7))/$A$7)
Границы интервала (для заданного уровня значимости альфа) вычисляются как:
Верхняя граница = Yпрогнозн.i+1 + RMSE*tm-1,1-альфа/2
Нижняя граница = Yпрогнозн.i+1 - RMSE*tm-1,1-альфа/2
tm-1,1-альфа/2 - верхний α/2-квантиль распределения Стьюдента с m-1 степенью свободы (это просто число, которое показывает сколько ошибок RMSE нужно, чтобы «интервал предсказания» накрыл прогнозное значение с вероятностью 1-альфа).
Примечание: «Интервал предсказания» вычислен лишь по аналогии с построением доверительного интервала для оценки среднего, для которого у нас была статистическая модель. Для случая скользящего среднего корректность такого построения обосновывается отдельно. В данной статье «Интервал предсказания» построен лишь с целью демонстрации самого процесса построения интервалов предсказания.
Верхний α/2-квантиль вычислим по формуле = СТЬЮДЕНТ.ОБР.2Х(C8;A7-1)
в ячейке С8 находится альфа – уровень значимости (обычно 5%).
Как видно из диаграммы выше для нашего стационарного процесса (фактически белый шум) прогнозное значение ожидаемо находится около глобального среднего, а доверительный интервал охватывает весь диапазон изменений исходного ряда, т.е. будущее значение этого ряда может появиться на всем интервале, что фактически говорит нам о невозможности предсказания.
Как и следовало ожидать, для растущего тренда картинка существенно отличается: доверительный интервал уже в 2 раза меньше и прогнозное значение находится вдали от глобального среднего.
Исследуем исходный ряд на наличие автокорреляции. Подробно об автокорреляции см. отдельную статью .
Автокорреляция (Autocorrelation, Lagged correlation, Serial correlation) – корреляция значений временного ряда с собственными значениями, сдвинутыми по времени на один или несколько периодов (лагов). Ниже показана диаграмма содержащая исходный ряд и ряд сдвинутый на лаг k=4 (общее количество значений ряда N уменьшится на k, глобальное среднее на диаграмме оставлено как у исходного ряда).
Примечание: Диаграмма построена на листе «Автокорреляция ИР» в файле примера . ИР – Исходный Ряд.
Для оценки автокорреляции используют 3 основных инструмента: график временного ряда (Time Series Plot), диаграмму рассеивания (Lagged Scatterplot) в зависимости от лага и функцию автокорреляции (Autocorelation Function, ACF).
Диаграмма рассеяния используется для отображения возможной взаимосвязи между двумя переменными.
В нашем случае будем исследовать корреляционную зависимость между двумя рядами данных, сдвинутых на лаг k относительно друг друга (см. диаграмму выше).
Для лага k=4 диаграмма рассеяния, очевидно, демонстрирует наличие линейной положительной корреляции.
С помощью инструмента диаграммы «Линия тренда» построена линия регрессии и вычислим коэффициент детерминации R2. Ниже мы вычислим R2 с помощью формул, т.к. это просто квадрат коэффициента автокорреляции.
Вычислим коэффициенты автокорреляции для лагов от 1 до 15.
Два массива в аргументах функции КОРРЕЛ () – это просто 2 ряда, которые сдвинуты на лаг k (ячейка K68) относительно друг друга:
СМЕЩ($B$13;;;$B$10-K68)
СМЕЩ($B$13;K68;;$B$10-K68)
Зависимость коэффициента автокорреляции от лага – это функция автокорреляции (ACF). График ACF – это коррелограмма. Для стационарного процесса (у нас это «постоянный» процесс, фактически белый шум) коррелограмма имеет следующий вид:
Для другого стационарного процесса (с апериодической цикличностью) коррелограмма имеет совершенно другой вид:
Все коэффициенты автокорреляции, которые выше границ доверительного интервала, являются статистически значимыми (про расчет доверительного интервала для ACF см. статью про Автокорреляцию ). Диаграмма рассеяния для выбранного лага (столбец гистограммы, который выделен цветом) также подтверждает отсутствие автокорреляции.
Коэффициент детерминации R2, указанный на диаграмме рассеяния можно рассчитать возведя в квадрат коэффициент корреляции или применив формулу
=КВПИРСОН(СМЕЩ($B$13;;;$B$10-K68);СМЕЩ($B$13;K68;;$B$10-K68))
для тех же массивов, полученных из исходного ряда.
Для прогнозирования значения временного ряда мы использовали модель скользящего среднего с определенным периодом усреднения m. Какое значение является лучшим для прогнозирования?
Критерием оптимальности m является минимизация ошибки модели.
Существует целый ряд формул для вычисления ошибок, но самой лучшей ошибкой для оценки точности модели является среднеквадратичная ошибка (RMSE), вычисленная нами ранее.
Кроме применяются еще несколько других ошибок:
• MAE (Mean Absolute Error, Средняя Абсолютная ошибка). В EXCEL вычисляется по формуле =СРЗНАЧ(ABS(СМЕЩ($D$112;-D10;;D10))). Сначала диапазон ошибок СМЕЩ($D$112;-D10;;D10) в столбце D берется по модулю, затем находится среднее значение. Эта ошибка менее чувствительна к одиночным выбросам, т.к. значения ошибок не возводятся в квадрат.
• MAPE (Mean Absolute Percentage Error, Средняя Абсолютная Процентная Ошибка). В EXCEL вычисляется по формуле =СРЗНАЧ(ABS(СМЕЩ($D$112;-D10;;D10)/СМЕЩ($B$112;-D10;;D10))) Вычисляется практически аналогично MAE, но вместо просто ошибки берется по модулю ее отношение к значению исходного ряда. Получается безразмерная величина. Подходит для исходных рядов с трендом или ярко выраженной сезонностью.
• ME (Mean Error, Средняя ошибка). Эта ошибка показывает имеет ли прогноз смещение. МЕ должна быть около 0. =СРЗНАЧ(СМЕЩ($D$112;-D10;;D10)). ME может быть положительной и отрицательной.
• MPE (Mean Percentage Error, Средняя Процентная ошибка). Вычисляется практически аналогично ME, но вместо просто ошибки берется ее отношение к значению исходного ряда. MPE может быть положительной и отрицательной. = СРЗНАЧ((СМЕЩ($D$112;-D10;;D10)/СМЕЩ($B$112;-D10;;D10)))
Все ошибки вычислены в файле примера на листе Ошибки модели в диапазоне M7:Q11.
Как было сказано выше, для построения «интервала предсказания» прогнозного значения использовалась среднеквадратичная ошибка (RMSE) причем вычисленная не для всего ряда, а лишь на периоде усреднения. Это соответствует формулам MS EXCEL в Пакете анализа. На обоих горизонтах расчета RMSE дает близкие значения, причем в зависимости от лага или значений ряда RMSE вычисленная на периоде усреднения m может давать непредсказуемо либо меньшее либо большее значение по сравнению с RMSE вычисленной для всего ряда (в этом можно убедиться проанализировав RMSE для динамически изменяемого постоянного процесса).
На листе Ошибки модели построена диаграмма разброса ошибок и гистограмма ошибок. Эти диаграммы автоматически перестраиваются в зависимости от выбранного лага или типа исходного ряда.
Диаграмму разброса ошибок можно построить на основе диаграммы MS EXCEL типа График. Специальных знаний построения диаграмм практически не требуется. Впрочем, как и для построения Гистограммы. Несколько сложнее построить таблицу исходных данных для гистограммы. Об этом подробно рассказано в статье Гистограмма распределения
Диаграмма разброса ошибок должна демонстрировать колебания ошибок около 0, а гистограмма - типичную выборку из нормального распределения. Проверить распределение ошибок на нормальность можно построить соответствующий график.
Подробнее о проверке распределения на нормальность см. в этой статье.
В заключение, по аналогии с проверкой исходного ряда на автокорреляцию можно вычислить автокорреляцию ошибок и построить диаграммы рассеяния и коррелограмм.
На картинке выше приведены диаграмма рассеяния и коррелограмм для ошибок «постоянного» процесса.
Примечание: На листе «скользящее среднее» объединены все диаграммы, о которых рассказывалось выше в статье.
Скользящее среднее используется для сглаживания краткосрочных колебаний с целью определения долгосрочного тренда. У этого метода есть несколько модификаций (центрированное, взвешенное), которые имеют положительные и отрицательные стороны. Рассмотрим как сделать соответствующие вычисления в MS EXCEL.
Метод сглаживания краткосрочных колебаний – Скользящее среднее подробно рассмотрен в одноименной статье Скользящее среднее в MS EXCEL. Здесь остановимся на его модификациях.
При поиске оптимального количества периодов усреднения методом Скользящего среднего необходимо учитывать, что чем больше количество периодов усреднения тем:
- глаже получаемый ряд (отфильтровываются колебания, скрывающие тренд);
- больше увеличивается лаг (ряд скользящего среднего запаздывает относительно тренда исходного ряда). Это проявляется в смещении вправо «поворотных» точек, когда меняется направление тренда.
На первой картинке (3 периода) ряд скользящего среднего содержит многочисленные колебания, но тренды обоих рядов не смещены относительно друг друга (см. файл примера на лист Скользящее среднее ).
На второй картинке (15 периодов) ряд скользящего среднего сглажен, но тренды обоих рядов существенно смещены относительно друг друга.
Для устранения сдвига сглаженного ряда вместо простого скользящего среднего используют центрированное скользящее среднее.
Для компенсации сдвига сглаженного ряда относительно исходного используют центрированное скользящее среднее. В этом случае значения сглаженного ряда получают путем усреднения как более ранних, так и более поздних значений.
Например, для 3-х периодов усреднения значения такого сглаженного ряда получают так:
См. файл примера на листе Центрированное-3 периода построение сглаженного ряда в случае 3-х периодов. При четном количестве усреднений используют метод двойного усреднения.
Например, для 4-х периодов усреднения значения такого сглаженного ряда получают так:
В файле примера на листе Центрированное реализовано построение сглаженного ряда с настраиваемым периодом усреднения:
В итоге для 15 периодов сглаженный ряд будет иметь вот такой вид.
Очевидно, смещения вправо не наблюдается как у простого сглаженного среднего.
Примечание: Так как для вычисления центрированного скользящего среднего требуется знать как более ранние, так и более поздние значения, то становится очевидным, что такой метод сглаживания нельзя использовать для прогнозирования.
В рассмотренных выше методах вклад всех значений Yi+1; Yi; Yi-1 в расчет скользящего среднего был одинаков, т.е. удаленные на разное количество периодов значения влияли одинаково на вычисление скользящего среднего.
В методе взвешенного скользящего среднего значения от более отдаленных периодов учитываются в меньшей степени, чем от близких.
Вспомним как рассчитывалось скользящее среднее в случае усреднения за 3 периода:
Таким образом, при усреднении значения исходного ряда учитывались в равной степени. Формулу можно переписать следующим образом:
1/3 – это вес значений исходного ряда, которые используются при усреднения.
Взвешенное скользящее среднее является модификацией скользящего среднего: при его расчете веса различных периодов имеют различный вес (более отдаленные периоды, как правило, имеют меньший вес). Сумма весов должна быть равна 1.
Примечание: Сумма весов может отличаться от 1, но в этом случае каждое значение сглаженного ряда нужно разделить на эту сумму.
В файле примера на листе Взвешенное-3 периода вычислим Взвешенное скользящее среднее для 3-х периодов со следующими весами:
Для вычисления значений ряда используется формула = СУММПРОИЗВ(B11:B13;$F$6:$F$8) , с помощью которой значения исходного ряда умножаются на соответствующие веса, затем производится сумма полученных произведений.
Веса для каждого периода усреднения выбираются из условий задачи или могут быть оптимизированы путем минимизации суммы квадратов ошибок (англ. Sum of Squared Errors - SSE), которая вычисляется по формуле:
Суммирование идет по всем точкам сглаженного ряда (номер первой точки сглаженного ряда равен периоду усреднения, т.е. n (для данного метода), N – общее количество точек исходного ряда.
Для подбора оптимальных весов можно использовать инструмент MS EXCEL Поиск решения.
В качестве ограничений используются минимальные и максимальные значения весов, кроме того сумма весов должна быть =1. Если не задать минимальные и максимальные значения весов, то все веса, кроме веса ближайшего периода будут установлены Поиском решения равными 0. Т.к. в этом случае среднеквадратичная ошибка будет минимальна и =0 (оба ряда просто совпадут).
Взвешенное скользящее среднее с настраиваемым количеством периодов усреднения приведено в файле примера на листе Взвешенное . В этом случае потребуется также автоматический расчет весов. Расчет весов выполнен по формуле wi=i/s, где s – сумма номеров периодов усреднения. Например, если периодов усреднения 3, то сумма номеров периодов усреднения равна 6=3+2+1. При расчете первой точки сглаженного ряда w1=1/6, w2=2/6, w3=3/6. Сумма весов равна 1. w3 имеет максимальной вес, т.к. по времени соответствующая точка №3 ближе всего к первой точке сглаженного ряда. Конечно, можно использовать и другие формулы или распределения: одной из разновидностей взвешенных методов является экспоненциальное сглаживание.
Метод Взвешенного скользящего среднего также как и просто скользящее среднее имеет значительный лаг при больших значениях периода усреднения, т.е. когда ряд скользящего среднего запаздывает относительно тренда исходного ряда. Этого недостатка лишена следующая модификация - Взвешенное центрированное скользящее среднее.
При расчете Центрированного скользящего среднего, рассмотренного выше, также можно изменить веса периодов усреднения.
Это можно сделать с помощью формулы:
Как и для взвешенного скользящего среднего, значения от более отдаленных периодов учитываются в меньшей степени, чем от близких.
Примечание: Для генерации весов в формуле использовано нормальное распределение.
В статье Анализируем ошибки моделей сглаживания временных рядов будет показано, что при увеличении количества периодов усреднения (n), погрешности будут расти практически монотонно: сначала за счет постепенного «выпрямления» сглаженной кривой по сравнению с исходным «пилообразным» графиком, затем и за счет его сдвига вправо. Сдвиг вправо сглаженной кривой особенно актуален для рядов с трендом и рядов с сезонностью.
Примечание: Оценка качества полученной модели для прогнозирования по кривым роста производится при проверке адекватности и оценки точности модели. В проверку адекватности входит: проверка независимости (отсутствие автокорреляции по критерию Дарбина-Уотсона), проверка случайности, соответствие остатков временного ряда случайному распределению(R/S критерий), равенство 0 средней ошибки.
Метод скользящей средней – это статистический инструмент, с помощью которого можно решать различного рода задачи. В частности, он довольно часто используется при прогнозировании. В программе Excel для решения целого ряда задач также можно применять данный инструмент. Давайте разберемся, как используется скользящая средняя в Экселе.
Применение скользящей средней
Смысл данного метода состоит в том, что с его помощью происходит смена абсолютных динамических значений выбранного ряда на средние арифметические за определенный период путем сглаживания данных. Этот инструмент применяется для экономических расчетов, прогнозирования, в процессе торговли на бирже и т.д. Применять метод скользящей средней в Экселе лучше всего с помощью мощнейшего инструмента статистической обработки данных, который называется Пакетом анализа. Кроме того, в этих же целях можно использовать встроенную функцию Excel СРЗНАЧ.
Способ 1: Пакет анализа
Пакет анализа представляет собой надстройку Excel, которая по умолчанию отключена. Поэтому, прежде всего, требуется её включить.
- Перемещаемся во вкладку «Файл». Делаем щелчок по пункту «Параметры».
После этого действия пакет «Анализ данных» активирован, и соответствующая кнопка появилась на ленте во вкладке «Данные».
А теперь давайте рассмотрим, как непосредственно можно использовать возможности пакета Анализ данных для работы по методу скользящей средней. Давайте на основе информации о доходе фирмы за 11 предыдущих периодов составим прогноз на двенадцатый месяц. Для этого воспользуемся заполненной данными таблицей, а также инструментами Пакета анализа.
-
Переходим во вкладку «Данные» и жмем на кнопку «Анализ данных», которая размещена на ленте инструментов в блоке «Анализ».
В поле «Интервал» следует указать интервал обработки значений методом сглаживания. Для начала давайте установим значение сглаживания в три месяца, а поэтому вписываем цифру «3».
В поле «Выходной интервал» нужно указать произвольный пустой диапазон на листе, где будут выводиться данные после их обработки, который должен быть на одну ячейку больше входного интервала.
Также следует установить галочку около параметра «Стандартные погрешности».
При необходимости, можно также установить галочку около пункта «Вывод графика» для визуальной демонстрации, хотя в нашем случае это и не обязательно.
В поле «Интервал» ставим цифру «2».
В поле «Выходной интервал» указываем адрес нового пустого диапазона, который, опять же, должен быть на одну ячейку больше входного интервала.
Способ 2: использование функции СРЗНАЧ
В Экселе существует ещё один способ применения метода скользящей средней. Для его использования требуется применить целый ряд стандартных функций программы, базовой из которых для нашей цели является СРЗНАЧ. Для примера мы будем использовать все ту же таблицу доходов предприятия, что и в первом случае.
Как и в прошлый раз, нам нужно будет создать сглаженные временные ряды. Но на этот раз действия будут не настолько автоматизированы. Следует рассчитать среднее значение за каждые два, а потом три месяца, чтобы иметь возможность сравнить результаты.
Прежде всего, рассчитаем средние значения за два предыдущих периода с помощью функции СРЗНАЧ. Сделать это мы можем, только начиная с марта, так как для более поздних дат идет обрыв значений.
-
Выделяем ячейку в пустой колонке в строке за март. Далее жмем на значок «Вставить функцию», который размещен вблизи строки формул.
Обязательным является только один аргумент.
Мы произвели расчет прогноза при помощи метода скользящей средней двумя способами. Как видим, данную процедуру намного проще выполнить с помощью инструментов Пакета анализа. Тем не менее некоторые пользователи не всегда доверяют автоматическому расчету и предпочитают для вычислений использовать функцию СРЗНАЧ и сопутствующие операторы для проверки наиболее достоверного варианта. Хотя, если все сделано правильно, на выходе результат расчетов должен получиться полностью одинаковым.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: