5 способов расчета значений линейного тренда в ms excel

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

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

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

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

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

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

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

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

Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:. y = 4,503x + 6,1333

y = 4,503x + 6,1333

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Период 14 15 16 17 18 19 20
Прогноз 1005,4 1024,18 1041,74 1058,24 1073,8 1088,51 1102,47

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

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

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

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

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

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

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

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

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

Построение графика

Чтобы правильно строить трендовые линии, нужно соблюдать функциональную зависимость y=f(x) . Для получения корректного прогноза в столбец А вносится информация о временном периоде, а в столбец В — цена в указанный промежуток.

Построение графика выполняется по следующему алгоритму:

  1. Первым действием нужно выделить диапазон данных , например это А1:В9, затем активировать инструмент: «Вставка»-«Диаграммы»-«Точечная»-«Точечная с гладкими кривыми и маркерами».
  2. После открытия графика пользователю станет доступна еще одна панель управления данными , на которой нужно выбрать следующее: «Работа с диаграммами»-«Макет»-«Линия тренда»-«Линейное приближение».
  3. Следующим шагом требуется выполнить двойной клик по образовавшейся линии тенденции в Excel . Когда появиться вспомогательное окно, отметить птичкой опцию «показывать уравнение на диаграмме».

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

Создание линии

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

Добавление тренда на диаграмму происходит следующим образом:

  1. Перейти во вкладку «Работа с диаграммами» , затем выбрать раздел «Макет»-«Анализ» и после подпункт «Линия тенденции» . Появится выпадающий список, в котором необходимо активировать строку «Линейное приближение».
  2. Если все выполнено правильно, в области построения диаграмм появится кривая линия черного цвета . По желанию цветовую гамму можно будет изменить на любую другую.

Этот способ поможет создать и построить тренд в Excel 2021 или более ранних версиях.

Однако важно помнить, что вставить линию нельзя для диаграмм и графиков следующего типа:

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

Настройка линии

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

Необходимо запомнить следующее:

Прогнозирование

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

Для этого выполняем последовательность действий:

  1. Вызвать для графика контекстное меню и выбрать «Изменить тип диаграммы» .
  2. Появится новое окно с настройками , в котором требуется найти опцию «Гистограмма» и после выбрать подвид с группировкой.

Теперь пользователю должны быть видны оба графика . Они визуализируют одни и те же данные, но имеют разные уравнения для образования тенденции.

Следующим шагом необходимо сравнить уравнения точки пересечения с осями на разных диаграммах .

Для визуального отображения нужно сделать следующее:

  1. Перевести гистограмму в простой точечный график с гладкими кривыми и маркерами . Процесс выполняется через пункт контекстного меню «Изменить тип диаграммы…».
  2. Выполнить двойной клик по прямой образовавшейся тенденции , задать ей параметр прогноза назад на 12,0 и сохранить изменения.

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

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

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

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

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

Построение диаграммы будет осуществляться с опорой на эти данные. Нужно просто выполнить некоторые действия, а именно:

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

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

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

Этот тип линии тренда знаком сейчас многим. По этому сценарию развиваются все пандемии, а также ряд других процессов. Характерная особенность этого вида линии тренда – цифры постоянно возрастают в геометрической прогрессии. Например, 1,2,4,8 и так далее. По похожему сценарию как раз и развиваются эпидемии, поскольку чем больше больных, тем большему количеству людей может передаться заболевание.

Мы попробуем абстрагироваться от неприятных вещей и перейдем на другие сферы, например, бизнес.

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

1

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

2

Достоверность линии тренда в рассматриваемом нами примере составляет 0,938, что говорит, что вероятность ошибки довольно низкая. Следовательно, прогнозам можно доверять. 

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

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

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

Давайте сделаем к этому примеру такой график.

3

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

Как вариант, возможна следующая таблица с прогнозами.

4

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

Полиномиальная линия тренда в Эксель

Эта линия тренда характерна для волатильных (изменчивых) показателей. Очень хорошо его использовать для торговли криптовалютами или другими высоко рисковыми активами.

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

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

5

Линия тренда в Excel

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

Построение графика

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

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

Выделяем данную таблицу. Переходим во вкладку «Вставка». Там на ленте в блоке инструментов «Диаграммы» кликаем по кнопке «График». Из представленного списка выбираем самый первый вариант.

После этого график будет построен, но его нужно ещё доработать. Делаем заголовок графика. Для этого кликаем по нему. В появившейся группе вкладок «Работа с диаграммами» переходим во вкладку «Макет». В ней кликаем по кнопке «Название диаграммы». В открывшемся списке выбираем пункт «Над диаграммой».

В появившееся поле над графиком вписываем то название, которое считаем подходящим.

Затем подписываем оси. В той же вкладке «Макет» кликаем по кнопке на ленте «Названия осей». Последовательно переходим по пунктам «Название основной горизонтальной оси» и «Название под осью».

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

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

В появившемся поле наименования вертикальной оси вписываем нужное название.

Урок: Как сделать график в Excel

Создание линии тренда

Теперь нужно непосредственно добавить линию тренда.

  1. Находясь во вкладке «Макет» кликаем по кнопке «Линия тренда», которая расположена в блоке инструментов «Анализ». Из открывшегося списка выбираем пункт «Экспоненциальное приближение» или «Линейное приближение».

После этого, линия тренда добавляется на график. По умолчанию она имеет черный цвет.

Настройка линии тренда

Имеется возможность дополнительной настройки линии.

  1. Последовательно переходим во вкладке «Макет» по пунктам меню «Анализ», «Линия тренда» и «Дополнительные параметры линии тренда…».

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

  • Полиномиальная;
  • Линейная;
  • Степенная;
  • Логарифмическая;
  • Экспоненциальная;
  • Линейная фильтрация.

Для того, чтобы определить достоверность нашей модели, устанавливаем галочку около пункта «Поместить на диаграмму величину достоверности аппроксимации». Чтобы посмотреть результат, жмем на кнопку «Закрыть».

Если данный показатель равен 1, то модель максимально достоверна. Чем дальше уровень от единицы, тем меньше достоверность.

Если вас не удовлетворяет уровень достоверности, то можете вернуться опять в параметры и сменить тип сглаживания и аппроксимации. Затем, сформировать коэффициент заново.

Прогнозирование

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

  1. Опять переходим в параметры. В блоке настроек «Прогноз» в соответствующих полях указываем насколько периодов вперед или назад нужно продолжить линию тренда для прогнозирования. Жмем на кнопку «Закрыть».

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

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Показать значение R-Squared

Значение R-квадрата – это число, которое показывает, насколько хорошо ваша линия тренда соответствует вашим данным. Чем ближе значение R-квадрата к 1, тем лучше подходит линия тренда.

На панели «Форматировать линию тренда» щелкните категорию «Параметры линии тренда», а затем установите флажок «Отображать значение R-квадрата на графике».

Значение 0,81 показано. Это разумное соответствие, так как значение выше 0,75 обычно считается достойным – чем ближе к 1, тем лучше.

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

Метод экспоненциального сглаживания.

Альтернативный подход к сокращению разброса значений ряда состоит в использовании метода экспоненциального сглаживания. Метод получил название «экспоненциальное сглаживание» в связи с тем, что каждое значение периодов, уходящих в прошлое, уменьшается на множитель (1 – α).

Каждое сглаженное значение рассчитывается по формуле вида:

St =aYt +(1−α)St−1,

где St – текущее сглаженное значение; Yt – текущее значение временного ряда; St – 1 – предыдущее сглаженное значение; α – сглаживающая константа, 0 ≤ α ≤ 1.

Чем меньше значение константы α , тем менее оно чувствительно к изменениям тренда в данном временном ряду.

Вставка точечной диаграммы в Excel

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

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

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

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

Добавить линию тренда в Excel

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

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

Теперь вы должны смотреть на Формат Trendline окно. Это окно содержит много опций для добавления линии тренда в график рассеяния Excel.

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

Нажмите на близко Кнопка и ваш график теперь должны отображать линию тренда линейной регрессии

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

Как и во всех вещах Microsoft Office, вы можете отформатировать свою линию тренда так, чтобы она выглядела именно так, как вы хотите. В следующем разделе мы обсудим некоторые наиболее популярные изменения, которые вы можете внести в свою линию тренда, чтобы она выделялась.

Форматирование Excel Trendline

Чтобы отформатировать вновь созданную линию тренда, начните с щелчка правой кнопкой мыши по линии и выбора Формат Trendline из меню. Excel снова откроет Формат Trendline панель.

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

Допустим, мы хотим, чтобы наша линия тренда отображалась более четко на графике. В конце концов, линия тренда по умолчанию имеет ширину всего в один пиксель и может иногда исчезать среди цветов и других элементов на графике. На левой стороне Формат Trendline нажмите на Fill & Line значок.

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

Когда вы закончите, нажмите Икс кнопка на Формат Trendline панель и обратите внимание на изменения вашего точечного графика. Обратите внимание, что уравнение линии и значения R-квадрата теперь отображаются на графике и что линия тренда является более заметным элементом диаграммы

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

Вы можете изменить цвет и толщину линии и даже добавить к ней 3D-элементы, такие как эффект затенения (нажмите на Последствия значок).

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

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

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

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

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

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

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

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

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

Как построить линию тренда в MS Excel

Зачем нужны диаграммы? Чтобы «сделать красиво»? Вовсе нет — главная задача диаграммы позволить представить малопонятные цифры в удобном для усвоения графическом виде. Чтобы с одного взгляда было понятно состояние дел, и не было необходимости тратить время на изучение сухой статистики.

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

Как диаграммы и графики нас обманывают

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

Проиллюстрирую свои слова простейшим примером:

Диаграмма построенная на основе таблицы в MS Excel

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

Не менее логично и то, что чем больше страниц просматривает посетитель, тем лучше сайт — он захватывает внимание пользователя и заставляет его углубиться в чтение

Возможности инструмента

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

Окно содержит четыре настройки, в которые входят цвет, объем и тип линии, а также параметры самого инструмента.

Параметры линии тренда можно условно поделить на четыре блока:

  1. Тип приближения.
  2. Название полученной кривой, которое формируется автоматически или может быть задано пользователем.
  3. Блок прогнозирования, который позволяет продлить линию тренда на заданное количество периодов вперед или назад, на основании имеющихся данных. Что позволяет оценить дальнейшее изменение исследуемой величины.
  4. Дополнительные опции, которые отражают математическую составляющую кривой. Самой интересной и полезной строчкой здесь является величина достоверности. Если значение коэффициента близко к единице, то ошибка минимальна и дальнейший прогноз будет достаточно точным.

Выведем на исходный график уравнение линии и коэффициент достоверности.

Как видите, значение близко к 0,5, это говорит о низкой достоверности полученной линии тренда, и дальнейший прогноз будет ошибочным.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector