Рекомендации по выполнению. Корреляционно-регрессионный и статистический анализ с использованием Excel
ЛАБОРАТОРНАЯ РАБОТА № 8
Корреляционно-регрессионный и статистический анализ с использованием Excel
Цель занятия: изучить возможности табличного процессора Excel для проведения корреляционно-регрессионного и статистического анализа.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
В экономике часто возникает задача подбора функциональной зависимости для двух наборов данных. В Excel введен набор функций, который позволяет решать эту задачу. Эти функции основаны на методе наименьших квадратов. Но регрессионный анализ — это не только метод наименьших квадратов. Относительно исходных данных делаются еще некоторые статистические предположения. В качестве результата выдаются не только коэффициенты функции, приближающие данные но и статистические характеристики полученных результатов. Набор подобных функций находится в категории Статистические.
Корреляция
Метод регрессионного и корреляционного анализа широко используется для определения тесноты связи между показателями, не находящимися в функциональной зависимости. Теснота связи между изучаемыми явлениями измеряется корреляционным отношением (для криволинейной зависимости). Для прямолинейной зависимости исчисляется коэффициент корреляции.
Коэффициент корреляции используется для определения наличия взаимосвязи и ее количественной оценки двух наборов данных. Например, можно установить зависимость между величиной складского товарооборота и размером складской площади. Коэффициент корреляции выборки представляет собой ковариацию двух наборов данных, деленную на произведение их стандартных отклонений. Уравнение для коэффициента корреляции имеет следующий вид:
где х и у – значения изучаемых признаков;
n - количество значений х и у в выборке;
ơх ơу - средние квадратичные отклонения;
х и у – средние величины по каждому признаку;
ơ 2ху - межгрупповая дисперсия результативного признака по фактическому.
Для характеристики изменчивости признаков используют следующие показатели: вариационный размах, среднеквадратическое отклонение и коэффициент вариации.
Вариационный размах (амплитуда колебания) — разница между максимальным и минимальным значениями изучаемого признака. Размах дает представление о крайних пределах вариации признаков, но не показывает степени изменчивости.
Среднее квадратичное отклонение (ơ) характеризует степень изменчивости признака в абсолютных величинах. В нормальных или близких к ним вариационных рядах отклонение вправо и влево от средней (х) относятся на три сигмы (3σ). По этому показателю средней изменчивости можно ориентировочно определить минимальное и максимальное значения х.
Коэффициент вариации характеризует изменчивость признака в изучаемой совокупности в относительных величинах. Его исчисляют как процентное отношение среднего квадратичного отклонения изучаемой совокупности к средней арифметической. Изменчивость признака считается незначительной, когда коэффициент вариации не более 10%, средней — от 11 до 30%, высокой — свыше 30%:
Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, т.е. большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция) или, наоборот, малые значения одного набора связаны с большими значениями другого набора (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция близка к нулю). Для вычисления коэффициента корреляции между двумя наборами данных используется статистическая функция
КОРРЕЛ(находится в категории Статистические). Функция КОРРЕЛ рассчитывает коэффициент корреляции между диапазонами или массивами ячеек.
Синтаксис функции выглядит так: КОРРЕЛ(массив1;массив2), где массив1 (fх)- — это первый интервал ячеек со значениями; массив2 (fx) — это второй интервал ячеек со значениями.
Аргументами функции могут быть числа, имена, массивы или ссылки содержащие числа. Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются. Однако ячейки с нулевыми значениями учитываются.
Если массив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. Для его исчисления предварительно вычисляют частные коэффициенты корреляции
Регрессия
Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия значений одной или более зависимых переменных на отдельную зависимую переменную Например, на объем оптово-складского товарооборота влияют складская площадь, величина товарных запасов и другие факторы. Регрессия пропорционально распределяет меру качества по этим факторам.
Линейное уравнение регрессии имеет следующий вид:
Y=a + bx, (
где а - независимая переменная;
Ь - значение коэффициента при переменной;
х - значение переменной.
Коэффициент регрессии (R) — величина, которая характеризует, насколько изменяется изучаемый признак исследуемого явления (совокупности) при изменении корреляционного признака на определенную величину. Коэффициент регрессии исчисляется по формулам:
Коэффициент регрессии имеет два значения и включает коэффициент корреляции (r) и ơх ơу - средние квадратичные отклонения по обоим признакам Он может быть положительным и отрицательным в зависимости от значения коэффициента корреляции.
Статистический анализ
В Excel имеется набор инструментов для анализа данных, называемый «пакет анализа данных», который может быть использован для решения сложных статистических задач. Для использования одного из этих инструментов необходимо указать входные данные и выбрать параметры, анализ которых будет проведен с помощью статистической макрофункции, и результаты будут представлены в выходном диапазоне. Некоторые инструменты позволяют представить результаты в графическом виде.
После выполнения команды СЕРВИС —Анализ данных из меню на экране появляется окно диалога, в котором перечислены инструменты статистического анализа данных.
Инструмент Генерация случайных чисел позволяет строить последовательности случайных чисел, распределенных в соответствии с нормальным распределением, распределением Бернулли, Пуассона, равномерным, биномиальным и другими видами распределений.
Инструмент Описательная статистика предлагает таблицу основных статистических характеристик для заданного множества исходных данных: среднее, стандартная ошибка, медиана, мода, стандартное отклонение, дисперсия и т.д. Для вывода таблицы должен быть включен флажок Итоговая статистика, можно также проверить распределение на «нормальность».
Инструмент Гистограмма строит диаграмму, в которой для исходного множества значений определяется количество значений (частот), попадающих в интервалы разбиения — карманы. При построении гистограммы следует ввести: место расположения исходных данных, границы интервалов разбиения и верхнюю левую ячейку выходного диапазона. Если интервал карманов пуст, создаются равные интервалы разбиения в количестве, равном квадратному корню из числа входных значений. Для получения графика необходимо установить флажок Вывод графика, иначе будет выведена только таблица с указанием карманов и частот для каждого из них. Флажок Парето используется для сортировки выходных, значений в порядке убывания частот, флажок Интегральный процент — для включения в таблицу накопленных частот в процентах для интервала гистограммы.
Инструмент Выборка извлекает из множества значений заданное их подмножество либо случайным образом, либо выбирая каждое н-е значение.
Инструмент Скользящее среднее позволяет выполнить анализ тенденции путем сглаживания колебаний измерений за некоторый период времени.
ПРАКТИКУМ
Задание 1. Рассчитать коэффициент корреляции, характеризующий тесноту связи между величиной оптово-складского товарооборота и размером складской площади фирмы. Значения элементов X (складская площадь) и Y (оптово-складской товарооборот) указаны в табл. 1.15.
Рекомендации по выполнению
1. Создайте таблицу с исходными данными.
Исходные данные
2. Рассчитайте коэффициент корреляции по следующей формуле:
=КОРРЕЛ(В2:В5;С2:С5). Функция КОРРЕЛ может быть вызвана
с помощью Мастера функций из категории Статистические.
3. В диалоговом окне функции указываются массивы Складской
площади и Оптово-складского товарооборота.
4. В итоге должен быть получен результат КОРРЕЛ =0,353990796.
Задание 2.На основе данных предыдущего задания выполнить регрессионный анализ.