Пример выполнения корреляционного анализа в Excel

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

• Предназначение корреляционного анализа
• Исполняем корреляционный анализ
  • Способ 1: применяем функцию КОРРЕЛ
  • Способ 2: используем “Пакет анализа”

  Предназначение корреляционного анализа

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

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

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

  Исполняем корреляционный анализ

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

  

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

  Способ 1: применяем функцию КОРРЕЛ

  В Excel предусмотрена особая функция, позволяющая создать корреляционный анализ – КОРРЕЛ. Ее синтаксис смотрится последующим образом:

  Порядок действий при работе с данным инвентарем последующий:

  1. Встаем в вольную ячейку таблицы, в которой планируем высчитать коэффициент корреляции. Потом щелкаем по значку “fx (Вставить функцию)” слева от строчки формул.
  2. В открывшемся окне вставки функции избираем категорию “Статистические” (либо “Полный алфавитный список”), посреди предложенных вариантов отмечаем “КОРРЕЛ” и щелкаем OK.

  Способ 2: используем “Пакет анализа”

  Другим методом выполнения корреляционного анализа является внедрение “Пакета анализа”, который за ранее необходимо включить. Для этого:

  1. Заходим в меню “Файл”.
  2. В списке слева избираем пункт “Характеристики”.
  3. В показавшемся окне кликаем по подразделу “Надстройки”. Потом в правой части окна в самом низу для параметра “Управление” избираем “Надстройки Excel” и щелкаем “Перейти”.
  4. В открывшемся окошке отмечаем “Пакет анализа” и подтверждаем действие нажатием клавиши OK.

  Все готово, “Пакет анализа” активирован. Сейчас можно перейти к выполнению нашей главный задачки:

  1. Жмем клавишу “Анализ данных”, которая находится во вкладке “Данные”.
  2. Покажется окно, в котором представлен список доступных вариантов анализа. Отмечаем “Корреляцию” и щелкаем OK.
  3. На дисплее отобразится окно, в котором нужно указать последующие характеристики:
     • “Входной интервал”. Выделяем весь спектр анализируемых ячеек (т.е. сходу оба столбца, а не по одному, как это было в описанной чуть повыше способе).
     • “Группирование”. На выбор предложено два варианта: по столбцам и строчкам. В нашем случае подступает 1-ый вариант, т.к. конкретно схожим образом размещены анализируемые данные в таблице. Если в выделенный спектр включены заглавия, следует поставить галочку напротив пт “Метки в первой строке”.
     • “Характеристики вывода”. Можно избрать вариант “Выходной интервал”, в этом случае результаты анализа будут вставлены на текущем листе (будет нужно указать адресок ячейки, начиная с которой будут выведены итоги). Также предлагается вывод результатов на новеньком листе либо в новейшей книжке (данные будут вставлены в самом начале, т.е. начиная с ячейки A1). В качестве примера оставляем “Новейший рабочий лист” (избран по дефлоту).
     • Когда все готово, щелкаем OK.
  4. Получаем этот же самый коэффициент корреляции, что и в первом способе. Это гласит о том, что в обоих вариантах мы все сделали правильно.

  Заключение

  Таковым образом, выполнение корреляционного анализа в Excel – довольно автоматическая и обычная в освоении процедура. Все что необходимо знать – где отыскать и как настроить нужный инструмент, а в случае с “Пакетом решения”, как его активировать, если ранее он уже не был включен в параметрах программки.

  Коэффициент корреляция в excel — примеры как использовать

  Microsoft Office Excel нередко употребляется для анализа данных и внедрения разных функций, которые интегрированы в программку. Для выявления зависимости одной величины от иной ведутся корреляционные исследования, которые довольно популярны в статистике. Сейчас разберемся, как осуществляется корреляция в excel.

  Введение

  Чтоб высчитать коэффициент корреляции, нужно пользоваться специальной функцией КОРРЕЛ. Формула содержит аргументы для 2-ух массивов данных, меж которыми необходимо отыскать зависимость. Приобретенный коэффициент корреляции в excel можно расшифровать последующим образом:

  1. Если значение близко к 1 либо -1, то существует мощная ровная либо оборотная связь меж величинами.
  2. Коэффициент около 0,5 либо -0,5 гласит о том, что меж массивами слабенькая связь.
  3. Если выходит число близкое к нулю, то величины не соединены меж собой.

  При всем этом есть ряд особенностей использования функции КОРРЕЛ:

  1. Программка не учитывает в расчете пустые ячейки, элементы массива с текстовым форматом и ячейки с логическими операторами. При всем этом числа в виде текста будут учтены.
  2. Размеры 2-ух массивов должны быть схожими, в неприятном случае редактор выдаст ошибку типа Н/Д.
  3. При корреляционном анализе недозволено применять пустые столбцы либо спектр с нулевыми значениями.

  Примеры использования

  Разглядим несколько задач, чтоб осознать механизм работы статистической функции.

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

  

  В случайной ячейке записываете формулу со ссылкой на два спектра и получаете число.

  

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

  Пример 2.

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

  

  В этом случае коэффициент корреляции стремится к -1, что гласит о мощной оборотной зависимости. Другими словами с повышением цены продукта, реализации падают.

  Пример 3.

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

  

  Приобретенный итог гласит о слабенькой связи этих категорий.

  Остальные способности

  Также с помощью функции КОРРЕЛ можно провести наиболее сложные исследования. Примером является парная и множественная корреляция. Отличие их заключается в том, что при множественной корреляции независящих переменных, влияющих на величину, быть может две и наиболее, а при парной – лишь одна. Эти инструменты употребляют спецы при анализе огромного количества данных для проведения статистических исследовательских работ и выявления сложных зависимостей одной величины от огромного количества остальных либо их отсутствие.

  Также можно создать график, чтоб наглядно показать зависимость одной величины от иной. Создадим это для первого примера с рекламой и продажами.

  

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

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

  Функция КОРРЕЛ для определения связи и корреляции в Excel

  Функция КОРРЕЛ в Excel употребляется для расчета коэффициента корреляции меж для 2-ух исследуемых массивов данных и возвращает соответственное числовое значение.

  Примеры использования функции КОРРЕЛ в Excel

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

  

  Формула для расчета:

  

  • B3:B13 – спектр ячеек, в которых хранятся данные о среднем курсе бакса;
  • C3:C13 – спектр ячеек со значениями средней заработной платы.

  

  Приобретенный итог близок к 1 и свидетельствует о мощной прямой связи меж исследуемыми величинами. Но прямо пропорциональной зависимости меж ними нет, другими словами на повышение средней заработной платы оказывали воздействие и остальные причины.

  Определение коэффициента корреляции воздействия действий на итог

  Пример 2. Два мощных кандидата на руководящий пост пользовались услугами 2-ух разных пиар-агентств для пуска предвыборной компании, которая продолжалась 15 дней. Раз в день проводился соцопрос независящими исследователями, которые определяли процент поддержки 1-го и второго кандидата. Респонденты могли отдавать предпочтение первому, второму кандидату либо выступать против обоих. Найти, как влияла любая предвыборная кампания на степень поддержки кандидатов, какая из их оказалась наиболее действенной?

  

  Произведем расчет коэффициентов корреляции при помощи формул:

  • A3:A17 – массив ячеек, содержащий номера дней предвыборной кампании;
  • B3:B17 и C3:C17 – спектр ячеек, содержащие данные о проценте поддержки первого и второго кандидатов соответственно.

  

  Как видно, уровень поддержки первого кандидата увеличивался с каждым деньком кампании, потому коэффициент корреляции в первом случае стремится к единице. На старте кампании 2-ой кандидат имел больший процент поддержки, и это значение в протяжении первых 5 дней показывало положительную динамику конфигураций. Но потом уровень поддержки стал понижаться, и к 15-му деньку свалился ниже исходного значения. Отрицательное значение коэффициента корреляции свидетельствует о нехорошем эффекте кампании. Но на действия могли оказывать воздействие разные причины, к примеру, размещенные компрометирующие материалы. В связи с сиим полагаться лишь на значение коэффициента корреляции в данном случае недозволено. Другими словами, коэффициент корреляции не охарактеризовывает причинно-наследственную связь.

  Анализ популярности контента по корреляции просмотров и репостов видео

  Пример 3. Обладатель канала YouTube употребляет социальную сеть для рекламы собственных роликов. Он увидел, что меж числом просмотров и количеством репостов в социальной сети существует некая связь. Можно ли спрогнозировать виральность контента канала в Excel? Найти необходимость использования уравнения линейной регрессии для пророчества количества просмотров роликов в зависимости от числа репостов.

  

  Определим наличие связи меж 2-мя параметрами по формуле:

  0,7;ЕСЛИ(КОРРЕЛ(A3:A8;B3:B8)>0,7;»Мощная ровная зависимость»;»Мощная оборотная зависимость»);»Слабенькая зависимость либо ее отсутствие»)’ >

  Если модуль коэффициента корреляции больше 0,7, считается оптимальным внедрение функции линейной регрессии (y=ax+b) для описания связи меж 2-мя величинами. В этом случае:

  

  Построим график зависимости числа просмотров от количества репостов, отобразим линию тренда и ее уравнение:

  

  Используем данное уравнение для определения количества просмотров при 200, 500 и 1000 репостов:

  

  Аналогичное уравнение употребляет функция ПРЕДСКАЗ. Другими словами, чтоб отыскать количество просмотров в случае, если было изготовлено, к примеру, 250 репостов, можно применять формулу:

  0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);»Величины не взаимосвязаны»)’ >

  

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

  Индивидуальности использования функции КОРРЕЛ в Excel

  Функция КОРРЕЛ имеет последующий синтаксис:

  • массив1 – неотклонимый аргумент, содержащий спектр ячеек либо массив данных, которые охарактеризовывают конфигурации характеристики какого-нибудь объекта.
  • массив2 – неотклонимый аргумент (спектр ячеек или массив), элементы которого охарактеризовывают изменение параметров второго объекта.
  1. Функция КОРРЕЛ не учитывает в расчетах элементы массива либо ячейки из избранного спектра, в которых содержатся данные текстового либо логического типов. Пустые ячейки также игнорируются. Текстовые представления числовых значений учитываются.
  2. Если нужно учитывать логические ИСТИНА либо ЛОЖЬ в качестве числовых значений 1 либо 0 соответственно, можно выполнить очевидное преобразование данных используя двойное отрицание «—».
  3. Размерности массив1 и массив2 либо количество ячеек, переданных в качестве этих 2-ух аргументов, должны совпадать. Если аргументы содержат различное количество точек данных, к примеру, =КОРРЕЛ(<1;2;3>;<4;6;8;10>), результатом выполнения функции будет код ошибки #Н/Д.
  4. Если один из аргументов представляет собой пустой массив либо массив нулевых значений, функция КОРРЕЛ возвратит код ошибки #ДЕЛ/0!. Аналогичный итог выполнения данной функции будет достигнут в случае, если обычное отклонение распределения величин в одном из массивов (массив1, массив2) равно 0 (нулю).
  5. Функция КОРРЕЛ производит расчет коэффициента корреляции по последующей формуле:

  Примечание 2: Коэффициент корреляции представляет собой количественную характеристику степени связи меж 2-мя качествами объектов. Этот коэффициент может принимать значения из спектра от -1 до 1, при всем этом:

  1. Если значение коэффициента приближается к 1 либо -1, меж 2-мя исследуемыми качествами существует мощная ровная либо оборотная связи соответственно.
  2. Если значение коэффициента стремится к 0,5 либо -0,5, два характеристики слабо прямо либо назад взаимосвязаны вместе соответственно.
  3. Если коэффициент корреляции близок к 0 (нулю), меж 2-мя исследуемыми качествами отсутствует ровная или оборотная связи.

  Примечание 3: Для осознания смысла коэффициента корреляции можно привести два обычных примера:

  Интересно почитать:  Функция бдсумм в excel примеры