Использование функции ТЕНДЕНЦИЯ

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

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

Предварительно, перед использованием функции ТЕНДЕНЦИЯ необходимо ввести (в столбец или в строку) массивы X и Y, взаимосвязанных величин, а также значения X для которых будет спрогнозирована величина Y.

Затем, вызываем статистическую функцию ТЕНДЕНЦИЯ. Для этого необходимо вызвать Мастер функций, щелкнув на кнопке строки формул или отдав команду Вставка/Функция. В окне Мастера функций выберите категорию Статистические и в поле Выберите функцию выберите из списка функцию ТЕНДЕНЦИЯ. В появившемся диалоговом окне Аргументы функции (рис.1) в соответствующие поля внесите ссылки на диапазоны ячеек в которых хранятся известные значения величин Y и X, новые значения Х, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения Y. С помощью поля Конст можно указать вычислять ли константу b или принять равной 0. Если это поле имеет значение ИСТИНА или опущено, то b вычисляется обычным образом, если ЛОЖЬ – то b полагается равным 0, и значения r подбираются таким образом, чтобы выполнялось соотношение y = rx.

Рис.1. Фрагмент диалогового окна Аргументы функции.

После ввода всех необходимых аргументов функции необходимо нажать на кнопке ОК.

Быстрое построение линий регрессии в Excel: линия тренда.

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

1) Выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!).

2) Вызвать Мастер диаграмм (используя инструмент или команду Вставка/Функция) и в открывшемся окне Мастер диаграмм выбрать в группе Тип – Точечная и сразу нажать Готово.

3) Не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма, в котором следует выбрать пункт Добавить линию тренда.

4) В открывшемся диалоговом окне Линия тренда во вкладке Тип выбрать тип аппроксимации рис.2. Можно выбрать одну из шести зависимостей: линейная, степенная, логарифмическая, экспоненциальная, полиномиальная и линейная фильтрация. Для полиномиальной аппроксимации можно указать степень. При линейной фильтрации (скользящее среднее) элементы данных усредняются, и полученный результат используется в качестве среднего значения для приближения. Так, если шаг линейной фильтрации равен 2, первая точка сглаживающей кривой определяется как среднее значение первых двух элементов данных, вторая точка — как среднее следующих двух элементов и так далее.

Рис.2. Вкладка Тип диалогового окна Линия тренда.

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

Рис.3. Вкладка Параметры диалогового окна Линия тренда.

6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2). Эта величина есть квадрат коэффициента корреляции и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R ² близка к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R ² близка к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).

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

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

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

Общая постановка задачи

Научиться осуществлять прогнозирование экономических процессов средствами табличного процессора MS Excel.