Вычислить простую, взвешенную и экспоненциальную скользящую среднюю в Excel

В школьной жизни вы узнали о среднем значении и методе его расчета. Формула для расчета среднего очень проста. Вам просто нужно сложить все значения в заданных данных и разделить результат на общее количество значений в данных. Скользящее среднее(Moving average) также является еще одним типом среднего, который имеет практическое применение. Он имеет приложения во многих областях, таких как фондовый рынок, продажи и т. д.

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

Скользящее среднее бывает трех типов: простое взвешенное и экспоненциальное. В этой статье мы объясним методы расчета всех трех типов скользящих средних в Excel .

Читать(Read) : Как использовать функции MIN, Max и AVERAGE в Excel(How to use MIN, Max, and AVERAGE Functions of Excel) .

Как рассчитать скользящую среднюю(Moving Average) в Excel

Мы будем вычислять скользящее среднее в Excel двумя способами. В одном методе мы будем использовать встроенный инструмент MS Excel , а в другом методе мы будем использовать формулы.

1] Использование пакета инструментов анализа данных

В этом методе мы будем использовать пакет инструментов анализа данных(Data Analysis Tool Pack) в Excel для расчета скользящего среднего. MS Excel поставляется с инструментом для расчета простых скользящих средних. Пакет инструментов для анализа (Analysis Tool Pack)данных(Data) — это надстройка, а это значит, что она может отсутствовать по умолчанию. Мы перечисляем здесь шаги, чтобы получить этот инструмент.

1] Нажмите « Файл(File) ».

Вычислить скользящее среднее Excel 1

2] Выберите « Параметры(Options) ».

Вычислить скользящее среднее Excel 2

3] Это откроет диалоговое окно. Нажмите(Click) « Надстройки(Add-Ins) ».

Вычислить скользящее среднее Excel 3

4] На правой панели диалогового окна вы увидите раскрывающееся меню внизу. В выпадающем меню выберите(Select) « Надстройки Excel » и нажмите на кнопку « (Excel Add-ins)Перейти(Go) ».

Вычислить скользящее среднее Excel 4

5] Теперь выберите « Пакет инструментов анализа(Analysis ToolPack) » и нажмите кнопку « ОК(OK) ».

Вычислить скользящее среднее Excel 5

6] Вышеуказанные шаги установят инструмент анализа данных(Data Analysis Tool) в MS Excel . Посмотреть его можно во вкладке « Данные ».(Data)

Вычислить скользящее среднее Excel 6

Читайте(Read) : Как рассчитать процентную разницу между двумя числами в Excel(How to calculate the percentage difference between two numbers in Excel) .

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

Вычислить скользящее среднее Excel 7

Пожалуйста(Please) , следуйте приведенным ниже шагам, чтобы рассчитать простое скользящее среднее с помощью пакета инструментов для анализа данных(Data Analysis Tool Pack) .

1] Перейдите на вкладку « Данные(Data) » и выберите параметр « Анализ данных(Data Analysis) », как показано на снимке экрана выше (см. шаг 6).

2] Откроется небольшое диалоговое окно, в котором вам нужно выбрать параметр « Скользящее среднее(Moving Average) » и нажать «ОК». Это простая скользящая средняя.

Вычислить скользящее среднее Excel_8

3] Вы должны ввести диапазон ввода данных, для которых вы хотите рассчитать простое скользящее среднее. В нашем образце данных у нас есть диапазоны данных от ячейки B2 до ячейки B11 . Поэтому мы вошли в диапазон B2: B11 . После этого введите « Интервал(Interval) ». В выходной диапазон вы должны ввести адрес ячейки, в которой вы хотите получить выходной диапазон. Мы выбрали Е2. Когда вы закончите, нажмите OK.

Вычислить скользящее среднее Excel 9

4] Вы получите результат. На скриншоте ниже видно, что в первых трех строках отображается ошибка #N/A. Это потому, что мы ввели 4 в « Интервал(Interval) », что указывает на то, что это 4- дневная SMA(SMA) .

Вычислить скользящее среднее Excel 10

Читайте(Read) : Как рассчитать простые проценты в Excel(How to Calculate Simple Interest in Excel) .

