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

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

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

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

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

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

Корреляционный анализ в Excel — 2 способа

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

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

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

Способ 1. Определение корреляции с помощью Мастера Функций

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

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

Способ 2. Вычисление корреляции с помощью пакета анализа

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

Все, теперь наша надстройка включена. Теперь мы во вкладке «Данные» можем увидеть кнопку «Анализ данных». Если она появилась, то мы все сделали правильно. Нажимаем на нее.

Появляется перечень с выбором разных способов анализа информации. Нам следует выбрать пункт «Корреляция» и нажать на «ОК».

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

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

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

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

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

После того, как мы это сделали, мы теперь можем сделать поле корреляции. Для этого выполняем следующие действия:

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

Диаграмма рассеивания. Поле корреляции

Далее выставляем настройки. Нас интересует тип «Линейная», а в окне параметров нужно поставить флажок «Показывать уравнение на диаграмме».

После подтверждения действий у нас появится что-то типа такого графика.

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

[expert_bq id=»1570″]Давайте приведем такой кейс сотрудник заключал сделки в течение десяти месяцев, и каждый месяц им было совершено определенное их количество. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Таким образом, Эксель дает сразу два метода осуществления корреляционного анализа. Как вы уже понимаете, в результате вычислений итог получится таким же. Но каждый пользователь может выбрать тот метод расчета, который ему больше всего подходит.

Построение графиков функции и поверхностей? в MS EXCEL — информатика, презентации

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

Построение графиков функции и поверхностей ?в MS EXCEL

Рассмотрим пример построения поверхности z = x2 + y2 при x, y Є [-1,1].

1. В диапазон ячеек A2:A12 введем последовательность значений: –1, –0.8, …, 1 переменной x, а в диапазон ячеек B1:L1 – последовательность значений: –1, –0.8, …, 1 переменной y. В ячейку В2 введем формулу

2.Выделим эту ячейку, установим указатель мыши на её маркере заполнения и протащим его так, чтобы заполнить диапазон B2:L12.

Просмотр содержимого документа
«Построение графиков функции и поверхностей ?в MS EXCEL»

Задание. Построить график функции у = sin x на отрезке [– 2; 2] с шагом

№ 2 Построить график функции y= x 3 +2x на отрезке [-2;2 ] с шагом h=0,5

№ 3 Построить график функции y=2х 3 +1 на отрезке [-1;2] с шагом h=0,25

№ 4 Построить график функции y=2х 3 – 1,5х + 3 на отрезке [-5;4] с шагом h=1,5

№ 5 Построить график функции y=2х 3 -10 на отрезке [-1;2] с шагом h=0,25

Решение уравнений графическим способом в EXCEL

Задание. Решить уравнение Х 2 -5Х+6=0 графическим способом.

Рассмотрим пример построения поверхности z = x 2 + y 2 при x, y Є [-1,1].

1. В диапазон ячеек A2:A12 введем последовательность значений: –1, –0.8, …, 1 переменной x , а в диапазон ячеек B1:L1 – последовательность значений: –1, –0.8, …, 1 переменной y . В ячейку В2 введем формулу

2. Выделим эту ячейку, установим указатель мыши на её маркере заполнения и протащим его так, чтобы заполнить диапазон B2:L12 .

Выделим диапазон ячеек A1:L12 и вызовем мастер диаграмм. Тип диаграммы – Поверхность и вид – Поверхность .

=КОРЕНЬ(64-$A2*B$1), скопировать ее в ячейки B3:B18, затем в С2:R18 5.

Выделить диапазон B2:R18 и в мастере диаграмм выбрать тип диаграммы поверхность

z = -sin(x 2 + y 2 )+1 при x, y Є [-1,1].

[expert_bq id=»1570″]Выбираем ВСТАВКА ТОЧЕЧНАЯ — ТОЧЕЧНАЯ С ГЛАДКИМИ КРИВЫМИ И МАРКЕРАМИ и действуем аналогично построению графика линейной функции. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В нашем случае y=5x-2. В ячейку с первым значением y введем формулу: =5*D4-2. В другую ячейку формулу можно ввести аналогично (изменив D4 на D5) или использовать маркер автозаполнения.

Как закрасить область на графике в excel

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