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

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

  • автор:

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

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

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

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

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

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

Показатели 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
  • Формулы и диапазоны
  • Фильтр и сортировка
  • Диаграммы и графики
  • Сводные таблицы
  • Печать документов
  • Базы данных и XML
  • Возможности Excel
  • Настройки параметры
  • Уроки Excel
  • Макросы VBA
  • Скачать примеры

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

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

Синтаксис

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

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

  • array1 Обязательный. Диапазон значений ячеек.
  • array2 Обязательный. Второй диапазон значений ячеек.

Замечания

  • Если массив или ссылочный аргумент содержит текст, логические значения или пустые ячейки, эти значения игнорируются; однако включаются ячейки с нулевыми значениями.
  • Если массивы 1 и массив2 имеют разное количество точек данных, функция CORREL возвращает ошибку #N/Д.
  • Если массив array1 или array2 пуст, или если значение s (стандартное отклонение) их значений равно нулю, функция CORREL возвращает #DIV/0! ошибку #ЗНАЧ!.
  • Если коэффициент корреляции ближе к +1 или -1, он указывает на положительную (+1) или отрицательную (-1) корреляцию между массивами. Положительная корреляция означает, что если значения в одном массиве увеличиваются, значения в другом массиве также увеличиваются. Коэффициент корреляции, близкий к 0, указывает на отсутствие или слабую корреляцию.
  • Уравнение для коэффициента корреляции имеет следующий вид:

x и y

где

Пример

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

Используйте функцию CORREL для возврата коэффициента корреляции двух наборов данных в столбце A & B с =CORREL(A1:A6, B2:B6). Результат — 0,997054486.

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

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

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

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

Научный форум dxdy

Последний раз редактировалось Pphantom 10.08.2017, 14:05, всего редактировалось 3 раз(а).

Наиболее распостранённый инструмент для задач эконометрики при преподавании ее в ВУЗах — это Ms Excel
Как эффективно провести множественный корреляционный анализ в MS Excel?
Давайте прямо признаем что в MS Excel нет прямых функций и надстроек непосредственно рассчитывающих множественный коэф-т корреляции $R$и множественный коэф-т детерминации $D=R^2$
Да, есть средство Анализ данных-> Корреляция . оно позволяет быстро построить корреляционную матрицу, точнее нижнюю ее половину. Но что с этим делать? Для вычислений коэф-та множественной корреляции надо ее ручками дополнить до полной, потом для применения формулы $R_<2,3. m>=\sqrt>>$» /> <br />вычислить пару определителей <img decoding=и $R_<11>$» /><br />(это правда можно сделать функцией МуОпред). <br />для случая 2-х факторной модели правда определитель <br /><img decoding=Как в гугл хром сделать стартовую страницу с закладками

  • Как в одну ячейку в excel вписать несколько значений
  • Как проверить hdr на телевизоре
  • Как создать базу данных в excel
  • Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *