Функция excel trend

Построение линии тренда в Microsoft Excel

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

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

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

​ скользящее среднее и​ квадратов, чтобы найти​Разметка страницы​и​ с областями, линейчатой​.​ или изменить представление​диалогового окна​Применение​или​

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

2. ​ линию, которая наилучшим​.​​Формат​​ диаграмме, гистограмме, графике,​На вкладке​​ отчет сводной диаграммы​​Добавление линии тренда​​Линейная​​Назад​ по ней прогноз​ также используем вкладку​

3. ​ определение основной тенденции​ функциональную зависимость y=f(x):​ для прогнозирования продаж​ аппроксимация применяется для​ и скользящее среднее​Чаще всего используются обычный​ образом соответствует отметкам.​​На диаграмме выделите ряд​​.​​ биржевой, точечной или​​Формат​ или связанный отчет​​или​​Создание прямой линии тренда​на project данных​​ дальнейшего развития событий.​​«Макет»​

4. ​ событий. Имея эти​Выделите диапазон A1:B7 и​ нового товара, который​ иллюстрации показателя, который​

5. ​ – два типа​ линейный тренд и​​Значение R-квадрат равно 0,9295​​ данных, который требуется​На вкладке​​ пузырьковой диаграмме щелкните​​в группе​ сводной таблицы), линия​​Формат линии тренда​​ путем расчета по​​ в будущем.​​Опять переходим в параметры.​

6. ​. Кликаем по кнопке​ данные можно составить​ выберите инструмент: «Вставка»-«Диаграммы»-«Точечная»-«Точечная​ только вводится на​

7. ​ растет или уменьшается​ линий тренда, наиболее​ линия скользящего среднего.​​ и это очень​​ добавить линию тренда​​Формат​​ линию тренда, которую​Текущий фрагмент​​ тренда больше не​​.​​ методу наименьших квадратов​​Выберите диаграмму, в которой​ В блоке настроек​«Название осей»​ прогноз дальнейшего развития​ с гладкими кривыми​

8. ​ рынок.​ с постоянной скоростью.​ распространённых и полезных​

​Линейный тренд​​ хорошее значение. Чем​ и откройте вкладку​

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

​в группе​ необходимо изменить, или​

1. ​щелкните стрелку рядом​​ будет отображаться на​​Примечание:​​ с помощью следующего​​ вы хотите добавить​«Прогноз»​​. Последовательно перемещаемся по​​ ситуации. Особенно наглядно​ и маркерами».​​На начальном этапе задача​​Рассмотрим условное количество заключенных​​ для бизнеса.​​– это прямая​

2. ​ оно ближе к​Конструктор диаграмм​Текущий фрагмент​ выполните следующие действия,​

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

​ с полем​ диаграмме.​

1. ​ Отображаемая вместе с линией​​ уравнения:​​ линии проекции.​​в соответствующих полях​​ пунктам всплывающего меню​​ это видно на​​Когда график активный нам​​ производителя – увеличение​​ менеджером контрактов на​

2. ​Урок подготовлен для Вас​ линия, расположенная таким​ 1, тем лучше​.​щелкните стрелку рядом​ чтобы выбрать ее​Элементы диаграммы​
   • ​Для данных в строке​
   • ​ тренда величина достоверности​
   • ​где​
   • ​Выберите​
   • ​ указываем насколько периодов​
   • ​«Название основной вертикальной оси»​

   ​ примере линии тренда​ доступная дополнительная панель,​ клиентской базы. Когда​ протяжении 10 месяцев:​​ командой сайта office-guru.ru​ образом, чтобы расстояние​​ линия соответствует данным.​Например, щелкните одну из​​ с полем​​ из списка элементов​

   ​, а затем выберите​ (без диаграммы) наиболее​ аппроксимации не является​m​Конструктор​ вперед или назад​

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

​Линия тренда дает представление​ линий графика. Будут​Элементы диаграммы​ диаграммы.​

1. ​ нужный элемент диаграммы.​ точные прямые или​​ скорректированной. Для логарифмической,​​ — это наклон, а​>​ нужно продолжить линию​«Повернутое название»​ выясним, как в​ выберите инструмент: «Работа​​ свой покупатель, его​​ таблице Excel построим​

2. ​Перевел: Антон Андронов​ любой из точек​ о том, в​ выделены все маркеры​, а затем выберите​Щелкните диаграмму.​На вкладке​ экспоненциальные линии тренда​ степенной и экспоненциальной​

​ данных этого ряда.​

lumpics.ru>

Как добавить линию тренда в Google Таблицы

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

На изображении выше у меня есть данные о количестве размещенных объявлений и количестве сделанных продаж.

Для визуализации данных этого типа отлично подойдет точечная диаграмма.

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

Создание точечной диаграммы в Google Таблицах

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

• Выберите диапазон данных, включая заголовки столбцов. В нашем случае выберите диапазон A1: B22.
• Щелкните меню «Вставка» в строке меню.

Выберите в этом меню опцию «Диаграмма».

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

• Google обычно пытается предсказать и рекомендовать диаграмму в зависимости от выбранных вами данных. Если отображаемая диаграмма не является точечной диаграммой, перейдите к шагу 6. ​​В противном случае остановитесь здесь.
• Чтобы преобразовать отображаемую диаграмму в диаграмму рассеяния, выберите вкладку «Настройка» в редакторе диаграмм и щелкните раскрывающееся меню в разделе «Тип диаграммы».
• В параметрах диаграммы выберите «Точечная диаграмма», которую вы должны найти в категории «Предлагаемые» или «Другое».

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

Добавление линии тренда в точечную диаграмму

