График диапазона уровней отклонения от скользящей средней Excel

В данном примере рассмотрим, как построить канал тренда между уровнями сопротивления и поддержки относительно скользящей средней на графике в Excel (channel levels). Более того сделаем наш трендовый канал динамически изменяемым.

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

Пример графика с уровнями поддержки и сопротивления скользящей средней

Шум высоковолатильных показателей подавляется за счет отсечения так называемых статистических выбросов, другими словами статистические погрешности. Принцип устранения или подавления шума достаточно прост. Все значения, что за пределами канала является статистическим выбросом. Их еще называют аномальными значениями, которые не должны учитываться в анализе общего тренда направления изменения тенденции на графике. Для практических расчетов с целью отсечения статистических выбросов можно использовать функции Excel такие как МЕДИАНА. Но для визуального анализа намного удобнее построить канал с настраиваемым диапазоном между уровнями сопротивления и поддержки относительно скользящей средней.

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

По традиции для примера смоделируем ситуацию. Допустим у нас имеются ежемесячные статистические показатели абсолютных значений с высокой волатильностью за учетный период 1 год. При том не имеет значения, что это за показатели: продажи, километраж, остатки товаров или расходы на топливо и т.п. Обозначим их наименованием «Факт»:

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

Сначала разместим на графике линию скользящего среднего значения (moving average), а потом сталкиваясь от него будем устанавливать уровни сопротивления и поддержки. Мы сформируем таким образом канал диапазона безопасной зоны для максимальны и минимальных значений показателей «Факт».

О способах расчета moving average в Excel подробно описано и проиллюстрировано в примере:

В результате у нас должны появится следующие значения необходимые для построения линии скользящей средней с периодичностью интервала 2 на графике:

А в ячейке I1 указываем переменное значение 10% для настройки верхнего и нижнего отклонения от скользящей средней определяя таким образом ширину диапазона канала уровней.

Получив значения скользящей средней для показателей «Факт» мы несложными арифметическими вычислениями определяем сразу 3 необходимых наборов данных – 2 отклонения от скользящего среднего и разница между ними, то есть сам диапазон безопасной зоны:

1. Столбец «Верхний предел» – формула для вычисления отклонение от скользящего среднего вверх =C2+C2*$I$1.
2. Столбец «Нижний предел» – формула вычисляет отклонение от скользящей средней вниз =C2-C2*$I$1 соответственно.
3. Столбец значений «Диапазон безопасной зоны» – формула вычисления размера диапазона для построения канала уровней на графике =D2-E2.

Мы используем отклонение от линии moving average в пределах 10%. При желании можно рассчитать оптимальный размер диапазона безопасной зоны используя формулу массива (CTRL+SHIFT+Enter):

Так мы определили оптимальный уровень отклонений, но это не обязательно – лучше пользоваться изменяемым.

Далее переходим непосредственно к построению самого графика с динамически изменяемым каналом уровней.

Построение графика диапазона отклонения от скользящего среднего в Excel

Выделите все столбцы таблицы КРОМЕ ОДНОГО – «Верхний придел» и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«График с маркерами»:

Далее кликнув левой кнопкой мышки по самому графику (чтобы сделать его активным) из его дополнительного меню выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Тип»-«Изменить тип диаграммы» и в появившемся окне придайте тип «График с областями и накоплением» для рядов «Нижний предел» и «Диапазон безопасной зоны» как показано ниже на рисунке:

Далее нам необходимо срыть нижнюю (серую область) ряда «Нижний уровень» убрав его заливку. Для этого кликаем правой кнопкой мышки по серой области ряда и из появившегося константного меню выбираем опцию «Формат ряда данных», а из выпадающего окна параметров указываем на опцию «ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Нет заливки»:

Таким же образом кликаем правой кнопкой мышки по вертикальной оси и выбираем опцию «Формат оси» из контекстного меню. После чего в окне опций изменяем значения в: «ПАРАМЕТРЫ ОСИ»-«Минимум» – 300 и здесь же «ДЕЛЕНИЯ»-«Основные» – наружу:

Таким образом мы увеличили масштаб отображаемых линий на графике без изменения его размеров. То есть изменили диапазон графика в Excel относительно вертикальной оси Y.

Дополнительные настройки и оформление стиля

Продолжаем оформление внешнего вида графика. Удаляем название щелкнув на кнопке плюс «+» в верхнем правом углу возле графика и из выпадающего меню убираем галочку на против опции «Название диаграммы» как показано выше на рисунке. Затем изменим стиль оформления скользящей средней линии сгладив ее углы. Для этого кликните правой кнопкой мышки по ряду «Скользящая средняя» и из контекстного меню выберите опцию «Формат ряда данных». В паявшемся окне параметров на вкладке «Заливка» внесите свои изменения: «Линия»-«Сплошная линия»-«Цвет» – белый, «Тип штриха» – круглые точки и здесь же отмечаем галочкой опцию «Сглаженная линия» в самом низу окна.

По такому же принципу настраиваем цвета заливок и стили линий для остальных элементов графика в результате получаем готовый полезный инструмент для визуализации данных и анализа:

Скачать график диапазона уровней скользящей средней в Excel

Благодаря статистическим методам расчетов можно качественно фильтровать исходные данные от шумов и погрешностей получая наиболее точные направления восходящего (бычий) и нисходящего (медвежий) тренда.