Смарт ТВ 

Создать диаграмму с линией тренда. Как построить линию тренда в MS Excel

Теоретическая справка

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

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

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

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

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

В Excel для построения регрессий имеются две возможности.

  1. Добавление выбранных регрессий (линий тренда - trendlines) в диаграмму, построенную на основе таблицы данных для исследуемой характеристики процесса (доступно лишь при наличии построенной диаграммы);
  2. Использование встроенных статистических функций рабочего листа Excel, позволяющих получать регрессии (линии тренда) непосредственно на основе таблицы исходных данных.

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

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

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

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

y = y(x)

где x - независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).

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

y = mx + b

где m - тангенс угла наклона линейной регрессии к оси абсцисс; b - координата точки пересечения линейной регрессии с осью ординат.

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

В этом случае линия тренда строится в соответствии с уравнением:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

где коэффициенты c0, c1, c2,... c6 - константы, значения которых определяются в ходе построения.

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

Строится в соответствии с уравнением:

y = c ln(x) + b

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

Строится в соответствии с уравнением:

y = c xb

где коэффициенты b, с - константы.

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

Строится в соответствии с уравнением:

y = c ebx

где коэффициенты b, с - константы.

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

Определяется по формуле:

Для добавления линии тренда к ряду данных следует:

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

Эти же действия легко реализуются, если навести указатель мыши на график, соответствующий одному из рядов данных, и щелкнуть правой кнопкой мыши; в появившемся контекстном меню выбрать команду Добавить линию тренда. На экране появится диалоговое окно Линия тренда с раскрытой вкладкой Тип (рис. 1).

После этого необходимо:

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

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

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

  • изменить название линии тренда в поле Название аппроксимирующей (сглаженной) кривой.
  • задать количество периодов (вперед или назад) для прогноза в поле Прогноз;
  • вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;
  • вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);
  • задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке;
  • щелкнуть на кнопке OK, чтобы закрыть диалоговое окно.

