Как построить корреляционную матрицу в excel

Функция КОРРЕЛ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Функция КОРРЕЛ возвращает коэффициент корреляции двух диапазонов ячеев. Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера.

Синтаксис

КОРРЕЛ(массив1;массив2)

Аргументы функции КОРРЕЛ описаны ниже.

• массив1 — обязательный аргумент. Диапазон значений ячеок.
• массив2 — обязательный аргумент. Второй диапазон значений ячеев.

Замечания

• Если аргумент массива или ссылки содержит текст, логические значения или пустые ячейки, эти значения игнорируются; однако ячейки с нулевыми значениями включаются.
• Если массив1 и массив2 имеют различное количество точек данных, то correl возвращает #N/A.
• Если массив1 или массив2 пуст или если s (стандартное отклонение) их значений равно нулю, то corREL возвращает значение #DIV/0! ошибку «#ВЫЧИС!».
• Так как коэффициент корреляции ближе к +1 или -1, он указывает на положительную (+1) или отрицательную (-1) корреляцию между массивами. Положительная корреляция означает, что при увеличении значений в одном массиве значения в другом массиве также увеличиваются. Коэффициент корреляции, который ближе к 0, указывает на отсутствие или неабную корреляцию.
• Уравнение для коэффициента корреляции имеет следующий вид:

где

Пример

В следующем примере возвращается коэффициент корреляции двух наборов данных в столбцах A и B.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Корреляционный и статистический анализ экспериментальных данных в программе Excel

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

3.

На основании значений матрицы и
парных коэффициентов корреляции r(xj, xk)
могут делаться разнообразные выводы о
природе
связей
между
явлениями,
зависящие от направления исследования и
изучаемой предметной области.
Корреляционная
матрица
—
это
квадратная
таблица,
в
которой
на
пересечении соответствующих строки и
столбца находится коэффициент корреляции
между соответствующими параметрами.

4.

1
1
2
3
4
5
6
7
2
3
4
5
6
7
r=1
r(1;2)
r=1
r(1;3) r(2;3)
r=1
r(1;4) r(2;4) r(3;4)
r=1
r(1;5) r(2;5) r(3;5) r(4;5)
r=1
r(1;6) r(2;6) r(3;6) r(4;6) r(5;6)
r=1
r(1;7) r(2;7) r(3;7) r(4;7) r(5;7) r(6;7)
1, 2, …, 7 – отдельные показатели;
r – коэффициент корреляции между показателями.
r=1

5.

Коэффициентом
корреляции
называется
величина, абсолютное значение которой оценивает
тесноту взаимосвязи между двумя показателями.
Значение коэффициента корреляции будем
интерпретировать следующим образом:
0 < |r| ≤ 0,2 – связи нет;
0,2 < |r| ≤ 0,4 – связь слабая;
0,4 < |r| ≤ 0,7 – связь средняя;
0,7 < |r| ≤ 1 – связь сильная;
r < 0 – связь обратная;
r > 0 – связь прямая.
Коэффициент корреляции находящийся на
пересечении одного и того же показателя равен 1,
так как каждый столбец во входном диапазоне
полностью коррелирует сам с собой.

6.

Хотя в результате будет получена
треугольная
матрица,
корреляционная
матрица симметрична. Подразумевается, что
в пустых клетках в правой верхней половине
таблицы находятся те же коэффициенты
корреляции, что и в нижней левой
(симметрично расположенные относительно
диагонали). То есть r(xj, xk) = r(xk, xj).
Например: r(2;5) = r(5;2)

7. Этапы корреляционного анализа

1. Оформить в программе Excel
экспериментальные данные в виде таблицы;
2. Построить корреляционную матрицу c
помощью пакета “Анализ данных…”;
3. Оценить каждый коэффициент корреляции;
4. Нарисовать граф (графическую структуру)
корреляционной матрицы средствами
рисования программы Excel или Word;
5. Сделать вывод.

8.

