 |
Использование функции ТЕНДЕНЦИЯ
Для прогнозирования процессов, изменение которых носит линейный характер можно использовать функцию ТЕНДЕНЦИЯ.
Эта функция позволяет находить значения в соответствии с линейным трендом. Она аппроксимирует прямой линией (по методу наименьших квадратов) массивы известных значений 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). Эта величина есть квадрат коэффициента корреляции и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R2 близка к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R2 близка к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).
7) Там же можно задать точку пересечения кривой с осью Y; выбрать, на какое количество единиц будет составлен прогноз и т.д.
8) После того как будут выбран тип аппроксимации и установлены необходимые параметры, необходимо нажать на кнопку ОК. В результате на рабочем листе появится диаграмма с графиком регрессии и ее уравнением.
9) При необходимости можно изменить параметры диаграммы (удалить легенду, добавить линии сетки, изменить цену основных и дополнительных делений и т.д.). Затем по графику регрессии можно определить значение исследуемой величины в прогнозируемых точках.
Общая постановка задачи
Научиться осуществлять прогнозирование экономических процессов средствами табличного процессора MS Excel.
