Одной из задач статистического анализа является изучение связи между некоторыми наблюдаемыми переменными. Результаты, полученные при таком исследовании, позволяют прогнозировать развитие ситуации в случае изменения конкретных характеристик изучаемого объекта или процесса. Задача подобного исследования решается методами корреляционного анализа. Целью решения задачи является получение корреляционной матрицы.
В MS Excel для целей корреляционного анализа служит инструмент Корреляция, который позволяет получить корреляционную матрицу, содержащую коэффициенты корреляции между различными параметрами. Корреляционная матрица – это квадратная таблица, на пересечении соответствующих строк и столбцов которой располагаются корреляционные коэффициенты.
Для выполнения корреляционного анализа необходимо выполнить следующую последовательность операций:
1. На ленте Данные в группе Анализ кликнуть на пиктографической кнопке Анализ данных (Data Analysis).
2. В списке Инструменты анализа диалогового окна Анализ данных (Data Analysis) выбрать строку Корреляция (Correlation).
3. В раскрывшемся окне диалога Корреляция (Correlation) в поле Входной интервал (Input Range) ввести адресную ссылку на диапазон, содержащий анализируемые данные (входной интервал должен состоять не менее чем из двух столбцов), установить соответствующий переключатель Группировка (Grouped By), установить переключатель Параметры вывода (Output Options) и ввести адрес верхней левой ячейки, с которой будет начинаться диапазон для вывода результатов вычислений.
4. Кликнуть на кнопке ОК.
В результате выполнения вычислений в выходной диапазон будет помещена корреляционная матрица.
Пример . Есть статистические данные, регистрирующие количество выходных и праздничных дней в месяце в период с января по июнь и снимаемые со счетов суммы (рис. 1).
Требуется определить, существует ли взаимосвязь между количеством выходных и праздничных дней и величиной сумм, снимаемых со счетов.
Решение
1. Введем в диапазон рабочего листа (A1:C8) данные приведенной таблицы (рис. 2 ).
2. На ленте Данные в группе Анализ кликнем на пиктографической кнопке Анализ данных (Data Analysis).
3. В раскрывшемся окне диалога Анализ данных (рис.3) из списка выберем Корреляция (Correlation) .
4. В окне диалога Корреляция (Correlation) (рис. 4):
· в группе Входные данные в поле Входной интервал (Input Range) укажем адресную ссылку на диапазон, содержащий исходную таблицу (B2:C8), установим переключатель Группирование по столбцам (Columns) и установим флажок Метки в первой строке (Labels in first Row);
· в группе Параметры вывода (Output options) установим переключатель Выходной интервал (Output Range) и введем адресную ссылку на ячейку, которая будет началом диапазона выходного интервала (E2).
Кликнем на ОК.
Полученная корреляционная матрица является треугольной с единичной диагональю. На самом деле она должна рассматриваться как симметричная, т.е. rij = rji (рис 5.) .
Из полученного результата видно, что коэффициент корреляции между количеством выходных дней и величиной суммы, снимаемой со счетов
r= 0,91668.
Таким образом, существует сильная прямая линейная связь между количеством выходных и праздничных дней и величинами сумм, снимаемых со счетов.