Лабораторная работа № 8 Корреляционно-регрессионный и статистический анализ с использованием Excel

Цель занятия: изучить возможности табличного процессора Excel для проведения корреляционно-регрессионного и статистического анализа.

Теоретическая часть

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

Корреляция

Метод регрессионного и корреляционного анализа широко использу­ется для определения тесноты связи между показателями, не находя­щимися в функциональной зависимости. Теснота связи между изуча­емыми явлениями измеряется корреляционным отношением (для криволинейной зависимости). Для прямолинейной зависимости исчис­ляется коэффициент корреляции.

Коэффициент корреляции используется для определения наличия взаи­мосвязи и ее количественной оценки двух наборов данных. Например, можно установить зависимость между величиной складского товаро­оборота и размером складской площади. Коэффициент корреляции вы­борки представляет собой ковариацию двух наборов данных, деленную на произведение их стандартных отклонений. Уравнение для коэффи­циента корреляции имеет следующий вид:

где х и у – значения изучаемых признаков;

n - количество значений х и у в выборке;

ơ_х ơ_у - средние квадратичные отклонения;

х и у – средние величины по каждому признаку;

ơ ²_ху - межгрупповая дисперсия результативного признака по фактическому.

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

Вариационный размах (амплитуда колебания) — разница между макси­мальным и минимальным значениями изучаемого признака. Размах дает представление о крайних пределах вариации признаков, но не показы­вает степени изменчивости.

Среднее квадратичное отклонение (ơ) характеризует степень измен­чивости признака в абсолютных величинах. В нормальных или близких к ним вариационных рядах отклонение вправо и влево от средней (х) относятся на три сигмы (3σ). По этому показателю средней изменчиво­сти можно ориентировочно определить минимальное и максимальное значения х.

Коэффициент вариации характеризует изменчивость признака в изуча­емой совокупности в относительных величинах. Его исчисляют как процентное отношение среднего квадратичного отклонения изучаемой совокупности к средней арифметической. Изменчивость признака счи­тается незначительной, когда коэффициент вариации не более 10%, сред­ней — от 11 до 30%, высокой — свыше 30%:

Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, т.е. большие значения из одного набо­ра данных связаны с большими значениями другого набора (положи­тельная корреляция) или, наоборот, малые значения одного набора свя­заны с большими значениями другого набора (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреля­ция близка к нулю). Для вычисления коэффициента корреляции между двумя наборами данных используется статистическая функция

КОРРЕЛ (находится в категории Статистические). Функция КОРРЕЛ рассчитывает коэффициент корреляции между диа­пазонами или массивами ячеек.

Синтаксис функции выглядит так: КОРРЕЛ(массив1;массив2), где массив1 (fх)- — это первый интервал ячеек со значениями; массив2 (f_x) — это второй интервал ячеек со значениями.

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

Если массив1 и массив2 имеют различное количество точек данных (разное количество ячеек), то функция КОРРЕЛ объявляет значение ошибки.

Если массив1 и массив2 пусты или стандартное отклонение (s) их зна­чений равно нулю, то функция КОРРЕЛ указывает значение ошибки. Корреляционные связи различают:

• по количеству признаков связи: однофакторные и многофактор­ные;

• по направленности: положительные и отрицательные;

• по аналитическому выражению: прямолинейные и криволи­нейные.

Тесноту корреляционной связи определяют с помощью корреляцион­ной решетки, построенной в прямоугольных осях координат.

Если частоты распределяются ближе к диагонали, то между признака­ми обнаружена высокая связь. Размещение частот близко к диагонали, пересекающей решетку с левого нижнего в правый верхний угол, сви­детельствует о положительной направленности, а с верхнего левого в правый нижний угол — об отрицательной направленности. Дугооб­разное размещение частот в решетке характеризует криволинейную связь и беспорядочное отсутствие связи. В процессе выбора модели урав­нения учитывают также характер динамического ряда. Уравнение пря­мой используется, когда на протяжении изучаемого периода сохраня­ется более или менее стабильный абсолютный прирост явления, При зигзагообразном возрастающем изменении динамического ряда без ста­бильного его роста и снижении применяют уравнение параболы. При отрицательной направленности и изменении динамических рядов по зиг­загообразной снижающей, а затем повышающей кривой без стабильно­го их снижения и роста используют уравнение гиперболы или кривой показательной функции.

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

Коэффициент линейной корреляции — показатель, отображающий на­правление и тесноту связи между признаками при прямолинейных (или близких к ним) взаимозависимостях. Он колеблется в пределах от 0 до ±1. Знак «+» означает прямую, а знак «-» — обратную связь. Значе­ния коэффициента линейной корреляции и теснота связи между при­знаками указаны в табл. 1.1.

Таблица 1.1

Значение коэффициента линейной корреляции		Теснота связи
	±0,15	Связь отсутствует
от±	0,1 6 до ±0,20	Плохая связь
от±	0,21 до ±0,30	Слабая связь
от ±0,31 до ±0,40		Умеренная связь
от±	0,41 до ±0,60	Средняя связь
от±	0,61 до ±0,80	Высокая связь
от±	0,81 до ±0,90	Очень высокая	связь
от±	0,91 до ± 1,0	Полная связь

Теснота связи двух или более признаков выражается коэффициентом множественной корреляции (совокупной). Коэффициент множественной корреляции — число всегда положитель­ное и изменяется от 0 до 1. Для его исчисления предварительно вычис­ляют частные коэффициенты корреляции