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

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

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

Пример 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).

как построить корреляционную матрицу (для парной торговли)

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

Сегодня мы по пунктам разберем, как построить корреляционную матрицу в экселе за 5 минут.

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

Алгоритм построения:
1. Скачиваем исторические дневные данные (минимум за 1 год). я пользуюсь сайтом финама (раздел экспорт данных) http://www.finam.ru/profile/moex-akcii/gazprom/export/

2. Вставляем все скаченные данные в эксель

3. Находим разность между (динамику изменения котировок день/день

4. Настраиваем в экселе Data Analysis (Файл-параметры-надстройки-перейти-выделяем галочкой все пункты). далее во вкладке данные у нас появляется функция Data Analysis

5.Далее выделяем все колонки с разностью и нажимаем на кнопку Data Analysis

6. Следующим пунктом выбираем Correlation

7. И в итоге у нас получается вот такая корреляционная матрица

если будут вопросы, пишите их в комментариях

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

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, указывает на отсутствие или неабную корреляцию.
• Уравнение для коэффициента корреляции имеет следующий вид: где являются средними значениями выборок СРЗНАЧ(массив1) и СРЗНАЧ(массив2).

Пример

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

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

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

Корреляционный анализ в Excel: как посчитать коэффициент корреляции

Друзья, приветствую вас на WiFiGid! Буквально на днях разрабатывал модель для одной рыночно-нейтральной торговой стратегии, где как раз и нужно было рассчитывать коэффициенты корреляции (на самом деле корреляционную матрицу). Лично я сделал это на «питоне» – все равно потом ее как-то же надо обрабатывать, ведь миллионы ячеек глазом не просмотришь. Но на этапе предварительного анализа кому-то может быть проще использовать именно Excel. Поэтому в этой статье я и покажу вам основные варианты расчета коэффициента корреляции в Excel.

А если вдруг останутся какие-то вопросы или дополнения – добро пожаловать в комментарии. По мере возможности буду отвечать.

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

Коротко про коэффициент корреляции

Коэффициент корреляции отражает зависимость одного ряда от другого. Принимает значение от -1 до +1 (включая все промежуточные дробные значения):

• +1 – обе величины абсолютно зависимы, куда идет первая, туда идет и вторая. Например, на рынке криптовалюты все очень зависимо от биткоина. Куда он, туда и все. И коэффициент корреляции к «битку» для среднего альткоина обычно стремится к единице (но не так сильно).
• 0 – нет никакой зависимости. Каждый ходит, куда хочет.
• -1 – обратная зависимость. Т.е. если один вверх, то второй строго вниз.

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

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

Что-то я увлекся теорией, предлагаю уже переходить к практике.

Способ расчета 1 – Формула

Очень надеюсь, что вас не нужно учить писать формулы. Иначе просто закройте Excel

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

Под массивами подразумеваем ряды данных величин, между которыми и хотим найти корреляцию. Показываю на примере:

В данном примере у меня на очень малом промежутке времени получился коэффициент корреляции 0,29. Но главное, что способ работает. Так что если нужно получить быстро значение корреляции – круче формулы ничего и не придумаешь.

Способ 2 (корреляционная матрица) – Пакет анализа

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

1. Сначала нужно активировать этот пакет. Переходим в «Файл – Параметры – Надстройки».
2. В списке находим «Пакет анализа» (обычно он в разделе неактивных надстроек), выделяем его, убеждаемся что внизу установлено «Надстройки Excel» и нажимаем по кнопке «Перейти».

1. Ставим галочку напротив «Пакет анализа» и нажимаем кнопку «Ок». Это должно активировать пакет.

1. Теперь переходим на вкладку «Данные». Именно тут справа должна добавить кнопка «Анализ данных». Щелкаем по ней.

1. В списке выбираем «Корреляцию».

1. Задаем настройки. Во входной интервал нужно передать сразу все столбцы, для которых будем рассчитывать корреляцию. Выходные данные можно переместить на новый лист или задать им место здесь же.

Если вы задали всего два столбца, то и получите то же самое число, что и через формулу. Но если передали сразу несколько столбцов, то и на выходе должна получиться целая корреляционная матрица:

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

Видео по теме

Прикладываю видео по теме, где кроме основ есть те же понятия корреляции Спирмена и поля корреляции.