Корреляционный анализ в excel диаграмма

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

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

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

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

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

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

1. Количество человек, которые стали утопленниками в бассейнах, четко коррелирует с количеством фильмов, в которых Николас Кейдж был актером.
2. Количество съеденной моцареллы и количество человек, которые получили докторскую степень, также коррелирует на протяжении 2000-2009 годов. Наверно, действительно, моцарелла как-то влияет на мозг и стимулирует желание совершать научные открытия.
3. Почти во всех случаях средний возраст женщин, которые получили статус «Мисс Америка» коррелирует с количеством людей, которые погибли от нахождения в горячем паре.
4. Число людей, которое погибло в результате дорожно-транспортного происшествия, четко коррелирует с количеством сметаны, которое съедают люди.
5. Мало кто знает, что чем больше курятины человек ест, тем больше сырой нефти импортируется в мире. Правда, это тоже пример ложной корреляции. Кстати, импорт сырой нефти родом из Норвегии тесно связано с количеством людей, которые погибли в результате столкновения автомобиля с поездом. Причем в этом случае корреляция почти 100 процентов.
6. А еще маргарин негативно влияет на статистику разводов. Чем больше людей, которые проживали в штате Мэн, потребляли маргарина, тем выше была частота разводов. Правда, здесь еще может быть рациональное зерно. Ведь частота потребления маргарина имеет обратную корреляцию с экономическим положением в семье. В свою очередь, плохое экономическое положение в семье имеет непосредственную связь с количеством разводов. И это уже доказано научно. Так что кто знает, может, эта корреляция и не является такой ложной. Правда, никто этого не перепроверял.
7. Количество денег, которое правительство США тратит на развитие науки, космоса и технологий, имеет тесную связь с количеством самоубийств, проведенных в форме повешения или удушения.

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

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

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

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

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

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

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

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

1. С помощью левой кнопки мыши выделяем ту ячейку, в которой будет находиться получившийся коэффициент корреляции. После этого находим слева от строки формул кнопку fx, которая откроет инструмент ввода функций. 
2. Далее выбираем категорию «Полный алфавитный перечень», в котором ищем функцию КОРРЕЛ. Как видно из названия категории, все названия функций располагаются в алфавитном порядке. 
3. Далее открывается окно ввода параметров функции. У нас два основных аргумента, каждый из которых являет собой массив данных, которые сравниваются между собой. В поле «Массив 1» указываем координаты первого диапазона, а в поле «Массив 2» – адрес второго диапазона. Для ввода данных массива, используемого для расчета, достаточно выделить нажать левой кнопкой мыши по соответствующему полю и выделить правильный диапазон. 
4. После того, как мы введем данные в аргументы, нажимаем кнопку «ОК», чем подтверждаем совершенные действия.

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

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

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

1. Нажимаем на кнопку «Файл», которая находится в левом верхнем углу сразу возле вкладки «Главная». 
2. После этого открываем раздел с настройками. 
3. В меню слева переходим в предпоследний пункт, озаглавленный, как «Надстройки». Делаем левый клик по соответствующей надписи. 
4. Открывается окно управления надстройками. Нам нужно переключить поле ввода, находящееся внизу, на пункт «Надстройки Excel» и нажать на «Перейти». Если это поле уже находится в таком положении, то не выполняем никаких изменений. 
5. Затем включаем пакет анализа в настройках. Для этого ставим соответствующую галочку и нажимаем на кнопку «ОК». 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Correlation basically means a mutual connection between two or more sets of data. In statistics bivariate data or two random variables are used to find the correlation between them. Correlation coefficient is generally the measurement of correlation between the bivariate data which basically denotes how much two random variables are correlated with each other.

If the correlation coefficient is 0, the bivariate data are not correlated with each other.

If the correlation coefficient is -1 or +1, the bivariate data are strongly correlated with each other.

r=-1 denotes strong negative relationship and r=1 denotes strong positive relationship.

In general, if the correlation coefficient is close to -1 or +1 then we can say that the bivariate data are strongly correlated to each other.

The correlation coefficient is calculated using Pearson’s Correlation Coefficient which is given by :

where,

r : Correlation coefficient
 : Values of the variable x.
 : Values of the variable y.
n : Number of samples taken in the data set.
Numerator : Covariance of x and y.
Denominator : Product of Standard Deviation of x and Standard Deviation of y.

In this article we are going to discuss how to make correlation charts in Excel using suitable examples.

Example 1 : Consider the following data set :

FINDING CORRELATION COEFFICIENT IN EXCEL

In Excel to find the correlation coefficient use the formula :

=CORREL(array1,array2)
array1 : array of variable x
array2: array of variable y

To insert array1 and array2 just select the cell range for both.

1. Let’s find the correlation coefficient for the variables and X and Y1.

array1 : Set of values of X. The cell range is from A2 to A6.

array2 : Set of values of Y1. The cell range is from B2 to B6.

Similarly, you can find the correlation coefficients for (X , Y2) and (X , Y3) using the Excel formula.

Finally, the correlation coefficients are as follows :

From the above table we can infer that :

X and Y1 has negative correlation coefficient.

X and Y2 has positive correlation coefficient.

X and Y3 are not correlated as the correlation coefficient is almost zero.

Correlation Chart in Excel:

 A scatter plot is mostly used for data analysis of bivariate data. The chart consists of two variables X and Y where one of them is independent and the second variable is dependent on the previous one. The chart is a pictorial representation of how these two data are correlated with each other. Three cases are possible on the basis of the value of the correlation coefficient, R as shown below :

Example 2: Consider the following data set :

The correlation coefficients for the above data set are :

The steps to plot a correlation chart are :

• Select the bivariate data X and Y in the Excel sheet.
• Go to Insert tab on the top of the Excel window.
• Select Insert Scatter or Bubble chart. A pop-down menu will appear.
• Now select the Scatter chart.

• Now, we need to add a linear trendline in the scatter plot to show the correlation between the bivariate data. In order to do so, select the chart and from the top right corner click on the “+” button and then check the box of Trendline.

• The trendline is now added and our correlation chart is now ready.

• Now you can format the Trendline by selecting and clicking on the “Format Trendline” option. A dialog box will open where you can change the type and color of the trendline and also show the  value in the chart. 

You can further format the above chart by making it more interactive by changing the “Chart Styles”, adding suitable “Axis Titles”, “Chart Title”, “Data Labels”, changing the “Chart Type” etc. It can be done using the “+” button in the top right corner of the Excel chart.

Finally, after all the modification the charts look like :

Since the correlation coefficient is R=-0.79, we have obtained a negative correlated chart. The linear trendline will grow downwards.

Since the correlation coefficient is R=0.89, we have obtained a positive correlated chart. The linear trendline will grow upwards.

