Как построить график корреляции в excel - Учим Эксель

Корреляционно-регрессионный анализ в Excel: {инструкция} выполнения

Регрессионный и корреляционный анализ – статистические способы исследования. Это более всераспространенные методы показать зависимость какого-нибудь параметра от одной либо нескольких независящих переменных.

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

Регрессионный анализ в Excel

Указывает воздействие одних значений (самостоятельных, независящих) на зависимую переменную. Например, как зависит количество экономически активного населения от числа компаний, величины зарплаты и др. характеристик. Либо: как влияют зарубежные инвестиции, цены на энергоресурсы и др. на уровень ВВП (Валовой внутренний продукт — макроэкономический показатель, отражающий рыночную стоимость всех конечных товаров и услуг, то есть предназначенных для непосредственного употребления, произведённых за год во всех отраслях экономики на территории государства).

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

  • линейной (у = а + bx);
  • параболической (y = a + bx + cx 2 );
  • экспоненциальной (y = a * exp(bx));
  • степенной (y = a*x^b);
  • гиперболической (y = b/x + a);
  • логарифмической (y = b * 1n(x) + a);
  • показательной (y = a * b^x).

Разглядим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задачка. На 6 предприятиях была проанализирована среднемесячная зарплата и количество уволившихся служащих. Нужно найти зависимость числа уволившихся служащих от средней заработной платы.

Модель линейной регрессии имеет последующий вид:

Где а – коэффициенты регрессии, х – действующие переменные, к – число причин.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – зарплата (х).

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

Активируем мощнейший аналитический инструмент:

  1. Жмем клавишу «Кабинет» и перебегаем на вкладку «Характеристики Excel». «Надстройки». Надстройки.
  2. Понизу, под выпадающим перечнем, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флаг справа и изберите). И клавиша «Перейти». Нажимаем.
  3. Раскрывается перечень доступных надстроек. Избираем «Пакет анализа» и жмем ОК.

Опосля активации надстройка будет доступна на вкладке «Данные».

Сейчас займемся конкретно регрессионным анализом.

  1. Открываем меню инструмента «Анализ данных». Избираем «Регрессия». Регрессия.
  2. Раскроется меню для выбора входных значений и характеристик вывода (где показать итог). В полях для начальных данных указываем спектр описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять. Параметры регрессии.
  3. Опосля нажатия ОК, программка покажет расчеты на новеньком листе (можно избрать интервал для отображения на текущем листе либо назначить вывод в новейшую книжку).

Сначала обращаем внимание на R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, либо 75,5%. Это значит, что расчетные характеристики модели на 75,5% разъясняют зависимость меж изучаемыми параметрами. Чем выше коэффициент детерминации, тем лучше модель. Отлично – выше 0,8. Плохо – меньше 0,5 (таковой анализ навряд ли можно считать резонным). В нашем примере – «хорошо».

Коэффициент 64,1428 указывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. Другими словами на значение анализируемого параметра влияют и остальные причины, не описанные в модели.

Коэффициент -0,16285 указывает весомость переменной Х на Y. Другими словами среднемесячная зарплата в границах данной модели влияет на количество уволившихся с весом -0,16285 (это маленькая степень воздействия). Символ «-» показывает на отрицательное воздействие: чем больше заработная плата, тем меньше уволившихся. Что справедливо.

Интересно почитать:  Excel красная строка в ячейке

Корреляционный анализ в Excel

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

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

Коэффициент корреляции обозначается r. Варьируется в границах от +1 до -1. Систематизация корреляционных связей для различных сфер будет различаться. При значении коэффициента 0 линейной зависимости меж подборками не существует.

Разглядим, как при помощи средств Excel отыскать коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

Задачка: Найти, есть ли связь меж временем работы токарного станка и стоимостью его обслуживания.

Время и стоимость.

Ставим курсор в всякую ячейку и жмем клавишу fx.

  1. В группы «Статистические» избираем функцию КОРРЕЛ.
  2. Аргумент «Массив 1» — 1-ый спектр значений – время работы станка: А2:А14.
  3. Аргумент «Массив 2» — 2-ой спектр значений – стоимость ремонта: В2:В14. Нажимаем ОК.

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

Для корреляционного анализа нескольких характеристик (наиболее 2) удобнее использовать «Анализ данных» (надстройка «Пакет анализа»). В перечне необходимо избрать корреляцию и обозначить массив. Все.

Приобретенные коэффициенты отобразятся в корреляционной матрице. Наподобие таковой:

Корреляционно-регрессионный анализ

На практике эти две методики нередко используются вкупе.

  1. Строим корреляционное поле: «Вставка» — «Диаграмма» — «Точечная диаграмма» (дает ассоциировать пары). Спектр значений – все числовые данные таблицы. Поле корреляции.
  2. Щелкаем левой клавишей мыши по хоть какой точке на диаграмме. Позже правой. В открывшемся меню избираем «Добавить линию тренда». Добавить линию тренда.
  3. Назначаем характеристики для полосы. Тип – «Линейная». Понизу – «Показать уравнение на диаграмме». Линейная линия тренда.
  4. Нажимаем «Закрыть».

Корреляционный анализ в Excel

Корреляционный анализ в Excel

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

Сущность корреляционного анализа

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

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

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

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

Способ 1. Обусловьте корреляцию при помощи функции мастера.

