Использование программы для работы с электронными таблицами, такой как Microsoft Excel, для отслеживания данных позволяет автоматизировать некоторые вычисления, например определение коэффициента корреляции. Использование автоматизированных возможностей Microsoft Excel для выполнения вычислений поможет вам сэкономить время и повысить точность вычислений. Изучение того, как найти коэффициент корреляции в Excel, может сделать вас более эффективным профессионалом. В этой статье мы обсудим, что такое коэффициент корреляции, как его использовать в бизнесе и как его рассчитать, а также дадим советы, как это сделать эффективно.
Что такое коэффициент корреляции?
Коэффициент корреляции — это показатель, который анализирует, насколько близко совпадают две переменные. Для отношений с высоким коэффициентом корреляции изменение одной переменной, вероятно, будет иметь устойчивый результат в отношении значения другой переменной. Это позволяет вам определить взаимосвязь между двумя или более показателями в вашей компании, чтобы определить методы улучшения ключевых показателей эффективности.
Коэффициент корреляции Пирсона, 2 способа вычисления
Бизнес использует коэффициент корреляции
Расчет коэффициента корреляции может быть ценным источником информации для бизнеса. Выявление связей между связанными показателями или процессами может быть полезным для многих отраслей и ролей в компании. Общие области, в которых коэффициент корреляции может принести пользу организации, включают:
- Удовлетворенность клиентов: анализ связей между поведением или уровнем обслуживания клиентов и их удовлетворенностью тем, что они получили от вашей компании, может помочь вам определить, какое поведение нравится клиентам. Это позволяет вам адаптировать будущие предложения для лучшего обслуживания вашего рынка, создавая более счастливых и потенциально более прибыльных клиентов.
- Логистика. Повышение эффективности логистики вашей организации может существенно повлиять на прибыльность. Например, компания может изучить, как время выполнения заказа коррелирует с различными переменными или процессами внутри организации, чтобы определить те из них, которые вызывают наиболее значительные задержки в процессе выполнения.
- Маркетинг: коэффициент корреляции является важным расчетом для многих маркетинговых инициатив, поскольку он позволяет определить эффективность конкретной маркетинговой кампании или серии маркетинговых мероприятий. Например, проверка корреляции между расходами и увеличением продаж может помочь вам оценить, эффективно ли ваши маркетинговые усилия стимулируют продажи.
- Производительность. Повышение производительности — один из самых эффективных способов сделать компанию более прибыльной. Выявление корреляции между поведением и уровнем производства может помочь вам выделить наиболее полезные действия, предпринимаемые вашей компанией, чтобы повысить их известность и еще больше повысить производительность.
- Удержание: Удержание ваших самых ценных сотрудников является важной частью поддержания вашей компании в лучшем виде. Выполнение корреляционного анализа в отношении удовлетворенности сотрудников может помочь вам лучше определить поведение и возможности, которые приносят наибольшую пользу моральному духу персонала, чтобы сохранить ваших лучших сотрудников и привлечь более качественных кандидатов на открытые вакансии.
Как рассчитать коэффициент корреляции в Excel
Автоматизированные формулы и надстройки, доступные в Microsoft Excel, упрощают выполнение вычислений, таких как коэффициент корреляции. Выполните следующие действия, чтобы включить коэффициент корреляции одним из двух способов:
Коэффициент корреляции Пирсона в Excel
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)
1. Введите свои данные
Чтобы использовать функции расчета Microsoft Excel, вам необходимо включить все ваши соответствующие данные. Создайте два или более заголовков для столбцов данных, а затем введите данные под каждым из них. И формульный подход, и подход инструмента корреляции требуют наличия существующих наборов данных для выполнения их автоматизированных вычислений, и оба варианта автоматически обновляются после создания, если вы вносите изменения в существующую структуру данных.
2. Напишите формулу коэффициента корреляции
При создании коэффициента корреляции между двумя переменными использование формулы коэффициента может быть самым простым способом найти коэффициент корреляции в Microsoft Excel. Выберите пустую ячейку, которую вы хотите использовать в качестве местоположения для результатов расчета коэффициента корреляции. Нажмите на строку формул в верхней части листа и введите «=CORREL([Range 1 first cell]:[Range 1 last cell],[Range 2 first cell]:[Range 2 last cell])”, где каждый диапазон представляет данные одной переменной.
Например, компания, оценивающая взаимосвязь между маркетингом и продажами своего продукта, может создать следующую таблицу:
AB1Бюджет на маркетинг**Продажи**2 10 000 долларов США
12,3003$9,50012,1004$8,0006,9005$9,00014,3006$15,00015,6007
8 Коэффициент корреляции: = КОРРЕЛ (A2: A6, B2: B6)
После ввода формулы пользователь нажимает клавишу ввода, и Excel выполняет расчет, отображая в ячейке коэффициент корреляции 0,7033. Это означает, что компания может объяснить 70,33% общего объема продаж количеством денег, вложенных в маркетинг.
3. Используйте инструмент корреляции
Инструмент корреляции — это эффективный способ создать таблицу коэффициентов корреляции для нескольких переменных.
- Нажмите «Данные» в верхнем меню Excel и выберите «Анализ данных» из отображаемых параметров.
- Выберите параметр «Корреляция» в диалоговом окне анализа данных.
- Установите флажок «Метки в первой строке», если вы хотите включить свои метки в созданную диаграмму.
- Введите диапазон данных, которые вы оцениваете, введя верхнюю левую ячейку и нижнюю правую ячейку, разделенные двоеточием, в диалоговом окне «Входной диапазон:». Включите строку меток, если вы щелкнули поле на предыдущем шаге.
- Выберите место выхода. Вы можете создать результирующую диаграмму, начиная с определенной ячейки, на новом листе в текущей книге или в новой книге.
- Нажмите кнопку «ОК», чтобы создать корреляционную диаграмму.
Например, возможно, одна и та же компания решает также оценивать клики по цифровому маркетингу. Сотрудники могут использовать инструмент корреляции для создания диаграммы, показывающей корреляцию между тремя переменными:
ABCD1Маркетинговый бюджет**Продажи**Клики
2$10,000
12 300 48 649
3 9 500 долларов12 10041 658
4$8 0006 90039 701
59 000 долларов14 30029 875
615 000 долларов15 60057 345
7
8
Маркетинговый бюджет**Продажи**Клики9Маркетинговый бюджет1
10Продажи0,70336430371
11Clicks0.81583292180.28831329361### 4. Обновите данные
Одним из преимуществ использования Microsoft Excel и его формул или надстроек для расчета ваших значений является возможность легко обновлять информацию, которую вы оцениваете. Когда вы изменяете информацию в исходном наборе данных, Excel автоматически обновляет значения коэффициентов. При создании формул вы также можете разрешить автоматическое расширение набора данных, создав диапазон, превышающий текущий набор данных. Excel игнорирует пробелы при расчетах, позволяя вам заполнить их позже, не изменяя формулу или диапазон надстройки.
Советы по расчету коэффициента корреляции в Excel
Если вы хотите использовать Microsoft Excel для расчета коэффициентов корреляции, вот несколько советов, которые помогут вам:
- Создайте диаграмму. Графики и диаграммы — это дополнительные автоматизированные инструменты, доступные в Microsoft Excel, которые обеспечивают те же преимущества автоматического создания и обновления при изменении данных. Создание графика двух или более переменных может позволить вам визуализировать корреляцию или ее отсутствие между двумя переменными.
- Практикуйте оба метода. Подход с формулой и подход с надстройками имеют свои преимущества, поэтому может быть полезно попрактиковаться в использовании обоих методов. Это позволяет вам освоиться с обоими вариантами, чтобы вы могли эффективно использовать их при необходимости.
- Примените свои результаты. Ценность расчета коэффициента корреляции между двумя или более переменными заключается в возможности проанализировать то, что эта корреляция говорит вам, и найти практическое применение. Изучение того, какие переменные влияют на ключевые показатели вашей компании, может помочь вам адаптировать свое поведение для максимизации производительности и прибыльности.
Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.
Источник: buom.ru
Ранговая корреляция Спирмена в Excel: формула и график
При проведении корреляционного анализа в Excel в большинстве случаев вы будете иметь дело с корреляцией Пирсона. Но поскольку коэффициент корреляции Пирсона измеряет только линейную связь между двумя переменными, он не работает для всех типов данных — ваши переменные могут быть сильно связаны нелинейным образом, а коэффициент по-прежнему будет близок к нулю. В таких обстоятельствах вы можете использовать ранговую корреляцию Спирмена вместо Пирсона.
Корреляция Спирмена – основы
Корреляция Спирмена — это непараметрическая версия коэффициента корреляции Пирсона, которая измеряет степень связи между двумя переменными на основе их рангов.
Корреляция момента продукта Pearson проверяет линейную связь между двумя непрерывными переменными. Линейная означает зависимость, при которой две переменные изменяются в одном направлении с постоянной скоростью.
Корреляция ранга Спирмена оценивает монотонную связь между ранжированными значениями. В монотонной зависимости переменные также имеют тенденцию изменяться вместе, но не обязательно с постоянной скоростью.
Когда делать корреляцию Спирмена
Корреляционный анализ Спирмена следует использовать в любом из следующих обстоятельств, когда основные допущения корреляции Пирсона не выполняются:
- Если ваши данные демонстрируют нелинейную зависимость или не имеют нормального распределения.
- Если хотя бы одна переменная порядковая. Если ваши значения можно расположить в порядке «первый, второй, третий…», вы имеете дело с порядковыми данными.
- Если есть существенные отклонения. В отличие от корреляции Пирсона, корреляция Спирмена не чувствительна к выбросам, поскольку она выполняет расчеты по рангам, поэтому разница между фактическими значениями не имеет значения.
Например, вы можете использовать корреляцию Спирмена, чтобы найти ответы на следующие вопросы:
- Люди с более высоким уровнем образования больше заботятся об окружающей среде?
- Связано ли количество симптомов у пациента с его готовностью принимать лекарства?
Коэффициент корреляции Спирмена
В статистике коэффициент корреляции Спирмена представлен либо рупий или греческое письмо р («ро»), поэтому его часто называют Ро Спирмена.
Коэффициент ранговой корреляции Спирмена измеряет как силу, так и направление взаимосвязи между рангами данных. Это может быть любое значение от -1 до 1, и чем ближе абсолютное значение коэффициента к 1, тем сильнее связь:
- 1 – идеальная положительная корреляция
- -1 – идеальная отрицательная корреляция
- 0 – нет корреляции
Формула ранговой корреляции Спирмена
В зависимости от наличия или отсутствия связей в ранжировании (один и тот же ранг, присвоенный двум или более наблюдениям), коэффициент корреляции Спирмена можно рассчитать по одной из следующих формул.

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

Чтобы иметь дело со связанными рангами, необходимо использовать полную версию формулы корреляции Спирмена, которая представляет собой слегка модифицированную версию r Пирсона:
- R(x) и R(y) — ранги Икс а также у переменные
- R(x) и R(y) — средние ранги
Как рассчитать корреляцию Спирмена в Excel с помощью функции КОРРЕЛ
К сожалению, в Excel нет встроенной функции для расчета коэффициента ранговой корреляции Спирмена. Однако это не означает, что вам придется ломать голову над приведенными выше формулами. Немного поэкспериментировав с Excel, мы можем придумать гораздо более простой способ корреляции Спирмена.

В качестве примера попробуем выяснить, имеет ли наша физическая активность какое-либо отношение к нашему кровяному давлению. В столбце B у нас есть количество минут, которые 10 мужчин того же возраста ежедневно проводят в тренажерном зале, а в столбце C у нас есть их систолическое артериальное давление.
Чтобы найти коэффициент корреляции Спирмена в Excel, выполните следующие действия:
- Ранжируйте свои данные Поскольку корреляция Спирмена оценивает связи между двумя переменными на основе их рангов, вам необходимо ранжировать исходные данные. Это можно быстро сделать с помощью функции Excel RANK.AVG. Чтобы ранжировать первую переменную (физическую активность), введите приведенную ниже формулу в D2, а затем перетащите ее вниз в D11: =РАНГ.СРЕДНЕЕ(B2,$B$2:$B$11,0) Чтобы ранжировать вторую переменную (кровяное давление), поместите следующую формулу в ячейку E2 и скопируйте ее вниз по столбцу: =РАНГ.СРЕДНЕЕ(C2,$C$2:$C$11,0) Чтобы формулы работали правильно, не забудьте заблокировать диапазоны с абсолютными ссылками на ячейки. На этом этапе ваши исходные данные должны выглядеть примерно так:

- Найдите коэффициент корреляции Спирмена
Теперь, когда ранги установлены, мы можем использовать функцию Excel CORREL для получения ро Спирмена: = КОРРЕЛ (D2: D11, E2: E11) Формула возвращает коэффициент -0,7576 (округленный до 4 цифр), который показывает достаточно сильную отрицательную корреляцию и позволяет сделать вывод, что чем больше человек тренируется, тем ниже у него артериальное давление. Коэффициент корреляции Пирсона для той же выборки (-0,7445) указывает на несколько более слабую корреляцию, но все же статистически значимую:
Прелесть этого метода в том, что он быстрый, простой и работает независимо от того, есть ничья в рейтинге или нет.
Рассчитать коэффициент корреляции Спирмена в Excel по традиционной формуле
Если вы не совсем уверены, что функция КОРРЕЛ вычислила право Спирмена, вы можете проверить результат с помощью традиционной формулы, используемой в статистике. Вот как:

- Найдите разницу между каждой парой рангов (d), вычитая один ранг из другого: =D2-E2 Эта формула помещается в F2, а затем копируется вниз по столбцу.
- Возведите каждую разницу в рангах в степень двойки (d2):
=F2^2 Эта формула идет в столбец G. - Складываем квадраты разностей: =СУММ(G2:G11) Эта формула может относиться к любой пустой ячейке, в нашем случае к G12. На следующем снимке экрана вы, вероятно, лучше поймете расположение данных:
- В зависимости от того, имеет ли ваш набор данных какие-либо связанные ранги или нет, используйте один из эти формулы для расчета коэффициента корреляции Спирмена.

В нашем примере ничьих нет, поэтому можно использовать более простую формулу:
С d2 равно 290, и н (количество наблюдений) равным 10, формула претерпевает следующие преобразования:
В результате вы получаете -0,757575758, что полностью согласуется с коэффициентом корреляции Спирмена, рассчитанным в предыдущем примере.
В Microsoft Excel приведенные выше расчеты можно выполнить с помощью следующего уравнения:

Где G12 — сумма квадратов разностей рангов (d2).
Как сделать корреляцию Спирмена в Excel с помощью графика
Коэффициенты корреляции в Excel измеряют только линейные (Пирсон) или монотонные (Спирмен) отношения. Однако возможны и другие ассоциации. Таким образом, независимо от того, какую корреляцию вы делаете, всегда полезно представить взаимосвязь между переменными на графике.
Чтобы построить график корреляции ранжированных данных, вам нужно сделать следующее:
- Вычислите ранги с помощью функции RANK.AVG, как описано в этот пример.
- Выберите два столбца с рангами.
- Вставьте точечную диаграмму XY. Для этого нажмите кнопку Разбросдиаграмма значок на Вставка вкладка, в Чаты группа.
- Добавьте линию тренда на график. Самый быстрый способ — нажать Элементы диаграммы кнопка >Добавить линию тренда….
- Отобразите значение R-квадрата на графике. Дважды щелкните линию тренда, чтобы открыть ее панель, переключитесь на Параметры линии тренда вкладку и выберите Отображение значения R-квадратана графике коробка.
- Покажите больше цифр в значении R2 для большей точности.
В результате вы получите наглядное представление о соотношении между рангами. Кроме того, вы получите Коэффициент детерминации (R2), квадратный корень из которого представляет собой коэффициент корреляции Пирсона (r). Но поскольку вы построили ранжированные данные, это значение r Пирсона не что иное, как значение rho Спирмена.
Примечание. R-квадрат всегда является положительным числом, поэтому выведенный коэффициент ранговой корреляции Спирмена также всегда будет положительным. Чтобы добавить соответствующий знак, просто посмотрите на линию на графике корреляции: наклон вверх указывает на положительную корреляцию (знак плюс), а наклон вниз указывает на отрицательную корреляцию (знак минус).
В нашем случае R2 равно 0,5739210285. Используйте функцию SQRT, чтобы найти квадратный корень:
…и вы получите уже знакомый коэффициент 0,757575758.

Нисходящий наклон на графике демонстрирует отрицательную корреляцию, поэтому мы добавляем знак минус и получаем правильный коэффициент корреляции Спирмена, равный -0,757575758.
Вот как вы можете рассчитать ранговый коэффициент корреляции Спирмена в Excel. Чтобы поближе ознакомиться с примерами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Источник: snaptik.ru
Коэффициент корреляция в excel — примеры как применять
Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.
Назначение корреляционного анализа
Зависимость устанавливается тогда, когда начинается выявление коэффициента корреляции. Этот метод отличается от анализа регрессии, так как здесь только один показатель, рассчитываемый при помощи корреляции. Интервал изменяется от +1 до -1. Если она плюсовая, то повышение первой величины способствует повышению 2-й.
Если минусовая, то повышение 1-й величины способствует понижению 2-й. Чем выше коэффициент, тем сильнее одна величина влияет на 2-ю.
Важно! При 0-м коэффициенте зависимости между величинами нет.

Расчет коэффициента корреляции
Разберем расчёт на нескольких образцах. К примеру, есть табличные данные, где по месяцам описаны в отдельных столбцах траты на рекламное продвижение и объём продаж. Исходя из таблицы, будем выяснять уровень зависимости объема продаж от денег, затраченных на рекламное продвижение.
Способ 1: определение корреляции через Мастер функций
КОРРЕЛ – функция, позволяющая реализовать корреляционный анализ. Общий вид – КОРРЕЛ(массив1;массив2). Подробная инструкция:
- Необходимо произвести выделение ячейки, в которой планируется выводить итог расчета. Нажать «Вставить функцию», находящуюся слева от текстового поля для ввода формулы.

1
- Открывается «Мастер функций». Здесь необходимо найти КОРРЕЛ, кликнуть на нее, затем на «ОК».

2
- Открылось окошко аргументов. В строку «Массив1» необходимо ввести координаты интервалы 1-го из значений. В рассматриваемом примере — это столбец «Величина продаж». Нужно просто произвести выделение всех ячеек, которые находятся в этой колонке. В строку «Массив2» аналогично необходимо добавить координаты второй колонки. В рассматриваемом примере — это столбец «Затраты на рекламу».

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

4
Способ 2: вычисление корреляции с помощью Пакета анализа
Существует еще один метод определения корреляции. Здесь используется одна из функций, находящаяся в пакете анализа. Перед ее использованием нужно провести активацию инструмента. Подробная инструкция:
- Переходим в раздел «Файл».

5
- Открылось новое окошко, в котором нужно кликнуть на раздел «Параметры».
- Жмём на «Надстройки».
- Находим в нижней части элемент «Управление». Здесь необходимо выбрать из контекстного меню «Надстройки Excel» и кликнуть «ОК».

6
- Открылось специальное окно надстроек. Ставим галочку рядом с элементом «Пакет анализа». Кликаем «ОК».
- Активация прошла успешно. Теперь переходим в «Данные». Появился блок «Анализ», в котором необходимо кликнуть «Анализ данных».
- В новом появившемся окошке выбираем элемент «Корреляция» и жмем на «ОК».

7
- На экране появилось окошко настроек анализа. В строчку «Входной интервал» необходимо ввести диапазон абсолютно всех колонок, принимающих участие в анализе. В рассматриваемом примере — это столбики «Величина продаж» и «Затраты на рекламу». В настройках отображения вывода изначально выставлен параметр «Новый рабочий лист», что означает показ результатов на другом листе. По желанию можно поменять локацию вывода результата. После проведения всех настроек нажимаем на «ОК».

8
Вывелись итоговые показатели. Результат такой же, как и в первом методе – 0,97.

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

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

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

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

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

