Как переименовать линию тренда. Построение функции тренда в Excel

Глядя на любой набор данных распределенных во времени (динамический ряд), мы можем визуально определить падения и подъемы показателей, которые он содержит. Закономерность подъемов и падений называется трендом, который может говорить о том, увеличиваются или уменьшаются наши данные.

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

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Y(t) = a0 + a1*t + E

Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);

t — номер периода (порядковый номер месяца), который объясняет план продаж Y;

a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);

a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;

E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.

Построение модели

Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:

Следующее, что мы должны сделать — это определить коэффициенты a0 и a1 для прогнозирования объема продаж за 10-ый месяц.

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

В Google Sheets выбираем Редактор диаграмм -> Дополнительные и ставим галочку возле Линии тренда . В настройках выбираем Ярлык — Уравнение и Показать R^2 .

Если вы делаете все в MS Excel, то правой кнопкой мыши кликаем на график и в выпадающем меню выбираем «Добавить линию тренда».

По умолчанию строится линейная функция. Справа выбираем «Показывать уравнение на диаграмме» и «Величину достоверности аппроксимации R^2».

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

Прогнозируем

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

Повысить точность модели можно добавлением сезонности к функции тренда, что мы и сделаем в следующей статье.

Выполнение заданий на построение линии тренда отличает то, что исходные данные могут быть набором чисел не связанных между собой.

Прогнозирование по обычному графику невозможно, так как его коэффициент детерминированности (R^2) будет близок к нулю.

Именно поэтому применяются специальные функции.

Сейчас мы их построим, настроим и проанализируем.

Легкая версия построения

Процесс построения линии тренда состоит из трех этапов: ввод в excel исходных данных, построение графика, выбор линии тренда и ее параметров.

Начнем с ввода данных.

1. Создаем в Excel таблицу с исходными данными.

(Рисунок 1)

2. Выделяем ячейки B3:B17 и перейдя на закладку «Вставка» выбираем «График».

(Рисунок 2)

3. После того как график построен, можно добавить подписи и заголовок.

Для начала кликнем левой кнопкой мыши по границе графика, чтобы выделить его.

Затем перейдем на закладку "Конструктор" и выберем "Макет 1".

(Рисунок 3)

4. Переходим к построению линии тренда. Для этого снова выделяем график и переходим на закладку «Макет».

(Рисунок 4)

5. Нажимаем на кнопку «Линия тренда» и выбираем «линейное приближение» или «экспоненциальное приближение».

(Рисунок 5)

Так мы построили первичную Линию тренда, которая может мало соответствовать действительности.

Это наш промежуточный результат.

(Рисунок 6)

И поэтому потребуется настроить параметры нашей линии тренда или выбрать другую функцию.

Профессиональная версия: выбор линии тренда и настройка параметров

6. Нажимаем на кнопку «Линия тренда» и выбираем «Дополнительные параметры и линии тренда».

(Рисунок 7)

7. В окне «Формат линии тренда», мы ставим флажок напротив «поместить на диаграмму величину достоверности аппроксимации R^2 и нажимаем кнопку «закрыть».

Видим на диаграмме коэффициент R^2= 0,6442

(Рисунок 8)

8. Отменяем изменения. Выделяем график, нажимаем на закладку "Макет", кнопку "линия тренда" и выбираем "Нет".

9. Переходим в окно «Формат линии тренда», но уже для того, чтобы выбрать «Полиноминальную» линию тренда, меняем степень, добиваясь показателей коэффициента R^2= 0,8321

(Рисунок 9)

Прогноз

Если нам нужно предположить, какие данные могли бы быть получены в следующем измерении, в окне «Формат линии тренда», указываем количество периодов на которые делается прогноз.

(Рисунок 10)

На основе прогноза мы можем предположить, что 25 января количество набранных баллов было бы от 60 до 70.

Вывод

И в заключение если Вам интересна формула по которой построен тренд, в коне «Формат линии тренда» поставьте флажок напротив «показать уравнение на диаграмме».

Теперь Вы знаете, как выполнить задание и построить линию тренда, даже в такой программе как excel 2010.

Задавайте вопросы, не стесняйтесь.

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

Рассмотрим, как добавить линию тренда на график в Excel.

Добавление линии тренда на график

Для примера возьмем средние цены на нефть с 2000 года из открытых источников. Данные для анализа внесем в таблицу:

Линия тренда в Excel – это график аппроксимирующей функции. Для чего он нужен – для составления прогнозов на основе статистических данных. С этой целью необходимо продлить линию и определить ее значения.

Если R2 = 1, то ошибка аппроксимации равняется нулю. В нашем примере выбор линейной аппроксимации дал низкую достоверность и плохой результат. Прогноз будет неточным.

Внимание!!! Линию тренда нельзя добавить следующим типам графиков и диаграмм:

• лепестковый;
• круговой;
• поверхностный;
• кольцевой;
• объемный;
• с накоплением.

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

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

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).

Получаем результат:

Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:

y = 4,503x + 6,1333

• где 4,503 – показатель наклона;
• 6,1333 – смещения;
• y – последовательность значений,
• х – номер периода.

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

Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.

Экспоненциальная линия тренда

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

Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

Строим график. Добавляем экспоненциальную линию.

Уравнение имеет следующий вид:

y = 7,6403е^-0,084x

• где 7,6403 и -0,084 – константы;
• е – основание натурального логарифма.

Показатель величины достоверности аппроксимации составил 0,938 – кривая соответствует данным, ошибка минимальна, прогнозы будут точными.

Логарифмическая линия тренда в Excel

Используется при следующих изменениях показателя: сначала быстрый рост или убывание, потом – относительная стабильность. Оптимизированная кривая хорошо адаптируется к подобному «поведению» величины. Логарифмический тренд подходит для прогнозирования продаж нового товара, который только вводится на рынок.

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

Построим график и добавим логарифмическую линию тренда для прогноза продаж условного продукта:

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Например:

Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

Полиномиальная линия тренда в Excel

Данной кривой свойственны переменные возрастание и убывание. Для полиномов (многочленов) определяется степень (по количеству максимальных и минимальных величин). К примеру, один экстремум (минимум и максимум) – это вторая степень, два экстремума – третья степень, три – четвертая.

Полиномиальный тренд в Excel применяется для анализа большого набора данных о нестабильной величине. Посмотрим на примере первого набора значений (цены на нефть).

Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

Скачать примеры графиков с линией тренда

Зато такой тренд позволяет составлять более-менее точные прогнозы.

Приветствую, уважаемые товарищи! Сегодня мы с вами разберем один из субъективных торговых методов – торговля с использованием трендовых линий. Давайте рассмотрим следующие вопросы:

1) Что такое тренд (это важно как отправная точка)
2) Построение трендовых линий
3) Использование в практической торговле
4) Субъективность метода

1) Что такое тренд
_________________
Прежде, чем перейти к построению трендовой линии, надо разобраться непосредственно с самим трендом. Не будем вдаваться в академические споры и для простоты примем следующую формулу:

Тренд (восходящий) – это последовательность растущих максимумов и минимумов, при этом каждый последующий максимум (и минимум) выше предыдущих.

Тренд (нисходящий) – это последовательность падающих (убывающих) максимумов и минимумов, где каждый последующий минимум (и максимум) НИЖЕ предыдущего.

Трендовая линия – это линия, проведенная между двумя максимумами (если тренд нисходящий) или двумя минимумами (если тренд восходящий). То есть, по сути, линия тренда показывает нам, что тренд на графике есть! А ведь его может и не быть (в случае с флетом).

2) Построение трендовых линий
____________________________

Это самый сложный вопрос! Мне доводилось видеть дискуссии на много страниц только о том, КАК ПРАВИЛЬНО строить линию тренда! А ведь нам надо не только строить, но и торговать по ней…

Что бы построить трендовую линию надо иметь, как минимум, два максимума (нисходящий тренд) или два минимума (восходящий тренд). Мы должны соединить эти экстремумы линией.

Важно соблюдать следующие правила при построении линий:

— Важен угол наклона линии тренда. Чем более крутой угол наклона, тем меньше надежность.
— Оптимально строить линию по двум точкам. Если строить по трем или более точкам – надежность трендовой линии снижается (вероятен ее пробой).
— Не пытайтесь построить линию в любых условиях. Если не удается ее начертить, значит, скорее всего, тренда нет. Следовательно, данный инструмент не годится к использованию в текущих рыночных условиях.

Данные правила помогут вам правильно строить трендовые линии!

3) Торговля по трендовым линиям
____________________________

Мы имеем две принципиально разные возможности:
А) Использовать линию как уровень поддержки (сопротивления), что бы войти по ней по направлению тренда
Б) Использовать трендовую линию Форекс для того, что бы сыграть на пробой (разворот) тренда.

Оба способа хороши, если уметь «правильно их готовить».

Итак, мы построили линию по двум точкам. Как только цена коснется линии, мы должны войти в рынок по направлению существующей тенденции. Для входа используем ордера типа «бай лимит или sell лимит».

Тут все просто и понятно. Единственное, что надо помнить – чем чаще цена тестирует линию тренда, отталкиваясь от нее, тем выше вероятность того, что следующее касание будет пробоем линии!

Если мы хотим сыграть на слом линии тренда, то надо действовать немного иначе:
1) Ждем касание линии
2) Ждем отскока
3) На образовавшуюся галочку ставим ордер бай-стоп (или sell стоп)
Обратите внимание на рисунок.

Мы дождались образования галочки и выставили ордер бай стоп на ее максимум.

Через некоторое время ордер сработал, и мы вошли в рынок.
Возникает закономерный вопрос – почему нельзя было войти в рынок сразу?
Дело в том, что мы не знаем, будет ли тестирование трендовой линии успешным или нет. А дождавшись «галочки» мы резко повышаем наши шансы на успех (отсеиваем ложные сигналы).

4) Субъективность метода
_________________________

Кажется все просто? На деле, используя данный метод, мы столкнемся со следующими трудностями:
А) Угол наклона линии (всегда можно построить линии тренда имеющие разный наклон.
Б) Что считать пробоем трендовой линии (насколько пунктов или процентов цена должна «переломить» линию, что бы считать это прорывом)?
В) Когда линию считать «устаревшей» и строить новую?

Обратите внимание на рисунок.

Красной линией обозначен один из вариантов начертания. Неопытный трейдер мог так провести линию (и поплатиться за это).

В данном деле важен практический опыт. То есть не удается все свести к нескольким простым правилам построения. Именно поэтому индикатора трендовых линий не существует. Точнее, может и существует, но строит их «криво» и неправильно. Эта техника изначально «заточена» под опыт и мастерство трейдера.

Лично я редко использую линии тренда как самостоятельный инструмент. Но, тем не менее, рассказываю о них по одной простой причине. Дело в том, что многие другие трейдеры используют их. Следовательно, мы (я и вы) должны быть в курсе техник наших конкурентов.

Нужен ли данный инструмент в вашей торговле – решать только вам!

Успехов и удачных торгов. Артур.
blog-forex.org

Похожие записи:

Концепция трендовой торговли (видео)

Трендовые модели (фигуры)

Видеоролик к данной теме:

Часть 10. Подбор формул по графику. Линия тренда

Предыдущая12345678910111213141516Следующая

Для рассмотренных выше задач удавалось построить уравнение или систему уравнений.

Но во многих случаях при решении практических задач имеются лишь экспериментальные (результаты измерений, статистические, справочные, опытные) данные. По ним с определенной мерой близости пытаются восстановить эмпирическую формулу (уравнение), которая может быть использована для поиска решения, моделирования, оценки решений, прогнозов.