Одним из методов проведения корреляционного анализа является внедрение функции CORREL. Сама функция имеет вид CORREL (array1; array2).

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

Корреляционный анализ в Excel

Корреляционный анализ в Excel

В поле "Массив2" нужно ввести координаты второго столбца. У нас есть стоимость рекламы. Так же, как и в прошлом случае, вводим данные в поле.

Интересно почитать:  Как выделить строки в excel по содержимому

Нажмите на клавишу "в порядке" .

Способ 2: высчитайте корреляцию при помощи пакета анализа

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

Корреляционный анализ в Excel

Корреляционный анализ в Excel

Корреляционный анализ в Excel

Корреляционный анализ в Excel

Корреляционный анализ в Excel

Корреляционный анализ в Excel

Корреляционный анализ в Excel

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

Характеристики вывода по дефлоту: "Новейший рабочий лист" , т.е. данные будут отображаться на другом листе. Вы сможете поменять положение, перемещая переключатель. Это быть может текущий лист (тогда для вас необходимо будет указать координаты выходных ячеек) либо новенькая книжка (файл).

Когда все опции настроены, нажмите клавишу "в порядке" .

Корреляционный анализ в Excel

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

Корреляционный анализ в Excel

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

Как высчитать коэффициент корреляции в Excel (2 обычных метода)

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

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

В этом уроке я покажу для вас два вправду обычных метода высчитать коэффициент корреляции в Excel . Для этого уже есть интегрированная функция, и вы также сможете употреблять Data Analysis Toolpak.

Что такое коэффициент корреляции?

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

Коэффициент корреляции — это значение, которое указывает, как тесновато соединены два ряда данных.

Нередко применяемый пример — это вес и рост 10 человек в группе. Если мы рассчитаем коэффициент корреляции для данных о росте и весе этих людей, мы получим значение от -1 до 1.

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

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

Чем поближе значение к 1, тем посильнее положительная корреляция. Таковым образом, значение 0,8 будет означать, что данные о росте и весе очень коррелированы.

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

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

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

Как я уже упоминал, есть несколько методов высчитать коэффициент корреляции в Excel.

Интересно почитать:  Excel удалить каждую вторую строку

Внедрение формулы CORREL

CORREL — это статистическая функция, представленная в Excel 2007.

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

Ниже приведена формула, которая сделает это:

Вышеупомянутая функция CORREL воспринимает два аргумента — серию с точками данных роста и серию с точками данных веса.

Как лишь вы нажмете кнопку ВВОД, Excel выполнит все вычисления в серверной части и выдаст для вас один единственный коэффициент корреляции Пирсона.

В нашем примере это значение незначительно больше 0,5, что показывает на достаточно сильную положительную корреляцию.

Этот способ идеальнее всего употреблять, если у вас есть две серии и все, что для вас необходимо, — это коэффициент корреляции.

Но если у вас есть несколько рядов, и вы желаете выяснить коэффициент корреляции всех этих рядов, вы также сможете разглядеть возможность использования пакета инструментов анализа данных в Excel (рассматривается дальше).

Внедрение пакета инструментов анализа данных

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

Но пакет Data Analysis Toolpak по дефлоту отключен в Excel. Итак, первым шагом было бы опять включить инструмент анализа данных, а потом употреблять его для расчета коэффициента корреляции Пирсона в Excel.

Включение пакета инструментов анализа данных

Ниже приведены шаги по включению пакета инструментов анализа данных в Excel:

  • Перейдите на вкладку Файл.
  • Нажмите на Характеристики
  • В открывшемся диалоговом окне «Характеристики Excel» щелкните параметр «Надстройки» на боковой панели.
  • В раскрывающемся перечне «Управление» изберите надстройки Excel.
  • Щелкните Дальше. Раскроется диалоговое окно надстроек.
  • Отметьте опцию Analysis Toolpak
  • Нажмите ОК

Вышеупомянутые шаги добавят новейшую группу на вкладке «Данные» на ленте Excel под заглавием «Анализ». В данной группе у вас будет функция анализа данных

Расчет коэффициента корреляции при помощи пакета Data Analysis Toolpak

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

Представим, у вас есть набор данных, показанный ниже, и вы желаете узнать корреляцию меж 3-мя рядами (рост и вес, рост и доход, вес и доход).

Ниже приведены шаги для этого:

  • Перейдите на вкладку «Данные».
  • В группе «Анализ» изберите параметр «Анализ данных».
  • В открывшемся диалоговом окне «Анализ данных» нажмите «Корреляция».
  • Щелкните ОК. Раскроется диалоговое окно «Корреляция».
  • Для спектра ввода изберите три серии, включая заглавия.
  • Удостоверьтесь, что для параметра «Сгруппировано по» выбрано значение «Столбцы».
  • Изберите вариант — «Ярлычек в первом ряду». Это гарантирует, что в результирующих данных будут схожие заглавия, и будет намного легче осознать результаты.
  • В параметрах вывода изберите, где вы желаете получить результирующую таблицу. Я собираюсь употреблять ячейку G1 на том же листе. Вы также сможете получить результаты на новеньком листе либо в новейшей книжке.
  • Нажмите ОК.

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

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

Итак, это два стремительных и обычных способа расчета коэффициента корреляции в Excel.

Ссылка на основную публикацию
Adblock
detector