Since the correlation coefficient is R=0.01, which is approximately 0, so we have obtained a zero correlated chart. The linear trendline will be a straight line parallel to X-axis and it implies the bivariate data X and Y3 are not correlated to each other.

2 способа корреляционного анализа в Microsoft Excel

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

​.​Открывается окно доступных надстроек​

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

​ результатов анализа было​В окне надстроек устанавливаем​В списке, который представлен​Корреляционный анализ – популярный​ из столбцов Y​ нескольких переменных.​Рассмотрим на примере способы​ 100 000, а​

​ ее помощью выявляется​ число коэффициента (для​ анализируемого параметра влияют​ заработная плата (х).​ примерах рассмотрим эти​Аргументы функции КОРРЕЛ описаны​После того, как все​Открывается небольшое окошко. В​ Эксель. Ставим галочку​ оставлено по умолчанию,​ галочку около пункта​ в окне Мастера​ метод статистического исследования,​ и X. Пробовал​Матрица коэффициентов корреляции в​ расчета коэффициента корреляции,​ максимальное – 200​ зависимость и характер​ каждой сферы деятельности​ и другие факторы,​В Excel существуют встроенные​ два очень популярные​ ниже.​ настройки установлены, жмем​ нём выбираем пункт​

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

​ около пункта​ мы перемещаемся на​«Пакет анализа»​ функций, ищем и​ который используется для​ строить точечную диаграмму,​ Excel строится с​ особенности прямой и​ 000. Показатели объема​ связи между двумя​ есть своя шкала).​ не описанные в​ функции, с помощью​

Способ 1: определение корреляции через Мастер функций

​ в среде экономистов​Массив1​ на кнопку​«Регрессия»​«Пакет анализа»​ новый лист. Как​​. Жмем на кнопку​​ выделяем функцию​

1. ​ выявления степени зависимости​ не получается сделать​ помощью инструмента «Корреляция»​ обратной взаимосвязи между​​ продаж находятся в​​ разными параметрами экономического​Для корреляционного анализа нескольких​

   

2. ​ модели.​ которых можно рассчитать​ анализа. А также​    — обязательный аргумент. Диапазон​​«OK»​​. Жмем на кнопку​​. Жмем на кнопку​​ видим, тут указан​

   
3. ​«OK»​КОРРЕЛ​​ одного показателя от​​ так, что бы​ из пакета «Анализ​ переменными.​ этих пределах:​ явления, производственного процесса.​ параметров (более 2)​Коэффициент -0,16285 показывает весомость​ параметры модели линейной​ приведем пример получения​ ячеек со значениями.​.​«OK»​ «OK».​

   ​ коэффициент корреляции. Естественно,​​.​​. Жмем на кнопку​ другого. В Microsoft​ в нижней(горизонтальной) оси​ данных».​Значения показателей x и​Минимальное значение для горизонтальной​ Диаграмма разброса показывает​ удобнее применять «Анализ​

   ​ переменной Х на​​ регрессии. Но быстрее​​ результатов при их​

​Массив2​Результаты регрессионного анализа выводятся​.​Теперь, когда мы перейдем​ он тот же,​После этого пакет анализа​«OK»​ Excel имеется специальный​ отображались параметры X,​На вкладке «Данные» в​

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

​ y:​ оси Х –​ вид и тесноту​ данных» (надстройка «Пакет​ Y. То есть​ это сделает надстройка​ объединении.​

1. ​    — обязательный аргумент. Второй​​ в виде таблицы​​Открывается окно настроек регрессии.​

   

2. ​ во вкладку​ что и при​​ активирован. Переходим во​​.​

   

3. ​ инструмент, предназначенный для​​ в той последовательности,​​ группе «Анализ» открываем​

   

4. ​Y – независимая переменная,​ 100, т.к. ниже​​ взаимосвязи между парами​​ анализа»). В списке​ среднемесячная заработная плата​​ «Пакет анализа».​​Показывает влияние одних значений​ диапазон ячеек со​ в том месте,​​ В нём обязательными​​«Данные»​

   
5. ​ использовании первого способа​ вкладку​​Открывается окно аргументов функции.​​ выполнения этого типа​​ как они стоят​​ пакет «Анализ данных»​

   

6. ​ x – зависимая.​ этого показателя данных​ данных. К примеру,​​ нужно выбрать корреляцию​​ в пределах данной​Активируем мощный аналитический инструмент:​ (самостоятельных, независимых) на​ значениями.​​ которое указано в​​ для заполнения полями​​, на ленте в​​ – 0,97. Это​«Данные»​

   

7. ​ В поле​ анализа. Давайте выясним,​ в таблице.​​ (для версии 2007).​​ Необходимо найти силу​​ в таблице нет.​​ между:​

   

8. ​ и обозначить массив.​ модели влияет на​Нажимаем кнопку «Офис» и​ зависимую переменную. К​​Если аргумент, который является​​ настройках.​ являются​ блоке инструментов​ объясняется тем, что​. Как видим, тут​«Массив1»​ как пользоваться данной​ber$erk​ Если кнопка недоступна,​

   ​ (сильная / слабая)​​Диаграмма разброса приобрела следующий​​качеством продукта и влияющим​ Все.​​ количество уволившихся с​​ переходим на вкладку​ примеру, как зависит​ массивом или ссылкой,​Одним из основных показателей​«Входной интервал Y»​«Анализ»​ оба варианта выполняют​ на ленте появляется​вводим координаты диапазона​​ функцией.​​: >>> отображались параметры​

   ​ нужно ее добавить​ и направление (прямая​​ вид:​​ фактором;​Полученные коэффициенты отобразятся в​ весом -0,16285 (это​ «Параметры Excel». «Надстройки».​ количество экономически активного​ содержит текст, логические​ является​и​мы увидим новую​ одни и те​ новый блок инструментов​

   ​ ячеек одного из​Скачать последнюю версию​​ X, в той​​ («Параметры Excel» -​

​ / обратная) связи​Какие можно сделать выводы​двумя разными характеристиками качества;​ корреляционной матрице. Наподобие​ небольшая степень влияния).​Внизу, под выпадающим списком,​ населения от числа​ значения или пустые​R-квадрат​«Входной интервал X»​ кнопку –​ же вычисления, просто​ –​ значений, зависимость которого​ Excel​ последовательности, как они​ «Надстройки»). В списке​

​ между ними. Формула​ по данной диаграмме​двумя обстоятельствами, влияющими на​ такой:​ Знак «-» указывает​ в поле «Управление»​ предприятий, величины заработной​ ячейки, то такие​. В нем указывается​. Все остальные настройки​«Анализ данных»​

​ произвести их можно​

lumpics.ru

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

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

​.​ разными способами.​

Подключение пакета анализа

​. Жмем на кнопку​ нашем случае это​ к выявлению наличия​А как вы​ «Корреляция».​ так:​Каждая точка дает представление​Диаграммы рассеяния применяются для​ методики часто применяются​

1. ​ чем больше зарплата,​​ Excel» (если ее​​ параметров. Или: как​

   

2. ​ ячейки, которые содержат​​ нашем случае данный​​ умолчанию.​

   

3. ​Существует несколько видов регрессий:​Как видим, приложение Эксель​​«Анализ данных»​​ будут значения в​

   

4. ​ зависимости между различными​ себе это представляеете?​Нажимаем ОК. Задаем параметры​​Чтобы упростить ее понимание,​​ об объеме продаж​​ обнаружения корреляции между​​ вместе.​ тем меньше уволившихся.​ нет, нажмите на​​ влияют иностранные инвестиции,​​ нулевые значения, учитываются.​

   
5. ​ коэффициент равен 0,705​В поле​параболическая;​​ предлагает сразу два​​, которая расположена в​ колонке «Величина продаж».​

​ факторами. То есть,​ Ось на то​​ для анализа данных.​​ разобьем на несколько​ и контактах (как​​ данными. Если корреляционная​​Пример:​ Что справедливо.​​ флажок справа и​​ цены на энергоресурсы​

Виды регрессионного анализа

​Если «массив1» и «массив2″​

• ​ или около 70,5%.​
• ​«Входной интервал Y»​
• ​степенная;​
• ​ способа корреляционного анализа.​
• ​ нем.​
• ​ Для того, чтобы​
• ​ определяется, влияет ли​

​ она и ось,​ Входной интервал –​ несложных элементов.​ об одномерных совокупностях)​

Линейная регрессия в программе Excel

​ зависимость присутствует, то​Строим корреляционное поле: «Вставка»​​ выберите). И кнопка​ и др. на​ имеют различное количество​ Это приемлемый уровень​указываем адрес диапазона​логарифмическая;​ Результат вычислений, если​Открывается список с различными​ внести адрес массива​ уменьшение или увеличение​ что на ней​

​ диапазон ячеек со​Найдем средние значения переменных,​ и о взаимосвязи​​ установить контроль над​ - «Диаграмма» -​​Корреляционный анализ помогает установить,​​ «Перейти». Жмем.​​ уровень ВВП.​ точек данных, функция​ качества. Зависимость менее​ ячеек, где расположены​экспоненциальная;​ вы все сделаете​​ вариантами анализа данных.​​ в поле, просто​ одного показателя на​ все по возрастанию​​ значениями. Группирование –​​ используя функцию СРЗНАЧ:​ между этими параметрами.​ наблюдаемым явлением значительно​ «Точечная диаграмма» (дает​ есть ли между​​Открывается список доступных надстроек.​​Результат анализа позволяет выделять​ КОРРЕЛ возвращает значение​

1. ​ 0,5 является плохой.​​ переменные данные, влияние​​показательная;​ правильно, будет полностью​​ Выбираем пункт​​ выделяем все ячейки​​ изменение другого.​​ идет.​

   

2. ​ по столбцам (анализируемые​Посчитаем разницу каждого y​​Количество контактов (горизонтальная ось)​​ проще.​​ сравнивать пары). Диапазон​​ показателями в одной​

   

3. ​ Выбираем «Пакет анализа»​ приоритеты. И основываясь​ ошибки #Н/Д.​Ещё один важный показатель​​ факторов на которые​​гиперболическая;​​ идентичным. Но, каждый​​«Корреляция»​ с данными в​Если зависимость установлена, то​

   ​Приложите хотябы картинку​​ данные сгруппированы в​​ и yсредн., каждого​ распределилось в диапазоне​​ значений – все​ или двух выборках​ и нажимаем ОК.​ на главных факторах,​Если какой-либо из массивов​ расположен в ячейке​ мы пытаемся установить.​линейная регрессия.​ пользователь может выбрать​. Кликаем по кнопке​ вышеуказанном столбце.​

   ​ определяется коэффициент корреляции.​​ — как должно​​ столбцы). Выходной интервал​ х и хсредн.​ 140-220. Типичное значение​Диаграмма разброса представляет наблюдаемое​ числовые данные таблицы.​ связь. Например, между​После активации надстройка будет​ прогнозировать, планировать развитие​ пуст или если​ на пересечении строки​ В нашем случае​О выполнении последнего вида​ более удобный для​«OK»​В поле​ В отличие от​

   

   ​ все выглядеть в​ – ссылка на​ Используем математический оператор​ равно примерно 170.​ явление в пространстве​Щелкаем левой кнопкой мыши​ временем работы станка​ доступна на вкладке​ приоритетных направлений, принимать​ «s» (стандартное отклонение)​«Y-пересечение»​ это будут ячейки​ регрессионного анализа в​ него вариант осуществления​.​«Массив2»​ регрессионного анализа, это​ итоге.​ ячейку, с которой​ «-».​Объемы продаж за анализируемый​ двух измерений. Если​ по любой точке​ и стоимостью ремонта,​ «Данные».​

   

   ​ управленческие решения.​ их значений равно​и столбца​​ столбца «Количество покупателей».​​ Экселе мы подробнее​

Разбор результатов анализа

​ расчета.​Открывается окно с параметрами​нужно внести координаты​ единственный показатель, который​________________________​

​ начнется построение матрицы.​Теперь перемножим найденные разности:​​ период (вертикальная ось)​​ одну величину рассматривать​ на диаграмме. Потом​ ценой техники и​Теперь займемся непосредственно регрессионным​Регрессия бывает:​ нулю, функция КОРРЕЛ​«Коэффициенты»​ Адрес можно вписать​

​ поговорим далее.​Автор: Максим Тютюшев​ корреляционного анализа. В​​ второго столбца. У​​ рассчитывает данный метод​​[email protected]​​ Размер диапазона определится​Найдем сумму значений в​ находятся в диапазоне​ как «причину», влияющую​ правой. В открывшемся​ продолжительностью эксплуатации, ростом​ анализом.​линейной (у = а​ возвращает значение ошибки​

​. Тут указывается какое​​ вручную с клавиатуры,​​Внизу, в качестве примера,​​Регрессионный анализ является одним​​ отличие от предыдущего​ нас это затраты​ статистического исследования. Коэффициент​anvg​ автоматически.​ данной колонке. Это​ примерно от 130​ на другую величину,​ меню выбираем «Добавить​

​ и весом детей​Открываем меню инструмента «Анализ​ + bx);​ #ДЕЛ/0!.​ значение будет у​ а можно, просто​ представлена таблица, в​ из самых востребованных​ способа, в поле​

​ на рекламу. Точно​

lumpics.ru

КОРРЕЛ (функция КОРРЕЛ)

​ корреляции варьируется в​: gooou​После нажатия ОК в​​ и будет числитель.​​ 000 до 190​

Описание

​ то ей будет​ линию тренда».​ и т.д.​ данных». Выбираем «Регрессия».​параболической (y = a​Уравнение для коэффициента корреляции​ Y, а в​ выделить требуемый столбец.​ которой указана среднесуточная​ методов статистического исследования.​

Синтаксис

​«Входной интервал»​

​ так же, как​ диапазоне от +1​

• ​А что за​​ выходном диапазоне появляется​Для расчета знаменателя разницы​

• ​ 000. Типичное значение​​ соответствовать ось Х​Назначаем параметры для линии.​Если связь имеется, то​

Замечания

• ​Откроется меню для выбора​ + bx +​ имеет следующий вид:​ нашем случае, это​ Последний вариант намного​ температура воздуха на​ С его помощью​мы вводим интервал​

• ​ и в предыдущем​ до -1. При​ термин такой: Поле​ корреляционная матрица. На​ y и y-средн.,​

• ​ равняется приблизительно 150​ (горизонтальная ось). Реагирующей​ Тип – «Линейная».​ влечет ли увеличение​ входных значений и​ cx2);​где​

• ​ количество покупателей, при​ проще и удобнее.​

  

  ​ улице, и количество​

  

  ​ можно установить степень​ не каждого столбца​

Пример

​ случае, заносим данные​ наличии положительной корреляции​ корреляции? Что то​ пересечении строк и​ х и х-средн.​ 000.​ на это влияние​ Внизу – «Показать​ одного параметра повышение​ параметров вывода (где​экспоненциальной (y = a​являются средними значениями выборок​ всех остальных факторах​

support.office.com

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

​ столбцов, которые участвуют​Жмем на кнопку​ способствует увеличению второго.​ такого нет :-(​ корреляции. Если координаты​ квадрат.​ и объемом сбыта​

​ Y (вертикальная ось).​Жмем «Закрыть».​ уменьшение (отрицательная) другого.​ полях для исходных​степенной (y = a*x^b);​Скопируйте образец данных из​ этой таблице данное​вводим адрес диапазона​

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

​ Давайте выясним при​ В функционале Microsoft​ в анализе. В​«OK»​ При отрицательной корреляции​По графику -​ совпадают, то выводится​Находим суммы значений в​ является положительной, т.к.​ Когда четко классифицировать​Теперь стали видны и​ Корреляционный анализ помогает​ данных указываем диапазон​

​гиперболической (y = b/x​ следующей таблицы и​ значение равно 58,04.​ ячеек, где находятся​ помощи регрессионного анализа,​ Excel имеются инструменты,​

​ нашем случае это​

• ​.​ увеличение одного показателя​
• ​ так?​ значение 1.​ полученных колонках (с​
• ​ точки выстроились слева​ переменные невозможно, распределение​
• ​ данные регрессионного анализа.​
• ​ аналитику определиться, можно​ описываемого параметра (У)​
• ​ + a);​ вставьте их в​Значение на пересечении граф​
• ​ данные того фактора,​ как именно погодные​

​ предназначенные для проведения​ данные в столбцах​Как видим, коэффициент корреляции​ влечет за собой​Guest​

​Между значениями y и​ помощью функции АВТОСУММА).​ направо снизу вверх.​ производится пользователем.​В окружающем мире очень​ ли по величине​ и влияющего на​

​логарифмической (y = b​ ячейку A1 нового​

​«Переменная X1»​_{​ влияние которого на​}​ условия в виде​_{​ подобного вида анализа.​}​ «Затраты на рекламу»​_{​ в виде числа​}​ уменьшение другого. Чем​_{​: Вот аналогичный пример.​}​ х1 обнаружена сильная​_{​ Перемножаем их. Результат​}​ Следовательно, чем больше​

​Построим диаграмму рассеяния для​ много взаимосвязей между​ одного показателя предсказать​ него фактора (Х).​

​ * 1n(x) +​ листа Excel. Чтобы​и​ переменную мы хотим​ температуры воздуха могут​

​ Давайте разберем, что​ и «Величина продаж».​ появляется в заранее​ больше модуль коэффициента​anvg​ прямая взаимосвязь. Между​ возводим в квадрат​

​ у менеджера было​

1. ​ небольшой двумерной совокупности​ объектами, предметами, событиями,​ возможное значение другого.​
2. ​ Остальное можно и​ a);​ отобразить результаты формул,​«Коэффициенты»​ установить. Как говорилось​ повлиять на посещаемость​ они собой представляют​Параметр​
3. ​ выбранной нами ячейке.​ корреляции, тем заметнее​: График то вот​

​ х1 и х2​ (функция КОРЕНЬ).​ контактов с клиентами​

​ данных:​ отношениями и т.д.​

1. ​Коэффициент корреляции обозначается r.​ не заполнять.​
2. ​показательной (y = a​ выделите их и​показывает уровень зависимости​ выше, нам нужно​ торгового заведения.​ и как ими​«Группирование»​ В данном случае​ изменение одного показателя​ такой.​ имеется сильная обратная​
3. ​Осталось посчитать частное (числитель​ (точки правее), тем​Предположим, что затраченные усилия​ Например, между количеством​ Варьируется в пределах​После нажатия ОК, программа​ * b^x).​ нажмите клавишу F2,​

​ Y от X.​ установить влияние температуры​Общее уравнение регрессии линейного​

​ пользоваться.​оставляем без изменений​ он равен 0,97,​ отражается на изменении​Только кто вам​ связь. Связь со​ и знаменатель уже​ больше прибыли организации​ каждого менеджера повлияли​ заключенных контрактов и​ от +1 до​ отобразит расчеты на​Рассмотрим на примере построение​ а затем — клавишу​ В нашем случае​ на количество покупателей​ вида выглядит следующим​

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

​ -1. Классификация корреляционных​ новом листе (можно​ регрессионной модели в​ ВВОД. При необходимости​ — это уровень​ магазина, а поэтому​ образом:​ Excel​«По столбцам»​ высоким признаком зависимости​ равном 0 зависимость​ имеет какое-то отношение​ х3 практически отсутствует.​Между переменными определяется сильная​

​ выше).​

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

​ работы (так принято​ сбытом и доходами​ связей для разных​ выбрать интервал для​ Excel и интерпретацию​ измените ширину столбцов,​ зависимости количества клиентов​ вводим адрес ячеек​У = а0 +​Но, для того, чтобы​, так как у​

​ одной величины от​ между ними отсутствует​ к корреляционному анализу?​Изобразим наглядно корреляционные отношения​ прямая связь.​Коэффициент корреляции отражает степень​ считать). Следовательно, число​ населения, между образованием​ сфер будет отличаться.​ отображения на текущем​

​ результатов. Возьмем линейный​ чтобы видеть все​ магазина от температуры.​ в столбце «Температура».​ а1х1 +…+акхк​ использовать функцию, позволяющую​ нас группы данных​ другой.​ полностью.​ Как минимум подразумевается​

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

​ контактов необходимо показать​ и уровнем заработной​

​ При значении коэффициента​ листе или назначить​ тип регрессии.​ данные.​ Коэффициент 1,31 считается​

​ Это можно сделать​. В этой формуле​ провести регрессионный анализ,​

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

​ вывод в новую​Задача. На 6 предприятиях​Данные1​ довольно высоким показателем​ теми же способами,​

​Y​ прежде всего, нужно​ два столбца. Если​ вычислить с помощью​ коэффициент корреляции на​ от другой. Такой​ y и х1.​ Рассчитаем коэффициент парной​

​ значение от -1​ а продажи (результат​ и состоянием экономики.​

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

​ между выборками не​ книгу).​ была проанализирована среднемесячная​

​Данные2​

1. ​ влияния.​ что и в​означает переменную, влияние​ активировать Пакет анализа.​ бы они были​ одного из инструментов,​
2. ​ конкретном примере. Имеем​ же тип построения​Сильная обратная связь между​ корреляции в Excel​ до 1. Если​ затраченных усилий) –​
3. ​ Каждое из измерений​ существует.​В первую очередь обращаем​ заработная плата и​
4. ​3​

​Как видим, с помощью​ поле «Количество покупателей».​

exceltable.com

Диаграмма рассеяния в Excel и сферы ее применения

​ факторов на которую​ Только тогда необходимые​ разбиты построчно, то​ который представлен в​ таблицу, в которой​ её исключает, поскольку​ y и х2.​ с ее помощью.​ коэффициент расположился около​ на вертикальной.​ в этих парах​Рассмотрим, как с помощью​ внимание на R-квадрат​ количество уволившихся сотрудников.​9​ программы Microsoft Excel​С помощью других настроек​ мы пытаемся изучить.​ для этой процедуры​ тогда следовало бы​ пакете анализа. Но​

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

Что показывает диаграмма рассеяния

​ инструменты появятся на​ переставить переключатель в​ прежде нам нужно​ отдельных колонках затрата​ Y от её​ параллельно друг другу.​ Находим нужную. Аргументы​ об отсутствии связи​ в Excel выделим​ отдельности. Как одномерную​ коэффициент корреляции.​R-квадрат – коэффициент детерминации.​ числа уволившихся сотрудников​

1. ​7​ таблицу регрессионного анализа.​
2. ​ уровень надёжности, константу-ноль,​
3. ​ это количество покупателей.​ ленте Эксель.​

​ позицию​ этот инструмент активировать.​ на рекламу и​ порядкового номера -​ Но если y​ функции – массив​ между переменными.​

​ столбцы «Контакты», «Объем​

Построение диаграммы рассеяния в Excel

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

​Если значение близко к​ продаж» (включая заголовки).​ результат получается лишь​

​ применяется функция КОРРЕЛ.​ – 0,755, или​Модель линейной регрессии имеет​12​ полученными на выходе​ вероятности, и выполнить​x​«Файл»​.​«Файл»​

​ предстоит выяснить степень​Guest​ Значения y увеличиваются​ массив значений х:​ единице (от 0,9,​ Перейдем на вкладку​ при изучении обоих​Задача: Определить, есть ли​ 75,5%. Это означает,​ следующий вид:​

​5​ данными, и понимать​ другие действия. Но,​

​– это различные​.​В параметрах вывода по​.​ зависимости количества продаж​: Как вы изменили​ – значения х​Покажем значения переменных на​ например), то между​

​ «Вставка» в группу​ измерений, взаимосвязи между​ взаимосвязь между временем​ что расчетные параметры​У = а​15​ их суть, сможет​

​ в большинстве случаев,​ факторы, влияющие на​Переходим в раздел​ умолчанию установлен пункт​В открывшемся окне перемещаемся​

​ от суммы денежных​ горизонтальную ось? Почему​

​ уменьшаются.​ графике:​ наблюдаемыми объектами существует​

1. ​ «Диаграммы». Использование данного​ ними.​ работы токарного станка​ модели на 75,5%​0​6​
2. ​ только подготовленный человек.​ эти настройки изменять​ переменную. Параметры​«Параметры»​
3. ​«Новый рабочий лист»​ в раздел​ средств, которая была​ мне не даёт​Отсутствие взаимосвязи между значениями​Видна сильная связь между​ сильная прямая взаимосвязь.​ инструмента анализа возможно​
4. ​При работе с двумерными​ и стоимостью его​ объясняют зависимость между​+ а​17​Автор: Максим Тютюшев​ не нужно. Единственное​a​.​, то есть, данные​«Параметры»​ потрачена на рекламу.​

exceltable.com

Коэффициент парной корреляции в Excel

​ её менять и​ y и х3.​ y и х,​ Если коэффициент близок​ с помощью точечных​ данными обычно рисуют​ обслуживания.​ изучаемыми параметрами. Чем​1​

​Формула​В этой статье описаны​ на что следует​являются коэффициентами регрессии.​Открывается окно параметров Excel.​ будут выводиться на​.​Одним из способов, с​ она не активна?​ Изменения х3 происходят​ т.к. линии идут​ к другой крайней​ диаграмм:​ диаграммы рассеяния. Другие​Ставим курсор в любую​ выше коэффициент детерминации,​х​Описание​ синтаксис формулы и​ обратить внимание, так​ То есть, именно​

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

​ Переходим в подраздел​ другом листе. Можно​Далее переходим в пункт​ помощью которого можно​Файл удален​

​ хаотично и никак​ практически параллельно друг​

​ точке диапазона (-1),​По умолчанию программа построила​ названия – «диаграммы​ ячейку и нажимаем​ тем качественнее модель.​1​Результат​ использование функции​ это на параметры​

​ они определяют значимость​«Надстройки»​ изменить место, переставив​

1. ​«Надстройки»​ провести корреляционный анализ,​
2. ​- велик размер​ не соотносятся с​ другу. Взаимосвязь прямая:​ то между переменными​ диаграмму разброса такого​
3. ​ разброса», «точечные диаграммы».​
4. ​ кнопку fx.​ Хорошо – выше​+…+а​
5. ​=КОРРЕЛ(A2:A6;B2:B6)​КОРРЕЛ​ вывода. По умолчанию​ того или иного​.​
6. ​ переключатель. Это может​.​ является использование функции​ — [​ изменениями y.​ растет y –​
7. ​ имеется сильная обратная​ вида:​ Подобные графики показывают​

​В категории «Статистические» выбираем​ 0,8. Плохо –​

​к​Коэффициент корреляции двух наборов​в Microsoft Excel.​ вывод результатов анализа​ фактора. Индекс​В самой нижней части​ быть текущий лист​В нижней части следующего​ КОРРЕЛ. Сама функция​МОДЕРАТОРЫ​

​Скачать вычисление коэффициента парной​ растет х, уменьшается​

​ взаимосвязь. Когда значение​Изменим параметры горизонтальной и​ значения двух переменных​ функцию КОРРЕЛ.​ меньше 0,5 (такой​х​ данных в столбцах​Возвращает коэффициент корреляции между​ осуществляется на другом​

​k​

Матрица парных коэффициентов корреляции в Excel

​ открывшегося окна переставляем​ (тогда вы должны​ окна в разделе​ имеет общий вид​]​ корреляции в Excel​ y – уменьшается​ находится где-то посередине​

​ вертикальной оси, чтобы​ в виде точек.​Аргумент «Массив 1» -​ анализ вряд ли​к​

1. ​ A и B.​ диапазонами ячеек «массив1″​ листе, но переставив​обозначает общее количество​ переключатель в блоке​ будете указать координаты​«Управление»​КОРРЕЛ(массив1;массив2)​ber$erk​Для чего нужен такой​
2. ​ х.​ от 0 до​ четыре пары показателей​ Если в двумерных​ первый диапазон значений​ можно считать резонным).​.​0,997054486​ и «массив2». Коэффициент​ переключатель, вы можете​ этих самых факторов.​«Управление»​ ячеек вывода информации)​
3. ​переставляем переключатель в​.​: Тип диаграммы не​ коэффициент? Для определения​​ 1 или от​ расположились более равномерно​ данных содержатся какие-либо​

​ – время работы​ В нашем примере​Где а – коэффициенты​Регрессионный и корреляционный анализ​ корреляции используется для​ установить вывод в​Кликаем по кнопке​в позицию​

​ или новая рабочая​ позицию​

1. ​Выделяем ячейку, в которой​ точечная, а график​
2. ​ взаимосвязи между наблюдаемыми​Корреляционная матрица представляет собой​ 0 до -1,​ в области построения.​ проблемы (выбросы), то​ станка: А2:А14.​ – «неплохо».​ регрессии, х –​ – статистические методы​
3. ​ определения взаимосвязи между​ указанном диапазоне на​«Анализ данных»​«Надстройки Excel»​ книга (файл).​«Надстройки Excel»​

​ должен выводиться результат​ с маркерами.​

​ явлениями и составления​ таблицу, на пересечении​ то речь идет​ Щелкнем сначала правой​ их легко будет​

exceltable.com

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

​ о слабой связи​​ кнопкой мыши по​ обнаружить с помощью​ второй диапазон значений​ будет Y, если​
​ – число факторов.​ распространенные способы показать​ можно установить зависимость​ где расположена таблица​ вкладке​ в другом положении.​ жмем на кнопку​
​ в другом положении.​ кнопке​[email protected]​Gooou​
​ которой находятся коэффициенты​
​ (прямой или обратной).​

​ вертикальной оси. Выберем​​ соответствующей диаграммы разброса.​
​ – стоимость ремонта:​ все переменные в​В нашем примере в​ зависимость какого-либо параметра​ между средней температурой​
​ с исходными данными,​«Главная»​

​ Жмем на кнопку​​«OK»​

​ Жмем на кнопку​​«Вставить функцию»​Guest​
​: Добрый день.​ корреляции между соответствующими​ Такую взаимосвязь обычно​ «Формат оси»:​Диаграмма рассеяния – один​ В2:В14. Жмем ОК.​ рассматриваемой модели будут​ качестве У выступает​ от одной или​ в помещении и​ или в отдельной​в блоке инструментов​«Перейти»​

​ показатель уволившихся работников.​​ нескольких независимых переменных.​ использованием кондиционера.​ книге, то есть​
​«Анализ»​
​.​

​Так как место вывода​​.​

planetaexcel.ru

​ от строки формул.​

2 способа корреляционного анализа в Microsoft Excel

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

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

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

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

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

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

Способ 1: определение корреляции через Мастер функций

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

Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.

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

В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.

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

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

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

Переходим во вкладку «Файл».

В открывшемся окне перемещаемся в раздел «Параметры».

Далее переходим в пункт «Надстройки».

В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».

В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.

Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».

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

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

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

Когда все настройки установлены, жмем на кнопку «OK».

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Корреляционно-регрессионный анализ в 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

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

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

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

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

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

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

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

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

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

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

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

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

На практике эти две методики часто применяются вместе.

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

Теперь стали видны и данные регрессионного анализа.

Задача №3. Расчёт параметров регрессии и корреляции с помощью Excel

По территориям региона приводятся данные за 200Х г.

1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнения линейной регрессии

.

3. Оцените тесноту связи с помощью показателей корреляции и детерминации.

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

5. Оцените с помощью средней ошибки аппроксимации качество уравнений.

6. Оцените с помощью F-критерия Фишера статистическую надёжность результатов регрессионного моделирования.

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости .

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

Решим данную задачу с помощью Excel.

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

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

Выделите область ячеек, содержащую данные.

Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.

Рисунок 1 Построение поля корреляции

Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.

2. Для расчёта параметров уравнения линейной регрессии
воспользуемся встроенной статистической функцией ЛИНЕЙН.

1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.
4) В окне Категория выберете Статистические, в окне функция – ЛИНЕЙН. Щёлкните по кнопке ОК как показано на Рисунке 2;

Рисунок 2 Диалоговое окно «Мастер функций»

5) Заполните аргументы функции:

Известные значения у – диапазон, содержащий данные результативного признака;

Известные значения х – диапазон, содержащий данные факторного признака;

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;

Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щёлкните по кнопке ОК;

Рисунок 3 Диалоговое окно аргументов функции ЛИНЕЙН

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

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

Основы регрессионного анализа для инвесторов. Построение модели в Excel

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

Что такое регрессия

Регрессионный анализ является статистическим методом исследования. Он позволяет оценить зависимость одной (зависимой) переменной от других (независимых) переменных. Самой простой является линейная регрессия. Ее формула такова:

Y = a0 + a1x1 + … + anxn

где Y — зависимая переменная,
x — независимые переменные, влияющие на нее,
a — коэффициенты регрессии.

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

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

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

Пример расчетов в Excel и выводы

В качестве примера возьмем акции американского нефтегазового гиганта Exxon Mobil (XOM). Модель будет упрощенной и учебной и не является рекомендацией для осуществления операций с бумагами, ситуацию нужно смотреть в комплексе.

Независимыми переменными у нас выступят фьючерсы на американскую нефть WTI (склеенные фронтальные контракты) и индекс S&P 500. Логика проста — бизнес компании зависит от цен на нефть, а поведение акций в теории должно быть связано в общерыночной ситуацией.

Шаг 1. Выкачиваем в Excel котировки XOM, SPX и CL1. Данные возьмем за пять лет. Так как на более длительных периодах наблюдалась разная структурная ситуация на нефтяном рынке. Возьмем статистику в недельной разбивке, будет 262 наблюдения.

Шаг 2. Активируем настройку регрессионного анализа. Открываем раздел Файл. Переходим на вкладку Параметры Excel — Надстройки. Внизу появившегося окна будет вкладка Управление, где стоит параметр Надстройки Excel, жмем — Перейти.

Выбираем опцию Пакет анализа.

Готово. Результат появится в разделе Данные — Анализ данных.

Шаг 3. Строим регрессию. При клике на Анализ данных появится меню с опциями функционала для анализа. Выбираем Регрессия.

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

На выходе получаем вот такие данные.

Шаг 4. Интерпретация. Статистических показателей много. Не вдаваясь в теорию, наиболее интересными являются значения коэффициентов регрессии и показатель R2.

Наша модель будет иметь следующий вид:

Цена акций Exxon Mobil = $96,2 + 0,28*WTI — 0,01*S&P 500

R — квадрат равен 0,61. Показатель показывает, насколько значение зависимой переменной определяется значениями независимых переменных. Речь идет о статистической значимости модели. Модель является очень хорошей, если R2 превышает 0,8, и при этом сама модель имеет экономическое обоснование. В нашем случае все не настолько идеально, но все же выше 0,5, поэтому модель можно использовать.

Отмечу, что в процессе подготовки материала делались расчеты не только за пять лет, но и за 10, и за три года, также WTI заменялась на Brent. Итоговый вариант был выбран в связи с наибольшим значением R2.

Шаг 5. Применение. Рассчитаем в Excel теоретические значения акций Exxon за весь использовавшийся для построения модели период (5 лет).

Построим линейную диаграмму, на которой будут представлены динамика фактической цены и расчетной цены акций. Заметно, что расхождения между двумя величинами редко носили слишком серьезный характер. По состоянию на 06.06.2019 фактическая цена акций составила $74,2, а теоретическая — $76,7. Исходя из этого, критерия бумаги вполне справедливо оценены рынком. Однако это только один, причем упрощенный подход. Ситуацию нужно рассматривать в комплексе. К примеру, медианный таргет аналитиков на 12 месяцев равен $84. Это усредненный показатель результатов моделей фундаментальной оценки, предполагающий заметный потенциал роста.

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

Дополним нашу регрессию корреляционным анализом. Корреляция означает зависимость одного показателя от другого. Коэффициент корреляции — показатель взаимосвязи (в нашем случае финансовых активов).

Строим корреляционную матрицу. В том же разделе Анализ данных выбираем опцию Корреляция. Заполняем окно, как показано ниже, с учетом котировок наших активов.

На выходе получаем корреляционную матрицу. На ней видно, что цена Exxon положительно связана с WTI (коэффициент корреляции = 0,55) и отрицательно зависит от динамики индекса S&P 500 (коэффициент корреляции = -0,48).

Так что Exxon — это преимущественно нефтяная история, зачастую не совпадающая по динамике с широким рынком. Это можно заметить на графике трех активов с 2010 г. Ситуация стала такой с 2014 г., когда рынок нефти обвалился из-за структурных сдвигов. На нашей выборке за 5 лет корреляция между WTI и S&P 500 равна 0,13, то есть несущественна.

Построение графика простой регрессии

Расскажем об еще одном регрессионном функционале Excel. Программа позволяет построить график линейной регрессии. Правда доступно это лишь при наличии одной независимой переменной. В нашем случае ею будет нефть, так как она в большей мере объясняет движения акций Exxon — коэффициент регрессии равен 0,28 против (-0,01) у S&P 500.

Строим точечную диаграмму по XOM и WTI за 5 лет. Получаем поле корреляции. Щелкаем по любой из точек на диаграмме и меню левой кнопки мыши выбираем Добавить линию тренда.

В окне выбираем линейную линию тренда, ставим галочки напротив Показывать уравнение и Поместить на диаграмму R2.

В итоге получим такую схему зависимости Exxon (y) от WTI (x). В нашем случае модель не является статистически значимой — R-квадрат равен лишь 0,3.

Как еще использовать корреляционно-регрессионный анализ

В архивах раздела Обучение БКС Экспресс есть материалы на эту тему.

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

БКС Брокер

пополни брокерский счёт без комиссии

• С карты любого банка
• Прямо на сайте
• Без комиссии

Последние новости

Рекомендованные новости

Итоги торгов. Возобновили опережающие темпы роста, несмотря на санкционные риски

Система объявила о продаже доли в Детском Мире

Топ 5 самых популярных ETF

Крупнейший фондовый рынок Азии терпит бедствие

Все указывает на продолжение коррекции на следующей неделе

Ставки по депозитам в России опустились ниже 6,2%

Рынок США. ФРС перестраховывается

Несмотря на снижение нефти, рубль укрепляется на фоне роста ОФЗ и развивающихся валют

Адрес для вопросов и предложений по сайту: website4@bcs.ru

Copyright © 2008–2019. ООО «Компания БКС» . г. Москва, Проспект Мира, д. 69, стр. 1
Все права защищены. Любое использование материалов сайта без разрешения запрещено.
Лицензия на осуществление брокерской деятельности № 154-04434-100000 , выдана ФКЦБ РФ 10.01.2001 г.

Данные являются биржевой информацией, обладателем (собственником) которой является ПАО Московская Биржа. Распространение, трансляция или иное предоставление биржевой информации третьим лицам возможно исключительно в порядке и на условиях, предусмотренных порядком использования биржевой информации, предоставляемой ОАО Московская Биржа. ООО «Компания Брокеркредитсервис» , лицензия № 154-04434-100000 от 10.01.2001 на осуществление брокерской деятельности. Выдана ФСФР. Без ограничения срока действия.

* Материалы, представленные в данном разделе, не являются индивидуальными инвестиционными рекомендациями. Финансовые инструменты либо операции, упомянутые в данном разделе, могут не подходить Вам, не соответствовать Вашему инвестиционному профилю, финансовому положению, опыту инвестиций, знаниям, инвестиционным целям, отношению к риску и доходности. Определение соответствия финансового инструмента либо операции инвестиционным целям, инвестиционному горизонту и толерантности к риску является задачей инвестора. ООО «Компания БКС» не несет ответственности за возможные убытки инвестора в случае совершения операций, либо инвестирования в финансовые инструменты, упомянутые в данном разделе.

Информация не может рассматриваться как публичная оферта, предложение или приглашение приобрести, или продать какие-либо ценные бумаги, иные финансовые инструменты, совершить с ними сделки. Информация не может рассматриваться в качестве гарантий или обещаний в будущем доходности вложений, уровня риска, размера издержек, безубыточности инвестиций. Результат инвестирования в прошлом не определяет дохода в будущем. Не является рекламой ценных бумаг. Перед принятием инвестиционного решения Инвестору необходимо самостоятельно оценить экономические риски и выгоды, налоговые, юридические, бухгалтерские последствия заключения сделки, свою готовность и возможность принять такие риски. Клиент также несет расходы на оплату брокерских и депозитарных услуг, подачи поручений по телефону, иные расходы, подлежащие оплате клиентом. Полный список тарифов ООО «Компания БКС» приведен в приложении № 11 к Регламенту оказания услуг на рынке ценных бумаг ООО «Компания БКС». Перед совершением сделок вам также необходимо ознакомиться с: уведомлением о рисках, связанных с осуществлением операций на рынке ценных бумаг; информацией о рисках клиента, связанных с совершением сделок с неполным покрытием, возникновением непокрытых позиций, временно непокрытых позиций; заявлением, раскрывающим риски, связанные с проведением операций на рынке фьючерсных контрактов, форвардных контрактов и опционов; декларацией о рисках, связанных с приобретением иностранных ценных бумаг.

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

Задача №3. Расчёт параметров регрессии и корреляции с помощью Excel

По территориям региона приводятся данные за 200Х г.

1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнения линейной регрессии

.

3. Оцените тесноту связи с помощью показателей корреляции и детерминации.

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

5. Оцените с помощью средней ошибки аппроксимации качество уравнений.

6. Оцените с помощью F-критерия Фишера статистическую надёжность результатов регрессионного моделирования.

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости .

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

Решим данную задачу с помощью Excel.

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

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

Выделите область ячеек, содержащую данные.

Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.

Рисунок 1 Построение поля корреляции

Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.

2. Для расчёта параметров уравнения линейной регрессии
воспользуемся встроенной статистической функцией ЛИНЕЙН.

1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.
4) В окне Категория выберете Статистические, в окне функция – ЛИНЕЙН. Щёлкните по кнопке ОК как показано на Рисунке 2;

Рисунок 2 Диалоговое окно «Мастер функций»

5) Заполните аргументы функции:

Известные значения у – диапазон, содержащий данные результативного признака;

Известные значения х – диапазон, содержащий данные факторного признака;

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;

Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щёлкните по кнопке ОК;

Рисунок 3 Диалоговое окно аргументов функции ЛИНЕЙН

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

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

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

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

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

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

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

Регрессия бывает:

• линейной (у = а + bx);
• параболической (y = a + bx + cx²);
• экспоненциальной (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

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

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

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

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

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

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

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

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

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

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

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

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

На практике эти две методики часто применяются вместе.

Пример:

1. Строим корреляционное поле: «Вставка» — «Диаграмма» — «Точечная диаграмма» (дает сравнивать пары). Диапазон значений – все числовые данные таблицы.



2. Щелкаем левой кнопкой мыши по любой точке на диаграмме. Потом правой. В открывшемся меню выбираем «Добавить линию тренда».



3. Назначаем параметры для линии. Тип – «Линейная». Внизу – «Показать уравнение на диаграмме».



4. Жмем «Закрыть».

Теперь стали видны и данные регрессионного анализа.

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

Назначение корреляционного анализа

Корреляционный анализ позволяет найти зависимость одного показателя от другого, и в случае ее обнаружения – вычислить коэффициент корреляции (степень взаимосвязи), который может принимать значения от -1 до +1:

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

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

Выполняем корреляционный анализ

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

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

Метод 1: применяем функцию КОРРЕЛ

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

КОРРЕЛ(массив1;массив2).

Порядок действий при работе с данным инструментом следующий:

1. Встаем в свободную ячейку таблицы, в которой планируем рассчитать коэффициент корреляции. Затем щелкаем по значку “fx (Вставить функцию)” слева от строки формул.
2. В открывшемся окне вставки функции выбираем категорию “Статистические” (или “Полный алфавитный перечень”), среди предложенных вариантов отмечаем “КОРРЕЛ” и щелкаем OK.
3. На экране отобразится окно аргументов функции с установленным курсором в первом поле напротив “Массив 1”. Здесь мы указываем координаты ячеек первого столбца (без шапки таблицы), данные которого требуется проанализировать (в нашем случае – B2:B13). Сделать это можно вручную, напечатав нужные символы с помощью клавиатуры. Также выделить требуемый диапазон можно непосредственно в самой таблице с помощью зажатой левой кнопки мыши. Затем переходим ко второму аргументу “Массив 2”, просто щелкнув внутри соответствующего поля либо нажав клавишу Tab. Здесь указываем координаты диапазона ячеек второго анализируемого столбца (в нашей таблице – это C2:C13). По готовности щелкаем OK.
4. Получаем коэффициент корреляции в ячейке с функцией. Значение “-0,63” свидетельствует об умеренно-сильной обратной зависимости между анализируемыми данными.

Метод 2: используем “Пакет анализа”

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

1. Заходим в меню “Файл”.
2. В перечне слева выбираем пункт “Параметры”.
3. В появившемся окне кликаем по подразделу “Надстройки”. Затем в правой части окна в самом низу для параметра “Управление” выбираем “Надстройки Excel” и щелкаем “Перейти”.
4. В открывшемся окошке отмечаем “Пакет анализа” и подтверждаем действие нажатием кнопки OK.

Все готово, “Пакет анализа” активирован. Теперь можно перейти к выполнению нашей основной задачи:

1. Нажимаем кнопку “Анализ данных”, которая находится во вкладке “Данные”.
2. Появится окно, в котором представлен перечень доступных вариантов анализа. Отмечаем “Корреляцию” и щелкаем OK.
3. На экране отобразится окно, в котором необходимо указать следующие параметры:
   • “Входной интервал”. Выделяем весь диапазон анализируемых ячеек (т.е. сразу оба столбца, а не по одному, как это было в описанном выше методе).
   • “Группирование”. На выбор предложено два варианта: по столбцам и строкам. В нашем случае подходит первый вариант, т.к. именно подобным образом расположены анализируемые данные в таблице. Если в выделенный диапазон включены заголовки, следует поставить галочку напротив пункта “Метки в первой строке”.
   • “Параметры вывода”. Можно выбрать вариант “Выходной интервал”, в этом случае результаты анализа будут вставлены на текущем листе (потребуется указать адрес ячейки, начиная с которой будут выведены итоги). Также предлагается вывод результатов на новом листе или в новой книге (данные будут вставлены в самом начале, т.е. начиная с ячейки A1). В качестве примера оставляем “Новый рабочий лист” (выбран по умолчанию).
   • Когда все готово, щелкаем OK.
4. Получаем тот же самый коэффициент корреляции, что и в первом методе. Это говорит о том, что в обоих случаях мы все сделали верно.

Заключение

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