Методы математической статистики Excel

Рис. 2.96 
 

Поиск статистических зависимостей. Корреляция 

  Знание  взаимосвязей между выборками важно  для прогнозирования ситуации и  принятия решений. Для оценки взаимосвязи  между выборками (переменными X и Y) применяют регрессионный анализ, корреляционный и ковариационный анализ. Первый устанавливает форму взаимозависимости, вторые - степень связи выборок. Корреляцию применяют, когда выборки представлены в безразмерном виде (с разной размерностью), например вес и рост. Ковариацию можно применять для выборок с одинаковой размерностью, например продажи до рекламной компании и продажи после рекламной компании.

  Степень связи двух выборок (случайных величин  X и Y) оценивается ковариацией и коэффициентом корреляции R. Ковариация есть среднее произведений отклонений для каждой пары значений выборок. Коэффициент корреляции выборки представляет собой ковариацию двух выборок, деленную на произведение их стандартных отклонений (см. справку по F1).

  Ковариация принимает значения в единицах анализируемых выборок. Коэффициент корреляции R принимает значения от –1 до 1. Если R=0 – зависимости нет, R>0 – зависимость прямо пропорциональная, R<0 – зависимость обратно пропорциональная.

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

  Функции Excel КОРРЕЛ, КОВАР и инструменты Корреляция, Ковариация вычисляют степень линейной взаимозависимости между выборками. Если коэффициент корреляции |R|>0.6, то линейную зависимость между выборками считают выявленной, при |R|<0.4 – не выявленной.

  Определим степень взаимосвязи между доходом  семьи и числом посещений супермаркета в месяц – рис. 2.97. Тут же показан  результат функции КОРРЕЛ(A2:A12;B2:B12)= –0.981225708. Это говорит о высокой  степени обратной линейной зависимости между рассматриваемыми параметрами. 

Рис. 2.97 

  Теперь  добавим третий параметр – среднюю  сумму одной покупки (рис. 2.98) и  применим инструмент Корреляция: меню СервисàАнализ данных…. Параметры заполните как на рис. 2.99.

  Результат показан в правой части рис. 2.98: в ячейках E1:H4 вычислена корреляционная матрица, на пересечении столбцов и строк которой записаны коэффициенты корреляции между параметрами (столбцами).

  В результате анализа выявлены:

сильная степень обратной линейной зависимости между столбцом 1 и столбцом 2 (R= –0,9812257);

сильная степень прямой линейной зависимости  между столбцом 1 и столбцом 3 (R= 0,99497);

сильная степень обратной линейной зависимости  между столбцом 2 и столбцом 3 (R= –0,982206); 

Рис. 2.98 

Рис. 2.99 

  Задания для самостоятельного выполнения:

1. Определить степень взаимосвязи между валовым доходом и расходами компании, заданной следующими выборками:

2. Определить  степень взаимосвязи между месяцами (сезонами) и доходами компьютерных и строительных компаний: