Что такое функция КОРРЕЛ в Excel?

Объем данных может быть огромным, но функция КОРРЕЛ в Excel поможет вам устранить шум. Расчет коэффициентов корреляции — секретное оружие для выявления скрытых тенденций и принятия более разумных решений.

Что такое функция КОРРЕЛ в Excel?

КОРРЕЛ — статистическая функция в Excel, используемая для измерения взаимосвязи между двумя наборами данных. Эта функция вычисляет коэффициент корреляции — значение от -1 до 1, которое указывает степень линейной связи между двумя переменными. Вот синтаксис функции КОРРЕЛ в Excel:

=CORREL(array1, array2)

Array1 и array2 — это два диапазона данных, которые вы хотите проанализировать.

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

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

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

Предположим, вы менеджер по продажам, анализирующий данные за прошлый год. У вас есть 3 набора данных: ежемесячный доход от продаж, расходы на телевизионную рекламу и расходы на радиорекламу, каждый из которых содержит 200 точек данных.

Чтобы рассчитать корреляцию между выручкой от продаж и расходами на телевизионную рекламу, выберите пустую ячейку и введите:

=CORREL(A2:A201, B2:B201)

Эта формула указывает Excel на необходимость рассчитать корреляцию между данными о доходах от продаж в ячейках A2:A201 и данными о расходах на телевизионную рекламу в ячейках B2:B201 . Далее, чтобы рассчитать корреляцию между выручкой от продаж и расходами на радиорекламу, используйте следующую формулу:

=CORREL(A2:A201, C2:C201)

Эта формула даст вам коэффициент корреляции между данными о доходах от продаж в A2:A201 и расходами на радиорекламу в C2:C201.

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

Визуализируйте корреляцию с помощью диаграмм рассеяния

Хотя функция КОРРЕЛ даст вам численное значение коэффициента корреляции, иногда визуальное представление может иметь большее влияние. Создайте диаграмму рассеяния для визуализации взаимосвязи между переменными.

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

1. Выберите диапазон данных, содержащий данные о бюджете телевизионной рекламы и продажах, включая заголовки. В этом примере данные о бюджете на телевизионную рекламу находятся в столбце B , а данные о продажах — в столбце A , строки со 2 по 201.
2. Выбрав данные, перейдите на вкладку «Вставка» на ленте Excel.
3. В разделе «Диаграммы» нажмите XY (Scatter) .
4. Excel автоматически создаст точечную диаграмму на основе выбранных вами данных. Данные о продажах будут располагаться на оси X, а данные о бюджете на телевизионную рекламу — на оси Y. Для лучшего форматирования можно добавить названия осей.
5. Чтобы добавить данные о бюджете радиорекламы на диаграмму, щелкните правой кнопкой мыши по диаграмме и выберите «Выбрать данные» в раскрывающемся меню.
6. Нажмите кнопку Добавить в разделе «Записи легенды (серии)» в окне «Выбор источника данных» .
7. В поле «Название серии» выберите ячейку, содержащую бюджет радиорекламы.
8. Для значений серии X выберите диапазон данных о продажах (столбец A, строки со 2 по 201).
9. Для значений серии Y выберите диапазон данных бюджета радиорекламы (столбец C, строки 2–201).
10. Нажмите «ОК» , чтобы применить изменения и закрыть окно «Выбор источника данных» .

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

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

Объединение функции КОРРЕЛ с другими инструментами Excel

Функция КОРРЕЛ — мощный инструмент, но в сочетании с другими функциями Excel она становится еще более мощной. Два ключевых инструмента, которые могут улучшить ваш корреляционный анализ, — это сводные таблицы и пакет инструментов анализа XLMiner.

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

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

XLMiner Analysis ToolPak — это надстройка, которая предоставляет расширенные статистические функции, включая корреляцию. В то время как функция CORREL вычисляет корреляцию между двумя переменными, инструмент «Корреляция» в XLMiner Analysis ToolPak может вычислить корреляцию для всего набора данных за один раз.

Функция КОРРЕЛ — мощный инструмент для анализа взаимосвязей в данных, помогающий получить более глубокое понимание. Однако если вы хотите проанализировать данные с течением времени, функция ТЕНДЕНЦИЯ в Excel — еще один отличный инструмент для изучения.