График корреляции в excel как построить

REDMOND

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

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

  1. Как проводится корреляционный анализ в Excel
  2. Вариант 1: Вызов через Мастер функций
  3. Вариант 2: Применение пакета анализа

Как проводится корреляционный анализ в Excel

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

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

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

Вариант 1: Вызов через Мастер функций

В отличии от некоторых других типов анализов, корреляционный анализ можно вызвать с помощью функций. За него отвечает функция КОРРЕЛ вида: КОРРЕЛ(массив1;массив2):

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

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

  • Вам потребуется заполните в окошке настройки функции два поля, то есть указать два массива ячеек. В первый массив укажите номера ячеек, зависимость которых следует определить. Для рассматриваемой таблицы это будет массив столбца дохода компании. Номера можно вписать вручную или выделить их, кликнув по иконке таблицы в поле.
  • Во втором же массиве потребуется указать перечень ячеек, которые предположительно должны оказывать влияние на первый массив. В рассматриваемой таблице это величина зарплат сотрудников.
  • Закончив с заполнением нажмите кнопку “Ок”. Подсчет будет произведен автоматически и выведен в указанной ранее ячейке.
  • Если полученный коэффициент оказался больше +/-0.5, то это значит, что одна величина сильно зависима от другой.
  • Вариант 2: Применение пакета анализа

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

      Перейдите во вкладку “Файл”, что расположена в верхней части окна.

  • В левой части переключитесь в раздел “Параметры”.
  • Откройте подраздел “Надстройки”, что находятся в левой части окна с параметрами.
  • У строки “Управление”, что расположена в нижней части открывшегося окна, установите значение “Надстройки Excel”. Нажмите “Перейти”, чтобы увидеть перечень доступных надстроек.
  • В открывшемся окне установите галочку у пункта “Пакет анализа” и нажмите “Ок”. После этого у вас должны появится дополнительные инструменты в верхней панели Excel.
  • Нужные нам инструменты расположена во вклакде “Данные”. Там должен будет появится дополнительный блок инструментов — “Анализ”. Воспользуйтесь в нем единственным инструментом — “Анализом данных”.
  • Открывается список с различными вариантами анализа данных. Укажите пункт “Корреляция”. Нажмите “Ок” для применения.
  • В открывшемся окошке настройки анализа уже потребуется заполнить только поле “Входной интервал”. Туда добавляется сразу два массива. В нашем случае это столбцы с зарплатой и доходом фирмы.
  • В блоке ниже можно указать, куда будет выводится результат. По умолчанию он выводит на новый рабочий лист, но вы можете настроить вывод в новую книгу или в определенных ячейках на текущем листе. Нажмите для применения и расчетов.
  • В итоге вы получите тот же результат, что и в первом способе. Единственное, в некоторых таблицах, при обработке большего количества данных значений может быть гораздо больше (в основном носят вспомогательный характер).
  • Первый рассмотренный нами способ подойдет для большинства таблиц, в то время как второй больше подходит для таблиц с большим перечнем данных, где еще желательно отследить логику проводимого анализа.

    Как в Excel построить поле корреляции

    Корреляцию в Excel можно найти по формуле:

    Результат показан ниже

    таблица находим корреляцию

    REDMOND

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

    график

    затем переходим на область графика

    выбрать данные

    и выбираем данные из диапазона B3:C11, затем Ок. В итоги получаем график поля корреляции по точкам

    график поля корреляции

    Также быстро корреляцию можно найти через анализ данных
    Вкладка Данные, затем Анализ данных. Если у вас эта вкладка не отображается в Excel, то см. здесь как сделать надстройку.

    Выбираем корреляцию и жмём Ок.

    анализ данных корреляция

    Такой же выбираем диапазон данных, как и ранее делали

    корреляция окно

    В результате получаем отчёт

    результат

    Аналитически, корреляция определяется по формуле:

    Насколько публикация полезна?

    Нажмите на звезду, чтобы оценить!

    Средняя оценка 4.5 / 5. Количество оценок: 4

    Оценок пока нет. Поставьте оценку первым.

    1635

    REDMOND

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