В методе 1 выше мы рассчитали скользящее среднее с помощью инструмента в Excel . Но используя метод 1, мы можем рассчитать только простое скользящее среднее. В методе 2 мы будем вычислять все три типа скользящих средних, используя формулы.

2 ] Расчет (Calculation)простой скользящей средней(Simple Moving Average) ( SMA ) с использованием формулы(Formula)

Здесь мы возьмем те же данные выборки.

1] Если вы хотите рассчитать 4- дневную SMA(SMA) , вам нужно ввести следующую формулу в ячейку, которая находится в строке 5 ( день 4(Day 4) ). После ввода формулы нажмите « Enter ».

=AVERAGE(B2:B5)

Рассчитать простую скользящую среднюю Excel_1

2] Теперь наведите курсор на правый нижний угол выбранной ячейки. Когда ваш курсор изменится на значок « Плюс(Plus) », нажмите и удерживайте левую кнопку мыши и перетащите ее в ячейку E11 . Это скопирует формулу в другие ячейки.

Рассчитать простую скользящую среднюю Excel_2

3] Вы получите свой результат.

Рассчитать простую скользящую среднюю Excel_3

Читать(Read) . Как вычислить площадь прямоугольника, треугольника или круга в Excel(How to calculate the area of a Rectangle, Triangle, or Circle in Excel) .

3] Расчет взвешенного (Calculation)скользящего среднего(Weighted Moving Average) ( WMA ) с использованием формулы(Formula)

Рассчитаем взвешенное скользящее среднее. Опять же(Again) , мы берем те же выборочные данные. Чтобы рассчитать WMA , вы должны иметь веса, присвоенные конкретным значениям. Формула для расчета взвешенного скользящего среднего:

WMA = [(Latest value * weight) + (Previous value * weight) + ...] / (Sum of all weights)

Здесь мы рассчитываем 3-точечный WMA с весом 70%, присвоенным последнему значению, 20% — значению непосредственно перед ним и 10% — значению перед вторым. В соответствии с этими данными вы должны ввести следующую формулу в ячейку E4.

=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)

Вычисление взвешенного скользящего среднего Excel 1

Первые две ячейки пусты, потому что мы вычисляем трехточечный WMA . Теперь перетащите ячейку в последнюю ячейку, как мы делали раньше при расчете SMA . Вы получите свой результат.

Вычисление взвешенного скользящего среднего Excel 2

Читайте(Read) : Как рассчитать медиану в Excel(How to calculate Median in Excel) .

4] Расчет экспоненциальной (Calculation)скользящей средней(Exponential Moving Average) ( EMA ) с использованием формулы(Formula)

Чтобы рассчитать EMA , у нас должно быть первое значение EMA , которое мы получаем путем вычисления SMA и весового множителя или константы сглаживания ( K ). Основная формула для расчета EMA выглядит следующим образом:

EMA = Latest Value or Today's Value * K + Yesterday EMA Value * (1 - K)

1] Возьмем ту же таблицу, в которой мы рассчитали SMA . В этом примере мы взяли K = 0.3 . Первое значение EMA равно первому значению SMA . Здесь мы рассчитываем значения SMA и EMA за 4 дня .

Вычислить экспоненциальное скользящее среднее Excel 1

2] Теперь примените следующую формулу к ячейке F6.

=B6*$G$1+F5*(1-$G$1)

Обратите внимание, что мы заблокировали ячейку G в приведенной выше формуле, потому что нам нужно одно и то же значение K для всех расчетов EMA .

3] Теперь перетащите(Drag) ячейку, чтобы скопировать формулу в остальные ячейки.

Вот и все, мы рассчитали все три вида скользящих средних в MS Excel . Надеемся, вам понравилась статья.

Вам также может понравиться(You may also like) : Как рассчитать средний балл или средний балл в Excel(How to calculate Grade Point Average or GPA in Excel) .



About the author

Я разработчик бесплатного программного обеспечения и сторонник Windows Vista/7. Я написал несколько сотен статей на различные темы, связанные с операционной системой, включая советы и рекомендации, руководства по ремонту и рекомендации. Я также предлагаю консультационные услуги, связанные с офисом, через мою компанию Help Desk Services. Я хорошо понимаю, как работает Office 365, его функции и способы их наиболее эффективного использования.



Related posts