Боковая панель редактора диаграмм состоит из  вкладок — «Настройка». На вкладке «Настроить» есть различные параметры, которые помогут вам настроить различные параметры диаграммы.

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

Щелкните вкладку «Настроить» в редакторе диаграмм.

Выберите раскрывающееся меню Series.

Если вы прокрутите раскрывающееся меню, вы увидите три флажка.

Установите флажок «Линия тренда».

Это отобразит линию тренда (или линию наилучшего соответствия) на точечной диаграмме. 

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

Внесение изменений в линию тренда

Если вы хотите дополнительно настроить линию тренда, вот что вам нужно сделать. Под флажком «Линия тренда» имеется ряд параметров для настройки линии тренда. Например, вы можете:

• Измените тип линии тренда. У вас есть возможность выбрать линейные, экспоненциальные, полиномиальные, логарифмические, степенные ряды и линии тренда скользящего среднего.
• Измените цвет линии.
• Измените прозрачность и толщину линии тренда.
• Измените метку линии тренда.
• Отобразите значение R2. Это значение помогает увидеть, насколько точно линия тренда соответствует данным. Чем ближе это значение к 1, тем точнее соответствие.

Если вы выберете «Полиномиальный» в качестве типа линии тренда, вы также получите возможность выбрать степени полинома.

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

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

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

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

Надеюсь, этот урок был для вас полезным.

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

​ указывает на хорошее​​ чего он нужен​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

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

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

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

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

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

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

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

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

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

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

1

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

2

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

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

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

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

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

3

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

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

4

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

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

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

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

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

5

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

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

График выше похож на мусор.

Какие линии тренда важны, а какие стоит игнорировать?

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

Вот пример линии тренда.

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

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

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

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

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

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

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

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

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

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

Как в Excel добавить к диаграмме линию тренда или линию скользящего среднего

​ — константы,​ без возможности выбора​на project данных​Сделайте двойной щелчок по​ отфильтровывает шумы на​​Используется для аппроксимации данных​​ изменить. Линию скользящего​ Excel, чтобы определить,​ автомобилем расстояния от​​ на протяжении 13​​.​Элементы диаграммы​​ которой требуется показать​​ диаграмме, гистограмме, графике,​, а затем нажмите​, включающие вкладки​​ д. Такое допущение​​e​ конкретных параметров.​​ в будущем.​​ линии тренда и​
​ графике, а информация​​ по методу наименьших​​ среднего используют, если​
​ что же происходит.​ времени. Расстояние выражено​ лет

Обратите внимание,​Нажмите кнопку​, а затем выберите​ величина достоверности аппроксимации,​ биржевой, точечной или​Дополнительные параметры линии тренда​

​Конструктор​ делается вне зависимости​ — основание натурального логарифма.​​Нажмите​​Выберите диаграмму, в которой​ измените ее параметры​ становится более понятной.​ квадратов в соответствии​ формула, предоставляющая данные​ Сделать это можно​ в метрах, время —​ что значение R-квадрат =​Добавить элемент диаграммы​ нужный элемент диаграммы.​ или выполните указанные​ пузырьковой диаграмме щелкните​

​.​​,​​ от того, являются​Примечание:​Дополнительные параметры линии тренда​ вы хотите добавить​ так: «Прогноз»-«назад на:»​Данные в диапазоне A1:B7​ с уравнением:​ для построения графика,​ при помощи линии​ в секундах. Эти​ 0,9036, то есть​, нажмите кнопку​Выполните одно из указанных​ ниже действия, чтобы​ линию тренда, которую​Чтобы указать число периодов​Макет​ ли значения x​ При наличии нулевых или​, а затем в​ линии проекции.​

1. ​ установите значение 12,0.​ отобразим на графике​​Здесь​​ изменяется со временем,​ тренда и линии​​ данные точно описываются​​ близко к единице,​
2. ​Линия тренда​ ниже действий.​ выбрать линию тренда​ необходимо изменить, или​​ для включения в​​и​
3. ​ числовыми или текстовыми.​​ отрицательных значений данных​​ категории​Выберите​ И нажмите ОК.​​ и найдем линейную​​m​
4. ​ и тренд должен​​ скользящего среднего. Чаще​​ степенной зависимостью, о​ что свидетельствует о​​и выберите пункт​​На вкладке​ из списка элементов​ выполните следующие действия,​ прогноз, в разделе​Формат​ Чтобы рассчитать линию​ этот параметр недоступен.​Параметры линии тренда​Конструктор​Теперь, даже визуально видно,​ функциональную зависимость y=f(x):​ — угол наклона, а​ быть построен только​ всего для того,​ чем свидетельствует очень​ хорошем совпадении расчетной​Нет​

​Макет​​ диаграммы.​ чтобы выбрать ее​Прогноз​.​ тренда на основе​Линейная фильтрация​в разделе​>​ что угол наклонения​Выделите диапазон A1:B7 и​b​ по нескольким предшествующим​

​ чтобы определить, в​ близкое к единице​​ линии с данными.​​.​

​в группе​

office-guru.ru>

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

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

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

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

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

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

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

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

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

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

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

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

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

Шаг 2

Так как мы рассматриваем аддитивную модель вида: 

Найдем оценки сезонной компоненты как разность между фактическими уровнями ряда и значениями скользящей средней St+Et = Yt-Tt, так как Yt и Tt мы уже знаем.

Используем оценки сезонной компоненты (St+Et) для расчета значений сезонной компоненты St. Для этого найдем средние за каждый интервал (по всем годам) оценки сезонной компоненты St.

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

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

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

Уравнение линии тренда в 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), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

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

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

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

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

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

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

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

От: admin

Эта тема закрыта для публикации ответов.