Составление линейных прогнозов: функция тенденция - pismo.netnado.ru o_O
Главная
Поиск по ключевым словам:
страница 1
Похожие работы
Название работы Кол-во страниц Размер
Лекция № Методы решения систем линейных уравнений 1 87.16kb.
«Составление экологической характеристики микрорайона города» 1 70.81kb.
1. Необходимость и сущность денег 1 31.86kb.
Определить оптимальный объем выпуска и цену продаж на монопольном... 1 24.07kb.
Руководство по составлению прогнозов. Мн.: Современная школа, 2008. 1 60.17kb.
Должностная инструкция 1 78.61kb.
Лекция №4 Прямые методы решения слау пусть дана система n линейных... 1 92.68kb.
Б. Рассел логический атомизм 1 332.18kb.
«Составление глаголицы первой славянской азбуки. Почему равноапостольные... 1 43.62kb.
Ларгранжиан взаимодействия двух зарядов 2 266.3kb.
Образовательная функция библиотеки и формирование информационной... 1 120.43kb.
Методические указания по выполнению расчётно-графических и контрольных... 1 73.73kb.
Урок литературы «Война глазами детей» 1 78.68kb.
Составление линейных прогнозов: функция тенденция - страница №1/1

Лабораторная работа №2:

Прогнозирование с помощью функций регрессии Excel


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

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



Составление линейных прогнозов: функция ТЕНДЕНЦИЯ

Использование функции рабочего листа ТЕНДЕНЦИЯ — это самый простой спо­соб регрессионного анализа. Предположим, результаты ваших наблю­дений внесены в ячейки А2:А11, а дни месяца расположены в ячейках В2:В11, как на рисунке 1.

Выделите ячейки С2:С11 и введите следующую формулу, используя формулу массива:
= ТЕНДЕНЦИЯ (А2:А11;В2:В11).
Для этого необходимо:

1) Запустить Мастер функций.

2) В появившемся диалоговом окне выбрать функции СтатистическиеТенденция.

3) Ведите нужные диапазоны ячеек.

4) Когда рассчиталось одно значение из выделенного диапазона необходимо щёлкнуть мышью на формульную строку (после знака равенства).

5) Для ввода формулы массива нажмите комбинацию клавиш <Ctrl+Shift+Enter>

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


  1. Каждый результат в ячейках С2:С11 получается на основе одной и той же формулы массива, внутри которой «спрятано» более сложное выражение – линейное уравнение регрессии. В данном случае формула имеет следующий вид:




Рисунок 1 - С помощью функции ТЕНДЕНЦИЯ можно прогнозировать базовую линию (линейный тренд) результатов наблюдений, а также значения показателя, выходящие за пределы конца базовой линии
Ячейка С2: = 9,13 + 0,61*1

Ячейка С3: = 9,13 + 0,61*2

Ячейка С4: = 9,13 +0,61*3 и так далее.
Значение 9,13 представляет собой длину отрезка, отсекаемого на оси ординат линией прогноза, т.е. значение прогноза в начальный момент. Это свободный член уравнения регрессии. Значение 0,61 равно угловому коэффициенту линии прогноза, другими словами, значения прогноза изменяются в результате изменений дат проведения наблюдений. Этот параметр уравнения регрессии носит название коэффициента регрессии. Знак перед коэффициентом регрессии говорит о направлении связи: прямая или обратная. В данном случае с увеличением времени от 1 до 10 дней ежедневный средний прирост объёмов продаж составляет 0,61 – связь прямая.


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

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

  1. В данном примере функция ТЕНДЕНЦИЯ вычисляет прогноз, основанный на связи между фактическими результатами наблюдений и числами 1-10, ко­торые могут отражать либо первых десять дней месяца, либо первых десять месяцев года. Excel выражает первый аргумент как аргумент изв_знач_у (известные значения у) функции ТЕНДЕНЦИЯ, а второй — как аргумент функции изв_знач_х (известные значения х).

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

Введите в ячейку В12 число 11, а в ячейку С12 — следующее:


= ТЕНДЕНЦИЯ (А2:А11;В2:В11;В12)
Первый аргумент - А2:А11 - определяет данные наблюдений базовой линии (изв_знач_у); второй аргумент - В2:В11 - определяет временные моменты, в кото­рые эти данные были получены (изв_знач_х). Значение 11 в ячейке В12 является нов_знач_х (новым значением х) и определяет время, которое связывается с перспектив­ной оценкой.

Формула фактически говорит о следующем: «Если известно, каким образом у-значения в диапазоне А2:А11 соотносятся с х-значениями в диапазоне B2:B11, то какой результат у-значения мы получим, зная новое х-значение временного момен­та, равное 11?». Полученное значение 15,87 является прогнозом на основе фактических данных на пока еще не наступивший одиннадцатый временной отсчет.

Введя в ячейку В12 большее значение, вы сможете прогнозировать данные более позднего временного момента, чем непосредственно следующий за текущим. Предположим, что результаты наблюдений в ячейках А2:А11 отражают ежемесячные объёмы продаж с января по октябрь 2002 года. В этом случае число 24, введённое в ячейку В12, будет определять 24 месяц, т.е. декабрь 2003 года. Выполняя вычисления с помощью функции ТЕНДЕНЦИЯ, получаем результат 23,82, который и будет отражать прогнозируемый объём продаж на декабрь 2003 года, полученный на основе фактических результатов наблюдений за период с января по октябрь 2002 года.

Кроме того, существует возможность одновременного прогнозирования данных для нескольких новых временных моментов. Например, введите числа 11-24 в ячейки В12:В25, а затем выделите ячейки С12:С25 и введите с помощью формулы массива следующее:


= ТЕНДЕНЦИЯ (А2:А11;В2:В11;В12:В25)
Excel вернет в ячейки С12:С25 прогноз на временные моменты с 11 по 24. Дан­ный прогноз будет базироваться на связи между данными наблюдений базовой линии диапазона А2:А11 и временными моментами базовой линии с 1 по 10, указанными в ячейках В2:В11.

Составление нелинейного прогноза: функция РОСТ

Функция ТЕНДЕНЦИЯ вычисляет прогнозы, основанные на линейной связи между результатом наблюдения и временем, в которое это наблюдение было зафиксировано. Предположим, что вы составляете линейный график данных, на вертикальной оси которого отмечаете результаты наблюдений, а на горизонтальной фиксируете временные моменты их получения. Если эта взаимосвязь носит ли­нейный характер, то линия на графике будет либо прямой, либо слегка наклонен­ной в одну или другую сторону, либо горизонтальной. Это и будет лучшей под­сказкой о том, что взаимосвязь является линейной, и поэтому в данном случае функция ТЕНДЕНЦИЯ — самый удобный способ регрессионного анализа.

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

Пример: книжная торговля


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

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

Чтобы спрогнозировать результаты 11-13 недель, следует ввести эти числа в ячейки В12:В14, а затем с помощью формулы массива в диапазон ячеек С12:С14 ввести следующее:
= РОСТ(А2:А11;В2:В11;В12:В14)


Рисунок 2 - Функция РОСТ очень удобна при прогнозировании нелинейных

базовых линий (нелинейных трендов)

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

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

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




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



Регрессионный анализ с помощью диаграмм

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

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

После этого выполните следующие шаги.



  1. Выберите команду ДиаграммаДобавить линию тренда.

  2. Выберите тип линии тренда Линейная.

  3. Щелкните на корешке вкладки Параметры.

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

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

6. Щелкните на кнопке ОК.

Рисунок 4. С помощью линий тренда можно создавать прогнозы,

основанные на регрессии, непосредственно на диаграмме


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