Тема 14: Прогноз и отображение линий тренда на диаграмме.

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

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

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

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

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

14.1. Точность аппроксимации.

Линия тренда в наибольшей степени приближается к представленной на диаграмме зависимости, если значение R-квадрат равно или близко к 1.

Значение R-квадрат - число от 0 до 1, которое отражает близость значений линии тренда к фактическим данным.

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

При использовании линии тренда значение R-квадрат рассчитывается автоматически. Полученный результат можно вывести на диаграмме.

14.2. Типы линий тренда.

14.2.1. Линейная.

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

Она описывается уравнением: y = аx + b, где а - угол наклона и b - координата пересечения оси абсцисс.

14.2.2. Логарифмическая.

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

Описывается уравнением: y = аln x + b, где а и b - константы, ln - функция натурального логарифма.

14.2.3. Полиномиальная.

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

Степень полинома определяется количеством экстремумов (максимумов и минимумов) кривой. Полином второй степени может описать только один максимум или минимум. Полином третьей степени - один или два экстремума. Полином четвертой степени - не более трех экстремумов.

Описывается уравнением: y = a + b₁x + b₂x² + b₃x³ + … + b₆x⁶ , где а и b₁, …, b₆ – константы.

В следующем примере используется полином второй степени.

14.2.4. Степенная.

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

Описывается уравнением: y = ax^b, где a и b - константы.

14.2.5. Экспоненциальная.

Используется, когда скорость изменения данных непрерывно возрастает. Ее нельзя применять, если данные содержат нулевые или отрицательные значения.

Описывается уравнением: y = аe^bx , где а и b - константы, e - основание натурального логарифма.

14.2.6. Скользящее среднее.

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

Скользящее среднее вычисляется по формуле:

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

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

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

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

3. На вкладке Тип выберите нужный тип линии тренда или линии скользящего среднего.

• При выборе типа Полиномиальная введите в поле Степень наибольшую степень для независимой переменной.

• При выборе типа Скользящее среднее введите в поле Период число периодов, используемых для расчета скользящего среднего.

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

14.4. Удаление линии тренда.

1. Выберите линию тренда, которую надо удалить.

2. Нажмите клавишу Delete.

14.5. Изменение параметров линии тренда.

1. Щелкните линию тренда, которую надо изменить.

2. В меню Формат выберите команду Выделенная линия тренда.

3. На вкладке Параметры выберите нужные параметры.

Примечание. Для изменения имени линии тренда или скользящего среднего введите имя в поле Другое.

14.6. Вывод величины R-квадрат для линии тренда.

З начение R-квадрат расчитывается по формуле:

1. Щелкните линию тренда, для которой надо вывести R-квадрат.

2. В меню Формат выберите команду Линия тренда.

3. На вкладке Параметры установите флажок поместить на диаграмму величину достоверности аппроксимации (R^2).

Примечание.  Для скользящего среднего значение R-квадрат не может быть отображено.

Примечание.  Отображаемое вместе с линией тренда значение величины R-квадрат не является корректным. Для логарифмической, степенной и экспоненциальной линий тренда в Excel используется несколько видоизмененная модель регрессии.