Процесс подбора эмпирической формулы P(x) для опытной зависимости F(x) называется аппроксимацией (сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ.

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

Степень близости подбираемой функции оценивается коэффициентом детерминации R 2 . Если нет других теоретических соображений, то выбирают функцию с коэффициентом R 2 , стремящимся к 1. Отметим, что подбор формул с использованием линии тренда позволяет установить как вид эмпирической формулы, так и определить численные значения неизвестных параметров.

Excel предоставляет 5 видов аппроксимирующих функций:

1. Линейная – y=cx+b . Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.

2. Полиномиальная – y=c 0 +c 1 x+c 2 x 2 +…+c 6 x 6 . Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.

3. Логарифмическая – y=c lnx+b . Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.

4. Степенная – y=cx b , (х >0и y >0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.

5. Экспоненциальная – y=ce bx , (e – основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

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

Линия тренда представляет собой прямую или кривую линию, аппроксимирующую (приближающую) исходные данные на основе уравнения регрессии или скользящего среднего. Аппроксимация определяется по ме-тоду наименьших квадратов. В зависимости от характера поведения исходных данных (убыва-ют, возрастают и т.д.) выбирается метод интерполяции, который сле-дует использовать для построения тренда.

Предусмотрено несколько вариантов формирования линии трен-да.

Линейной функцией: y=mx+b

где m — тангенс угла наклона прямой, b — смещение.

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

Логарифмической функцией: y=c*ln⁡x+b

где с и b — константы.

Логарифмическая линия тренда соответствует ряду данных, значения которого вначале быстро растут или убывают, а затем постепенно стабилизируются. Может использоваться для положительных и отрицательных данных.

Полиномиальной функцией (до 6-й степени включительно): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6

где b, c 1 , c 2 , ... c 6 — константы.

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

Степенной функцией: y = cxb

где c и b — константы.

Степенная линия тренда дает хорошие результаты для положительных данных с постоянным ускорением. Для рядов с нулевыми или отрицательными значениями построение указанной линии трен-да невозможно.

Экспоненциальной функцией: y = cebx

где c и b — константы, е — основание натурального логарифма.

Экспоненциальный тренд используется в случае непрерывного возрастания изменения данных. Построение указанного тренда не- возможно, если в множестве значений членов ряда присутствуют нулевые или отрицательные данные.

С использованием линейной фильтрации по формуле: F t = (A t +A (t-1) +⋯+A (t-n+1))/n

где n — общее число членов ряда, t — заданное число точек (2 ≤ t < n).

Тренд с линейной фильтрацией позволяет сгладить колебания данных, наглядно демонстрируя характер зависимостей. Для построения указанной линии тренда пользователь должен задать число — параметр фильтра. Если задано число 2, то первая точка линии трен-да определяется как среднее значение из первых двух элементов данных, вторая точка — как среднее второго и третьего элементов данных и т.д.

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

Линия тренда, а также ее параметры добавляются к данным диа-граммы следующими командами:

При необходимости параметры линии можно изменить, вызвав щелчком мыши по ряду данных диаграммы или линии трен-да окно Формат линии тренда. Можно добавить (или удалить) урав-нение регрессии, коэффициент достоверности аппроксимации, оп-ределить направление и прогноз изменения ряда данных, а также выполнить коррекцию оформительских элементов линии тренда. Выделенная линия тренда может быть также удалена.

На рисунке приведена таблица данных по изменению стоимости ценной бумаги. На основе этих условных данных построена точечная диаграмма, добавлена поли-номиальная линия тренда третьего порядка (задана штриховой ли-нией) и некоторые другие параметры. Полученное значение коэф-фициента достоверности аппроксимации R 2 на диаграмме близко к единице, что свидетельствует о близости расчетной линии тренда с данными задачи. Прогнозное значение изменения стоимости ценной бумаги направлено в сторону роста.

Тренд - это закономерность описывающая подъем или падение показателя в динамике. Если изобразить любой динамический ряд (статистические данные, представляющие собой список зафиксированных значений изменяемого показателя во времени) на графике, часто выделяется определенный угол – кривая либо постепенно идет на увеличение или на уменьшение, в таких случаях принято говорить, что ряд динамики имеет тенденцию (к росту или падению соответственно).

Тренд как модель

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

Итак, что же собой представляет тренд как модель? Это совокупность расчетных коэффициентов уравнения, которые выражают регрессионную зависимость показателя (Y) от изменения времени (t). То есть, это точно такая же регрессия, как и те, что мы рассматривали ранее, только влияющим фактором здесь выступает именно показатель времени.

Важно!

В расчетах под t обычно подразумевается не год, номер месяца или недели, а именно порядковый номер периода в изучаемой статистической совокупности – динамическом ряде. К примеру, если динамический ряд изучается за несколько лет, а данные фиксировались ежемесячно, то использовать обнуляющуюся нумерацию месяцев, с 1 по 12 и опять сначала, в корне неверно. Также неверно в случае, если изучение ряда начинается, к примеру, с марта месяца в качестве значения t использовать 3 (третий месяц в году), если это первое значение в изучаемой совокупности, то его порядковый номер должен быть 1.

Модель линейного тренда

Как и любая другая регрессия, тренд может быть как линейным (степень влияющего фактора t равна 1) так и нелинейным (степень больше или меньше единицы). Так как линейная регрессия является самой простейшей, хотя далеко не всегда самой точной, то рассмотрим более детально именно этот тип тренда.

Общий вид уравнения линейного тренда:

Y(t) = a 0 + a 1 *t + Ɛ

Где a 0 – это нулевой коэффициент регрессии, то есть, то каким будет Y в случае, если влияющий фактор будет равен нулю, a 1 – коэффициент регрессии, который выражает степень зависимости исследуемого показателя Y от влияющего фактора t, Ɛ – случайная компонента или стандартная ошибка, по сути являет собой разницу между реально существующими значениями Y и расчетными. t – это единственный влияющий фактор – время.

Чем более выраженная тенденция роста показателя или его падения, тем будет больше коэффициент a 1 . Соответственно, предполагается, что константа a 0 совместно со случайной компонентой Ɛ отражают остальные регрессионные влияния, помимо времени, то есть всех прочих возможных влияющих факторов.

Рассчитать коэффициенты модели можно стандартным Методом наименьших квадратов (МНК). Со всеми этими расчетами Microsoft Excel справляется на ура самостоятельно, при чем, чтобы получить модель линейного тренда либо готовый прогноз существует целых пять способов, которые мы по отдельности разберем ниже.

Графический способ получения линейного тренда

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

Добавим к исходным данным еще один столбец, который назовем t и пометим цифрами по возрастающей порядковые номера всех зафиксированных значений ВВП за указанный период с 2004-го по 2012-й гг. – 9 лет или 9 периодов .

Эксель добавит пустое поле – разметку под будущий график, выделяем этот график и активируем появившуюся вкладку в панели меню – Конструктор , ищем кнопку Выбрать данные , в отрывшемся окне жмем кнопочку Добавить . Всплывшее окошко предложит выбрать данные для построения диаграммы. В качестве значения поля Имя ряда выбираем ячейку, которая содержит текст, наиболее полно отвечающий названию графика. В поле Значения X указываем интервал ячеек стобца t – влияющего фактора. В поле Значения Y указываем интервал ячеек столбца с известными значениями ВВП (Y) – исследуемого показателя.

Заполнив указанные поля, несколько раз нажимаем кнопку ОК и получаем готовый график динамики. Теперь выделяем правой кнопкой мыши саму линию графика и из появившегося контекстного меню выбираем пункт Добавить линию тренда

Откроется окошко для настройки параметров построения линии тренда, где среди типов моделей выбираем Линейная , ставим галочки напротив пунктов Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации R2 , этого будет достаточно чтобы на графике отобразилась уже построенная линия тренда, а также математический вариант отображения модели в виде готового уравнения и показатель качества модели R 2 . Если вас интересует отображение на графике прогноза, чтобы визуально оценить отрыв исследуемого показателя укажите в поле Прогноз вперед на количество интересующих периодов.

Собственно это все, что касается этого способа, можно конечно добавить, что отображаемое уравнение линейного тренда это и есть непосредственно сама модель, которую можно использовать, в качестве формулы, чтобы получить расчетные значения по модели и соответственно точные значения прогноза (прогноз отображаемый на графике, оценить можно лишь приблизительно), что мы и сделали в приложенному к статье примере.

Построение линейного тренда с помощью формулы ЛИНЕЙН

Суть этого метода сводится к поиску коэффициентов линейного тренда с помощью функции ЛИНЕЙН , затем, подставляя эти влияющие коэффициенты в уравнение, получим прогнозную модель.

Нам потребуется выделить две рядом стоящие ячейки (на скриншоте это ячейки A38 и B38), далее в строке формул вверху (выделено красным на скриншоте выше) вызываем функцию, написав «=ЛИНЕЙН(», после чего эксель выведет подсказки того, что требуется для этой функции, а именно:

1. выделяем диапазон с известными значениями описываемого показателя Y (в нашем случае ВВП, на скриншоте диапазон выделен синим) и ставим точку с запятой
2. указываем диапазон влияющих факторов X (в нашем случае это показатель t, порядковый номер периодов, на скриншоте выделено зеленым) и ставим точку с запятой
3. следующий по порядку требуемый параметр для функции – это определение того нужно ли рассчитывать константу, так как мы изначально рассматриваем модель с константой (коэффициент a 0 ), то ставим либо «ИСТИНА» либо «1» и точку с запятой
4. далее нужно указать требуется ли расчет параметров статистики (в случае, если бы мы рассматривали этот вариант, то изначально пришлось бы выделить диапазон «под формулу» на несколько строк ниже). Указывать необходимость расчета параметров статистики, а именно стандартного значение ошибки для коэффициентов, коэффициента детерминированности, стандартной ошибки для Y, критерия Фишера, степеней свободы и пр. , есть смысл только тогда, когда вы понимаете, что они означают, в этом случае ставим либо «ИСТИНА», либо «1». В случае упрощенного моделирования, которому мы пытаемся научиться, на этом этапе прописывания формулы, ставим «ЛОЖЬ» либо «0» и добавляем после закрывающую скобочку «)»
5. чтобы «оживить» формулу, то есть заставить ее работать после прописывания всех необходимых параметров, не достаточно нажать кнопку Enter, необходимо последовательно зажать три клавиши: Ctrl, Shift, Enter

Как видим на скриншоте выше, выделенные нами под формулу ячейки заполнились расчетными значениями коэффициентов регрессии для линейного тренда, в ячейке B38 находится коэффициент a 0 , а в ячейке A38 - коэффициент зависимости от параметра t (или x ), то есть a 1 . Подставляем полученные значения в уравнение линейной функции и получаем готовую модель в математическом выражении – y = 169 572,2+138 454,3*t

Чтобы получить расчетные значения Y по модели и, соответственно, чтобы получить прогноз, нужно просто подставить формулу в ячейку экселя, а вместо t указать ссылку на ячейку с требуемым номером периода (смотрите на скриншоте ячейку D25 ).

Для сравнения полученной модели с реальными данными, можно построить два графика, где в качестве Х указать порядковый номер периода, а в качестве Y в одном случае – реальный ВВП, а, в другом – расчетный (на скриншоте диаграмма справа).

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

В статье , по сути, полностью описан этот метод, единственная же разница в том, что в наших исходных данных только один влияющий фактор Х (номер периода – t ).

Как видно на рисунке выше, диапазон данных с известными значениями ВВП выделен как входной интервал Y , а соответствующий ему диапазон с номерами периодов t – как входной интервал Х . Итоги расчетов Пакетом анализа выносятся на отдельный лист и выглядит как набор таблиц (см. рисунок ниже) на котором нас интересуют ячейки, которые были закрашены мною в желтый и зеленый цвета. По аналогии с порядком, расписанным в указанной выше статье, из полученных коэффициентов собирается модель линейного тренда y=169 572,2+138 454,3*t , на основе которой и делаются прогнозы.

Прогнозирование с помощью линейного тренда через функцию ТЕНДЕНЦИЯ

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

В целевую ячейку (ту ячейку, где хотим видеть результат) ставим знак равно и вызываем волшебную функцию, прописав «ТЕНДЕНЦИЯ(», далее необходимо выделить , то есть , после ставим точку с запятой и выделяем диапазон с известными значениями Х, то есть с номерами периодов t , которые соответствуют столбцу с известными значениями ВВП, опять ставим точку с запятой и выделяем ячейку с номером периода, для которого мы делаем прогноз (правда, в нашем случае, номер периода можно указать не ссылкой на ячейку, а просто цифрой прямо в формуле), далее ставим еще одну точку с запятой и указываем ИСТИНА или 1 , в качестве подтверждения для расчета коэффициента a 0 , наконец, ставим закрывающую скобочку и нажимаем клавишу Enter .

Минус данного метода в том, что он не показывает ни уравнения модели, ни его коэффициентов, из-за чего нельзя сказать, что на основе такой-то модели мы получили такой-то прогноз, также как и нет какого-либо отражения параметров качества модели, того таки коэффициента детерминации, по которому можно было бы сказать имеет ли смысл брать во внимание полученный прогноз или нет.

Прогнозирование с помощью линейного тренда через функцию ПРЕДСКАЗ

Суть данной функции целиком и полностью идентична предыдущей, разница лишь в порядке прописывания исходных данных в формуле и в том, что нет настройки для наличия или отсутствия коэффициента a 0 (то есть функция подразумевает, что этот коэффициент, в любом случае, есть)

Как видно с рисунка выше, в целевую ячейку прописываем «=ПРЕДСКАЗ(» и затем указываем ячейку с номером периода , для которого необходимо просчитать значение по линейному тренду, то есть прогноз, после ставим точку с запятой, далее выделяем диапазон известных значений Y , то есть столбец с известными значениями ВВП , после ставим точку с запятой и выделяем диапазон с известными значениями Х , то есть с номерами периодов t , которые соответствуют столбцу с известными значениями ВВП и, наконец, ставим закрывающую скобочку и жмем клавишу Enter .

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

Подводя итог к статье

Можно сказать, что каждый из методов может быть наиболее приемлемым среди прочих в зависимости от текущей цели, которую мы ставим перед собой. Первые три метода пересекаются между собой как по смыслу, так и по результату, и годятся для любой более или менее серьезной работы, где необходимо описание модели и ее качества. В свою очередь, последние два метода также идентичны между собой и максимально быстро вам дадут ответ, например, на вопрос: «Какой прогноз продаж на следующий год?».