Корреляционный и регрессионный анализ в MS Excel

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

Вычисления коэффициента корреляции

Коэффициент корреляции r характеризует степень приближения зависимости между случайными величинами к линейной функциональной зависимости. Коэффициент корреляции принимает значения –1 £ r £ 1. Коэффициент корреляции может быть равен –1 или 1, только если величины линейно зависят друг от друга. Величина r, близкая к –1 или 1, указывает, что зависимость между данными величинами почти линейная. Значения r, близкие к нулю, означают, что связь между данными величинами либо слабая, либо не носит линейного характера. Если r> 0, то связь между переменными прямая. При r< 0 связь обратная.

Для примера рассмотрим задачу 2.

Задача 2. Дана информация о динамике некоторых экономических показателей за 8 лет. Необходимо оценить взаимосвязь и взаимное влияние этих показателей.

Для решения задачи 2. необходимо произвести расчет коэффициентов корреляции, оценивающих тесноту связи между двумя случайными величинами.

Таблица входных данных имеет вид, представленный на рис. 4.

Рис. 4.

Для вычисления коэффициентов корреляции избираем в окне Анализ данных (рис. 1.) раздел Корреляция. В окне Корреляция (рис. 5.)нужно указать диапазон входного интервала (флажок Метки в первой строке(столбце) устанавливается тогда, когда диапазон включает заголовки данных). Матрица коэффициентов может быть представлена либо в квадратном, либо в прямоугольном виде. Результат расчета имеет вид окна рис. 6. Он имеет вид треугольной матрицы.

Рис. 5.

Рис. 6.

Наиболее информативной для решения задачи 2. является квадратная матрица. Из нее следует:

§ величина платы за электроэнергию прямо пропорциональна тарифу на элктроэнергию (коэффициент корреляции равен 1)*;

§ цены на уголь и сталь в значительно степени связаны с величиной тарифа на электроэнергию (коэффициенты корреляции соответственно равны 0, 93 и 0, 91);

§ цена на рожь связана с величиной тарифа на электроэнергию в значительно меньшей степени (коэффициент корреляции 0, 49);

§ высока степень связи цен на уголь и сталь (коэффициент корреляции равен 0, 93);

§ практически не связаны цены на рожь с ценами на сталь и уголь (коэффициенты соответственно равны 0, 34 и 0, 24).