§2 Построение корреляционной матрицы в
Excel
В электронной таблице Excel для вычисления
корреляционных матриц используется процедура
Корреляция из пакета или надстройки Анализ
данных.
Процедура
позволяет
получить
корреляционную
матрицу,
содержащую
коэффициенты корреляции между различными
параметрами.
Для реализации процедуры необходимо
установить надстройку «Пакет анализа», которая
выведет команду Анализ данных в пункт Данные
(см лекцию2).

9. Пусть спортивные результаты группы спортсменов набраны в программе Excel.

Тогда для построения
корреляционной матрицы
по показателям надо:
1) Выбрать пункт «Данные»
→ команду «Анализ
данных» → инструмент
«Корреляция» → ОК
Откроется диалоговое окно!
2) Для входного интервала
на рабочем листе выделить
диапазон всех данных с
метками (С5-F13)

10. 3) Установить галочку в окошке «Метки в первой строке» → Группирование по столбцам → Выходной интервал в пустой ячейке → ОК

Построится
корреляционная матрица
Все получившиеся
коэффициенты
корреляции округлить
до сотых, используя
инструмент
«Уменьшение
разрядности»

11. §3 Граф корреляционной матрицы

Граф построенный на основе корреляционной
матрицы отображает наиболее значимые связи
между спортивными результатами, а так же
визуально выявляет не значимые показатели.

12. Построение графа корреляционной матрицы

1) Сначала вводятся обозначения:
— сильные связи
— связи средней силы
— слабые связи
2) Далее с помощью инструмента
— «надпись»
обозначения
показателей
располагаются
равномерно, по порядку, по кругу. Для этого
удобно на окружности отметить нужное количество
точек на одинаковом расстоянии. У каждой
надписи отменить контур и заливку фигуры.

13. Например:

1
Например:
3) Соединить
показатели
7
линиями,
применяя к
ним нужный
формат
6
контура.
Если r(3;7) указывает
на сильную взаимосвязь
между показателями 3 и
7, то от точки 3 до точки
7 рисуем линию и
изменяем её толщину.
2
3
5
4
Причём не важно, какая это
связь прямая или обратная.

14. 4) Когда все значимые связи будут изображены на графе, удалить круг (окружность)

1
4) Когда все
значимые связи
7
будут изображены
на графе, удалить
круг (окружность)
2
6
5
Если |r| ≤ 0,2, то на
графике такую взаимосвязь
отображать не нужно
3
4

15.

Например:
Пусть показатель П3 результаты тройного прыжка ПБР
в данной группе спортсменов.
Тогда из данного графа
видно, что тройной прыжок П
СР
выпадает из общей картины
взаимосвязей.
Б4
ВР
СБ
П5
П3
Следовательно, в данной группе спортсменов
тройной прыжок можно исключить из тренировочного
процесса и заменить его другими упражнениями,
которые повышают эффективность тренировок.

16. §4 Статистический анализ в Excel

Microsoft Excel содержит большое число
встроенных статистических функций.
После установки надстройки «Пакет анализа»
расширяются
возможности
обработки
статистической информации, кроме того процесс
статистического анализа значительно упрощается.
Средства,
включенные
в
«Пакет
анализа»
позволяют
выполнить
проверку
статистических гипотез о параметрах распределения
случайных величин, построение линейных и
нелинейных уравнений регрессии, прогнозирование
и многое другое.

17. Одним из инструментов пакета «Анализ данных…» является инструмент «Описательная статистика».

Цель
«Описательной статистики» обобщить первичные результаты, полученные
в результате наблюдений и экспериментов.
Это средство анализа служит для создания
одномерного
статистического
отчёта
содержащего информацию о центральной
тенденции и изменчивости или вариации
входных данных.

18.

В состав описательной статистики входят
следующие характеристики:
ЦЕНТРАЛЬНЫЕ ТЕНДЕНЦИИ
среднее;
стандартная
ошибка;
медиана;
мода
ИЗМЕНЧИВОСТЬ ДАННЫХ
стандартное
отклонение;
дисперсия выборки;
эксцесс;
асимметричность;
интервал;
минимум;
максимум;
сумма;
счет

19. Порядок построения таблицы основных статистических характеристик

Пусть в программе Excel набраны данные.
Тогда для построения таблицы с итоговой
описательной статистикой надо:
1) Выбрать пункт «Данные» → команду «Анализ
данных» → инструмент «Описательная
статистика» → ОК
Откроется диалоговое окно!
2) Для входного интервала на рабочем листе
выделить диапазон данных с метками
(названиями столбцов)

20.

3) Установить галочку в окошке «Метки в
первой строке» → Группирование по
столбцам → Выходной интервал в пустой
ячейке → Флажок «Итоговая статистика»
→ уровень надёжности 95% → ОК

21.

Округлить характеристики,
которые необходимо, до
сотых с помощью значка на
панели инструментов
«Уменьшение
разрядности».
Сделать вывод

22. Подготовить презентацию к защите РГР «Корреляционный и статистический анализ данных»

23. Требования к презентации:

1. Презентация должна состоять минимум из 7-ми
слайдов (титульный лист, исходные данные,
корреляционная
матрица,
граф
корреляционной
матрицы,
итоговая
статистика одного показателя гистограмма и
вывод)
2. Эффект смены каждого слайда.
3. Эффекты анимации (вход каждому объекту на
слайдах, выделение отдельных элементов.)
4. Полная разгруппировка графа и постепенное
рисование отдельных элементов (см.след. слайд).
5. Все эффекты по щелчку.
6. Поддержание единого стиля.

24.

Порядок создания слайда:
1. Создать картинку, т.е. расположить все нужные
объекты на слайде в нужных местах. Все лишние
объекты удалять.
2. Установить фон слайда или оформление слайда
3. Установить смену слайда
4. Установить эффект входа объекту «заголовок»
(Выделить заголовок → Анимация → Настройка
анимации → Добавить эффект → Вход → выбрать
эффект)
5. Установить эффекты входа остальным объектам
слайда
6. При необходимости установить эффект выделения
отдельному элементу (Выделить элемент →
Добавить эффект → Выделение → выбрать
эффект)

Построение корреляционной матрицы

Пример 3.10. Построить корреляционную матрицу по следующим данным:

1. Ввести данные на Лист 1.

После ввода данных, получим таблицу в окне электронной таблицы Excel, изображенную на рисунке 3.3: Рисунок 3.3 – Таблица ввода данных

1. Выбираем опцию Сервис.
2. Выбираем опцию Анализ данных.

В результате появится окно, изображенное на рисунке 3.2.

1. Выбираем опцию Корреляция.

В результате появится окно, изображенное на рисунке 3.4: Рисунок 3.4 – Окно Корреляция

1. Активизируем окно Входной интервал (установить стрелку мыши в окне и нажать левую клавишу)
2. Выбираем ячейку А2 и при нажатой левой клавише мыши передвигаемся к ячейке D8.

Номера ячеек, из которых будут взяты исходные данные, автоматически заносятся в окно Входной интервал. 7. Ставим флажок в окне Новый рабочий лист. 8. Нажимаем кнопку . В результате получим корреляционную матрицу, представленную на рисунке 3.5: Рисунок 3.5 – корреляционная матрица

Построение модели множественной линейной регрессии и ее анализ

Пример 3.11.По данным примера 14 найти коэффициент корреляции, индекс детерминации, уравнения множественной линейной регрессии,F-статистику,t-статистику, доверительные интервалы для параметров уравнения регрессии. Решение. 1. Введем данные на Лист 1. 2. Выбираем опцию Сервис. 3. Выбираем опцию Анализ данных. 4. Выбираем опцию Регрессия. В результате появится окно, изображенное на рисунке 3.6: Рисунок 3.6 – Окно Регрессия

1. Активизируем окно Входной интервал Y.
2. Выбираем ячейку А2 и при нажатой левой клавише мыши передвигаемся к клетке A8.
3. Активизируем окно Входной интервал Х.
4. Выбираем ячейку В2 и при нажатой левой клавише мыши передвигаемся к клетке D8 (если участвуют все переменные).