9
- В отобразившемся окошке жмем на элемент «Корреляция» и кликаем на «ОК».
- В строку «Входной интервал» вбиваем интервал по трём или более столбцам исходной таблицы. Диапазон можно ввести вручную или же просто выделить его ЛКМ, и он автоматически отобразится в нужной строчке. В «Группирование» выбираем подходящий способ группировки. В «Параметр вывода» указывает место, в которое будут выведены результаты корреляции. Кликаем «ОК».

10
- Готово! Построилась матрица корреляции.

11
Надстройка Пакет анализа
В надстройке Пакет анализа для вычисления ковариации и корреляции имеются одноименные инструменты анализа .

После вызова инструмента появляется диалоговое окно, которое содержит следующие поля:

- Входной интервал : нужно ввести ссылку на диапазон с исходными данными для 2-х переменных
- Группирование : как правило, исходные данные вводятся в 2 столбца
- Метки в первой строке : если установлена галочка, то Входной интервал должен содержать заголовки столбцов. Рекомендуется устанавливать галочку, чтобы результат работы Надстройки содержал информативные столбцы
- Выходной интервал : диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона.
Надстройка возвращает вычисленные значения корреляции и ковариации (для ковариации также вычисляются дисперсии обоих случайных величин).
Коэффициент парной корреляции в Excel
Разберем, как правильно проводить коэффициент парной корреляции в табличном процессоре Excel.
Расчет коэффициента парной корреляции в Excel
К примеру, у вас есть значения величин х и у.
Х – это зависимая переменна, а у – независимая. Необходимо найти направление и силу связи между этими показателями. Пошаговая инструкция:
- Выявим средние показатели величин при помощи функции СРЗНАЧ.
- Произведем расчет каждого х и хсредн, у и усредн при помощи оператора «-».

14
- Производим перемножение вычисленных разностей.

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

17
- Используя функцию АВТОСУММА, найдем показатели в полученных столбиках. Производим перемножение. При помощи функции КОРЕНЬ возводим результат в квадрат.
- Производим подсчет частного, используя значения знаменателя и числителя.
- КОРРЕЛ – интегрированная функция, которая позволяет предотвратить проведение сложнейших расчетов. Заходим в «Мастер функций», выбираем КОРРЕЛ и указываем массивы показателей х и у. Строим график, отображающий полученные значения.

21
Матрица парных коэффициентов корреляции в Excel
Разберем, как проводить подсчет коэффициентов парных матриц. К примеру, есть матрица из четырех переменных.
- Заходим в «Анализ данных», находящийся в блоке «Анализ» вкладки «Данные». В отобразившемся списке выбираем «Корелляция».
- Выставляем все необходимые настройки. «Входной интервал» – интервал всех четырех колонок. «Выходной интервал» – место, в котором желаем отобразить итоги. Кликаем на кнопку «ОК».
- В выбранном месте построилась матрица корреляции. Каждое пересечение строки и столбца – коэффициенты корреляции. Цифра 1 отображается при совпадающих координатах.
Прочие возможности
Также при помощи функции КОРРЕЛ можно провести более сложные исследования. Примером является парная и множественная корреляция. Отличие их заключается в том, что при множественной корреляции независимых переменных, влияющих на величину, может быть две и более, а при парной – только одна. Эти инструменты используют специалисты при анализе большого количества данных для проведения статистических исследований и выявления сложных зависимостей одной величины от множества других или их отсутствие.
Также можно сделать график, чтобы наглядно показать зависимость одной величины от другой. Сделаем это для первого примера с рекламой и продажами.

Такой способ отображения данных позволяет быстро оценить влияние, а коэффициент корреляции отображает силу зависимости. Однако делать окончательный вывод на основе корреляционных исследований не рекомендуется, необходимо проводить дополнительный анализ влияющих факторов.
Как видите, редактор Excel от Microsoft позволяет проводить статистические исследования и выявлять взаимосвязи между массивами данных при помощи встроенных функций. Корреляция дает общее представление о взаимосвязи данных, но более точные результаты можно получить только с использованием нескольких статистических инструментов.
Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel
КОРРЕЛ – функция, применяемая для подсчета коэффициента корреляции между 2-мя массивами. Разберем на четырех примерах все способности этой функции.
Примеры использования функции КОРРЕЛ в Excel
Первый пример. Есть табличка, в которой расписана информация об усредненных показателях заработной платы работников компании на протяжении одиннадцати лет и курсе $. Необходимо выявить связь между этими 2-умя величинами. Табличка выглядит следующим образом:
Алгоритм расчёта выглядит следующим образом:

25
Отображенный показатель близок к 1. Результат:

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

27
Используя нижеприведенные формулы, рассчитаем коэффициент корреляции:
- =КОРРЕЛ(А3:А17;В3:В17).
- =КОРРЕЛ(А3:А17;С3:С17).

28
Из полученных результатов становится понятно, что степень поддержки 1-го претендента повышалась с каждыми сутками проведения рекламного продвижения, следовательно, коэффициент корреляции приближается к 1. При запуске рекламы другой претендент обладал большим числом доверия, и на протяжении 5 дней была положительная динамика. Потом степень доверия понизилась и к пятнадцатым суткам опустилась ниже изначальных показателей. Низкие показатели говорят о том, что рекламное продвижение отрицательно повлияло на поддержку. Не стоит забывать, что на показатели могли повлиять и остальные сопутствующие факторы, не рассматриваемые в табличной форме.
Анализ популярности контента по корреляции просмотров и репостов видео
Третий пример. Человек для продвижения собственных роликов на видеохостинге Ютуб применяет соцсети для рекламирования канала. Он замечает, что существует некая взаимосвязь между числом репостов в соцсетях и количеством просмотров на канале. Можно ли про помощи инструментов табличного процессора произвести прогноз будущих показателей? Необходимо выявить резонность применения уравнения линейной регрессии для прогнозирования числа просмотров видеозаписей в зависимости от количества репостов. Табличка со значениями:

29
Теперь необходимо провести определение наличия связи между 2-мя показателями по нижеприведенной формуле:
0,7;ЕСЛИ(КОРРЕЛ(A3:A8;B3:B8)>0,7;”Сильная прямая зависимость”;”Сильная обратная зависимость”);”Слабая зависимость или ее отсутствие”)’ >
Если полученный коэффициент выше 0,7, то целесообразней применять функцию линейной регрессии. В рассматриваемом примере делаем:

30
Теперь производим построение графика:

31
Применяем это уравнение, чтобы определить число просматриваний при 200, 500 и 1000 репостов: =9,2937*D4-206,12. Получаем следующие результаты:

32
Функция ПРЕДСКАЗ позволяет определить число просмотров в моменте, если было проведено, к примеру, двести пятьдесят репостов. Применяем: 0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);”Величины не взаимосвязаны”)’ Получаем следующие результаты:

33
Особенности использования функции КОРРЕЛ в Excel
Данная функция имеет нижеприведенные особенности:
- Не учитываются ячейки пустого типа.
- Не учитываются ячейки, в которых находится информация типа Boolean и Text.
- Двойное отрицание «–» применяется для учёта логических величин в виде чисел.
- Количество ячеек в исследуемых массивах обязаны совпадать, иначе будет выведено сообщение #Н/Д.
Поле корреляции (диаграмма рассеяния)
Корреляционное поле — это графическое отображение исходных данных. По расположению точек можно определить наличие зависимости и ее характер.

В редакторе Excel построение выполняется с помощью инструмента «Диаграмма»:
- Выделить столбцы с данными.

- Кликнуть «Вставка» — «Точечная» — «Точечная с маркерами».

Результат построения корреляционной матрицы.

По расположению точек на диаграмме можно сделать вывод о том, что прослеживается сильная положительная корреляционная зависимость между величиной затрат на маркетинг и объемом продаж.
Для того, чтобы использовать диаграмму в практических целях, можно добавить линию тренда и уравнение. Для этого нужно выполнить следующие действия:
- Кликнуть правой кнопкой мыши на любой точке диаграммы.
- В контекстном меню выбрать «добавить линию тренда».

- Настроить параметры линии тренда (можно оставить по умолчанию).

- Нажать кнопку «закрыть».

Источник: ktexnika.ru
