Корреляционно-регрессионный анализ в Excel с использованием настройки Пакет анализа

Для проведения корреляционно-регрессионного анализа в первую очередь необходимо построить матрицу коэффициентов парной корреляции для оценки степени влияния факторов на зависимую переменную и друг на друга. Для построения матрицы коэффициентов парной корреляции необходимо выбирать команду меню Сервис/Анализ данных/Корреляция.

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

После построения матрицы коэффициентов парной корреляции можно рассчитать параметры линейной и экспоненциальной регрессионных моделей. Для расчета параметров линейной модели использовать функцию ЛИНЕЙН и инструмент Регрессия надстройки Пакет анализа, для расчета параметров экспоненциальной - функцию ЛГРФПРИБЛ.

Для линейной и экспоненциальной моделей можно рассмотреть случаи, когда аргумент Константа в функциях ЛИНЕИН и ЛГФРФПРИБЛ имеет значение ИСТИНА и ЛОЖЬ.

Далее следует:

1. Сделать выводы: о значимости коэффициентов, входящих в модель, об адекватности модели фактическим данным;
2. На основе проведенного анализа определить вид модели, наиболее точно описывающей фактические данные;
3. Рассчитать прогнозные значения, используя выбранную модель. Найти отклонение фактических данных от расчетных;
4. Построить график, отражающий фактические и расчетные данные.

Скачать детально разобранный пример проведения корреляционно-регрессионного анализа в Excel с использованием настройки Пакет анализа с depositfiles

Ключевые слова: корреляционно-регрессионный анализ, Excel, регрессия, корреляция, пакет анализа.