Для того, чтобы начать редактирование уже построенной линии тренда, существует три способа:

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

    • На экране появится диалоговое окно Формат линии тренда (рис. 3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (рис.1-2). На вкладке Вид, можно задать тип линии, ее цвет и толщину.

    Для удаления уже построенной линии тренда следует выбрать удаляемую линию тренда и нажать клавишу Delete.

    Достоинствами рассмотренного инструмента регрессионного анализа являются:

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

    К недостаткам можно отнести следующие моменты:

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

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

    Нельзя дополнить линиями тренда ряды данных на объемных, нормированных, лепестковых, круговых и кольцевых диаграммах.

    Использование встроенных функций Excel

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

    В Excel имеется несколько функций для построения линейной регрессии, в частности:

    • ТЕНДЕНЦИЯ;
    • ЛИНЕЙН;
    • НАКЛОН и ОТРЕЗОК.

    А также несколько функций для построения экспоненциальной линии тренда, в частности:

    • РОСТ;
    • ЛГРФПРИБЛ.

    Следует отметить, что приемы построения регрессий с помощью функций ТЕНДЕНЦИЯ и РОСТ практически совпадают. То же самое можно сказать и о паре функций ЛИНЕЙН и ЛГРФПРИБЛ. Для четырех этих функций при создании таблицы значений используются такие возможности Excel, как формулы массивов, что несколько загромождает процесс построения регрессий. Заметим также, что построение линейной регрессии, на наш взгляд, легче всего осуществить с помощью функций НАКЛОН и ОТРЕЗОК, где первая из них определяет угловой коэффициент линейной регрессии, а вторая - отрезок, отсекаемый регрессией на оси ординат.

    Достоинствами инструмента встроенных функций для регрессионного анализа являются:

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

    А к недостаткам относится то, что в Excel нет встроенных функций для создания других (кроме линейного и экспоненциального) типов линий тренда. Это обстоятельство часто не позволяет подобрать достаточно точную модель исследуемого процесса, а также получить близкие к реальности прогнозы. Кроме того, при использовании функций ТЕНДЕНЦИЯ и РОСТ не известны уравнения линий тренда.

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

    Примеры решения конкретных задач

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

    Задача 1

    С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг. необходимо выполнить следующие действия.

    1. Построить диаграмму.
    2. В диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда.
    3. Используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 1995-2004 г.г.
    4. Составить прогноз по прибыли предприятия на 2003 и 2004 гг.

    Решение задачи

    1. В диапазон ячеек A4:C11 рабочего листа Excel вводим рабочую таблицу, представленную на рис. 4.
    2. Выделив диапазон ячеек В4:С11, строим диаграмму.
    3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда (см. рис. 1) поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры (см. рис. 2), в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на: периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки показывать уравнение на экране и поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда (см. рис. 3). Полученная диаграмма с добавленными линиями тренда представлена на рис. 5.
    4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 1995-2004 гг. воспользуемся уравнениями линий тренда, представленными на рис. 5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии - диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2003 и 2004 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 6.

    Задача 2

    1. Построить диаграмму.
    2. В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.
    3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.
    4. Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 1995-2002 гг.
    5. Составить прогноз о прибыли предприятия на 2003 и 2004 гг., используя эти линии тренда.

    Решение задачи

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

    На рис. 5 и рис. видно, что модели с логарифмическим трендом, соответствует наименьшее значение достоверности аппроксимации

    R2 = 0,8659

    Наибольшие же значения R2 соответствуют моделям с полиномиальным трендом: квадратичным (R2 = 0,9263) и кубическим (R2 = 0,933).

    Задача 3

    С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг., приведенной в задаче 1, необходимо выполнить следующие действия.

    1. Получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ.
    2. Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2003 и 2004 гг.
    3. Для исходных данных и полученных рядов данных построить диаграмму.

    Решение задачи

    Воспользуемся рабочей таблицей задачи 1 (см. рис. 4). Начнем с функции ТЕНДЕНЦИЯ:

    1. выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия;
    2. вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки (Вставка функции) стандартной панели инструментов.
    3. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11;
    4. чтобы вводимая формула стала формулой массива, используем комбинацию клавиш + + .

    Введенная нами формула в строке формул будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11)}.

    В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (рис. 9).

    Для составления прогноза о прибыли предприятия на 2003 и 2004 гг. необходимо:
    1. выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ.
    2. вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y - диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11; а в поле Новые_значения_х - диапазон ячеек B12:B13.
    3. превратить эту формулу в формулу массива, используя комбинацию клавиш Ctrl + Shift + Enter.
    4. Введенная формула будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11;B12:B13)}, а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 9).

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

    На рис.10 представлена таблица в режиме показа формул.

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

    Задача 4

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

    1. Получить ряды данных для линейной регрессии: используя функции НАКЛОН и ОТРЕЗОК; используя функцию ЛИНЕЙН.
    2. Получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.
    3. Используя вышеназванные функции, составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.
    4. Для исходных и полученных рядов данных построить диаграмму.

    Решение задачи

    Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.

    Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.

    1 . Построим линейную регрессию, имеющую уравнение:

    y = mx+b

    с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b - функцией ОТРЕЗОК.

    Для этого осуществляем следующие действия:

    1. заносим исходную таблицу в диапазон ячеек A4:B14;
    2. значение параметра m будет определяться в ячейке С19. Выбираем из категории Статистические функцию Наклон; заносим диапазон ячеек B4:B14 в поле известные_значения_y и диапазон ячеек А4:А14 в поле известные_значения_х. В ячейку С19 будет введена формула: =НАКЛОН(B4:B14;A4:A14);
    3. по аналогичной методике определяется значение параметра b в ячейке D19. И ее содержимое будет иметь вид: =ОТРЕЗОК(B4:B14;A4:A14). Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C19, D19;
    4. далее заносим в ячейку С4 формулу линейной регрессии в виде: =$C*A4+$D. В этой формуле ячейки С19 и D19 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 12). В связи с тем, что количество заявок - целое число, следует установить на вкладке Число окна Формат ячеек числовой формат с числом десятичных знаков 0.

    2 . Теперь построим линейную регрессию, заданную уравнением:

    y = mx+b

    с помощью функции ЛИНЕЙН.

    Для этого:

    1. вводим в диапазон ячеек C20:D20 функцию ЛИНЕЙН как формулу массива: ={ЛИНЕЙН(B4:B14;A4:A14)}. В результате получаем в ячейке C20 значение параметра m, а в ячейке D20 - значение параметра b;
    2. вводим в ячейку D4 формулу: =$C*A4+$D;
    3. копируем эту формулу с помощью маркера заполнения в диапазон ячеек D4:D17 и получаем искомый ряд данных.

    3 . Строим экспоненциальную регрессию, имеющую уравнение:

    y = bmx

    с помощью функции ЛГРФПРИБЛ оно выполняется аналогично:

    в диапазон ячеек C21:D21 вводим функцию ЛГРФПРИБЛ как формулу массива: ={ ЛГРФПРИБЛ (B4:B14;A4:A14)}. При этом в ячейке C21 будет определено значение параметра m, а в ячейке D21 - значение параметра b;
  • в ячейку E4 вводится формула: =$D*$C^A4;
  • с помощью маркера заполнения эта формула копируется в диапазон ячеек E4:E17, где и расположится ряд данных для экспоненциальной регрессии (см. рис. 12).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    y = 4856*x + 105104

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

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

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

    y = 4856*10 + 105104

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

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

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

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


    Линия тренда в Excel. Процесс построения

    Линия тренда — это один из основных инструментов анализа данных

    Чтобы сформировать линию тренда , необхдимо совершить три этапа, а именно:
    1. Создать таблицу;
    2.
    3. Выбрать тип линии тренда.

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

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

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

    Различные вариации л инии тренда

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

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

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

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

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

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

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

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

    Линия тренда в Excel. Настройка параметро в функциональной линии

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

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

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

    Назначение сервиса . Сервис используется для расчета параметров тренда временного ряда y t онлайн с помощью метода наименьших квадратов (МНК) (см. пример нахождения уравнения тренда), а также способом от условного нуля. Для этого строится система уравнений:
    a 0 n + a 1 ∑t = ∑y
    a 0 ∑t + a 1 ∑t 2 = ∑y t

    и таблица следующего вида:

    t y t 2 y 2 t y y(t)
    1
    ... ... ... ... ... ...
    N
    ИТОГО ∑ ∑ ∑ ∑ ∑

    Инструкция . Укажите количество данных (количество строк). Полученное решение сохраняется в файле Word и Excel .

    Количество строк (исходных данных)
    Использовать способ отсчета времени от условного начала (перенос начала координат в середину ряда динамики)
    ",1);">

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

    Способ отсчета времени от условного начала

    Для определения параметров математической функции при анализе тренда в рядах динамики используется способ отсчета времени от условного начала. Он основан на обозначении в ряду динамики показаний времени таким образом, чтобы ∑t i . При этом в ряду динамики с нечетным числом уровней порядковый номер уровня, находящегося в середине ряда, обозначают через нулевое значение и принимают его за условное начало отсчета времени с интервалом +1 всех последующих уровней и –1 всех предыдущих уровней. Например, при обозначения времени будут: –2, –1, 0, +1, +2 . При четном числе уровней порядковые номера верхней половины ряда (от середины) обозначаются числами: –1, –3, –5 , а нижней половины ряда обозначаются +1, +3, +5 .

    Пример . Статистическое изучение динамики численности населения.

    1. С помощью цепных, базисных, средних показателей динамики оцените изменение численности, запишите выводы.
    2. С помощью метода аналитического выравнивания (по прямой и параболе, определив коэффициенты с помощью МНК) выявите основную тенденцию в развитии явления (численность населения Республики Коми). Оцените качество полученных моделей с помощью ошибок и коэффициентов аппроксимации.
    3. Определите коэффициенты линейного и параболического трендов с помощью средств «Мастера диаграмм». Дайте точечный и интервальный прогнозы численности на 2010 г. Запишите выводы.
    1990 1996 2001 2002 2003 2004 2005 2006 2007 2008
    1249 1133 1043 1030 1016 1005 996 985 975 968
    Метод аналитического выравнивания

    а) Линейное уравнение тренда имеет вид y = bt + a
    1. Находим параметры уравнения методом наименьших квадратов . Используем способ отсчета времени от условного начала.
    Система уравнений МНК для линейного тренда имеет вид:
    a 0 n + a 1 ∑t = ∑y
    a 0 ∑t + a 1 ∑t 2 = ∑y t

    t y t 2 y 2 t y
    -9 1249 81 1560001 -11241
    -7 1133 49 1283689 -7931
    -5 1043 25 1087849 -5215
    -3 1030 9 1060900 -3090
    -1 1016 1 1032256 -1016
    1 1005 1 1010025 1005
    3 996 9 992016 2988
    5 985 25 970225 4925
    7 975 49 950625 6825
    9 968 81 937024 8712
    0 10400 330 10884610 -4038

    Для наших данных система уравнений примет вид:
    10a 0 + 0a 1 = 10400
    0a 0 + 330a 1 = -4038
    Из первого уравнения выражаем а 0 и подставим во второе уравнение
    Получаем a 0 = -12.236, a 1 = 1040
    Уравнение тренда:
    y = -12.236 t + 1040

    Оценим качество уравнения тренда с помощью ошибки абсолютной аппроксимации.

    Ошибка аппроксимации в пределах 5%-7% свидетельствует о хорошем подборе уравнения тренда к исходным данным.

    б) выравнивание по параболе
    Уравнение тренда имеет вид y = at 2 + bt + c
    1. Находим параметры уравнения методом наименьших квадратов.
    Система уравнений МНК:
    a 0 n + a 1 ∑t + a 2 ∑t 2 = ∑y
    a 0 ∑t + a 1 ∑t 2 + a 2 ∑t 3 = ∑yt
    a 0 ∑t 2 + a 1 ∑t 3 + a 2 ∑t 4 = ∑yt 2

    t y t 2 y 2 t y t 3 t 4 t 2 y
    -9 1249 81 1560001 -11241 -729 6561 101169
    -7 1133 49 1283689 -7931 -343 2401 55517
    -5 1043 25 1087849 -5215 -125 625 26075
    -3 1030 9 1060900 -3090 -27 81 9270
    -1 1016 1 1032256 -1016 -1 1 1016
    1 1005 1 1010025 1005 1 1 1005
    3 996 9 992016 2988 27 81 8964
    5 985 25 970225 4925 125 625 24625
    7 975 49 950625 6825 343 2401 47775
    9 968 81 937024 8712 729 6561 78408
    0 10400 330 10884610 -4038 0 19338 353824

    Для наших данных система уравнений имеет вид
    10a 0 + 0a 1 + 330a 2 = 10400
    0a 0 + 330a 1 + 0a 2 = -4038
    330a 0 + 0a 1 + 19338a 2 = 353824
    Получаем a 0 = 1.258, a 1 = -12.236, a 2 = 998.5
    Уравнение тренда:
    y = 1.258t 2 -12.236t+998.5

    Ошибка аппроксимации для параболического уравнения тренда.

    Поскольку ошибка меньше 7%, то данное уравнение можно использовать в качестве тренда.

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

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

    m = 1 - количество влияющих факторов в уравнении тренда.
    Uy = y n+L ± K
    где

    L - период упреждения; у n+L - точечный прогноз по модели на (n + L)-й момент времени; n - количество наблюдений во временном ряду; Sy - стандартная ошибка прогнозируемого показателя; T табл - табличное значение критерия Стьюдента для уровня значимости α и для числа степеней свободы, равного n-2 .
    По таблице Стьюдента находим Tтабл
    T табл (n-m-1;α/2) = (8;0.025) = 2.306
    Точечный прогноз, t = 10: y(10) = 1.26*10 2 -12.24*10 + 998.5 = 1001.89 тыс. чел.

    1001.89 - 71.13 = 930.76 ; 1001.89 + 71.13 = 1073.02
    Интервальный прогноз:
    t = 9+1 = 10: (930.76;1073.02)

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

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

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

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

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

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

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

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

    (Рисунок 1)

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

    (Рисунок 2)

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

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

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

    (Рисунок 3)

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

    (Рисунок 4)

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

    (Рисунок 5)

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

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

    (Рисунок 6)

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

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

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

    (Рисунок 7)

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

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

    (Рисунок 8)

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

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

    (Рисунок 9)

    Прогноз

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

    (Рисунок 10)

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

    Вывод

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

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

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