Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анализа, позволяющий:
· строить на основе метода наименьших квадратов и добавлять в диаграмму пять типов регрессий, которые с той или иной степенью точности моделируют исследуемый процесс;
· добавлять к диаграмме уравнение построенной регрессии;
· определять степень соответствия выбранной регрессии отображаемым на диаграмме данным.
На основе данных диаграммы Excel позволяет получать линейный, полиномиальный, логарифмический, степенной, экспоненциальный типы регрессий, которые задаются уравнением
где x – независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).
1. Линейная регрессия хороша при моделировании характеристик, значения которых увеличиваются или убывают с постоянной скоростью. Это наиболее простая в построении модель исследуемого процесса. Она строится в соответствии с уравнением
Прогнозирование в Excel с помощью линий тренда
где m – тангенс угла наклона линейной регрессии к оси абсцисс; b – координата точки пересечения линейной регрессии с осью ординат.
2. Полиномиальная линия тренда полезна для описания характеристик, имеющих несколько ярко выраженных экстремумов (максимумов и минимумов). Выбор степени полинома определяется количеством экстремумов исследуемой характеристики. Так, полином второй степени может хорошо описать процесс, имеющий только один максимум или минимум; полином третьей степени – не более двух экстремумов; полином четвертой степени – не более трех экстремумов и т. д.
В этом случае линия тренда строится в соответствии с уравнением
где коэффициенты c0, c1, c2. c6 – константы, значения которых определяются в ходе построения.
3. Логарифмическая линия тренда с успехом применяется при моделировании характеристик, значения которых вначале быстро меняются, а затем постепенно стабилизируются.
Данная линия тренда строится в соответствии с уравнением
где коэффициенты b, с – константы.
4. Степенная линия тренда дает хорошие результаты, если значения исследуемой зависимости характеризуются постоянным изменением скорости роста. Примером такой зависимости может служить график равноускоренного движения автомобиля. Если среди данных встречаются нулевые или отрицательные значения, использовать степенную линию тренда нельзя.
Линия строится в соответствии с уравнением
где коэффициенты b, с – константы.
5. Экспоненциальную линию тренда следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Для данных, содержащих нулевые или отрицательные значения, этот вид приближения также неприменим.
Линия тренда строится в соответствии с уравнением
где коэффициенты b, с – константы.
Трендом называется общая тенденция изменения данных в зависимости от времени.
При подборе линии тренда Excel автоматически рассчитывает значение величины R2 (коэффициент детерминации), которая характеризует достоверность аппроксимации (степень близости линии тренда к исходным данным): чем ближе значение R2 к единице, тем надежнее линия тренда аппроксимирует исследуемый процесс. При необходимости значение R2 всегда можно отобразить на диаграмме.
Для добавления линии тренда к ряду данных следует:
· активизировать построенную на основе ряда данных диаграмму, т. е. щелкнуть в пределах области диаграммы. В главном меню появится пункт Диаграмма;
· после щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.
Эти же действия легко реализуются, если навести указатель мыши на график, соответствующий одному из рядов данных, и щелкнуть правой кнопкой мыши; в появившемся контекстном меню выбрать команду Добавить линию тренда. На экране появится диалоговое окно Линия тренда с раскрытой вкладкой Тип (рис. 4.1).
Рис. 4.1. Определение линии тренда (функции)
После этого необходимо выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень следует задать степень выбранного полинома.
В поле Построен на ряде перечислены все ряды данных рассматриваемой диаграммы. Для добавления линии тренда к конкретному ряду данных следует в поле Построен на ряде выбрать его имя.
Рис. 4.2. Выбор параметров
При необходимости, перейдя на вкладку Параметры (рис. 4.2), можно для линии тренда задать следующие параметры:
1) изменить название линии тренда в поле Название аппроксимирующей (сглаженной) кривой;
2) задать количество периодов (вперед или назад) для прогноза в поле Прогноз.
Прогнозирование заключается в том, чтобы каким-либо образом определить значение Y0 переменной Y при таком наборе значений и факторов t0, x10, x20,…, xm0, которого нет в исходной таблице данных.
Временной шаг, с которым представлены в таблице данные, называется шагом, или периодом, прогнозирования;
3) вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;
4) вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);
5) задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке.
Для того чтобы начать редактирование уже построенной линии тренда, существует три способа.
1. Воспользоваться командой Выделенная линия тренда из меню Формат, предварительно выбрав линию тренда.
2. Выбрать команду Формат линии тренда из контекстного меню, которое вызывается щелчком правой кнопки мыши по линии тренда.
3. Двойным щелчком по линии тренда.
Рис. 4.3. Определение формата линии тренда
На экране появится диалоговое окно Формат линии тренда (рис. 4.3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (см. рис. 4.1, 4.2). На вкладке Вид можно задать тип линии, ее цвет и толщину.
Задание 1. С таблицей данных о прибыли автотранспортного предприятия за 2000–2007 гг. (табл. 4.1) необходимо выполнить следующие действия:
1) построить диаграмму;
2) в диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда;
3) вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них;
4) используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 2000–2009 гг.;
5) составить прогноз по прибыли предприятия на 2008 и 2009 гг.
Исходные данные
| № п/п | Год | Прибыль, тыс. руб. |
| 159 500 | ||
| 167 800 | ||
| 165 400 | ||
| 178 600 | ||
| 178 900 | ||
| 184 700 | ||
| 184 000 | ||
| 185 500 | ||
| ? | ||
| ? |
Методика выполнения
1. В диапазон ячеек A3:C13 рабочего листа Excel вводим исходную таблицу (рис. 4.4).
2. Выделив диапазон ячеек В4:С11, строим диаграмму.
3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры, в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки Показывать уравнение на экране и Поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда.
Рис. 4.4. Исходная таблица
4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 2000–2007 гг. воспользуемся уравнениями линий тренда, представленными на рис. 4.5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд.
Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13.
Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии – диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2008 и 2009 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 4.6.
Рис. 4.5. Динамика прибыли предприятия, линейная,
квадратичная и кубическая линии тренда
Рис. 4.6. Прогнозирование прибыли предприятия
Задание для самостоятельной работы
С таблицей данных о прибыли автотранспортного предприятия за 2000–2007 гг. (см. табл. 4.1), приведенной в задаче 1, необходимо выполнить следующие действия.
1) построить диаграмму, в которую необходимо добавить логарифмическую, степенную и экспоненциальную линии тренда;
2) вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них;
3) используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 2000–2007 гг.;
4) составить прогноз о прибыли предприятия на 2008 и 2009 гг., используя эти линии тренда.
Что способствует осуществлению желаний? Стопроцентная, непоколебимая уверенность в своем.
ЧТО ТАКОЕ УВЕРЕННОЕ ПОВЕДЕНИЕ В МЕЖЛИЧНОСТНЫХ ОТНОШЕНИЯХ? Исторически существует три основных модели различий, существующих между.
Что делает отдел по эксплуатации и сопровождению ИС? Отвечает за сохранность данных (расписания копирования, копирование и пр.).
ЧТО ПРОИСХОДИТ ВО ВЗРОСЛОЙ ЖИЗНИ? Если вы все еще «неправильно» связаны с матерью, вы избегаете отделения и независимого взрослого существования.
Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте:
Источник: zdamsam.ru
Как построить линию тренда?
Своим возникновением технический анализ обязан теории Чарльза Генри Доу. В начале ХХ века для анализа рынка использовались только трендовые линии. Прошло более 100 лет, а метод анализа с помощью трендовых линий остаётся эффективным и актуальным по-прежнему. Гарантом успешности любой торговой операции на всех тайм-фреймах и видах рынков является умение построить линии тренда.
Линия тренда – инструмент в техническом анализе, предназначенный для определения направления движения тренда, идентификации участков замедления, изменения направления и разворота. Трендовая линия помогает определять тип тренда (бычий, медвежий или боковой), сильный или слабый тренд, произойдёт ли изменение направления движения цены и через какой временной интервал.
Как построить линию тренда
В данной статье рассматриваем, как провести линии тренда для восходящего и нисходящего движения.
- Восходящий тренд – последовательность более высоких максимумов и минимумов.
- Нисходящий тренд (или медвежий) – последовательность более низких максимумов и минимумов.
Ничего сложного в построении линии тренда на самом деле нет, просто нужно тщательно придерживаться основных правил построения трендовых линий. При этом опытный трейдер должен уметь, взглянув на график, определять значимые максимумы и минимумы.
- Для построения трендовой линии бычьего тренда нужно соединить два минимума. Первая опорная точка – самый первый низкий минимум. Вторая точка – соседний с первым минимум.
- Для построения линии медвежьего тренда надо соединить два максимума. Первая опорная точка – самый первый и высокий максимум. Вторая точка – соседний максимум.
- Линию можно построить, соединив фитили свечей – экстремумы. А можно соединить значения открытия и закрытия свечей. Есть способ: максимально уменьшить график терминала и соединить экстремумы так, чтобы получить наибольшее число касаний. Какой же способ является правильным? Каждый трейдер выбирает такой метод, который удобен именно ему. Только после того, как выбор сделан, нужно чётко придерживаться выбранного способа.
Трендовая линия построена, но график со временем изменяется и линия как будто теряет свою актуальность. Это не совсем так, линию после пробоя удалять не нужно, а достаточно просто откорректировать.
Торговля по линиям тренда
Для восходящего тренда уровень поддержки – это трендовая линия, соединяющая последовательно расположенные минимумы. А уровнем сопротивления является параллельная ей линия.
Для нисходящего тренда: уровень поддержки – трендовая линия, соединяющая последовательно расположенные максимумы. Уровень сопротивления – линия, которая ей параллельна.
В техническом анализе линии тренда являются уровнями поддержки и сопротивления. Уровень поддержки нужен для того, чтобы открывать сделки на покупку или закрывать ордера на продажу. Уровень сопротивления – чтобы от него открывать ордера на продажу или фиксировать прибыль от покупки.
Бывшая линия поддержки, которая стала линией сопротивления, более значима и нуждается в тщательном внимании.
Пробой линии тренда
- Пробой трендовой линии сигнализирует о смене действующего движения. Но не говорит о развороте долгосрочного тренда. И не является толчком к открытию сделок. Информацию о развороте нужно получать от других более существенных факторов. График может пересечь линию тренда, а затем развернуться и пересечь линию в противоположном направлении. Можно встретить огромное количество подобных примеров.
- Линия тренда помогает определить начало нового или остановку действующего движения тренда.
- Наклонная линия не показывает чёткого значения для открытия или закрытия ордера. В этом помогают инструменты компьютерного анализа, которые вместе с трендовыми линиями покажут результат.
Факторы определения важности линии тренда:
- Таймфрейм. Тренд недельного таймфрейма считается более главным, чем дневного, а дневного таймфрейма – важнее, чем часового и так далее.
- Число касаний. Чем больше было касаний графиком цены трендовой линии, тем более стабилен и надёжен тренд.
- Продолжительность. Линия, существующая месяцы, надёжнее той, которая появилась несколько часов назад.
- Угол наклона. Чем ближе угол между трендовой линией и горизонталью к значению прямого угла, тем сильнее тренд. Если угол наклона близок к нулю, то тренд является слабым, значит произойдёт или разворот цены, или переход во флэт. Угол 45 градусов – показатель того, что возможно, разовьётся сильный и протяжённый тренд.
Для технического анализа нужно набраться опыта, поэтому крайне важно практиковаться в проведении трендовых линий. Бывает, что для правильного их построения нужно полгода ежедневных тренировок.
Есть очень хорошее упражнение. Нужно открывать графики любых торговых инструментов, отматывать график назад и строить линии тренда, при этом тщательно обосновывая и обдумывая сколько времени линия существовала, сколько раз цена отбивалась или пересекала линию, насколько далеко отходила цена от линии тренда после пробоя и так далее.
После анализа нескольких сотен графиков можно стать настоящим аналитиком в этой сфере.
Понравилась статья про фондовый рынок, форекс, акции, инвестиции в бизнес? — Хорошо, поделитесь ссылкой в соц. сетях, расскажите и друзьям — это лучшая благодарность нам! Поставьте свою оценку этой статье — Как построить линию тренда?, ПРОГОЛОСУЙТЕ за полезность — это анонимно и всем видно.
Источник: biznes-prost.ru
5 способов расчета значений линейного тренда в MS Excel
Это первая статья из серии «Как самостоятельно рассчитать прогноз продаж с учетом роста и сезонности», из которой вы узнаете о 5 способах расчета значений линейного тренда в Excel.
Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:
- Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
- Расчет сезонности;
- Расчет прогноза;
После изучения данного материала вы сможете выбрать оптимальный способ расчета значений линейного тренда, который будет удобен для решения вашей задачи, а в последствии, и для расчета прогноза наиболее удобным для вас способом.
Линейный тренд хорошо применять для временного ряда, данные которого увеличиваются или убывают с постоянной скоростью.
Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.
Временной ряд продажи по месяцам (см. вложенный файл).
В этом временном ряду у нас есть 2 переменных:
- Время — месяцы;
- Объём продаж;
Уравнение линейного тренда y(x)=a+bx, где
y — это объёмы продаж
x — номер периода (порядковый номер месяца)
a – точка пересечения с осью y на графике (минимальный уровень);
b – это значение, на которое увеличивается следующее значение временного ряда;
1-й способ расчета значений линейного тренда в Excel с помощью графика
Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У — объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044
Для прогнозирования нам необходимо рассчитать значения линейного тренда, как для анализируемых значений, так и для будущих периодов.
При расчете значений линейного тренде нам будут известны:
- Время — значение по оси Х;
- Значение «a» и «b» уравнения линейного тренда y(x)=a+bx;
Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.
Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551
27-го y=135134*27+4594044=8242686
2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН
1. Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel:
=ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)
Для расчета коэффициентов в формулу вводим
- известные значения y (объёмы продаж за периоды),
- известные значения x (номера периодов),
- вместо константы ставим 1,
- вместо статистики 0,
Получаем 135135 — значение (b) линейного тренда y=a+bx;
Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо
- установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке;
- нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.
Получаем 135135, 4594044 — значение (b) и (a) линейного тренда y=a+bx;
2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов . Подставляем в уравнение y=135134*x+4594044 номера периодов — x, для которых хотим рассчитать значения линейного тренда.
2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также быстрее.
3-й способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ
Рассчитаем значения линейного тренда с помощью стандартной функции Excel:
=ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)
Подставляем в формулу
- известные значения y — это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
- известные значения x — это номера периодов x для известных значений объёмов продаж y;
- новые значения x — это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
- константа — ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;
Для того чтобы рассчитать значения тренда для всего временного диапазона, в «новые значения x» вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.
4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ
Рассчитаем значения линейного тренда с помощью стандартной функции Excel:
=ПРЕДСКАЗ(x; известные значения y; известные значения x)
Вместо X поставляем номер периода, для которого рассчитываем значение тренда.
Вместо «известные значения y» — объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
«известные значения x» — это номера периодов для каждого выделенного объёма продаж.
3-й и 4-й способ расчета значений линейного тренда быстрее, чем 1 и 2-й, однако с его помощью невозможно управлять коэффициентами тренда, как описано в статье «О линейном тренде».
5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO
1. Устанавливаем курсор в начало временного ряда, выбираем в настройках программы:
— Что рассчитываем — значения тренда;
— Тренд — Линейный тренд;
— Временной ряд — месячный;
и сохраняем;
2. Заходим в меню программы и нажимаем «Start_Forecast». Значения линейного тренда рассчитаны.
Для расчета прогноза осталось применить к значениям трендов будущих периодов коэффициенты сезонности, и прогноз продаж с учетом роста и сезонности готов.
В следующих статье «Как самостоятельно сделать прогноз продаж с учетом роста и сезонности» мы:
- рассчитаем коэффициенты сезонности,очищенные от роста и выровненные;
- сделаем прогноз;
О том, что еще важно знать о линейном тренде, вы можете узнать в статье «Что важно знать о линейном тренде».
Точных вам прогнозов!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel .
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.
Зарегистрируйтесь и скачайте решения
Статья полезная? Поделитесь с друзьями
Комментарии
1 2 3 4 5
#41 Алексей Батурин 27.12.2017 09:20
Сергей Иванович, добрый день!
С наступающим Новым Годом! Удачи и счастья в Новом Году!
По поводу формулы =ЛИНЕЙН()
Она очень гибкая в плане настроек и можно сделать все что хотите.
Посмотрите, пожалуйста. пример изменения формулы в этой статье:
https://4analytics.ru/trendi/3-sposoba-rascheta-polinoma-v-excel.html
Цитирую Сергей Иванович К.:
Алексей, добрый день и с наступающим Новым Годом!
У меня давно есть вопрос про линию тренда в Excel. Многие реальные процессы, хорошо описываемые экспонентой, асимптотически стремятся (или растут) не обязательно к (из) оси абсцисс. Линия тренда, построенная в Excel при этом очень сильно отклоняется от реального графика, ибо асимптотически стремится именно к нулю («растёт» из нуля). Как быть? Нет ли каких то усовершенствований, чтобы можно было использовать формулу вида y=a+b*e^c*x, а то и ещё более универсальную — y=a+b*х+c*e^d*x, т.е. чтобы асимптота могла быть и наклонной?
С уважением, Сергей.
#40 Сергей Иванович К. 26.12.2017 20:48
Алексей, добрый день и с наступающим Новым Годом!
У меня давно есть вопрос про линию тренда в Excel. Многие реальные процессы, хорошо описываемые экспонентой, асимптотически стремятся (или растут) не обязательно к (из) оси абсцисс. Линия тренда, построенная в Excel при этом очень сильно отклоняется от реального графика, ибо асимптотически стремится именно к нулю («растёт» из нуля). Как быть? Нет ли каких то усовершенствова ний, чтобы можно было использовать формулу вида y=a+b*e^c*x, а то и ещё более универсальную — y=a+b*х+c*e^d*x , т.е. чтобы асимптота могла быть и наклонной?
С уважением, Сергей.
#39 Алексей Батурин 04.11.2016 05:58
Цитирую Сергей:
вечер добрый, почему вы используете формулу тренда y(x)=a+bx? обычно используют ax+b
Сергей, доброе утро. Так захотелось, чтобы думали, а не просто копировали. В разных источниках по разному, есть и mx+b, у нас вот так ) Главное суть!
Источник: 4analytics.ru