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

Линии тренда обычно используются в задачах анализа данных с целью прогнозирования. В простейшем случае речь может идти о необходимости продолжить на некоторый промежуток уже имеющийся график линией тренда (рисунок 19), который может основываться на различных функциях. Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений. Например, приведенная на рисунке 19 диаграмма использует линейную и логарифмическую линии тренда, которая является прогнозом на будущие годы для демонстрации тенденции увеличения объема производства.

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

Рисунок 19 Пример диаграммы с линиями тренда

Существуют следующие типы линий тренда:

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

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

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

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

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

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

Для добавления линии тренда на диаграмму нужно:

· выделить диаграмму;

· выбрать команду Диаграмма-Добавить линию тренда;

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

Пример 4.3 По таблице с данными о прибыли автотранспортного предприятия за 1995-2003гг. (рисунок 20) выполнить следующие действия:

· построить диаграмму;

· в диаграмму добавить линейную и две полиномиальные (квадратичную и кубическую) линии тренда;

· вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R² для каждой из них;

· используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 1995-2004 г.г.;

· составить прогноз по прибыли предприятия на 2003 и 2004 гг.

Рисунок 20 Данные для примера 4.3

1. В диапазон ячеек A4: C15 рабочего листа Excel вводим таблицу, представленную на рисунке 19.

2. Выделив диапазон ячеек В4: С12, строим диаграмму. Так как числовые значения, используемые для построения диаграммы, достаточно большие, а разброс этих значений невелик, то целесообразно изменить шкалу по оси Y. Для этого двойным щелчком мыши выделим шкалу Y и укажем в окне Формат оси новые значения на вкладке Шкала: минимальное значение – 150000, максимальное значение – 200000, цена основных делений – 5000, цена промежуточных делений – 1000.

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

4. В диалоговом окне Линия тренда открываем вкладку Параметры, в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед задаем значение 3, так как планируется сделать прогноз по прибыли на три года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R² включаем флажки Показывать уравнение на экране и Поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда. Полученная диаграмма с добавленными линиями тренда представлена на рисунке 21.

Рисунок 21

5. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 1995-2004 гг. воспользуемся уравнениями линий тренда, представленными на рисунке 21. Для этого в ячейки диапазона D3: F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5: D15. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4: D15 в качестве аргумента стоит соответствующая ячейка из диапазона A4: A13. Например, для линейного тренда формула примет вид: =3806, 7*A4 + 159444. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4: E15, а для кубической регрессии - диапазон ячеек F4: F15. Таким образом, составлен прогноз по прибыли предприятия на 2003 - 2005 гг. с помощью трех трендов. Полученная таблица значений представлена на рисунке 23.

Рисунок 23 Таблица значений, полученная по уравнениям регрессии