Номера ячеек, из которых будут взяты исходные данные, автоматически заносятся в окно Входной интервал. 9. Ставим маркер в окне Новый рабочий лист. 10. Ставим маркер в окне Остатки. 11. Нажимаем кнопку . В результате появится таблица, изображенная на рисунке 3.7: Рисунок 3.7 – Вывод итогов Из таблицы, представленной на рис. 3.7 находим:

• множественный коэффициент корреляции – 0,791;
• индекс детерминации – 0,626;
• уравнения множественной линейной регрессии

• F-статистику – 1,674;
• t-статистики:

для коэффициента при переменной : -1,486; для коэффициента при переменной : -1,062; для коэффициента при переменной : -0,008;

• доверительные интервалы для параметров уравнения регрессии.

для коэффициента при переменной : (-49,96;18,14); для коэффициента при переменной : (-31,28;15,62); для коэффициента при переменной : (-2,77; 2,75).

Коэффициент парной корреляции в Excel

Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -1 до 1. Если коэффициент расположился около 0, то говорят об отсутствии связи между переменными.

Если значение близко к единице (от 0,9, например), то между наблюдаемыми объектами существует сильная прямая взаимосвязь. Если коэффициент близок к другой крайней точке диапазона (-1), то между переменными имеется сильная обратная взаимосвязь. Когда значение находится где-то посередине от 0 до 1 или от 0 до -1, то речь идет о слабой связи (прямой или обратной). Такую взаимосвязь обычно не учитывают: считается, что ее нет.

Расчет коэффициента корреляции в Excel

Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.

Значения показателей x и y:

Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:

Чтобы упростить ее понимание, разобьем на несколько несложных элементов.

1. Найдем средние значения переменных, используя функцию СРЗНАЧ:
2. Посчитаем разницу каждого y и yсредн., каждого х и хсредн. Используем математический оператор «-».
3. Теперь перемножим найденные разности:
4. Найдем сумму значений в данной колонке. Это и будет числитель.
5. Для расчета знаменателя разницы y и y-средн., х и х-средн. Нужно возвести в квадрат.
6. Находим суммы значений в полученных колонках (с помощью функции АВТОСУММА). Перемножаем их. Результат возводим в квадрат (функция КОРЕНЬ).
7. Осталось посчитать частное (числитель и знаменатель уже известны).

Между переменными определяется сильная прямая связь.

Встроенная функция КОРРЕЛ позволяет избежать сложных расчетов. Рассчитаем коэффициент парной корреляции в Excel с ее помощью. Вызываем мастер функций. Находим нужную. Аргументы функции – массив значений y и массив значений х:

Покажем значения переменных на графике:

Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.

Матрица парных коэффициентов корреляции в Excel

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

Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».

1. На вкладке «Данные» в группе «Анализ» открываем пакет «Анализ данных» (для версии 2007). Если кнопка недоступна, нужно ее добавить («Параметры Excel» — «Надстройки»). В списке инструментов анализа выбираем «Корреляция».
2. Нажимаем ОК. Задаем параметры для анализа данных. Входной интервал – диапазон ячеек со значениями. Группирование – по столбцам (анализируемые данные сгруппированы в столбцы). Выходной интервал – ссылка на ячейку, с которой начнется построение матрицы. Размер диапазона определится автоматически.
3. После нажатия ОК в выходном диапазоне появляется корреляционная матрица. На пересечении строк и столбцов – коэффициенты корреляции. Если координаты совпадают, то выводится значение 1.

Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.

Изобразим наглядно корреляционные отношения с помощью графиков.

1. Сильная прямая связь между y и х1.
2. Сильная обратная связь между y и х2. Изменения значений происходят параллельно друг другу. Но если y растет, х падает. Значения y увеличиваются – значения х уменьшаются.
3. Отсутствие взаимосвязи между значениями y и х3. Изменения х3 происходят хаотично и никак не соотносятся с изменениями y.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры