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

Вставка точечной диаграммы в 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. Наслаждайтесь!

Выбор наиболее подходящей линии тренда для данных

​ указывает на хорошее​​ чего он нужен​c​ нас важно, чтобы​ отрицательные значения, экспоненциальная​ для описания величин,​ внизу страницы. Для​ 2 из описанных​ всего для того,​ времени

Расстояние выражено​ убывает с постоянной​ оно ближе к​ и аппроксимации. Затем,​ пунктам всплывающего меню​ построение линии тренда​ линию тренда.​20​ совпадение расчетной прямой​ – для составления​и​ эта статья была​ линия тренда неприменима.​

​ попеременно возрастающих и​ удобства также приводим​ выше, а затем​ чтобы определить, в​ в метрах, время —​ скоростью.​ 1, тем лучше​ сформировать коэффициент заново.​«Название основной вертикальной оси»​

​ при помощи графика.​​​Прогноз​ с исходными данными.​ прогнозов на основе​b​ вам полезна. Просим​Для расчета точек методом​ убывающих. Например, при​ ссылку на оригинал​ сделайте вот что:​ каком направлении происходит​ в секундах. Эти​В следующем примере прямая​

​ линия соответствует данным.​

​Главной задачей линии тренда​и​ При этом, исходные​Сменим график на гистограмму,​1005,4​ Прогнозы должны получиться​ статистических данных. С​ — константы, а ln —​ вас уделить пару​ наименьших квадратов экспоненциальная​ анализе большого набора​ (на английском языке).​Кликните направленную вправо стрелку​

​ развитие данных, в​ данные точно описываются​ линия описывает стабильный​Линия тренда дает представление​ является возможность составить​«Повернутое название»​ данные для его​ чтобы сравнить их​1024,18​ точными.​ этой целью необходимо​

​ натуральный логарифм.​

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

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

​ параметры различных линии​

​Линия тренда​ тренда. Чтобы автоматически​ чем свидетельствует очень​ на протяжении 13​ каком направлении идут​ дальнейшего развития событий.​ расположения наименования оси​ заранее подготовленной таблицы.​Щелкните правой кнопкой по​1058,24​ контрактов, например, в​ определить ее значения.​ по методу наименьших​ вам, с помощью​где​ определяется количеством экстремумов​ тренда, доступные в​(Trendline) и выберите​ рассчитать такую линию​

​ близкое к единице​ лет

Обратите внимание,​ продажи. В период​Опять переходим в параметры.​ будет наиболее удобен​Для того, чтобы построить​ графику и выберите​1073,8​ 11 периоде, нужно​

​Если R2 = 1,​

​ квадратов в соответствии​ кнопок внизу страницы.​c​ (максимумов и минимумов)​ Office.​ вариант​ и добавить её​ значение R-квадрат, равное​ что значение R-квадрат =​13​ В блоке настроек​ для нашего вида​

​ график, нужно иметь​ опцию из контекстного​1088,51​ подставить в уравнение​ то ошибка аппроксимации​ с уравнением:​ Для удобства также​и​ кривой. Обычно полином​Используйте линию тренда этого​Скользящее среднее​ к диаграмме Excel,​

​ 0,9923.​

​ 0,9036, то есть​продажи могут достичь​«Прогноз»​ диаграмм.​ готовую таблицу, на​ меню: «Изменить тип​1102,47​ число 11 вместо​ равняется нулю. В​

​Здесь​ приводим ссылку на​b​ второй степени имеет​ типа для создания​(Moving average).​ нужно сделать следующие​Экспоненциальное​ близко к единице,​

​ отметки​

​в соответствующих полях​В появившемся поле наименования​ основании которой он​ диаграммы».​Для расчета прогнозных цифр​ х. В ходе​ нашем примере выбор​c​ оригинал (на английском​ — константы и​​ только один экстремум,​​ прямой линии, которая​Проделайте шаги 1 и​ шаги:​Экспоненциальное приближение следует использовать​ что свидетельствует о​​120​​ указываем насколько периодов​ вертикальной оси вписываем​ будет формироваться. В​В появившемся окне выберите​ использовалась формула вида:​ расчетов узнаем, что​ линейной аппроксимации дал​и​

​ языке) .​e​ полином третьей степени —​ наилучшим образом описывает​ 2 из предыдущего​

support.office.com>

Уравнение линии тренда в 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

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

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

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

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

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 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 степень.

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

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

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

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

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

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

Екатерина Шипова

Магистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.

• Прогнозирование продаж в Excel с учетом сезонности — 27.06.2018
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности — 05.06.2018
• Когортный анализ. Сколько пользователей к вам вернулось? — 24.05.2018

Можете ли вы анализировать тенденции данных с помощью спарклайнов?

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

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

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

Вот как вы можете сделать это:

1. Нажмите на пустую ячейку рядом с данными, которые вы хотите проиллюстрировать на спарклайне.
2. Затем вам нужно нажать на кнопку Вставить; оттуда вы направляетесь к набору Sparklines, а затем либо на столбец, линию или выигрыш / проигрыш.
3. Перейдите в поле «Диапазон данных», где вам нужно будет ввести диапазоны ячеек, которые содержат данные, которые вы хотите отобразить в спарклайне. Например, если ваши данные расположены в A, B, C и D строки 3, вам нужно ввести A3: D3.
4. Кроме того, вы также можете выбрать диапазон ячеек на листе напрямую. Вы можете временно свернуть диалоговое окно, а затем снова открыть его.
5. Как только вы нажмете на OK, вы увидите раздел Sparkline Tools на ленте. Вы можете воспользоваться рекомендациями на вкладке «Дизайн», чтобы адаптировать спарклайны в соответствии со своими предпочтениями.

Как вы настраиваете свои спарклайны?

Закончив создание спарклайнов, вы можете в любое время изменить его формат, стиль и тип:

1. Сначала вам нужно выбрать спарклайны, которые вы хотите настроить.
2. Для этого вам нужно перейти на вкладку «Дизайн» и выбрать параметры, которые вы хотите выбрать.
3. Здесь вы можете изменить формат или стиль спарклайнов, а также настройки оси.
4. При нажатии на параметр «Тип оси даты» в Excel откроется новое диалоговое окно «Спарклайн». здесь вы можете выбрать диапазон вашей рабочей книги, в котором есть все значения данных, которые вы ищете в своих данных спарклайна.
5. После завершения настройки вы увидите результаты в своем листе Excel.

Заключение по анализу трендов в Excel

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

Итоги.

Результатом всех предыдущих действий стала полученная формула аппроксимирующей функции y=-172,01*ln (x)+1188,2. Зная ее, и количество уголков в месячном наборе работ, можно с высокой степенью вероятности (±4% — смотри планки погрешностей) спрогнозировать общий выпуск металлоконструкций за месяц! Например, если в плане на месяц 140 тонн уголков, то общий выпуск, скорее всего, при прочих равных составит 338±14 тонн.

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

Из практики скажу, что хорошим результатом следует считать нахождение аппроксимирующей функции с коэффициентом достоверности R 2 >0,87. Отличный результат – при R 2 >0,94.

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

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

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

переменных…. Об этом и не только читайте в следующих статьях на блоге.